海量数据模拟
SQL优化
作者: 西魏陶渊明 博客: https://blog.springlearn.cn/
!!! tip 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 !!!
一、创建表
sql
1create table dept
2(
3 dno int(5) primary key default 0,
4 dname varchar(20) not null default '',
5 loc varchar(30) default ''
6) engine =innodb default charset=utf8;
7
8create table emp
9(
10 eid int(5) primary key,
11 ename varchar(20) not null default '',
12 job varchar(20) not null default '',
13 deptno int(5) not null default 0
14) engine =innodb default charset=utf8;
15二、存储函数插入海量数量
存储过程无return,存储函数有。
1. 创建存储函数生成id和name
name随机字符串
text
1delimiter $
2create function randstring(n int) returns varchar(255)
3begin
4 declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
5 declare return_str varchar(255) default '';
6 declare i int default 0;
7 while i <n
8 do
9 set return_str = concat(return_str,substring(all_str,rand()*52,1));
10 set i = i+1;
11 end while;
12 return return_str;
13end $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)
如果开启了慢慢查询日志,在开启存储函数就会冲突,解决办法1就是管理慢日志查询。
解决办法2:
show variables like '%log_bin_trust_function_creators%';
set global log_bin_trust_function_creators=1;
id随机数
text
1delimiter $
2create function rand_num()returns int(5)
3begin
4 declare i int default 0;
5 set i = floor(rand() * 100);
6 return i;
7end $;2. 通过存储过程插入海量数据emp
text
1create procedure insert_emp(in eid_start int(10),in data_times int(10))
2begin
3 declare i int default 0;
4 set autocommit = 0;
5 repeat
6 insert into emp values(eid_start + i,randstring(5),'other',rand_num());
7 set i = i + 1;
8 until i = data_times
9 end repeat;
10 commit;
11end $;2. 通过存储过程插入海量数据dept
text
1create procedure insert_dept(in dno_start int(10),in data_times int(10))
2begin
3 declare i int default 0;
4 set autocommit = 0;
5 repeat
6 insert into dept values(dno_start+i,randstring(6),randstring(8));
7 set i = i + 1;
8 until i = data_times
9 end repeat;
10commit;
11end$3. 插入海量数据
delimiter ;分割符改回原来
员工表插入80w条数据
call insert_emp(1000,800000);
部门表插入30条数据
call insert_dept(10,30);
text
1mysql> select count(1) from emp;
2+----------+
3| count(1) |
4+----------+
5| 800000 |
6+----------+
71 row in set (0.05 sec)
8
9mysql> select count(1) from dept;
10+----------+
11| count(1) |
12+----------+
13| 30 |
14+----------+
151 row in set (0.00 sec)
16三、利用profiles分析海量数据
1. 打开profiles
text
1set profiling = on;
2show variables like '%profiling%';
3
4mysql> set profiling = on;
5Query OK, 0 rows affected, 1 warning (0.00 sec)
6
7mysql> show variables like '%profiling%';
8+------------------------+-------+
9| Variable_name | Value |
10+------------------------+-------+
11| have_profiling | YES |
12| profiling | ON |
13| profiling_history_size | 15 |
14+------------------------+-------+
153 rows in set (0.00 sec)
16
2. 查询每条耗时
profiles会记录每个sql的耗时
text
1mysql> show profiles;
2+----------+------------+-----------------------------------+
3| Query_ID | Duration | Query |
4+----------+------------+-----------------------------------+
5| 1 | 0.00164000 | show variables like '%profiling%' |
6| 2 | 0.04513900 | select count(1) from emp |
7| 3 | 0.00056200 | select count(1) from dept |
8+----------+------------+-----------------------------------+
93 rows in set, 1 warning (0.00 sec)但是这样不能精确匹配到耗时在哪里。此时可以使用profile精确来分析sql
3. 精确查询耗时
精确
根据上面的Query_ID来精确查找
show profile all for query 2;
text
1+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
2| Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
3+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
4| starting | 0.000106 | 0.000094 | 0.000012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
5| Executing hook on transaction | 0.000008 | 0.000004 | 0.000004 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1106 |
6| starting | 0.000013 | 0.000010 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | launch_hook_trans_begin | rpl_handler.cc | 1108 |
7| checking permissions | 0.000009 | 0.000007 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 2202 |
8| Opening tables | 0.000047 | 0.000045 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5587 |
9| init | 0.000012 | 0.000008 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 661 |
10| System lock | 0.000014 | 0.000012 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 332 |
11| optimizing | 0.000010 | 0.000007 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 213 |
12| statistics | 0.000037 | 0.000023 | 0.000014 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | optimize | sql_optimizer.cc | 423 |
13| preparing | 0.000025 | 0.000023 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 497 |
14| executing | 0.000007 | 0.000004 | 0.000003 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 228 |
15| Sending data | 0.044768 | 0.072019 | 0.003191 | 0 | 810 | 0 | 0 | 0 | 0 | 0 | 9 | 0 | exec | sql_executor.cc | 304 |
16| end | 0.000018 | 0.000009 | 0.000010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | execute | sql_select.cc | 714 |
17| query end | 0.000006 | 0.000004 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4520 |
18| waiting for handler commit | 0.000013 | 0.000011 | 0.000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ha_commit_trans | handler.cc | 1533 |
19| closing tables | 0.000009 | 0.000008 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4566 |
20| freeing items | 0.000026 | 0.000012 | 0.000013 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5237 |
21| cleaning up | 0.000011 | 0.000009 | 0.000002 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2147 |
22+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
2318 rows in set, 1 warning (0.00 sec)
24我们可以看到信息太多了,我们其实只用关系几列的数据就行了。
text
1mysql> show profile cpu,block io for query 2;
2+--------------------------------+----------+----------+------------+--------------+---------------+
3| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
4+--------------------------------+----------+----------+------------+--------------+---------------+
5| starting | 0.000106 | 0.000094 | 0.000012 | 0 | 0 |
6| Executing hook on transaction | 0.000008 | 0.000004 | 0.000004 | 0 | 0 |
7| starting | 0.000013 | 0.000010 | 0.000002 | 0 | 0 |
8| checking permissions | 0.000009 | 0.000007 | 0.000003 | 0 | 0 |
9| Opening tables | 0.000047 | 0.000045 | 0.000002 | 0 | 0 |
10| init | 0.000012 | 0.000008 | 0.000003 | 0 | 0 |
11| System lock | 0.000014 | 0.000012 | 0.000003 | 0 | 0 |
12| optimizing | 0.000010 | 0.000007 | 0.000002 | 0 | 0 |
13| statistics | 0.000037 | 0.000023 | 0.000014 | 0 | 0 |
14| preparing | 0.000025 | 0.000023 | 0.000003 | 0 | 0 |
15| executing | 0.000007 | 0.000004 | 0.000003 | 0 | 0 |
16| Sending data | 0.044768 | 0.072019 | 0.003191 | 0 | 0 |
17| end | 0.000018 | 0.000009 | 0.000010 | 0 | 0 |
18| query end | 0.000006 | 0.000004 | 0.000002 | 0 | 0 |
19| waiting for handler commit | 0.000013 | 0.000011 | 0.000001 | 0 | 0 |
20| closing tables | 0.000009 | 0.000008 | 0.000002 | 0 | 0 |
21| freeing items | 0.000026 | 0.000012 | 0.000013 | 0 | 0 |
22| cleaning up | 0.000011 | 0.000009 | 0.000002 | 0 | 0 |
23+--------------------------------+----------+----------+------------+--------------+---------------+
2418 rows in set, 1 warning (0.01 sec)
25
26mysql>4. 全局查询日志
仅仅在调优和开发中使用,生产要关闭
show variables like '%general_log%';
开启全局日志记录并将sql都写入到表中
text
1set global general_log = 1;
2set global log_output = 'table';text
1mysql> show variables like '%general_log%';
2+------------------+------------------------------------+
3| Variable_name | Value |
4+------------------+------------------------------------+
5| general_log | OFF |
6| general_log_file | /usr/local/var/mysql/localhost.log |
7+------------------+------------------------------------+
82 rows in set (0.01 sec)
9
10mysql> set global general_log = 1;
11Query OK, 0 rows affected (0.00 sec)
12
13mysql> show variables like '%general_log%';
14+------------------+------------------------------------+
15| Variable_name | Value |
16+------------------+------------------------------------+
17| general_log | ON |
18| general_log_file | /usr/local/var/mysql/localhost.log |
19+------------------+------------------------------------+
202 rows in set (0.01 sec)开启之后就可以在mysql库中的general_log表中进行查询
select * from mysql.general_log;
将sql设置到文件中
text
1set global general_log = 1;
2set global log_output = 'file';
3set global general_log_file='/Users/liuxin/general.log';
最后求关注,求订阅,谢谢你的阅读!