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
[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
