栈记
MySQL

索引

索引结构、B+ 树、执行计划与索引优化

信息

索引是一种有序的数据结构,它可以帮助 MySQL 高效获取数据;

但在增删改的场景下,索引节点也需要进行同步操作,会降低效率;

不同的存储引擎使用的索引也是不同的;

索引InnodbMyIsamMemory
B+Tree(常用)✅✅✅
Hash❌❌❌
Full-Text 全文索引5.6 之后支持✅❌
R-Tree 地理空间索引❌✅❌

索引分类

  • 主键索引
  • 唯一索引
  • 常规索引
  • 全文索引

根据存储形式不同

  • 聚集索引
  • 二级索引(辅助索引,非聚集索引)

如果存在主键,逐渐索引就是聚集索引;

如果不存在主键,第一个唯一字段,将会被作为聚集索引;

如果没有主键,也没有唯一字段,innodb 会自动生成一个 rowid,作为隐藏的聚集索引

索引语法

-- unique 唯一索引
-- fulltext 全文索引
-- 不写表示默认索引
create {unique | fulltext} index 索引名称 on 表名(字段名...);

-- 查看索引
show index from 表名;

-- 删除索引
drop index 索引名称 on 表名;

索引练习

-- 创建表语法
create database db1 charset utf8mb4;
use db1;

create table emp(
    id int primary key auto_increment,
    name varchar(16) not null,
    gender enum('male', 'female') not null,
    phone varchar(11) not null,
    email varchar(50),
    age int not null,
    salary float(10, 2),
    post varchar(16),
    join_date date,
    leader_id int
);

-- 通过 AI Prompt 生成数据练习
-- 需要注意的是练习的时候还是通过终端,避免客户端的连接工具执行一些不必要的操作;

查看当前表的索引


show index from emp;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |          30 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

-- 符合要求,默认索引就是主键索引
-- Index_type 显示 BTREE 就是 b+树

创建索引,索引的名称的规范 idx_表名_字段名 尽可能符合规范

-- 为 name 创建索引, 后续再执行 show index from emp; 就会多出来一条索引数据
create index idx_emp_name on emp(name);  

-- 创建唯一索引,前提是这个数据在表中是唯一的
create unique idx_emp_phone on emp(phone);

-- 创建联合索引,就是指定多个字段, 查看索引时会出现多条数据;
create index idx_emp_age_salary_post on emp(age, salary, post);

创建新的表用来测试全文索引

create table articles (content varchar(5000));

-- 如果没有 with parser ngram 就仅支持英文检索 5.7.6+ 支持
create fulltext index idx_articles_content on articles(content) with parser ngram;
-- 增加全文之后查询中的条件也会出现对应的变化
-- 匹配 content 字段中带有你好的内容 (不常用)
select * from articles where match(content) against ('你好');

-- 删除
drop index idx_articles_content on articles;

SQL 分析 - 查询执行频次

查询 sql 的执行频次

-- 查出来的数据太多,大部分场景只需要知道 curd 的就可以了
-- 可以指定 gloabl 和 session 如果不指定就是 session 会话级别
show global status;

show global status where variable_name rlike 'com_[idus][enp].{4}$';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 4     |
| Com_insert    | 45    |
| Com_select    | 756   |
| Com_update    | 15    |
+---------------+-------+

SQL 分析 - 慢查询日志

上面的数据仅知道需要优化查询语句,但是不知道具体哪些语句需要优化;

可以通过 慢查询日志 查看需要优化的具体内容。默认只要查询超过 10s 的数据就会放在日志中。

默认情况下慢查询日志是没有打开的;

-- 查看慢查询日志的状态和存放地址
show variables like 'slow%';

+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_launch_time    | 2                                    |
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/b7025f74112f-slow.log |
+---------------------+--------------------------------------+

-- 查看慢查询日志
select @@slow_query_log;
-- 开启日志, 设置全局
set @@global.slow_query_log = 1;

-- 查询慢查询阈值, 这个会话和全局都有一份, 注意你要操作的是哪个
select @@global.long_query_time
-- 设置慢查询日志的时间阈值
set @@global.long_query_time = 2;

在设置变量的时候,如果不指定默认会修改会话的变量,当没有找到时才会修改全局;

实际上就算是全局变量重启服务后也会被重置,如果需要永久保存就需要放在配置文件中, 默认在 etc/my.cnf。

SQL 分析 - profile

profile 可以查询某一个语句具体慢在哪里;使用之前需要看当前数据是否支持。

-- 查看是否支持 profile
select @@have_profiling;

-- 查看是否开启 profile
select @@profiling;

-- 开启 profile,同时支持会话和全局模式
set @@profiling=1;

-- 查询最近几条数据的执行情况
show profiles;

+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.00914925 | show tables                                         |
|        2 | 0.00132450 | select * from emp                                   |
|        3 | 0.01014000 | update emp set email = '123@163.com' where age = 19 |
|        4 | 0.00078150 | select * from emp where age = 19                    |
+----------+------------+-----------------------------------------------------+

-- 查询具体的 sql 执行耗时
show profile for query [Query_ID];
-- 可以增加一列 cpu 的耗时查询
show profile cpu for query 4;

SQL 分析 - explain

单纯通过执行耗时评估 sql 的执行性能有点不合理;比较常用的是通过 explain | desc 查看 sql 的执行计划。

在要执行的语句前增加该关键字,desc 最后也会被解析成 explain 去执行;

-- 如果是多表查,就会出现多条数据
explain select * from emp;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   30 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

-- select_type: SIMPLE 表示单表查询
-- type: 访问类型,性能顺序为 all < index < range < ref < eq_ref < const < system < null
--       null 就是不访问任何表
--       system 表中只有一条数据的时候才是 system
--       const 根据主键或者唯一索引查询
--       ref 通过非唯一索引是
--       range 在索引上进行范围查询
--       index 查询的时候走索引
-- key: 查询过程中实际用到的索引
-- possible_keys: 查询时可能用到的索引

索引失效场景 - 联合索引

最左前缀法则:最左列必须存在,并且不能跳过索引中间的列,如果跳过了 联合索引 中的某一列,后面的字段也会失效;如果出现了范围查询右侧的索引就会失效;关于左右的说法主要来自索引的 Seq_in_index 中的序号

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          1 | idx_age_salary_post |            1 | age         | A         |          15 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_age_salary_post |            2 | salary      | A         |          27 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | idx_age_salary_post |            3 | post        | A         |          27 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
-- 以 emp 表中的 age salary post 联合索引为例; idx_emp_age_salary_post
-- 最左前缀也就是说,设置索引的第一列一定要在查询条件中

-- 正常查询会用到所有索引
select * from emp where age = 25 and salary = 2400 and post = 'xx';

-- 如果顺序调整后就不会用到所有的索引,如果 age 没有的话则索引失效;
select * from emp where salary = 24000 and post = 'xx'; -- 索引失效

-- 通过返回查询就会使索引失效, 如果不想失效可以使用 <= 这样的范围
select * from emp where age < 24;

索引失效场景 - 单列索引

  • 不要使用运算操作;
  • 如果用 like 查询可以使用后面模糊,但是不能使用前面模糊;
  • 字符串不加引号也会导致索引失效
  • 用 or 的情况,两边的情况都要有索引不然会失效
  • 数据分部影响,在范围查询时优化器会评估是全表扫描快还是索引快,并不是加了索引就一定会走索引;
-- 运算场景失效:通过 substring 匹配手机号后缀
explain select * from emp where substring(phone, 8, 4) = 5001;

-- like 失效场景
explain select * from emp where name like "%x";

-- 字符串不加索引
explain select * from emp where phone = 13035445001;

-- or 场景, name 和 age 都要有索引不然就失效
explain select * from emp where age = 20 or name = 'zs';

SQL 提示

某些场景下,可能给一个字段即创建了联合索引又创建了单列索引;默认情况下在查询的时候就完全凭优化器选择了;

也可以通过语法来自己指定;

  • use index() 建议走某个索引查询
  • ignore index() 忽略这个索引
  • force index() 强制走某个索引查询
explain select * from emp where age = 25;

+----+-------------+-------+------------+------+---------------------------------+---------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys                   | key                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------------------+---------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | idx_age_salary_post,idx_emp_age | idx_age_salary_post | 4       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------------------+---------------------+---------+-------+------+----------+-------+

explain select * from emp use index(idx_emp_age)  where age = 25;
explain select * from emp ignore index(idx_emp_age) where age = 25;
explain select * from emp force index(idx_emp_age) where age = 25;

覆盖索引

查询的时候使用了索引且查询的字段是索引的值,就是覆盖索引;该种查询不需要回表查询性能更高;

-- age salary post 是联合索引,但是查了整行的数据
select * from emp where age = 25 and salary = 2400 and post = 'x';

-- 该种场景不会回表查询
select age, salary, post where age = 25 and salary = 2400 and post = 'x'; 

前缀索引

-- 使用 email 列的前四个字符创建索引
create index idx_emp_email_4 on emp(email(4));

总结

  • 对于数据量较大且查询比较频繁的表,就可以创建索引
  • 一般都是选择查询条件、排序条件或者分组条件用到的字段来创建索引
  • 选择字段的时候尽量选择区分度较高的列作为索引,尽量创建唯一索引,区分度越高效率越高
  • 尽量创建联合索引,很多场景下可以覆盖索引,效率更高(避免回表查询)
  • 字符串较长可以创建前缀索引,降低索引的体积,减少磁盘 I/O
  • 索引不是越多越好,增删改的时候会降低效率,每次增删改都会维护索引。
  • 如果一个字段不能为 null 要增加 not null 约束,让优化器更精确的规划查询的过滤条件;

On this page