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.组合索引的生效原则: 

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

 3.正确使用索引

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

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'

　

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;

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

posted on 2017-12-14 20:31  潘红伟  阅读(1546)  评论(1)  编辑  收藏  举报

导航