文章

MySQL 单表访问方法

单表访问方法

先创建一张表：

CREATE TABLE `employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100),
  `employee_id` INT,
  `english_name` VARCHAR(100),
  `country` VARCHAR(100),
  `province` VARCHAR(100),
  `city` VARCHAR(100),
  `extra_info` VARCHAR(100),
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  UNIQUE KEY `idx_employee_id` (`employee_id`),
  KEY `idx_english_name` (`english_name`),
  KEY `idx_country_province_city` (`country`, `province`, `city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入测试数据的脚本放在了文章末尾

MySQL 中有两种方式执行查询：

(1) 使用全表扫描进行查询

(2) 使用索引进行查询

使用索引执行查询又可以细分为 4 类：

  1. 针对主键索引或着唯一二级索引的等值查询

  2. 针对普通二级索引的等值查询

  3. 针对索引列的范围查询

  4. 扫描整个索引

MySQL 把执行查询语句的方式称为访问方法（access method），下面具体了解下 MySQL 中的各种访问方法：

const

针对主键索引的等值查询，如：

SELECT * FROM employee WHERE id = 3;

以及针对唯一二级索引的等值查询，如：

SELECT * FROM employee WHERE employee_id = 3;

上面这两种查询的速度是非常快的，因此 MySQL 给它的访问方法取名为 const，意思是常数级别的查询。

注意：对于唯一二级索引来说，查询列为 NULL 时其访问方法有所不同，如：

SELECT * FROM employee WHERE employee_id IS NULL;

由于唯一二级索引不限制索引列的值为 NULL 的记录数，因此上面这条 SQL 可能查到多条记录，故其访问方法不是 const，而是下面要介绍的 ref。

普通二级索引也不限制索引列的值为 NULL 的记录数

ref

针对普通二级索引的等值查询，如：

SELECT * FROM employee WHERE name = 'Tom';

由于普通二级索引不限制索引列值的唯一性，因此可能找到多条记录，也就是说普通二级索引的等值查询代价取决于匹配到的记录数量。如果匹配的记录比较少，那么回表的代价是比较低的，此时 MySQL 会倾向于使用索引而不是全表扫描。对于普通二级索引等值查询，当使用二级索引而不是全表扫描执行该查询时，MySQL 给它的访问方法取名为 ref，ref 的性能要比 const 差一些。

注意：对于联合索引来说，如果符合最左匹配原则原则的查询列不全是等值查询的话，其访问方法不是 ref，如：

-- 下面这 3 条 SQL 的访问方法在匹配到的记录不多时可能是 ref
SELECT * FROM employee WHERE country = '中国';
SELECT * FROM employee WHERE country = '中国' AND province = '江苏';
SELECT * FROM employee WHERE country = '中国' AND province = '江苏' AND city = '南京';

-- 下面这条 SQL 的访问方法不会是 ref，而是 range
SELECT * FROM employee WHERE country = '中国' AND province > '江苏';

ref_or_null

有时候我们不仅想找出某个二级索引列等于某个值的情况，还想把该索引列值为 NULL 的记录也找出来，如：

SELECT * FROM employee WHERE name = 'Tom' OR name IS NULL;

当使用二级索引而不是全表扫描执行上面这种查询时，MySQL 给它的访问方法取名为 ref_or_null，其性能要比 ref 差一些。

上面说的二级索引包括唯一二级索引和普通二级索引

在表数据量少的时候，上面的 SQL 可能走全表扫描，即其访问方法不一定是 ref_or_null

range

前面介绍的几种访问方法都是针对索引列的等值查询，很多时候我们也有对索引列进行范围查询的需求，如：

SELECT * FROM employee WHERE employee_id IN (520, 1314) OR employee_id < 10;

MySQL 可能使用全表扫描来执行这个查询，也可能使用`索引 + 回表`的方式来执行。当使用`索引 + 回表`的方式来执行上面这种查询时，，MySQL 给它的访问方法取名为 range，其性能一般比 ref_or_null 差一些。

注意：上面说的索引包括聚簇索引和二级索引

index

看一个查询：

SELECT province, city FROM employee WHERE province = '江苏'; 

对于这种查询列（这里是：province, city）都在索引列（这里是：country, province, city）中，并且查询条件中的列（这里是：province）不符合最左匹配原则的查询，通常会遍历整个二级索引，MySQL 给它的访问方法取名为 index，其性能要比 range 差一些。

ALL

访问方法 ALL 指的是全表扫描，对于 InnoDB 来说是直接扫描整个聚簇索引，性能最差。

其他相关知识

一般只会用到单个二级索引

一般情况下，MySQL 只会使用一个二级索引进行查询，如：

SELECT * FROM employee WHERE name = 'Tom' AND employee_id > 10;

查询优化器会根据表的统计数据选择使用扫描行数更少的索引来执行查询，假设优化器选择 idx_name 索引来执行查询，其过程如下：

  1. 二级索引定位记录阶段，根据 name = 'Tom' 查询条件，从 idx_name 索引中找到符合条件的索引记录

  2. 回表阶段，根据上一步找到的记录的主键值从聚簇索引中找到对应的完整记录

  3. 过滤阶段，根据 employee_id > 10 的查询条件在上一步获取到的完整记录中进行过滤，得到最终符合所有条件的记录

注意：一般情况下执行一个查询只会用到单个二级索引，不过也有特殊情况 

明确range访问方法使用的范围区间

只要索引列对一个值使用 > 、< 、>= 、<= 、=、!=、<>、<=>、BETWEEN、IN、NOT IN、IS NULL、IS NOT NULL 或 LIKE，就可以产生一个范围区间。当我们想使用 range 访问方法来执行一个查询语句时，重点就是找出该查询可用的索引以及这些索引对应的范围区间，找到范围区间后只需要回表并过滤记录就完成了查询的执行。下边看一下怎么从 AND 或 OR 组成的复杂搜索条件中提取出正确范围区间的两种情况：

(1) 所有查询条件都可以使用同一个索引的情况

查询条件使用 AND 连接的情况

如果所有查询条件都可以使用同一个索引，并且查询条件使用 AND 连接，如：

SELECT * FROM employee WHERE employee_id > 10 AND employee_id < 100;

对这个查询，它使用索引查询的范围区间是两个查询区间的交集，即 (10, 100)。

查询条件使用 OR 连接的情况

如果所有查询条件都可以使用同一个索引，并且查询条件使用 OR 连接，如：

SELECT * FROM employee WHERE employee_id < 10 OR employee_id > 1000;

对这个查询，它使用索引查询的范围区间是两个查询区间的并集，即 (0, 10) U (1000, +∞)。

#### (2) 有的查询条件无法使用索引的情况

查询条件使用 AND 连接的情况

如果查询条件使用 AND 连接，并且有的查询条件无法使用索引，如：

SELECT * FROM employee WHERE employee_id < 10 AND extra_info = 'abc';

这个查询能使用的只有 idx_employee_id 这个索引，并且索引中没有 extra_info 这一列，因此 extra_info = 'abc' 这个查询条件是在回表后过滤记录使用的。由于范围区间表示的是在二级索引寻找记录的范围，因此在回表前用不到的查询条件可以用 TRUE 替代：

SELECT * FROM employee WHERE employee_id < 10 AND TRUE;

继续简化：

SELECT * FROM employee WHERE employee_id < 10;

根据简化后的 SQL 可知，对于这个查询，使用 idx_employee_id 索引查询的范围区间是 (-∞, 10)

查询条件使用 OR 连接的情况

如果查询条件使用 OR 连接，并且有的查询条件无法使用索引，如：

SELECT * FROM employee WHERE employee_id < 10 OR extra_info = 'abc';

初步简化：

SELECT * FROM employee WHERE employee_id < 10 OR TRUE;

继续简化：

SELECT * FROM employee WHERE TRUE;

根据简化后的 SQL 可知，如果这个查询要使用 idx_employee_id 索引，就需要把所有索引记录回表，因此其使用 idx_employee_id 索引查询的范围区间是 (-∞, +∞)

实战：复杂查询条件下找出范围匹配的区间

以下面这个查询为例：

SELECT * FROM employee 
WHERE (name < 'Louis' AND employee_id = 2333) 
OR (name < 'Jerry' AND name > 'Tom') 
OR (name LIKE '%伟' AND name < 'Amy' AND (employee_id < 8000 OR extra_info = 'abc'));

这个查询符合有的查询条件无法使用索引的情况，其可使用的索引有两个：idx_name 和 idx_employee_id，因此需要分情况讨论：

(1) 使用 idx_name 索引

初步简化：

SELECT * FROM employee 
WHERE (name < 'Louis' AND TRUE) 
OR FALSE 
OR (TRUE AND name < 'Amy' AND (TRUE OR TRUE));

继续简化：

SELECT * FROM employee 
WHERE name < 'Louis' 
OR FALSE 
OR name < 'Amy';

此时，符合所有查询条件都可以使用同一个索引的情况，OR 取并集：

SELECT * FROM employee WHERE name < 'Amy';

最终使用 idx_name 索引的范围区间为 name < 'Amy'，因此有可能使用 idx_name 索引。

(2) 使用 idx_employee_id 索引

初步简化：

SELECT * FROM employee 
WHERE (TRUE AND employee_id = 2333) 
OR (TRUE AND TRUE) 
OR (TRUE AND TRUE AND (employee_id < 8000 OR TRUE));

继续简化：

SELECT * FROM employee 
WHERE employee_id = 2333 
OR TRUE 
OR TRUE;

此时，符合所有查询条件都可以使用同一个索引的情况，OR 取并集：

SELECT * FROM employee WHERE TRUE;

最终使用 idx_employee_id 索引的范围区间为 (-∞, +∞)，因此并不会使用 idx_employee_id 索引。

索引合并

一般情况下执行一个查询最多只会用到一个二级索引，但是在某些特殊情况下也可能在一个查询中使用到多个二级索引，这种使用到多个索引来完成一次查询的执行方法称之为：index merge，即索引合并，下面介绍三种索引合并算法：

(1) Intersection 合并

Intersection 是交集的意思，而 Intersection 合并指的是将从多个二级索引查到的结果集取交集，发生在 AND 查询中，如：

SELECT * FROM employee WHERE name = '张伟' AND english_name = 'Zhang_Wei';

EXPLAIN 的 extra 列中出现：Using intersect 表示使用了 Union 合并

假设这个查询使用 Intersection 合并的方式执行的话，那这个过程大致是这样的: 

  1. 从 idx_name 索引中取出 name = '张伟' 的记录 A

  2. 从 idx_english_name 索引中取出 english_name = 'Zhang_Wei' 的记录 B

  3. 二级索引都包含主键 id，因此计算记录 A 和记录 B 中的 id 的交集

  4. 根据上面计算出来的 id 交集进行回表操作，取出这些 id 对应的完整的用户记录

可能会使用 Intersection 索引合并的情况

(1) 二级索引所有列都是等值匹配

比如下面这个查询可能用 idx_name 和 idx_country_province_city 这两个二级索引进行 Intersection 索引合并操作：

SELECT * FROM employee WHERE name = '张伟' AND country = '中国' AND province = '湖北' AND city = '武汉'; 

而下边这两个查询就不能进行 Intersection 索引合并:

-- 查询1
SELECT * FROM employee WHERE name < '张伟' AND country = '中国' AND province = '湖北' AND city = '武汉';

-- 查询2

SELECT * FROM employee WHERE name = '张伟' AND country = '中国' AND province = '湖北';

第一个查询是因为使用了范围查询，第二个查询是因为只等值匹配了联合索引 idx_country_province_city 中的部分列，所以不会使用 Intersection 索引合并。

对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况

(2) 主键列可以是范围匹配

比如下面这个查询可能用 idx_name 和 主键 进行 Intersection 索引合并操作：

SELECT * FROM employee WHERE id < 100 AND name = '张伟'; 

Intersection 合并的原理

至于为什么上面举的例子有的可以进行 Intersection 合并有的不行，我们需要了解下 Intersection 合并的原理：

Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询到的结果集本身就是已经按照主键排好序的，那么求交集的过程就会很快，其时间复杂度是O(n)级别。因此 MySQL 只在所有二级索引查询到的结果集中的主键都已经排好序的情况下，才可能使用 Intersection 合并。

我们知道对于 InnoDB 的二级索引来说，记录是按照索引列进行排序的，如果该二级索引是一个联合索引，那么会按照联合索引中的各个列依次排序。而二级索引叶子节点中的记录是由 索引列 + 主键 构成的，当索引列的值相同时，这些记录会再按主键值进行排序。可知，只有使用二级索引等值查询时才能保证结果集中的主键有序，因此上面介绍的可能会使用 Intersection 索引合并的两种情况就可以理解了。

按照有序的主键值去回表取记录叫 Rowid Ordered Retrieval，简称ROR。

当每个索引查询出来的结果集都不太大时，才可能进行 Intersection 索引合并操作

使用联合索引替代 Intersection 索引合并

SELECT * FROM employee WHERE name = '张伟' AND english_name = 'Zhang_Wei';

对于我们上面提到的这个查询语句，如果条件允许的话，可以将 idx_name 和 idx_english_name 这两个单独的索引替换成一个联合索引 idx_name_english_name (name, english_name)，这样就不用进行索引合并操作了，可以少查询一颗索引树，并且还节省了一些存储空间。

(2) Union 合并

Union 是并集的意思，即计算多个索引查询结果集的并集，适用于使用不同索引的查询条件之间使用 OR 连接起来的情况，如：

SELECT * FROM employee WHERE name = '张伟' OR english_name = 'Zhang_Wei'; 

EXPLAIN 的 extra 列中出现：Using union 表示使用了 Union 合并

可能会使用 Union 索引合并的情况

(1) 二级索引所有列都是等值匹配

对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况

比如：

SELECT * FROM employee WHERE name = '张伟' OR (country = '中国' AND province = '湖北' AND city = '武汉');

而下边这两个查询就不能进行 Union 索引合并:

-- 查询1
SELECT * FROM employee WHERE name < '张伟' OR (country = '中国' AND province = '湖北' AND city = '武汉');

-- 查询2
SELECT * FROM employee WHERE name = '张伟' OR (country = '中国' AND province = '湖北');

第一个查询是因为使用了范围查询，第二个查询是因为只等值匹配了联合索引 idx_country_province_city 中的部分列，所以不会使用 Union 索引合并。

(2) 主键列可以是范围匹配

比如：

SELECT * FROM employee WHERE id < 100 OR name = '张伟'; 

(3) 复合情况（同时存在 Intersection 和 Union 索引合并）

比如：

SELECT * FROM employee WHERE country = '中国' AND province = '湖北' AND city = '武汉' OR (name = '张伟' AND english_name = 'Zhang_Wei'); 

其 Explain 的 Extra 列可能出现： Using union(idx_country_province_city,intersect(idx_english_name,idx_name))，即先对 idx_english_name 和 idx_name 索引使用 Intersection 索引合并获取一个主键集合 A，然后对 idx_country_province_city 和 主键集合 A 进行 Union 索引合并获取最终的主键集合 B，最后使用主键集合 B 回表查询对应的用户记录。

(3) Sort-Union 合并

Sort-Union 合并相比 Union 合并多了一步给二级索引查询结果集的主键排序的过程，如：

SELECT * FROM employee WHERE name < '张伟' OR english_name > 'Zhang_Wei'; 

如果上面的查询使用 Sort-Union 合并，其过程大致如下：

  1. 先根据 name < '张伟' 查询条件从 idx_name 二级索引中获取记录，并按记录的主键值进行排序

  2. 再根据 english_name > 'Zhang_Wei' 查询条件从 idx_english_name 二级索引中获取记录，并按记录的主键值进行排序

  3. 剩下的操作和 Union 索引合并方式一致

为什么没有 Sort-Intersection ?

Sort-Union 适用于二级索引中查询出来的结果集比较小的场景，这样对结果集进行排序的成本不会太高。

而 Intersection 合并适用于二级索引中查询出来的结果集相对较多，导致回表成本比较高，合并后可以降低成本的场景。因此，不适合对这些较大的结果集进行排序，否则成本可能比直接回表还高。

插入测试数据

#!/bin/bash

# 数据库配置
DB_HOST="localhost"
DB_USER="your_username"
DB_PASS="your_password"
DB_NAME="your_database"
TABLE_NAME="employee"

# 数据源
names=("张伟" "李娜" "王强" "刘洋" "陈杰" "杨敏" "赵磊" "黄丽" "吴刚" "周颖")
english_names=("Zhang_Wei" "Li_Na" "Wang_Qiang" "Liu_Yang" "Chen_Jie" "Yang_Min" "Zhao_Lei" "Huang_Li" "Wu_Gang" "Zhou_Ying")
countries=("中国" "美国" "加拿大" "英国" "澳大利亚" "印度" "日本" "德国" "法国" "意大利")
provinces=("北京" "上海" "广东" "江苏" "浙江" "四川" "山东" "河南" "陕西" "湖北")
cities=("东城区" "西城区" "浦东新区" "黄浦区" "广州" "杭州" "成都" "青岛" "郑州" "武汉")

# 函数生成随机索引
random_index() {
    local array_length=$1
    echo $((RANDOM % array_length))
}

# 获取数组长度
get_array_length() {
    local array=("${!1}")
    echo "${#array[@]}"
}

# 初始化
batch_size=100
counter=0

# 构建插入语句
while ((counter < 10000)); do
    insert_values=""
    for ((i=1; i<=batch_size; i++)); do
        index=$((counter + i))
        name="${names[$(random_index $(get_array_length names[@]))]}"
        english_name="${english_names[$(random_index $(get_array_length english_names[@]))]}"
        country="${countries[$(random_index $(get_array_length countries[@]))]}"
        province="${provinces[$(random_index $(get_array_length provinces[@]))]}"
        city="${cities[$(random_index $(get_array_length cities[@]))]}"
        extra_info="额外信息_$((RANDOM % 100 + 1))"
        
        # Escape single quotes in strings for SQL
        name=$(printf "%s" "$name" | sed "s/'/''/g")
        english_name=$(printf "%s" "$english_name" | sed "s/'/''/g")
        country=$(printf "%s" "$country" | sed "s/'/''/g")
        province=$(printf "%s" "$province" | sed "s/'/''/g")
        city=$(printf "%s" "$city" | sed "s/'/''/g")
        extra_info=$(printf "%s" "$extra_info" | sed "s/'/''/g")
        
        insert_values+="('$name', $index, '$english_name', '$country', '$province', '$city', '$extra_info'),"
    done

    # 移除最后一个逗号
    insert_values=${insert_values%,}

    # 执行插入语句
    mysql -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" -e "
        INSERT INTO $TABLE_NAME (
            name, employee_id, english_name, country, province, city, extra_info
        ) VALUES $insert_values;
    "

    # 打印进度提示
    echo "已插入 $((counter + batch_size)) 条记录..."

    # 更新计数器
    counter=$((counter + batch_size))
done

echo "插入完成"

MySQL
License:  CC BY 4.0