为什么SQL语句用了索引，还是会查询的很慢？

本文主要是介绍为什么SQL语句用了索引，还是会查询的很慢？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

慢查询和索引

慢查询和索引没有必然的联系。

1. long_query_time这个参数可监听慢查询，只要查询结果大于该参数值，就会记录到慢查询日志。默认是10s，但是生产环境中一般设置1s，对于一些敏感业务，会设置更小的值。
2. explain可查看SQL语句是否使用索引。

索引的过滤性

比如一句SQL，SELECT * FROM table WHERE id > 0;,id是非负数的主键索引，此SQL也是进行了全表扫描。不过是扫描的索引树。所以使用EXPLAIN解析的时候，也是属于使用了索引的。

因此InnoDB中没有使用索引的情况就是：从主键索引最左边的叶子节点开始，向右扫码整个索引树。

举例: 假设一张表记录了全国人口的信息，此时想获取10-15岁之间小朋友的数据，这时候查询结果可能上亿条。即使在age字段上加上索引，执行起来也会很慢。
SELECT * FROM t_people WHERE age BETWEEN 10 AND 15;
该SQL的执行流程如下:

1. 在索引age上用树搜索，获取到第一个age=10的记录，得到它的主键ID
2. 到主键索引行上获取整行信息，作为结果集的一部分返回。
3. 再获取下一个ID值，再去主键索引上获取整行信息。
4. 重复以上步骤直到碰到一个age>15的记录。

所以：加了索引不一定就可以查询的很快，我们关心的是扫描行数！

索引的扫描行数

如果在全国人口记录表中获取姓张且年龄为8岁的人的数据，SQL如下：
SELECT * FROM t_people WHERE name LIKE '张%' AND age = 8;
MySQL5.5之前的版本，这个语句的执行流程是这样的。

先在联合索引树上找到第一个姓名字段是张开头的记录，取出主键ID。根据ID获取整行信息，判断年龄是否等于8，不是的话就丢弃，是的话就存入结果集。

我们把根据主键ID查找整行数据的这个动作叫做回表。假设全国人口姓张的有8000万，那就要回表8000万次。不仅要回表，还得判断年龄是否等于8。
如何解决？
MySQL5.6以后引入了索引下推。
有了索引下推后的执行流程：

先在联合索引数上找到第一个姓名字段是张开头的记录，然后继续判断这个索引记录中年龄的值是否等于8。如果不等于8就直接丢弃，如果等于8再去根据主键ID获取整行记录，记录结果集。这样就减少了回表的操作。

但是，索引下推的话，还是绕不开最左匹配原则，还是需要在联合索引中遍历8000万次，然后回表100万次！此时怎么解决？
可以使用MySQL5.7中引入的虚拟列来实现。

虚拟列是很实用且容易被忽视的功能！

-- 创建虚拟列
alter table t_people add name_first varchar(2) generated always as 
(left(name, 1)), add index (name_first, age);-- 建表
CREATE TABLE `t_people` (`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAULT NULL,`age` int(11) DEFAULT NULL,`info` varchar(255) DEFAULT NULL,`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`, 1)) VIRTUAL,KEY `name_first` (`name_first`, `age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

以上SQL直接创建了一个虚拟列字段name_first,该字段存储姓名中的第一个字符。虚拟列不可被修改，也不能指定值。
我们此时再查询姓张且年龄等于8的数据，SELECT * FROM t_people WHERE name_fist = '张' AND age = 8;
此时只需要扫描联合索引100万次并回表一百万次了。

这个优化的本质是创建了一个更紧凑的索引来加速了查询的过程。

什么情况下索引失效？

1. 隐式的类型转换，索引失效
   EXPLAIN SELECT * FROM table where id = 1

   如果此时的id是varchar类型的话，索引不生效。因为此时是字符串类型和数字进行比较，MySQL会将它们都转成浮点型再比较。
   需要注意的是：如果此时id是int类型，传的是字符串类型的'1',此时是走索引的，至于为什么我就不知道了~

2. 条件查询包含OR,可能导致索引失效。
   EXPLAIN SELECT * FROM user where age = '12' or userId = '12'

   如果userId加了索引，age没有加索引，此时索引不生效。将userId放在or前面，也是不生效。
   因为此时即便是userId走了索引，使用or查询的时候，到age字段的时候，还是会进行全表扫描。就等于是进行了全表扫描+索引扫描+合并三个操作。但是换成and的话，索引就会生效了。

3. like通配符会导致索引失效
   EXPLAIN SELECT * FROM user where userId like '%12'

   以上SQL就不会走索引。但是如果将%放在后面就会走索引了。

4. 查询条件不满足最左匹配原则不会走索引。
   假设你建表如下：

   CREATE TABLE test (id INT ( 11 ) NOT NULL AUTO_INCREMENT,user_id VARCHAR ( 32 ) NOT NULL,age VARCHAR ( 16 ) NOT NULL,`name` VARCHAR ( 255 ) NOT NULL,`remark` varchar(255) DEFAULT NULL,PRIMARY KEY ( id ),KEY idx_userid_age_name ( user_id, age, `name` ) USING BTREE 
   ) ENGINE = INNODB DEFAULT CHARSET = utf8;
   

   那么执行SQLexplain select * from test where age ='捡田螺的小男孩';的时候，索引是不生效的。

   你建立的一个(a,b,c)的联合索引，相当于建立了(a),(a,b),(a,c),(a,b,c)四个索引。必须第一个查询字段是a字段才可以生效。
   需要注意的是，如果上述建表语句中，没有remark字段的话，怎么查询都会生效。因为如果没有remark字段的话，所有字段都在索引中！！

5. 在索引列上使用内置函数

   在字段create_time上加了索引，但是如果使用DATE_FORMAT、DATE_ADD等内置函数的话，索引是不会生效的。

6. 对索引列进行+、-、*、/、运算的话，索引不会生效。

7. 对索引列上进行!=、not in 等操作时，索引不生效。

   in的话会走索引。其实和MySQL的优化器有关，优化器觉得这么操作即使走了索引也需要扫描很多行，不如直接不走。

8. 索引列上使用is null、is not null有可能不走索引。

   在单个索引列上使用is null或者is not null的时候，是会走索引的。但是如果用or连接起来的话，就不会走索引了。
   因为在单个列上使用is null或者is not null的时候，索引类型是range。如果再加上or连接的话，那么优化器就会因为查询数据量的问题，放弃走索引。

9. 字段编码的问题

   如果表a中name字段编码是utf8_mb4，而表b中name字段的编码是utf8，那么这俩表关联的时候。a left join b on a.name = b.name，此时不会走索引。如果把两个name的字段编码改成一致，就会走索引了。

10. 优化器选错了索引

    不断地删除历史数据和新增数据的场景，有可能会导致MySQL选错索引。

    那么有哪些解决方案呢？

    • 使用force index 强行选择某个索引
    • 修改你的SQl，引导它使用我们期望的索引
    • 优化你的业务逻辑
    • 优化你的索引，新建一个更合适的索引，或者删除误用的索引。

limit深度分页会导致慢查询

如果数据量很大，你要获取第一百万页到第一百万零一页的数据，可想而知肯定会查询的慢。
MySQL会先扫描前面一百万页的数据，然后丢弃掉，再返回第一百万零一页的数据。

如何优化深度分页的问题？

• 标签记录法:就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。
  获取100万零一页数据，可以使用以下SQL。
  SELECT * FROM table WHERE id > 1000000 limit 10;
• 延迟关联法:就是把条件转移到主键索引树，然后减少回表。如下：
  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;

单表数据量太大也会导致慢查询。

一个B+树大概可以存放多少数据量呢？
InnoDB存储引擎最小储存单元是页，一页大小就是16k。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。
同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。
B+树高度一般为1-3层，已经满足千万级别的数据存储。

如果B+树想存储更多的数据，那树结构层级就会更高，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。
如何解决？—>分库分表！
但是分库分表会带来很多问题，比如事务问题、夸库问题、排序问题、分页问题、分布式ID等等。

JOIN或者子查询过多也会造成慢查询。

IN后面括号内的元素过多也会造成慢查询。

数据库在刷脏页也会造成慢查询。

1. 什么是脏页？

   当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。一般有更新SQL才可能会导致脏页。

2. 一条更新SQL是如何执行的？
   update t set c=c+1 where id=666;

   • 对于这条更新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的过程是顺序写磁盘的。磁盘顺序写会减少寻道等待时间，速度比随机写要快很多的。

3. 为什么会出现脏页？

   更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,就出现脏页。

4. 什么时候会刷脏？

   • redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住。
   • 内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页。
     （解释：InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。）
   • MySQL 认为系统空闲的时候，也会刷一些脏页
   • MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上

5. 为什么刷脏会导致SQL变慢呢？

   • redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。
   • 一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长。

order by 文件排序会导致查询变慢 （Using filesort）

explain select name,age,city from staff where city = '深圳' order by age limit 10;
如果Extra中含有Using filesort，就表示它用到了文件排序。

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。

• rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。
  如何优化？ 建立索引！

拿不到锁或者死锁。查询是相当的慢。。

delete in (子查询) 此时不走索引，也慢。

group by 使用临时表也会慢。

参考文章

1. 为什么 SQL 语句使用了索引，但却还是慢查询？
2. 如何处理 MySQL 的慢查询问题？

创建测试数据的存储过程函数

-- 创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `GenerateRandomChineseCharacters`(IN length INT)
BEGINDECLARE i INT DEFAULT 0;DECLARE char_set VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';WHILE i < length DOINSERT INTO zls_test (name, age) VALUES (CONCAT('张', SUBSTRING(char_set, FLOOR(RAND() * 52) + 1, 1), FLOOR(RAND() * 1000000)),FLOOR(RAND() * 80) + 1);SET i = i + 1;END WHILE;END
-- 执行200万次
CALL GenerateRandomChineseCharacters(2000000)   -- 会很慢  200万的话也测试不出来什么效果，建议没必要执行。

这篇关于为什么SQL语句用了索引，还是会查询的很慢？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---