MySQL 之 创建千万数据测试

 

-- MySQL测试任务：使用存储过程，往表中插入千万级数据，根据索引优化速度

-- 1.使用索引查询

-- 2.不使用索引查

-- 3.比较两者查询速度的差异

1.创建数据和索引

1.创建索引测试表

1 2 3 4 5 6 7

DROP TABLE IF EXISTS big_data; CREATE TABLE big_data( id int PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(16) DEFAULT NULL, age int(11), email varchar(64) default null )ENGINE=MYISAM DEFAULT CHARSET=utf8;

 注意:MYISAM存储引擎不产生引擎事务，数据插入速度极快，为方便快速插入千万条测试数据，等我们插完数据，再把存储类型修改为InnoDB

　　2.创建存储过程，插入数据

1 2 3 4 5 6 7 8

CREATE PROCEDURE `insert_data_p`(IN num INT) BEGIN DECLARE n INT DEFAULT 1; WHILE n <= num DO INSERT INTO big_data(name,age,email)values(concat('alex',n),rand()*50,concat('alex',n,'@qq.com')); set n=n+1; end while; ENd;

　　3.调用存储过程,插入1000万条数据(总耗时:382.614 sec)

1	CALL insert_data_p(10000000);

　　

 　　

　  4.此步骤可以忽略。修改引擎(执行耗时: 104.431 sec  )

1	ALTER TABLE `big_data` ENGINE=INNODB;

　　5.通过主键索引查询(总耗时:0.012 sec)

1	select * from big_data where id = 8950000

注意: 大家可能会发现一个问题: 第一次查询时,会有点慢,而第二次查询时就要比第一次快很多,  why?

原因: 表引擎使用innodb.第一次查询会走数据文件，第二次直接走buffer_pool(缓冲池),所以比直接查询数据文件要快

　　6.为 name 字段创建普通索引(耗时:73.972 sec)

1	CREATE INDEX index_name ON big_data (name);

 2.组合索引的生效原则: 

　　原则: 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

+ View Code

 3.正确使用索引

　　数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效：

+ View Code

4.需要注意

1 2 3 4 5 6 7 8 9 10

1.避免使用select * 2.count(1)或count(列) 代替 count(*) 3. 创建表时尽量时 char 代替 varchar 4. 表的字段顺序固定长度的字段优先 5. 组合索引代替多个单列索引（经常使用多个条件查询时） 6. 使用连接（JOIN）来代替子查询(Sub-Queries) 7. 不要有超过5个以上的表连接（JOIN） 8. 优先执行那些能够大量减少结果的连接。 9. 连表时注意条件类型需一致 10.索引散列值不适合建索引，例：性别不适合

5.时间 (执行计划)

　　explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

1	explain select * from big_data where age ='44' and  name ='用户8950000'

　

+ View Code

6.慢日志查询

 慢查询日志 

　　 将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。

慢查询日志参数:

1 2 3 4 5

long_query_time     ：  设定慢查询的阀值，超出设定值的SQL即被记录到慢查询日志，缺省值为10s  slow_query_log      ：  指定是否开启慢查询日志  log_slow_queries    ：  指定是否开启慢查询日志(该参数已经被slow_query_log取代，做兼容性保留)  slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log  log_queries_not_using_indexes: 为使用索引的搜索是否记录

查看 MySQL慢日志信息

1 2 3 4

#.查询慢日志配置信息 : show variables like '%query%'; #.修改配置信息 set global slow_query_log  = on;

查看不使用索引参数状态:

1 2 3 4

# 显示参数　　 show variables like '%log_queries_not_using_indexes'; # 开启状态 set global log_queries_not_using_indexes  = on;

 7.大数据量分页优化

1	select * from tb1 limit 3000000,10;

优化方案:

一. 简单粗暴，就是不允许查看这么靠后的数据，比如百度就是这样的

最多翻到72页就不让你翻了，这种方式就是从业务上解决；

二.在查询下一页时把上一页的行id作为参数传递给客户端程序，然后sql就改成了

1	select * from tb1 where id>3000000 limit 10;

这条语句执行也是在毫秒级完成的，id>300w其实就是让mysql直接跳到这里了，不用依次在扫描全面所有的行

如果你的table的主键id是自增的，并且中间没有删除和断点，那么还有一种方式，比如100页的10条数据

1	select * from tb1 where id>100*10 limit 10;

三.最后第三种方法：延迟关联

我们在来分析一下这条语句为什么慢，慢在哪里。

1	select id from tb1 limit 3000000,10;

玄机就处在这个 * 里面，这个表除了id主键肯定还有其他字段  比如 name  age  之类的，因为select  *  所以mysql在沿着id主键走的时候要回行拿数据，走一下拿一下数据；

如果把语句改成 

1	select id from tb1 limit 3000000,10;

你会发现时间缩短了一半；然后我们在拿id分别去取10条数据就行了；

语句就改成这样了：

1	select table.* from tb1 inner join ( select id from tb1 limit 3000000,10 ) as tmp on tmp.id=table.id;

这三种方法最先考虑第一种 其次第二种，第三种是别无选择