mysql索引优化Note


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));
    索引的选择性是不重复的索引值和表的记录数的比值

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

4.覆盖索引
    优点
        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; //使用不当会导致锁表


分类:   数据库  / 
标签:   索引 mysq
 /  浏览量:  753
2017-5-20 10:44 Saturday