mysql的索引简述
索引分类
主键索引
PRIMARY KEY (
id)
唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一 查看
alter table t1 add unique idx_u1(`u1`)
#查看
UNIQUE KEY `idx_u1` (`u1`)
单列索引
即索引创建在单个列上
alter table t1 add index idx_u1(`u1`)
#查看
KEY `idx_u1` (`u1`)
复合索引
创建多列索引时记得遵循最左匹配原则，把区分度较高的字段放前面
alter table t1 add index idx_u1_u2(`u1`,`u2`)
#查看
KEY `idx_u1_u2` (`u1`,`u2`)
前缀索引
即当字段的长度超过索引限制，可以为字段的部分前缀创建索引
alter table t1 add index idx_u3(`u3`(4))
#查看
KEY `idx_u3` (`u3`(4))
倒序索引
alter table t1 add index idx_u3(u3 desc)
函数索引
alter table t1 add index idx_abs_u2 ((ABS(`u2`)))
#查看
KEY `idx_abs_u2` ((abs(`u2`)))
表达式索引
alter table t1 add index idx_u1u2 ((u1 + u2))
#查看
KEY `idx_u1u2` (((`u1` + `u2`)))
不可见索引
ALTER TABLE t1 ALTER INDEX idx_u1 INVISIBLE
#查看
KEY `idx_u1` (`u1` DESC)
空间索引
空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引
alter table 表名 add SPATIAL KEY `idx_gis` (`gis`)
#查看
SPATIAL KEY `idx_gis` (`gis`)
全文索引
alter table 表名 add fulltext index idx_fulltext_u4(`u4`) with parser ngram;
#查看
FULLTEXT KEY `idx_fulltext_u4` (`u4`)
自适应哈希索引
- InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”。当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找
- 哈希索引能以 O(1) 时间进行查找，但是失去了有序性，只支持精确查找，无法用于部分查找和范围查找
数据存储维度
聚簇索引
- 索引与数据存放在一起，找到索引的同时也找到了数据；聚簇索引具有唯一性，一张表只有一个聚簇索引
- 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个非空唯一索引代替。如果没有，InnoDB 会定义一个隐藏的_rowid 列来作为聚簇索引
非聚簇索引
- 索引与数据分开存放，索引结构的叶子节点指向了数据的对应行（叶子节点记录了主键的值，通过主键的值到聚簇索引中进行查找）
索引设计参考
设计原则
- 字段的数值有唯一性的限制
- 例如主键、唯一索引
- 频繁作为 WHERE 查询条件的字段
- 经常 GROUP BY 和 ORDER BY 的列
- UPDATE、DELETE 的 WHERE 条件列
- DISTINCT 字段需要创建索引
- 多表 JOIN 连接操作时，对用于连接的字段创建索引（连接表的数量尽量不要超过 3 张）
- 使用列的类型小的创建索引
- 使用字符串前缀创建索引（在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引）
- 区分度高(散列性高)的列适合作为索引
- 使用最频繁的列放到联合索引的左侧
- 在多个字段都要创建索引的情况下，联合索引优于单值索引
索引设计建议
- 建议单张表索引数量不超过6个
- 联合索引把区分度高（散列性高）的列放在前面
- 在where中使用不到的字段，不要设置索引
- 数据量小的表最好不要使用索引
- 有大量重复数据的列上不要建立索引
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引。例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等
- 删除不再使用或者很少使用的索引
- 不要定义冗余或重复的索引
索引使用注意事项
-
索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
SELECT * FROM 表名 WHERE num + 1 = 5 -
负向查询不能使用索引
负向查询不能使用索引 -
前导模糊查询不能使用索引
select name from user where name like '%xxx'
拓展内容
B Tree
B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层
B+Tree
B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能
- 文件系统及数据库系统普遍采用 B+ Tree 作为索引结构
- 更少的查找次数
- 利用计算机预读特性
- InnoDB 的 B+Tree 索引分为主索引和辅助索引
R Tree
MyISAM 存储引擎支持空间数据索引(R-Tree)
可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询
