外观
优化概述
分析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行。
