Mysql支持的索引类型

B-tree索引

特点
    B-tree索引以B+树的结构存储数据
    B-tree索引能加快数据的查询速度
    B-tree索引更适合进行范围查找[顺序存储]
在什么情况下可以用到B数索引
    1.全值匹配的查询
        order_sn = '123456'
    2.匹配最左前缀的查询
    3.匹配列前缀查询
        order_sn like '123%'
    4.匹配范围值的查找
        order_sn > '123456' and order_sn < '654321'
    5.精确匹配左前列并范围匹配另外一列
    6.只访问索引的查询
Btree索引的使用限制
    1.如果不是按照索引最左列开始查找，则无法使用索引
    2.使用索引时不能跳过索引中的列
    3.Not in 和 <> 操作无法使用索引
    4.如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

Hash索引

Hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中所有列时，才能够使用到hash索引；
对于Hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。
Hash索引的限制
    1.Hash索引必须进行二次查找
    2.Hash索引无法用于排序
    3.Hash索引不支持部分索引查找也不支持范围查找
    4.Hash索引中的hash码的计算可能存在hash冲突

为什么要使用索引

1.索引大大减少了存储引擎需要扫描的数据量
2.索引可以帮助我们进行排序可以避免使用临时表
3.索引可以把随机I/O变为顺序I/O

索引的缺点

1.索引会增加写操作的成本
2.太多的索引会增加查询优化器的选择时间

索引优化策略

1.索引列上不能使用表达式或函数
2.前缀索引和索引列的选择性
    create index index_name on table(col_name(n));
索引的选择性是不重复的索引值和表的记录数的比值

联合索引

如何选择索引列的顺序
    经常会被使用到的列优先
    选择性高的列优先
    宽度小的列优先

覆盖索引

优点
    1.可以优化缓存，减少磁盘I/O操作
    2.可以减少随机I/O,变随机IO操作为顺序IO操作
    3.可以避免对Innodb主键索引的二次查询
    4.可以避免MyISAM表进行系统调用
无法使用覆盖索引的情况
    1.存储引擎不支持覆盖索引
    2.查询中使用了太多的列
    3.使用了双%号的like查询

使用索引来优化查询

使用索引扫描来优化排序
    1.索引的顺序和order by子句的顺序完全一致
    2.索引中所有列的方向(升序、降序)和order by子句完全一致
    3.order by 中的字段全部在关联表中的第一张表中

模拟hash索引优化查询

    1.只能处理键值的全职匹配查询
    2.所使用的hash函数决定着索引建的大小

利用索引优化锁

1.索引可以减少锁定的行数
2.索引可以加快处理速度，同时也加快了锁的是释放
    例子:
    begin;
    select from test where name = 'x' for update;
    ----
    在开一个事务
    begin;
    select from test where name = 'y' for update;
    如果name 没有索引，第二个事务将卡住，否则将顺利执行

删除重复和冗余的索引

pt-duplicate-key-checker h = 127.0.0.1

查询未被使用过的索引

select object_schema,object_name,index_name,b.TABLE_ROWS from performance_schema.table_io_waits_summary_by_index_usage a join information_schema.TABLES b on a.OBJECT_SCHEMA=b.TABLE_SCHEMA AND a.OBJECT_NAME = b.TABLE_NAME where index_name is not null and count_star = 0 order by object_schema,object_name;

更新索引统计信息及减少索引碎片

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