Mysql索引
索引类似于新华字典的目录页,可以提高数据检索的效率
创建索引时,要确保该索引是应用查询语句的条件上(WHERE)
索引的效率取决于索引列的值是否散列
索引也是一张表,该表保存了主键与索引字段
建立索引也有缺点,在对表进行INSERT、UPDATE、DELETE时要维护索引文件,经常更新的表就不需要建立索引了
索引分为:主键索引、唯一索引、普通索引
1. 创建
ALTER TABLE <表名> ADD [UNIQUE] INDEX indexName (字段1, 字段2, ...)
DROP INDEX indexName ON <表名>
# 创建索引的顺序,应将去重后记录较多的放前面,大多数情况下应扩索引,而不是建索引,要空间
2. 存储结构
页是Mysql的基本存储结构,里面存储了各种数据,包括行记录
各个页组成双向链表,页里面的行记录组成单向链表
页会为存储在那的行记录生成页目录,页目录把行记录分组,在通过主键查找时用二分法定位分组,然后就可以快速找到指定行记录
通过非主键搜索只能从头遍历单链表,eg:
SELECT * FROM <表名> WHERE name = "Howl"
# 1. 先遍历双向链表找到对应的页
# 2. 不是主键查询,从头遍历页内的单链表
3. 索引结构
关系数据库会自动对其创建主键索引,使用主键索引的效率是最高的,因为主键会保证绝对唯一
有B+树和哈希,B+树的索引三层但已经足够使用了,使用哪种是引擎所决定,不是我们能干预
B+树(log N):
第一层:最大目录项
第二次:普通目录项
第三层:主键存放记录的地址,非主键存放主键和索引项
Hash:
没有排序
键大量重复效率也低,哈希碰撞
4. 聚集和非聚集索引
聚集索引:索引键值的逻辑顺序与表中记录的物理顺序一致(主键索引,叶子节点存放记录的地址)
非聚集索引:索引键值的逻辑顺序与表中记录的物理顺序不一致(非主键索引,叶子节点存放主键和索引项)
使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据,这叫回表
若查询的字段,在索引上已满足,不用再拿主键去查,叫覆盖索引
5. 最左匹配原则
使用索引是从左到右匹配索引字段,前一个不匹配,后一个就不使用索引,所以总是从第一个开始匹配,因为B+树只能以一个值(即主键)来建树
联合索引时,索引只能用于查找key是否存在或相等,遇到范围查询(>、<、between、like),就不能进一步匹配了,退化为线性查询,因此列的排列顺序决定了可命中索引的列数
参考Java3y