优化概述

分析MySQL数据库的性能

数据库管理员可以使用SHOW STATUS语句查询MySQL数据库的性能，语法如下。

SHOW STATUS LIKE 'value 参数';

其中，value参数是常用的几个统计参数，具体介绍如下。

Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。
Show_queries：慢查询次数。
Com_select：查询操作的次数。
Com_insert：插入操作的次数。
Com_delete：删除操作的次数。

MySQL中存在查询InnoDB类型的表的一些参数。例如，Innodb_rows_read参数表示SELECT语句查询的记录数；Innodb_rows_inserted参数表示INSERT语句的插入数；Innodb_rows_updated参数表示UPDATE语句更新的记录数；Innodb_rows_deleted参数表示DELETE语句删除的记录数。

如我们查询MySQL服务器的连接数。

show status like 'Connections';

运行结果如上图，通过这些结果，我们可以知晓什么语句使用得多，如使用SELECT语句的次数大于INSERT语句的次数，我们就可以通过多使用索引来提升查询的速度，而因为插入表的次数少，也不会有太大影响。

使用profile工具分析语句消耗性能

在MySQL命令窗口中，使用SELECT语句查询到记录后，最后一行会显示查询的时间，精度为秒，小数点后两位，若要知晓精度更高的查询速度，可以通过开启profile工具实现。MySQL8.0安装后profile工具是默认不开启的。MySQL是通过profilig参数标记profile工具是否开启的，查看方法如下。

SHOW VARIABLES LIKE '%pro%';

运行结果如下图，可以看到，现在的profile工具处于关闭状态。

开启profile工具的语法如下。

SET profiling=1;

运行结果如下。

开启profile工具后，我们就可以通过先执行SELECT语句，再使用profile工具查看执行此语句消耗的时间。语法如下。

select * from db_stu.tb_stu;
show profiles;

红色框内就是此语句执行的时间，字段Duration表示本次查询耗费的时间，单位为秒。

优化查询

分析查询语句

可使用关键字EXPLAIN分析查询语句，语法如下。

EXPLAIN SELECT 语句;

其中SELECT 语句就是我们平时使用的查询语句，例如下面的例子。

explain select * from db_stu.tb_stu;

其中主要的字段代表意义如下。

id：指出在整个查询中SELECT的位置。
table：查找的表名。
type：连接类型，该列中存储许多值，范围从const到ALL。
possible_keys：指出为了提高查找速度，可以使用的索引。
keys：指出实际使用的键。
rows：指出MySQL需要在相应表中返回查询结果所检验的行数，为了得到总行数，MySQL必须扫描处理整个查询，再乘以每个表的行值。
Extra：包含一些其他信息，如MySQL如何设计查询。

MySQL也支持使用关键字DESCRIBE分析查询，语法就是把EXPLAIN换成DECRIBE，且DESCRIBE可简写成DESC。

索引对查询速度的影响

下面我们通过使用索引和不使用索引，分别分析两次查询然后对比来观察查询的优化情况。

首先我们先查看一下数据表的定义语句以及数据。

可以看到表中字段price加了索引，其他列无索引。下面开始对比。

首先分析不使用索引的查询情况，我们查询bookname='MySQL从入门到精通'的记录，语法如下。

explain select * from tb_bookinfo where bookname = 'MySQL从入门到精通';

可以看到表中总共查询了6行记录，接下来我们为字段bookname添加索引，并且再查询一次，语法如下。

create index index_bookname on tb_bookinfo;
explain select * from tb_bookinfo where bookname = 'MySQL从入门到精通';

可以看到此时只查询了两行，比不使用索引少查询了4 行，所以在查询操作时使用索引不仅可以提高查询效率，还可以降低服务器开销。

使用索引查询

虽然索引可以提高查询的速度，但并不能充分发挥其作用，因此可以通过关键字或者其他方式优化查询处理。

应用关键字like优化索引查询

应用EXPLAIN语句执行以下查询。

explain select * from tb_bookinfo where bookname like '%Java Web';

可以看到查询的行数仍为6条，这是因为若匹配字符串第一个字符就是百分号“%”，则索引不会被使用，若“%”不在匹配字符串的第一位，则索引会被正常使用。

如我们再次使用查询，语法如下。

explain select * from tb_bookinfo where bookname like 'Java Web%';

查询语句中使用多列索引

多列索引是在表的多个字段上创建一个索引。只有在查询条件中使用了这些字段中的一个字段时，索引才会被正常使用。

应用多列索引在表的多个字段中创建一个索引，其命令如下。

CREATE INDEX index_name ON table_name (col_name1,[col_name2...]);

如拿此语句：create index index_name on table_name (c1,c2);来说，在应用字段c2时，索引不能正常使用。这就意味着索引并未在MySQL优化中起到任何作用，故必须使用第一字段c1，索引才可以被正常使用。具体可看下图。
运行结果

查询语句中使用关键字OR

在MySQL中，查询语句包含OR关键字时，要求查询的两个字段必须同为索引，若所搜索的字段有一个不为索引，则在查询中不会应用索引进行查询。下面我们通过一个实例来对比使用索引和不使用索引的性能差异。

首先查看tb_bookinfo表的结构。我们发现字段price有索引，我们把此索引删除。
运行结果
然后我们使用EXPLAIN语句分析使用OR关键字查询，语法如下。
```
explain select * from tb_bookinfo
where bookname = 'MySQL从入门到精通' or price = 89;
```
运行结果
可以看到它扫描了整个表（6行），并且一个索引也没有使用。

我们重新添加索引，然后再次查找，语法如下。

create index index_price on tb_bookinfo (price);
explain select * from tb_bookinfo
where bookname = 'MySQL从入门到精通' or price = 89\G

可以看到它成功使用了两个索引，且共扫描了4行。

版权所有

许可证：署名 4.0 国际 (CC-BY-4.0)