数据库索引优化

内存不可完全缓存所有数据的时候，体现索引的重要性。

MySQL支持的索引，不同的存储引擎支持不同的类型

B-tree索引

B-tree索引的特点

    以B+树结构存储数据

    可加快数据的查询速度

    适合范围查找

B-tree的使用情况

    1、全值匹配的chaxun

        order_sn = "123456789987655";

    2、匹配最左前缀的查询

        如果建立的所用是[order_sn,order_date],这时用order_sn查找还是可以找到的。查找order_sn = "123456789987655"为查找条件是可以找到的，如果order_date ='2016-11-03',则不可以找到。

    3、匹配列前缀

        order_sn like '9876%'

    4、匹配范围的查询

    order_sn>'123456789';

    order_sn<'123456789';

    5、精确匹配左前列并范围匹配另一列

    可精确匹配order_sn 列，而order_date 是在范围内匹配。

    6、只访问索引的查询

限制：

    1、如果不是按索引最左列开始查找，则无法使用索引

     2、使用索引时不能跳过左边索引中的列

    3、Not in 和<>操作无法使用索引

    4、如果查询中有某个列的查询范围，则其右边所有列都无法使用索引

Hash索引

Hash索引的特点

memory和innodb都支持

        hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到hash索引。

        对于Hash索引中的所有列，存储引擎会为每一行计算一个hash码，Hash索引中存储的就是Hash码。

限制：

1、两次查找

2、以hash码存储无法用于排序

3、不支持部分索引查找也不支持范围查找

4、可产生Hash冲突

为什么使用索引

1、大量减少存储引擎需要扫描的的数据量

2、索引可帮助我们进行排序，以避免使用临时表

3、索引可以把随机I/O变为顺序I/O

影响

1、索引会增加写操作的成本

2、太多的索引增加查询时间

索引优化

1、索引列上不能使用表达式或函数

问题语句：

select ...... from product 

where to_days(out_date) - to_days(current_date)<=30 

to_days：函数

out_date：索引列

优化语句：

 select ...... from product 

where out_date <= date_add(current_date,interval 30 day);

2、前缀索引和索引列的选择性

mysql B-tree 索引对键值的大小是有限制的。根据存储引擎的不同而不同，对于innodb来说索引最大的大小不能超过767个字节（255字符），而myIsam最大不超过1000个字节。当字段比较大的时候，MySQL支持对前缀进行索引

    前缀索引和索引列的选择性

    create index index_name on table(col_name(n));

    索引的选择性是不重复的索引值和表的记录数的比值

3、联合索引

    选择索引列的顺序

        经常会被使用到的列优先

        选择性高的列优先

        宽度比较小的列优先（选择性高前提）

4、覆盖索引

        通过B-tree索引直接获得所需的数据，通过索引关键字直接索引存储的信息，就不需要再读取行的信息。包含所有查询字段全部值的索引我们称为覆盖索引。这里的全部值包括where groupBy orderBy中的值

        可优化缓存、减少I/O

select language_id 是using index来自索引获取数据

select * 是use where 将所有数据装于内存中，用where条件过滤。

使用索引来优化查询

1、使用索引扫描来优化排数

    索引的列顺序和orderby子句的顺序完全一致

    索引中所有列的方向（升序，降序）和orderby子句完全一致

    Order by中的字段全部在关联表中的第一张表中。

2、B-tree模拟Hash索引优化查询

索引的维护和优化

删除冗余索引

pt-duplicate-key-checker h=127.0.0.1

查找未被使用过的索引