发布日期: 2023-04-29

文章字数: 1.9k

阅读时长: 7 分

阅读次数:

MySQL优化

Explain

模拟优化器执行SQL语句，分析。包括子查询

show warnings

explain select ...;
show warnings;
结果二可以得到优化后的查询语句

id列

select序号，id越大执行优先级越高，相同的按顺序执行，id为null最后执行

select_type列

查询类型

simple：简单查询
primary：复杂查询中最外层的查询
subquery：子查询（不在from子句）
derived：from子句中的查询，结果会存在临时表（派生表，衍生表）
union：union中第二个和随后的select

table列

select访问的表，derived是衍生表，后面跟衍生查询的id

type列

查询级别
system > const > eq_ref > ref > range > index > ALL
一般保证到range级别，最好达到ref

NULL：不用访问表就能拿到结果
```
explain select max(id) from user;
```
system：表里只有一条记录查询（是const的一个特例）
const：查询时优化成常量，唯一索引和常数比较，最多一个匹配行，读取1次（主键和唯一索引）
eq_ref：唯一索引的所有部分被连接使用，最多匹配一行（唯一索引join连接）
```
explain select * from user_role left join user on user.id = usre_role.id;
```
ref：不是唯一索引的查询，可能有多个值
range：使用索引范围扫描in,between,>,<等
index：全索引扫描（叶子开始扫描）拿到结果，一般扫描二级索引，二级索引比主键索引小，比ALL块一些
ALL：全表扫描扫描主键索引所有叶子节点

possible_keys列

查询可能使用哪些索引来查找
有可能possible_keys存在值，key为NULL，mysql认为走索引帮助不大，直接全盘扫描

key列

查询使用的索引

key_len列

索引里使用的字节数，通过key_len可以知道联合索引中使用到几个字段
char：n字节长度
varchar：(utf-8)3n + 2字节，2字节存字符串长度
如果字段允许为NULL，还需1字节存放是否为NULL

ref列

在key列记录的索引中，查找值所用到的列或常量，const(常量),字段名

rows列

估计读取并检测的行数，不是结果集的行数

Extra列

额外信息，很多情况
UsingIndex覆盖索引
Using temporary使用临时表，可以优化，建立覆盖索引
Using filesort使用文件排序，可以优化，考虑使用索引
Select tables optimized away使用聚合函数来访问存在索引的字段

原则

全值匹配：聚合索引用到字段越多越好
最左前缀
不在索引列上做任何操作（计算，函数，类型转换）

联合索引范围条件右边的列不能走索引

select * from user where name = '1' and age = 1 and gold = 11; // 全值
select * from user where name = '1' and age > 1 and gold = 11; // 只走前两个

尽量覆盖索引
不等于!= <>可能不走索引，具体分析
is null, is not null 可能不走索引
%like不走索引，可以利用覆盖索引优化
or,in可能不走索引（in数据量大一般走索引）
force index(index_name)强制使用索引

索引下推：索引遍历过程中可以对索引包含字段先判断，过滤不符合条件的数据再回表

name like zhangsan% and age = 1 and gold = 1; #使用三个索引
name > zhangsan and age = 1 and gold = 1; #不使用索引
范围查找过滤的结果集过大，不会过滤，like相比较小

Mysql如何选择的索引

mysql内部会cost成本计算
使用trace工具分析（开启会影响性能）

set session optimizer_trace="enabled=on",end_markers_in_json=on; // 开启
select * from user ...
select * from information_schema.OPTIMIZER_TRACE; // 执行步骤分析
# 结果二是分析结果

trace:

join_preparation 第一阶段：SQL准备阶段，格式化SQL
join_optimization 第二阶段：SQL优化

rows_estimation 预估表访问成本

table_scan: {    // 全表扫描情况
	rows: 11111,  // 扫描行数
	cost: 2322   // 查询成本
}
potential_range_indexes: [  // 查询可能使用的索引
	{
		index: primary
		usable: false,
		cause: not_applicable
	},
	{
		index: other_index
		usable: true,
		key_parts: [
			name,
			age,
			gold,
			id
		]
	}
analyzing_range_alternatives: {   // 分析各个索引使用成本
	"range_scan_alternatives": [
		{
			index: other_index
			ranges: [
				a < name
			],
			...
			rowid_ordered: false,  // 使用该索引获取的记录是否按照主键索引排序
			index_only: false,     // 是否使用覆盖索引
			rows: 4444,			   // 扫描行数
			cost: 5555,            // 使用成本
			chosen: false          // 是否使用该索引
		}
	]
}
considered_execution_plans: [
	best_ access_path: {             // 最优访问路径
		considered_access_paths: [   // 最终选择访问路径
			{
				access_type: scan    // 访问类型全盘扫面
				chosen: true         // 确认
			}
		]
	}
]

常见优化

order by group by

order by group by也使用索引最左前列
尽量在索引上排序，遵循最左前列
尽量覆盖索引
group by是先排序后分组，不需要排序可以order by null禁止排序，where高于having,最好用where
文件排序filesort:

单路排序：把所有字段从磁盘拿到内存排序，然后直接得到结果集，消耗内存较大
双路排序：把需要排序的字段和主键拿到内存排序，然后用ID回表，最后得到结果集，内存消耗小，需要回表

索引设计

代码逻辑写好后，再设计索引
基数小的字段不建索引比如sex
联合索引尽量覆盖条件
长字符串太长会占用磁盘空间，可以建前缀索引，一般前20 KEY index(name(20),age,gold)
where和orderby冲突一般先优化where
联合索引范围的字段一般建最后
尽量1-2个联合索引处理主要业务，1-2个辅助索引处理其它业务

limit

select * from user order by name limit 99999,9;
# 子查询先查出ID，避免大量数据回表
select * from user u inner join (select id from usre order by name limit 99999,9) us on u.id = us.id

join关联查询

嵌套循环连接NLJ算法
循环从驱动表（遍历）读取行，根据关联字段在被驱动表找出满足条件的行

select * from t1 inner join t2 on t1.id = t2.id;
# left join左表是驱动表（遍历）right join相反 inner join数据少的是驱动表

如果join关联字段没有索引，会使用基于块的嵌套循环连接BNL算法
把驱动表的所有数据读到join_buffer中，然后与被驱动表比对（没索引会全盘扫描比对）

尽量关联字段加索引
尽量小表驱动大表（小表指参与关联的记录，不是整张表记录）

in exsits

# 从t2拿出数据和t1比（t2是驱动表，选择小表合适）
select * from t1 where id in (t2)
# 从t1拿出数据和t2比（t1是驱动表，选择小表合适）
select * from t1 where exists (t2)

count()效率

效率都几乎一样
count(*) = count(1) > count(id) > count(字段)

count(*)进行了优化，不取字段，按行累加
count(1)不取字段，取常量1，累加
count(id)取id放入内存，累加（其中走二级索引会较快，因为小一些，mysql一般会优化走二级索引，如果有的话）
count(字段)如果字段没索引效率最低，不过也差不远，有索引走二级索引(和count(id)差不多)

数据类型

char(4)会自动补空格，确定长度用char，其它varchar
尽量少用blob text，可以考虑单独存一张表，id关联
int(11)这种数字数据类型占用空间大小和11没啥关系，11指显示宽度，只在设置了填充0作用

MySQL

Spring底层

2023-05-19 Spring

Spring

JVM调优

2023-04-24 Java

Java JVM