Mysql数据库慢查询常用优化方式

MySQL
351
0
0
2023-05-19
MySQL慢查询日志超详细总结
上一篇
MySQL中binlog备份脚本的方法
下一篇
标签   MySQL语句
目录
  • 慢查询日志概念
  • 一、数据库中设置SQL慢查询
  • 1、mysql慢查询相关配置参数介绍
  • 2、实现配置步骤
  • 二、分析慢查询日志
  • 三、常见的慢查询优化
  • 1、索引没起作用的情况
  • 2、优化数据库结构
  • 3、分解关联查询
  • 4、优化LIMIT分页
  • 四、常用优化方法
  • 1． SQL语句的优化
  • 2． 表结构的优化
  • 总结

慢查询日志概念

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

一、数据库中设置SQL慢查询

1、mysql慢查询相关配置参数介绍

  • slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
  • log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  • slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
  • long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
  • log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
  • log_output：日志存储方式。log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

2、实现配置步骤

第一步.开启mysql慢查询

方式一：

修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

方法二：通过MySQL数据库开启慢查询:

二、分析慢查询日志

直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

例如：执行EXPLAIN SELECT * FROM erp_bill_index ORDER BYmodifiedtime LIMIT 0,1000

得到如下结果： 显示结果分析：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解释：

table 显示这一行的数据是关于哪张表的

type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

rows 显示需要扫描行数

key 使用的索引

三、常见的慢查询优化

1、索引没起作用的情况

1）. 使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

2）. 使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

2、优化数据库结构

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

1）. 将字段很多的表分解成多个表

对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

2）. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

3、分解关联查询

将一个大的查询分解为多个小查询是很有必要的。

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，例如：

SELECT * FROM tag
JOIN tag_post ON tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = ‘mysql';

分解为:

SELECT * FROM tag WHERE tag = ‘mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);

4、优化LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：select id,title from collect limit 90000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

常用的方法

1）、筛选字段上加索引

2）、先查出主键id值：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

3）、关延迟联

如果这个表非常大，那么这个查询可以改写成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。

这个技术也可以用在优化关联查询中的limit。

4）、建立复合索引acct_id和create_time

select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

5）、查询语句使用Group by和Order by会影响数据查询效率

四、常用优化方法

1． SQL语句的优化

1） 查询语句应该尽量避免全表扫描，首先应该考虑在Where子句以及OrderBy子句上建立索引，但是每一条SQL语句最多只会走一条索引，而建立过多的索引会带来插入和更新时的开销，同时对于区分度不大的字段，应该尽量避免建立索引，可以在查询语句前使用explain关键字，查看SQL语句的执行计划，判断该查询语句是否使用了索引；

2）应尽量使用EXIST和NOT EXIST代替 IN和NOT IN，因为后者很有可能导致全表扫描放弃使用索引；

3）应尽量避免在Where子句中对字段进行NULL判断，因为NULL判断会导致全表扫描；

4）应尽量避免在Where子句中使用or作为连接条件，因为同样会导致全表扫描；

5）应尽量避免在Where子句中使用！=或者<>操作符，同样会导致全表扫描；

6）使用like “%abc%” 或者like “%abc” 同样也会导致全表扫描，而like “abc%”会使用索引。

7）在使用Union操作符时，应该考虑是否可以使用Union ALL来代替，因为Union操作符在进行结果合并时，会对产生的结果进行排序运算，删除重复记录，对于没有该需求的应用应使用Union ALL，后者仅仅只是将结果合并返回，能大幅度提高性能；

8）应尽量避免在Where子句中使用表达式操作符，因为会导致全表扫描；

9）应尽量避免在Where子句中对字段使用函数，因为同样会导致全表扫描

10）Select语句中尽量 避免使用“*”，因为在SQL语句在解析的过程中，会将“”转换成所有列的列名，而这个工作是通过查询数据字典完成的，有一定的开销；

11）Where子句中，表连接条件应该写在其他条件之前，因为Where子句的解析是从后向前的，所以尽量把能够过滤到多数记录的限制条件放在Where子句的末尾；

12）若数据库表上存在诸如index(a,b,c)之类的联合索引，则Where子句中条件字段的出现顺序应该与索引字段的出现顺序一致，否则将无法使用该联合索引；

13）From子句中表的出现顺序同样会对SQL语句的执行性能造成影响，From子句在解析时是从后向前的，即写在末尾的表将被优先处理，应该选择记录较少的表作为基表放在后面，同时如果出现3个及3个以上的表连接查询时，应该将交叉表作为基表；

14）尽量使用>=操作符代替>操作符，例如，如下SQL语句，select dbInstanceIdentifier from DBInstance where id > 3，该语句应该替换成 select dbInstanceIdentifier from DBInstance where id >=4 ，两个语句的执行结果是一样的，但是性能却不同，后者更加 高效，因为前者在执行时，首先会去找等于3的记录，然后向前扫描，而后者直接定位到等于4的记录。

2． 表结构的优化

这里主要指如何正确的建立索引，因为不合理的索引会导致查询全表扫描，同时过多的索引会带来插入和更新的性能开销；

1）首先要明确每一条SQL语句最多只可能使用一个索引，如果出现多个可以使用的索引，系统会根据执行代价，选择一个索引执行；

2）对于Innodb表，虽然如果用户不指定主键，系统会自动生成一个主键列，但是自动产生的主键列有多个问题1. 性能不足，无法使用cache读取；2. 并发不足，系统所有无主键表，共用一个全局的Auto_Increment列。因此，InnoDB的所有表，在建表同时必须指定主键。

3）对于区分度不大的字段，不要建立索引；

4）一个字段只需建一种索引即可，无需建立了唯一索引，又建立INDEX索引。

5）对于大的文本字段或者BLOB字段，不要建立索引；

6）连接查询的连接字段应该建立索引；

7）排序字段一般要建立索引；

8）分组统计字段一般要建立索引；

9）正确使用联合索引，联合索引的第一个字段是可以被单独使用的，例如有如下联合索引index(userID,dbInstanceID),一下查询语句是可以使用该索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是语句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用该索引；

10）索引一般用于记录比较多的表，假如有表DBInstance，所有查询都有userID条件字段，目前已知该字段已经能够很好的区分记录，即每一个userID下记录数量不多，所以该表只需在userID上建立一个索引即可，即使有使用其他条件字段，由于每一个userID对应的记录数据不多，所以其他字段使用不用索引基本无影响，同时也可以避免建立过多的索引带来的插入和更新的性能开销；

参考地址：

https://blog.csdn.net/kris1025/article/details/80085020

MySQL慢查询日志超详细总结
上一篇
MySQL中binlog备份脚本的方法
下一篇