做免费试用的网站,如何建立网站链接,建设银行官方网站广州,百度淘宝网日常开发中#xff0c;我们经常会遇到数据库慢查询。那么导致数据慢查询都有哪些常见的原因呢#xff1f;今天就跟大家聊聊导致 MySQL 慢查询的 12 个常见原因#xff0c;以及对应的解决方法#xff1a;
SQL 没加索引SQL 索引失效limit 深分页问题单表数据量太大join 或者…日常开发中我们经常会遇到数据库慢查询。那么导致数据慢查询都有哪些常见的原因呢今天就跟大家聊聊导致 MySQL 慢查询的 12 个常见原因以及对应的解决方法
SQL 没加索引SQL 索引失效limit 深分页问题单表数据量太大join 或者子查询过多in元素过多数据库在刷脏页order by 文件排序拿不到锁delete in 子查询不走索引group by 使用临时表系统硬件或网络资源
1. SQL 没加索引
很多时候我们的慢查询都是因为没有加索引。如果没有加索引的话会导致全表扫描的。因此应考虑在 where 的条件列建立索引尽量避免全表扫描
2. SQL 索引失效
【MYSQL】MYSQL 的学习教程四之索引失效场景
有时候我们明明加了索引了但是索引却不生效。在哪些场景索引会不生效呢主要有以下十大经典场景
查询条件包含 or可能导致索引失效隐式的类型转换索引失效like 通配符 “%” 在关键词前面导致索引失效在索引列上使用 MYSQL 的内置函数索引失效对索引列运算如、-、*、/索引失效索引字段上使用负向查询NOT、!、、NOT IN、NOT LIKE时可能会导致索引失效索引字段可以为 null使用 is null is not null可能导致索引失效违反了索引的最左匹配原则联合索引查询时的条件列不是联合索引中的第一个列索引失效左连接查询或者右连接查询查询关联的字段编码格式不一样可能导致索引失效MYSQL 估计使用全表扫描要比使用索引快,则不使用索引
3. limit 深分页问题
3.1 limit 深分页为什么会变慢
limit深分页为什么会导致 SQL 变慢呢假设我们有表结构如下
CREATE TABLE account (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键Id,name varchar(255) DEFAULT NULL COMMENT 账户名,balance int(11) DEFAULT NULL COMMENT 余额,create_time datetime NOT NULL COMMENT 创建时间,update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间,PRIMARY KEY (id),KEY idx_name (name),KEY idx_create_time (create_time) //索引
) ENGINEInnoDB AUTO_INCREMENT1570068 DEFAULT CHARSETutf8 ROW_FORMATREDUNDANT COMMENT账户表;执行如下 SQL
select id,name,balance from account where create_time 2020-09-19 limit 100000,10;这个SQL的执行流程
通过普通二级索引树 idx_create_time过滤 create_time 条件找到满足条件的主键 id通过主键 id回到 id 主键索引树找到满足记录的行然后取出需要展示的列回表过程扫描满足条件的 100010 行然后扔掉前 100000 行返回结果
如下图 limit 深分页导致 SQL 变慢原因有两个
limit 语句会先扫描 offset n 行然后再丢弃掉前 offset 行返回后 n 行数据。也就是说 limit 100000,10就会扫描100010行而 limit 0,10只扫描10行limit 100000,10 扫描更多的行数也意味着回表更多的次数
3.2 如何优化深分页问题
我们可以通过减少回表次数来优化。一般有标签记录法、延迟关联法
3.2.1 标签记录法
标签记录法标记一下上次查询到哪一条了下次再来查的时候从该条开始往下扫描
假设上一次记录到 100000则 SQL 可以修改为
select id,name,balance FROM account where id 100000 limit 10;这样的话后面无论翻多少页性能都会不错的因为命中了 id 主键索引。但是这种方式有局限性需要一种类似连续自增的字段
3.2.2 延迟关联法
延迟关联法把条件转移到主键索引树然后减少回表
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time 2020-09-19 limit 100000, 10) AS acct2 on acct1.id acct2.id;优化思路先通过 idx_create_time 二级索引树查询到满足条件的主键 ID再与原表通过主键 ID 内连接这样后面直接走了主键索引了同时也减少了回表
4. 单表数据量太大
4.1 单表数据量太大为什么会变慢
一个表的数据量达到好几千万或者上亿时加索引的效果没那么明显啦。性能之所以会变差是因为维护索引的 B 树结构层级变得更高了查询一条数据时需要经历的磁盘IO变多因此查询性能变慢
4.2 一棵 B 树可以存多少数据量
【MYSQL】MYSQL 的学习教程五之 MySQL 索引底层B 树详解 一棵高度为 3 的 B 树能存放 1170 *1170 *16 21902400也就是说可以存放两千万左右的记录。B 树高度一般为 1-3 层已经满足千万级别的数据存储
如果 B 树想存储更多的数据那树结构层级就会更高查询一条数据时需要经历的磁盘IO变多因此查询性能变慢
4.3 如何解决单表数据量太大查询变慢的问题
一般超过千万级别我们可以考虑分库分表了
分库分表可能导致的问题
事务问题跨库问题排序问题分页问题分布式ID
在评估是否分库分表前先考虑下是否可以把部分历史数据归档如果可以的话先不要急着分库分表。如果真的要分库分表综合考虑和评估方案。比如可以考虑垂直、水平分库分表。水平分库分表策略的话range范围、hash取模、rangehash取模混合等等
5. join 或者子查询过多
一般来说不建议使用子查询可以把子查询改成 join 来优化
而数据库有个规范约定就是尽量不要有超过3个以上的表连接
MySQL中join的执行算法分别是Index Nested-Loop Join、Block Nested-Loop Join
Index Nested-Loop Join索引嵌套循环连接这个 join 算法跟我们写程序时的嵌套查询类似并且可以用上被驱动表的索引Block Nested-Loop Join缓存块嵌套循环连接被驱动表上没有可用的索引它会先把驱动表的数据读入线程内存join_buffer 中再扫描被驱动表把被驱动表的每一行取出来跟 join_buffer 中的数据做对比满足join条件的作为结果集的一部分返回
join 过多的问题
过多的表连接会大大增加 SQL 复杂度如果可以使用被驱动表的索引那还好并且使用小表来做驱动表查询效率更佳。如果被驱动表没有可用的索引join是在 join_buffer 内存做的如果匹配的数据量比较小或者 join_buffer 设置的比较大速度也不会太慢。但是如果join的数据量比较大时mysql 会采用在硬盘上创建临时表的方式进行多张表的关联匹配这种显然效率就极低本来磁盘的 IO 就不快还要关联
一般情况下如果业务需要的话关联 2~3 个表是可以接受的但是关联的字段需要加索引哈。如果需要关联更多的表建议从代码层面进行拆分在业务层先查询一张表的数据然后以关联字段作为条件查询关联表形成 map然后在业务层进行数据的拼装
6. in元素过多
如果使用了 in即使后面的条件加了索引还是要注意in后面的元素不要过多哈。in 元素一般建议不要超过500个如果超过了建议分组每次500一组进行哈
如果我们对in的条件不做任何限制的话该查询语句一次性可能会查询出非常多的数据很容易导致接口超时。尤其有时候我们是用的子查询in后面的子查询你都不知道数量有多少那种更容易采坑。如下这种子查询
select * from user where user_id in (select author_id from artilce where type 1);正例是分批进行每批500个
select user_id,name from user where user_id in (1,2,3...500);7. 数据库在刷脏页
7.1 什么是脏页
当内存数据页跟磁盘数据页内容不一致的时候我们称这个内存页为**“脏页”。内存数据写入到磁盘后内存和磁盘上的数据页的内容就一致了称为“干净页”**。一般有更新 SQL 才可能会导致脏页
7.2 一条更新语句是如何执行的
以下的这个更新SQL如何执行的呢
update t set cc1 where id666;对于这条更新SQL执行器会先找引擎取 id 666 这一行。如果这行所在的数据页本来就在内存中的话就直接返回给执行器。如果不在内存就去磁盘读入内存再返回执行器拿到引擎给的行数据后给这一行 C 的值加一得到新的一行数据再调用引擎接口写入这行新数据引擎将这行新数据更新到内存中同时将这个更新操作记录到 redo log 里面但是此时 redo log 是处于 prepare 状态的哈执行器生成这个操作的 binlog并把 binlog 写入磁盘执行器调用引擎的提交事务接口引擎把刚刚写入的 redo log 改成提交commit状态更新完成
如下图 InnoDB 在处理更新语句的时候只做了写日志这一个磁盘操作。这个日志叫作 redo log重做日志。平时更新 SQL 执行得很快其实是因为它只是在写内存和 redo log 日志等到空闲的时候才把 redo log 日志里的数据同步到磁盘中 redo log 日志不是在磁盘嘛那为什么不慢其实是因为写 redo log 的过程是顺序写磁盘的。磁盘顺序写会减少寻道等待时间速度比随机写要快很多的。 7.3 为什么会出现脏页呢
更新 SQL 只是在写内存和 redo log 日志等到空闲的时候才把 redo log 日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致就出现脏页
7.4 什么时候会刷脏页flush
InnoDB 存储引擎的 redo log 大小是固定且是环型写入的如下图 那什么时候会刷脏页有几种场景
redo log 写满了要刷脏页。这种情况要尽量避免的。因为出现这种情况时整个系统就不能再接受更新啦即所有的更新都必须堵住内存不够了需要新的内存页就要淘汰一些数据页这时候会刷脏页 InnoDB 用缓冲池buffer pool管理内存,而当要读入的数据页没有在内存的时候就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉如果要淘汰的是一个干净页就直接释放出来复用但如果是脏页呢就必须将脏页先刷到磁盘变成干净页后才能复用 MySQL 认为系统空闲的时候也会刷一些脏页MySQL 正常关闭时会把内存的脏页都 flush 到磁盘上
7.5 为什么刷脏页会导致 SQL 变慢呢
redo log 写满了要刷脏页这时候会导致系统所有的更新堵住写性能都跌为0了肯定慢呀。一般要杜绝出现这个情况。一个查询要淘汰的脏页个数太多一样会导致查询的响应时间明显变长
8. order by 文件排序
order by 就一定会导致慢查询吗不是这样的哈因为 order by 平时用得多并且数据量一上来还是走文件排序的话很容易有慢SQL的
8.1 order by 的 Using filesort 文件排序
平时经常需要用到 order by 主要就是用来给某些字段排序的。比如以下 SQL:
select name,age,city from staff where city 深圳 order by age limit 10;查看 explain 执行计划的时候可以看到 Extra 这一列有一个 Using filesort它表示用到文件排序
8.2 order by文件排序效率为什么较低
order by 用到文件排序时为什么查询效率会相对低呢 order by 排序分为全字段排序、rowid排序。它是拿 max_length_for_sort_data 和结果行数据长度对比如果结果行数据长度超过 max_length_for_sort_data 这个值就会走 rowid 排序相反则走全字段排序
8.2.1 rowid 排序
rowid 排序一般需要回表去找满足条件的数据所以效率会慢一点。以下这个 SQL使用 rowid 排序执行过程是这样
select name,age,city from staff where city 深圳 order by age limit 10;MySQL 为对应的线程初始化 sort_buffer放入需要排序的 age 字段以及主键 id从索引树 idx_city 找到第一个满足 city深圳’ 条件的主键 id也就是图中的 id9到主键 id索引树拿到 id9 的这一行数据 取 age 和主键 id 的值存到 sort_buffer从索引树 idx_city 拿到下一个记录的主键 id即图中的 id13重复步骤 3、4 直到 city 的值不等于深圳为止前面 5 步已经查找到了所有 city 为深圳的数据在 sort_buffer 中将所有数据根据 age 进行排序遍历排序结果取前 10 行并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端 8.2.2 全字段排序
同样的 SQL如果是走全字段排序是这样的
MySQL 为对应的线程初始化 sort_buffer放入需要查询的 name、age、city 字段从索引树 idx_city 找到第一个满足 city深圳’ 条件的主键 id也就是图中的 id9到 主键id索引树 拿到 id9 的这一行数据 取 name、age、city 三个字段的值存到 sort_buffer从 索引树idx_city 拿到下一个记录的主键 id即图中的 id13重复步骤 3、4 直到 city 的值不等于深圳为止前面 5 步已经查找到了所有 city 为深圳的数据在 sort_buffer 中将所有数据根据 age 进行排序按照排序结果取前 10 行返回给客户端 sort_buffer 的大小是由一个参数控制的sort_buffer_size
如果要排序的数据小于 sort_buffer_size排序在 sort_buffer 内存中完成如果要排序的数据大于 sort_buffer_size则借助磁盘文件来进行排序 借助磁盘文件排序的话效率就更慢一点。因为先把数据放入 sort_buffer当快要满时。会排一下序然后把 sort_buffer 中的数据放到临时磁盘文件等到所有满足条件数据都查完排完再用归并算法把磁盘的临时排好序的小文件合并成一个有序的大文件 8.3 如何优化 order by 的文件排序
order by 使用文件排序效率会低一点。我们怎么优化呢
因为数据是无序的所以就需要排序。如果数据本身是有序的那就不会再用到文件排序啦。而索引数据本身是有序的我们通过建立索引来优化 order by 语句可以通过调整 max_length_for_sort_data、sort_buffer_size 等参数优化
9. 拿不到锁
有时候我们查询一条很简单的 SQL但是却等待很长的时间不见结果返回。一般这种时候就是表被锁住了或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放
这时候我们可以用 show processlist 命令看看当前语句处于什么状态哈
10. delete in 子查询不走索引
当 delete 遇到 in 子查询时即使有索引也是不走索引的。而对应的 select in 子查询却可以走索引
实际执行的时候MySQL 对 select in 子查询做了优化把子查询改成 join 的方式所以可以走索引。但是很遗憾对于 delete in 子查询MySQL 却没有对它做这个优化
11. group by 使用临时表
group by 一般用于分组统计它表达的逻辑就是根据一定的规则进行分组
假设有表结构
CREATE TABLE staff (id bigint(11) NOT NULL AUTO_INCREMENT COMMENT 主键id,id_card varchar(20) NOT NULL COMMENT 身份证号码,name varchar(64) NOT NULL COMMENT 姓名,age int(4) NOT NULL COMMENT 年龄,city varchar(64) NOT NULL COMMENT 城市,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT15 DEFAULT CHARSETutf8 COMMENT员工表;我们查看一下这个SQL的执行计划
explain select city ,count(*) as num from staff group by city;Extra 这个字段的 Using temporary 表示在执行分组的时候使用了临时表Extra 这个字段的 Using filesort 表示使用了文件排序
group by 是怎么使用到临时表和排序了呢我们来看下这个SQL的执行流程
创建内存临时表表里有两个字段 city 和 num全表扫描 staff 的记录依次取出 city ‘X’ 的记录 判断临时表中是否有为 cityX 的行没有就插入一个记录 (X,1);如果临时表中有 city‘X’ 的行就将X这一行的 num 值加 1 遍历完成后再根据字段 city 做排序得到结果集返回给客户端
这个流程的执行图如下 临时表的排序是怎样的呢 就是把需要排序的字段放到 sort buffer排完就返回。在这里注意一点哈排序分全字段排序和 rowid 排序
如果是全字段排序需要查询返回的字段都放入sort buffer根据排序字段排完直接返回如果是 rowid 排序只是需要排序的字段放入sort buffer然后多一次回表操作再返回
11.2 group by 可能会慢在哪里
因为它既用到临时表又默认用到排序。有时候还可能用到磁盘临时表
如果执行过程中会发现内存临时表大小到达了上限控制这个上限的参数就是tmp_table_size会把内存临时表转成磁盘临时表。如果数据量很大很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间
11.3 如何优化 group by 呢
从哪些方向去优化呢
方向1既然它默认会排序我们不给它排是不是就行啦。方向2既然临时表是影响 group by 性能的因素我们是不是可以不用临时表 执行 group by 语句为什么需要临时表呢group by 的语义逻辑就是统计不同的值出现的个数。如果这个这些值一开始就是有序的我们是不是直接往下扫描统计就好了就不用临时表来记录并统计结果啦? 可以有这些优化方案
group by 后面的字段加索引order by null 不用排序尽量只使用内存临时表使用 SQL_BIG_RESULT直接用磁盘临时表。不需要从内存临时表转到磁盘临时表这个过程很耗时
12. 系统硬件或网络资源
如果数据库服务器内存、硬件资源或者网络资源配置不是很好就会慢一些哈。这时候可以升级配置。这就好比你的计算机有时候很卡你可以加个内存条什么的一个道理如果数据库压力本身很大比如高并发场景下大量请求到数据库来数据库服务器CPU占用很高或者IO利用率很高这种情况下所有语句的执行都有可能变慢的哈
