MySQL索引（三）explain实践，优化 MySQL 数据库查询性能
MySQL索引（三）explain实践，优化 MySQL 数据库查询性能
MySQL 安装
为了方便管理，可以采用 docker 安装方式，也是小鱼在本文中给出的安装方式。
MySQL 5.7 安装
docker run -d \
-p 3306:3306 \
--privileged=true \
-v ./mysql5.7/conf/my.cnf:/etc/my.cnf \
-v ./mysql5.7/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql5.7 \
mysql:5.7 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci参数说明：
run运行一个容器-d后台运行-p 3306:3306容器内部端口和服务器端口映射关联--privileged=true设置mysql用户，否则外部不能使用root用户登录-v ./mysql5.7/conf/my.cnf:/etc/my.cnf服务器的/docker/mysql/conf/my.cnf 配置映射到 docker 的 my.cnf-v ./mysql5.7/data:/var/lib/mysql映射数据库的数据目录，避免 docker 删除重新运行 mysql 容器，导致数据丢失-e MYSQL_ROOT_PASSWORD=123456设置root账号的密码--name mysql5.7 mysql:5.7从 docker 镜像 mysql: 5.7启动一个容器，并设置容器的名称为 mysql 5.7--character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci设置数据库默认编码
MySQL 8.0 安装
docker run -d \
-p 3307:3306 \
--privileged=true \
-v ./mysql8/conf/my.cnf:/etc/my.cnf \
-v ./mysql8/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql8 \
mysql:8.0.20 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_general_ci特别注意：
如果使用的 M 系列的 Mac 本的同学，需要找下支持 linux/arm64/v8 的版本。
MySQL 使用
通过命令窗口进入 mysql 容器创建用户
docker exec -it mysql8 bash或者通过可视化工具连接，如 DBeaver 等。此处不与赘述。
我们在容器内登录 mysql。若登录过程出现异常，如初始密码不成功，可能密码默认为空，可以直接回车进入。当出现 mysql> 就表明已经进入到 mysql 命令行。
mysql -u root -p如果远程不能连接，可能需要先创建远程连接用户，指令如下：
create user 'root'@'%' identified by '123456' PASSWORD EXPIRE NEVER;
grant all privileges on *.* to 'root'@'%';
flush privileges;索引实践
创建示例数据表
首先，小鱼创建一个名为 employees 的示例数据表，该表用于存储员工的基本信息。
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';在上面的示例中，我们创建了一个名为 employees 的表，包含了 id、name、age、position 和 hire_time 等字段，其中 id 字段作为主键，并创建了 name、age 和 position 组合的索引。
特别注意：注意下建表时创建的组合索引，后文中不会再特意强调该索引。
插入示例数据
接下来，小鱼向 employees 表中插入了一些示例数据，以便后续的查询和性能优化。
INSERT INTO employees(name,age,position,hire_time) VALUES('张三',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('李四', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('王五',23,'dev',NOW());以上 SQL 语句向表中插入了三条员工记录，分别是张三、李四和王五，每条记录包括姓名、年龄、职位和入职时间等信息。
通过以上步骤，我们成功创建了示例数据表并插入了一些数据，接下来小鱼将通过实际查询来演示如何利用索引来提高数据库的查询性能。
SQL 实践索引优化
全值匹配
EXPLAIN SELECT * FROM employees WHERE name= '张三';
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 22 AND position ='manager';
可以看到 key_len 和 ref 中看到使用了索引字段长度，以及表查找值所用到的常量。
ref：表示 key 列记录的索引中，表查找值所用到的字段或常量。常见的有：const（常量），字段名（例：film. Id）
最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
EXPLAIN SELECT * FROM employees WHERE name = '李四' AND age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 23 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';第一个语句我们可以看到使用了索引。
而第二、第三个语句，没有遵循最左前缀原则，则并没有使用索引。
Using where表示 MySQL 在执行查询时使用了 WHERE 子句来过滤数据。
索引失效
like以通配符开头，mysql 索引失效会变成全表扫描操作。
EXPLAIN SELECT * FROM employees WHERE name LIKE '%四';
而使用通配符结尾又可以使用索引。
EXPLAIN SELECT * FROM employees WHERE name LIKE '李%';
解决 like'%字符串%'索引不被使用的方法？
- 使用覆盖索引，查询字段必须是建立覆盖索引字段。
EXPLAIN SELECT name,age,position FROM employees WHERE name LIKE '%王%';
- 如果查询语句不能使用覆盖索引，可以试试借助搜索引擎，如
Easysearch等。
总结：like 王% 相对于 = 常量，而 王% 和 %王% 相当于范围查找。
- 字符串不加引号会导致索引失效，实际是发生了类型转换。
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
字符串类型如果不加引号，实际上 mysql 底层会进行函数转换，因为使用了函数，所以导致索引失效。
- 在
where条件上做操作，如运算、内置函数、数据类型转换等，都会导致索引失效，最后使用全表扫描查询。
EXPLAIN SELECT * FROM employees WHERE name = '王五';
EXPLAIN SELECT * FROM employees WHERE left(name,1) = '王五';
但是如果我们再增加一个缩影，如给 hire_time 增加一个普通索引，此时再去使用函数包装查询，可能就走索引了。
增加索引：
ALTER TABLE `employees` ADD index `idx_hire_time` (`hire_time`) using btree; 现在执行下列 sql 查询，发现还是不会走索引。
EXPLAIN SELECT * FROM employees WHERE date(hire_time) = '2024-03-31'; 
我们如果将该 sql 优化成日期范围查询，此时发现走索引了。
EXPLAIN SELECT * FROM employees WHERE hire_time >= '2024-03-31 00:00:00' AND hire_time <= '2024-03-31 23:59:59'; 
再删除该索引，还原到建表时的索引状态。
ALTER TABLE `employees` DROP index `idx_hire_time`;索引中范围条件右边的列无法使用索引
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age > 22 AND position ='manager';
我们可以看到第一个 sql 语句符合最左前缀原则，使用了所有组合索引字段。而第二个语句则仅使用了组合索引的第一个字段。
尽可能使用覆盖索引
EXPLAIN SELECT name,age FROM employees WHERE name= '李四' AND age = 23 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= '李四' AND age = 23 AND position ='manager';
回顾覆盖索引：如何看懂explain工具信息，使用explain工具来分析索引
不等于 (<>或者!=)、not in、not exists 无法使用索引
EXPLAIN SELECT * FROM employees WHERE name != '王五';
is null 或者 is not null 通常也不会走索引。
EXPLAIN SELECT * FROM employees WHERE name is null
若是使用 >、<、<=、>=条件，mysql 内部会进行优化，评估是否使用索引。
语句使用 in、or 时不一定会使用索引
EXPLAIN SELECT * FROM employees WHERE name != '王五' or name = '张三';
同样，mysql 内部进行优化时，会根据索引比例、表大小等等因素，决定是否使用索引。
范围查找优化
我们先给 age 字段增加索引：
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;EXPLAIN SELECT * FROM employees WHERE age >=1 AND age <= 50;
我们发现并没有走索引，原因同样也是 mysql 内部进行优化时，会根据索引比例、表大小等等因素，决定是否使用索引。
EXPLAIN SELECT * FROM employees WHERE age >= 1 AND age <= 22;
EXPLAIN SELECT * FROM employees WHERE age >= 23 AND age <= 50;
我们可以对查询范围进行拆分成多个小范围，这样也可以走索引。
删除索引
ALTER TABLE `employees` DROP INDEX `idx_age`;总结
