MYSQL-索引

Learn1993
565 阅读8分钟

索引

索引是提升查询速度的一种数据结构。MySQL8.0中, InnoDB存储引擎支持的索引有B+树索引， 全文索引, R树索引。

B+树索引结构

特点是: 基于磁盘的平衡树，但树特别矮，通常有3~4层，能存放千万到上亿的排序数据。

B+ 树索引由根节点(root node), 中间节点(non leaf node), 叶子节点(leaf node)组成。

所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。索引是对记录进行排序， 高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。

可随着插入 B+ 树索引的记录变多，1个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2，当 B+ 树的高度大于等于 2 时，根节点和中间节点存放的是索引键对，由（索引键、指针）组成。索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。

优化B+ 树索引的插入性能

开销在于B+ 树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况:

  • 数据顺序(逆序)插入: 维护代价非常小，如自增ID的插入，时间的插入。
  • 数据无序插入: B+树为了维护排序，对页进行分裂，旋转灯开销较大的操作，另外，随机写性能不如顺序写，磁盘性能受到较大影响，如昵称索引

B+ 树索引的设计与管理

我们可以通过查询mysql.innodb_index_stats查看每个索引的大致情况:

SELECT table_name, index_name, stat_name, stat_value, stat_description FROM innodb_index_stat WHERE table_name='orders' and index_name ='PRIMARY'

可以通过查询sys.schema_unused_indexes查看哪些索引一直未被使用过

SELECT * FROM schema_unused_indexes WHERE object_schema != 'performance_schema'

MYSQL8.0版本推出了索引不可见(invisible)功能， 在删除废弃索引前，可以将索引设置为对优化器不可见，观察业务是否有影响

ALTER TABLE T1	
ALTER INDEX ID_NAME INVISIBLE/VISIBLE

索引组织表

数据存储有堆表和索引组织表两种方式。堆表中的数据无序存放，数据的排序完全依赖索引。而索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。在索引组织表中，数据即索引，索引即数据。

二级索引通过主键索引进行再一次查询的操作叫作“回表”。索引组织表这样的二级索引设计有一个非常大的好处：若记录发生了修改，则其他索引无须进行维护，除非记录的主键发生了修改。

由于每个二级索引都包含了主键值，查询通过主键值进行回表，所以在设计表结构时让主键值尽可能的紧凑，为的就是能提升二级索引的性能.

设计主键时，有两点设计原则:

  • 要比较顺序， 对聚集索引性能友好
  • 尽可能紧凑，对二级索引性能和存储友好。

函数索引

MySQL 5.7 版本开始，MySQL 就开始支持创建函数索引 （即索引键是一个函数表达式）。函数索引有两大用处：

  • 优化业务 SQL 性能；
ALTER TABLE User ADD INDEX idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));
EXPLAIN SELECT * FROM User WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01'
  • 配合虚拟列（Generated Column）。
CREATE TABLE UserLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_cellphone(cellphone)
);

cellphone 就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。这样做得好处是在写 SQL 时可以直接使用这个虚拟列，而不用写冗长的函数.

-- 不用虚拟列
SELECT  *  FROM UserLogin
WHERE loginInfo->>"$.cellphone" = '13918888888'
-- 使用虚拟列
SELECT  *  FROM UserLogin 
WHERE cellphone = '13918888888'

组合索引

组合索引能避免额外排序，避免回表，性能提升。

通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index）。

组合索引三大优势:

  • 覆盖多个查询条件
  • 避免SQL额外排序，提升性能
  • 实现索引覆盖功能，提升查询效率

CBO工作原理

如何选择索引

优化器的选择基于成本，哪个索引低，优先使用哪个索引。

SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。

Cost  = Server Cost + Engine Cost = CPU Cost + IO Cost

CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成

IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销.

表 server_cost、engine_cost 则记录了对于各种成本的计算, 表 server_cost 记录了 Server 层优化器各种操作的成本，这里面包括了所有 CPU Cost.

  • disk_temptable_create_cost：创建磁盘临时表的成本，默认为20。
  • disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为0.5。
  • key_compare_cost：索引键值比较的成本，默认为0.05，成本最小。
  • memory_temptable_create_cost：创建内存临时表的成本：默认为1。
  • memory_temptable_row_cost：内存临时表中每条记录的成本，默认为0.1。
  • row_evaluate_cost：记录间的比较成本，默认为0.1。

表 engine_cost 记录了存储引擎层各种操作的成本，这里包含了所有的 IO Cost，具体含义如下。

  • io_block_read_cost：从磁盘读取一个页的成本，默认值为1。
  • memory_block_read_cost：从内存读取一个页的成本，默认值为0.25。

所有的成本都是可以修改的，SQL如下L

INSERT INTO engine_cost(engine_name,device_type,cost_name,cost_value,last_update,comment) 
VALUES ('InnoDB',0,'io_block_read_cost',12.5,CURRENT_TIMESTAMP,'Using HDD for InnoDB');
FLUSH OPTIMIZER_COSTS;

业务实践

字段o_orderstatus有三个值， 但是订单状态时有数据倾斜的，可以建立索引.

ALTER TABLE orders ADD INDEX idx_orderstatus(o_orderstatus)

此时， 优化器认为订单状态为P的占用1/3的数据，使用全表扫描，避免二级索引回表的效率更高。但实际上此时使用索引效率更高。这种情况下我们可以利用Mysql8.0的直方图功能， 让优化器知道数据的分布，从而更好地选择执行计划。

ANALYZE TABLE orders UPDATE HISTOGRAM ON o_orderstatus;

此时，查询就会用到索引了。

JOIN连接

算法

MySQL8.0支持两种JOIN算法用于表关联

查询数据量较小、语句相对简单，大多使用索引连接表之间的数据。这种情况下，优化器大多会用 Nested Loop Join 算法；而 查询数据量较大，关联表的数量非常多，所以用 Hash Join 算法，直接扫描全表效率会更高。

Nested Loop Join

表关联使用索引进行匹配

驱动表R 中通过 WHERE 条件过滤出的数据会在表 S 对应的索引上进行一一查询。如果驱动表 R 的数据量不大，上述算法非常高效。

对于 Left Join 来说，驱动表就是左表 R；Right Join中，驱动表就是右表 S。这是 JOIN 类型决定左表或右表的数据一定要进行查询。但对于 INNER JOIN，谁需要查询的数据量越少，谁就是驱动表。

优化器一般认为，通过索引进行查询的效率都一样，所以 Nested Loop Join 算法主要要求驱动表的数量要尽可能少。

Hash Join

用于两张表之间连接条件没有索引的情况.

Hash Join会扫描关联的两张表：

  • 首先会在扫描驱动表的过程中创建一张哈希表；
  • 接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录。

较小的表作为驱动表。如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘。

子查询

IN和EXIST 执行计划一样，效率一样。

依赖子查询问题

Mysql8.0之前，子查询会看到DEPENDENT SUBQUERY 的提示，这表示是一个依赖子查询，子查询需要依赖外部表的关联。DEPENDENT SUBQUERY 执行速度可能非常慢，大部分时候需要手动把它转化成两张表之间的连接。

对于依赖子查询的优化，就是要避免子查询由于需要对外部的依赖，而需要对子查询扫描多次的情况。所以可以通过派生表的方式，将外表和子查询的派生表进行连接，从而降低对于子查询表的扫描，从而提升 SQL 查询的性能。

分区表

分区表就是把物理表结构相同的几张表，通过一定算法，组成一张逻辑大表。MySQL 数据库支持的分区函数类型有 RANGE、LIST、HASH、KEY、COLUMNS。无论选择哪种分区函数，都要指定相关列成为分区算法的输入条件，这些列就叫“分区列”。另外，在 MySQL 分区表中，主键也必须是分区列的一部分，不然创建分区表时会失败。

CREATE TABLE t (
    a INT,
    b INT,
    c DATETIME,
    d VARCHAR(32),
    e INT,
    PRIMARY KEY (a,b,c),
    KEY idx_e (e)
)
partition by range columns(c) (
    PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
    PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
    PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
    PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);

唯一索引

在 MySQL 数据库中，分区表的索引都是局部，而非全局。也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报错.正因为唯一索引包含了分区列，唯一索引也就变成仅在当前分区唯一，而不是全局唯一了。

分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理.

ALTER TABLE orders_par TRUNCATE PARTITION p1998
avatar
文章
阅读
粉丝