MySQL利用自定义函数和存储过程创建海量表，并使用索引优化

昨天学习韩顺平老师的视频时明白了上一章explain的意义，为了自己的联系，我学着创建了一个海量表，供自己练习使用。

代码如下：

#创建表DEPT        CREATE TABLE dept( /*部门表*/  deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  dname VARCHAR(20)  NOT NULL  DEFAULT "",  loc VARCHAR(13) NOT NULL DEFAULT ""  ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;        #创建表EMP雇员  CREATE TABLE emp  (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0,  ename VARCHAR(20) NOT NULL DEFAULT "",  job VARCHAR(9) NOT NULL DEFAULT "",  mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,  hiredate DATE NOT NULL,  sal DECIMAL(7,2)  NOT NULL,  comm DECIMAL(7,2) NOT NULL,  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0  )ENGINE=MyISAM DEFAULT CHARSET=utf8 ;    #工资级别表  CREATE TABLE salgrade  (  grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,  losal DECIMAL(17,2)  NOT NULL,  hisal DECIMAL(17,2)  NOT NULL  )ENGINE=MyISAM DEFAULT CHARSET=utf8;    INSERT INTO salgrade VALUES (1,700,1200);  INSERT INTO salgrade VALUES (2,1201,1400);  INSERT INTO salgrade VALUES (3,1401,2000);  INSERT INTO salgrade VALUES (4,2001,3000);  INSERT INTO salgrade VALUES (5,3001,9999);    # 随机产生字符串  #定义一个新的命令结束符合，因为函数语句中有分号，容易发生错误  delimiter $$  #删除自定的函数  drop  function rand_string $$    #这里我创建了一个函数.    create function rand_string(n INT)  returns varchar(255)  begin   declare chars_str varchar(100) default     'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';   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 ;  select rand_string(6);    # 随机产生部门编号  delimiter $$  drop  function rand_num $$    #这里我们又自定了一个函数  create function rand_num( )  returns int(5)  begin   declare i int default 0;   set i = floor(10+rand()*500);  return i;    end $$      delimiter ;  select rand_num();    #******************************************  #向emp表中插入记录(海量的数据)      delimiter $$  drop procedure insert_emp $$        create procedure insert_emp(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 emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());    until i = max_num   end repeat;     commit;   end $$      delimiter ;  #调用刚刚写好的函数, 1800000条记录,从100001号开始  call insert_emp(100001,1800000);      #**************************************************************  #  向dept表中插入记录    delimiter $$  drop procedure insert_dept $$      create procedure insert_dept(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 dept values ((start+i) ,rand_string(10),rand_string(8));    until i = max_num   end repeat;     commit;   end $$      delimiter ;  call insert_dept(100,10);            #------------------------------------------------  #向salgrade 表插入数据  delimiter $$  drop procedure insert_salgrade $$  create procedure insert_salgrade(in start int(10),in max_num int(10))  begin  declare i int default 0;   set autocommit = 0;   ALTER TABLE emp DISABLE KEYS;     repeat   set i = i + 1;   insert into salgrade values ((start+i) ,(start+i),(start+i));    until i = max_num   end repeat;     commit;   end $$  delimiter ;  #测试不需要了  #call insert_salgrade(10000,1000000);            #----------------------------------------------  

首先创建了一个180W条数据的数据表

觉得太少，机器速度略快 不利于联系，又创建了9820W的数据

（卧槽 图挂了）

貌似有点玩拖了……创建的过程中出现了很多有意思的数据

（卧槽 图挂了）

就是一大堆临时文件……

20分钟之后 变成这样了 这个MYD文件一直在增长 

（卧槽 图挂了）

插了两千万条数据 感觉差不多了。

（卧槽 图挂了）

弄个整数比较好操作，删删改改最后有1400W条

（卧槽 图挂了）

启动慢查询

然后测试一个sql语句 发现很慢 4秒多，然后建立索引

索引文件大约占1/4-1/3

速度比较

确实牛逼的多！至于explain 需要一个专门的学习笔记来记录。

突然知道了怎么复制powershell的代码，方便多了！

mysql> show variables like 'slow%';+---------------------+-------------------------------------------------------------+| Variable_name       | Value                                                       |+---------------------+-------------------------------------------------------------+| slow_launch_time    | 2                                                           || slow_query_log      | OFF                                                         || slow_query_log_file | e:\wamp\bin\mysql\mysql5.6.17\data\2013-20140912XV-slow.log |+---------------------+-------------------------------------------------------------+3 rows in set (0.00 sec)mysql> set slow_query_log="on";ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBALmysql> exitByePS E:\wamp\bin\mysql\mysql5.6.17\bin> cd E:\wamp\bin\mysql\mysql5.6.17\binPS E:\wamp\bin\mysql\mysql5.6.17\bin> mysqld -slow-query-log;PS E:\wamp\bin\mysql\mysql5.6.17\bin> mysql -u root -pEnter password: *****Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 44Server version: 5.6.17 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select * from emp where empno=2345678;ERROR 1046 (3D000): No database selectedmysql> select * fromlustudy.emp where empno=2345678;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fromlustudy.emp where empno=2345678' at line 1mysql> select * from lustudy.emp where empno=2345678;+---------+--------+----------+-----+------------+---------+--------+--------+| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |+---------+--------+----------+-----+------------+---------+--------+--------+| 2345678 | zfflMc | SALESMAN |   1 | 2017-10-13 | 2000.00 | 400.00 |     37 |+---------+--------+----------+-----+------------+---------+--------+--------+1 row in set (3.97 sec)mysql> show variables like 'long%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)mysql> set long_query_time=2;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'long%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 2.000000 |+-----------------+----------+1 row in set (0.00 sec)mysql> select * from lustudy.emp where empno=2345678;+---------+--------+----------+-----+------------+---------+--------+--------+| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |+---------+--------+----------+-----+------------+---------+--------+--------+| 2345678 | zfflMc | SALESMAN |   1 | 2017-10-13 | 2000.00 | 400.00 |     37 |+---------+--------+----------+-----+------------+---------+--------+--------+1 row in set (3.97 sec)mysql> show variables like 'slow%';+---------------------+-------------------------------------------------------------+| Variable_name       | Value                                                       |+---------------------+-------------------------------------------------------------+| slow_launch_time    | 2                                                           || slow_query_log      | OFF                                                         || slow_query_log_file | e:\wamp\bin\mysql\mysql5.6.17\data\2013-20140912XV-slow.log |+---------------------+-------------------------------------------------------------+3 rows in set (0.00 sec)mysql> set slow_query_log="On";ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBALmysql> set globa slow_query_log ="On";ERROR 1193 (HY000): Unknown system variable 'globa'mysql> set global slow_query_log ="On";Query OK, 0 rows affected (0.09 sec)mysql> select * from lustudy.emp where empno=2345678;+---------+--------+----------+-----+------------+---------+--------+--------+| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |+---------+--------+----------+-----+------------+---------+--------+--------+| 2345678 | zfflMc | SALESMAN |   1 | 2017-10-13 | 2000.00 | 400.00 |     37 |+---------+--------+----------+-----+------------+---------+--------+--------+1 row in set (3.98 sec)mysql> select * from lustudy.emp where empno=2345679;+---------+--------+----------+-----+------------+---------+--------+--------+| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |+---------+--------+----------+-----+------------+---------+--------+--------+| 2345679 | gvKorO | SALESMAN |   1 | 2017-10-13 | 2000.00 | 400.00 |    463 |+---------+--------+----------+-----+------------+---------+--------+--------+1 row in set (4.07 sec)mysql> explain select * from 6666666;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6666666' at line 1mysql> explain select * from emp where empno=6666666;ERROR 1046 (3D000): No database selectedmysql> use lustudy;Database changedmysql> explain select * from emp where empno=6666666;+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 14000000 | Using where |+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+1 row in set (0.00 sec)mysql>  select * from emp where empno=6666666;+---------+--------+----------+-----+------------+---------+--------+--------+| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |+---------+--------+----------+-----+------------+---------+--------+--------+| 6666666 | MdiDsf | SALESMAN |   1 | 2017-10-13 | 2000.00 | 400.00 |    219 |+---------+--------+----------+-----+------------+---------+--------+--------+1 row in set (4.12 sec)mysql> alter table emp add index(empno);Query OK, 14000000 rows affected (24.93 sec)Records: 14000000  Duplicates: 0  Warnings: 0mysql> select * from emp where emp =6666666;ERROR 1054 (42S22): Unknown column 'emp' in 'where clause'mysql> select * from emp where empno =6666666;+---------+--------+----------+-----+------------+---------+--------+--------+| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |+---------+--------+----------+-----+------------+---------+--------+--------+| 6666666 | MdiDsf | SALESMAN |   1 | 2017-10-13 | 2000.00 | 400.00 |    219 |+---------+--------+----------+-----+------------+---------+--------+--------+1 row in set (0.03 sec)mysql>