MySQL查询性能的优化涉及多个方面，其中包括库表结构、建立合理的索引、设计合理的查询。库表结构包括如何设计表之间的关联、表字段的数据类型等。这需要依据具体的场景进行设计。如下我们从数据库的索引和查询语句的设计两个角度介绍如何提高MySQL查询性能。
数据库索引
索引是存储引擎中用于快速找到记录的一种数据结构。索引有多种分类方式:
- 按照存储方式可以分为：聚簇索引和非聚簇索引
- 按照数据的唯一性可以分为：唯一索引和非唯一索引
- 按照列个数可以分为：单列索引和多列索引等。
索引也有多种类型：B-Tree索引、Hash索引、空间数据索引（R-Tree）、全文索引等。
B-Tree索引
在利用B-Tree索引进行查询的过程中，有几点注意事项，我们以表A进行说明。其中表A的定义如下：
1 2 3 4 5 6 7 8
| CREATE TABLE A( id INT AUTO_INCREMENT PRIMARY KEY, name varchar(10), age TINYINT, sex enum('男','女'), birth DATETIME, key(name,age,sex) ) ENGINE = INNODB;
|
id为主键，并在name,age,sex列上建立了索引
- 全值匹配：指和索引中的所有列进行匹配，例如查找name=’Jone’ and age=13 and sex=’男’的人
- 匹配最左前缀：指用索引的第一列name，如where name=’Jone’，该查询只使用了索引的第一列
- 匹配列前缀：匹配索引列值的开头，如where name like ‘J%’，查找名字以J开头的人
- 匹配范围值：例如查找年龄在10-30之间的Jone，where name=’Jone’ and age between 10 and 30
- 只访问索引的查询：如果在select中选择的字段都是索引中的字段，那么就不需要访问数据行，从而提高查询速度
- 如果不是按照索引的最左列进行查找，则无法使用索引，如当仅查找表A中年龄为15岁的人时则无法使用索引
- 不能跳过索引中的列，如查找表A中名字为Jone且为男性的人，则索引只能使用name列，无法使用sex列
- 查询中索引的某列是范围查询，则该列后的查询条件将不能使用索引。
Hash索引与B-Tree的区别:
- Hash索引指包含哈希值（根据key中的列计算）和行指针，而B-Tree存储的是列值。所以Hash不能使用索引来避免读取数据行
- Hash索引数据不是按照索引值顺序存储的，所以无法用于排序；
- Hash索引不支持部分索引列匹配查找，因为Hash值是根据索引中的全部列计算出来的；
- Hash索引只支持等值比较查询，包括=、in、<=>。不支持范围查询。
索引的优点
索引不仅仅可以让服务器快速定位到表的指定位置，而且还有以下优点：
- B-Tree索引按照列的顺序存储数据，所以可以用来做Order by和group by操作，避免排序和临时表
- B-Tree索引中存储索引列的值，所以当select的值在索引中时，可以避免访问数据行
- 索引可以有效减少服务器扫描的数据量。
高性能的索引策略
正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引以及对应的优缺点。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化，有些则是针对特定行为的优化。
一、独立的列
指索引不能是表达式的一部分，也不能是函数的参数。如：select * from A where id+1=5; 则无法使用主键索引
二、前缀索引和索引选择性
有时需要索引很长的字符串，索引会占用很大的空间，通常可以索引开始的部分字符来节约索引空间，提高索引效率，但也会降低索引的选择性。索引的选择性=不重复索引值/数据表的记录总数。索引的选择性越高查询效率越高.
三、多列索引
首先需要说明在多列上创建索引不等同于给这些列的每一列单独建立索引。当执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。即使是限制最严格的单列索引，它的限制能力也肯定远远低于这三个列上的多列索引。
比如我们想查询表A中id为3或者名字首字母为A的人，sql语句的两种写法对比，其中第二种写法比第一种减少对表的扫描次数：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| mysql> explain extended select id from A where id=3 or name like 'A%'; +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | A | NULL | index | PRIMARY,name | name | 37 | NULL | 3 | 55.56 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select id from A where id=3 union all select id from A where name like 'A%' and id<>3; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | A | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 2 | UNION | A | NULL | index | PRIMARY,name | name | 37 | NULL | 3 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 2 warnings (0.00 sec)
|
多列索引中索引列的顺序也十分重要，在设计索引的顺序时也需要考虑如何更好地满足排序和分组的需要（B-Tree）。
在一个多列的B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列等等。
确定索引列的顺序有一个经验法则：将选择性最高的列放到索引最前列， 当然如果需要考虑对表的排序的情况就需要另当考虑了。
四、聚簇索引
不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于其实现方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行，一个表只能有一个聚簇索引(primary key)。
聚簇的优点：
- 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能够获取某个用户的全部邮件。如果没有聚簇索引，则每封邮件都可能导致一次磁盘I/O；
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此聚簇索引中获取数据通常比在非聚簇索引中查找要快；
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值；
聚簇的缺点：
- B-Tree索引插入速度严重依赖于插入顺序。按照聚簇索引列中值的顺序插入是加载数据到InnoDB表中速度最快的；
- 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
- 被插入的新行在移动时，可能面临“页分裂”的问题。页分裂问题是聚簇索引要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，也就是一次页分裂操作，导致表占用更多的磁盘空间；
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
五、覆盖索引
大家都会根据where的条件建立合适的索引，这只是索引优化的一个方面。优秀的索引还应该考虑整个查询。MySQL可以使用索引直接获取列的数据，这样就不需要读取数据行了。如果索引包含（覆盖）所有需要查询的字段值，我们就称之为覆盖索引。当查询是一个索引覆盖查询时，Extra列可以看到Using index的信息。
当然覆盖查询还是有很多陷阱可能导致无法实现优化的。MySQL查询优化器会在执行查询前判断是否有一个索引能够进行覆盖，覆盖where条件中的字段和select的字段。如果不能覆盖，则还是需要扫描数据行。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| mysql> explain extended select * from A where name like '%v%'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | A | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select * from A inner join (select id from A where name like '%v%') as B on A.id=B.id; +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+ | 1 | SIMPLE | A | NULL | index | PRIMARY | name | 37 | NULL | 3 | 33.33 | Using where; Using index | | 1 | SIMPLE | A | NULL | eq_ref | PRIMARY | PRIMARY | 4 | index_q.A.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+--------------------------+ 2 rows in set, 2 warnings (0.00 sec)
|
因为InnoDB表中非聚簇索引中存储主键值，所以我们先根据条件获取主键值，然后再根据主键值进行查询，这种方式叫做延迟关联。
六、使用索引扫描来做排序
如果EXPLAIN出来的type列值为index，说明MySQL使用了索引扫描来做排序。扫描索引本身是很快的，但是如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的行。这基本都是随机I/O，因此按索引顺序读取的速度通常要比顺序地全表扫描慢，尤其是I/O密集型的工作负载时。因此MySQL设计索引时应尽可能的满足排序和查找。只有索引列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一致时，MySQL才能使用索引来对结果做排序。如果查询关联多张表，则只有order by子句引用的字段全部为第一个表时，才能使用索引排序。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| mysql> explain extended select * from A order by id; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | A | NULL | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> explain extended select * from A order by name; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | A | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 2 warnings (0.00 sec)
|
如上是分别使用主键id排序和name排序的查询，可以看出使用id排序的查询使用了索引排序，而name排序的查询使用的是filesort。
总结
总的来说编写查询语句时，应尽可能选择合适的索引以避免单行查找，尽可能的使用原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。我们通过响应时间来对查询进行分析，找出消耗时间最长的查询或者给服务器带来压力最大的查询，然后检查查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用了随机I/O访问数据，或者太多回表查询哪些不在索引中的列的操作。