索引
索引结构、B+ 树、执行计划与索引优化
信息
索引是一种有序的数据结构,它可以帮助 MySQL 高效获取数据;
但在增删改的场景下,索引节点也需要进行同步操作,会降低效率;
不同的存储引擎使用的索引也是不同的;
| 索引 | Innodb | MyIsam | Memory |
|---|---|---|---|
| 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约束,让优化器更精确的规划查询的过滤条件;