数据库分组排序和优化策略

数据库分组排序和优化策略

1、分组排序

查询每个部门的最高平均工资

select deptno,avg(sal) from emp group by deptno order by avg(sal) limit 0,1;

查询到平均工资大于2000的部门的名称，并且显示部门的平均工资：

select [department_name],avg([salary]) from [tablename] group by [department_name] having avg([salary]) >2000

limit : LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持该语法： LIMIT # OFFSET #. mysql group by limit (根据某一分组，取固定条数) 实现。

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15   

2、mysql 数据量和并发量

数据表的单表最大数据量：

• Mysql 单表的最优最大数量的一个重要因素是磁盘空间，除了硬件之外，索引也是重要影响因素。 Mysql 的主要存储引擎 InnoDB 采用 B+树结构索引。

• 那么 B+树索引是如何影响 Mysql 单表数据量的呢？Mysql 的 B+树索引存储在磁盘上，Mysql 每次读取磁盘 Page 的大小是 16KB，为了保证每次查询的效率，需要保证每次查询访问磁盘的次数，一般设计为 2-3 次磁盘访问，再多性能将严重不足。Mysql B+树索引的每个节点需要存储一个指针（8Byte）和一个键值（8Byte）。因此计算16KB/(8B+8B)=1K， 16KB 可以存储 1K 个节点，3 次磁盘访问(即 B+树 3 的深度)可以存储 1K *1K *1K 即 10 亿数据。如果查询依赖非主键索引，那么还涉及二级索引。这样数据量将更小。

数据库的单库并发量： MySQL: ERROR 1040: Too many connections”的情况。造成这种情况的一种原因是访问量过高，MySQL服务器抗不住，这个时候就要考虑增加从服务器分散读压力；另一种原因就是MySQL配置文件中max_connections值过小。mysql的最大连接数默认是100, 最大可以达到16384。（16384=2的14次方）

• 查看mysql的最大连接数：show variables like ‘%max_connections%’;

业务层优化的针对连接数过大的一些优化。

​ 一、过度依赖数据库： 首先，数据库并不是万能，不要把各位运算和数据都存放进去，尽量减小数据压力；其次，合理使用缓存，例如Memcache和Redis，我们都知道二八定律，它同样适用于互联网，百分之八十的请求最终落在百分之二十的业务上，所以能不查询数据库的时候尽量不要去找数据库。

​ 二、谨慎使用长链接： 很多时候是不需要使用长连接的，特别在一些网站中（比如PHP中mysql_pconnect），短连接能解决的问题就用短连接，长连接有可能导致大量连接得不到释放无法使用。

​ 三、优化慢SQL： 慢SQL可以说是影响网站性能的毒瘤，SQL越快，连接占用的时间就越短。MySQL 提供了慢查询日志。这个日志会记录所有执行时间超过 long_query_time（默认是 10s）的 SQL 及相关的信息。慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）。直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000

• 索引失效导致慢查询

3、mysql 索引失效的情况

• 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
• LIKE操作中，like查询是以’%‘开头的不会使用索引，’%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
• 在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
• 字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=‘99999’。
• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
• 联合索引中，where中索引列违背最左匹配原则，一定会导致索引失效

4、mysql的优化策略

1、缓存。缓存是解决这类问题的一把手。它既可以加快整个系统（并非数据库系统，使用缓存的时候并没有去访问数据库）的访问速度，也可以减少数据库负载的压力。而缓存一般都是在查询中使用，我们并不希望每一次的查询都要去访问数据库。那么我们的缓存做到哪里呢？一般情况下版的系统都会存在服务层这一结构层次，而数据访问层一般都只是对于数据库的增删改查的接口的定义，所以我们的缓存就需要在服务层进行。而mybatis中的一级缓存，通过判断查询条件是否要访问数据库，查询条件与某一次相同，则直接返回缓存中的数据，查询条件不同则需要访问数据库，并且将结果放到缓存中。

2、当只需要一条数据时使用LIMIT 1.我们作为开发者，是能够知道我们需要的数据的条数的，若已经知道结果只有一条的时候，一定要使用limit 1 ，这样一来，MySQL在查询到一条数据之后，会立即停止搜索，这会带来性能上的提升。

3、避免select * ，取之所需。公司里的一些同事，无论查询什么都是直接select *，然后再从结果中取想要的字段。这样做的话，平白无故的给MySQL带来了不必要的负担，因为从数据库中读出越多的数据，查询就会变得越慢。所以，以后看到select * 的时候，想一下是否可以在这里进行一些优化。

4、为每张表设置一个id作为其主键。这个id最好是一个int类型的，推荐使用unsigned，并将其设置为自动增加auto_increment。之前就出现过一个同事将varchar的字段作为主键的情况， 然后在数据量较大的时候，数据库这个环节速度变得不是很友好，所以尽量不要使用varchar来当主键，它会使得性能出现下降。而且在某些情况下，id这个主键字段是非常重要的。

5、使用enum而不是varchar。实际上，enum保存的是tinyint类型，但其显示为字符串。用这个字段来作一些选项列表就变得很合适了。比如你有一个字段，比如“性别”、“状态”或“所属部门”等，你知道这些字段的值是固定且有限的，那么可以考虑使用enum。对于性别这个字段，一般分为两种，有可能还有保密这种情况，我们可以使用数字1、2、3来分别表示这三张情况，而对于这些数字含义的区分则是业务层的事情了。我们需要将一些繁琐的需要计算的步骤全部放到业务层（或者说是服务层），因为系统的瓶颈在数据库，我们不能将过多的计算过程压到数据库上面去。数据库存储的数据应该尽量简单，但是，我们会在业务层结合具体的业务，对这些简单的数据进行分析。

6、尽可能的使用not null。除非你有一个很特别的原因要去使用null值，你应该总让你的字段保持为not null。

7、选择正确的存储引擎。myisam适合一些需要大量查询的应用。但其对于大量写操作并不是很好。因为它使用到的是表级锁，所以在你更新的时候，整张表都会被锁起来，试想一下，当你在更新某一行数据的时候，导致其他的行都无法被访问，这会不会 很难受呢。另外，myisam对于select count(*)这类操作的计算时很快的。而至于innodb而言，对于一些小的应用，它会比myisam还慢。它支持的是行级锁，于是写操作较多的时候，它会更加优秀。它还支持一些更高级的应用，比如说：事务。

8、提高磁盘读写速度 RAID0 尤其是在使用 EC2 这样的虚拟磁盘 (EBS) 的时候，使用软 RAID0 很重要

5、线上bug定位：

现象是：平台web定期查看出现数据空白的现象。通过后台k8s查看发现框架一pod周期性重启。首先查看k8s的日志发现是livenessprobe存活检测策略定期执行时发现业务容器的暴露的http端口无返回（容器启动后10s进行探测，如果1s内容器没有给出回应则记作探测失败。每次间隔10s进行一次探测，在探测连续失败3次后重启容器。）。再通过业务日志查看为什么http端口服务不通。

• 一个process协程负责往一个数据channel通道发送数据，process会有数据库的sql查找逻辑。

• 另外一个接收协程负责从channel通道接收数据并且伴有超时退出机制。

发现process()在超时时间没有返回，则返回nil。但是当超时发生的时候，由于已经没有receiver了，数据无法发送会被block 住，导致http无法及时返回数据。

发现是process协程处理sql超时以后分析慢sql超时原因：

• 发现多表关联查找时exist和in的用法问题。

exist 与in查询： 一般使用的场景是 IN适合主表大子表小，EXIST适合主表小子表大

A表（主查询，即：外表）和B表（子查询，即：内表）关联查询；

当A表和B表大小相当时，用in和exists查询效率差不多；

当A表（主查询）比B表（子查询）大的时，即子查询表小的时候用In效率高；

A表（主查询）比B表（子查询）小的时，即子查询表大的时候用exists效率高;

• in确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，只执行一次然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

  SELECT * FROM A WHERE id IN (SELECT id FROM B);
  等价于：1、SELECT id FROM B ----->先执行in中的查询
  
         2、SELECT *  FROM A  WHERE A.id = B.id
  
  以上in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，
  然后检查A表中查询出的id在缓存中是否存在，
  如果存在则将A的查询数据加入到结果集中，
  直到遍历完A表中所有的结果集为止。
  

• exist指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。 先执行外部查询语句，然后在执行子查询，子查询中它每次都会去执行数据库的查询，执行次数等于外查询的数据数量, 多次执行子查询语句。exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；具体sql语句如下：

  SELECT
      `user`.*
  FROM
      `user`
  WHERE
      EXISTS (
          SELECT
              `order`.user_id
          FROM
             `order`
         WHERE
             `user`.id = `order`.user_id
     )