数据库索引优化

1、MySQL支持的索引类型

B-tree索引的特点：

    a、B-tree过引以B+树的结构存储数据

   b、B-tree索引能够加快数据的查询速度

   c、B-tree索引更适合进行范围查找

   d、适合范围查找

   e、B-tree索引是按顺序存储的

 

什么情况下可以用B树索引：

 a、全值匹配的查询

        查询order_sign='12345678'

 b、匹配最左前缀的查询

 c、匹配列前缀查询

         查询order_sign='123%'

 d、匹配范围值的查询

       order_sign>'100'  and order_sign<'999'

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

f、只访问索引的查询

 

Btree索引的使用限制：

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

  b、使用索引时不能跳过索引中的列

  c、not in 和<>操作无法使用索引

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

 

Hash索引的特点：

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

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

Hash索引的限制：

   a、 hash索引必须进行二次查找

   b、hash索引无法用于排序

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

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

 

为什么要使用索引：

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

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

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

 

索引是不是越多越好：

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

   b、太多的索引会增加查询优化器的选择时间

  

安装演示数据库：

索引优化策略：

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

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

c、联合索引

如何选择索引列的顺序

    经常会被使用到的列优先

    选择性高的列优先

    宽度小的列优先

 

d、覆盖索引

   优点：可以优化缓存，减少磁盘IO操作

              可以减少随机IO，变随机IO操作变为顺序IO操作

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

              可以避免MyISAM表进行系统调用

      

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

      存储引擎不支持覆盖索引

      查询中使用了太多的列

      使用了双%号的like查询

 

覆盖索引的例子：

 

 

2、如何使用索引优化查询

   a、使用索引扫描来优化排序（通过排序操作、按照索引顺序扫描数据）

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

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

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

b、模拟hash索引优化查询

       只能处理键值的全值匹配查询

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

2.1、利用索引优化锁

         索引可以减少锁定的行数

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

 

2.2、索引的维护和优化

删除重复和冗余的索引