1. 索引本质
索引是存储引擎快速找到记录的一种数据结构。
2. 索引的分类
1)主键索引(PRIMARY KEY):列的值必须唯一且不允许有NULL值。一张表只允许有一个主键。
2)唯一索引(UNIQUE) :唯一索引列的值必须唯一,允许有NULL值。
3)普通索引:
4)组合索引:索引包含多个列。
5)全文索引(FULL TEXT): 全文检索
3. 索引数据结构
使用国外的数据结构模拟网站[https://www.cs.usfca.edu/~galles/visualization/Algorithms.html] 模拟各种数据结构的创建过程。
MySQL的索引使用B+Tree数据结构。
31. MyISAM索引
MyISAM的索引与行记录是分开存储的,叫做非聚集索引(UnClustered Index)。每张myisam表对应下面3个文件,如mysql.user[user.frm->存储表结构;user.MYD->存储数据;user.MYI->存储索引]。
1)主键索引,也是一颗B+Tree树,叶子节点存储索引列的值和叶子节点的data域存储的是数据记录的物理地址,每个叶子节点页保存下一个叶子页的指针。
2)二级索引,也是一颗B+Tree树,跟主键索引结构是一样,区别在于二级索引的列值可以重复
3.2 InnoDB索引
1)主键索引,叶子节点存储所有的主键索引及行记录数据,这种索引也叫聚集索引。内节点存储key和节点指针。
2)二级索引,叶子节点存储索引列和主键。使用二级索引检索数据需要检索两遍索引,首先获得主键,然后通过主键找到具体的记录。
3. 建议
(1)不建议使用较长的列做主键,例如uuid char(64),因为所有的普通索引都会存储主键,会导致普通索引过于庞大,索引裂变平衡,这个非常消耗性能资源;
(2)建议使用趋势递增整型列的key做主键,由于数据行与索引一体,这样不至于插入记录时,有大量索引分裂,行记录移动;
4. InnoDB索引和MyISAM索引区别
1)MyISAM的索引与数据分开存储;
2)MsISAM索引叶子节点存储数据行的物理地址
3)InnoDB索引,主键索引和数据行统一存储(*.idb文件)
4)二级索引的叶子节点存储key和主键
5. 创建索引的原则
1)适合索引的列是出现在where子句中的列,或者连接子句中指定的列; 2)基数较小的类,索引效果较差,没有必要在此列建立索引;(基数=列值唯一的数量) 3)使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间; 4)不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。
5)dan表索引建议控制在5个以内
6)禁止在更新频繁、区分度不高的字段上创建索引
7)组合索引,必须把区分度字段放在前面
6. 索引优化策略
最左前缀
索引选择性与前缀索引
InnoDB的主键选择与插入优化
备注
文章中图片均为引用互联网上的图片