美文网首页
数据库索引优化

数据库索引优化

作者: Sara_d94e | 来源:发表于2018-03-28 08:50 被阅读0次

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

    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

    查找未被使用过的索引

    相关文章

      网友评论

          本文标题:数据库索引优化

          本文链接:https://www.haomeiwen.com/subject/ycxscftx.html