010 学习笔记--SQL优化

• 插入数据
• 主键优化
• order by 优化
• group by 优化
• limit 优化
• count 优化
• update 优化
• ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
• 插入数据
  • 原始操作：insert into tablename values('','')；insert into tablename values('','')......
  • 批量插入：不建议超过1000条，insert into tablename values(),(),()
  • 手动提交事务 ：start transaction  insert into tablename values('','')；insert into tablename values('','')...... commit;
  • 主键顺序插入：顺序插入性能高于乱序插入
  • 大批量数据插入：如果一次性需要插入大批量数据，使用insert语句插入性能较低，可使用Mysql数据库提供的 load 指令进行插入。操作如下所示：

    • 

• 主键优化
  • 数据组织方式
    • 在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为--索引组织表——index organized table,IOT
  • 页分裂
    • 页可以为空，也可以填充一般，也可以填充100%。每个也包含了 2-N 行数据(如果一行数据过大，会行溢出)，根据主键排列。
  • 页合并
    • 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且其空间变得允许被其他记录声明使用。
    • 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
    • 注：MERGE_THRESHOLD，合并页的阈值，可以自己设置，在创建表或创建索引时指定
  • 主键设计原则
    • 满足业务需求的情况下，尽量降低主键的长度
    • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
    • 尽量不要使用UUID做主键或者是其他自然主键，如身份证号(UUID--无序，且长度长)
    • 业务操作是，避免对主键的修改
• order by 优化
  • Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后再排序缓冲区 sort buffer 中完成排序操作，
    • 所有不是通过索引直接返回排序结果的排序都叫filesort 排序
  • Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index,不需要额外排序，操作效率高。
  • eg：

  • EXPLAIN select id,NAME,age,gender,phone from tb_users order by age;
    EXPLAIN select id,NAME from tb_users use INDEX(idx_user_name) order by name;

  • 

    

  • 创建索引优化：
  • create index idx_user_age_phone on tb_users(age asc,phone desc)——联合索引，指定排序规则
  • explain select id,age,phone from tb_users order by age asc,phone desc

    

  • 总结：
    • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
    • 尽量使用覆盖索引
    • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ACS\DESC)
    • 如果不可避免的出现filesort，大数据排序时，可以适当增大排序缓冲区大小 sort_buffer_size （默认256k）
• group by 优化
  • 在分组操作时，可以通过索引来提高效率
  • 分组操作是，索引的使用也是满足最左前缀法则的
• limit 优化
  • 在使用limit进行分页查询时，一个常见的问题——越往后的数据，查询代价越大。例如 limit 2000000,10——此时需要mysql排序前2000010的数据，并返回后10条数据，其他记录丢弃，查询排序的代价非常大。
  • 优化方案：覆盖索引 + 子查询(一般分页查询时，通过创建覆盖索引能够比较好的提高性能，可以通过覆盖索引加子查询形式进行优化)
  • SELECT s.* from tb_users s ,(select id from tb_users ORDER BY id LIMIT 2,2) a where a.id=s.id;
• count 优化
  • count 的几种用法
    • count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就是 1 ，否则不加，最后返回累计值(count(name)——如果name==NULL,则计数不加)。
    • count(*)、count(主键)、count(字段)、count(1)
      • count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为NULL)
      • count(字段)：
        • 字段没有not null约束，InnoDB引擎会遍历整张表把每一行的字段值取出来，返回给服务层，服务层判断是否为null，不为null,计数累加。
        • 有not null 约束，InnoDB引擎会遍历整张表把每一行的字段值取出来，返回给服务层，直接进行累加。
      • count(*)：InnoDB引擎并不会吧全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
      • count(1)：InnoDB引擎遍历整张表，但不取值，服务层对于返回的每一行，放一个数字 1  进去，直接按行进行累加。——count(数字)
  • 按效率排序：count(*) > count(1) > count(主键) > count(字段)
• update 优化
  • 在更新数据时，需根据索引列作为条件更新——索引列，更新触发行锁、 非索引列触发表锁
  • 行锁升级为表锁降低并发性能