MySQL千万级别优化·中 - HelloWorld开发者社区

MySQL 千万级别的查询优化手段·中

单列索引（假设在 v_record 表中存在 id 列的索引）

1、WHERE 条件使用

EXPLAIN SELECT * FROM v_record WHERE id = 2

结论：利用索引进行回表查询

2、SELECT 字段使用

SELECT 字段使用索引列的话，结果就是索引覆盖查询，EXPLAIN结果中Extra列的结果就是Using index

如：

EXPLAIN SELECT id FROM v_record WHERE id = 2

结论：直接通过索引信息返回所需信息，不需要回表

3、GROUP BY 条件

EXPLAIN SELECT id FROM v_record GROUP BY id

结论：记住其实这样的情况下，总体的查询性能怎么样，需要结合 EXPLAIN 的结果分析，如果 EXPLAIN 中的 rows 列结果很大，总体性能耗时也是一个不可以接受的问题，可以利用 LIMIT 关键字进行处理

4、ORDER BY 条件

EXPLAIN SELECT id FROM v_record ORDER BY id DESC

结论：必须谨记，MySQL中 GROUP BY 结果中已经隐藏的进行了一次 ORDER BY 操作，如果当前的 SELECT 语句中，没有ORDER 需求的话，那么可以显示指定 ORDER BY NULL进行禁用自动的GROUP BY 的ORDER BY 操作

5、SELECT 、WHERE、GROUP BY、ORDER BY混合情况

EXPLAIN SELECT id FROM v_record WHERE id = 2 GROUP BY id LIMIT 10

结论：通过索引查询索引的信息然后通过索引进行分组，这个具体的耗时需要根据等待的分组的信息的大小进行确定，在CMS中使用的很多，一般一个分页的数据在30~50左右，这样的话，整体的性能实在可以接受的范围

多列索引【假设在 v_record 表中有一个主键 id，复合索引(vote_id, group_id)】

1、索引的列的选择性

索引的列的选择性的高低决定了复合索引的顺序，经验法则就是：选择性高的放在前面，具体的选择性的高低判断是通过去重后的数据集与总数据量的倍数决定的，因为重复的值越多，选择性就更低这样的列一般不建议建立索引，一定要建立的话，可以建立在复合索引的后面，如果是单利索引的话，当前列的重复值已经超过总数据量的70%左右我们不建议建立索引，因为就算是大表的话，索引的性能也会很低，特大的表就算是索引性能也低，建议分区或者分布式部署数据库。

2、最左前缀原则以及适应的条件

最左前缀原则意思就是MySQL的索引的顺序是按照建立索引的顺序进行数据的索引的，如果在底层需要利用好建立的索引的话，那么就一定让MySQL能够分析出可以利用最左前缀原则的索引，如果MySQL优化引擎觉得不能利用索引的话，那么建立的复合多列索引就不会生效！如下两个示例：

EXPLAIN SELECT * FROM v_record WHERE group_id = 2 AND vote_id = 10

EXPLAIN SELECT * FROM v_record WHERE vote_id = 10 AND group_id = 10

上面的这条语句就可以利用复合索引 (vote_id, group_id)，但是下面的这条语句就不会利用复合索引而不论调整两列的顺序；这个是因为根据语句就是要去查询 group_id 等于 2 或者 vote_id 等于 10的结果集合，但是我们知道在 vote_id 上我们可以使用复合索引 (vote_id, group_id) 生效，但是对于 group_id 而言，没有任何一个索引可以生效，索引整个语句索引就不会生效，解决方案就是在 group_id 上建立一个索引就可以解决问题：

EXPLAIN SELECT * FROM v_record WHERE group_id = 2 OR vote_id = 10

3、WHERE 条件

EXPLAIN SELECT * FROM v_record = 2 AND group_id = 2(复合索引生效)

EXPLAIN SELECT * FROM group_id = 2 AND v_record = 2(复合索引生效)

EXPLAIN SELECT * FROM v_record = 2 OR group_id = 2(索引不生效)

结论：建议整体的语句使用 AND 进行连接，OR条件可以转换为 IN 的情况，可以优化为IN或者AND进行处理。善于使用让引擎容易理解的方式使用索引处理查询

4、GROUP BY 条件

EXPLAIN SELECT * FROM v_record WHERE vote_id = 2 GROUP BY group_id（紧凑索引扫描）

EXPLAIN SELECT * FROM v_record GROUP BY vote_id (松散索引扫描)

结论：利用好复合索引，紧凑索引、松散索引

5、ORDER BY 条件

EXPLAIN SELECT * FROM v_record WHERE vote_id = 2 ORDER BY group_id

结论：利用好复合索引

6、索引覆盖

索引覆盖含义就是查询的列就是索引列的一部分或者全部列或者聚簇索引列，COUNT(*) 等的时候，这样的就可以避免让引擎进行回表查询数据行的数据并拼接后返回。

多列搜索

在CMS系统或者报表系统中，我们一般会利用多列进行条件过滤找到自己需要的列，这样的话，建议使用好复合索引，将常用的列放入复合索引的各个索引中，可以利用好IN关键字，一般如果搜索条件存在区间或者前缀模糊搜索的话，建议放在复合索引列的最后，避免后续的列无法利用索引。

LIMIT 建议

如果当前的数据偏移量过大的话，使用 LIMIT 这个是一个特大的性能问题，无论怎么优化都是一个问题，这样的话，我们建议数据进行灰暗删除，不进行真正的删除，使用标识位处理即可，这样利用表的主键进行偏移或者索引列进行偏移，这样对于千万级别的表而言，性能不是问题。

MySQL的优化策略

1、在条件中如果是AND连接的话，至于具体的条件的顺序不重要，MySQL会自动的利用上索引，如果是复合索引的话，索引的列在SQL的书写上不需要满足最左前缀原则，MySQL自动从最左原则的索引开始自动匹配

2、如果是OR条件的话，MySQL会自动的判断各列是否有索引，如果存在的话，那么MySQL会自动的在各列上使用索引查询，然后使用 UNION操作合并结果集

分析SQL善于利用 EXPLAIN 关键字

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

SIMPLE

v_record

const

PRIMARY

const

100

一个 EXPLIAN 结果中重要查看第一： select_type、type、key、ref、rows、Extra列的结果

select_type列可以查出当前的查询的类型，当前的是简单的查询

type: 看出结果类型，是NULL、const等

key：当前使用的索引列

rows：结果集的数据量

Extra：额外的信息，这个取决于MySQL服务器层面，上面都是InnoDB存储引擎级别的分析结果

MySQL中的InnoDB的索引特性分析

MySQL中InnoDB引擎分为聚簇索引、二级索引，InnoDB的数据是根据聚簇索引进行B+·Tree存储的，每个二级索引中都包含当前的聚簇索引列的值，因为在InnoDB中聚簇索引的值每列都是唯一的。因此可以通过o(1)查询出结果，查询性能极高的就是利用聚簇索引进行查询，而且二级索引的数据查找也是通过聚簇索引的，因此我们经常听到，二级索引需要二次查找：具体的原因就是二级索引就是利用的聚簇索引值进行查找的。