mysql索引优化之我见

索引
    索引是一种数据结构，数据库优化的常用工具之一。
索引的类型
1.主键索引 PRIMARY key  不能为空
2.唯一索引 unique  可以为空
3.普通索引 index 
4.组合索引 index   一个索引包含多个列 常用于回表操作
5.全文索引 fullText 

索引的设计原则
1.适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2.基类较小的列不建议使用索引
3.使用短索引，如果对长字符串进行索引应该指定一个前缀长度
4.不要过度使用索引，因为这样会导致更新表结构变慢

回表
 就是当对一个列创建索引的时候，索引会包含该列的键值以及键值对应的rowId，通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

 索引优化原则
     1.如果mysql估计使用索引比全表扫描还慢就不会使用索引
     2.前导模糊查询不会使用索引
     3.数据类型的隐式转换也不会走索引， 特别是类型是字符串的时候，一定要加上引号
     4.复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。
     最左原则不是说查询条件的顺序，而是查询条件是否包含最左列字段。
     5.union,or,in,都可以命中索引
     6.负向条件不会走索引 !=、<>、not in、not exists、not like
     7.范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
     8.数据库参与计算不会走索引
     9.建议利用覆盖索引进行查询，避免回表
     10.建立索引的列不允许为null
     11.更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。
    12.区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。

    13.业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。

    14.多表关联时，要保证关联字段上一定有索引。

    15.创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

数据库索引背后的数据结构

B-Tree的特性

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度：

 h 叫做B-树的高

 key和指针互相间隔，节点两端是指针：

一个节点中的key从左到右非递减排列：

 

每个指针要么为null，要么指向另外一个节点；每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d：

 

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null

 

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于key1，其中key1为node的第一个key的值： 

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于keym，其中keym为node的最后一个key的值：

 

如果某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于keyi+1且大于keyi：

 

B-Tree是一个非常有效率的索引数据结构。这主要得益于B-Tree的度可以非常大，高度会变的非常小，只需要二分几次就可以找到数据。例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2))，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。 

而B+树是B-树的一个变种

B+Tree比B-Tree更适合实现外存储索引结构

B+Tree的特性

区别于B-Tree:

• 每个节点的指针上限为2d而不是2d+1；

• 内节点不存储data，只存储key；叶子节点不存储指针。