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 优化
- 在更新数据时，需根据索引列作为条件更新——索引列，更新触发行锁、非索引列触发表锁
- 行锁升级为表锁降低并发性能