图片 2

如何查找MySQL中查询慢的SQL语句

  通过上面的慢日志定位和processlist
找出效率低的sql语句后,可以通过explain或者desc命令获取mysql
如何执行查询语句的信息。

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
的执行计划

测试数据库地址:

统计某个 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='[email protected]'\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 ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: sakila.b.film_id
 rows: 1
 filtered: 100.00
 Extra: Using where
2 rows in set, 1 warning (0.03 sec)

6.type=const/system,单表中最多有一个匹配行,查起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理,例如,根据主键
primary key 或者唯一索引 unique index 进行查询。

mysql> create table test_const (
 ->  test_id int,
 ->  test_context varchar(10),
 ->  primary key (`test_id`),
 -> );

insert into test_const values(1,'hello');

explain select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: test_const
 partitions: NULL
 type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 4
  ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
 1 row in set, 1 warning (0.00 sec)

7.type=null, mysql 不用访问表或者索引,直接就能够得到结果:

mysql> explain select 1 from dual where 1 \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: NULL
 partitions: NULL
 type: NULL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: NULL
 filtered: NULL
 Extra: No tables used
1 row in set, 1 warning (0.00 sec)

  类型 type 还有其他值,如 ref_or_null(与 ref
类似,区别在于条件中包含对 null
的查询)、index_merge(索引合并优化)、unique_subquery (in
的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery
类似,区别在于 in 的后面是查询非唯一索引字段的子查询)等。

  • possible_keys : 表示查询时可能使用的索引。
  • key :表示实际使用索引
  • key-len : 使用到索引字段的长度。
  • rows : 扫描行的数量
  • extra:执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

show warnings 命令

执行explain 后再执行 show warnings,可以看到sql
真正被执行之前优化器做了哪些 sql 改写:

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = '[email protected]'\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)

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = '[email protected]')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从 warning 的 message 字段中能够看到优化器自动去除了 1=1
恒成立的条件,也就是说优化器在改写 sql 时会自动去掉恒成立的条件。

explain 命令也有对分区的支持.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NOT NULL,
 -> `active` tinyint(1) NOT NULL DEFAULT '1',
 -> `create_date` datetime NOT NULL,
 -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`customer_id`)
 -> 
 -> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)

MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0

MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: customer_part
 partitions: p2
  type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: const
  rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warnings (0.00 sec)

可以看到 sql 访问的分区是 p2。

四、通过 performance_schema 分析 sql
性能

旧版本的 mysql 可以使用 profiles 分析 sql
性能,我用的是5.7.18的版本,已经不允许使用 profiles 了,推荐用
performance_schema 分析sql。

五、通过 trace
分析优化器如何选择执行计划。

mysql5.6 提供了对 sql 的跟踪 trace,可以进一步了解为什么优化器选择 A
执行计划而不是 B 执行计划,帮助我们更好的理解优化器的行为。

使用方式:首先打开 trace ,设置格式为 json,设置 trace
最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整显示。

MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

接下来执行想做 trace 的 sql 语句,例如像了解租赁表 rental 中库存编号
inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25
4:00:00 ~ 5:00:00 之间出租的记录:

mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)

MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
    QUERY: select * from infomation_schema.optimizer_trace
    TRACE: {
 "steps": [
 ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

六、
确定问题并采取相应的优化措施

经过以上步骤,基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施,进行优化以提高执行的效率。

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对帮客之家的支持。

前言
本文主要给大家分享了关于sql语句优化的一般步骤,分享出来供大家参考学习,下面话不多…

如何查找MySQL中查询慢的SQL语句

更多

如何在mysql查找效率慢的SQL语句呢?这可能是困然很多人的一个问题,MySQL通过慢查询日志定位那些执行效率较低的SQL
语句,用–log-slow-queries[=file_name]选项启动时,mysqld
会写一个包含所有执行时间超过long_query_time
秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL
。下面介绍MySQL中如何查询慢的SQL语句

一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句

 

1,slow_query_log

这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。

 

2,long_query_time

当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短。

 

3,slow_query_log_file

记录日志的文件名。

 

4,log_queries_not_using_indexes

这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。

 

二、检测mysql中sql语句的效率的方法

 

1、通过查询日志

(1)、Windows下开启MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.ini找到[mysqld]下面加上

代码如下

log-slow-queries = F:/MySQL/log/mysqlslowquery.log
long_query_time = 2

(2)、Linux下启用MySQL慢查询

MySQL在Windows系统中的配置文件一般是是my.cnf找到[mysqld]下面加上

代码如下

log-slow-queries=/data/mysqldata/slowquery。log
long_query_time=2

说明

log-slow-queries = F:/MySQL/log/mysqlslowquery。

为慢查询日志存放的位置,一般这个目录要有MySQL的运行帐号的可写权限,一般都将这个目录设置为MySQL的数据存放目录;
long_query_time=2中的2表示查询超过两秒才记录;

 

2.show processlist 命令

 

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin
processlist
语句得到此信息。

各列的含义和用途:

ID列

一个标识,你要kill一个语句的时候很有用,用命令杀掉此查询 /*/mysqladmin
kill 进程号。

user列

显示单前用户,如果不是root,这个命令就只显示你权限范围内的sql语句。

host列

显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。

db列

显示这个进程目前连接的是哪个数据库。

command列

显示当前连接的执行的命令,一般就是休眠(sleep),查询(query),连接(connect)。

time列

此这个状态持续的时间,单位是秒。

state列

显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个
sql语句,以查询为例,可能需要经过copying to tmp table,Sorting
result,Sending data等状态才可以完成

info列

显示这个sql语句,因为长度有限,所以长的sql语句就显示不全,但是一个判断问题语句的重要依据。

 

这个命令中最关键的就是state列,mysql列出的状态主要有以下几种:

Checking table
 正在检查数据表(这是自动的)。
Closing tables
 正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
 复制从服务器正在连接主服务器。

Copying to tmp table on disk
 由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
 正在创建临时表以存放部分查询结果。
deleting from main table
 服务器正在执行多表删除中的第一部分,刚删除第一个表。
deleting from reference tables
 服务器正在执行多表删除中的第二部分,正在删除其他表的记录。

Flushing tables
 正在执行FLUSH TABLES,等待其他线程关闭数据表。
Killed
 发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。
Locked
 被其他查询锁住了。
Sending data
 正在处理SELECT查询的记录,同时正在把结果发送给客户端。

Sorting for group
 正在为GROUP BY做排序。
 Sorting for order
 正在为ORDER BY做排序。
Opening tables
 这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER
TABLE或LOCK
TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
 正在执行一个SELECT
DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。

Reopen table
 获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。
Repair by sorting
 修复指令正在排序以创建索引。
Repair with keycache
 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by
sorting慢些。
Searching rows for update
 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
 正在等待客户端发送新请求.

System lock
 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加–skip-external-locking参数来禁止外部系统锁。
Upgrading lock
 INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
 正在搜索匹配的记录,并且修改它们。

User Lock
 正在等待GET_LOCK()。
Waiting for tables
 该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH
TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE
TABLE,或OPTIMIZE TABLE。
waiting for handler insert
 INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。
 大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。
 还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。

 

例如如图:

 

 

3、explain来了解SQL执行的状态

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法,在select语句前加上explain就可以了

例如:

explain select surname,first_name form a,b where a.id=b.id

结果如图

 

EXPLAIN列的解释

table

显示这一行的数据是关于哪张表的

type

这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys

显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key

实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句
中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE
INDEX(indexname)来强制MYSQL忽略索引

key_len

使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref

显示索引的哪一列被使用了,如果可能的话,是一个常数

rows

MYSQL认为必须检查的用来返回请求数据的行数

Extra

关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using
temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢

 

extra列返回的描述的意义

Distinct

一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

Not exists

MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了

Range checked for each Record(index map:#)

没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort

看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary

看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER
BY上,而不是GROUP BY上

Where used

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)

const

表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待

eq_ref

在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用

ref

这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range

这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况

index

这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)

ALL

这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

 

 

 

图片 1  

MySQL – 查看慢SQL

查看MySQL是否启用了查看慢SQL的日志文件

(1) 查看慢SQL日志是否启用

mysql> show variables like ‘log_slow_queries’; 
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| log_slow_queries | ON    |
+——————+——-+
1 row in set (0.00 sec)

(2) 查看执行慢于多少秒的SQL会记录到日志文件中
mysql> show variables like ‘long_query_time’;
+—————–+——-+
| Variable_name   | Value |
+—————–+——-+
| long_query_time | 1     |   
+—————–+——-+
1 row in set (0.00 sec)

这里value=1, 表示1秒

 

  1. 配置my.ini文件(inux下文件名为my.cnf),
    查找到[mysqld]区段,增加日志的配置,如下示例:
    [mysqld]
    log=”C:/temp/mysql.log”
    log_slow_queries=”C:/temp/mysql_slow.log”
    long_query_time=1

log指示日志文件存放目录;
log_slow_queries指示记录执行时间长的sql日志目录;
long_query_time指示多长时间算是执行时间长,单位s。

Linux下这些配置项应该已经存在,只是被注释掉了,可以去掉注释。但直接添加配置项也OK啦。

 

 

查询到效率低的 SQL 语句 后,可以通过 EXPLAIN 或者 DESC 命令获取 MySQL
如何执行 SELECT 语句的信息,包括在 SELECT
语句执行过程中表如何连接和连接的顺序,比如我们想计算 2006
年所有公司的销售额,需要关联 sales 表和 company 表,并且对 profit
字段做求和( sum )操作,相应 SQL 的执行计划如下: 
mysql> explain select sum(profit) from sales a,company b where
a.company_id = b.id and a.year = 2006\G; 
*************************** 1. row
*************************** 
id: 1 
select_type: SIMPLE 
table: a 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
*************************** 2. row
*************************** 
id: 1 
select_type: SIMPLE 
table: b 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
2 rows in set (0.00 sec)

每个列的解释如下:

•select_type :表示 SELECT 的 类型,常见的取值有 SIMPLE
(简单表,即不使用表连接或者子查询)、 PRIMARY
(主查询,即外层的查询)、 UNION ( UNION
中的第二个或者后面的查询语句)、 SUBQUERY (子查询中的第一个 SELECT
)等。

•table :输出结果集的表。

•type :表示表的连接类型,性能由好到差的连接类型为 system
(表中仅有一行,即常量表)、 const (单表中最多有一个匹配行,例如
primary key 或者 unique index )、 eq_ref
(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用
primary key 或者 unique index )、 ref (与 eq_ref
类似,区别在于不是使用 primary key 或者 unique index
,而是使用普通的索引)、 ref_or_null ( 与 ref
类似,区别在于条件中包含对 NULL 的查询 ) 、 index_merge ( 索引合并优化
) 、 unique_subquery ( in 的后面是一个查询主键字段的子查询)、
index_subquery ( 与 unique_subquery 类似,区别在于 in
的后面是查询非唯一索引字段的子查询)、 range (单表中的范围查询)、
index (对于前面的每一行,都通过查询索引来得到数据)、 all
(对于前面的每一行,都通过全表扫描来得到数据)。

•possible_keys :表示查询时,可能使用的索引。 
•key :表示实际使用的索引。 
•key_len :索引字段的长度。 
•rows :扫描行的数量。 
•Extra :执行情况的说明和描述。

在上面的例子中,已经可以确认是 对 a 表的全表扫描导致效率的不理想,那么
对 a 表的 year 字段创建索引,具体如下:

mysql> create index idx_sales_year on sales(year); 
Query OK, 12 rows affected (0.01 sec) 
Records: 12 Duplicates: 0 Warnings: 0
创建索引后,这条语句的执行计划如下: 
mysql> explain select sum(profit) from sales a,company b where
a.company_id = b.id and a.year = 2006\G; 
*************************** 1. row
*************************** 
id: 1 
select_type: SIMPLE 
table: a 
type: ref 
possible_keys: idx_sales_year 
key: idx_sales_year 
key_len: 4 
ref: const 
rows: 3 
Extra: 
*************************** 2. row
*************************** 
id: 1 
select_type: SIMPLE 
table: b 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 12 
Extra: Using where 
2 rows in set (0.00 sec)

可以发现建立索引后对 a 表需要扫描的行数明显减少(从全表扫描减少到 3
行),可见索引的使用可以大大提高数据库的访问速度,尤其在表很庞大的时候这种优势更为明显,使用索引优化
sql 是优化问题 sql
的一种常用基本方法,在后面的章节中我们会具体介绍如何使索引来优化 sql 。

 

 

 

 

本文主要介绍的是MySQL慢查询分析方法,前一段日子,我曾经设置了一次记录在MySQL数据库中对慢于1秒钟的SQL语句进行查询。想起来有几个十分设置的方法,有几个参数的名称死活回忆不起来了,于是重新整理一下,自己做个笔记。

  对于排查问题找出性能瓶颈来说,最容易发现并解决的问题就是MySQL慢查询以及没有得用索引的查询。

  OK,开始找出MySQL中执行起来不“爽”的SQL语句吧。

  MySQL慢查询分析方法一:

  这个方法我正在用,呵呵,比较喜欢这种即时性的。

  MySQL5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

  MySQL> show variables like ‘long%’;

  注:这个long_query_time是用来定义慢于多少秒的才算“慢查询”

  +—————–+———–+

  | Variable_name | Value |

  +—————–+———–+

  | long_query_time | 10.000000 |

  +—————–+———–+

  1 row in set (0.00 sec)

  MySQL> set long_query_time=1;

  注: 我设置了1, 也就是执行时间超过1秒的都算慢查询。

  Query OK, 0 rows affected (0.00 sec)

  MySQL> show variables like ‘slow%’;

  +———————+—————+

  | Variable_name | Value |

  +———————+—————+

  | slow_launch_time | 2 |

  | slow_query_log | ON |

  注:是否打开日志记录

  | slow_query_log_file | /tmp/slow.log |

  注: 设置到什么位置

  +———————+—————+

  3 rows in set (0.00 sec)

  MySQL> set global slow_query_log=’ON’

  注:打开日志记录

  一旦slow_query_log变量被设置为ON,MySQL会立即开始记录。

  /etc/my.cnf 里面可以设置上面MySQL全局变量的初始值。

  long_query_time=1 slow_query_log_file=/tmp/slow.log

  MySQL慢查询分析方法二:

  MySQLdumpslow命令

  /path/MySQLdumpslow -s c -t 10 /tmp/slow-log

  这会输出记录次数最多的10条SQL语句,其中:

  -s,
是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;

  -t, 是top n的意思,即为返回前面多少条的数据;

  -g, 后边可以写一个正则匹配模式,大小写不敏感的;

  比如

  /path/MySQLdumpslow -s r -t 10 /tmp/slow-log

  得到返回记录集最多的10个查询。

  /path/MySQLdumpslow -s t -t 10 -g “left join” /tmp/slow-log

  得到按照时间排序的前10条里面含有左连接的查询语句。

 

简单点的方法:

打开 my.ini ,找到 [mysqld] 在其下面添加   long_query_time = 2
log-slow-queries = D:/mysql/logs/slow.log
#设置把日志写在那里,可以为空,系统会给一个缺省的文件
#log-slow-queries = /var/youpath/slow.log linux下host_name-slow.log
log-queries-not-using-indexes   long_query_time
是指执行超过多长时间(单位是秒)的sql会被记录下来,这里设置的是2秒。

以下是mysqldumpslow常用参数说明,详细的可应用mysqldumpslow -help查询。  
-s,是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序(从大到小),ac、at、al、ar表示相应的倒叙。
  -t,是top n的意思,即为返回前面多少条数据。 www.2cto.com
 -g,后边可以写一个正则匹配模式,大小写不敏感。  
接下来就是用mysql自带的慢查询工具mysqldumpslow分析了(mysql的bin目录下
),我这里的日志文件名字是host-slow.log。  
列出记录次数最多的10个sql语句   mysqldumpslow -s c -t 10 host-slow.log  
列出返回记录集最多的10个sql语句  mysqldumpslow -s r -t 10 host-slow.log
  按照时间返回前10条里面含有左连接的sql语句   mysqldumpslow -s t -t 10
-g “left join” host-slow.log  
使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句,对MySQL查询语句的监控、分析、优化起到非常大的帮助

 

 

 

在日常开发当中,经常会遇到页面打开速度极慢的情况,通过排除,确定了,是数据库的影响,为了迅速查找具体的SQL,可以通过Mysql的日志记录方法。

— 打开sql执行记录功能

set global log_output=’TABLE’; — 输出到表

set global log=ON; — 打开所有命令执行记录功能general_log, 所有语句:
成功和未成功的.

set global log_slow_queries=ON; — 打开慢查询sql记录slow_log,
执行成功的: 慢查询语句和未使用索引的语句

set global long_query_time=0.1; — 慢查询时间限制(秒)

set global log_queries_not_using_indexes=ON; —
记录未使用索引的sql语句

— 查询sql执行记录

select * from mysql.slow_log order by 1; —
执行成功的:慢查询语句,和未使用索引的语句

select * from mysql.general_log order by 1; — 所有语句:
成功和未成功的.

— 关闭sql执行记录

set global log=OFF;

set global log_slow_queries=OFF;

— long_query_time参数说明

— v4.0, 4.1, 5.0, v5.1 到
5.1.20(包括):不支持毫秒级别的慢查询分析(支持精度为1-10秒);

— 5.1.21及以后版本 :支持毫秒级别的慢查询分析, 如0.1;

— 6.0 到 6.0.3: 不支持毫秒级别的慢查询分析(支持精度为1-10秒);

— 6.0.4及以后:支持毫秒级别的慢查询分析;

通过日志中记录的Sql,迅速定位到具体的文件,优化sql看一下,是否速度提升了呢?

 

 

 

 

 

本文针对MySQL数据库服务器查询逐渐变慢的问题,
进行分析,并提出相应的解决办法,具体的分析解决办法如下:会经常发现开发人员查一下没用索引的语句或者没有limit
n的语句,这些没语句会对数据库造成很大的影…

 

 

 

 

本文针对MySQL数据库服务器查询逐渐变慢的问题,
进行分析,并提出相应的解决办法,具体的分析解决办法如下:

会经常发现开发人员查一下没用索引的语句或者没有limit
n的语句,这些没语句会对数据库造成很大的影响,例如一个几千万条记录的大表要全部扫描,或者是不停的做filesort,对数据库和服务器造成io影响等。这是镜像库上面的情况。

而到了线上库,除了出现没有索引的语句,没有用limit的语句,还多了一个情况,mysql连接数过多的问题。说到这里,先来看看以前我们的监控做法 

  1. 部署zabbix等开源分布式监控系统,获取每天的数据库的io,cpu,连接数 
  2. 部署每周性能统计,包含数据增加量,iostat,vmstat,datasize的情况 
  3. Mysql slowlog收集,列出top 10

以前以为做了这些监控已经是很完美了,现在部署了mysql节点进程监控之后,才发现很多弊端 
第一种做法的弊端:
zabbix太庞大,而且不是在mysql内部做的监控,很多数据不是非常准备,现在一般都是用来查阅历史的数据情况 
第二种做法的弊端:因为是每周只跑一次,很多情况没法发现和报警 
第三种做法的弊端:
当节点的slowlog非常多的时候,top10就变得没意义了,而且很多时候会给出那些是一定要跑的定期任务语句给你。。参考的价值不大 
那么我们怎么来解决和查询这些问题呢

对于排查问题找出性能瓶颈来说,最容易发现并解决的问题就是MYSQL的慢查询以及没有得用索引的查询。 
OK,开始找出mysql中执行起来不“爽”的SQL语句吧。

方法一: 这个方法我正在用,呵呵,比较喜欢这种即时性的。

01 Mysql5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。
02 mysql> show variables like ‘long%’; 注:这个long_query_time是用来定义慢于多少秒的才算“慢查询”
03 +—————–+———–+
04 | Variable_name | Value |
05 +—————–+———–+
06 | long_query_time | 10.000000 |
07 +—————–+———–+
08 1 row in set (0.00 sec)
09 mysql> set long_query_time=1; 注: 我设置了1, 也就是执行时间超过1秒的都算慢查询。
10 Query OK, 0 rows affected (0.00 sec)
11 mysql> show variables like ‘slow%’;
12 +———————+—————+
13 | Variable_name | Value |
14 +———————+—————+
15 | slow_launch_time | 2 |
16 | slow_query_log | ON | 注:是否打开日志记录
17 | slow_query_log_file | /tmp/slow.log | 注: 设置到什么位置
18 +———————+—————+
19 3 rows in set (0.00 sec)
20 mysql> set global slow_query_log=’ON’ 注:打开日志记录
21 一旦slow_query_log变量被设置为ON,mysql会立即开始记录。
22 /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。
23 long_query_time=1
24 slow_query_log_file=/tmp/slow.log

方法二:mysqldumpslow命令

01 /path/mysqldumpslow -s c -t 10 /tmp/slow-log
02 这会输出记录次数最多的10条SQL语句,其中:
03 -s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
04 -t, 是top n的意思,即为返回前面多少条的数据;
05 -g, 后边可以写一个正则匹配模式,大小写不敏感的;
06 比如
07 /path/mysqldumpslow -s r -t 10 /tmp/slow-log
08 得到返回记录集最多的10个查询。
09 /path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log
10 得到按照时间排序的前10条里面含有左连接的查询语句。

最后总结一下节点监控的好处 
1. 轻量级的监控,而且是实时的,还可以根据实际的情况来定制和修改 
2. 设置了过滤程序,可以对那些一定要跑的语句进行过滤 
3.
及时发现那些没有用索引,或者是不合法的查询,虽然这很耗时去处理那些慢语句,但这样可以避免数据库挂掉,还是值得的 
4.
在数据库出现连接数过多的时候,程序会自动保存当前数据库的processlist,DBA进行原因查找的时候这可是利器

  1. 使用mysqlbinlog 来分析的时候,可以得到明确的数据库状态异常的时间段 
    有些人会建义我们来做mysql配置文件设置

调节tmp_table_size 的时候发现另外一些参数 
Qcache_queries_in_cache 在缓存中已注册的查询数目 
Qcache_inserts 被加入到缓存中的查询数目 
Qcache_hits 缓存采样数数目 
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目 
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的,或由于
QUERY_CACHE_TYPE) 
Qcache_free_memory 查询缓存的空闲内存总数 
Qcache_free_blocks 查询缓存中的空闲内存块的数目 
Qcache_total_blocks 查询缓存中的块的总数目 
Qcache_free_memory
可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度

-- 主键索引
ALTER TABLE city ADD PRIMARY KEY(city_id);
-- 唯一索引
ALTER TABLE city ADD UNIQUE  KEY(city_id);
-- 普通索引 或叫辅助索引
CREATE INDEX ixcityname ON city(cityname);
-- 前缀索引 cityname字段创建10个字节
CREATE INDEX ixcityname ON city(cityname(10));
-- 复合索引  创建city表的多列
CREATE INDEX ix1 ON city(cityname(10),citycode);
-- 外键索引
ALTER TABLE city ADD KEY idx_fk_country_id(country_id) ;

图片 2

  3. 确定问题采取优化措施