(一)索引的数据结构
使用索引的原因:避免全表扫描,提高检索效率
可以作为索引:主键,唯一键
1.普通查找:
- 二叉搜索树:有左子树和右子树
优点:利用二叉查找;时间复杂度为O(log n)
缺点:数据库要进行插入和删除,当对其操作后其高度可能会发生变化,时间复杂度增加,I/O访问时间增加,性能下降;
二叉树主要用于组织内存中规模较小的目录,在检索过程中平均要对外存使用log2 n次访问
对于较大的外存储器上的文件,一般使用每个结点包含多个关键码的B树
- B树:平衡多路查找树
B树也是一种平衡多路查找树,其中树中结点的最大的孩子数目称为树的阶m
- 树中每个结点最多有m个子树
- 根结点最少有两个子树
- 除了根结点以外的非叶子结点最少有m/2个子树
- 所有叶子结点都出现在同一层
- 其中有n+1个子树的非叶子结点的信息如下图:
B+树更适合做存储索引
- 1.B+树的磁盘读写代价更低
B+树内存中只有索引信息,内部结点相对小,一次性读入要查找的关键字更多 - 2.B+树的查询效率更稳定
所有关键字查询的长度相同,使得每个数据查询的效率几乎相同,为O(log n) - 3.B+树更有利于对数据库的扫描
B+树只用遍历叶子结点即可二完成数据的查询
2.特殊索引
- Hash索引:运用Hash函数进行定位查找
优点:查询效率理论上较高
缺点:
- 仅仅能够满足”IN“,“=”,不能使用范围查询
- 无法被用来数据的排序操作
- 不能利用部分索引键查询
- 不能避免表扫描
- 遇到大量Hash值相等的情况后性能可能会下降
- BitMap索引(位图索引):一个bit位来存放
优点:一个叶子块可存放较多bit位来表示不同行,
缺点:
- 只是用与某个字段的值只有固定的几个的情况;
- -同时,其锁力度较大,当新增或修改数据时,与其在同一位图的数据操作都会被锁住,并不适合并发较多的系统
(二)密集索引和稀疏索引
一个表只能有一个密集索引
- 密集索引文件的每个搜索码值都对应一个索引,
- 稀疏索引只为索引码的某些值建立索引项
如图所示:
InnoDB:有且仅有一个密集索引
- 若有一个主键被定义,则主键是密集索引
- 若没有主键被定义,该表的第一个唯一非空索引作为密集索引
- 若不满足上述条件,innoDB内部会生成一个隐藏主键(密集索引)
- 非主键索引存储相关键位和其对应的主键值,包含两次查找
(三)索引其它问题
1.定位并优化Sql:
大致思路:
- 1.根据慢日志定位慢查询Sql
set global show_query_log=on; //打开慢日志
set global long_query_time=1; //设置时间
select count(id)from person_info_large; //灌入数据
- 2.使用explain等工具分析Sql:直到效率低的原因,改进查询
explain中有type和extra关键字段,explain中的字段type:index>all(全表查询要优化):
explain select name from person_info_large order by name desc;
- 3.修改Sql或尽量让Sql走索引:
alter table person_info_large add index idx_name(name); //添加索引
explain select count(id) from person_info_large force index(primary); //强制索引
2.联合索引的最左匹配原则
- 1.最左前缀匹配原则:mysql会一直向右匹配直到遇到范围查询(<,>,between,like)就会停止匹配,比如a=3and b=4and c>5 and d=6,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,d,b,c)的索引则都可以用到,abd的顺序可以任意调整;
- 2.=和in可以乱序:比如a=3and b=4and c=3建立(a,b,c)索引可以任意顺序,mysql优化器可以帮助优化索引完成识别的形式。
3.索引是越多越好吗
- 数据量小的表不需要索引,建立会增加额外的开销
- 数据变更也需要维护索引,因此更多的索引意味更大的维护成本
- 更多的索引也意味更多的空间