sql与索引优化
数据库表结构优化
系统配置
硬件
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
慢日志查询没有开启,可以开启一下。
找到my.cnf,添加如下内容sudo vim /usr/local/mysql/my.cnf
# 添加慢查询日志
log_output=file
slow_query_log=on
slow_query_log_file = /tmp/mysql-slow.log
log_queries_not_using_indexes=on
long_query_time = 1
重启mysql。
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%quer%';
+----------------------------------------+------------------------------------+
| Variable_name | Value |
+----------------------------------------+------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| log_queries_not_using_indexes | ON |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 8388608 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /tmp/mysql-slow.log |
+----------------------------------------+------------------------------------+
这时候再看,已经开启了慢查询日志。
$ sudo cat /tmp/mysql-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.6.40-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
如果更改日志目录位置
比如,更改为/home/msyql/log/mysql-slow.log
至少需要将log目录所有者设置为mysql:mysql。
这样重启mysql的时候,才能自动生成mysql-slow.log文件。
jiqing@jiqing-pad:/home/mysql/log$ ll
总用量 12
drwxr-xr-x 2 mysql mysql 4096 5月 31 01:46 ./
drwxr-xr-x 3 jiqing jiqing 4096 5月 31 01:26 ../
-rw-rw---- 1 mysql mysql 175 5月 31 01:46 mysql-slow.log
jiqing@jiqing-pad:/home/mysql/log$ pwd
/home/mysql/log
如果log目录不是mysql用户权限,将无法生成日志文件,mysql用户无权写入数据。
mysql自带了一个查看慢日志的工具mysqldumpslow。
执行mysqldumpslow --h可以查看帮助信息。
主要介绍两个参数-s和-t
-s 这个是排序参数,可选的有:
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
c: 计数
l: 锁定时间
r: 返回记录
t: 查询时间
-t n 显示头n条记录。
实例:
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。
用了这个工具就可以查询出来那些sql语句是性能的瓶颈,进行优化,比如加索引,该应用的实现方式等。