Mysql 优化笔记

最新推荐文章于 2024-06-01 17:29:00 发布

Ambition Jin

最新推荐文章于 2024-06-01 17:29:00 发布

阅读量281

点赞数

分类专栏： Mysql 文章标签： mysql 数据库 java 大数据

本文链接：https://blog.csdn.net/qq_33710903/article/details/109733991

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql 索引优化笔记

索引优化
SQL性能跟踪
分区，分表，分库
- 分区类型及操作

索引优化

索引优化原则

前导模糊查询不能命中索引；

EXPLAIN SELECT * FROM ddpt WHERE dname LIKE '%s%';

在这里插入图片描述
注意：前导等值和非前导模糊查询是可以命中索引的；

EXPLAIN SELECT * FROM ddpt WHERE dname ='%s%';

在这里插入图片描述

explain select * from dept where dname like 's%';

在这里插入图片描述

数据类型隐式转换

explain select * from dept where dname=1111111111;

在这里插入图片描述

explain select * from dept where dname=‘’1111111111;

在这里插入图片描述
3.最左匹配原则

# 表有5列，第2~4列构成组合索引

在这里插入图片描述

# 搜索条件均命中索引，需要回表查询
EXPLAIN SELECT * FROM dept WHERE deptno=111 AND dname='1111111111' AND loc='11111111';

在这里插入图片描述

 # 搜索条件未命中索引，需要回表查询
EXPLAIN SELECT * FROM dept WHERE dname='1111111111' AND loc='11111111';

在这里插入图片描述
注意：

# 虽然没有遵循最左匹配，但是覆盖索引起到了作用
EXPLAIN SELECT deptno FROM dept WHERE dname='1111111111' AND loc='11111111';

在这里插入图片描述
4. union、in、or都能够命中索引，建议使用in。
5. or条件，

# 如果or前的条件列有索引，而后的条件列没有索引，那么涉及到的索引都不会被用到。
# 因为or后的条件列没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。
EXPLAIN SELECT * FROM dept WHERE deptno='101' OR dname='1111111111';

在这里插入图片描述
6. 反向条件查询不能使用索引
!=, <>, not in, not exists, not like等
7. 范围条件查询可以命中索引
<, <=, >, >=, between等。范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
8. 数据库执行计算不会命中索引。
9. 利用覆盖索引进行查询，避免回表。
10.建立索引的列，不允许为null。

ORDER BY

order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序
MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；
ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用>where子句与ORDER BY子句条件列组合满足索引最左前列
尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序
双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序

优化策略
增大sort_buffer_size参数的设置
增大max_lencth_for_sort_data参数的设置

GROUP BY

group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了

SQL性能跟踪

Explain

执行explain

id：select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
a. id相同：执行顺序从上往下
b. id全不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
c. id部分相同：执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行
select_type：查询类型，用于区别普通查询、联合查询、子查询等复杂查询
a. SIMPLE(简单SELECT，不使用UNION或子查询等)
b. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
c. UNION(UNION中的第二个或后面的SELECT语句)
d. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
e. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
f. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
g. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
h. DERIVED(派生表的SELECT, FROM子句的子查询)
i. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table：显示这一行的数据是来自哪张表的。有时不是真实的表名字，看到的是derivedx（x是个数字，是第几步执行的结果）
partitions：匹配的分区。
type：对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
a. NULL: 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
b. SYSTEM：表只有一行记录（等于系统表），这是CONST类型的特例，平时不大会出现，可以忽略。
c. CONST：表示通过一次就找到了想要的结果。CONST用于比较primary key或uique索引，因为只匹配一行数据，所以很快。如主键置于WHERE列表中，就能将该查询转换为一个常量。犹如java代码中的常量一样，在内存中唯一。
d. EQ_REF：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于primary key或uique索引。与CONST的区别在于，需要根据多个索引建查找多条记录。
e. REF：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。然而，可能会找到多个符合条件的行，应该属于查找和扫描的混合体。
f. ref_or_null：类似ref，但是可以搜索值为NULL的行。
g. index_merge：表示使用了索引合并的优化方法。
h. range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在WHERE语句中出现between,<>,in等查询。
i. index：使用了二级索引。
j. ALL：Full Table Scan，将遍历全表以找到匹配行。
possible_keys：显示可能应用在这表中的索引，可以有多个查询涉及到的字段，但不一定被真正使用。
key：实际使用到的索引。如果查询中使用了覆盖索引，则使用的索引仅仅出现在key列表中。
key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度，在不损失精度的情况下，越短越好。key_len现实的是索引字段最大可能长度，并非实际使用长度。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，那些列或常量被用于查找索引列上的值。
rows：根据表统计信息和索引选用情况，大致估算出找到所需的记录所需要读取的行数。
filtered：返回数据的行数占rows的百分比。
Extra：包含不适合在其他列中显示但十分重要的额外信息。
a. Using Index: 表示应用的是覆盖索引
b. Using Where: 使用where来处理结果，并且查询的列，未加索引。
c. Using Index Condition: 查询的列不能完全被索引覆盖，where条件中一个前导列的范围
d. Using Temporary: mysql需要创建一张临时表来处理查询。出现这一情况一般是需要进行优化的，首先是想用索引来优化。简单来说就是需要第二次查询或扫描结果集，不能一次性从表中得到想要的结果。
额。
e. Using filesort: 把结果集再进行排序，不能一次性从表中得到结果。
f. Select tables optimized away: 使用某些聚合函数（max,min）来访问存在某个字段

Profiling

SHOW variables LIKE 'profiling'; --查看profiling是否开启

在这里插入图片描述

SET profiling=1; --开启profiling
SHOW profiling; --查看最近15条profiling

SHOW profiling

SHOW profile all for query 5; --查看query的id为5的SQL细节

在这里插入图片描述

慢查询

SHOW VARIABLES LIKE '%slow_query_log%'

在这里插入图片描述

SET GLOBAL slow_query_log='ON';
SET GLOBAL slow_query_log_file='/var/lib/mysql/hostname-slow.log';
SET GLOBAL long_query_time=2;

分区，分表，分库

逻辑数据分割
提高单一的写和读应用速度
提高分区范围读查询的速度
分割数据能够有多个不同的物理文件路径
高效的保存历史数据

分区类型及操作

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。好处在于说，扩容的时候很简单。
LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。