索引基本介绍
索引类型
存储结构:B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
应用层次:普通索引、唯一索引、主键索引、复合索引
索引键值类型:主键索引、辅助索引(二级索引)
数据存储和索引键值逻辑关系:聚集索引(聚簇索引)、非聚集索引(非聚簇索引)
普通索引
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
唯一索引
1.索引字段的值必须唯一,但允许有空值
2.在创建或修改表时追加唯一 约束,就会自动创建对应的唯一索引。
与普通索引相比 增加UNIQUE =》UNIQUE INDEX
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;
主键索引
它是一种特殊的唯一索引,不允许有空值。也会自动创建
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名)
复合索引
复合索引可以代替多个单一索引,相比多个单一索引复合 索引所需的开销更小。
重要原则就是能用窄索引不用宽索引。更有效
CREATE INDEX <索引的名字> ON tablename (字段名1,字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1,字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1,字段名2...) );
何时使用复合索引,要根据where条件建索引,注意不要过多使用索引,过多使用会对更新操作效率有很大影响。
如果表已经建立了(col1,col2),就没有必要再单独建立(col1);
如果现在有(col1)索引,如果查 询需要col1和col2条件,可以建立(col1,col2)复合索引,对于查询有一定提高。
全文索引
待补充 2021年11月30日
索引原理
索引是物理数据页存储,在数据文件中(InnoDB,ibd文件),利用数据页(page)存储。
索引可以加快检索速度,但是同时也会降低增删改操作速度,索引维护需要代价。
二分
hash
B tree
b数的阶是由我们自己考虑决定的,m=5时
- 每个节点最多只有m个子节点。
- 每个非叶子节点(除了根)具有至少⌈ m/2⌉子节点。
- 如果根不是叶节点,则根至少有两个子节点。
- 具有k个子节点的非叶节点包含k -1个键。
- 所有叶子都出现在同一水平,没有任何信息(高度一致)。
B+ tree
非叶子节点不存储data数据,只存储索引值。这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接,提高区间的访问性能
- List item