暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL 索引那点事 . . . . . .

MySQL 索引那点事 . . . . . .

ClickHouse周边 2021-09-08
460

1.介绍

      索引用于快速找出在某个列中特定值的行。  大大提高MySQL的检索速度。如果不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多；那如果表中查询的列有索引，MySQL能够快速根据位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。        
        比如：汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
2. 索引种类及查找算法演变
    BTREE      ----> InnoDB
    RTREE      ----> MongoDB
    HASH       ----> Redis
    FULLTEXT   ----> ES

            二分法   ---> 二叉树 ---> 红黑树 ---> Balance Ttree(平衡多叉树，简称为BTREE)

    3. BTREE查找算法演变
      B-TREE ：  普通 BTREE
      B+TREE ：  叶子节点双向指针
      B*TREE ：  枝节点的双向指针
      4. MySQL中如何使用BTREE




      4.1 聚簇（区）索引
      ⭐ extent ？ 
             区，默认是连续的64个page，大小默认1M。区是聚簇索引分配叶子节点空间的最小单元。
      ⭐ IOT组织表 ？
             聚簇索引组织表。表的数据行都是（逻辑）有序的存储到聚簇索引中的。按照聚簇索引组织存储（叶子节点）。
      ⭐ MySQL 聚簇索引生成说明：

              Each InnoDB
       table has a special index called the clustered index that stores row data. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, it is important to understand how InnoDB
       uses the clustered index to optimize the common lookup and DML operations.

      • When you define a PRIMARY KEY
         on a table, InnoDB
         uses it as the clustered index. A primary key should be defined for each table. If there is no logical unique and non-null column or set of columns to use a the primary key, add an auto-increment column. Auto-increment column values are unique and are added automatically as new rows are inserted.

      • If you do not define a PRIMARY KEY
         for a table, InnoDB
         uses the first UNIQUE
         index with all key columns defined as NOT NULL
         as the clustered index.

      • If a table has no PRIMARY KEY
         or suitable UNIQUE
         index, InnoDB
         generates a hidden clustered index named GEN_CLUST_INDEX
         on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB
         assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion.

      上述说明：
              1.如果表中设置了主键（例如ID列），自动根据ID列生成聚簇索引；
              2.如果没有设置主键，自动选择第一个NN唯一键的列作为聚簇索引；
              3.自动生成隐藏（6字节row_id）的聚簇索引。InnoDB表中一定是有聚簇索引。

              也就是说，InnoDB表中一定是有聚簇索引。

      附录：MySQL 聚簇索引生成说明官档

      https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
      ⭐ 功能： 
      聚簇索引组织表。将逻辑上连续的数据，在磁盘存储时也是物理（同一个区内）上连续的。
              1.录入数据时，按照聚簇索引组织存储数据，磁盘上有序存储数据行；
              2.加速查询。基于ID作为条件的判断查询。
      ⭐ 聚簇索引btree构建过程：

       仁义同学出品
             a. 叶子节点：存储数据行时就是有序的，直接将数据行的page作为叶子节点（相邻的叶子结点，有双向指针）；
              b. 枝节点 ：提取叶子节点ID的范围+指针，构建枝节点（相邻枝节点，有双向指针）；
              c. 根节点 ：提取枝节点的ID的范围+指针，构建根节点。

      4.2 辅助索引
      ⭐ MySQL 辅助索引生成说明：
              Indexes other than the clustered index are known as secondary indexes. In InnoDB
      , each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB
       uses this primary key value to search for the row in the clustered index.
              If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.
      上述说明：
             需要人为创建辅助索引，将经常作为查询条件的列创建辅助索引，起到加速查询的效果。MySQL 辅助索引存储的是ID主键值 + 辅助索引列值+指针。

      附录：MySQL 二级索引生成说明官档

      https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
      ⭐ 功能： 
      按照辅助索引列，作为查询条件时。
             1. 查找辅助索引树，得到ID值；
             2. 拿着ID值回表（聚簇索引）查询聚簇索引和辅助索引。
      ⭐ 辅助索引btree构建过程：

       仁义同学出品
              a. 叶子节点：提取主键（ID）+辅助索引列，按照辅助索引列进行从小到大排序后，生成叶子节点。（相邻的叶子结点，有双向指针）；
              b. 枝节点 ：提取叶子节点辅助索引列的范围+指针，构建枝节点（相邻枝节点，有双向指针）；
              c. 根节点 ：提取枝节点的辅助索引列的范围+指针，构建根节点 。
      5. 使用索引考虑的事项
      ⭐ 回表是什么？ 
             按照辅助索引列，作为查询条件时，先查找辅助索引树得到ID，再到聚簇索引树查找数据行的过程。
      ⭐ 回表会带来什么问题？ 
              IO量多、IO次数多、随机IO会增多、SQL层和engine交互多次。

      ⭐ 怎么减少回表？

              1. 辅助索引不能够完全覆盖查询结果，可以使用联合索引；

              2. 尽量让查询条件精细化，尽量使用唯一值多的列作为查询条件；

              3. 优化器：MRR（Multi-Range-Read），ICP（index condition pushdown） ，锦上添花的功能。  

        mysql> select @@optimizer_switch;  
        mysql> set global optimizer_switch='mrr=on';
                功能：
                1. 辅助索引查找后得到ID值，进行自动排序  
                2. 一次性回表，很有可能受到B+TREE中的双向指针的优化查找。
        ⭐ 索引树高度的影响因素？如何解决？
                a. 高度越低越好；
                b. 数据行越多，高度越高；
            1. 可以采用分区表。一个实例里管理。     
            2. 按照数据特点，进行归档表（pt-archiver）   
            3. 分布式架构（针对海量数据、高并发业务主流方案)
                c.主键规划不宜过长，可以使用自增数字列。
                d. 列值长度越长，数据量大的话，会影响到高度（可以使用前缀索引）；
                e. 选择合适的、简短的、足够的数据类型。
        例如：存储人名，char(20)和varchar(20)的选择哪一个好。     
                    a. 站在数据插入性能角度思考，应该选：char     
                    b. 从节省空间角度思考,应该选：varchar
                    c. 从索引树高度的角度思考，应该选：varchar   
        综合上述：建议使用varchar类型存储变长列值。 

        近期文章推荐：
        MySQL （不）同版本多实例
        MySQL 8.0 常见锁介绍（一）
        EXPLAIN 之 key_len 计算




        mysql
        文章转载自ClickHouse周边，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

        评论