• linkedu视频
  • 平面设计
  • 电脑入门
  • 操作系统
  • 办公应用
  • 电脑硬件
  • 动画设计
  • 3D设计
  • 网页设计
  • CAD设计
  • 影音处理
  • 数据库
  • 程序设计
  • 认证考试
  • 信息管理
  • 信息安全
菜单
linkedu.com
  • 网页制作
  • 数据库
  • 程序设计
  • 操作系统
  • CMS教程
  • 游戏攻略
  • 脚本语言
  • 平面设计
  • 软件教程
  • 网络安全
  • 电脑知识
  • 服务器
  • 视频教程
  • MsSql
  • Mysql
  • oracle
  • MariaDB
  • DB2
  • SQLite
  • PostgreSQL
  • MongoDB
  • Redis
  • Access
  • 数据库其它
  • sybase
  • HBase
您的位置:首页 > 数据库 >Mysql > 有关sql语句优化的教程

有关sql语句优化的教程

作者:匿名 字体:[增加 减小] 来源:互联网 时间:2018-12-05

匿名通过本文主要向大家介绍了教程,优化,语句等相关知识,希望本文的分享对您有所帮助
网上关于SQL优化的教程很多,但是比较杂乱,近日有空整理了一下,写出来跟大家分享,下面这篇文章主要给大家分享介绍了关于sql语句优化的一般步骤,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧。

前言

本文主要给大家分享了关于sql语句优化的一般步骤,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。

一、通过 show status 命令了解各种 sql 的执行频率

mysql 客户端连接成功后,通过 show [session|global] status 命令可以提供服务器状态信息,也可以在操作系统上使用 mysqladmin extend-status 命令获取这些消息。

show status 命令中间可以加入选项 session(默认) 或 global:

  • session (当前连接)

  • global (自数据上次启动至今)


# Com_xxx 表示每个 xxx 语句执行的次数。
mysql> show status like 'Com_%';

我们通常比较关心的是以下几个统计参数:

  • Com_select : 执行 select 操作的次数,一次查询只累加 1。

  • Com_insert : 执行 insert 操作的次数,对于批量插入的 insert 操作,只累加一次。

  • Com_update : 执行 update 操作的次数。

  • Com_delete : 执行 delete 操作的次数。

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对 innodb 的,累加的算法也略有不同:

  • Innodb_rows_read : select 查询返回的行数。

  • Innodb_rows_inserted : 执行 insert 操作插入的行数。

  • Innodb_rows_updated : 执行 update 操作更新的行数。

  • Innodb_rows_deleted : 执行 delete 操作删除的行数。

通过以上几个参数,可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种类型的 sql 大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。

对于事务型的应用,通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题。

此外,以下几个参数便于用户了解数据库的基本情况:

  • Connections : 试图连接 mysql 服务器的次数。

  • Uptime : 服务器工作时间。

  • Slow_queries : 慢查询次数。

二、定义执行效率较低的 sql 语句

1. 通过慢查询日志定位那些执行效率较低的 sql 语句,用 --log-slow-queries[=file_name] 选项启动时,mysqld 写一个包含所有执行时间超过 long_query_time 秒的 sql 语句的日志文件。

2. 慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前 mysql 在进行的线程,包括线程的状态、是否锁表等,可以实时的查看 sql 的执行情况,同时对一些锁表操作进行优化。

三、通过 explain 分析低效 sql 的执行计划

测试数据库地址:https://downloads.mysql.com/docs/sakila-db.zip(本地下载)

统计某个 email 为租赁电影拷贝所支付的总金额,需要关联客户表 customer 和 付款表 payment , 并且对付款金额 amount 字段做求和(sum) 操作,相应的执行计划如下:


mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)
  • select_type: 表示 select 类型,常见的取值有:
    simple:简单表,及不使用表连接或者子查询
    primary:主查询,即外层的查询
    union:union 中的第二个或后面的查询语句
    subquery: 子查询中的第一个 select

  • table : 输出结果集的表

  • type : 表示 mysql 在表中找到所需行的方式,或者叫访问类型,常见类型性能由差到最好依次是:all、index、range、ref、eq_ref、const,system、null:

1.type=ALL,全表扫描,mysql 遍历全表来找到匹配的行:


mysql> explain select * from film where rating > 9 \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 33.33
 Extra: Using where
1 row in set, 1 warning (0.01 sec)

2.type=index, 索引全扫描,mysql 遍历整个索引来查询匹配的行


mysql> explain select title form film\G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: index
possible_keys: NULL
  key: idx_title
 key_len: 767
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)

3.type=range,索引范围扫描,常见于<、<=、>、>=、between等操作:


mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: range
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: NULL
 rows: 1350
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.07 sec)

4.type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行,例如:


mysql> explain select * from payment where customer_id = 350 \G 
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: const
 rows: 23
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.01 sec)

索引 idx_fk_customer_id 是非唯一索引,查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中:


mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 599
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.b.customer_id
 rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

5.type=eq_ref,类似 ref,区别就在使用的索引时唯一索引,对于每个索引的键值,表中只要一条记录匹配;简单的说,就是多表连接中使用 primary key 或者 unique index 作为关联条件。


mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G

*************************** 1. row ********
  


 
分享到:QQ空间新浪微博腾讯微博微信百度贴吧QQ好友复制网址打印

您可能想查找下面的文章:

  • mysql 精简过程(删除一些文件)
  • 基于mysq字段选择的详解
  • mysql存储过程 在动态SQL内获取返回值的方法详解
  • 基于mysql多实例安装的深入解析
  • 基于Php mysql存储过程的详解
  • 关于mysql基础知识的介绍
  • 深入Mysql,SqlServer,Oracle主键自动增长的设置详解
  • Windows XP系统安装MySQL5.5.28图解教程
  • 解析SQLite中的常见问题与总结详解
  • linux下mysql提示"mysql deamon failed to start"错误的解决方法

相关文章

  • 2017-05-11mysql中order by与group by的区别
  • 2018-12-05使用mysql_fetch_row()函数逐行获取结果集中的每条记录(PHP操作MySQL数据库的方法六)
  • 2018-12-05MySQL 查询时强制区分大小写
  • 2017-05-11master and slave have equal MySQL server UUIDs 解决方法
  • 2017-05-11基于mysql全文索引的深入理解
  • 2017-05-11MYSQL explain 执行计划
  • 2017-05-11MySQL 性能优化的最佳20多条经验分享
  • 2018-12-05SQL2005 自动备份的脚本
  • 2018-12-05MySQL高级十——事务的应用
  • 2017-05-11MySql 备忘录

文章分类

  • MsSql
  • Mysql
  • oracle
  • MariaDB
  • DB2
  • SQLite
  • PostgreSQL
  • MongoDB
  • Redis
  • Access
  • 数据库其它
  • sybase
  • HBase

最近更新的内容

    • 关于游标数据的详细介绍
    • Oracle7.X 回滚表空间数据文件误删除处理方法
    • Oracle 数据库自动存储管理-安装配置
    • MySQL 数据类型 大全
    • 十个 MongoDB 使用要点
    • Mysql服务器的启动与停止(二)
    • 通过Mybatis调用MySQL存储过程的实例详解
    • mysql安装图解 mysql图文安装教程(详细说明)
    • 关于mysql 优化 insert 性能 的相关介绍
    • MYSQL之插入极限分析

关于我们 - 联系我们 - 免责声明 - 网站地图

©2020-2025 All Rights Reserved. linkedu.com 版权所有