MySQL 创建千万数据测试

mysql admin 62℃ 0评论

— 1.使用索引查询 ,2的N次方  折半查找

— 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 (32) NOT NULL DEFAULT ”,
age INT (11),
email VARCHAR (64) NOT NULL DEFAULT ”
) 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;

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

转载请注明:朋克网 » MySQL 创建千万数据测试

喜欢 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址