索引优化分析

为什么要有索引

性能下降SQL慢、执行时间长、等待时间长：查询语句写的烂，索引失效(单值、复合)，关联查询太多join(设计缺陷或者不得已的需求)，服务器调优及各个参数设置(缓冲、线程数等)。

常见通用的join查询：

SQL顺序：

                     编写过程：

                     select dinstinct  ..from  ..join ..on ..where ..group by ...having ..order by ..limit ..

                     解析过程：

                     from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

7中join总结：

 

索引简介

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构，可以得到索引的本质：索引是数据结构。可以简单理解为“排好序的快速查找数据结构”。

索引的目的在于提高查找效率。

在数据之外，数据系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据、

这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引，下图就是一种可能的索引方式示例:

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录。

一般来说，索引本身特很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，当然，除了B+树这种类型的索引之外，还有哈希索引等。

索引优势：
类似大学图书馆建立书目索引，提高数据检索的效率，降低数据库的IO成本；通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引劣势：
1.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；

2.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，MySQL不仅仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的的键值变化后的索引信息。

3.索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优的索引，或者优化查询。

索引底层数据结构

索引底层的数据结构一般包括：hash表和B+树。

B+树索引

在建立索引的时候如果不加以说明，默认是B+树索引。如下图所示：

MySQL的源码中，每个B+树节点的最大存储容量：16kb（指针+数据+索引），假设我们一行数据大小为1K，那么一页就能存储16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在InnoDB(存储引擎)源码中为6B，一种就是14B，那么一页里面就可以存储16K/14=1170个(主键+指针)，那么一棵高度为2的B+树能存储的数据为1170*16=18720条，一棵高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级条）。所以一棵高度为n的B+树能存储的数据为1170^(n-1)*16。

以上就是使用B+树作为索引的根本原因，相对于平常的二叉排序树、平衡二叉树、红黑树来说，同样的数据量，B+树可以保证树的高度最小来存储这些数据量，从而在查找的时候，所花的时间是最小的。并且像二叉平衡树和红黑树虽然也能保证树的平衡，但是在插入和删除过程中需要不断的去调节左右子树来确保平衡，而且B+树不仅支持从树的根节点开始查找也支持从树的叶节点开始查找，因为树的叶节点都用指针连接起来了，所以支撑多路和顺序查找。

hash索引

hash索引基于哈希表实现，哈希表也叫散列表，是根据关键码值而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。hash索引的关键就是散列函数和冲突的处理：

一般采用链地址法，当然还有一种是开放定址法。

Hash索引也可以加快SQL的查找速度，但是这种索引有很大的局限性，只用当SQL中的查找条件是精确的值的时候，这种索引才有效。以下为hash索引的局限性：

1、hash索引中只有hash值和行数的指针，因此无法直接使用索引来避免读取行，但是因为这种索引读取快，性能影响不明显。

2、hash索引不是按照索引值顺序存储，无法使用于排序。

3、不支持部分列匹配查找，这里面是使用索引列的全部内容来计算哈希值，例如(A,B)两列一起建索引，单纯使用A一列，那么就无法使用索引，B+ Tree索引的话，因为支持匹配最左前缀，所以这种情况适用性偏好。

4、哈希索引只支持等值查询，包括=、in()、<=>，不支持where age > 10 这种范围查询。

5、哈希冲突很多的话，维护索引操作的代价也很高。

索引分类

1、单值索引：即一个索引中只包含单个列，一个表可以有多个单列索引；

2、唯一索引：索引列的值必须唯一，但是允许有空值；

3、主键索引：是一种特殊的唯一索引，不允许有空值；

4、复合索引：即一个索引包含多个列；

5、全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引；

复合索引在实际应用中是使用最多的。

基本语法：

              创建：

                     Create [unique] index indexName on table(columnname(length))

                     Alter table add [unique] index [indexName] on (columnname(length))

              删除：

                     Drop index[indexName] on table

              查看：
                     show index from table_name

哪些情况下需要建立索引：

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引

3、查询中与其他表关联的字段，外键关系建立索引

4、频繁更新的字段不适合创建索引

5、where条中用不到的字段不创建索引

6、查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度

7、查询中统计或者分组字段

哪些情况下不要创建索引：

1、表记录太少

2、经常删改的表

3、数据重复并且分布平均的表字段不应该建立索引，如果某个数据列中包含许多重复的内容，为它建立索引就没有太大的实际效果

假如一个表中有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约有50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比，如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择就是1980/2000=0.99。一个索引的选择性越接近1，这个索引的效率就越高。