mysql性能优化

一、慢查询

1、慢查询日志

是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQl语句的日志，通过定位分析性能的瓶颈，才能更好地优化数据库系统的性能。

2、参数说明

（1）slow_query_log：慢查询开启状态

（2）slow_query_log_file：慢查询日志存放的位置

（3）long_query_time：查询超过多少秒才记录

3、设置步骤

1、查看慢查询相关参数

mysql> show variables like 'slow_query%';#（查看是否开启了慢查询日志，日志保存的位置）
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

mysql> show variables like 'long_query_time';#（查看当超过多少秒时会被记录到日志中去）
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2、设置方法

#1、开启慢查询，将slow_query_log设置为on状态
mysql> set global slow_query_log='on';
Query OK, 0 rows affected (0.66 sec)

#2、设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/var/lib/mysql/localhost-slow.log';
Query OK, 0 rows affected (0.00 sec)

#3、设置超出时间，当超出指定时间后会被记录到慢查询日志中
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

#4、重启MySQL服务
[root@localhost etc]# systemctl restart mysqld

#5、重新登陆后，查看设置后的参数
mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

#6、测试
#1）执行一条满查询SQl语句
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.02 sec)
#2）查看存在该日志，并进行查看
ls /var/lib/mysql/localhost-slow.log

3、慢查询日志解析

第一行：用户名、用户的IP信息、线程ID号
第二行：执行话费的时间（单位：毫秒）
第三行：执行获得锁的时间
第四行：获得的结果行数
第五行：扫描的数据行数
第六行：这SQL执行的具体时间
第七行：具体的SQL语句

4、慢查询分析

慢查询的日志记录非常多，要从里面找寻一条查询慢的日志并不是很容易的事情，一般来说都需要一些工具辅助才能快速定位到需要优化的 SQL 语句。

4.1Mysqldumpshow

mysqldumpshow -s r -t 10 slow-mysql.log -s order (c,t,l,r,at,al,ar);
#c：总次数
#t：总时间
#l：锁的时间
#r：总数据行
#at,al,ar :t,l,r 平均数 【例如:at = 总时间/总次数】
#-t top 指定取前面几条作为结果输出


# 按照时间排序，取出前10条记录
mysqldumpslow -s t -t 10 /usr/local/mysql/data/mysql-slow.log

二、索引

索引是一种可以让SELECT语句提高效率的数据结构，可以起到快速定位的作用。

1、索引的优势和劣势

优势：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录
通过索引列对数据进行排序，减低数据排序，降低CPU的消耗
- 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
- 如果按照索引的顺序进行排序，对于order by语句来说，效率会提高很多

劣势：

索引会占据磁盘空间
索引虽然会提高查询效率，但会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的所有文件。

2、mysql索引 B+ tree

2.1B+tree定义

数据只存储在叶子节点上，非叶子节点只保存索引信息；
- 非叶子节点（索引节点）存储的只是一个Flag，不保存实际数据记录；
- 索引节点指示该节点的左子树比这个Flag小，而右子树大于等于这个Flag；
叶子节点本身按照数据的升序排序进行链接（串联起来）

2.2为什么使用B+数索引而不是用hash索引

因为hash索引不支持范围查找
MySQL中hash解决冲突：InnoDB中采用除法散列函数(取余法)，冲突机制采用链接法
Hash 索引无法做 like ‘xxx%’ 这样的部分模糊查询，因为需要对完整 key 做 Hash 计算，定位bucket。而 B+tree 索引具有最左前缀匹配，可以进行部分模糊查询

mysql性能分析