MySQL-进阶篇 ( SQL 优化 )

SQL 优化

插入数据 index

批量插入

• 一般情况下，都用批量插入写法

• 一个 index 插入多条： Insert into 某表名 values(1, 'xx'),(2, 'yy'),(3, 'xy');

  • 每一个 insert 都要与数据库连接连接、传输，所以不使用一个 insert 插入一条的语句
  • 虽此方式可以插入多条，但也不建议一个 insert 插入过千条

手动提交事务

• 数据超千条、达万条的话，使用此方式

• 在多条 insert 前开启事务，插入语句都编写完成后再统一提交：

  start transaction;

  insert ...;

  insert ...;

  ......;

  commit;

主键插入

• 不管数据量，在插入时都推荐使用顺序插入

• 主键乱序插入：3 5 9 1 6 2

• 主键顺序插入：1 2 3 5 6 9

  • 顺序性能更高

大批量插入数据

• 如果一次性需要插入大批量数据，使用 insert 语句插入性能较低，此时可以使用 MySQL 数据库提供的 load 指令进行插入：

  -- 客户端连接服务端时，加上参数：--local-infile
  mysql --local-infile -u root -p 
  -- 使用load加载的话就要加上：--local-infile为当前客户端连接服务端时需要加载本地文件。-u指定用户，-p指定密码
  
  -- 查看开关是否开启
  select @@local_infile;
  
  -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
  set global local_infile = 1;
  
  -- 执行load指令将准备好的数据加载到表结构中
  load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
  -- 这里设置加载的文件路径为 '/root/sql1.log'，加载到 tb_user 表中，字段间 ',' 分隔开，每条数据换行符 '\n' 隔开
  

  • 通过 load 指令可以一次性把本地文件数据全部加载到数据库
  • 本地文件非 SQL 语句编写，而是以一定规则写的每条数据内容

主键优化

• 在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的 ( 聚集索引 )，这种存储方式的表称为索引组织表 ( index organized table IOT )
  • 在聚集索引中，数据都是存放在页中

页分裂

• 页可以为空，也可以填充一半，也可以填充 100%。每个页包含了 2-N 行数据 ( 如果一行数据就相当于一个链表了，而且数据较大超出阈值就会导致行溢出 )，根据主键排列
• 主键顺序插入的话，就是先在第一个页中填写数据，如果第一个页满了就写第二个页中依此类推
• 主键乱序插入的话，就不是依此类推的往后插入，因为叶子节点是有序的，所以就产生了页分裂现象，举例说明：
  • 假设一页存 5 数据，现已有 page 1 ( 1、5、9、23、47 ) ——> page 2 ( 55、67、89、100、101 )
  • 此时乱序要插入 50 的话，因为本是要插入到第一个数据页 47 之后，而现有页已满，所以会开辟一个新的数据页 3，然后把第一个数据页的 50% 处的后面部分移动放到新的数据页中，然后将数据 50 填到新页的 47 之后，然后改链将此页放到页 1 后，页 2 前
  • 即：page 1 ( 1、5、9 ) ——> page 3 ( 23、47、50 ) ——> page 2 ( 55、67、89、100、101 )
  • 此现象即为页分裂现象

页合并

• 在删除一行记录时，实际上记录并没有被物理删除，只是记录被标记 ( flaged ) 为删除并且它的空间变得允许被其他记录声明使用
• 当页中被标记为删除的记录达到 MERGE_THRESHOLD ( 默认为页的 50% )，InnoDB 会开始寻找最靠近的页 ( 前或后 ) 看看是否可以将两个页合并以优化空间使用
  • MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

主键设计原则

• 满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
• 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改

order by 优化

Using filesort：

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

• 没有 age 的索引，或者联合失效没用到
• explain select xx ...... order by xx; 可见执行计划的 Extra 列值为 Using filesort

Using index：

通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

• 若是创建一个 ( age, phone ) 的联合索引，再查询根据 age, phone 升序 ( 或降序 )
• explain 看执行计划的 Extra 列值为 Backward index scan; Using index
  • Backward index scan：反向扫描索引
• 若是查询时排序语句为 ...... order by phone, age; 则 Extra 列值为 Using index; Using filesort
  • 注意：where 后不管先后顺序，存在即可。但 order by 后顺序必须和索引创建时一致才符合最左前缀法则
• 若是查询时排序语句为 ...... order by age asc, phone desc; 则 Extra 列值也为 Using index; Using filesort，因为默认是都升序，所以在 age 时直接用，age 相同的要 phone 另拿出降序，所以 filesort
• 想要一个升序一个降序、还想用到索引的话，就要在创建索引时指定好：create index idx_user_age_pho_ad on tb_user(age asc, phone desc);
  • show 索引看到此联合索引的 Collation 列 A 为顺序，D 为降序
• 一旦建立了一升一降的索引，在查询时不管降序的是 age 还是 phone，只要两不相同就是用到索引了的 Using index
  • 此时索引的叶子节点就是 age 升序，age 相同的再 phone 降序填入，所以只要 age 与 phone 不同序就可以用扫描和反向扫描解决
• 如果查询时 select * ...... 就不会用到索引，Extra 列值为 Using filesort
  • 没有用到覆盖索引

优化注意：

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则
• 如果不可避免出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size ( 默认 256k )
  • show variables like 'sort_buffer_size'; 可得 value 值为 262144 ( 默认 262144 / 1024 = 256k )
  • 不更改的话，如果超出了默认就会在磁盘文件中进行排序，这样性能会较低

group by 优化

未创建索引时：

• explain select profession, count(*) from tb_user group by profession; 见执行计划中 key 是空 NULL，Extra 列为 Using temporary：使用临时表 ( 性能低 )

创建索引后：

• 创建索引：...... (profession, age, status);

• explain select profession, count(*) from tb_user group by profession; 见执行计划中 key 用到了索引，Extra 列为 Using index

• 执行语句 explain select age, count(*) from tb_user group by age; 的话，虽用到了索引，但是 Extra 却为 Using index; Using temporary ( 性能并不高 )

  • 显示使用了索引，却还是临时表 temporary，是因为只有 group by 后的 age 不满足最左前缀法则
  • 若是先 profession 筛选了的话，执行语句 explain select age, count(*) from tb_user where profession = '软件工程' group by age; 就会发现 Extra 就是 Using index，因为 where 后有 profession 和 age 符合最左前缀法则
    • 即 where 后面对于最左前缀法则的要求是存在即可，不管顺序位置

• 执行语句 explain select profession, age, count(*) from tb_user group by profession, age; 执行计划中 key 用到了索引，Extra 列为 Using index

  • 此时就不用考虑 select * 的可能了，因为没有意义

优化

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

limit 优化

• 大数据情况下，limit x, y 中，y 相同的情况下 x 越大耗费时间越长：例如 limit 2000000, 10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大

• 若是先查询主键 id 而非全部记录的话：select id from tb_user order by id limit 2000000, 10;，此时就获取了要返回信息的主键 id

  • 因为 SQL 语句 in 的后面不能有 limit 所以不能用 where id in ( 查 id 的 limit 语句 )
  • 但是可以把 limit 获取的 10 条 id 当作一个表，即多表联查：select * from tb_user u, (select id from tb_user order by id limit 2000000, 10) i where u.id = i.id;
  • 这个方法执行查询要比起初方法节省不少时间

• 优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

count 优化

一般的 count 语句

• 大数据情况下，查询总数比较费时，实际这是取决于 InnoDB 的处理方式
  • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高 ( 有条件的话也是比较慢的 )
  • InnoDB 引擎就比较麻烦，执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累积计数
  • 优化思路：自己计数

count 用法

• count() 是一个聚集函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加一，否则不加，最后返回累计值
• 用法有：count(*)、count( 主键 )、count( 字段 )、count(1)
  • count( 字段 ) 的话就是字段非 null 就加一
    • 是 null 就不计数
  • count(1)：每返回一条记录就放一个 1 进去，在服务层对数据进行累加，是 1 非 null 就相加

优化

• count ( 主键 )：InnoDB 引擎会遍历整张表，把每行的主键 id 值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加 ( 主键不可能为空 null 所以不用判断 )
• count ( 字段 )：根据有无约束，有两种情况
  • 没有 not null 约束的话，InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为 null，不为 null，计数累加
  • 若有 not null 约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
• count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
  • count(0) 就是每一行放一个 0 进去，count(-1) 就是每一行放一个 -1 进去，只要不是 null 都会进行累加
• count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序

• count( 字段 ) < count( 主键 ) < count(1) < count(*)

• 所以尽量使用 count(*)

update 优化

需要规避注意的问题

• 若是加以行锁，在客户端 1 里 begin; 开启事务，然后 update 语句更改 id 为 1 的数据，不加以提交，在客户端 2 里也同上 begin; 开启事务，然后紧接着 update 语句更改 id 为 4 的数据，因为是行锁，所以此处语句执行成功，不用等待 1 的事务提交
• 同上条件，在客户端 1 里 begin; 开启事务，并 update 语句更改 name 为 xx 的数据，不加以提交，然后在客户端 2 里开启事务后，同先前用 update 语句更改 id 不同的 数据的 name 值，此时语句就阻塞无法执行了，除非客户端 1 commit; 释放了，客户端 2 的更新语句才能够执行成功
  • 因为客户端 1 update 语句中的 name 没有索引 ( 行锁只针对于有索引的主键 id )，所以此处就不是行锁了，是表锁，将整张表都锁住了，所以客户端 2 就算更新不同 id 的数据也会阻塞
  • 若是针对 name 创建一个索引的话，客户端 2 的语句就能够执行成功了
• InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则行锁就会升级为表锁，并发性能就会降低

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset
• Mysql时间常用函数
• 【Leetcode1949. 坚定的友谊】使用MySQL在无向图中寻找{"CompleteTripartite", {1, 1, 3}}这个pattern

update MySQL order group countupdate mysql order group having mysql group order 别名mysql group order order外层group count错误 结果group mysql limit order 字段 备注mysql order mysql order limit by 索引 情况mysql order mysql group by