MySQL的索引优化策略.pptx

MySQL的索引优化策略 王光亮 2019.10 ————————————————————————————— 目录 CONTENTS 一、索引的基础介绍 二、索引的使用限制 三、索引的优化策略 1.1 什么是索引 1.2 索引是如何工作的 1.3 索引的优点 1.4 索引的分类 一、索引的基础介绍 打个比方：把表(包含表结构和数据)比作一本书，索引就是书的目录，方便快速定位查找书中的内容。 一、索引的基础介绍 1.1 什么是索引 索引在MySQL中也叫做键（key），是存储引擎用于快速查找记录的一种数据结构。 再次以书为例，我们是怎么查找书中的内容？ 一、索引的基础介绍 1.2 索引是如何工作的 1、确定需要查找的内容主题，通过目录确定内容的页码范围； 2、找到页码指定的页面； 3、查看页面内容。 （存储引擎）先在索引中找到对应值，根据匹配的索引记录找到对应的数据行，然后返回查询结果。 一、索引的基础介绍  索引大大减少了服务器需要扫描的数据量。  索引可以避免服务器使用临时表和排序。  索引可以将随机 I/O变为顺序I/O。 1.3 索引的优点 一、索引的基础介绍 1.4 索引的分类 索引类型 特点 使用场景 B-Tree索引 数据是有序存储的，支持排序和范围查询。 大部分数据场景 哈希索引（hash index） 基于哈希表，查询速度快，但不支持排序和范围查询。 某些特定场景 空间数据索引（R-Tree） 可有效使用任意维度来组合查询。 地理数据存储 全文索引（FullText index） 匹配方式基于文本中的关键字，而非索引的值。 关键字搜索 2.1 查询列不能包含表达式和函数 2.2 遵循左侧匹配原则 2.3 字符串判断必须加引号 2.4 不支持通配符开头的LIKE查询 二、索引的使用限制 2.5 其他限制 二、索引的使用限制 查询列如果包含表达式或函数，会导致查询无法使用索引。 2.1 查询列不能包含表达式和函数 举例2.1：以下2个查询的逻辑条件和查询结果一样，但是效率相差很多。 正常查询：select * from ams_orderpaydetail_1 where order_id=746212228578983936; 问题查询：select * from ams_orderpaydetail_1 where order_id+1=746212228578983937； 二、索引的使用限制 分析：由于第2个语句“=”左侧是表达式“order_id+1”，优化器无法正常解析，导致索引失效，因此查询走全表扫描。 二、索引的使用限制 在索引列为多列的情况下，查询语句的列顺序必须遵循“最左匹配原则”，即查询列的顺序必须与索引列顺序一致。 2.2 遵循左侧匹配原则 举例2.2：如果A表上建有多列索引 idx(a,b,c)，则以下查询符合“左侧匹配原则”： 1、where a=？and b=？and c=? 2、where a=？and b=？ 3、where a=？或 where a=？and c=？ 二、索引的使用限制 以下查询不符合“左侧匹配原则”，无法使用索引的情况： 1、where b=？and c=? 或 where b=？ 2、where c=？and b=? 或 where c=? 因此：针对以上索引，只要查询中有“a=？”的条件，就可以使用索引，但考虑到索引的使用效率，尽量按照“a=？and b=？and c=?”的顺序来写查询语句。 二、索引的使用限制 当索引列为字符类型时，进行等值或范围比较时，查询条件中的常量值必须添加引号，否则会导致索引失效。 2.3 字符串判断必须加引号 举例2.3：ams_orderpaydetail_1表的pay_request_no字段是字符类型，使用该字段进行等值判断查询时，引号加与不加的在性能上有什么差别？ 二、索引的使用限制 加了引号：可以使用索引，只需要扫描1行记录。 不加引号：无法使用索引，走全表扫描方式，效率很低。 二、索引的使用限制 MySQL存储引擎不支持通配符开头的前缀LIKE（LIKE %...）查询，因此，查询尽量采用后缀匹配（LIKE ...%）的方式。 2.4 不支持通配符开头的LIKE查询 如果在部分业务场景下，必须用最左前缀的LIKE，可以采用“延迟关联”的策略来提升索引的利用率（下一章节介绍）。 二、索引的使用限制 后缀LIKE