性能调优知识点（mysql）二

** 调优工具**
A.Explain
查看sql执行计划

各列详解
mysql5.7及之后的版本无需加extended会自动展示partitions和filtered两列

explain紧随其后通过 show warnings 命令可
以得到优化后的查询语句，从而看出优化器优化了什么。
explain extended select * from eme_task_info LEFT JOIN jcwb_dayplan on 1=1 LEFT JOIN jcw_jdl_wl_record on 1=1;
show warnings;

//rows: mysql估计要读取并检测的行数，为一个估算值，该值越小越好
//filtered: 该操作返回的结果集行数占检测行数的百分比，为一个估算值，值越大越好
//rows * filtered / 100 = 实际返回的结果集行数，为一个估算值，该值越小越好
//partitions : 表示查询语句涉及的分区数。如果查询语句使用了分区表的一个分区，则该列的值为1。如果查询语句使用了分区表的多个分区，则该列的值为涉及的分区数。
//由于mysql是支持多线程并发操作的，如果用hashmap来存储的话就会有并发问题，所以为了解决这个问题，就需要对整个hashmap加锁（相当于使用hashtable），但是对这个hashmap加锁会影响性能，所以mysql5.7以后就引入了分区的概念，原来只有一个hashmap,5.7以后分为了8个hashmap。每个索引都绑定到一个特定的分区，每个分区都由一个单独的 latch 锁保护。相当于将索引分散存储，减小(减小而不是去除)加锁带来性能的影响。分区大小可以设置，默认情况下设置为8，最大设置为512。

id列
id列的编号是 select 的序列号，有几个 select 就有几个id。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
left join的两张表id是一样的，即他们是同一层级，子表的id才是不同级，子表id更大。具体看有道笔记。

select_type列
（ 表示对应行是简单还是复杂的查询）
表示对应行是简单还是复杂的查询。
主要包括以下几种，但不只以下几种，还会有其它的。具体查看官方文档。
1.simple 简单查询。只有一个select语句
2.primary 复杂查询。最外层的select
3.subquery表示子查询，from的子句不算。
4.derived。from后面的子查询语句，叫衍生查询或派生查询

table列
（表示 explain 的一行正在访问哪个表）
这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查 询。 当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。
derived3表示id为3的select

type列
（如何查找表中的行，索引、全表还是其他）
主要包括以下几种，但不只以下几种，还会有其它的。具体查看官方文档。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
a.NULL
（不需要访问表，在优化节点在索引列中获取）
mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引,但很少有这么简单的sql。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
1 mysql> explain select min(id) from film;
b.const
(表示通过索引一次就找到了)
表中只有一条数据匹配。比如id=1。如sql select * from film where id = 1。就像读取常量一样。
c.system
(表只有一行记录)
表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个系统表只有一行记录，所以可以忽略。
查询效率更高，比const高。
d.eq_ref
（表示使用主键索引/唯一索引，且最多只返回一条数据）
primary key主键索引或 unique key唯一 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记。
e.ref
(表示使用普通索引，或者唯一索引的部分前缀)
表示使用普通索引，或者唯一索引的部分前缀，索引要和某个值相比较，可能会找
到多个符合条件的行，所以会比eq_ref效率低点。
普通索引，比如简单 select 查询，name是普通索引。比如sql
explain select * from film where name = ‘film1’;
唯一索引的部分前缀，如关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。。比如sql
explain select film_id from film left join film_actor on film.id = film_actor.film_id;
f.range
范围查找，范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
g.index
（需要扫描全部索引才能获取到数据，index会从二级索引的叶子节点开始遍历，而不是从根节点开始遍历，速度比较慢）
扫描全索引就能拿到结果(即只需要查找所有就能拿到结构，只不过是需要扫描所有所有才能拿到)，一般是扫描某个二级索引（非主键索引，不是联合索引），这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
index虽然用到索引，但是效率不高，因为会遍历索引中的所有值，而且索引也是存在磁盘中的。
h.ALL
(会扫描聚簇索引所有叶子节点)
即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。扫描二级索引比全表扫描(扫描聚簇索引)效率高点。

key列
（实际采用哪个索引来优化对该表的访问）
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

possible_keys列
（可能使用哪些索引来查找）
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key_len列
（可以看出使用索引的哪些列）
a.这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通
过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找
b.key_len计算规则如下：
1.字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
varchar是变长字符串
2.数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
3.时间类型
date：3字节
timestamp：4字节
datetime：8字节
4.如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索
引。
c.上面说了varchar key_len=3n+2,这只是针对字段的字符集为utf8mb3的时候，表示一个汉字占3个字节。
但是当字段字符集为utf8mb4的时候，一个汉字占用4个字节，此时key_len=50*4+2=202

ref列
（使用到的索引列或常量，具体看笔记）
a.这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量）或者字段名（例：film.id）
下面igmc ref用到的是const
select * from im_group_msg_content igmc
where igmc.CREATE_BY = '12’and igmc.sender_id=‘12’
b.下面igmc因为使用的了like，为范围range索引，所以ref为null。
ig用到igmc.id=ig.git,所以ref为字段名
select * from im_group_msg_content igmc
left join im_group ig on igmc.gid=ig.gid
where igmc.CREATE_BY like '12’and igmc.sender_id=‘12’

rows列（估计要扫描的行数）
不是结果集里的行数count,是预估扫描的行数，具体要看情况。

Extra列（展示的额外信息）
a. Using index(使用覆盖索引)
表示sql查询语句使用的是覆盖索引
b.Using where（没走索引，需要优化）
普通的查询，使用 where 语句来处理结果
c.Using index condition（不完全被索引覆盖，查询的是范围值）
查询的列不完全被索引覆盖，where条件是一个范围而不是具体值
比如select * from film_actor where film_id > 1;
d.Using temporary（表示会创建临时表处理查询，需要优化）
需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
比如
1.actor表name是没有建索引的，此时创建了张临时表来distinct，所以查询的时候，会将所有字段查询出来，放到临时表，然后筛选出name列所有值。
mysql> explain select distinct name from actor;
2.film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
mysql> explain select distinct name from film;
e.Using filesort（不走索引，通过外部进行排序，需要优化）
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
外部排序比如内存、磁盘
f.Select tables optimized away(使用聚合函数如max、min在索引中查询，这种效率高，无需优化)
使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。这种效率高，无需优
化。

B.Trace
mysql最终如何选择索引，我们可以用trace工具来查看。
开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭