（二）数据库索引优化

有关树的的基本知识可参考：二叉树抽象数据类型及遍历实现

（一）索引的数据结构

使用索引的原因：避免全表扫描，提高检索效率
可以作为索引：主键，唯一键

1.普通查找：

• 二叉搜索树：有左子树和右子树
  优点：利用二叉查找；时间复杂度为O（log n）
  缺点：数据库要进行插入和删除，当对其操作后其高度可能会发生变化，时间复杂度增加，I/O访问时间增加，性能下降；
  具体代码实现可参考：二叉搜索树，平衡二叉树

二叉树主要用于组织内存中规模较小的目录，在检索过程中平均要对外存使用log2 n次访问
对于较大的外存储器上的文件，一般使用每个结点包含多个关键码的B树

• B树：平衡多路查找树
  相关具体代码实现可参考：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.索引是越多越好吗

• 数据量小的表不需要索引，建立会增加额外的开销
• 数据变更也需要维护索引，因此更多的索引意味更大的维护成本
• 更多的索引也意味更多的空间