MySQL优化--索引-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_62967939/article/details/148351567

一、索引介绍

什么是索引：索引是MySQL提供的一种高效处理数据的结构。

索引能干什么：加速查询，提高SQL执行效率。

索引的危害：占用空间，降低写操作速度。

二、索引分类

存储结构分类
1. B+索引（默认）：支持范围、排序，适用等值、范围查询。
  MySQL对经典的B+索引的基础上，维护了一个指向相邻叶子结点的链表指针，形成了带有顺序指针的B+Tree，从而提高区间访问能力。
```
-- 创建B+树索引
CREATE INDEX idx_name ON table_name(column_name);
```
2. Hash索引：仅支持等值（极快），不支持排序、范围。
```
-- 创建Hash索引（某些数据库支持）
CREATE INDEX idx_name ON table_name(column_name) USING HASH;
```
3. 全文索引：支持关键词匹配、模糊，适用搜索引擎、文本查询。
```
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);
```
4. 空间索引：支持点线面等几何、适用GIS地理空间查询。
```
-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON maps(coordinates);
```
作用分类
1. 主键索引：标识唯一行数据，不允许为NULL，且一张表有且仅有一个。
```
-- 创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
```
2. 单值索引：只包含单列，最常见。
```
-- 创建单值索引
CREATE INDEX idx_single ON table_name(column_name);
```
3. 唯一索引：确保索引列值唯一，允许NULL值，用于避免重复。
```
-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);
```
4. 复合索引：包含多列，适合多列组合查询，使用时注意最左前缀原则。
```
-- 创建复合索引
CREATE INDEX idx_composite ON table_name(col1, col2, col3);
```

三、索引优化方案

索引结构选择

例如实际业务中多见等值场景，则在等值列上Hash索引。

最佳左前缀匹配原则

DROP TABLE if EXISTS students
CREATE table students(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主键id",
	sname VARCHAR(20) COMMENT "学生姓名",
	age INT COMMENT "年龄",
	score INT COMMENT "成绩",
	`time` TIMESTAMP COMMENT "时间"
)

INSERT INTO students(sname,age,score,`time`) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,`time`) VALUES('小绿',24,80,now());
INSERT INTO students(sname,age,score,`time`) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,`time`) VALUES('小黑',23,70,now());

ALTER TABLE students ADD INDEX idx_sname_age_score(sname,age,score);

#正常
EXPLAIN SELECT * FROM students where sname = "小明" AND age = 22 AND score = 100;
EXPLAIN SELECT * FROM students where sname = "小明" AND age > 20;

#最佳左前缀法则匹配，顺序
EXPLAIN SELECT * FROM students where sname = "小明";
EXPLAIN SELECT * FROM students where age = 22;  #失效
EXPLAIN SELECT * FROM students where sname = "小明" AND age = 22;

复合索引的创建顺序依赖于多列组合判断的顺序。

失效场景

复合索引违背最佳左前缀匹配原则
范围条件右边的列
模糊匹配
索引列使用函数（聚合，日期等）
索引列使用字符串/数字隐式转换
OR关键字

使用场景

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中与其它表关联的字段，外键关系建立索引
多字段查询下倾向创建组合索引
查询中排序的字段
查询中统计或者分组字段

常用命令

explain select... 分析select执行机制
1. 类型扫描type[ALL全表扫描 | ref使用非唯一或前缀索引 | range使用索引范围扫描]
2. 可能使用的索引possible_keys
3. 实际使用的索引key -NULL表示未使用索引
4. 返回行数rows -MySQL估计需要扫描的行数（预估值）
5. 命中率filtered -表示存储引擎返回的数据在服务层过滤后，剩余数据的百分比。值越小说明过滤效果越好
6. 额外信息extra -[Using使用覆盖索引 | Using where使用WHERE条件过滤 | Using temporary创建临时表 | Using filesort额外排序]
analyze table table_name. 更新表统计信息
数据库维护操作，用于收集和更新表的元数据（如行数、列值分布、索引基数等）。这些统计信息帮助查询优化器评估不同执行计划的成本，从而选择更高效的索引或连接方式。
```
ANALYZE TABLE table_name;
```
该命令会更新table_name的统计信息，存储于数据字典中。
1. 适用场景
  1. 表数据发生大量变更（如插入、删除或更新）后。
  2. 查询性能突然下降，怀疑优化器选择了错误的执行计划。
  3. 新创建的索引未被有效利用。
2. 性能影响
  1. 资源消耗：分析大型表可能占用CPU和I/O。
  2. 锁机制：某些实现需要表级锁（如MySQL的MyISAM引擎）。
  3. 频率平衡：过于频繁的分析浪费资源，过时统计则导致性能问题。
    
    建议在低峰期执行，并监控information_schema或系统视图查看统计时效性。