​浅谈mySql优化的二三事

1. 索引

1）分类

• 全文索引：倒排索引

• 哈希索引：哈希表

• B+树索引：B+树
  InnerDb中主键索引叶子节点直接存储数据，其他索引叶子节点存储的是主键值，需要再次查找一遍主键索引所在B+树才能找到所查询的数据；而MyISAM所有索引叶子节点都是存储的数据指针。
  
  
  

  InnerDb中主键索引数据存储示意图
  

  
  
  
  InnerDb中非主键索引存储示意图
  

  
  
  
  MyISAM中索引数据存储示意图
  

  

2）适用情况：

• 全值匹配。

• 匹配左边的列。

• 匹配范围值。

• 精确匹配某一列并范围匹配另外一列。

• 用于排序。

• 用于分组。

3）注意事项：

• 只为用于搜索、排序或分组的列创建索引。

• 为列的基数大的列创建索引。

• 索引列的类型尽量小。

• 可以只对字符串值的前缀建立索引。

• 只有索引列在比较表达式中单独出现才可以适用索引。

• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。

• 定位并删除表中的重复和冗余索引。

• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

• B+树索引在空间和时间上都有代价。

2. 分析一个单表查询

1) const

SELECT * FROM single_table WHERE id = 1438;

等值比较，结果唯一，主键列，唯一二级索引列和一个常数进行等值比较时（如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效）

2) ref

SELECT * FROM single_table WHERE key1 = 'abc';

二级索引等值比较，多个匹配结果

3) ref_or_null

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

二级索引等值和null两个范围，多个匹配结果

4) range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) 
OR 
(key2 >= 38 AND key2 <= 79);

索引列范围匹配

5) index

SELECT key_part1, key_part2, key_part3 FROM single_table
WHERE key_part2 = 'abc';

遍历二级联合索引，不用回表

6) all

全表扫描

3. 查询过程分析

1) 多个索引：

1)读取一个索引，回表，过滤其他条件
2)读取多个索引，对主键进行取交集（产生多个结果区间，每个区间的主键是排序的才会执行），并集等操作，再回表

2) 索引合并（联合索引的话必须每列都在where中都出现才会用到，如果不是不会使用）：

①Intersection合并（交集合并）

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

②Union合并（并集合并）

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

③Sort-Union合并（没有sort-交集）

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

先查key1区间，主键排序；再查key3区间，主键排序。—->再回表

3）表连接：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2
 AND t2.n2 < 'd';

1）确定驱动表t1
2）根据条件查询驱动表t1数据
3）根据驱动表查询结果集，分别匹配查询被驱动表t2的数据

4）内连接：

驱动表中的记录在被驱动表中找不到匹配的记录，该记录会不会加入到最后的结果集

5）多个表：

前面的查询结果集就是新的驱动表

6）基于块的嵌套循环连接（Block Nested-Loop Join）：

引入了join buffer；在 2）过程的时候，驱动表结果集会放入join buffer，以减少访问被驱动表的次数

4. 表执行顺序

select  distinct 字段名1,字段名2，[fun(字段名)]  
from 表1  
<join类型>join 表2 on <join条件> 
where <where条件>
group by <字段>  with <cube|rollup> 
having <having条件> 
order by <排序字段> 
limit <起始偏移量,行数>

执行顺序：
（1）from 表1
（2）on 
（3）join 表2
（4）where 
（5）group by <字段>
（6）with 
（7）[fun(字段名)]
（8）having 
（9）select
（10）distinct 字段名1,字段名2，
（11）order by <排序字段>
（12）limit <起始偏移量,行数>

5. 子查询过程分析

1）子查询分类：

①标量子查询：只返回一个单一值的子查询
②行子查询：就是返回一条记录的子查询
③列子查询：查询出一个列的数据喽，不过这个列的数据需要包含多条记录
④表子查询：查询的结果既包含很多条记录，又包含很多个列
⑤不相关子查询：子查询可以单独运行出结果
⑥相关子查询：子查询的执行需要依赖于外层查询的值

SELECT (SELECT m1 FROM t1 LIMIT 1);
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;

2）不相关子查询查询过程：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

①先单独执行(SELECT common_field FROM s2 WHERE key3 = ‘a’ LIMIT 1)这个子查询。
②然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = …。

3）相关子查询查询过程：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

①先从外层查询中获取一条记录，也就是先从s1表中获取一条记录。
②然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询。

6. 优化器对子查询的优化

1）Table pullout （子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');

key2列是s2表的唯一二级索引列，所以我们可以直接把s2表上拉到外层查询的FROM子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

2）DuplicateWeedout execution strategy （重复值消除）

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

在执行连接查询的过程中，每当某条s1表中的记录要加入结果集时，就首先把这条记录的id值加入到一个临时表（id是主键）里，
如果之前这条s1表中的记录已经加入过最终的结果集，这里直接把它丢弃，
这种使用临时表消除semi-join（右表只用于过滤左表的数据而不出现在结果集中）结果集中的重复值的方式称之为DuplicateWeedout。

3）LooseScan execution strategy （松散扫描）

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

如果将s2作为驱动表执行查询的话，当s2中存在相同的值，只取值相同的记录的第一条去做匹配操作的方式称之为松散扫描

4）Semi-join Materialization execution strategy

先把外层查询的IN子句中的不相关子查询进行物化（转成临时表），然后再进行外层查询的表和物化表的连接本质上也算是一种semi-join，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询。

5）FirstMatch execution strategy （首次匹配）

先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，
如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，
如果找不到则把该外层查询的记录丢弃掉；
然后再开始取下一条外层查询中的记录，重复上边这个过程。

7. sql中的in与not in,exists与not exists的区别以及性能分析

1) 如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。
2) in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in。

8. count(1)，count(*)，count(字段)性能分析

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL 。
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL 。
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

1）MyISAM引擎：

针对count() 的操作，mysql自己了一个优化，类似于维护一份元数据信息，专门用来记录表的行数，这样每当有count()查询的时候就直接返回这个维护好的值，不需要再扫描全表了。所以它是一个O(1)复杂度的操作。

2）InnoDB引擎：

持事务支持行级锁变化很快而不可维护，所以针对count(*)的操作不得不扫描全表以返回一个准确的结果。这是一个O(n)复杂度的操作。所以在具体执行的过程如果有非聚簇索引的话mysql会自动选择在非聚簇索引的列上做统计，这样就能提高查询的速度。

9. 分页查询LIMIT优化：

SELECT * FROM 表名称 LIMIT M, N;

适用于数据量较少的情况(百/千级)查询的时候会先找出N条结果集，Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.

1) 缩短分页范围：

SELECT * FROM 表名称 WHERE ... AND id > (?) ORDER BY id ASC LIMIT M

2) 利用二级索引，先查找出数据的id，再查完整数据：

SELECT * FROM 表名称 WHERE id IN
(SELECT id FROM 表名称 WHERE ... LIMIT M, N )

10. union和union all：

union对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

11. 业务上优化

子查询嵌套过多，考虑先创建临时表，把子查询数据（去重）导进入表中，建立相关索引，再进行连表查询连表过多的业务，sql中不一次性全查询出来，在java代码中进行循环查询

12. 删除优化

1) 删除时，使用limit字段，避免一次性删除数据过多导致mysql负载崩溃

DELETE FROM xquark_order_item WHERE id <= '666666666678844' 
AND id >= '96'  ORDER BY id ASC LIMIT 1000000;

2) mysql中带where条件的删除并不是真的删除，而是把记录加上删除标识，表数据占有空间并不会变小

OPTIMIZE TABLE order;

对于InnoDB表不支持optimize操作，提示“Table does not support optimize, doing recreate + analyze instead”，可以通过以下命令来替代：

ALTER TABLE order TYPE = innodb;

‍