《开发实战》07 | 数据库索引：索引并不是万能药

boy1007
48 阅读5分钟

07 | 数据库索引：索引并不是万能药

InnoDB 是如何存储数据的？

为了减少磁盘随机读取次数，InnoDB 采用页而不是行的粒度来保存数据，即数据被分成若干页，以页为单位保存在磁盘中。InnoDB 的页大小，一般是 16KB。
在这里插入图片描述

聚簇索引和二级索引

页目录就是最简单的索引。B+树，聚簇索引
在这里插入图片描述

为了实现非主键字段的快速搜索，就引出了二级索引，也叫作非聚簇索引、辅助索引
二级索引：
在这里插入图片描述

创建二级索引的代价，主要表现在维护代价、空间代价和回表代价三个方面
维护代价：
创建 N 个二级索引，就需要再创建 N 棵 B+ 树，新增数据时不仅要修改聚簇索引，还需要修改这 N 个二级索引。删除也是需要维护。
空间代价：
虽然二级索引不保存原始数据，但要保存索引列的数据，所以会占用更多的空间，有时候索引的数据占的内存会比数据本身的占的内存大
回表的代价：
二级索引不保存原始数据，通过索引找到主键后需要再查询聚簇索引，才能得到我们要的数据。
可以根据EXPLAIN查看程序的执行计划
如果Extra 列多了一行 Using index 的提示，证明这次查询直接查的是二级索引，免去了回表，这种情况也叫作索引覆盖。

索引开销的最佳实践
第一，无需一开始就建立索引，可以等到业务场景明确后，或者是数据量超过 1 万、查询变慢后，再针对需要查询、排序或分组的字段创建索引。创建索引后可以使用 EXPLAIN 命令，确认查询是否可以使用索引
第二，尽量索引轻量级的字段，比如能索引 int 字段就不要索引 varchar 字段。索引字段也可以是部分前缀，在创建的时候指定字段索引长度。针对长文本的搜索，可以考虑使用、Elasticsearch 等专门用于文本搜索的索引数据库。
第三，尽量不要在 SQL 语句中 SELECT *，而是 SELECT 必要的字段，甚至可以考虑使用联合索引来包含我们要搜索的字段，既能实现索引加速，又可以避免回表的开销。

不是所有针对索引列的查询都能用上索引

如果创建了一个 name+score 的联合索引，仅搜索 name 时就能够用上这个联合索引。这就引出两个问题：
是不是建了索引一定可以用上？
怎么选择创建联合索引还是多个独立索引？

分析索引失效的情况：
索引只能匹配列前缀：
EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100
那么就无法走索引了，type=all，全表扫描。
把百分号放到后面走前缀匹配。
EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100
type=range 表示走索引扫描。
因为索引 B+ 树中行数据按照索引值排序，只能根据前缀进行比较。

条件涉及函数操作无法走索引
EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7
索引保存的是索引列的原始值，而不是经过函数计算后的值。如果需要针对函数调用走数据库索引的话，只能保存一份函数变换后的值，然后重新针对这个计算列做索引。

联合索引只能匹配左边的列
EXPLAIN SELECT * FROM person WHERE SCORE>45678
在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序
如果我们仅仅按照第二列搜索，肯定无法走索引。尝试把搜索条件加入 name 列，可以看到走了 name_score 索引：EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'
image.png

数据库基于成本决定是否走索引

查询数据可以直接在聚簇索引上进行全表扫描，也可以走二级索引扫描后到聚簇索引回表，选哪个方案取决于成本。
成本分为：包括 IO 成本和 CPU 成本。
IO 成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的 IO 成本常数是 1（也就是读取 1 个页成本是 1）。
CPU 成本，是检测数据是否满足条件和排序等 CPU 操作的成本。默认情况下，检测记录的成本是 0.2。
全表扫描的成本：
把聚簇索引中的记录依次和给定的搜索条件做比较，把符合搜索条件的记录加入结果集的过程。
聚簇索引占用的页面数，用来计算读取数据的 IO 成本；表中的记录数，用来计算搜索的 CPU 成本。

查看表的信息：SHOW TABLE STATUS LIKE '表名'
在这里插入图片描述

  1. MySQL 选择索引，并不是按照 WHERE 条件中列的顺序进行的；
  2. 即便列有索引，甚至有多个可能的索引方案，MySQL 也可能不走索引。

因为这些信息只是估算的，所以有时候会差距比较大，可以牵制走索引
EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00’

可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。

SET optimizer_trace="enabled=on"; -- 打开开关
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00’ --执行SQL
SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 查看表的执行计划
SET optimizer_trace="enabled=off";-- 关闭optimizer_trace 功能

{
  "index": "name_score",
  "ranges": [
    "name84059 < name"
  ],
  "rows": 25362,
  "cost": 30435,
  "chosen": false,
  "cause": "cost"
},


{
  "index": "create_time",
  "ranges": [
    "0x5e2a79d0 < create_time"
  ],
  "rows": 23758,
  "cost": 28511,
  "chosen": false,
  "cause": "cost"
}

最终选择了全表扫描方式作为执行计划。可以看到，全表扫描 100086 条记录的成本是 20306，和我们之前计算的一致，显然是小于其他两个方案的 28511 和 30435

{
  "considered_execution_plans": [{
    "table": "`person`",
    "best_access_path": {
      "considered_access_paths": [{
        "rows_to_scan": 100086,
        "access_type": "scan",
        "resulting_rows": 100086,
        "cost": 20306,
        "chosen": true
      }]
    },
    "rows_for_plan": 100086,
    "cost_for_plan": 20306,
    "chosen": true
  }]
},

把 SQL 中的 create_time 条件从 05:00 改为 06:00，再次分析 OPTIMIZER_TRACE 可以看到，这次执行计划选择的是走 create_time 索引。因为是查询更晚时间的数据，走create_time 索引需要扫描的行数从 23758 减少到了 16588。这次走这个索引的成本19907 小于全表扫描的 20306，更小于走 name_score 索引的 30435：

{
  "index": "create_time",
  "ranges": [
    "0x5e2a87e0 < create_time"
  ],
  "rows": 16588,
  "cost": 19907,
  "chosen": true
}

``
avatar
文章
阅读
粉丝