Mysql慢查询常见原因

[羌俊恩]

1、没有索引或没有用到索引或索引配置不合理没有起作用。

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表 的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录 即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

索引类型：

• 普通索引：这是最基本的索引类型，没唯一性之类的限制。

• 唯一性索引：和普通索引基本相同，但所有的索引列只能出现一次，保持唯一性。

• 主键：主键是一种唯一索引，但必须指定为"PRIMARY KEY"。

• 全文索引：MYSQL从3.23开始支持全文索引和全文检索。在MYSQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

索引没起作用的常见情况：

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

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

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

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

 

2、IO吞吐量小形成了瓶颈。

这是从系统层来分析MYSQL是比较耗IO的。我们需要站点关注数据库IO这个指标。

监控命令：$iostat -d -k 1 10

参数 -d 表示，显示设备（磁盘）使用状态；-k某些使用block为单位的列强制使用Kilobytes为单位；1 10表示，数据显示每隔1秒刷新一次，共显示10次。

3、内存不足

监控内存使用：vmstat [-n] [延时[次数]]

Memory、swpd：如果 swpd 的值不为0，或者还比较大，比如超过100M了，但是si, so 的值长期为0，这种情况我们可以不用担心，不会影响系统性能。

free: 空闲的物理内存

buff: 作为buffer cache的内存，对块设备的读写进行缓冲

cache: 作为page cache的内存, 可以理解为文件系统的cache， 如果 cache 的值大的时候，说明cache暂存的文件数多，如果频繁访问到的文件都能被cache住，那么磁盘的读IO ，即bi 会非常小。

4、网络速度慢

ping IP -t 查看是否有丢包。

5、单次查询的数据量过大。

比如没有分页查询，一次提取上万条记录。数据库有可能卡死。

6、出现死锁

所谓死锁: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,需要外力来阻断这种现象；表级别锁是mysql实现的， 行级锁是引擎实现的比如InnoDb。读取表的时一般会增加表级锁来锁定表进行读取操作。

Show innodb status检查引擎状态 ,可以看到哪些语句产生死锁。

执行show processlist找到死锁线程号.然后Kill　processNo；

7、使用*进行了全表扫描

一般查询SQL语句一定要将字段明确指定。而不要使用*进行查询

8、注意UNion和UNion all 的区别。UNION all好

相同点：
union和union all 都是对于多个查询结果的并集进行操作
不同点：
1.union 不会输出两个结果并集的重复行，会对所产生的结果集进行排序运算，删除重复的记录再返回结果
2.union all 会输出两个结果并集的重复行

综上，union all的效率相对要高！

9、减轻或优化措施

1）优化数据库结构

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

（1） 将字段很多的表拆分成多个表

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

（2.）增加中间表

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

（3）分解关联查询

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

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

（4）优化LIMIT分页

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

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

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

示例：

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。
 

#建立复合索引 acct_id和create_time

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

[CSDN-Ada助手]

您可以前往 CSDN问答-大数据 发布问题, 以便更快地解决您的疑问