Mysql系列-索引优化

1 Explain工具介绍

使用Explain关键字可以模拟优化器执行SQL语句，分析查询语句或结构的性能瓶颈，在select语句之前增加explain关键字，mysql会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行当前SQL;

其中：如果from中包含子查询，仍会执行该子查询，将结果放入临时表中；

2 Explain示例分析

2.1 准备表

CREATE TABLE `mall_order` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',`shop_id` bigint NOT NULL DEFAULT '0' COMMENT '店铺id',`memeber_id` bigint NOT NULL DEFAULT '0' COMMENT '卖家id',`order_no` varchar(32) NOT NULL DEFAULT '' COMMENT '订单号',`order_amount` bigint NOT NULL DEFAULT '0' COMMENT '订单金额',`pay_money` bigint NOT NULL DEFAULT '0' COMMENT '支付金额',`order_state` int NOT NULL DEFAULT '0' COMMENT '订单状态',`address` varchar(512) NOT NULL DEFAULT '' COMMENT '地址',`soure` varchar(5) NOT NULL DEFAULT '' COMMENT '订单来源',`create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',PRIMARY KEY (`id`),KEY `idx_m_s_id` (`shop_id`,`memeber_id`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';CREATE TABLE `mall_order_item` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',`order_id` bigint NOT NULL DEFAULT '0' COMMENT '订单id',`item_id` bigint NOT NULL DEFAULT '0' COMMENT '卖家id',`item_name` varchar(512) NOT NULL DEFAULT '' COMMENT '商品名称',`item_amount` bigint NOT NULL DEFAULT '0' COMMENT '商品金额',`item_count` bigint NOT NULL DEFAULT '0' COMMENT '商品数量',`pay_money` bigint NOT NULL DEFAULT '0' COMMENT '支付金额',`create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',PRIMARY KEY (`id`),KEY `idx_o_itemn_id` (`order_id`,`item_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='订单明细';CREATE TABLE `member_address` (`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',`memeber_id` bigint NOT NULL DEFAULT '0' COMMENT '买家id',`address` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',`create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='买家收货地址';

2.2 explain常用语法

explain select * from mall_order where memeber_id = 2;

explain select * from mall_order where memeber_id = 2;
show WARNINGS;

2.3 explain中的列 

2.3.1 id

• id列的值是select执行顺序，有几个select就会有几个id，id的值是按select出现的顺序增长的。
• id列越大执行顺序优先级越高，id相同则从上往下执行，id位NULL最后执行；

2.3.2 select_type

select_type表示对应行是简单查询还是复杂查询

1）simple：简单查询，查询不包含子查询和union

2）primary：复杂查询中最外层的select

3）subquery：包含在select中的子查询(不是在from中的子句中)

4）derived：包含在from字句中的子查询。mysql会将查询结果存放在一个临时表中，也称为派生（衍生表）表

set session optimizer_switch='derived_merge=off'; -- 5.7起mysql对衍生表进行了合并优化，此处临时关闭
explain select (select 1 from mall_order_item where id = 1) from (select * from mall_order where memeber_id = 2)tt ;

5）union：在union中的第二个和随后的select 

explain select * from mall_order where id = 1 union select * from mall_order where id = 2;

2.3.3 table

table列表示正在访问哪个表。

• 当from子句中有子查询时，table列的值<derivenN>，表示当前查询依赖id=N的查询，因此先执行id=N的查询；
• 当有union时，UNION RESULT的table列的值为<union1,2>,其中1和2表示参与union的select行id；

2.3.4 type 

type列表示关联类型或访问类型，即mysql决定如何查询表中的行，查找数据行记录的大概范围。 

依次从最优到最差：system>const>eq_ref>ref>range>index>ALL

优化结果：一般来说，至少保证查询达到range级别，最好达到ref；

常见的取值说明：

1）NULL：mysql在优化阶段分解查询语句，在执行阶段不需要访问表或索引。例如在所以列中选择最小值，可以单独查找索引来完成，不需要再执行时访问表；

explain select min(id) from mall_order;

2）const,system：mysql会对查询的某部分进行优化并将其转化成一个常量(可以通过show warning查看结果)。例如使用primary key或unique key的所有列与常数比较时，因为表中最多匹配到一行记录，所以读取速度回比较快。system是const的特例，表中只有一条元组匹配时为system

set session optimizer_switch='derived_merge=off'; -- 5.7起mysql对衍生表进行了合并优化，此处临时关闭
explain select * from (select * from mall_order where id = 1)t;

set session optimizer_switch='derived_merge=off'; -- 5.7起mysql对衍生表进行了合并优化，此处临时关闭
explain select * from (select * from mall_order where id = 1)t;
show WARNINGS;

3）eq_ref：primary key 或 unique key索引的所有部分被关联使用，最多只会返回一条符合条件的记录。这种类型可能是除const之外最好的关联类型了，简单的select不会出现这种type；

explain select * from mall_order_item oi left join mall_order mo on mo.id = oi.order_id 

4）ref：不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，即索引要和某个值相比较，可能会找到多个符合条件的行；

4.1）简单select查询，name是普通索引(非唯一索引)

explain select * from mall_order where shop_id = 1;

4.2）关联表查询

explain select * from mall_order mo left join mall_order_item oi on mo.id = oi.order_id 

 5）range

range是范围扫描，通常出现在in、between、>、<、>=等操作中，使用一个索引来检索给定范围的行；

explain select * from mall_order where shop_id >2;

6）index

• 扫描全索引就可以获取到的查询结果；一般是扫描某个二级索引；
• 这种扫描不会从索引的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的；
• 通常这种查询使用覆盖索引(查询的列都在二级索引中)，因为二级索引比较小，所以通常比ALL快一些；

7）ALL

ALL即全表扫描，扫描聚簇索引的全部叶子节点，通常这种情况需要增加索引来优化；

explain select * from member_address;

2.3.5 possible key 

• 显示可能使用哪些所以来查询数据；
• explain时可能出现possible keys有列，而key显示null的情况，这种情况是因为表中的数据不多，mysql计算成本后判断索引效率不如全表扫描，因此不走索引；
• 如果该列是null，则没有相关的索引。在这种情况下，可以通过检查where子句确认是否可以创造一个适当的索引来提高查询性能，然后再使用explain查看效果；

 2.3.6 key

key列显示mysql执行sql过程中实际采用哪个索引来优化该表访问；

如果没有使用索引，则该列值为NULL；

如果想强制mysql使用索引或者忽略possible key列中的索引，可在查询时使用force index或ignore index；

2.3.7 key_len

key_len显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引的哪些列；

key_len的计算规则：

• 字符串，char(n)和varchar(n),5.0.3以后版本中，n代表字符数，而不是字节数，
  • 如果是utf8,一个数字或字母占1个字节，一个汉字占3个字节；
    • char(n):如果存储汉字长度就是3n字节；

    • varchar(n):如果存汉字则长度是3n+2字节，其中+2用来存储字符串的长度(因为varchar是变长字符串)；

  • 如果是utf8mb4,一个数字或字母占1个字节，一个汉字占4个字节；

    • char(n):如果存储汉字长度就是4n字节；

    • varchar(n):如果存汉字则长度是4n+2字节，其中+2用来存储字符串的长度(因为varchar是变长字符串)；

• 数值类型：

  • tinyint：1字节

  • smallint：2字节

  • int：4字节

  • bigint：8字节

• 时间类型：

  • date：3字节

  • timestamp：4字节

  • datetime：8字节

• 如果字段允许为null，需要1个字节记录是否为NULL；

note：索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引；

2.3.8 ref

显示在key列记录的索引中，表查所用到的列或常量，常见的有：const(常量)、字段名;

2.3.9 rows

mysql预估要读取并检查的行数，注意这个不是结果集中的行数；

2.3.10 Extra

展示额外信息

1）Using Index

使用覆盖索引，explain执行计划结果里的key有使用索引；

• 如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是使用到了覆盖索引，此时extra中一般会包含using index；
• 覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键然后回表查询其他字段；

explain select id,shop_id from mall_order;

2）Using where

使用where语句来处理结果，并且查询的列没有被索引覆盖；

explain select * from mall_order where order_no = '0000';

3）Using index condition

查询的列不完全被索引覆盖，where条件中是一个前导列的范围； 

explain select * from mall_order where shop_id >20;

4）Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是需要要进行优化的，首先想到需要用索引来优化；

explain select distinct order_no from mall_order;

5）Using filesort

使用外部排序而不会使用内存排序；

• 当数据量较小时在内存中排序，否则需要使用磁盘进行排序。此时需要考虑使用索引优化； 

explain select * from mall_order order by order_no;

6）Select tables optimized away

 使用聚合(例如min() max())函数访问索引中的某个字段

explain select min(shop_id) from mall_order;