欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。

文摘  MySQL创建千万数据的测试

MySQL 网络 955 0评论

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

-- 1.使用索引查询

-- 2.不使用索引查

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


一、创建数据和索引


1.创建索引测试表

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.创建存储过程,插入数据

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)

CALL insert_data_p(10000000);

111.png


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

ALTER TABLE `big_data` ENGINE=INNODB;


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

select * from big_data where id = 8950000

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

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


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

CREATE INDEX index_name ON big_data (name);


二、组合索引的生效原则


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


三、正确使用索引


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

即使建立索引,索引也不会生效:


四、需要注意


1.避免使用select *

2.count(1)或count(列) 代替 count(*)

3. 创建表时尽量时 char 代替 varchar

4. 表的字段顺序固定长度的字段优先

5. 组合索引代替多个单列索引(经常使用多个条件查询时)

6. 使用连接(JOIN)来代替子查询(Sub-Queries)

7. 不要有超过5个以上的表连接(JOIN)

8. 优先执行那些能够大量减少结果的连接。

9. 连表时注意条件类型需一致

10.索引散列值不适合建索引,例:性别不适合


五、时间 (执行计划)


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

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

333.png


六、慢日志查询


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


慢查询日志参数

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慢日志信息

#.查询慢日志配置信息 :

show variables like '%query%';

#.修改配置信息

set global slow_query_log  = on;


查看不使用索引参数状态

# 显示参数  

show variables like '%log_queries_not_using_indexes';

# 开启状态

set global log_queries_not_using_indexes  = on;


七、大数据量分页优化


select * from tb1 limit 3000000,10;


优化方案:

1)简单粗暴,就是不允许查看这么靠后的数据,比如百度就是这样的

222.png

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


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

select * from tb1 where id>3000000 limit 10;

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

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

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


3)最后第三种方法:延迟关联

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

select id from tb1 limit 3000000,10;

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


如果把语句改成 :

select id from tb1 limit 3000000,10;

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


语句就改成这样了:

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

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


原文地址:https://www.cnblogs.com/zhuchuanbo/p/8038727.html

转载请注明: ITTXX.CN--分享互联网 » MySQL创建千万数据的测试

最后更新:2019-03-13 11:13:18

赞 (1) or 分享 ()
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽