• 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;

    这三种方法最先考虑第一种 其次第二种，第三种是别无选择
  • 相关阅读:
    推箱子（简易版）
    [LeetCode] Word Ladder II
    [LeetCode] Path Sum
    [LeetCode] Word Ladder
    DFS & BFS
    [LeetCode] Surrounded Regions
    [LeetCode] Add Binary
    [LeetCode] Plus One
    [LeetCode] Single Number II
    [LeetCode] Single Number
  • 原文地址：https://www.cnblogs.com/panfb/p/8039796.html
Copyright © 2020-2023  润新知