MySQL索引是什么?
为什么要使用索引?(即索引的优缺点是什么?)
创建,查看,删除索引的方式
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索引分类
MySQL索引使用原则
B-Tree索引的底层实现是什么?
B-Tree索引的底层实现是一个B+Tree而不是BTree
1. BTree和B+Tree结构在存储数据上的区别?
先分别来看一下BTree 和 B+Tree的结构:
<1> BTree 和 B+Tree结构上的明显区别:
- B+Tree 所有的数据都存储在叶子节点上。
- B+Tree 每个叶子节点都有指向下一个叶子节点的链接,形成一种链式结构,这样的好处是:可以从任意一个叶子节点开始遍历,来获取所有的数据。
再来看看BTree 和 B+Tree在存储数据上的结构对比:
<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呢?
这里假设关键字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呢?
计算: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. 归纳总结
什么是哈希索引?
哈希索引基于Hash表实现,必须精确匹配索引所有列的查询才有效。
Hash表中的key:对于每一行数据,对所有索引列计算的一个哈希码 Hash表中的value:指向每个数据行的指针
<1> 哈希索引的数据结构:
<2> 哈希索引的限制:
- 哈希索引只包含哈希值和行指针,不存储字段值,不能使用索引中的值来避免读取行
- 哈希索引数据不是按照索引值顺序存储的,无法用于排序
- 哈希索引使用全部索引列来计算哈希值,不支持部分索引列匹配查找
- 哈希索引只支持等值比较查询:=,in(),<=>,不支持任何范围查找
- 当出现哈希冲突时,存储引擎必须遍历链表中所有行指针,逐行比较
什么是自适应哈希索引?
InnoDB引起有一个特殊的功能叫做“自适应哈希索引”,当索引值被使用的非常频繁时,它会在内存中基于B-Tree索引之上再创建一个哈希索引,这样也就有了哈希索引的一些优点,如快速的哈希查找。
什么是聚簇索引,非聚簇索引?
聚簇索引是将数据存放在索引树的叶子节点上,找到叶子节点就可以读取这行数据。InnoDB存储引擎的索引方式就是聚簇索引。一个表只能有一个聚簇索引,一般会根据主键或者唯一索引,或者以数据库内部生成的rowid为主键,来建立聚簇索引。
非聚簇索引是在索引树的叶子节点上存放数据的地址,找到该地址后,需要到磁盘中查询一次才能获取到数据。MyISAM存储引擎的索引方式就是非聚簇索引,只在索引树的叶子节点上存放地址。
聚簇索引也被称为主键索引,非聚簇索引也被称为二级索引。
对比上面的索引分类,聚簇索引不是一种单独的索引类型,而是一种数据存储方式:数据行和相邻的键值紧凑地存储在一起。
<1> InnoDB的聚簇索引
InnoDB的聚簇索引本质:是在同一个结构中保存了B-Tree索引和数据行。
InnoDB通过主键聚集数据:
- 主键为聚簇索引
- 若没有主键,选择一个唯一非空索引代替
- 若没有唯一非空索引,InnoDB会隐式定义一个主键来作为聚簇索引
<2> 聚簇索引和非聚簇索引的区别?
InnoDB支持聚簇索引,MyISAM不支持聚簇索引
对比InnoDB和MyISAM的数据分布:
区别总结如下:
- InnoDB聚簇索引通过主键来聚集数据;若没有主键,则选择唯一非空索引,若没有唯一非空索引,则隐式生成一个主键
- InnoDB聚簇索引聚集的就是表的数据行,每个叶子节点包含了主键值,事务ID,事务回滚指针以及数据行所有的剩余列。
- InnoDB二级索引(非聚簇索引)不聚集数据,叶子节点存储的也不是数据的地址(即指向数据行物理位置的指针),而是数据行的主键值。因此二级索引查找数据行需要两次索引查找。
- MyISAM不支持聚簇索引,主键索引和二级索引都不聚集数据,叶子节点存储的是数据的地址。
<3> 聚簇索引的优缺点
什么是覆盖索引?
覆盖索引是指查询的列正好是索引的一部分,那么它直接从索引上获取数据,而不需要到磁盘中查找数据,这种查询效率非常高。
比如:有一个用户表user,在用户名username上建立了索引,现在要查询user表的所有用户名:
select username from user;
索引的叶子节点中包含了username的值,不需要再回表查数据行,直接取索引列的值即可,也就是说索引覆盖了要查询的username字段的值。
如下图结构所示:username上建立了索引,索引覆盖了要查询的username字段的值
覆盖索引其它相关总结如下:
主键索引,唯一索引,普通索引
主键索引:不允许重复,不允许有空值,是唯一索引的一种特例。
唯一索引:列值不允许重复,但允许有空值(NULL)。
普通索引:最基本的索引,没有任何限制,支持上面提到的三种创建索引方式。
持续更新......