mysql 索引分类
- 从物理存储角度分为:
聚簇索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据
非聚簇索引:将数据与索引分开存储,索引结构的叶子节点指向了数据对应的位置
- 从逻辑存储角度分为:
普通索引:仅加速查询,无限制
唯一索引:加速查询 ,列值唯一(可以有null),如果是组合索引,则列值的组合必须唯一
主键索引:加速查询 ,列值唯一(不可以有null),表中只允许有一个主键索引, 是一种特殊的唯一索引
组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并(索引合并:使用多个单列索引组合搜索)
只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
全文索引:目前只有 CHAR、VARCHAR 、TEXT 列上可以创建全文索引
1. 索引的优势
- 通过创建索引,可以在查询的过程中,提高系统的性能
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间,比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。
- 降低CPU使用率
- 提高查询效率(降低IO使用率)
2. 何时创建索引
- 经常需要搜索的列上
- 作为主键的列上
- 经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
- 经常需要根据范围进行搜索的列上
- 经常需要排序的列上
- 经常使用在where子句上面的列上
3. 创建索引的规范
(1)单张表中索引数量不超过5个。
(2)单个索引中的字段数不超过5个。
(3)索引名必须全部使用小写。
(4)非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。
(5)唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。
(6)组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。
(7)表必须有主键,推荐使用UNSIGNED自增列作为主键。
(8)唯一键由3个以下字段组成,并且字段都是(整)(形)(时),可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。
(9)禁止冗余索引。
(10)禁止重复索引。
(11)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。
(12)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。
(13)对字符串使用前缀索引,前缀索引长度不超过8个字符。
(14)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。
(15)合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
(16)合理使用覆盖索引减少IO,避免排序。
4. 索引失效的情况
- 条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
- 在索引字段上使用计算、函数等,会导致索引失效。例如left(name,4),导致name字段索引失效。
- 对于组合索引,如果不使用的组合索引的第一列字段,则不会使用索引(即不符合最左前缀原则)
- 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 范围查找,右边的索引全失效。eg:一个组合索引 index(name,age.sex)
select name,age,sex from where name= 'aa' and age >25 and sex=1
sex 字段上的索引,无法被使用 - 使用