创建表:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`user_name` varchar(45) DEFAULT NULL,
`mobile` varchar(45) DEFAULT NULL,
`num` bigint(20) DEFAULT NULL,
`user_id` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id_UNIQUE` (`user_id`),
KEY `index_num_age` (`num`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
可以写个程序插入一些数据作为测试。
创建索引:
ALTER TABLE user ADD INDEX index_num_age (num, age);
首先介绍一下 explain mysql查询计划的返回值代表什么
type=const表示通过索引一次就找到了;
key=primary的话,表示使用了主键;
type=all,表示为全表扫描;
key=null表示没用到索引。type=ref,因为这时认为是多个匹配行,在联合查询中,一般为REF。
id
select查询的序列号
select_type
select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。
table
输出的行所引用的表。
type
联合查询所使用的类型。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
key
显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。
key_len
显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
ref
显示哪个字段或常数与key一起被使用。
rows
这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
Extra
如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。
1.
explain select id,num,age,user_name,mobile,user_id from user where num = 383567854010163526;/** 这里使用了索引 index_num_age**/
反例:explain select id,num,age,user_name,mobile,user_id from user where age = 20;/** 这里age虽然有索引但是看到type是all,全表查询。依照mysql左前缀法则即查询从索引的左前列开始,并且不跳过索引中的列**/
explain select id,num,age,user_name,mobile,user_id from user where age = 20 and num = 1001;/**这里加上了num 查询时索引便会生效**/
explain select id,num,age,user_name,mobile,user_id from user where num = 383567854010163526; 一条正规sql
索引列不应该作为表达式的一部分,即也不能在索引列上使用函数
反例:explain select id,num,age,user_name,mobile,user_id from user where abs(num) = 383567854010163526;
加上abs函数的时候就变成了全表扫描。而没有使用索引 。同理如果对于索引字段进行了算数操作也不会处罚索引。
反例:explain select id,num,age,user_name,mobile,user_id from user where num+1 = 383567854010163526;
尽量借用覆盖索引 意为查询的字段中有索引字段
MySQL支持二种方式的排序,FileSort和Index,后者效率高,它指MySQL扫描索引本身完成排序。FileSort方式效率较低。ORDER BY满足以下情况,会使用Index方式排序:
正例:
explain select id,num,age from user order by num; /** 这里查询的内容中都有索引 order by **/
正例:explain select id,num from user where num = 383567854010163526 order by num;/**id (pk集合索引),num 是有索引的 所以这里查询走的索引**/
反例: explain select id,num,mobile from user where num = 383567854010163526 order by num;/**因为查询字段中有mobile,而mobile 不在索引列表中所以没有走索引 **/
反例:explain select num,mobile from user order by num,id;/**使用了不同的索引,MySQL每回只采用一个索引 order by出现二个索引 num(索引),id(聚合索引pk) **/
反例:explain select num from user order by num asc, age desc;/**对索引列同时使用了ASC和DESC。也会引起filesort **/
反例: explain select num,mobile from user where num = 383567854010163526 order by id;/**where语句与order by语句,使用了不同的索引。 也会引起filesort**/
反例: explain select num,mobile from user where num -1 = 383567854010163526 order by num;/*where语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式 都会引起filesort***/
反例: explain select num,mobile from user where num -1 = 383567854010163526 order by abs(num);/**where语句或者ORDER BY语句中索引列使用了表达式,包括函数表达式 都会引起filesort**/
反例:explain select num,mobile from user where num >383567854010163526 order by num;/** where 语句与ORDER BY语句组合满足最左前缀,但where语句中使用了条件查询 虽然where与order by构成了索引最左有缀的条件,但是where子句中使用的是条件查询。 也会引起filesort**/
在使用表连接的时候 在order by 的时候如果没有使用左边表的索引排序 依然会使用filesort 慎用left join语句,避免创建临时表