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 避免行锁升级为表锁 ？？？