mysql优化参考(二)-索引

参考：https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

 

索引（Indexs）

 

一、作用（轻定义）：索引用于快速查找具有特定列值的行。没有索引，MySQL必须从第一行开始，然后通读整个表以找到相关的行。表越大，花费时间越长。如果表中有相关​​列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。

• 减少服务器（Mysql-Server）需要扫描的数据量
• 减少文件排序和临时表（子查询可能产生临时表）（Order By 全表排序）
• 随机读取->顺序读取（速度更快）

二、数据结构：B+树（官方写的是B树）-与二叉树不同：多个分叉

• 文档
• 与存储引擎的关系：InnoDB、MyIsAM,Memory也可以使用（但一般使用哈希表会更合适）。B树更适合范围查询，哈希表更适合值匹配

三、mysql具体使用索引做什么

• WHERE中作为查询条件快速查找与子句匹配的行。

• 从考虑中消除行（possible_keys中有多个可考虑的索引的时候，mysql通常会选择最少行数的索引）。最具 选择性的索引）。

• 如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引 (col1, col2, col3)，你有索引的搜索功能(col1)， (col1, col2)以及(col1, col2, col3)。有关更多信息，请参见 第8.3.5节“多列索引”（https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html）。【这个指的应该是组合索引，会使用最左匹配，优先用组合索引最左的进行匹配】

• 执行联接时从其他表中检索行。如果声明相同的类型和大小，MySQL可以更有效地在列上使用索引。在这种情况下， VARCHAR与 CHAR被认为是相同的，如果它们被声明为相同的大小。例如， VARCHAR(10)和 CHAR(10)是相同的大小，但是 VARCHAR(10)和 CHAR(15)不是。【应该是指用于连接两个表的字段大小一致】

  对于非二进制字符串列之间的比较，两个列应使用相同的字符集。例如，将一utf8列与一 latin1列进行比较会排除使用索引。

  如果不能不通过转换直接比较值，则比较不同的列（例如，将字符串列与时间或数字列进行比较）可能会阻止使用索引。对于给定的值，如1 在数值列，它可能比较等于在字符串列，例如任何数量的值 '1'，' 1'， '00001'，或'01.e1'。这排除了对字符串列使用任何索引的可能性。

• 查找特定索引列的MIN()或 MAX()值key_col。这由预处理器优化，该预处理器检查您是否正在 索引中之前出现的所有关键部分上使用。在这种情况下，MySQL为每个表达式或 表达式执行一次键查找，并将其替换为常量。如果所有表达式都用常量替换，查询将立即返回。例如： WHERE key_part_N = constantkey_colMIN()MAX()

  SELECT MIN(key_part2),MAX(key_part2)
    FROM tbl_name WHERE key_part1=10;

• 如果排序或分组是在可用索引的最左前缀（例如）上完成的，则对表进行排序或分组 。如果所有关键部分后面都有，则按相反顺序读取密钥。请参见 第8.2.1.14节“按优化排序”和 第8.2.1.15节“按优化分组”。 ORDER BY key_part1, key_part2DESC

• 在某些情况下，可以优化查询以检索值而无需查询数据行。（为查询提供所有必要结果的索引称为 覆盖索引。）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值，以提高速度：

  SELECT key_part3 FROM tbl_name
    WHERE key_part1=1

 四、索引类型（为主键和外键默认建立索引）

• 主键索引：从非空约束中受益，非空在索引执行效率上有帮助（避免一些复杂的比较、范围判断等）
• 唯一索引：
• 普通索引：
• 全文索引：
• 组合索引：

五、索引的匹配方式

• 全值匹配:比如a,b,c组合索引, where a=1 and b=2 and c=3
• 匹配最左前缀:比如a,b,c组合索引，where a=1 and b=2
• 匹配列前缀:比如a索引,where a like 'ga%'(尝试是range，其他%ga%是index，倒数第二差的方式)
• 匹配范围值:比如a索引,where a > 1
• 精确匹配一列并范围匹配另一列:比如a,b组合索引，where a=1 and b>2
• 只访问索引的查询（覆盖索引）:查询的列都建了索引

参考材料： sakila database

 六、一些名词概念

• 哈希索引：一般用于memory内存型引擎
  • 按照哈希值排序，所以相当于无序
  • 因为存储的事哈希值，所以结构比较紧凑
  • 一般仅适用于精确查找，不适合范围查找
  • 缺点：
    • 哈希索引只包含索引值和行指针，而不存储字段值，不能用于覆盖索引
    • 无序
    • 不支持部分匹配和范围匹配
    • 哈希冲突的时候需要逐行访问比较，效率受到影响；哈希冲突严重的话，影响插入和查询性能【哈希冲突如何解决：除非很牛逼，不然只能用别人写好的哈希算法：crc32（循环冗余校验）】
• 聚簇索引：InnoDB（数据和索引一起）
  • 优点：
    • 相关数据存储一起
    • 数据访问更快（访问索引的时候就可以得到数据）
    • 可以使用覆盖索引
  • 缺点
    • 不适用内存模型
    • 插入速度依赖插入顺序，按照主键的顺序是最快的
    • 更新聚簇索引的代价很大，结合数组长度扩展理解
    • 页分裂问题 - 硬盘空间分为多个格子，索引的大小会影响其存入的方式，如低于格子的50%，可能产生页分裂；而如果大于的话，可能出现页合并
    • 影响全表扫描的速度，和页分裂也有一定关系
• 非聚簇索引：MyisAM（数据和索引分开）
• 覆盖索引（Extra:Using Index）
  • 一般查询的列都创建了索引的情况下即是覆盖索引
  • 需要索引包含了完整的列值
  • 像memory引擎就不支持覆盖索引，其存储的是哈希值和数据指针
  • 优势：
    • 索引行大小通常小于行数据，减少访问数据的IO
    • 索引的顺序访问会提高IO效率
    • InnoDB的聚簇索引特性特别适用覆盖索引

七、优化技巧

• 减少函数或计算（where中）
• 尽量触发主键索引，避免回表操作
• 字符长度长的类型使用前缀索引（减少索引的数据量，但需要注意索引的选择性-重复性索引的问题）
• 使用索引扫描排序，避免文件排序(order by不用索引，或者包含一个asc，一个desc也会造成文件排序)
• union、all、in、or都能使用索引，但建议使用in
• 范围列可以用到索引
  • 使用范围查询之后，下一个索引无法命中（组合索引）
• 强制类型转换会全表扫描
  • 所以关联字段类型要一致，否则出现类型转换会无法使用索引
• 更新十分频繁，数据区分度不高的字段不宜建立索引
  • 更新频繁会不断触发索引构建，所以有些批量插入操作会临时关闭索引，插入完成之后再开启
    • 开启：ALTER TABLE `test` ENABLE KEYS;　　
    • 关闭：ALTER TABLE `test` DISABLE KEYS; 
  • 数据区分度比如这个列只有男和女两种类型，则就算建立了索引，也和哈希取模一样，会有大量的哈希冲突，需要实际到链表之中查询一样，和没有建立索引差别不大
    • 区分度可以通过：select count(distinct columnName)/count(1) from tableNam; (一般超过百分之80可以建立索引)
    • 同样可以对字符串类型的进行前缀的区分度分析select count(distinct left(columnName, size))/count(1) from tableNam; 
• 表连接最好不超过三张，连接字段类型必须一致，否则触发类型转换会使得索引失效
  • cross join 笛卡尔积
  • (inner) join 内连接，左右顺序无关
  • (outer) join : left join | right join，left把左表所有数据查出，right把右表所有数据查出。
• 明确结果只有一条的时候，limit 1性能更高（匹配到一行就会停止扫描），如果触发文件排序就没有差别了。
• 单表索引建议5个以内
• 单索引字段数不超过5个
• 避免过早优化

附录：

　　回表：比如过滤条件是name，查询的字段是age，扫描b+树之后获取到的主键id，再通过主键ID去扫描出数据 【存在于普通索引】

　　索引覆盖：查询的字段直接在索引中，不需要回表查询；以回表的例子来看，我们把查询的字段改为主键id，那就不需要再扫描数据【查询计划中的Extra中会含有Using index】

　　最左匹配：一般用于优化过滤，优先过滤数据较少的表

　　索引下推：一般是指组合索引中，两个过滤条件命中的情况下，会一次过滤，而不是多次回表过滤