查询优化
- 永远是小表驱动大表：in 和exist 哪个效率搞？不能一概而论，当in后面跟的子查询比主查询少，in的效率高。当子查询比主查询数据多的时候用exist效率高。
- order by
- 尽量使用index排序，避免filesort排序
- 当排序字段是两个或者多个索引时候，如果一个升序一个降序则会出现filesort
- 要尽可能的使用index排序，遵循最左原则，order by后面跟的索引字段带头的必须在，或者跟where组成的加在一起组成最左。
索引index:name_age
select * from user order by name,age
或者是
select * from user where name = 'z3' order by age- mysql4.1之后order by 采用单路算法排序（之前是双路相较于单路多一次I/O），mysql会有一个缓冲区sort_buffer_size，当缓冲区放不下所查询的数据时候就会开辟新的一路再去缓冲然后把数据拼接起来，这样导致多次I/O，没有达到单路的效果，反而不如双路，因此当发现此情况应把mysql的缓冲区调大。
- group by 与order by的一致 唯一的区别就是having能在where限制的就不要在having限制了。
慢查询日志
- 查看是否开启
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)- 开启慢查询，一般不开启，会对性能有消耗
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)
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 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)修改阈值
mysql> set session long_query_time = 3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)模拟查询5秒
mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.01 sec)
[root@localhost Desktop]# cd /var/lib/mysql
[root@localhost mysql]# cat localhost-slow.log
/usr/sbin/mysqld, Version: 5.5.48-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 200305 23:50:34
# User@Host: root[root] @ localhost []
# Query_time: 5.014617 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use db1;
SET timestamp=1583481034;
select sleep(5);检查系统中有多少条慢查询记录
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 1 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select sleep(6);
+----------+
| sleep(6) |
+----------+
| 0 |
+----------+
1 row in set (6.00 sec)
mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 2 |
+---------------+-------+
1 row in set (0.00 sec)- mysqldumpslow分析慢查询
具体用法百度搜索
[root@localhost mysql]# mysqldumpslow --help;
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time- 批量插入数据脚本
-- 创建函数返回随机字母
DELIMITER $
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJGLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i+1;
end while;
return return_str;
end $
-- 创建函数返回一个随机数
DELIMITER $
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $
-- 创建存储过程批量插入数据
DELIMITER $
create procedure insert_user(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i+1;
insert into user values((start+i),rand_string(6));
until i = max_num
end repeat;
commit;
end $
-- 要先开启 log_bin_trust_function_creators 参数否则会报错
ERROR 1418 (HY000): This function has none of DETERMINISTIC,
NO SQL, or READS SQL DATA in its declaration and binary logging
is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
1 row in set (0.00 sec)
-- 先插入50万
mysql> call insert_user(1,500000)$
Query OK, 0 rows affected, 1 warning (42.22 sec)
-- 哈哈看来我的机器还凑合哦show profile（展示明细。默认关闭，开启之后默认保存最近15次结果）
- 是什么：记录当前会话中，一定是当前会话中，sql语句的执行的资源消耗情况，可用于sql的调优测量。
- 查看状态
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)- 开启
mysql> set profiling = on;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set (0.00 sec)- show profiles;查看最近15条sql
mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 11 | 0.00010175 | insert into user values((start+i),rand_string(6)) |
| 12 | 0.00001300 | SET autocommit = 0 |
| 13 | 0.00007225 | insert into user values((start+i),rand_string(6)) |
| 14 | 0.00004425 | set global log_bin_trust_function_creators = 1 |
| 15 | 0.00001025 | SET autocommit = 0 |
| 16 | 0.01168150 | commit |
| 17 | 0.00001150 | SET autocommit = 0 |
| 18 | 0.00508100 | commit |
| 19 | 0.00017150 | select * from user |
| 20 | 0.00005525 | select * from user |
| 21 | 0.00021675 | delete from user |
| 22 | 0.00000925 | SET autocommit = 0 |
| 23 | 0.08364900 | commit |
| 24 | 0.25836150 | select * from user |
| 25 | 0.00002875 | shoe profiles |
+----------+------------+---------------------------------------------------+
15 rows in set (0.00 sec)- 诊断 show profile cpu,block io for query 27; 27代表上一步中show profiles;命令的query id
mysql> show profile cpu,block io for query 27;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000026 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000062 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000049 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.117902 | 0.109983 | 0.000000 | 0 | 0 |
| end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000091 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000096 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
24 rows in set (0.00 sec)此处只看了cpu 和 block io 也用其他参数内存等等，若status列出现下列几个必须要优化
converting HEAD to MyISAM 查询结果太大，内存不够往磁盘搬
creating temp table 创建临时表，用完再删除
copying to temp table on disk 把内存中的临时表复制到磁盘，危险全局查询日志（永远不要在正式环境启用）
-- 设置开启
mysql> set global general_log = 1;
Query OK, 0 rows affected (0.01 sec)
-- 设置输出表
mysql> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)
-- 查询一条记录
mysql> select * from user limit 10;
+------+--------+
| id | name |
+------+--------+
| 2 | lJPVie |
| 3 | WulVTG |
| 4 | zDsgDv |
| 5 | tIkavd |
| 6 | emXcrC |
| 7 | OjGFfL |
| 8 | nhZyuC |
| 9 | DlvvNF |
| 10 | ObPWoD |
| 11 | cAtsJr |
+------+--------+
10 rows in set (0.01 sec)
-- 在mysql库下的general_log表中记录
mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2020-03-06 11:20:52 | root[root] @ localhost [] | 1 | 1 | Query | select * from user limit 10 |
| 2020-03-06 11:21:18 | root[root] @ localhost [] | 1 | 1 | Query | select * from mysql.general_log |
+---------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.00 sec)
只是自己学习记录，不足之处欢迎指正！
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
