mysql 查询索引优化

最新推荐文章于 2020-10-12 16:55:16 发布

Edward_developer

最新推荐文章于 2020-10-12 16:55:16 发布

阅读量657

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 索引优化

本文链接：https://blog.youkuaiyun.com/Etoak_james/article/details/77531320

mysql 专栏收录该内容

3 篇文章

订阅专栏

创建表：

 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。

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，这就要看是先过滤再排序划算，还是先排序再过滤划算。

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语句,避免创建临时表