Featured image of post MySQL 索引
技术

MySQL 索引

索引

索引（index）是帮助 MySQL 高效获取数据的数据结构，提高查询效率。

  • 优点

    • 提高数据的查询速度
    • 降低数据库的 IO 成本

  • 缺点

    • 占用磁盘空间
    • 增删改需要维护索引，性能可能会下降

索引的分类

1
2

-- 查看表中的索引
show index from table_name;

主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引。

1
2
3

create table demo (
  id int primary key auto_increment
);

普通索引

用表中的普通列构建的索引，没有任何限制。

1
2
3
4
5
6
7
8
9

create table demo (
  id int primary key auto_increment,
  name varchar(128),
  index demo_index_name(name)
);

---

create index demo_index_name on demo_table(name);

唯一索引

索引列的值必须唯一，允许有多个空值 null（和主键不同，主键不允许为空）。

1
2
3
4
5
6
7
8
9

create table demo (
  id int primary key auto_increment,
  name varchar(128),
  unique index new_unique_index_name(name)
);

---

create unique index new_unique_index_name on demo_table(name);

全文索引

1
2
3
4
5
6

create table demo (
  id int primary key auto_increment,
  name varchar(128),
  content text,
  fulltext index demo_fulltext_index_content(content)
);

组合索引

组合索引 / 联合索引

1

create index demo_name_age_index on demo(name, age);

聚簇索引

  • 索引和数据放在一起，索引结构的叶子节点保存了行数据，找到了索引就找到了数据。

  • 访问数据更快，聚簇索引将索引和数据保存在一个B+树上。

  • 插入速度严重依赖于插入顺序。

  • 更新聚簇索引列的代价很高。

index8

非聚簇索引（二级索引）

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。

clustering-index

  • 二级索引访问需要两次索引查找，要回表。

index9

索引的结构

B Tree

B 树是一种多叉平衡查找树，其主要特点：

  • 树的节点中存储着多个元素，每个内节点有多个分叉。

  • 在所有的节点都储存数据。每个节点中包含键值和数据，节点中的键值从小到大排列。

  • 父节点当中的元素不会出现在子节点中。

  • 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

BTree

B+ Tree

B+ 树是 B 树的升级版。B+ 树在结构上和B树的区别在于：

  • 只有叶子节点才会存储数据，非叶子节点只存储键值，用于查找。

  • 叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

  • 叶子节点保存了父节点所有关键字记录的指针。

B+Tree

相比与 B 树，B+ 树有以下提升：

  • 扫表能力更强（进行全表扫描，只需要遍历叶子节点即可）

  • 具有更好的磁盘读写能力（非叶子节点不存储数据，使树更矮，IO 操作更少，一次磁盘加载扫描范围更大）

  • 查询效率稳定（数据都存储在叶子节点，IO 次数稳定）

  • 增删数据效率更高（数据在叶子节点以有序链表存储，可提高数据增删效率）

数据库中，B+ 树的高度一般都在 2~4 层。由于 MySQL 的 INNODB 引擎在设计时是将 B+ 树的根节点常驻内存的，因此在查找某一键值的行记录时，最多只需要 1~3 次磁盘IO。

Hash

支持 hash 索引的是 Memory 存储引擎。采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。

hash

特点：

  • 只能用于对等比较（=, in），不支持范围查询（between, >, <）
  • 无法利用索引完成排序操作
  • 查询效率高，通常只需要一次检索（无 hash 冲突的情况）

创建索引的原则

🙆 建议创建索引

  • ✅ select 语句，频繁作为 where 条件的字段
1
2
3
4
5
6
7
8

select * from employees where first_name="Georgi";
-- index(first_name)

select * from employees where first_name="Georgi" and last_name="Cools";
-- 联合索引
-- index(first_name, last_name)
-- 注意满足：最左前缀原则
-- index(last_name, first_name) -> where first_name="Georgi" 无法使用索引
  • ✅ update/delete 语句的 where 条件
1
2
3

update employees set first_name="Jim" where emp_no="100001";

delete from employees where fitst_name="Georgi";
  • ✅ 需要分组，排序的字段
1

select dept_no, count(*) from dept_emp group by dept_no;
  • ✅ 使用 distinct 的字段
1

select distinct(first_name) from employees;

  • ✅ 字段需要唯一性约束

    • 唯一索引
    • 主键索引

  • ✅ 多表查询，连接字段应创建索引

    • 类型务必保持一致，避免隐式装换
1
2
3
4
5
6
7

select emp.*, d.dept_name
from employees emp
        left join dept_emp de on emp.emp_no=de.emp_no
        left join departments d on de.dept_no=d.dept_no
where de.emp_no="100001";
-- employees.emp_no  dept_emp.emp_no  类型务必保持一致
-- dept_emp.dept_no  departmants.dept_no  类型务必保持一致

🙅 不建议创建索引

  • ❌ where 子句中用不到的字段

    索引的作用是快速定位到想要的数据，用不到就没有必要创建数据了。

  • ❌ 表里的记录非常少

    如只有 100 条数据，有无影响不大。

  • ❌ 有大量重复数据，选择性低

    如：性别字段，导致大量回表。

    索引的选择性越高，查询效率越好，可以在查找过程中过滤更多的行

  • ❌ 频繁更新的字段，创建索引要考虑维护索引的开销

    频繁修改的字段，不推荐创建索引。

索引失效与解决方案

是否使用索引取决于 🚀 查询代价

即使可以使用索引时，但 MySQL 评估后，不如全表扫描，会放弃使用索引

⚠️ 索引列不独立，进行了计算或变成了参数

  • 索引字段进行了表达式计算
1
2
3

explain
select * form employees
where emp_no + 1 = 100003

1

应事先计算好表达式的值，再传入，避免在 SQL where 的左侧做计算。

  • 索引字段是函数的参数
1
2
3
4

explain
select * 
from employees 
where substring(fitst_name, 1, 2) = "Geo";

事先计算好再传入。

或者使用一些等价的 SQL。

1
2
3
4
5
6

explain
select *
from employees
where first_name list "Geo%";
-- first_name 需要是索引的情况下有效（根据实际场景）
-- 注意 like 索引失效的情况

2

⚠️ 使用了左模糊

1
2
3
4
5
6
7

explain
select * from employees
where first_name like "%Geo%";  -- 无法使用索引

explain
select * from employee
where first_name like "Geo%";  -- 可以使用索引

如果无法避免，且有较高需求，可以考虑使用搜索引擎。

⚠️ or 查询的部分字段没有索引

or 的含义是两个条件满足一个即可，所以只有一个条件列有索引是无用的。只要条件列中有一个不是索引列，就会进行全表扫描。

1
2
3
4
5

explain
select * from employees
where first_name="Georgi" or last_name="Georgi";
-- 假设当前 first_name 有索引，last_name 没有索引，该条 sql 无法使用索引
-- index(fitst_name)

3

解决：根据实际业务，添加相应的索引。

添加索引后，避免了全表扫描。

4

⚠️ 不符合最左前缀原则的查询

1
2
3
4

explain
select * from employees
where first_name="Facello";
-- index(last_name, first_name)

5

1
2
3
4
5

explain
select * from employees
where first_name="Facello";
-- 更换索引顺序
-- index(first_name, last_name)

6

1
2
3
4

select * from employees
where last_name="" and first_name=""
-- index(first_name, last_name)
-- MySQL 的引擎为更好利用复合索引，会动态调整字段顺序

⚠️ 字符串条件没有使用 ""

1
2
3

explain
select * from dept_emp
where dept_no=3;  -- 这里 dept_no 在数据库定义为 varchar

⚠️ 隐式转换导致索引失效

1
2
3
4
5
6
7

select emp.*, d.dept_name
from employees emp
    left join dept_emp de on emp.emp_no=de.emp_no
    left join departments d on de.dept_no=d.dept_no
where de.emp_no="100001";
-- employees.emp_no  dept_emp.emp_no  类型不一致时会导致隐式转换
-- dept_emp de  departments.dept_no  类型不一致时会导致隐式转换

null 条件查询

  • 查询时，采用 is null is not null 条件，根据表中的数据分布，MySQL 会自行判断是否走索引，并不固定

  • MySQL 官方建议尽量把字段定义为 NOT NULL

7

1
2
3

explain
select * from users
where mobile is null;

MySQL 评估全表扫描更快

MySQL 在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。

因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

最左前缀法则

最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

中间不能跳过某一列，否则该列后面的字段索引将失效。

范围查询

联合索引中，出现范围查询(>, <)，范围查询右侧的列索引失效。

覆盖索引

对于索引 X，SELECT 的字段只需要从索引中就能获得，而无需回表获取。

1
2
3
4

-- index(name, age, pos)

select pos from staffs
where name="July" and age=14;

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让 索引变得很大，查询时，浪费大量的磁盘 IO， 影响查询效率。

可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

1
2

-- 为 email 字段，建立索引长度为 5 的前缀索引
create index index_email_5 on tb_user(email(5));

explain

查看 SQL 语句的执行计划。字段含义：

  • id

    • 在一个大的查询语句中，每个 select 关键字都对应一个唯一 id
    • id 相同，执行顺序从上到下；id 不同，值越大，越先执行

  • select_type

    • 表示 select 对应的查询类型
    • SIMPLE (简单 select,不使用 union 或子查询等)
    • PRIMARY (查询中若包含任何复杂的子部分,最外层的 select 被标记为 PRIMARY)
    • UNION (union 中的第二个或后面的 select 语句)
    • SUBQUERY (子查询中的第一个 select)

  • table

    • 这一行的数据是关于哪张表

  • type

    • MySQL 在表中找到所需行的方式
    • 性能由好到差的连接类型为：NULL、system、const、eq_ref）、ref、range、index、all（全表扫描）

  • possible_keys

    • 可能应用在这张表上的索引

  • key

    • 实际使用的索引，如果为 NULL，则没有使用索引

  • key_len

    • 实际使用索引的长度（该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好）

  • ref

    • 上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

  • rows

    • MySQL 认为必须要执行查询的行数，在 innodb 引擎的表中，是一个估计值，不是准确的

  • Extra

    • MySQL解决查询的详细信息

参考

MySQL
Licensed under CC BY-NC-SA 4.0