数据库索引优化

    本文主要介绍索引的两种存储结构(B-tree索引和Hash索引),以及索引的优化策略

    一 B-tree索引

        1 B-tree索引的特点

               1.1 B-tree索引以B+树的结构存储数据

                   

                1.2 B-tree索引能加快数据的查询速度

                1.3 b-tree更适合进行范围查找

          2 什么情况下可以使用B数索引

                2.1 全值匹配的查询

                    order_num="12345678"

                2.2 匹配最左前缀的查询

                     联合查询中最左一列

                2.3 匹配列前缀的查询

                       order_num like "1234"

                2.4 匹配范围值的查询

                        order_num> '22222' and order_num<'55555'

                2.5 精确匹配左前列并范围匹配另外一列

                2.6 只访问索引的查询

            3 B-tree的使用限制

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

                3.2 使用索引是不能跳过索引中的列

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

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

     二 Hash索引

                1 hash索引的特点

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

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

                2 hash索引的限制

                    2.1 hash索引必须进行二次查找

                    2.2 hash索引无法进行排序

                    2.3 hash索引不支持部分索引查找也不支持范围查找

                    2.4 hash索引中hash码的计算可能存在hash冲突

     三 为什么要使用索引

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

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

                   3 索引可以把随机i/o变成顺序i/o

                   注：索引不是越多越好，有两方面的原因：索引会增加写操作的成本，太多的索引会增加查询优化器的选择时间

      四  索引的优化策略

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

        

                   

                    

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

                        创建前缀索引：create index index_name on table(col_name(n))

                        索引列的选择性：不重复的索引值/表的记录数

                   3 联合索引

                            如何使用索引列的顺序：

                             3.1 经常会被使用到的列优先 

                             3.2 选择性高的列优先

                             3.3 宽度小的列优先

                  4 覆盖索引

                             4.1 通常我们建立索引会根据where条件创建，但是优秀的索引需要考虑整个查询。mysql其实可以通过

                          索引来直接获取列的数据。

                             4.2 覆盖索引：如果索引的叶子结点包含了要查询的数据，那么就不用回表查询了，也就是这种索引包

                           含所有需要查询的字段的值

                             4.3 优点：

                                      4.3.1 可以优化缓存，减少磁盘i/o操作

                                      4.3.2 可以减少随机i/o,变随机i/o为顺序i/o

                                      4.3.3 可以避免对innodb主键索引的二次查询 

                                      4.3.4 可以避免myisam表进行系统调用

                             4.4 无法使用覆盖索引的情况

                                       4.4.4 存储引擎不支持覆盖索引

                                       4.4.5 查询中使用了太多的列

                                       4.4.6 使用了双%号的like查询

                5 使用索引来优化查询

                               5.1 使用索引扫描来优化排序

                                        5.1.1 索引的列顺序和order by子句的顺序完全一致

                                        5.1.2  索引中所有列的方向（升序，降序)和order by子句完全一致

                                        5.1.3  order by 中的字段全部关联表中的第一章表中

                               5.2 模拟hash索引优化查询

                                        增加一列，将选择字段的值进行hash函数处理

                                          5.2.1 只能处理键值的全职匹配查找

                                          5.2.2 所使用的hash函数决定着索引的大小

                 6 利用索引优化锁

                                          6.1 索引可以减少锁定的行数

                                          6.2 索引可以加快处理速度，同时也加快了锁的释放     

                 7 索引的维护和优化

                                          7.1 删除重复和冗余的索引

                                                primary key(id),unique key(id),index(in)重复索引

                                          7.2 查找未被使用过的索引

                                          7.3 更新索引信息及减少索引碎片

                                                analyze table table_name

                                                optimize table table_name 使用不当会导致锁表