• Mysql - 索引

    目录

    索引概述

     索引结构

     B Tree​编辑

     B+Tree

    B+Tree索引

     Hash索引

     思考 -- Innodb为什么选择 B+Tree

    索引分类

     思考

    索引的语法

     SQL 性能分析

    ​编辑

    慢查询日志

     profile

    ​编辑

    show profiles

    show profile for query query_id;

    explain

    索引使用

     最左前缀法则

    思考

     范围查询

    索引列运算

    ​编辑 字符串不加引号

    模糊查询

     OR连接 

    数据分布影响

    ​编辑

     SQL提示

     覆盖索引

    图形实例

     前缀索引

     单列索引 与 联合索引

     索引使用的原则

     总结

    索引概述

     

     索引结构

     

     

     B Tree

    Btree特点：
    • 5阶的B树，每一个节点最多存储4个key，对应5个指针。
    • 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
    • 在B树中，非叶子节点和叶子节点都会存放数据。

    B-Tree Visualization

    插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88
    120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

     B+Tree

    我们可以看到，两部分：
    绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
    红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。
    插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88
    120 268 250 。然后观察一些数据插入过程中，节点的变化情况
    B+Tree 与 B-Tree 相比，主要有以下三点区别：
    • 所有的数据都会出现在叶子节点。
    • 叶子节点形成一个单向链表。
    • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

    B+Tree索引

     Hash索引

     

     思考 -- Innodb为什么选择 B+Tree

    二叉树一个节点只能有2个子节点，所以层级深，所以查询销量低

    对于与B 树，非叶子节点也能保存数据，每一组节点都保存在页上(一个页的大小只有16K)，如果节点既要保存数据也有指针，则会减少指针的数量，导致增加树的高度，降低了查询速度。

    索引分类

     聚集索引下面挂的是行数据， 二级索引挂的是 行的id。

    当此时通过name查询行数据时，先在二级索引中查到id，再去聚集索引中根据id查询行数据，被称之为 回表查询

     思考

     根据Id直接查询聚集索引获取行数据，根据name，先查询二级索引获取id，在去聚集索引中查询行数据，前者更快

     因为一个page 16K，一个主键bigint占8 byte，指针占6 byte, 指针数量比存放数量大1，

    n * 8 + (n+1)* 6 = 16 * 1024 ，则一个page可以存储1170个节点，1171个指针，

    高度为2时那就连接1171个 page，一个page 16k，一行数据为1k，则一个page可以存储16行数据，所以 1171 * 16 = 18736行数据

    高度为3时，那2级就连接1171个 page，3级1171*1171个，一个page 16k，一行数据为1k，则一个page可以存储16行数据，所以 1171 * 1171 * 16 = 21939856行数据

    索引的语法

     

    1. create index idx_user_name on tb_user(name);
    2.  
    3. create unique index idx_user_phone on tb_user(phone);
    4.  
    5. create index idx_user_profession_age_status on tb_user(profession,age,status);
    6.  
    7. create index idx_user_email on tb_user(email);
    8.  
    9. show index from tb_user;
    10.  
    11. drop index idx_user_email on tb_user;

     SQL 性能分析

    查看此数据库是不是已查询为主，如果是已查询为主，才有优化的必要。

    1. -- 查询服务状态 7个_
    2. show global status like 'Com_______';

    慢查询日志

    1. -- 慢查询日志是否开启 默认是关闭的
    2. show variables like 'slow_query_log';

    1. # 开启MySQL慢日志查询开关
    2. slow_query_log=1
    3. # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    4. long_query_time=2

    慢查询日志路径：/var/lib/mysql/localhost-slow.log

     profile

    查看数据库是否支持show profiles

    SELECT @@have_profiling ;

    查看profiling是否关闭，0表示关闭，需要将其设置成1

    1. -- 查询profiling是否开启
    2. select @@profiling;
    3. -- 如果是0,,将其设置成1
    4. set profiling = 1;

    show profiles

    -- 查看每一条 SQL 的耗时基本情况 
    show profiles;

     show profile for query query_id;

    -- 查看指定 query_id 的 SQL 语句各个阶段的耗时情况 
    show profile for query 16;

    show profile cpu for query 16;

    explain

    1. desc select * from tb_user where id = 1;
    2. explain select * from tb_user where id = 1;

     展现的列以及相关含义，主要关注高亮列信息。

    id
    select 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序
    (id 相同，执行顺序从上到下； id 不同，值越大，越先执行 ) 。
    select_type
    表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接
    或者子查询）、 PRIMARY （主查询，即外层的查询）、
    UNION （ UNION 中的第二个或者后面的查询语句）、
    SUBQUERY （ SELECT/WHERE 之后包含了子查询）等
    type
    表示连接类型，性能由好到差的连接类型为 NULL 、 system 、 const 、 eq_ref、 ref 、 range 、 index 、 all 。
    ----------------------------------------------------------------------------------
    NULL ： 一般是没有查询表的情况        例如：select curdate()
    system : 表中只有一行数据或者空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是innoDB引擎表，type列在这个情况下通常是all或者index。
    const : 使用主键或者unique索引
    ref ： 使用非唯一索引
    range ：索引范围查询，常见于使用=，<>，>=，<，<=，is null，between，in()或者like等运算符的查询中
    Index ： 索引全表扫描，把索引从头到尾扫一遍
    all  ： 全表扫描，性能最差。
    possible_key
    显示可能应用在这张表上的索引，一个或多个。
    key
    实际使用的索引，如果为 NULL ，则没有使用索引。
    key_len
    表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长
    度，在不损失精确性的前提下， 长度越短越好 。
    rows
    MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值， 可能并不总是准确的。
    filtered
    表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
    Extra额外信息

    索引使用

     最左前缀法则

    在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession， age，status。

    对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效。 接下来，我们来演示几组案例，看一下

    具体的执行计划：

    以上的这三组测试中，我们发现只要联合索引最左边的字段 profession 存在，索引就会生效，只不
    过索引的长度不同。 而且由以上三组测试，我们也可以推测出 profession 字段索引长度为 47 、 age
    字段索引长度为 2 、 status 字段索引长度为 5 。

    而通过上面的这两组测试，我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引 最左边的列profession 不存在
    上述的 SQL 查询时，存在 profession 字段，最左边的列是存在的，索引满足最左前缀法则的基本条
    件。但是查询时，跳过了 age 这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索 引的长度就是47 。

    思考

    当执行 SQL 语句 :
    explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程 ' ； 时，是否满足最左前缀法则，走不走上述的联合索引，索引长度？

     可以看到，是完全满足最左前缀法则的，索引长度54，联合索引是生效的。

    注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是
    第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关

     范围查询

    当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为49，就说明范围查询右边的status字 段是没有走索引的。

    当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。 所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

    索引列运算

     

    当根据phone字段进行函数运算操作之后，索引失效。

     字符串不加引号

     我们会明显的发现，如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

    模糊查询

    经过上述的测试，我们发现，在 like 模糊查询中，在关键字后面加 % ，索引可以生效。而如果在关键字前面加了% ，索引将会失效。

     OR连接 

    由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

    注意:虽然上例中OR两侧的索引都存在时，执行执行计划显示会使用到索引，但是这个是不一定，根据本地多次测试，优化器是否使用索引似乎和查询字段值的重复率有关,具体没有找到相关文档作证，只能列举一下情况，以供大家参考

    当前表的索引情况: id主键，phone 唯一索引，age普通索引

     当查询id or phone时，会走索引

     当查询id or age=33时: 会走索引

    age=33所占的比例为 :

     当查询id or age=23时:不走索引

    age=23所占的比例为:

    由上可见，当OR两侧字段都存在索引时，是否使用索引查询或者全部扫描，优化器内部会根据数据分布情况来判断，不是像其他博客上面所说的以偏概全 OR一定使索引失效，或者一定会使用索引。

    以上是字段都是单独索引时，如果OR查询条件

    1.一侧是单独索引，一侧是联合索引，并且是最左前的字段

    和两侧的单独索引情况一样。

    2.当一个测单独索引，一侧是联合索引，非最左前字段

    相当于 一侧是单独索引，一侧是没有索引，则一定不会走索引

    3.当一侧是联合索引的最左前字段，一侧是联合索引的其他字段

     可以发现都不会走索引

    数据分布影响

    经过测试我们发现，相同的 SQL 语句，只是传入的字段值不同，最终的执行计划也完全不一样，这是为 什么呢？
    就是因为 MySQL 在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃 索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不 如走全表扫描来的快，此时索引就会失效。
    我们再来看看 is null 与 is not null 操作是否走索引

     SQL提示

     执行SQL，创建profession的单列索引：create index idx_user_pro on tb_user(profession);

    那么，我们能不能在查询的时候，自己来指定使用哪个索引呢？ 答案是肯定的，此时就可以借助于 MySQL的 SQL 提示来完成。 接下来，介绍一下 SQL 提示。
    SQL提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
    1.  use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。 
      explain select * from tb_user use index(idx_user_pro) where profession = '软件工 程';
    2. ignore index ： 忽略指定的索引。 
      explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工 程';
    3. force index ： 强制使用索引。 
      explain select * from tb_user force index(idx_user_pro) where profession = '软件工 程';

     覆盖索引

     尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并

    且需要返回的列，在该索引中已经全部能够找到 。 

    1. explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ; 
    2. explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ; 
    3. explain select id,profession,age, status, name from tb_user where profession = '软 件工程' and age = 31 and status = '0' ; 
    4. explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

     从上述的执行计划我们可以看到，这四条SQL语句的执行计划前面所有的指标都是一样的，看不出来差 异。但是此时，我们主要关注的是后面的Extra，前面两天SQL的结果为 Using where; Using Index ; 而后面两条SQL的结果为: Using index condition 。

    Extra        含义
    Using where; Using Index
    查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
    Using index condition
    查找使用了索引，但是需要回表查询数据

    因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段
    profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主
    键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引
    直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表
    查询（除非是根据主键查询，此时只会扫描聚集索引）。

    图形实例

     

    答案 : 针对于 username, password 建立联合索引 , sql 为 : create index idx_user_name_pass on tb_user(username,password); 这样可以避免上述的SQL 语句，在查询的过程中，出现回表查询。

     前缀索引

    create index idx_xxxx on table_name(column(n)) ;

     单列索引 与 联合索引

     索引使用的原则

     总结

  • 相关阅读:
    minio拉取的时候报错了
    基于 Next.js实现在线Excel
    搞定ESD（一）：静电放电测试标准解析
    Java大厂面试题第2季
    4461. 范围分区
    多线程与锁
    bash和sh区别
    vscode设置代码模板
    Web3 的 10 大应用
    vivo 场景下的 H5无障碍适配实践
  • 原文地址：https://blog.csdn.net/qq_33753147/article/details/126749392