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; https://i-blog.csdnimg.cn/direct/ae91e8feab474b8fbe19e0d87577bd41.png
https://i-blog.csdnimg.cn/direct/28d4e3bfcd414432aa9581a7760bb15c.png
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
https://i-blog.csdnimg.cn/direct/0facd6ee5e7c4d29a7b57c9787641338.png
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 ; https://i-blog.csdnimg.cn/direct/1fb5742b1bd44a02931ecd0fbbc5119b.png
5）union：在union中的第二个和随后的select
explain select * from mall_order where id = 1 union select * from mall_order where id = 2;
https://i-blog.csdnimg.cn/direct/f86f35ba7e884b46bad269d9b613219a.png
2.3.3 table
table列表现正在访问哪个表。
[*]当from子句中有子查询时，table列的值<derivenN>，表现当前查询依靠id=N的查询，因此先执行id=N的查询；
[*]当有union时，UNION RESULT的table列的值为<union1,2>,其中1和2表现到场union的select行id；
https://i-blog.csdnimg.cn/direct/b0d274be1129462482be05e59c2095ae.png
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;
https://i-blog.csdnimg.cn/direct/ee55731b09c14e29a541bf79b72f8e14.png
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; https://i-blog.csdnimg.cn/direct/9d550b193476495e87f4dd848b176c03.png
set session optimizer_switch='derived_merge=off'; -- 5.7起mysql对衍生表进行了合并优化，此处临时关闭
explain select * from (select * from mall_order where id = 1)t;show WARNINGS; https://i-blog.csdnimg.cn/direct/dbd5fb1d99c649b4bae04b8a4bd0c7d3.png
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
https://i-blog.csdnimg.cn/direct/8db0b1a9c0234dbb95ded4d864531edf.png
4）ref：不使用唯一索引，而是使用平常索引大概唯一索引的部分前缀，即索引要和某个值相比较，可能会找到多个符合条件的行；
4.1）简单select查询，name是平常索引(非唯一索引)
explain select * from mall_order where shop_id = 1;
https://i-blog.csdnimg.cn/direct/0183aabc63164ed1a51c222f01b2bc37.png
4.2）关联表查询
explain select * from mall_order mo left join mall_order_item oi on mo.id = oi.order_id
https://i-blog.csdnimg.cn/direct/b498ca181a8e4df9be0c4024d5ebfeaf.png
5）range
range是范围扫描，通常出现在in、between、>、<、>=等操作中，使用一个索引来检索给定范围的行；
explain select * from mall_order where shop_id >2;
https://i-blog.csdnimg.cn/direct/8e545b88946d4ed680635733155920d3.png
6）index
[*]扫描全索引就可以获取到的查询结果；一样平常是扫描某个二级索引；
[*]这种扫描不会从索引的根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的；
[*]通常这种查询使用覆盖索引(查询的列都在二级索引中)，由于二级索引比较小，所以通常比ALL快一些；
7）ALL
ALL即全表扫描，扫描聚簇索引的全部叶子节点，通常这种情况需要增长索引来优化；
explain select * from member_address; https://i-blog.csdnimg.cn/direct/ecbe280617484c0cb3d45cd1f9f7ad96.png
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; https://i-blog.csdnimg.cn/direct/484adc58a8dc4e07908ad035a8001117.png
2）Using where
使用where语句来处理结果，而且查询的列没有被索引覆盖；
explain select * from mall_order where order_no = '0000'; https://i-blog.csdnimg.cn/direct/5dd3500ffc1d4c0f8300d1c71e4f3e44.png
3）Using index condition
查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
explain select * from mall_order where shop_id >20;
https://i-blog.csdnimg.cn/direct/1cbaca8bfc7a4c78922323fd4f0387da.png
4）Using temporary
mysql需要创建一张临时表来处理查询。出现这种情况一样平常是需要要进行优化的，首先想到需要用索引来优化；
explain select distinct order_no from mall_order;
https://i-blog.csdnimg.cn/direct/832e6ad689484b099a0c7bbc894ed486.png
5）Using filesort
使用外部排序而不会使用内存排序；
[*]当数据量较小时在内存中排序，否则需要使用磁盘进行排序。此时需要思量使用索引优化；
explain select * from mall_order order by order_no;
https://i-blog.csdnimg.cn/direct/e87e6a858b694bdd95f816e7dace0b72.png6）Select tables optimized away
使用聚合(比方min() max())函数访问索引中的某个字段
explain select min(shop_id) from mall_order;
https://i-blog.csdnimg.cn/direct/061919c89012445f8e882586f8ab25c7.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]