思考并回答以下问题：
SQL语句优化的首要任务是了解SQL语句的频率，分析当前的应用是查询操作频繁还是更新操作频繁。一条频繁执行但是性能糟糕的SQL语句可能对整个应用产生严重的负面影响，例如消耗过多的服务器内存资源，消耗过多的网络带宽。
如果当前的应用更新操作较为频繁，需要解决更新操作带来的并发问题，例如锁等待、死锁问题。可以采用InnoDB存储引擎引入行级锁缩小锁的粒度，解决更新操作的并发问题。读者可以参考事务与锁机制章节的内容，这里不再赘述。
如果当前的应用查询操作较为频繁，此时需要借助explain命令分析select语句，了解select语句的执行情况，分析select语句的性能瓶颈，进而对select语句进行重新设计或者对表结构进行重新设计（例如添加索引或者拆分表），让查询优化器能够更好地工作，提升查询性能，这是本节着重讲解的内容。
了解SQL的执行频率
单个数据库用户对数据库中的少量数据进行操作，无法挖掘数据库的瓶颈。只有数据库服务器长时间运行，并且有批量用户进行频繁操作的时候，数据库服务器的性能瓶颈才会体现出来。
1．每秒查询率QPS
每秒查询率QPS（query per second）是指MySQL每秒执行的查询总量，在MySQL 5.1.30及以下版本可以通过Questions状态值每秒内的变化量来近似表示，而从MySQL 5.1.31开始，可以通过Queries状态值每秒内的变化量来近似表示。引入Queries的目的是解决Questions状态变量没有记录存储程序中执行的Query的问题。执行MySQL命令“show status like ‘questions’;”以及“show status like ‘queries’;”可以查看两个状态值，如图1所示。
图1 Queries与Questions
- Questions：MySQL服务执行的MySQL语句数量，仅仅包含MySQL客户机发送到MySQL服务器的MySQL语句，存储程序中的MySQL语句不包含在内。该状态变量不统计com_ping、com_statistics、com_stmt_prepare、com_stmt_close以及com_stmt_reset命令。
- Queries：MySQL服务执行的MySQL语句数量，包括存储程序中的MySQL语句。该状态变量不统计com_ping以及com_statistics命令。
使用MySQL命令“show global status like ‘Com_％’;”可以查看当前MySQL服务实例执行各种SQL语句的次数。由于该命令的统计信息比较多，这里不再一一罗列，下面仅列出几种典型的统计信息。
- Com_select：记录了执行select操作的次数。
- Com_insert：记录了执行insert操作的次数，对于批量插入的insert操作，只累加一次。
- Com_update：记录了执行update操作的次数。
- Com_delete：记录了执行delete操作的次数。
通过查看上面的统计信息，可以计算得出当前应用中数据更新语句与查询语句的大致比例，从而了解当前应用偏向于OLTP还是OLAP，以便对存储引擎进行适当调整。针对InnoDB表的更新操作，可以通过Com_commit和Com_rollback得到事务提交的次数以及回滚的次数，如果回滚频繁，就说明应用程序存在某些问题。
说明
Com_xxx计数器状态变量表示xxx类型语句执行的次数。
2．数据处理状态信息
执行了删除语句，删除语句影响的行数可能为0，使用MySQL命令“show status like ‘handler％’;”可以查看表数据的处理情况。
- Handler_delete：表示记录从表中删除的次数。Handler_delete与Com_delete不一样，只要执行delete操作，Com_delete状态值就会增加；只有从表中删除了记录的时候，Handler_delete状态值才会增加。
- Handler_commit：表示提交SQL语句数。
- Handler_rollback：表示rollback语句的执行次数。
- Handler_update：更新表的行数。
- Handler_write：向表插入记录的行数。
Handler_savepoint：在事务中放置保护点的次数。 - Handler_savepoint_rollback：在事务中回滚到保护点的次数。
3．索引使用情况
- Handler_read_first：表示索引中第一个键值被读的次数。值如果较高，说明执行了大量的全索引扫描select语句。例如select column_name from table，假设column_name列上有索引。
- Handler_read_key：表示根据索引读一行记录的请求数。值如果较高，说明 select 语句正在使用索引进行查询操作。
- Handler_read_next：表示按照索引的顺序读下一行记录的请求数，按索引的顺序读取数据。
- Handler_read_prev：表示按照索引的顺序读前一行记录的请求数，按索引的倒序读取数据，主要用于优化order by …… desc子句。
说明
Handler_xxx计数器状态变量表示xxx类型语句执行的次数或者对记录影响的行数。从MySQL5.6开始，新增了下面的数据处理状态信息。
- Handler_read_last：表示索引中最后一个键值被读的次数。对索引字段使用order by 子句时，首先发起第一个索引值的请求，然后是下一个索引值的请求。对索引字段使用order by…desc子句时，首先发起最后一个索引值的请求，然后是前一个索引值的请求。
- Handler_read_rnd：表示根据固定位置读一行记录的请求数。如果执行了大量需要对结果进行排序的查询，该值较高。该值较高，通常意味着很多查询扫描了整个表，或者表中没有创建合适的索引。
- Handler_read_rnd_next：表示读取数据文件中下一行记录的请求数。如果进行了大量的全表扫描，该值较高。该值较高，通常意味着表中没有创建合适的索引或者查询没有利用好已有的索引。
另外，使用MySQL 命令“show status like ‘Innodb_rows％’;”可以查看InnoDB表记录的查询、更新行数。
- Innodb_rows_read：从InnoDB表中读取的记录行数。
- Innodb_rows_inserted：向InnoDB表插入的记录行数。
- Innodb_rows_updated：更新InnoDB表的记录行数。
- Innodb_rows_deleted：从InnoDB表中删除的记录行数。
定位执行效率较低的SQL语句
分析Select_scan、Select_full_join、Select_full_range_join、Select_range_check、Handler_read_rnd以及Handler_read_rnd_next等状态信息，可以查看当前MySQL服务实例中select语句没有使用索引的次数。开启慢查询日志，可以在慢查询日志中找出超过某一个时间阈值（该值由 long_query_time参数设置）或者没有使用索引的SQL语句。如果这些SQL语句频繁执行，则可能对MySQL的性能造成负面影响。
慢查询日志只包含成功执行过的SQL语句，在查询语句执行期间，当系统性能出现问题时，使用慢查询日志并不能定位问题，可以使用MySQL命令“show processlist;”查看当前MySQL服务实例正在执行询日志并不能定位问题，可以使用MySQL命令“show processlist;”查看当前MySQL服务实例正在执行的线程，包括线程的状态（例如是否出现表锁现象等）。这样就可以实时地查看MySQL服务实例的执行情况，同时可以对一些表级锁操作进行适当优化。状态信息出现在Command列，一条查询从开始运行到运行结束，它的状态变化多次（共有12个状态）。这里仅罗列其中几个，如表1所示。
表1 线程的常用状态
另外，还可以使用MySQL命令“show status like ‘uptime’;”查询当前MySQL服务实例的工作时间，使用MySQL命令“show status like ‘slow_queries’;”查询当前MySQL服务实例执行的慢查询的次数等信息。
分析select语句
有的应用对select语句的性能要求较高，此时单纯依靠开发者的直觉设计select语句，可能导致“理想”与“现实”出现偏差。可以使用explain命令或desc命令分析select语句的执行计划，从而了解select语句的执行情况，进而分析select语句的性能瓶颈，然后对select语句进行重新设计或者对表结构进行重新设计（例如添加索引或者拆分表），让查询优化器能够更好地工作，提升查询性能。explain的语法格式非常简单（desc语法格式与explain的语法格式相同），如下所示：
explain select语句
说明
desc命令通常用于获取表结构的相关信息。而explain命令通常用于获取查询的执行计划，例如，多个表进行 join 连接运算时，这些表如何连接、连接顺序如何等信息都可以通过explain命令获取。在MySQL 5.7中，使用explain命令还可以获取select、delete、insert、replace以及update等语句的执行计划。然而，一般而言，由于select语句对MySQL的性能影响较大，通常使用explain命令获取select语句的执行计划。explain命令返回了一行或者多行记录，包括了select语句中用到的各个表的信息。
例如，查询姓“张”学生的信息，可以使用下面的SQL语句。使用explain对该SQL语句进行分析，如图2所示。1
explain select * from student where student_name like '张_'\G
图2 分析select语句
explain命令的返回信息说明如下。
- id：查询的序列号。
- select_type：查询语句的类型，可以为以下任何一种。
- simple：简单查询语句（不使用union或子查询的查询）。
- primary：主查询语句。
- union：union中的第二个或后面的select语句。
- dependent union：相关子查询中的union语句，union中的第二个或后面的select语句。
- union result：union 的合并结果。
- subquery：非相关子查询中的第一个select语句。
- uncacheable subquery：结果集无法缓存的子查询。
- dependent subquery：相关子查询中的第一个select语句。
- derived：派生表的select语句。
- table：执行该查询时所访问的数据库表。
- type：表数据的访问类型。下面给出各种访问类型，按照性能从最佳类型到最坏类型进行排序。
- system：结果集中仅有一条记录。这是const连接类型的一个特例。
- const：表中有多条记录，但结果集只包含一条记录。例如比较运算符中含有主键字段或者唯一性约束字段，只查询出表的一条记录。
- eq_ref：最多只会有一条匹配结果。两个表进行连接运算时，一个表使用主键字段或者唯一性约束字段与另一个表连接，查询出若干条记录。除了const类型，是比较好的连接类型。
- ref：两个表进行连接运算时，一个表使用普通索引与另一个表连接，查询出若干条记录。
- ref_or_null：两个表进行连接运算时，一个表使用普通索引与另一个表连接（这与ref类似），不同之处在于，检索时额外搜索包含null值的记录。
- index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge之后再读取表数据。在这种情况下，key字段表示使用了哪个索引，key_len字段表示使用索引时关键字的最长长度（字节数）。
- unique_subquery：使用了子查询，且子查询的返回结果包括主键字段或者唯一性约束字段。
- index_subquery：使用了子查询，且子查询使用了普通索引（不是主索引或唯一索引）。
- range：使用索引字段，检索给定范围的记录。在这种情况下，key字段表示使用了哪个索引。key_len字段表示使用索引时关键字的最长长度（字节数）。在该类型中，ref字段值为NULL。
- index：从第一个关键字开始，对索引进行顺序扫描。即便如此，index通常比ALL快，因为索引文件比数据文件小。
- all：对表进行全表扫描。说明检索数据时MySQL可能没有使用索引，效率会受到重大影响，应尽可能地优化select语句或者添加索引以避免此类情况的发生。
- fulltext：全文索引。说明
从全表扫描（full table scan）、索引扫描（index scans）、范围扫描(range scans)、唯一索引查找（unique index lookups）到常量（constants）扫描，访问速度依次递增，访问的数据越来越少。
- possible_keys：检索数据时可能使用到的索引，这就意味着possible_keys里面所包含的索引可能在 select 语句实际运行过程中根本没有用到。如果这个字段的值是 null，就表示没有索引被用到。这种情况下，可以检查where子句中哪些字段适合增加索引以提高查询的性能。
- key：实际使用的索引。当没有任何索引被用到的时候，这个字段的值就是NULL。想让MySQL强行使用或者忽略在possible_keys 字段中的索引列表，可以在select 语句中使用关键字force index，use index或ignore index。
- key_len：使用的索引关键字的长度（字节数）。当key字段的值为NULL时，索引的长度就是NULL。
- ref：显示了哪些字段或者常量被用来和索引关键字匹配以从表中查询记录。
- rows：返回MySQL认为在查询中应该检索的记录数。
- extra：显示了查询中MySQL的附加信息。以下是这个字段的几个不同值的解释。
- distinct：当MySQL找到第一个匹配记录后，就不再搜索其他记录了。
- not exists：MySQL 能够对查询进行left join 优化，当在当前表中找到一条记录符合left join匹配标准时，就不再搜索更多的记录了。
- range checked for each record (index map：#)：MySQL 没找到合适的可用的索引，但是发现来自前面表的字段值已知，部分索引可以使用。
- using filesort：当查询中包含order by子句，而且无法利用索引完成排序操作的时候，MySQL查询优化器不得不选择相应的排序算法，在内存或者硬盘上进行排序。应尽可能地优化select语句或者添加索引以避免此类情况的发生。
- using index：直接从索引中取得信息，不需要从表中获取数据。这就意味着查询时的字段是索引的关键字。
- using temporary：MySQL需要创建临时表存储结果集以完成查询。在group by以及order by查询中比较常见。
- using where：如果查询不是读取表的所有数据，也不是仅仅通过索引就可以获取所有需要的数据，则会出现using where信息。
- using sort_union(……), using union(……), using intersect(……)：说明如何为index_merge连接类型合并索引扫描。
- using index for group-by：类似于访问表的using index 方式，using index for group-by表示在进行group by或distinct查询时，分组字段也在索引中。
Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问NULL 值的时候使用。
Impossible WHERE noticed after reading const tables：MySQL 查询优化器通过收集到的统计信息判断出不可能存在的结果。
No tables：查询语句中使用不包含任何FROM的子句。
Select tables optimized away：当使用某些聚合函数来访问某个索引字段的时候，MySQL 查询优化器会通过索引直接一次定位到所需的数据行，完成整个查询。当然，前提是在查询语句中不能有group by操作。
使用下面的SQL语句在student表的student_name字段创建两个普通索引，其中name_index1索引仅仅对学生的“姓”创建了索引。然后重新使用explain命令分析两条select语句，如图14-69所示。1
2
3
4create index name_index on student (student_name);
create index name_index1 on student (student_name(1));
explain select * from student where student_name like '张％'\G
explain select * from student use index (name_index1) where student_name like '张％'\G
图14-69 分析select语句
从图中可以分析，第一条select语句执行时使用了name_index索引检索了student表，第二条select语句由于指定使用索引name_index1，执行时使用了name_index1索引检索了student表（注意两个索引的长度key_len的值不相同）。
索引与select语句
有时数据库表即使存在了合理的索引，不良的select语句也可能导致索引无法使用，对于InnoDB表而言，这样不仅会降低检索性能，还会导致行级锁升级为表级锁。因此，为了提升select语句的性能，合理的select语句与良好的索引结构缺一不可。
（1）对索引关键字字段使用了函数将导致索引无法使用。
下面的SQL语句同样实现了查询姓“张”学生的信息，使用explain或desc对该SQL语句进行分析，如图14-70所示。可以看到：由于type为ALL，并且rows的值为6（总共有6条学生信息），可以得知该select语句进行的是全表扫描，当学生表中的记录非常多时，该select语句会导致MySQL的性能瓶颈。事实上，where条件语句后的索引字段加上了函数处理的话，将不能使用索引。1
explain select * from student where substring(student_name,1,1)='张'\G
▲图14-70 索引与select语句（1）
（2）对索引字段进行模糊查询时，模式的第一个字符是通配符时，将导致索引无法使用。
下面的SQL语句实现了查询姓名中含有“张”的学生信息，使用explain或desc对该SQL语句进行分析，如图14-71所示。同样，该select语句会导致MySQL的性能瓶颈。事实上,模糊查询时，查询模式的第一个字符是通配符时，将不能使用索引。
1 | explain select * from student where student_name like '％张％'\G |
▲图14-71 索引与select语句（2）
3）违反最左前缀原则的select语句，将导致索引无法使用。
例如，下面的SQL语句首先在teacher表的（teacher_name、teacher_contact）组合字段创建一个组合索引name_contact，然后查询teacher_contact值为“11000000000”的教师信息，执行结果如图14-72所示。1
2create index name_contact on teacher (teacher_name,teacher_contact);
explain select * from teacher where teacher_contact='11000000000'\G
上面的select语句并没有使用组合索引name_contact，原因是该select语句违反了最左前缀原则。下面的select语句适用于最左前缀原则，因此使用了组合索引name_contact，执行结果如图14-73所示。
explain select * from teacher where teacher_name=’张老师’\G
4）使用不等于（not操作符、！=或者<>）作为查询条件，将导致索引无法使用。
例如查询不是姓“张”学生的信息，可以使用下面的SQL语句。使用explain或desc对该SQL语句进行分析，执行结果如图14-74所示。1
desc select * from student where student_name not like '张_'\G
图14-72 索引与select语句（3）
▲图14-73 索引与select语句（4）
5）检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引。
例如，下面的两条select语句从student表中检索出学号为“2012001”的学生信息，使用explain或desc对这两条select语句进行分析，第二条select语句没有使用索引，如图14-75所示。1
2desc select * from student where student_no='2012001'\G
desc select * from student where student_no=2012001\G
▲图14-74 索引与select语句(5)
图14-75 索引与select语句（6）
分页与select
对于MySQL而言，只需要指定select语句limit关键字start与length的值，就可以轻松地实现分页功能。但是如果考虑性能因素，当start的值比较大的时候，MySQL的分页性能会明显下降。下面的操作步骤意在说明start的取值与MySQL分页性能之间的关系。
步骤1：准备海量测试数据。
假设某个新闻发布系统存在新闻表news，主键是news_id，创建news 表的create table 语句如下所示。title表示新闻的标题，content表示新闻的内容，publish_time表示新闻的发布时间。1
2
3
4
5
6create table news(
news_id int auto_increment primary key,
title char(100) not null,
content text,
publish_time datetime
);
下面的MySQL语句负责创建存储过程insert_n()，该存储过程的功能是向news表中插入n条新闻信息。1
2
3
4
5
6
7
8
9
10
11
12
13delimiter $$
create procedure insert_n(n int)
modifies sql data
begin
declare start int default 0;
while start<n do
set start = start + 1;
insert into news values(start,'title','content',now());
end while;
end;
$$
delimiter ;
下面的MySQL语句负责调用存储过程insert_n()，向news表插入百万级别的新闻信息。1
call insert_n(1000000);
主机执行该存储过程共用了将近 30 分钟的时间，如图14-76所示。
▲图14-76 批量插入百万条数据所需要的时间
步骤2：数据库用户需要分页浏览其中的10条新闻信息。
假设数据库用户分页浏览新闻内容时，每一页最多容纳 10 条新闻信息，当前页的第一条新闻的news_id是900001，最后一条新闻的news_id应该是900010。对于该应用，数据库开发人员可能使用下列方法实现。
方法一：1
select * from news limit 900000,10;
方法二：1
select * from news order by news_id limit 900000,10;
方法三：1
select * from news where news_id>900000 order by news_id limit 10;
或者1
select * from news where news_id>900000 limit 10;
比较这3种方法的执行时间，如图14-77、图14-78、图14-79所示。第三种方法耗费的时间最短，大概是0.05秒；前面的两个方法执行时间大致相当，耗时最长，大概是4秒多钟的时间（耗费的时间是第三种方法的80倍）。如果多个数据库用户同时分页浏览新闻信息，第三种方法的性能优势更加明显。
图14-77 方法一的执行时间
▲图14-78 方法二的执行时间
▲图14-79 方法三的执行时间
分析：limit 900000,10的意思是扫描满足条件的900010行记录，扔掉前面的900000行记录，返回最后的10行记录。对于方法一的select语句而言，实际上执行的是全表扫描，如图14-80所示；对于方法二的select语句而言，使用聚簇索引（或者主键索引）扫描前900010行记录；对于方法三的select语句而言，使用聚簇索引（或者主键索引）进行范围扫描，扫描201394行记录。可以看出：方法三的分页性能更加优越。
结论：在进行分页时，如果使用聚簇索引（或者主键索引）进行范围扫描，性能更加优越。
扩展：同样的道理，假设数据库用户分页浏览新闻内容时，每一页最多容纳 10 条新闻信息，当前页的第一条新闻的news_id是900001，最后一条新闻的news_id是900010。那么当前页的下一页可以使用下面的select语句实现。1
select * from news where news_id>900010 limit 10;
或者1
select * from news where news_id>900010 order by news_id limit 10;
当前页的上一页可以使用下面的select语句实现。1
select * from news where news_id<900000 order by news_id desc limit 10;
SQL语句其他注意事项
SQL语句的编写是一门艺术，在编写SQL语句时，首先应该确保SQL语句能够正确地执行，除此以外，还要考虑SQL语句的效率。下面介绍几点在编写SQL语句时应该注意的问题。
1）获取了不需要的字段。
如果应用程序并不需要表的所有字段，避免使用“*”可以减轻MySQL服务器内存压力以及网络带宽压力。
2）同一功能的SQL语句，书写格式尽量保持统一。
例如查询学生的所有信息，如果A、B程序员分别写为：1
2select * from student;
select * from student;（中间多了空格）
上面两个select语句功能完全相同，并且它们产生的结果以及执行的时间是一样的。但是如果考虑到缓存因素（例如查询缓存Query Cache 开启时），功能相同的两条select语句可能导致查询缓存失效。原因在于：查询缓存匹配的过程是一个区分大小写的hash查找，在缓存中两个相似的查询哪怕是一个单字节不一样，也不会匹配。
3）使用存储程序。
存储程序在解析时，自动检查语法错误、权限以及所有对象的依赖性。而等到执行的时候，会直接执行，而不会进行上述检查，这也是存储程序执行效率高的主要原因。当存储程序所依赖的对象发生了变化，MySQL会自动将存储程序的状态设置为INVALID，而存储程序的状态如果是INVALID，则会在下次执行的时候尝试重新解析。
4）使用预处理prepared statement。
使用预处理prepared statement可以提升SQL语句的性能，原因在于预处理中的SQL语句只需要解析一次，之后执行SQL语句时，就不需要再进行解析操作。如果需要多次执行同一个SQL语句，可以使用预处理prepared statement提升SQL语句的性能。
5）根据齿轮原则，多个表进行join连接运算时，使用小结果集启动大结果集。
6）当查询结果集只有一行数据时使用limit 1，可以提前终止查询语句的执行。
7）使用连接（join）来代替子查询
连接（join）之所以更有效率一些，是因为连接（join）不需要在内存中使用临时表，而子查询需要在内存中创建临时表来完成，逻辑上需要两个步骤的查询工作。
14.6.7 profiling性能分析工具
通过慢查询日志可以得知哪些SQL语句执行效率低下，通过explain命令可以得知SQL语句的执行计划、索引使用等信息。如果这些信息还不够详细，可以通过profiling性能分析工具得到更准确的SQL执行消耗系统资源的信息。
profiling是另一个常用的性能分析工具，profiling能够显示SQL语句执行过程中消耗各种系统资源的信息。通过分析这些信息可以及时发现不良的SQL语句，从而对其进行调优。例如，通过profiling工具可以查看SQL语句的执行时间、System lock 以及Table lock花费的时间等信息，这对定位一条I/O或CPU消耗严重的SQL语句非常重要。默认情况下，profiling是关闭的。
1）使用下面的set命令开启profiling1
set profiling=1;
2）使用下面的SQL语句查询姓“张”学生的信息。1
select * from student where student_name like '张％';
3）使用MySQL命令“show profiles;”查看所有SQL语句的执行时间（Duration 的单位为秒），执行结果如图14-81所示。
4）使用MySQL命令“show profile for query 1;”查询Query_ID 值为1 的SQL语句的具体执行时间，如图14-82所示。
▲图14-82 profiling性能分析工具
5）测试完毕以后，关闭profiling。1
set profiling=0;