什么是索引

  • 索引是一种数据结构（而不仅仅是建立了索引的表字段），这种数据结构维持一种有序的状态，因此支持高效的查询。
  • 一般来说，索引指的就是B+tree（多叉搜索树）组织的索引，但也有哈希索引。
  • 索引也很大，往往以索引文件的形式存储在磁盘上。
  • 简单来说，就是排序好的支持快速查找的数据结构。

索引的优势

从两方面考虑：排序成本和查找效率

  1. 排序成本：因为只需要面向索引字段进行排序，而无需面向整张表排序，因此降低了排序成本，减少了cup功耗
  2. 查找效率：因为已经对索引字段进行了排序，因此基于索引（B+树）进行查找效率会很高，意味着更少的查询次数，更少的磁盘IO，从而降低了数据库的IO成本。

索引的劣势

同样从两个方面考虑：空间成本和更新效率

  1. 空间成本：维护索引需要占用额外的空间。
  2. 更新效率：建立索引会大大提高查询效率，但更新数据的同时为了维护索引有序的状态，需要更新索引，因此会降低更新的效率。

索引的分类

  1. 单值索引：一个索引只包含一个字段
  2. 复合索引：一个索引包含多个字段
  3. 唯一索引：带有unique关键字建立的索引，索引列不允许重复，但允许为null
  4. 主键索引：是唯一索引，并且不允许为null

索引相关的语法

  1. 创建索引
    1. create [unique] index 索引名 on 表名(字段名1(length), 字段名2(length), …);
    2. alter 表名 add [unique] index [索引名] on (字段名1(length), 字段名2(length), …);
  2. 删除索引：drop index [索引名] on 表名
  3. 查看指定表上的索引：show index from 表名 [\G]

哪些字段适合建立索引

  1. 主键自动建立索引
  2. 频繁作为where子句中查询条件的字段
  3. 与其他表关联的字段，即：外键
  4. 单值索引/复合索引一般选择建立复合索引
  5. 查询中排序字段
  6. 查询中统计字段或分组字段

哪些字段不适合建立索引

  1. 表记录太少（mysql几百万条记录考虑建立索引）
  2. 经常增删改的表，因为索引会降低更新效率
  3. 数据重复且分布平均的字段不适合建立索引，因此效果不明显

公式：索引选择性=索引列不同值的数目/总数目，比值越大，索引相对于顺序查找效果越明显。

索引优化经验

explain查询结果的表头如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|

  1. 对于单表查询：type=range，即查询中where筛选条件涉及between、>、<、in的索引字段会使得复合索引中该索引字段后的索引字段无效（对于含有order by 或 group by子句）

如：

  • 假设对name, department_id, age 建立了复合索引
  • select * from stu where name = ‘Jack’ and department_id > 2 order by age; department_id使得age索引字段无效。
  • 而如果只对name, age 建立了复合索引，查询效果更好
    1. 对于两表查询，
  • 如果采用left join，说明左表信息都有，查询效果取决于右表如何查询，此时应该将索引建立在右表的连接字段上。
  • 如果采用right join，应该将索引建立在左表的连接字段上。
    1. 对于多表查询，可以按照两表查询的方法，一一为右表（left join）连接字段建立索引，或为左表（left join）连接字段建立索引。
    2. 永远小表驱动大表，in 和 exists 的选择：
  • 如果B表小于A表，则in 优于exists：select * from A where id in (select id from B)
  • 如果A表小于B表，则exists 优于in：select * from A where exists (select 1 from B)

避免索引失效需要遵循的原则

  • 全值匹配我最爱：使用索引字段的个数和顺序与复合索引中字段的个数顺序均相同
  • 最佳左前缀法则：使用索引的顺序从最左前列开始，并且不能跳过中间索引。即：带头大哥必须在（否则，索引全部失效），中间兄弟不能断（否则，只会用到部分索引）。
  • 不要在索引列上做任何操作，包括计算、函数、手动或自动的类型转换
    • 特别地，varchar字符串不加引号索引会失效
  • 存储引擎不能使用与范围条件关联的索引字段右边的字段，即：跟索引优化经验中第1条一致
  • 尽量只使用覆盖索引（索引列与查询列相同），而减少使用select *
  • mysql中在使用了不等于（!=、<>）会导致索引失效而全表扫描，但不得已的情况下可以使用
  • is null或is not null 会导致索引失效转而全表扫描，因此一般给索引列一个默认值
  • 低版本（5.5）中like以通配符（%abc…）开头的查询索引会失效，而以%结尾的查询索引不会失效
    • name like %jack% 索引失效
    • name like %jack 索引失效
    • name like jack% 索引不会失效，虽然违反了最佳左前缀法则，type = range，但因为jack%在某种意 义上是一种定值，索引不会导致范围之后的索引失效。
    • name like jack%rose% 索引不会失效
    • 而高版本（5.7）中三种情况均不会失效（至少type = index，索引全表扫描）
    • 如何解决低版本中 name like %jack% 索引失效的问题：使用覆盖索引、或者查询列等于索引列 + 主键字段
  • 尽量少用or，or会导致索引失效
  • order by排序字段的排序顺序必须一致，要么全升，要么全降。
  • 总结口诀：
    • 全值匹配我最爱
    • 最左前缀要遵守
    • 带头大哥不能死
    • 中间兄弟不能断
    • 索引列上不计算
    • like百分加右边
    • 范围之后全失效
    • 字符串有引号
    • 索引覆盖要写*

order by 排序查询

mysql支持两种排序方式：filesort（文件排序）和 index

  • index索引排序：
    • 是什么：mysql直接使用本身有序的索引完成排序
    • 效率：高
    • 什么情况下使用索引排序：
      • order by子句使用了索引最左前列
      • where子句和order by子句条件列组合满足索引最左前列（mysql索引则作用：调高查询和拍序效率）
  • filesort文件排序：
    • 是什么：当没有建立索引或索引失效后，mysql使用外排序对排序字段进行排序
    • 效率：低
    • 有两种文件排序策略：
      • 双路排序：
        • 特点：需要进行两遍IO，mysql4.1之前的文件排序策略
        • 具体实现过程：第一次IO读取排序字段，在缓冲区sort_buffer中进行内排序，如果一次性读取了所有排序字段的记录，则按照排序后的顺序进行第二次IO，读取数据。假如超出了缓冲区大小，则将部分排序结果写入临时文件，进行归并排序，最后再按照排序后的顺序进行第二次IO，读取数据。
      • 单路排序：
        • 特点：情况好的时候，只需要进行一遍IO，情况差时，可能比双路排序耗时。mysql4.1之后改进的文件排序策略。
        • 具体实现过程：一次性将查询字段和排序字段读取到缓冲区sort_buffer中进行排序，同样，如果一次性读取了所有数据，则只需进行一次IO，如果超出缓冲区大小，则进行归并排序。
    • 那么什么使用单路排序什么时候又使用双路排序呢？
      • mysql配置文件中有一个选项max_length_for_sort_data，当查询字段总和小于这个阈值时，采用单路排序，否则使用双路排序。
      • 注意：无论单路排序还是双路排序，当数据超过mysql配置文件中sort_buffer_size大小时，均会使用归并排序，总体来说，单路排序超出sort_buffer_size大小的风险比双路排序要大。
  • order by 排序查询优化指导原则：
    • order by 时尽量少使用select *，以免超出sort_buffer_size大小
    • 尝试提高sort_buffer_size大小
    • 尝试提高max_length_for_sort_data，从而使用单路排序。