程序员五年状态.jpg
目录
- MySQL索引是什么？
- 为什么要使用索引？
- 创建，查看，删除索引的方式
- 创建索引的三种方式：
- 查看索引的两种方式：
- 删除索引的两种方式：
- MySQL索引分类
- MySQL索引使用原则
- B-Tree索引的底层实现是什么？
-
- BTree和B+Tree结构在存储数据上的区别？
- <1> BTree 和 B+Tree结构上的明显区别：
- <2> BTree 和 B+Tree在存储数据上的区别：
-
- B+Tree为什么适合做索引？为什么不用红黑树或者BTree呢？
- <1> 为什么不用红黑树？
- <2> 为什么不用BTree而用B+Tree做索引？
- 归纳总结
-
- 什么是哈希索引？
- <1> 哈希索引的数据结构：
- <2> 哈希索引的限制：
- 什么是自适应哈希索引？
- 什么是聚簇索引，非聚簇索引？
- <1> InnoDB的聚簇索引
- <2> 聚簇索引和非聚簇索引的区别？
- <3> 聚簇索引的优缺点
- 什么是覆盖索引？
- 主键索引，唯一索引，普通索引
MySQL索引是什么？
MySQL索引(键(key)).png
为什么要使用索引？（即索引的优缺点是什么？）
索引.png
创建，查看，删除索引的方式
1. 创建索引的三种方式：
第一种：建表时创建索引
CREATE TABLE student (
id int unsigned not null auto_increment,
num int unsigned not null,
name varchar(255),
primary key(id),
unique key uk_num(num),
index idx_name(name)
) ENGINE=InnoDB;
第二种：使用create index创建索引：能创建普通索引和唯一索引，不能创建主键索引
CREATE INDEX 索引名 ON 表名(column_list);
CREATE UNIQUE INDEX 索引名 ON 表名(column_list);
举例：mysql> CREATE INDEX idx_name ON student(name);
第三种：使用alter table创建索引
ALTER TABLE 表名 ADD INDEX [索引名](column_list); -- 索引名可选，不写的话，默认索引名等于列名
ALTER TABLE 表名 ADD UNIQUE [索引名](column_list);
ALTER TABLE 表名 ADD PRIMARY KEY(column_list);
说明：索引名可选，不写的话，默认生成的索引名等于列名；如果是多列的联合索引，默认生成的索引名等于第一列的名字
举例：
mysql> ALTER TABLE student ADD INDEX idx_name(name);
mysql> ALTER TABLE student ADD UNIQUE (num); -- 默认生成的唯一索引名字为num
2. 查看索引的两种方式：
第一种：SHOW INDEX FROM 表名;
第二种：SHOW KEYS FROM 表名;
3. 删除索引的两种方式：
第一种：DROP INDEX 索引名 ON 表名;
第二种：ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY;
说明：
表只可能有一个primary key主键索引，所以不需要指定索引名
如果没有创建primary key主键索引，但表有一个或多个unique索引，则将删除第一个unique索引
总结：
MySQL创建查看删除索引.png
MySQL索引分类
MySQL索引.png
MySQL索引使用原则
MySQL索引使用原则.png
B-Tree索引的底层实现是什么？
B-Tree索引的底层实现是一个B+Tree而不是BTree
1. BTree和B+Tree结构在存储数据上的区别？
先分别来看一下BTree 和 B+Tree的结构：
BTree.png
B+Tree.png
<1> BTree 和 B+Tree结构上的明显区别：
- B+Tree 所有的数据都存储在叶子节点上。
- B+Tree 每个叶子节点都有指向下一个叶子节点的链接，形成一种链式结构，这样的好处是：可以从任意一个叶子节点开始遍历，来获取所有的数据。
再来看看BTree 和 B+Tree在存储数据上的结构对比：
BTree存储数据结构.png
B+Tree存储数据结构.png
<2> BTree 和 B+Tree在存储数据上的区别：
- 在BTree的节点中存放有孩子指针，关键字key，以及key对应的数据，而在B+Tree中，内部非叶子节点只存放孩子指针和关键字key，而key对应的所有数据存放在叶子节点上。
- B+Tree非叶子节点不存储key对应的数据，因此相对于BTree，在同一块磁盘页上(InnoDB默认为16kb)可以存储更多的key和孩子指针，所以B+Tree存储的数据量比BTree大很多。
那具体看一下BTree和B+Tree 存储关键字key和数据量的情况：
BTree存储关键字key和数据量.png
BTree一个磁盘页能存储多少个关键字key呢？
这里假设关键字key，指针p都占用4byte，数据data占用1kb
计算：N*key + (N+1)p + Ndata = 16kb
则有：4Nb +4(N+1)b + 1000Nb = 16000b，1008Nb + 4b = 16000b，忽略较小的数，N = 16
BTree一个磁盘页能存储16个关键字key，孩子指针p有17个，存储数据总量 = 17 * 16kb = 272kb
B+Tree存储关键字key和数据量.png
B+Tree一个磁盘页能存储多少个关键字key呢？
计算：N*key + (N+1)p = 16kb
则有：4Nb + 4(N+1)b = 16000b，忽略较小的数，8Nb = 16000b，N = 2000
B+Tree一个磁盘页能存储2000个关键字key，孩子指针p有2001个，存储数据总量 = 2001 * 16kb = 32016kb
由此可见B+Tree能存储的关键字key和数据量跟BTree不是一个数量级别。
2. B+Tree为什么适合做索引？为什么不用红黑树或者BTree呢？
<1> 为什么不用红黑树？
总结：
- 红黑树是二叉的，即一个节点最多有两个孩子，而BTree或B+Tree可以有很多孩子，即一个节点可以存储很多关键字
- 遍历时，每次读入内存的key值更多，相对来说I/O就降低，因此BTree或B+Tree性能更好一些
<2> 为什么不用BTree而用B+Tree做索引？
总结：
- B+Tree 非叶子节点只存储 key 值和孩子指针，不存储key对应的数据，因此相对于BTree节点可以存储更多的关键字和数据量，每次读入内存的key值就更多，相对来说I/O就降低。
- B+Tree 的叶子节点存储的是全量数据，并且有序，只需要遍历叶子节点就可以对所有的 key 进行扫描，查询范围时效率更高。
3. 归纳总结
B-Tree索引.png
什么是哈希索引？
哈希索引基于Hash表实现，必须精确匹配索引所有列的查询才有效。
Hash表中的key：对于每一行数据，对所有索引列计算的一个哈希码
Hash表中的value：指向每个数据行的指针
<1> 哈希索引的数据结构：
哈希索引数据结构.png
<2> 哈希索引的限制：
- 哈希索引只包含哈希值和行指针，不存储字段值，不能使用索引中的值来避免读取行
- 哈希索引数据不是按照索引值顺序存储的，无法用于排序
- 哈希索引使用全部索引列来计算哈希值，不支持部分索引列匹配查找
- 哈希索引只支持等值比较查询：=，in()，<=>，不支持任何范围查找
- 当出现哈希冲突时，存储引擎必须遍历链表中所有行指针，逐行比较
什么是自适应哈希索引？
InnoDB引起有一个特殊的功能叫做“自适应哈希索引”，当索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样也就有了哈希索引的一些优点，如快速的哈希查找。
什么是聚簇索引，非聚簇索引？
聚簇索引是将数据存放在索引树的叶子节点上，找到叶子节点就可以读取这行数据。InnoDB存储引擎的索引方式就是聚簇索引。一个表只能有一个聚簇索引，一般会根据主键或者唯一索引，或者以数据库内部生成的rowid为主键，来建立聚簇索引。
非聚簇索引是在索引树的叶子节点上存放数据的地址，找到该地址后，需要到磁盘中查询一次才能获取到数据。MyISAM存储引擎的索引方式就是非聚簇索引，只在索引树的叶子节点上存放地址。
聚簇索引也被称为主键索引，非聚簇索引也被称为二级索引。
对比上面的索引分类，聚簇索引不是一种单独的索引类型，而是一种数据存储方式：数据行和相邻的键值紧凑地存储在一起。
聚簇与非聚簇索引.png
<1> InnoDB的聚簇索引
InnoDB的聚簇索引本质：是在同一个结构中保存了B-Tree索引和数据行。
InnoDB通过主键聚集数据：
- 主键为聚簇索引
- 若没有主键，选择一个唯一非空索引代替
- 若没有唯一非空索引，InnoDB会隐式定义一个主键来作为聚簇索引
<2> 聚簇索引和非聚簇索引的区别？
InnoDB支持聚簇索引，MyISAM不支持聚簇索引
对比InnoDB和MyISAM的数据分布：
InnoDB数据分布.png
MyISAM数据分布.png
区别总结如下：
- InnoDB聚簇索引通过主键来聚集数据；若没有主键，则选择唯一非空索引，若没有唯一非空索引，则隐式生成一个主键
- InnoDB聚簇索引聚集的就是表的数据行，每个叶子节点包含了主键值，事务ID，事务回滚指针以及数据行所有的剩余列。
- InnoDB二级索引(非聚簇索引)不聚集数据，叶子节点存储的也不是数据的地址(即指向数据行物理位置的指针)，而是数据行的主键值。因此二级索引查找数据行需要两次索引查找。
- MyISAM不支持聚簇索引，主键索引和二级索引都不聚集数据，叶子节点存储的是数据的地址。
<3> 聚簇索引的优缺点
聚簇索引.png
什么是覆盖索引？
覆盖索引是指查询的列正好是索引的一部分，那么它直接从索引上获取数据，而不需要到磁盘中查找数据，这种查询效率非常高。
比如：有一个用户表user，在用户名username上建立了索引，现在要查询user表的所有用户名：
select username from user;
索引的叶子节点中包含了username的值，不需要再回表查数据行，直接取索引列的值即可，也就是说索引覆盖了要查询的username字段的值。
如下图结构所示：username上建立了索引，索引覆盖了要查询的username字段的值
索引覆盖username字段.png
覆盖索引其它相关总结如下：
覆盖索引.png
主键索引，唯一索引，普通索引
主键索引：不允许重复，不允许有空值，是唯一索引的一种特例
唯一索引：列值不允许重复，但允许有空值(NULL)。
普通索引：最基本的索引，没有任何限制，支持上面提到的三种创建索引方式。
主键唯一普通索引.png
持续更新......
网友评论