深入浅出的讲解mysql优化 关于gruop by，where和order by ，以及一些mysql的探讨

mysql版本 8.3.0 ,mysql存贮引擎 InnoDB

一。innodb的存储特点和搜索算法

mysql的优化还得从mysql存储引擎的存储结构讲起

mysql的innodb存储引擎是b+树，他是把数据和索引分别存在两个文件中的，其中索引的存储是一个有序的变种二叉树形式（高度平衡的多分枝二叉树）。

因为是索引是有序的，所以在用where进行检索的时候其实可以简单的理解为变种的快速查找（二分查找）。

当定位到索引的某条记录后，在该记录上会关联聚族索引（主键），一个聚族索引会指向数据在磁盘上的存贮位置。（机械硬盘）磁盘每秒中都在高速转动，磁头在磁盘转动时会循环读取磁盘的信息。每转一圈都需要去除无用数据。一几年的时候偶尔会关注下机械硬盘转速，有5000转/秒或者7200转/s的。

看个sql

select * from student where xuehao = 'jsj2014010001' 

假设xuehao是索引，那么mysql是先到xuehao的索引上面查找数据，其中学号为 jsj2014010001的索引数据会关联这个表的主键（假设id为主键自增数字类型，这个值为1），在通过主键在数据存贮的文件中定位到数据主键id。这一过程就是回表查询

我们在看这个sql

select id,xuehao from student where xuehao = 'jsj2014010001' ；

还是刚刚的假设，通过xuehao 的索引查询找数据，查询xuehao = 'jsj2014010001'的数据的xuehao（索引）。那么mysql发现此时不需要关联到数据存贮的文件中就可以直接查找到数据，该条sql就会快于前一条sql 。虽然这第二个sql看起来有些傻，但是我们常用的文章查询差不多就是这么个意思，比如给文章标题一个索引，然后在搜索的时候 让title = 输入的内容，用户在搜索结果页在点击查询到的结果跳转到详情

有兴趣的，可以弄个10万条数据的表循环查询10万次看看其速度。

二。group by ，where条件和order by的连用

当然比较在深入一些的就会进行组合一些个联合索引。

比如有个广告推广的访问记录表，用户一次访问一次就会在log中有一次访问记录，我们会根据用户的访问记录和广告投放方进行结算，其中需要根据广告推广人，推广渠道，推广项目，推广子项目id进行分组统计，一个用户在一天之内最多有5次访问会生效产生费用。

访问日志表字段如下，该表按天分表，一天的访问记录就会生成一张表

  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

  `dycode` int unsigned NOT NULL DEFAULT '0' COMMENT '广告分成人的推广渠道的id',

  `ucode` int unsigned NOT NULL DEFAULT '0' COMMENT '广告分成人的id',

  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '访问用户id',

  `project_id` int unsigned NOT NULL COMMENT '推广项目id',

  `g_id` int unsigned NOT NULL COMMENT '推广子项目ID',

  `createtime` int DEFAULT NULL COMMENT '创建时间',

  `ip` int unsigned NOT NULL DEFAULT '0' COMMENT '用户IP地址',

  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态:1=有效,2=无效,3=未处理',

  PRIMARY KEY (`id`) USING BTREE,

如果正常统计的sql如下

select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by project_id,ucode,dycode,g_id ,user_id 

假设表中有300多万条数据, 我用limit进行了限制，下面是mysql的执行结果，取第10001条数据，用了7秒，如果取道最后面的200万条以后m=,取1万条慢的可能会需要10秒以上，这显然是不能接受的

mysql> select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by user_id  ,project_id,ucode,dycode,g_id  limit 10000,1 \G

*************************** 1. row ***************************

       num: 1

   user_id: 107993679

project_id: 40

     ucode: 2657309

    dycode: 1322678

      g_id: 3626231

1 row in set (7.76 sec)

 

 

为了方便查看性能，查看下mysql的执行计划

mysql> explain select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by  user_id,project_id,ucode,dycode,g_id  limit 10000,1 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: dy_views_log_20220721

   partitions: NULL

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 2875736

     filtered: 10.00

        Extra: Using where; Using temporary

1 row in set, 1 warning (0.00 sec)

看到这个执行结果，里面没有用到索引，但是用到了where，在进行groupby的时候会用到临时表

根据innodb的存储结构，我进行了第一次添加索引，添加了一个做组合索引，能够方便数据库快速定位到数据，而不用全表查询

alter table dy_views_log_20220721 add index idx_status_userid_projectid_ucode_dycode_gid (status,user_id,project_id,ucode,dycode,g_id)

这个时候因为我的where 条件有status =1 ，因此我将status 也列入到联合索引之中，且放在了首位，在mysql解析时可以直接可以命中该索引。

gruop by时也依然在索引上面了，注意：因为索引是有序存储的，是为了方便变种的二分查找。组合索引也是要把字段的值按照一定的规则拼装存储，因此也是有序的。字段的顺序也要体现在gruop by中，否则就会造成索引失效。

再次执行sql和执行计划

mysql> select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by  user_id,project_id,ucode,dycode,g_id  limit 10000,1 \G

*************************** 1. row ***************************

       num: 2

   user_id: 2819845

project_id: 65

     ucode: 563357

    dycode: 504894

      g_id: 300678

1 row in set (0.01 sec)

sql仅用了0.01秒，提升了几百的效率

 

mysql> explain select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by  user_id,project_id,ucode,dycode,g_id  limit 10000,1 \G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: dy_views_log_20220721

   partitions: NULL

         type: ref

possible_keys: idx_status_userid_projectid_ucode_dycode_gid

          key: idx_status_userid_projectid_ucode_dycode_gid

      key_len: 1

          ref: const

         rows: 1437868

     filtered: 100.00

        Extra: Using index

1 row in set, 1 warning (0.00 sec)

而sql的执行计划可以看到，使了用到索引，没有使用临时表。额外说句，因为所有的数据都在组合索引上，因此也不需要在进行回表查询叶子节点的数据，因此导致了查询速率的提升。

好了现在第一步的sql优化已经完成了，当我门运行程序的时候，第一批数据可以使用limit 10000很快的获取到了，但是我门知道limit这个是有有性能限制的。

select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by  user_id,project_id,ucode,dycode,g_id  limit 10000

假如gruop by后依然有200万条数据，那么取道第200万条的时候，mysql是查询了200万条数据，然后将前面的199万条舍弃掉，将剩下的数据返回，并不是直接从199万条开始取到200万条，因此也有性能消耗。我们常用的解决方法是添加where条件，因为where条件是毫秒级的。

比如文章列表，我门按id生序排列，在下一页时，将上一页的最后一条id传给sql用于where条件即可，这样我们就可以继续从第0行开始取了,如下

where id > 上一页最后一条id limit 0, pagesize

这个需求的分页也可以参考上面的列子，但是id显然是不使用的，第一因为取不到，第二就算取道了，也不能确信这个id就是分组数据中全部数据的最后一条数据的id，你只能确定是该分组的最后一条数据所在的那一组数据的最后一条。

gruop by本身就按其后面的字段去重了，因此可以直接使用，order by 一下这些字段即可

select count(*) as num, user_id, project_id, ucode, dycode, g_id from dy_views_log_20220721 where status = 1  group by  user_id,project_id,ucode,dycode,g_id order by user_id,project_id,ucode,dycode,g_id  limit 10000

一定要注意order by 也是有序的，如果前后顺序调换，就会在此用到临时表

 

第二页写法需要注意where条件，这也是一个简单的最左侧小算法

第一个字段> 上一条数据 ，后面字段可以任意 ，如果第一个字段 = 上一条数据 ，第二个字段 必须 > 上一条数据  一次类推。

如 where frist_filed > last['frist_filed'] or (frist_filed = last['frist_filed'] and filed2>last[filed2])

这其实就是一个简单的比数大小的算法，

比如比较121和112谁大？

先比较百位，百位大的那个数就大，

如果百位相等，就比较后面的，后面线比较十位，十位大的数就大，如果十位相同

就最后比较个位

 

就写到了，感兴趣的可以自己继续照这个思路

最后explain 下这个得到的sql，除了上面的还多了一个use where的评价，这是一个正向的，是加快查询速度的