索引:帮助MySQL高效获取数据的数据结构。
InnoDB存储引擎使用的索引结构是B+Tree,选择其的理由是:
a.相对于二叉搜索树,层级更少,搜索效率高;
b.对于B-tree,非叶子节点也会保存数据,导致一页中存储的键值减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
c.相对于Hash索引,B+tree支持范围匹配及排序操作;
索引分类:
a.按具体类型分类:
(1)主键索引,默认自动创建,只能有一个,PRIMARY
(2)唯一索引,避免同一个表中某列数据列中的值重复,可以有多个,UNIQUE
(3)常规索引,快速定位特定数据,可以有多个
(4)全文索引,查找的是文本中的关键词,而不是比较索引中的值,可以有多个,FULLTEXT
b.按存储形式分类
(1)聚集索引,将数据存储与索引放到一块,叶子节点保存了行数据,必须有,只有一个
若存在主键,则主键索引就是聚集索引;
若没有主键,则第一个唯一索引作为聚集索引,
若没有主键和唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引
(2)二级索引,将数据存储与索引分开,叶子结点关联的是对应的主键,可以存在多个
索引语法:
#创建索引
create [unique|fulltext] index index_name on table_name(field_name,...);
#查看索引
show index from table_name;
#删除索引
drop index index_name on table_name;
索引失效:
(1)联合索引遵守最左前缀法则,查询从索引的最左列开始,并且不跳过索引中的列,若跳过某一列,后面的字段索引会失效。
(2)联合索引中,出现范围查询(>,<),范围查询右侧的列索引会失效,尽量使用>=,<=。
(3)所有索引对索引对应的字段进行函数运算操作后,索引失效。
(4)所有索引中索引对应的字段若是字符串且不加引号,索引失效。
(5)所有索引若对索引对应的字段进行头部模糊匹配,索引失效。
(6)所有索引使用or连接,or前的列中有索引,后面列中没有索引,则涉及的索引都失效。
索引设计原则:
(1)针对数据量较大,且查询比较频繁的表建立索引
(2)针对常作为查询条件,排序,分组操作的字段建立索引
(3)尽量选择区分度高的列作为索引,如唯一索引
(4)尽量使用联合索引,减少单列索引
(5)控制索引的数量,索引越多,维护索引结构的代价就越大,影响增删改
(6)若索引不能存储null值,则对应字段应该使用not null约束