MySQL 索引

最新推荐文章于 2023-07-20 19:49:48 发布

weixin_33758863

最新推荐文章于 2023-07-20 19:49:48 发布

阅读量73

点赞数

原文链接：http://blog.51cto.com/gaoquan/1732823

版权

*************************索引分类*************************

逻辑分类：
Single column indexes单列索引
Combined Indexes，Multiple-Column Indexes 多列索引
Unique 唯一索引
NonUnique 非唯一索引

物理存储分类：
clustered index 聚簇索引(主键和数据存放到一起)
not-clustered index非聚簇索引（普通索引）

数据结构分类：
BTREE索引，是B+树（B+ Tree）的简写
HASH索引(InnoDB是自适应哈希索引)，只用于HEAP表
空间索引(5.6以前Myisam存储引擎支持，5.7InnoDB引入空间索引)，很少用
Fractal Tree索引，用于TokuDB表

全文索引（5.6.10版本开始支持）

*************************B+TREE索引*************************

演化：二分查找->平衡二叉树->B树->b+树

介绍：所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接

插入操作：必须保证插入后叶子节点记录依然排序，需要考虑的几种情况

Leaf Page满	Index Page满	操作
NO	NO	直接将记录插入叶子节点
YES	NO	1）拆分Leaf Page 2）将中间的节点放入到Index Page中 3）小于中间节点的记录放左边 4）大于或者等于中间节点的记录放到右边
YES	YES	1）拆分Leaf Page 2）小于中间节点的记录放左边 3）大于或者等于中间节点的记录放到右边 4）拆分IndexPage 5）小于中间节点的记录放左边 6）大于中间节点的记录放右边 7）中间节点放入上一层IndexPage
YES	左右页节点未满	旋转，首先检查左兄弟节点，左兄弟节点如果未满，则移到左兄弟节点，这样做的好处是没必要做拆分

删除操作：使用填充因子来控制树的删除变化，50%是填充因子可设的最小值，删除操作同样必须保证删除后叶子节点的排序性，删除操作的几种情况：

叶子节点小于填充因子	中间节点小于填充因子	操作
NO	NO	直接将记录从叶子节点删除，如果该节点还是Index page的节点，那么则用该页节点的右节点替代
YES	NO	合并叶子节点和它的兄弟节点，同时更新Index Page
YES	YES	1）合并叶子节点和他的兄弟节点 2）更新index page 3）合并index page和它的兄弟节点

*************************B+TREE索引分类*************************

聚集索引：按照每张表的主键构造一颗B+TREE，叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。在InnoDB表中，可使用py_innodb_page_info工具来分析页，可看出索引是单独存放的一个页，该页储存键值以及指向数据页的偏移量。

非聚集索引：叶子节点不包含行记录的全部数据，叶子节点除了包含键值外，每个叶子节点中的索引行中还包含了聚集索引键，通过该聚集索引键可获取行内容。

*************************B+TREE索引管理*************************

创建索引语法：

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

[index_type]

ON tbl_name (index_col_name,...)

[index_option]

[algorithm_option | lock_option] ...

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT 'string'

algorithm_option:

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

或者

ALTER TABLE tbl_name

| ADD {INDEX|KEY} [index_name]

[index_type] (index_col_name,...) [index_option] ...

| DROP PRIMARY KEY

| DROP {INDEX|KEY} index_name

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT 'string'

查看表上索引信息：

mysql> show index from t1\G;

*************************** 1. row ***************************

Table: t1

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 9

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: t1

Non_unique: 1

Key_name: idx_name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 9

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

2 rows in set (0.02 sec)

ERROR:

No query specified

命令show index结果中每列的含义：

1、Table 表的名称。

2、 Non_unique 如果索引不能包括重复词,则为0,如果可以则为1。

3、 Key_name 索引的名称

4、 Seq_in_index 索引中的列序列号，从1开始。

5、 Column_name 列名称。

6、 Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

7、Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

8、Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

9、 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。

10、 Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。

11、 Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

12、 Comment 多种评注，您可以使用db_name.tbl_name作为tbl_name FROM db_name语法的另一种形式。这两个语句是等价的：

*************************B+TREE创建和删除的四种方式*************************

1、传统索引创建方式

首先创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结构
然后把原始表中数据导入到临时表
删除原表
最后把临时表重新命名为原来的表名

总结：对于大表的索引创建和删除，需要较长时间，若是有大量事务需要访问正在被修改的表，意味着此时数据库不可用

2、FIC索引创建方式(inplace)

对于辅助索引的创建需要加一个S锁，创建过程不需要重建表，删除索引时只需要更新内部视图，并将付诸索引的空间标记为可用，同时删除MySQL内部视图上对该表的索引定义。

缺点：创建过程中只能对该表进行读操作，另外该操作只限定于辅助索引，对于主键的创建和修改同样需要重建一张表。

3、Online Schema Change(OSC)

init 初始化阶段，检查表是否有主键，是否有外键或者触发器，如果无主键或者有外键和触发器，那么直接报错
createCopyTable 创建和原始表结构一样的新表
alterCopyTable 对创建的新表进行alter table操作，如添加索引或者列
createDeltasTable 创建deltas表，该表的作用是为下一步创建的触发器所使用之后对原表的所有的DML操作都会被记录到deltas表中
createTriggers 对原表创建INSERT,DELETE,UPDATE操作的触发器，触发操作产生的记录被写入到deltas表中
startSnpshotXact 开始OSC操作的事务
selectTableIntoOutfile 将原表中的数据写入到新表中。为了减少对原表的锁定时间，这里通过分片将数据输出到多个外部文件，然后将外部文件的数据导入到copy表中，分片的大小可以指定，默认为500000
dropNCIndexs 在导入到新表前，删除新表中所有的辅助索引
loadCopyTable 将导出的分片文件导入到新表
replayChanges 将OSC过程中原表的DML操作的记录应用到新表中，这些记录被保存在deltas
recreateNCIndexes 重建辅助索引
replayChanges 再次回放DML日志的，这些日志是在创建辅助索引期间产生的
swapTables 原表和新表交换名字，速度很快

缺点：修改的表一定要有主键，不能有外键和触发器，如果Binlog复制模式不是row模式，那么可能主从复制有问题，因为过程中会有set sql_bin_log=0操作。

4、Online DDL

注（何登成大神博客）

online方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键等；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(修改记录格式)，比如添加、删除列、修改列默认值等；另外一类是只需要修改表的元数据，比如添加、删除索引、修改列名等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式。online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

Prepare阶段：

创建新的临时frm文件持有EXCLUSIVE-MDL锁，禁止读写根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild) 更新数据字典的内存对象分配row_log对象记录增量生成新的临时ibd文件

ddl执行阶段：

降级EXCLUSIVE-MDL锁，允许读写扫描old_table的聚集索引每一条记录rec 遍历新表的聚集索引和二级索引，逐一处理根据rec构造对应的索引项将构造索引项插入sort_buffer块将sort_buffer块插入新的索引处理ddl执行过程中产生的增量(仅rebuild类型需要)

commit阶段

升级到EXCLUSIVE-MDL锁，禁止读写重做最后row_log中最后一部分增量更新innodb的数据字典表提交事务(刷事务的redo日志) 修改统计信息 rename临时idb文件，frm文件变更完成

关键函数堆栈

拷贝数据

row_merge_build_indexes

row_merge_read_clustered_index

//拷贝全量

{

遍历老表的聚集索引

row_build //创建一个row

row_merge_buf_add //将row加入到sort_buffer

row_merge_insert_index_tuples //插入到新表(聚集索引+二级索引)

}

row_log_table_apply // 对于rebuild类型，处理增量

{

row_log_table_apply_insert //以insert为例

row_log_table_apply_convert_mrec //将buf项转为tuple

{

插入聚集索引 // row_ins_clust_index_entry_low

插入二级索引 // row_ins_sec_index_entry_low

}

修改表数据字典

commit_try_norebuild，commit_try_rebuild

常见的ddl操作

类型

并发DML

算法

备注

添加/删除索引

Yes

Online(no-rebuild)

全文索引不支持

修改default值

修改列名

修改自增列值

Yes

Nothing

仅需要修改元数据

添加/删除列

交换列顺序

修改NULL/NOT NULL

修改ROW-FORMAT

添加/修改主键

Yes

Online(rebuild)

由于记录格式改变，需要重建表

修改列类型

Optimize table

转换字符集

Copy

需要锁表，不支持online

若干问题

1.如何实现数据完整性

使用online ddl后，用户心中一定有一个疑问，一边做ddl，一边做dml，表中的数据不会乱吗？这里面关键部件是row_log。row_log记录了ddl变更过程中新产生的dml操作，并在ddl执行的最后将其应用到新的表中，保证数据完整性。

2.online与数据一致性如何兼得

实际上，online ddl并非整个过程都是online，在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁止读写；而在整个ddl执行阶段，允许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短，因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁止读写，主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存的字典；Commit阶段，禁止读写后，重做最后一部分增量，然后提交，保证数据一致。

3.如何实现server层和innodb层一致性

在prepare阶段，server层会生成一个临时的frm文件，里面包含了新表的格式；innodb层生成了临时的ibd文件(rebuild方式)；在ddl执行阶段，将数据从原表拷贝到临时ibd文件，并且将row_log增量应用到临时ibd文件；在commit阶段，innodb层修改表的数据字典，然后提交；最后innodb层和mysql层面分别重命名frm和idb文件。

转载于:https://blog.51cto.com/gaoquan/1732823