mysql性能优化

参考：

MySQL时间设计 int timestamp datatime 查询效率性能比较

时间类型字段的查询写法

UNIX_TIMESTAMP(d_timestamp) < time($datetime) //举例

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 full  processlist;
show status like '%Max_used_connections%';
show status like '%Threads_connected%';#当前连接数
show status like '%table_lock%';#表锁定
show status like 'innodb_row_lock%';#行锁定
show status like '%qcache%'; #查询缓存情况
show status like 'Qcache%';
show status like 'Aborted_clients';#由于客户没有正确关闭连接已经死掉，已经放弃的连接数量
show variables like "%binlog%";
show variables like "%query_cache%";
show variables like '%max_connections%';//查看最大连接数量
show variables like '%timeout%';#查看超时时间
show variables like 'log_%'; #查看日志是否启动

慢查询日志

检测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, 后边可以写一个正则匹配模式，大小写不敏感的；

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：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况(注:不一定好于index)
nindex：以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描
nALL：全表扫描，应该尽量避免

mysql性能优化

|-- 服务器硬件的优化
|-- MySQL数据库配置优化
|-- CentOS系统针对mysql的参数优化
    |-- 内核相关参数(/etc/sysctl.conf)
    |-- 增加资源限制(/etc/security/limit.conf)
    |-- 磁盘调度策略
|-- MySQL的参数配置
|-- MySQL表结构与SQL优化
    |-- 索引优化规则
        |-- 1.使用最左前缀规则
        |-- 2.模糊查询不能利用索引(like '%XX'或者like '%XX%')
        |-- 3.不要过多创建索引
        |-- 4.索引长度尽量短
        |-- 5.索引更新不能频繁
        |-- 6.索引列不能参与计算
    |-- 查询时的优化
        |-- 小表驱动大表
        |-- 避免全表扫描
        |-- 避免mysql放弃索引查询
        |-- 使用覆盖索引，少使用select*
        |-- order by的索引生效
        |-- 不正确的使用导致索引失效
        |-- for update锁表
    |-- 其他优化
        |-- 开启慢查询
        |-- 实时获取有性能问题的SQL
        |-- 垂直分割
        |-- 拆分执行时间长的DELETE或INSERT语句

MySQL数据库配置优化

表示缓冲池字节大小。
推荐值为物理内存的50%~80%。
innodb_buffer_pool_size

用来控制redo log刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1

每提交1次事务同步写到磁盘中，可以设置为n。
sync_binlog=1

脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。
innodb_max_dirty_pages_pct=30

后台进程最大IO性能指标。
默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200

指定innodb共享表空间文件的大小。
innodb_data_file_path

慢查询日志的阈值设置，单位秒。
long_qurey_time=0.3

mysql复制的形式，row为MySQL8.0的默认形式。
binlog_format=row

调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections=200

过大，实例恢复时间长；过小，造成日志切换频繁。
innodb_log_file_size

全量日志建议关闭。
默认关闭。
general_log=0

mysql性能优化

Mysql调试和分析

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

MySql查看命令和设置

慢查询分析mysqldumpslow

explain分析查询

mysql性能优化

目录

MySQL数据库配置优化