group by优化

情况一（未得到优化）

假设一个表中包含三个字段profession、age、status，并且此时我们没有建立他们的联合索引，根据profession字段进行分组操作：

结果如下：

结果如下：

情况二（得到优化）

为了解决上述全表扫描的问题、提高效率，我们针对于 profession ， age， status 创建一个联合索引

然后我们再次通过explain执行语句查看执行计划：

结果如下：

情况三（未得到优化）

上述情况二，是在创建联合索引后，只根据profession字段进行分组。假如不按照该字段，而是只按照age字段分组呢？

结果如下：

情况四（得到优化）

在情况三的基础上，假设我们依次根据profession和age进行分组，即满足最左前缀法则的情况，查看会有什么结果：

结果如下：

总结

• 在分组操作时，可以通过索引来提高效率。比如在上述案例过程中，未创建索引的时候进行分组，采用的查询方式是“ALL（全表扫描）”，效率低；在创建索引之后采用的查询方式是“index（根据索引）”，效率高。
• 分组操作时，索引的使用满足最左前缀法则。

limit优化

回顾limit语句

先来回顾一下limit语句：

limit语句可用于MySQL数据库的分页操作。我们在网站中看到的各种各样的分页条就是借助于数据库的分页操作。

limit的语法如下：

举例：假设有一个包含学生信息的表tb_student，我们要查询第1页学生数据, 每页展示10条记录的指令：

其中，0表示从0索引开始，往后查询10条记录；

紧接着再查询第二页学生数据：

其中，0表示从10索引开始，往后查询10条记录；

情况一（未得到优化）

假设此时有一个包含1000万条记录的表tb_sku。

假设查询第一页，即从0索引开始查，每页展示10条记录：

回 2000000 - 2000010 的记录，查询排序的代价非常大 。这种情况被称为深度分页。

情况二（得到优化）

limit分页操作常常通过创建覆盖索引加子查询进行优化。

覆盖索引

先来回顾一下什么是覆盖索引：

覆盖索引指的是查询过程中使用了索引（using index），并且需要返回的列在该索引中已经全部能够找到 （即不需要通过回表查询）

举个栗子：假设有一张表tb_demo，包含主键id，还有其他字段name，gender，createdate，我们已经事先为name创建了索引，即：

创建覆盖索引优化：此时因为name字段建立了索引，其包含的数据含有主键id，因此需要返回的列（id和name）在索引中已经全部能够找到，无需回表查询

没有覆盖索引的情况：此时gender并未事先创建索引，因此需要返回gender数据需要通过name索引获取主键，再通过主键获取gender的数据（回表查询），效率很低

limit优化

limit分页操作通过创建覆盖索引加子查询进行优化：

其核心思路是通过，需要返回的数据id在索引中能够找到，无需回表查询

count优化

count是MySQL数据库中的聚合函数，用来统计数量。对于count返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

count的局限性及优化思路

假设有一张表tb_sku，表中存放了1000万条记录，如果直接采用count函数统计数目，我们来查看效果：

MyISAM 引擎把表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM效率也很低。

这时候对count聚合函数的优化思路：借助内存级别数据库Redis来实现自己维护计数。

count几种用法的性能比较

• count(主键)：InnoDB 引擎会遍历整张表，读取每一行的主键值并返回给服务层。服务层拿到主键后，直接按行进行累加(因为主键不可能为null)
• count(字段)：
  • 字段没有not null 约束 : InnoDB 引擎会遍历整张表，读取每一行的主键值并返回给服务层，服务层判断是否为null，不为null，计数累加。
  • 字段有not null 约束：InnoDB 引擎会遍历整张表，读取每一行的主键值并返回给服务层，直接按行进行累加。
• count(1或某个数字)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
• count(*)：InnoDB引擎并不会把全部字段取出来，而是服务层对其做了专门优化，不取值，直接按行进行累加。

性能高低：count(*) ≈ count(1或某个数字) > count(主键) > count(字段)

update优化

注意事项：

• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁 ，并发性能降低

• 在执行update修改语句时，采用的是行级锁，不同行之间可以进行并发操作，前提是where语句中是以索引字段为条件

• 但是当where语句中以其他没有创建索引的字段为条件，行锁会升级为表锁，降低了update的并发性能

举例：

• 在客户端 1 里开启事务，用 update 语句更改 name 为 "Tom"的数据（字段name未创建索引），不加以提交；
• 在客户端 2 里开启事务，用 update 语句更改 id 不同的 数据的 name 值，此时语句就阻塞无法执行，因为此时表已被锁定。

原因：

• 因为 name 没有索引 ( 行锁只针对于有索引的主键 id )，所以此处就不是行锁了，是表锁，将整张表都锁住了，因此客户端 2 就算更

  新不同 id 的数据也会阻塞

• 若是针对 name 创建一个索引的话，客户端 2 的语句就能够执行成功