慢sql的排查和优化

第一步，排查：mysql开启慢日志查询

SET GLOBAL slow_query_log = ON; —开启慢日志查询

SET GLOBAL long_query_time = xxx; —执行超过多久就记录下来

第二步，分析：EXPLAIN执行计划

如果有追问explain怎么看：（说重要的字段）

• type字段（重点），（如果问到题目：explain的字段怎么看，可以说下面的白点）
  • const只有一行匹配
  • eq_ref对于前表的每一行，此表只有一行被读取
  • ref使用非唯一索引或者唯一索引的前缀匹配，可能返回多行
  • range给定范围的多行
  • index全索引扫描，与全表扫描类似，但是扫描的是索引树
  • all全表扫描
• key（重点），实际使用到的索引，如果没有使用到的索引，则为null
• key_len，实际使用的索引的长度，字节数，通常用来看，使用联合索引的时候，使用了几个字段
• rows，在执行这个sql的时候扫描的行数，如果过于大，就要检查有没有合适的索引，有没有索引失效，也可能是因为深度分页问题导致扫描的行数过于大。
• filtered（重点），表明存储引擎返回的数据经过where过滤之后，剩余结果占过滤前的百分比。越大越好，100说明，返回数据都是想要的。
• Extra（重点），一些其他说明，有些信息很重要，比如：（如果追问了这个字段，展开说下面的）
  • Using temporary：说明使用了临时表，通常是group by和order by使用的列不一样的情况下，很影响性能，需要调整group by和order by使用相同的索引列。
  • Using filesort：说明MySql无法使用索引完成排序操作，只能从内存或者磁盘进行排序，要优化索引让这个排序字段能走索引
  • Using index：覆盖索引。表示查询所需的所有列都在索引树中，不需要回表查询数据行。
  • Using index condition：索引下推，说明在索引层面进行了过滤。
  • Using where：表示 MySQL 服务器将在存储引擎检索行后，再进行 WHERE 条件过滤

SQL优化技巧

• 避免select * 这种，只查询需要的字段
• 方便使用索引覆盖的时候，使用索引覆盖，减少回表
• 利用索引完成排序和分组
• 尽量避免深度分页
• 避免join多张表

索引失效的场景

1. 类型不匹配，隐式类型转化这种
2. 对索引列使用了函数或者计算式，因为在索引层面是根据实际值进行排序的，如果计算或者操作了，优化器就不会采用索引
3. 左模糊查询，因为字符串类型的数据是按照从左到右的字符依次排序的，如果只清楚右边的结果，无法进行匹配
4. 使用复合索引的时候，违反了最左匹配原则，导致排序过程中数据的列是无序的，只有部分字段生效
5. 空值判断is not null，优化器会认为要走全表扫描
6. 逻辑运算or、not in、!=
7. 优化器经过分析，发现走索引反而更慢，不如全表扫描。

如果有个查询语句where a=1 and b=2 and c=3，会怎么设计这个索引？（考索引设计）

字段顺序：

什么是区分度：值的种类越多，区分度越高，比如性别，只有男女或未知，就三个状态，这种就是区分度低，如果像猫的种类，有狸花猫、橘猫、布偶猫、等等等，非常多种类，这种就是区分度高。

先分析区分度，区分度使用COUNT(DISTINCT a)/COUNT(*) 计算，这个值越高越应该放在前面，能保证扫描的数据更少，查询效率更快。

覆盖索引：

设计的时候，如果select的字段里面有abcd，更适合加上一个d，如此可以查询的时候，走索引覆盖，直接省了回表的操作。

通过最左匹配原则共用索引：

如果a区分度差别不是很大的情况下，然后又有一个语句where b = 2 and c = 3，可以考虑建一个索引（b，c，a），这样这个where b = 2 and c= 3和这个where a=1 and b=2 and c=3就可以共用这一个联合索引，避免索引创建过多。

如果有个查询语句where a=1 and b>2 and c=3，这个联合索引会走联合索引（a，b，c）的几个字段？

先说结果，两个。

c=3要走索引的话，要在一个有序的情况下才能保证。联合索引里面，后面的字段有序要保证在前一个字段相等的情况下才能利用索引的有序进行排列。

分析一下这个语句的执行：

a=1的情况下，b保证了前一个字段a是一样的，所以此时的b有序，大于二可以走索引。

但是b此时有很多值，此时c的排序并非有序

示例（不用答这玩意儿，放出来理解一下）可以看出此时的c是无序的，实际上会filesort

b c

3 3

3 4

3 5

4 1

4 2

4 5

innodb索引为什么要使用b+树？为什么不用b树，b树和b+树有啥区别（说b+树，说两种数据结构在作为索引的情况下的优缺点）

可以从以下几个方面：

结构差异

特性	B-Tree	B+Tree
数据存储位置	非叶子节点和叶子节点都存储数据。	只有叶子节点存储数据，非叶子节点只存储索引（键值）。
叶子节点结构	叶子节点之间无链表连接。	叶子节点之间通过双向链表连接，形成有序链表。

范围查询

b树查询一个范围的时候，因为非叶子节点也存了数据，可能查的时候要在不同层级之间跳转扫描，效率低。

b+树所有叶子节点之间通过双向链表链接，查询的时候只要找到范围的左边开始向后扫描即可。

I/O

b树因为每个节点都要存储实际数据，所以导致每个节点能存储的键值信息也要更少，导致扇出要更少，导致树的高度要更高，io次数更多

b+树非叶子节点只存储索引键信息，用作目录使用，所以可以存储大量的键值，扇出也更大，同样的数据量，b+树的高度会比b树更低。

维护成本

b树如果插入了新的数据，需要分裂的时候，可能会上溢，父节点那层可能因为新的分裂有引发了更高一级的分裂，可能会引起连锁分裂。重组可能会导致整个树中的很大一部分要做变动。

加上因为非叶子节点存储了实际数据导致节点占用空间大，导致更容易填满导致分裂重组，重组相比更加频繁。

频繁+变动可能巨大，导致b树维护成本更大。

b+树在发生页分裂和页合并的时候，因为只有叶子节点存数据，只会影响当前叶子页和其父索引页，维护成本更低。

联合索引的叶子节点存的数据是什么样的？

以（a，b，c）为例，叶子节点存储的是a，b，c这三个字段值，加上这个记录的主键。

顺序：先a有序排列，当a相同的情况下，b是有序排列的，b相同的情况下，c是有序的，以此类推。

当需要通过这个联合索引去查询其他更多字段的时候，即不走索引覆盖，就会使用这个主键，进行回表查询

count（*）、count（1）、count（列名）、count（主键字段），说一下区别，哪个性能更好？

COUNT(*) = COUNT(1) > COUNT(主键) > COUNT(列名)

count（*）和count（1）是一样的，优化器都是一样的处理，通常是最小的二级索引

count（）和count（1）都是只在乎行数，不在乎值，而count（主键）多了一个取值操作，但是影响很小，所以说count（）和count（1）只是略好一点

count（列名）一定要建立一个这个列名对应的索引，否则全表扫描，然后这个扫描的列，要多一个非空判断，不会去记录值为空的记录，因为这个非空判断，会慢于前三者。