海量数据模拟

举报
西魏陶渊明 发表于 2022/09/25 02:08:09 2022/09/25
【摘要】 作者: 西魏陶渊明 博客: https://blog.springlearn.cn/ (opens new window) 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 # 一、创建表 create table dept( dno int(5) pr...

作者: 西魏陶渊明 博客: https://blog.springlearn.cn/ (opens new window)

西魏陶渊明

莫笑少年江湖梦，谁不少年梦江湖

# 一、创建表


        create table dept
        (
         dno int(5) primary key default 0,
         dname varchar(20) not null default '',
         loc varchar(30) default ''
        ) engine =innodb default charset=utf8;
        create table emp
        (
         eid int(5) primary key,
         ename varchar(20) not null default '',
         job varchar(20) not null default '',
         deptno int(5) not null default 0
        ) engine =innodb default  charset=utf8;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

# 二、存储函数插入海量数量

存储过程无return,存储函数有。

# 1. 创建存储函数生成id和name

name随机字符串


        delimiter $
        create function randstring(n int) returns varchar(255)
        begin
           declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
           declare return_str varchar(255) default '';
           declare i int default 0;
            while i <n
            do
               set return_str = concat(return_str,substring(all_str,rand()*52,1));
               set i = i+1;
           end while;
           return return_str;
        end $
1 2 3 4 5 6 7 8 9 10 11 12 13

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随机数


        delimiter $
        create function rand_num()returns int(5)
        begin
           declare i int default 0;
           set i = floor(rand() * 100);
           return i;
        end $;
1 2 3 4 5 6 7

# 2. 通过存储过程插入海量数据emp


        create procedure insert_emp(in eid_start int(10),in data_times int(10))
        begin
           declare i int default 0;
           set autocommit = 0;
           repeat
                insert into emp values(eid_start + i,randstring(5),'other',rand_num());
               set i = i + 1;
               until i = data_times
           end repeat;
            commit;
        end $;
1 2 3 4 5 6 7 8 9 10 11

# 2. 通过存储过程插入海量数据dept


        create procedure insert_dept(in dno_start int(10),in data_times int(10))
        begin
           declare i int default 0;
           set autocommit = 0;
           repeat
                insert into dept values(dno_start+i,randstring(6),randstring(8));
               set i = i + 1;
               until i = data_times
           end repeat;
        commit;
        end$
1 2 3 4 5 6 7 8 9 10 11

# 3. 插入海量数据

delimiter ;分割符改回原来

员工表插入80w条数据 call insert_emp(1000,800000); 部门表插入30条数据 call insert_dept(10,30);


        mysql> select count(1) from emp;
        +----------+
        | count(1) |
        +----------+
        |   800000 |
        +----------+
        1 row in set (0.05 sec)
        mysql> select count(1) from dept;
        +----------+
        | count(1) |
        +----------+
        |       30 |
        +----------+
        1 row in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 三、利用profiles分析海量数据

# 1. 打开profiles


        set profiling = on;
        show variables like '%profiling%';
        mysql> set profiling = on;
        Query OK, 0 rows affected, 1 warning (0.00 sec)
        mysql> show variables like '%profiling%';
        +------------------------+-------+
        | Variable_name          | Value |
        +------------------------+-------+
        | have_profiling         | YES   |
        | profiling              | ON    |
        | profiling_history_size | 15    |
        +------------------------+-------+
        3 rows in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 2. 查询每条耗时

profiles会记录每个sql的耗时


        mysql> show profiles;
        +----------+------------+-----------------------------------+
        | Query_ID | Duration   | Query                             |
        +----------+------------+-----------------------------------+
        |        1 | 0.00164000 | show variables like '%profiling%' |
        |        2 | 0.04513900 | select count(1) from emp          |
        |        3 | 0.00056200 | select count(1) from dept         |
        +----------+------------+-----------------------------------+
        3 rows in set, 1 warning (0.00 sec)
1 2 3 4 5 6 7 8 9

但是这样不能精确匹配到耗时在哪里。此时可以使用profile精确来分析sql

# 3. 精确查询耗时

精确 根据上面的Query_ID来精确查找 show profile all for query 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 |
        +--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
        | starting                       | 0.000106 | 0.000094 |   0.000012 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                    | NULL                 |        NULL |
        | 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 |
        | starting                       | 0.000013 | 0.000010 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | launch_hook_trans_begin | rpl_handler.cc       |        1108 |
        | checking permissions           | 0.000009 | 0.000007 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access            | sql_authorization.cc |        2202 |
        | Opening tables                 | 0.000047 | 0.000045 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | open_tables             | sql_base.cc          |        5587 |
        | init                           | 0.000012 | 0.000008 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | execute                 | sql_select.cc        |         661 |
        | System lock                    | 0.000014 | 0.000012 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_lock_tables       | lock.cc              |         332 |
        | optimizing                     | 0.000010 | 0.000007 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                | sql_optimizer.cc     |         213 |
        | statistics                     | 0.000037 | 0.000023 |   0.000014 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 1 |     0 | optimize                | sql_optimizer.cc     |         423 |
        | preparing                      | 0.000025 | 0.000023 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | optimize                | sql_optimizer.cc     |         497 |
        | executing                      | 0.000007 | 0.000004 |   0.000003 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | exec                    | sql_executor.cc      |         228 |
        | Sending data                   | 0.044768 | 0.072019 |   0.003191 |                 0 |                 810 |            0 |             0 |             0 |                 0 |                 0 |                 9 |     0 | exec                    | sql_executor.cc      |         304 |
        | end                            | 0.000018 | 0.000009 |   0.000010 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | execute                 | sql_select.cc        |         714 |
        | query end                      | 0.000006 | 0.000004 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command   | sql_parse.cc         |        4520 |
        | 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 |
        | closing tables                 | 0.000009 | 0.000008 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | mysql_execute_command   | sql_parse.cc         |        4566 |
        | freeing items                  | 0.000026 | 0.000012 |   0.000013 |                 0 |                   0 |            0 |             0 |             1 |                 0 |                 0 |                 0 |     0 | mysql_parse             | sql_parse.cc         |        5237 |
        | cleaning up                    | 0.000011 | 0.000009 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command        | sql_parse.cc         |        2147 |
        +--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-------------------------+----------------------+-------------+
        18 rows in set, 1 warning (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

我们可以看到信息太多了,我们其实只用关系几列的数据就行了。


        mysql> show profile cpu,block io for query 2;
        +--------------------------------+----------+----------+------------+--------------+---------------+
        | Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
        +--------------------------------+----------+----------+------------+--------------+---------------+
        | starting                       | 0.000106 | 0.000094 |   0.000012 |            0 |             0 |
        | Executing hook on transaction  | 0.000008 | 0.000004 |   0.000004 |            0 |             0 |
        | starting                       | 0.000013 | 0.000010 |   0.000002 |            0 |             0 |
        | checking permissions           | 0.000009 | 0.000007 |   0.000003 |            0 |             0 |
        | Opening tables                 | 0.000047 | 0.000045 |   0.000002 |            0 |             0 |
        | init                           | 0.000012 | 0.000008 |   0.000003 |            0 |             0 |
        | System lock                    | 0.000014 | 0.000012 |   0.000003 |            0 |             0 |
        | optimizing                     | 0.000010 | 0.000007 |   0.000002 |            0 |             0 |
        | statistics                     | 0.000037 | 0.000023 |   0.000014 |            0 |             0 |
        | preparing                      | 0.000025 | 0.000023 |   0.000003 |            0 |             0 |
        | executing                      | 0.000007 | 0.000004 |   0.000003 |            0 |             0 |
        | Sending data                   | 0.044768 | 0.072019 |   0.003191 |            0 |             0 |
        | end                            | 0.000018 | 0.000009 |   0.000010 |            0 |             0 |
        | query end                      | 0.000006 | 0.000004 |   0.000002 |            0 |             0 |
        | waiting for handler commit     | 0.000013 | 0.000011 |   0.000001 |            0 |             0 |
        | closing tables                 | 0.000009 | 0.000008 |   0.000002 |            0 |             0 |
        | freeing items                  | 0.000026 | 0.000012 |   0.000013 |            0 |             0 |
        | cleaning up                    | 0.000011 | 0.000009 |   0.000002 |            0 |             0 |
        +--------------------------------+----------+----------+------------+--------------+---------------+
        18 rows in set, 1 warning (0.01 sec)
        mysql>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

# 4. 全局查询日志

仅仅在调优和开发中使用,生产要关闭

show variables like '%general_log%';

开启全局日志记录并将sql都写入到表中


        set global general_log = 1;
        set global log_output = 'table';
1 2 

        mysql> show variables like '%general_log%';
        +------------------+------------------------------------+
        | Variable_name    | Value                              |
        +------------------+------------------------------------+
        | general_log      | OFF                                |
        | general_log_file | /usr/local/var/mysql/localhost.log |
        +------------------+------------------------------------+
        2 rows in set (0.01 sec)
        mysql> set global general_log = 1;
        Query OK, 0 rows affected (0.00 sec)
        mysql> show variables like '%general_log%';
        +------------------+------------------------------------+
        | Variable_name    | Value                              |
        +------------------+------------------------------------+
        | general_log      | ON                                 |
        | general_log_file | /usr/local/var/mysql/localhost.log |
        +------------------+------------------------------------+
        2 rows in set (0.01 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

开启之后就可以在mysql库中的general_log表中进行查询

select * from mysql.general_log;

将sql设置到文件中


        set global general_log = 1;
        set global log_output = 'file';
        set global general_log_file='/Users/liuxin/general.log';
1 2 3

最后求关注,求订阅,谢谢你的阅读!

文章来源: springlearn.blog.csdn.net，作者：西魏陶渊明，版权归原作者所有，如需转载，请联系作者。

原文链接：springlearn.blog.csdn.net/article/details/125857977

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
SQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

目录

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入