MySQL23 - SQL优化

SQL优化

插入数据

• 批量插入

  • 插入多条数据时，执行批量插入，但批量插入也不建议插入超过1k条

  • 几百万数据时，多次批量插入

  • INSERT INTO table_name VALUES (..) (..) (..) ...;
    

• 手动事务提交

  • 默认每一条DML语句都会开启\提交一次事务

  • 手动开启事务，将多次插入进行一次提交

  • START TRANSACTION;
    INSERT ..
    INSERT ..
    ...
    COMMIT;
    

• 主键顺序插入

• load

  • 将本地磁盘文件的数据直接加载到数据库中，常用于万级、百万级数据

主键优化

• 在InnoDB存储引擎中，表数据是根据主键顺序组织存放的，称为索引组织表（IOT index organized tabel)
• 尽量减少页分裂、增多页合并 -- 尽量按序插入
• 应尽量降低主键的长度，因为二级索引的叶子节点存放主键的值
• 插入数据时，应尽量选择主键顺序插入，如选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用UUID做主键，或其他自然主键，如身份证号，会乱序插入，且长度过长

ORDER BY 排序优化

• Using filesort：通过表的所有或全表扫描，读取满足条件的数据行，然后在排序缓存区 sort buffer 中完成排序，所有不是通过索引直接返回排序结果的排序都叫做 FileSort 排序

• Using index：通过有序所有顺序扫描直接返回有序数据，不需要额外排序，操作效率高

• 尽量使用 Using index => 对有索引的字段进行与其索引相同顺序的排序

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则

• 尽量使用覆盖索引

• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则 （ASC\DESC）

• 如果不可避免的出现FileSort，大量数据排序时，可以适当增大排序缓冲区大小（默认为256k）

GROUP BY 分组优化

• 可以通过索引提高效率
• 分组操作时，索引的使用也是满足最左前缀法则

LIMIT 分页优化

• 当执行 LIMIT 200_0000, 10时，此时 MySQL 需要排序前 200_0010条记录，仅仅返回 200_0000 ~ 200_0010 的记录，其他记录丢弃，查询排序代价大，性价比低
• 可以先查询数据的主键，在与查询到的主键数据进行多表联查，返回完整记录

COUNT 求和优化

• 在MyISAM引擎中，表的总行数被存储在磁盘中，因此执行 COUNT(*) 的时候会直接返回这个数，效率很高

• InnoDB引擎执行 COUNT(*) 时，需要吧数据一行行得读取，再进行累计计数

• 优化思路：自己计数存储数据量 。。。

• COUNT 的主要用法：COUNT( * ) , COUNT( 主键 ) , COUNT( 字段 ) , COUNT( 1 )

• **COUNT( 主键 ) **

  • InnoDB 引擎会遍历整张表，将 主键id 值都取出来，返回给服务层，直接按行进行累加，主键不为 null ，因此不用判断值是否为 null

• COUNT( 字段 )

  • 如果没有 NOT NULL 约束，InnoDB 引擎会遍历整张表，将每一行的字段值都取出来，返回给服务层，服务层判断是否为 NULL，不为 NULL，计数累加
  • 有 NOT NULL 约束，InnoDB 引擎会遍历整张表，将每一行的字段值都取出来，返回给服务层，直接按行进行累加

• COUNT ( 1 )

  • InnoDB 引擎会遍历整张表，但不取值，服务层对于返回的每一行，放一个数字 1 进去，直接按行进行累加

• **COUNT( * ) **

  • InnoDB 引擎并不会把全部字段取出，而是专门做了优化，不取值，服务层直接按行累加

• **效率：COUNT( * ) = COUNT ( 1 ) ** > 其他

UPDATE 更新优化

• Kakfa 避免行锁升级为表锁 ？？？