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

2024-08-22 00:20
文章标签 sql 查询 索引 语句 database 很慢

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

文章目录

        • 慢查询和索引
        • 索引的过滤性
        • 索引的扫描行数
        • 什么情况下索引失效？
        • limit深度分页会导致慢查询
        • 单表数据量太大也会导致慢查询。
        • JOIN或者子查询过多也会造成慢查询。
        • IN后面括号内的元素过多也会造成慢查询。
        • 数据库在刷脏页也会造成慢查询。
        • order by 文件排序会导致查询变慢 （Using filesort）
        • 拿不到锁或者死锁。查询是相当的慢。。
        • delete in (子查询) 此时不走索引，也慢。
        • group by 使用临时表也会慢。
        • 参考文章
        • 创建测试数据的存储过程函数

慢查询和索引

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

  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语句用了索引，还是会查询的很慢？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1094702。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
活用c4d官方开发文档查询代码

活用c4d官方开发文档查询代码

当你问AI助手比如豆包，如何用python禁止掉xpresso标签时候，它会提示到 这时候要用到两个东西。https://developers.maxon.net/论坛搜索和开发文档 比如这里我就在官方找到正确的id描述 然后我就把参数标签换过来
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...
Java 连接Sql sever 2008

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2