mysql sql 索引相关用法,加快查询速度
可用 explain 查看相关的查询是否应用了索引
格式:explain sql语 [\G];
as:
explain select * from wp_posts where id = 10 \G;
结果
id: 1
select_type: SIMPLE
table: wp_posts
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra:
适合大型级别的百级以上的数据分页:
如果少于10W的用 sql_2/sql_3即可
表1:
create table `t1`(
`id` int(11) not null auto_increment,
`title` char(200) not null,
`content` text not null,
flag tinyint(1) default '0'
);
sql_1:(慢)
sql_1 = "select id,title from t1 limit 开始行数,每页的纪录数";
查询慢,因为没有用到索引(改进用sql_2):
sql_2:(多慢/少快)
sql_2 = "select id,title from t1 order by id desc 开始行数,每页的纪录数";
查询不理想,用到了索引,但是如果数据大时,也会变慢,(改进用):
如果加上where,如:
sql_3:(多慢/少快)
sql_3 = "select id,title from t1 where flag = 1 order by id desc 开始行数,每页的纪录数";
此时加上 where 时,失去了索引的意义了,修改如下:
先修改数表,增加索引:(给where 的字段也加上索引,并放在索引的开始字段位置,即就是把 limit的字段放最后)
create table `t1`(
`id` int(11) not null auto_increment,
`title` char(200) not null,
`content` text not null,
`flag` tinyint(1) default '0',
primary key(`id`),
key `idx_key`(`flag`,`id`), /* 此时为增加的索引 */
);
sql_4 = "select id,title from t1 where flag = 1 order by flag,id limit 开始行数,每页的纪录数";
同时可以考虑分页的情况:(基本思路如下:)
先创建一个主表,只有 id,title,flag,
再创建一个详细表,如 id,content,
从主表先搜索出,再从详细表中获取相关的具体内容
user:表
id int primary key;
name char(40) ;
email char(100) not null
phone char(20)
sex char(3)
birthday datetime
....
key index1 (name)
key index2(birthday,sex)
用了主键
select * from user where id = 1;
select * from user where id > 4;
select * from user where id in(1,3);
select * from user where id like '3%';
select * from user order by id ; 只主键才有用
用了 index1
select * from user where name = 'lin3615';
select * from user where name > '';
select * from user where name in();
select * from user where name like '3%';
select * from user where name is not null
select * from user where name != '';
select * from user where name = '';
select * from user where email = 'xx' and name = 'xx';
select * from user where email = '' or name = '';
select * from user where email like '%xx%' or name = '';
用了 index2
select * from user where birthday = ''
没有使用索引
select * from user where id like '%3%';
select * from user where name is null;
select from user where sex = 'M'
总结:当用 like '%查询的字符%', is null, 联合多个字段的索引(不是开始的字段引用),则索引失去意义