delimiter $$ ## 生成随机字符串 create function rand_str(n int) returns varchar(10) begin declare CHARS char(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; declare result varchar(255) default ''; declare i int default 1; while i < n do set result = concat(result, substr(CHARS,floor(1+RAND()*52), 1)); set i = i + 1; end while; return result; end $$
## 生成随机数(i <= R < j) create function rand_num(i int, j int) returns int begin return floor(i + rand() * (j - i)); end $$
delimiter ;
测试数据存储过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
delimiter $$ create procedure insert_tb_user(c int) begin declare start_date int default TO_DAYS(STR_TO_DATE('1970-01-1','%Y-%m-%e')); declare end_date int default TO_DAYS(CURDATE()); declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into tb_user(name,birth) values(rand_str(10),FROM_DAYS(rand_num(start_date,end_date))); until i = c end repeat; set autocommit = 1; end $$ delimiter ;
mysql> select * from tb_user where id=100000; +--------+-----------+------------+ | id | name | birth | +--------+-----------+------------+ | 100000 | UZlihlHEY | 1975-10-09 | +--------+-----------+------------+ 1 row in set (0.00 sec)
mysql> show profiles; +----------+------------+---------------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------------+ | 1 | 0.00048275 | select * from tb_user where id=100000 | +----------+------------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> select * from tb_user where name='UZlihlHEY'; +--------+-----------+------------+ | id | name | birth | +--------+-----------+------------+ | 100000 | UZlihlHEY | 1975-10-09 | +--------+-----------+------------+ 1 row in set (0.42 sec)
mysql> show profiles; +----------+------------+-----------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-----------------------------------------------+ | 1 | 0.00048275 | select * from tb_user where id=100000 | | 2 | 0.00061350 | show index from tb_user | | 3 | 0.00052125 | explain select * from tb_user where id=100000 | | 4 | 0.42458000 | select * from tb_user where name='UZlihlHEY' | +----------+------------+-----------------------------------------------+ 4 rows in set, 1 warning (0.00 sec)
executing The thread has begun executing a statement. 线程已经开始执行语句
Sending data The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query. 线程正在读取并处理select语句选择的行数据，然后将数据发送给客户端。 因为这个状态期间的操作偏重执行大量的磁盘访问(读取磁盘)， 它通常是整个查询生命周期中运行时间最长的状态。
mysql> select * from tb_user where name='UZlihlHEY'; +--------+-----------+------+ | id | name | age | +--------+-----------+------+ | 100000 | DnGrPZAGD | 2 | +--------+-----------+------+ 1 row in set (0.00 sec)
## 创建索引后耗时从原来的0.5秒缩短到现在的0.67毫秒 mysql> show profiles; +----------+------------+------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------+ | 1 | 0.00048275 | select * from tb_user where id=100000 | | 2 | 0.00061350 | show index from tb_user | | 3 | 0.00052125 | explain select * from tb_user where id=100000 | | 4 | 0.42458000 | select * from tb_user where name='UZlihlHEY' | | 5 | 5.31390800 | create index idx_tb_user_name on tb_user(name) | | 6 | 0.00033175 | show index from tb_user | | 7 | 0.00046250 | select * from tb_user where name='UZlihlHEY' | +----------+------------+------------------------------------------------+ 7 rows in set, 1 warning (0.00 sec)
## 索引列在表达式中 mysql> select * from tb_user where id+30000=130000; +--------+-----------+------+ | id | name | age | +--------+-----------+------+ | 100000 | DnGrPZAGD | 2 | +--------+-----------+------+ 1 row in set (0.38 sec)
## SQL解析器无法优化这样的等式 mysql> explain select * from tb_user where id+30000=130000\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb_user partitions: NULL type: ALL # 全表扫描 possible_keys: NULL key: NULL # 没有用到主键索引 key_len: NULL ref: NULL rows: 998430 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
## 索引列单独在等式的一端，仍可以使用索引 mysql> select * from tb_user where id=30000+70000; +--------+-----------+------+ | id | name | age | +--------+-----------+------+ | 100000 | DnGrPZAGD | 2 | +--------+-----------+------+ 1 row in set (0.00 sec)
