MySQL之五 索引

MySQL中的索引:

  基本法则:索引应该构建在被用作查询条件的字段上

  索引类型:

      B+ Tree索引:使用B+树的数据结构来存储数据的;顺序存储;每一个叶子节点到根结点的距离是相同的  

   左前缀类型 适合范围查找
        可以使用B-Tree索引的查找类型:全键值、键值范围或键前缀查找 ;
          全值匹配:和索引中的所有列匹配(精确查找某个值)  比如:'yanguo'
          匹配最左前缀:只精确匹配起头部分 比如:'yang%'
          匹配范围值:
          精确匹配某一列并范围匹配另一列
        不适合适合
B-Tree索引的场景:
           如果不从最左列开始,索引无效:(Age,Name) 
           不能跳过索引中的列:(StuID,Name,Age) 不能跳过索引条件,全满足才能匹配到数据  
           如果查询中某个列是为范围查询,那么其右侧的列都无法再使用索引优化查询(StuID,Name)
 

       Hash索引:基于哈希表(把每一个键提取出来做哈希运算,运算出的结果放置在哈希表中,并分段)

            特别适用于精确匹配索引中的所有列(键值对)

  注意:只有Memory存储引擎支持显示hash索引

适用场景:
    只支持等值比较查询:包括=,IN(),<=>;  
不适合使用hash索引的场景:         存储的非为值的顺序,因此,不适用于顺序查询         不支持模糊匹配

       空间索引(R-Tree): MyISAM支持空间索引    (了解即可)

       全文索引(FULLTEXT):在文本中查找关键词  (了解即可)

索引优点:

  • 索引可以降低服务需要扫描的数据量,减少了I/O次数
  • 索引可以帮助服务器避免排序和使用临时表
  • 索引可以帮助将随机I/O转为顺序I/O 

 高性能索引策略:

    在索引中一定不一要进行算数运算(即独立使用列:尽量避免其参与运算)
         SELECT * FROM students WHERE Age+20>50;   #一旦对于列做了算数运算,则这个列将无法用成索引

    左前缀索引:索引构建于字段的左侧的多少字符,要通过索引选择性来评估
        索引选择性:不重复的索引值和数据表的记录总数的比值
    多列索引:ADN操作时更适合使用多列索引

    选择合适的索引列次序:将选择性最高放左侧

通过EXPLAIN来分析索引的有效性:

    EXPLAIN  SELECT  clause :获取查询执行计划信息,用来查看查询优化器如何执行查询

输出字段含义:

   id:当前查询语句中,每个select语句的编号

    •  复杂类型的查询有三种:
      • 简单子查询
      • 用于FROM中的子查询  
      • 联合查询:UNION

      注:UNION查询的分析结果会出现----额外匿名临时表

   select_type:

      简单查询为SIMPLE

      复杂查询:

        • SUBQUERY:简单子查询  (WHERE中的子查询)
        •  DERIVED:用于FROM中的子查询
        • UNION:UNION语句的第一个之后的SELECT语句
        • UNION RESULT:匿名临时表

E.G  mysql> EXPLAIN select name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);

  

    table:SELECT语句关联到的表

     type :关联类型或访问类型  即MySQL决定的如何去查询表中的行的方式  (记住)

    • ALL:表全表扫描方式 (性能最差)
    • Index : 根据索引的次序进行全表扫描:如果在Extra列出现“Using index”表示了使用覆盖索引
    • rang :有范围限制的根据索引实现范围扫描:扫描位置始于索引中的某一点,结束于另一点
    • ref  : 根据索引返回表中匹配某单个值得所有行
    • eq_ref  :仅返回一个行,但与需要额外与某个参考值做比较
    • const ,system :直接返回单个行  (根据主键查找)

   possible_keys :查询可能会用到的索引

   key :查询中使用了的索引

   key_len  : 在索引使用的字节数

   ref:在利用key字段所表示的索引完成查询时所有的列或某常量值

   rows:MySQL估计为找所有的目标行而需要读取的行数

   Extra:额外信息

    • Using index  :MySQL将会使用覆盖索引,以避免访问表
    • Using where  : MySQL 服务器将在存储引擎检索后,再进行一次过滤(记住)
    • Using temporary : MySQL对结果排序时使用临时表
    • Using filesort :对结果使用一个外部索引排序

 

 

原文地址:https://www.cnblogs.com/liuzhiyun/p/13196733.html