mysql 性能调试_MySql性能调试

MySql自带的mysqlslap命令用来检测数据服务器接受并发的能力

mysqlslap --concurrency=100 --iterations=1 --create-schema='sunboDataBase' --query='select * from Table1;' --number-of-queries=10 --debug-info -p123456 -uroot

--create-schema=name:指定测试的数据库名

--query：指定测试的SQL语句

--concurrency=100：模拟100个客户端

--number-of-queries=1000：同时并发 1000次

--iterations=1：迭代执行次数

--debug-info:打印输出服务器内存等信息

-p123456:数据库密码123456

-uroot:数据库登陆用户root

-h 172.16.81.99：远程数据库地址

-P 3306：端口号

MySql查看命令和设置

性能瓶颈定位Show命令

我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：

Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)

Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

查看状态变量及帮助：

比较全的Show命令的使用可参考： http://blog.phpbean.com/a.cn/18/

慢查询日志

检测MySQL是否开启慢日志功能

MariaDB [(none)]> show variables like "slow%";

+---------------------+---------------------------+

| Variable_name | Value |

+---------------------+---------------------------+

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | VM_63_166_centos-slow.log |

+---------------------+---------------------------+

3 rows in set (0.00 sec)

开启慢日志

MariaDB [(none)]> set global slow_query_log=ON;

设置超时需要记录的时间

为便于演示，我们将全局和session级别long_query_time设置为1

root@localhost[tempdb]> set global long_query_time=1;

root@localhost[tempdb]> set session long_query_time=1;

只要是SQL语句执行超过1S的就会记录在VM_63_166_centos-slow.log

设置没有使用索引的SQl也要记录在日志里

root@localhost[tempdb]> set global log_queries_not_using_indexes=1;

慢查询分析mysqldumpslow

对于Log里数据比较多的时候 可以使用mysqldumpslow进行过滤

[root@VM_63_166_centos ~]# mysqldumpslow -s t -g "insert" /var/lib/mysql/VM_63_166_centos-slow.log

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

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

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

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

MariaDB [sunboDataBase]> explain select * from Table1执行如下

+------+-------------+--------+-------+------+---------+------+--------+-------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+--------+------+---------------+------+---------+------+--------+-------+

| 1 | SIMPLE | Table1 | ALL | NULL | NULL | NULL | NULL | 630842 | |

+------+-------------+--------+------+---------------+------+---------+------+--------+-------+

1 row in set (0.00 sec)

EXPLAIN字段：

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

type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL

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

key：实际使用的索引。如果为NULL，则没有使用索引。MYSQL很少会选择优化不足的索引，此时可以在SELECT语句中使用USE INDEX(index)来强制使用一个索引或者用IGNORE INDEX(index)来强制忽略索引

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

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

rows：MySQL认为必须检索的用来返回请求数据的行数

由于SQL语句比较少以下情况没有出现 先一放等遇到了在解决

nsystem、const：可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.

neq_ref：访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或惟一键)

nref：访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生

nrange：这个连接类型使用索引返回一个范围中的行，比如使用>或

nindex：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描

nALL：全表扫描，应该尽量避免

Extra：关于MYSQL如何解析查询的额外信息，主要有以下几种

nusing index：只用到索引,可以避免访问表.

nusing where：使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.

nusing tmporary：用到临时表

nusing filesort：用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)

nrange checked for eache record(index map:N)：没有好的索引.

索引及查询优化

全文索引：MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型，可以索引列的左前缀

多列索引有一个特点，即最左前缀(Leftmost Prefixing)。假如有一个多列索引为key(firstname lastname age)，当搜索条件是以下各种列的组合和顺序时，MySQL将使用该多列索引：

firstname，lastname，age

firstname，lastname

firstname

也就是说，相当于还建立了key(firstname lastname)和key(firstname)。

合理的建立索引的建议：

越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值

当结果集只有一行数据时使用LIMIT 1

避免SELECT *，始终指定你需要的列

使用连接(JOIN)来代替子查询(Sub-Queries)

使用ENUM、CHAR 而不是VARCHAR，使用合理的字段属性长度

尽可能的使用NOT NULL

固定长度的表会更快

拆分大的DELETE 或INSERT 语句

查询的列越小越快

Where条件

在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。

有些where条件会导致索引无效：

Ø where子句的查询条件里有！=，MySQL将无法使用索引。

Ø where子句使用了Mysql函数的时候，索引将无效，比如：select * from tb where left(name, 4) = ‘xxx’

Ø 使用LIKE进行搜索匹配的时候，这样索引是有效的：select * from tbl1 where name like ‘xxx%’，而like ‘%xxx%’ 时索引无效