思路

MySQL服务器调优思路
MySQL服务器调优思路

第一步要做的是确定问题的症结,而不是一上来就看表结构和查询语句。

开启profiling

MySQL默认会开启缓存池,本地调试的时候清除缓存的命令是:reset query cache

MariaDB [(none)]> show variables like '%profi%';  
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

MariaDB [(none)]> set profiling=on;

# 打开之后就可以查看每一次查询了
MariaDB [(none)]> show profiles;
+----------+------------+-------------------------------+
| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+
|        1 | 0.00033615 | show variables like '%profi%' |
+----------+------------+-------------------------------+
1 row in set (0.00 sec)

# 查看查询每一步的耗时
MariaDB [(none)]> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000034 |
| Opening tables       | 0.000027 |
| After opening tables | 0.000003 |
| System lock          | 0.000002 |
| Table lock           | 0.000002 |
| After table lock     | 0.000004 |
| init                 | 0.000007 |
| optimizing           | 0.000010 |
| statistics           | 0.000014 |
| preparing            | 0.000006 |
| executing            | 0.000002 |
| Filling schema table | 0.000185 |
| executing            | 0.000002 |
| Sending data         | 0.000012 |
| end                  | 0.000003 |
| query end            | 0.000002 |
| closing tables       | 0.000002 |
| removing tmp table   | 0.000004 |
| closing tables       | 0.000002 |
| freeing items        | 0.000003 |
| updating status      | 0.000008 |
| cleaning up          | 0.000003 |
+----------------------+----------+
22 rows in set (0.00 sec)

一般的优化就是profiling结合explain。explain中几个注意点如下:

possible_key: 可能用到的索引,系统估计可能用的几个索引,但最终,只能用1个
key : 最终用的索引
key_len: 使用的索引的最大长度

重点关注的是type:

all: 意味着从表的第1行,往后,逐行做全表扫描,运气不好扫描到最后一行.
index: 比all性能稍好一点,通俗的说: all 扫描所有的数据行,相当于data_all,index 扫描所有的索引节点,相当于index_all。两种情况下可能出现:索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描;利用索引来进行排序,但取出所有的节点。
ref:指通过索引列,可以直接引用到某些数据行
eq_ref:是指,通过索引列,直接引用某1行数据常见于连接查询中
const, system, null 这3个分别指查询优化到常量级别, 甚至不需要查找时间.一般按照主键来查询时,易出现const,system或者直接查询某个表达式,不经过表时, 出现NULL

ref列:连接查询的时候前表和后表的引用关系

extra列:

  • index:是指用到了索引覆盖,效率非常高
  • using where 是指光靠索引定位不了,还得where判断一下
  • using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
  • using filesort : 文件排序(文件可能在磁盘,也可能在内存)

select sum(shop_price) from goods group by cat_id(???? 这句话,用到了临时表和文件排序)

SQL性能优化的目标,至少要达到range级别,要求是ref级别,最好是const。

MySQL状态监控收集

定时收集MySQL的指标绘制成图表,便于查询分析问题。

  • show status;可以查看服务器状态,重点观察以下几个指标:Queries,Threads_connected,Threads_running。可以根据这个指标计算出QPS。
  • show proceslist;显示了哪些线程在运行,重点关注:State列。

几个很耗时的状态如下,尽量避免:

  • Convert HEAP to MyISAM:查询结果太大内存放不下放在磁盘,show variables like '%tmp_table%',少取数据或者调整服务器配置临时表的大小
  • Create tmp table:如group的时候创建临时表
  • Copying to tmp table on disk:
  • Sending data:发送数据过多
  • Sorting result
  • Locked:被其他查询锁住

表优化和列类型选择

  • 字段类型选择优先级:整数 > data,time > char,vchar > blob,字符串类型要考虑字符集的转换和排序的时候的校对集
  • 字段类型够用就行,能用smallint都不要用int
  • 避免用NULL:不利于索引,要用特殊字节标注,在磁盘上的空间其实更大

一般而言group by需要按照分组字段进行排序,排序结果放在临时表,然后根据临时表做聚合操作。因此explain的时候会出现Using temporaryUsing filesort

MyISAM会缓存count(*),一旦有where查询count(*)速度就不快了,尤其是where的列没加索引的情况下。
union all不过滤效率较高,去重应该放在程序里面。

SQL优化

sql优化
sql优化

索引优化

索引优化
索引优化

参考资料: