【mysql - sql优化】

最新推荐文章于 2024-09-07 08:07:39 发布

木可南_杨

最新推荐文章于 2024-09-07 08:07:39 发布

阅读量611

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/mukenan_yang/article/details/124081412

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL优化的准备工作，包括慢查询日志的设置和使用，以及EXPLAIN命令的解析。强调了创建索引的原则，如遵循最左匹配原则，并给出了索引失效的常见场景。此外，还分享了JOIN优化、分页查询和COUNT、ORDER BY的优化策略，旨在提升SQL查询效率。

摘要由CSDN通过智能技术生成

文章目录

mysql优化的准备工作

使用mysql官方测试数据库
https://github.com/datacharmer/test_db

慢查询日志

记录执行超过指定时间的sql语句

mysql查询的相关参数

参数	作用	默认值
set global log_output	表示日志输出的文件可以设置为多种格式 FILE,TABLE	FILE
set global long_query_time	执行sql超过的时间记录到慢sql日志中单位秒	10
set global slow_query_log	是否开启慢sql查询	OFF
set global log_queries_not_using_indexes	记录没有使用索引的查询	OFF

使用方式

方式一：修改my.cnf , 需要重启mysql

slow_query_log = 'ON';

log_output = 'FILE,TABLE';

long_query_time = 10;

log_queries_not_using_indexes = 'ON';

方式二：全局上设置

set global slow_query_log = 'ON';

set global log_output = 'FILE,TABLE';

set global long_query_time = 10;

set global log_queries_not_using_indexes = 'ON';

查询慢sql的日志

select * from `mysql`.slow_log; #基于TABLE

主要看sql_text字段的数据，记录了是那条sql语句

show variables like '%slow_query_log_file%' #基于FILE

通过命令行去服务器上看日志

EXPLAIN详解

EXPLAIN 使用在sql语句前面加上这个关键字

explain select * from employees;

重要的字段解析

id：

id越大越在前面执行，id一样顺序执行

type：

system：只有一行数据被扫描。

const：表最多有一个匹配行，针对主键或唯一索引。

eq_ref：就是两张表通过其中一张表的主键或者唯一非空索引进行关联时，查询结果是一对一的关系。当使用了索引的全部组成部分，索引为 UNIQUE等才能使用该类型。

ref：当满足索引的最左匹配原则，或者索引不是主键或者唯一索引才能触发，

fulltext：使用了全文索引。

range：使用索引去检索范围内的行，输出行中的键列指示使用哪个索引。基本上是用到了以下操作符： !=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()。

index：全索引扫描。

all：全表扫描，性能最差。

rows：

扫描的行数（优化的目的减少扫描的行数，sql才能提升性能）。

Extra：

using filesort：包含order by 操作，排序字段没有用到索引。

using temporary：MySQL需要创建一个临时表来保存结果。如果查询包含GROUP BY和ORDER BY，通常会发生这种情况。

创建索引的原则

建议创建索引

1、频繁作为where条件的字段。（单独的索引、组合索引）

注意组合索引需要遵从最左匹配原则
`最左匹配原则：指的是索引按照最左优先的方式匹配如给A,B,C三个字段创建索引

where A=1；
WHERE A=1 AND B=2 ；
WHERE A=1 AND B=2 AND C=3； 可以使用
where B=2；
WHERE A=1 AND C=3； 不可以使用

2、频繁作为分组、排序的字段。

3、distinct的字段。

4、字段的只有唯一性约束。

5、多表关联查询的字段需要创建索引注意数据类型必须保持一致否则会有隐式转换问题，导致索引失效。

不建议创建索引

where条件种用不到的字段。
表数据少。
有大量重复的数据比如枚举，年龄，性别等。
频繁需要修改更新的数据，如果创建索引要考虑到索引的开销。

索引失效的场景

索引列不独立。字段进行了表达式计算，字段是函数的参数

where emp_no + 1 = 10003;
解决方案：事先计算好表达式的值，再传过来，避免在SQLwhere条件 = 的左侧做计算
where SUBSTRING(first_name, 1, 3) = 'Geo';
解决方案：预先计算好结果，再传过来，在where条件的左侧，不要使用函数；或者使用等价的SQL去实现

使用了左模糊。

where first_name like '%A%';
解决方案：尽量避免使用左模糊，如果避免不了，可以考虑使用搜索引擎去解决

使用OR查询的部分字段没有索引。

where A = 'A'
  or B = 'B';
解决方案：分别为A以及B字段创建索引

字符串条件未使用’'引起来。

where dept_no = 3;
解决方案：规范地编写SQL

不符合最左前缀原则的查询。
索引字段建议添加NOT NULL约束。
MySQL官方建议尽量把字段定义为NOT NULL：https://dev.mysql.com/doc/refman/8.0/en/data-size.html
隐式转换导致索引失效。

索引调优的技巧索引调优的技巧

覆盖索引
select的字段只需要从索引就能获取，不需要到表数据里面获取，这个的索引就叫覆盖索引
select 后面更想的字段，减少网络的开销，还可能使用到覆盖索引
冗余、重复索引的优化

重复索引

在相同的列上按照相同的顺序创建的相同类型的索引。
应该避免创建这样的重复索引，发现之后也应该立即移除。

冗余索引

如果已经存在索引index（A,B），有创建了index（A），那么index（A）就是index（A,B）的冗余索引

JOIN优化

join的种类

图片来自于：http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

cross join：笛卡尔连接会把A表里面的数据和B表里面的数据任意的连接
如果cross join带有on子句，就相当于inner join

join算法

Table   Join Type
t1      range
t2      ref
t3      ALL

Nested-Loop Join（NLJ-嵌套循环join）

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

Block Nested-Lopp Join（BNLJ块嵌套循环join）

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}
if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

扫描次数计算公式：（S*C）/join_buffer_size+1

驱动表和被驱动表

外层循环的表是驱动表，内层循环的表是被驱动表

JOIN调优的原则

1、用小表驱动大表

优化器回自动选择最优来执行
当优化器没有选择的时候可以用STRSIGHT_JOIN

2、where条件使用索引，减少外层循环的数据量

3、join on 的字段类型保持一致，否则会有隐式转换索引失效

4、join的表不要太多

阿里规范建议不要稍多三张

分页查询优化

方案一：覆盖索引

方案二：覆盖索引+join

方案三：覆盖索引+子查询

方案四：禁止传入过大的页码（参考百度）

COUNT优化

当没有非主键索引时，会使用主键索引
如果存在非主键索引的话，会使用非主键索引
如果存在多个非主键索引，会使用一个最小的非主键索引

count(1) 和 count(*)

count(*)和count(1)没有区别

官方文档：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

对于MyISAM表，COUNT(*)如果SELECT从一个表中检索，没有检索到其他列，并且没有 WHERE子句，则优化为非常快速地返回

方案一：创建一个更小的非主键索引

方案二：把数据库引擎换成MyISAM （建议不药使用）

方案三：information_schema.tables

select TABLE_ROWS from `information_schema`.TABLES where TABLE_SCHEMA ='employees' and TABLE_NAME = 'employees';
--好处：employees，不管employees有多少数据，都可以迅速地返回结果
-- 缺点：估算值，并不是准确值

方案四：使用explain 有优缺点同上

方案五：

show table status where Name = 'salaries';
优缺点同上

ORDER BY优化

我们在使用order by 可以使用索引，因为它是天然有序性的链表

无法利用索引避免排序，升降序不一致一个字段使用desc，一个使用asc

mysql官方文档sql优化 https://dev.mysql.com/doc/refman/5.7/en/optimization.html

仅供自己学习使用，如有侵权，请及时联系我删除

木可南_杨

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
【mysql - sql优化】

文章目录mysql优化的准备工作慢查询日志mysql查询的相关参数使用方式EXPLAIN详解重要的字段解析创建索引的原则建议创建索引不建议创建索引索引失效的场景索引调优的技巧索引调优的技巧JOIN优化join的种类join算法驱动表和被驱动表JOIN调优的原则分页查询优化COUNT优化ORDER BY优化mysql优化的准备工作使用mysql官方测试数据库https://github.com/datacharmer/test_db慢查询日志记录执行超过指定时间的sql语句mysql查询的相关参数
复制链接

扫一扫