欢迎访问shiker.tech

请允许在我们的网站上展示广告

您似乎使用了广告拦截器,请关闭广告拦截器。我们的网站依靠广告获取资金。

SQL语句如何优化
(last modified Jun 23, 2023, 8:56 PM )
by
侧边栏壁纸
  • 累计撰写 176 篇文章
  • 累计创建 61 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

SQL语句如何优化

橙序员
2023-06-23 / 0 评论 / 0 点赞 / 290 阅读 / 1,685 字 / 正在检测百度是否收录... 正在检测必应是否收录...
文章摘要(AI生成)

优化思路:首先需要使用【慢查询日志】功能,去获取所有查询时间比较长的SQL语句查看执行计划,查看有问题的SQL的执行计划针对查询慢的SQL语句进行优化使用show profile[s] 查看有问题的SQL的性能使用情况慢查询日志慢查询开启通过在配置文件中添加:slow_query_log=ONlon

优化思路:

  1. 首先需要使用【慢查询日志】功能,去获取所有查询时间比较长的SQL语句
  2. 查看执行计划,查看有问题的SQL的执行计划
  3. 针对查询慢的SQL语句进行优化
  4. 使用show profile[s] 查看有问题的SQL的性能使用情况

慢查询日志

慢查询开启通过在配置文件中添加:

slow_query_log=ON
long_query_time=1

开启后在对应的mysql数据存储路径中可以找到慢查询日志:

image-20230623193353649

每条sql语句,都记录了对应的执行时的系统时间,用户和连接信息,以及对应的查询耗时(Query_time),等待表锁耗时(Lock_time),查询返回行数(Rows_sent),查询扫描行数(Rows_examined)。

使用mysqldumpslow工具可以分析慢查询日志,例如按查询时间,筛选出最慢的10条查询语句:

/www/server/mysql/bin/mysqldumpslow -s t -t 10 -g "SELECT" /www/server/data/mysql-slow.log

得到结果如下:

image-20230623195203870

其它可用参数如下:

-v 版本信息
-d debug
-s 排序条件,默认使用at. al:平均锁定时间;ar:平均返回记录时间;at:平均查询时间;c:数量;l:锁定时间;r:返回记录时间;t:查询时间
-r 逆序排序(只对结果排序)
-t 展示前n条记录
-a 查询所有具体sql,即不抽象sql中的条件值
-n 名称中至少包含 n 位数字的抽象数字
-g 正则表达式匹配搜索
-i 服务器实例的名称(如果使用 mysql.server 启动脚本)
-l 不要从总时间中减去锁定时间

执行计划

通过explain查看sql执行计划,来对sql进行优化:

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型. 比如普通查询、联合查询(union、union all)、子查询(subquery)等复杂查询。
  • table: 查询的是哪个表
  • partitions: 匹配的分区
  • type: join 类型。从好到差依次为system、const、eq_ref、fulltext、ref_or_null、unique_subquery、index_subquery、range、index_merge、index、ALL
    • system:表中只有一行数据或者为空表
    • const:使用唯一索引或主键。返回记录一定是1行记录的等值where条件时,通常type是const。
    • eq_ref:唯一性索引扫描。对于每个索引键,表中只有一条记录与之匹配
    • ref:非唯一性索引扫描,返回多个匹配条件的行。
    • range:索引范围扫描
    • index:结果列中使用到了索引
    • all:全表扫描数据文件
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • key_len: 索引长度,长度越小,效率越高
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • filtered: 表示此查询条件所过滤的数据的百分比
  • extra: 额外的信息
    • using index:查询时不需要回表查询,直接通过索引就可以获取查询的结果数据。
    • using where:Mysql将对storage engine提取的结果进行过滤,过滤条件字段无索引。

sql语句优化

索引优化

  • 为搜索字段(where中的条件)、排序字段、select查询列,创建合适的索引,不过要考虑数据的业务场景:查询多还是增删多?
  • 尽量建立组合索引并注意组合索引的创建顺序,按照顺序组织查询条件、尽量将筛选粒度大的查询 条件放到最左边。
  • 尽量使用覆盖索引,SELECT语句中尽量不要使用。
  • order by、group by语句要尽量使用到索引
  • 索引长度尽量短,短索引可以节省索引空间,使查找的速度得到提升,同时内存中也可以装载更多 的索引键值。太长的列,可以选择建立前缀索引
  • 索引更新不能频繁,更新非常频繁的数据不适宜建索引,因为维护索引的成本。
  • order by的索引生效,order by排序应该遵循最佳左前缀查询,如果是使用多个索引字段进行排序,那么排序的规则必须相同(同是升序或者降序),否则索引同样会失效。

limit优化

  • 如果预计SELECT语句的查询结果是一条,最好使用 LIMIT 1,可以停止全表扫描。
  • 处理分页会使用到LIMIT ,当翻页到非常靠后的页面的时候,偏移量会非常大,这时LIMIT的效率会非常差。 LIMIT的优化问题,其实是OFFSET的问题,它会导致MySql扫描大量不需要的行然后再抛弃掉。 解决方案:单表分页时,使用自增主键排序之后,先使用where条件 id > offset值,limit后面只写rows

其他优化

  • 小表驱动大表,建议使用left join时,以小表关联大表,因为使用join的话,第一张表是必须全扫描的,以少关联多就可以减少这个扫描次数。
  • 避免全表扫描,mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时候,如果对索引使用不等于的操作将会导致索引失效,进行全表扫描
  • 避免mysql放弃索引查询,如果mysql估计使用全表扫描要比使用索引快,则不使用索引。(最典型的场景就是数据量少的时候) 尽量不使用count( * )、尽量使用count(主键)
  • JOIN两张表的关联字段最好都建立索引,而且最好字段类型是一样的。
  • WHERE条件中尽量不要使用not in语句(建议使用not exists)
  • 合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

profile分析sql

开启profile:

image-20230623204305078

查看sql分析列表,分析sql执行情况:

image-20230623204431742

0

评论区