Mysql系列-索引优化

立聪堂德州十三局店 · 5 小时前

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有使用索引；