一、分页查询优化

image.png

1.1、查询过程

  1. -- 分页sql
  2. select * from employees limit 10000, 10
  3. 查询过程：
  4.     从employees表中读取 0-10010条记录，然后抛弃前10000条，取后面10条，因此如果查询一张大表靠后的数据话，
  5.   效率非常低

1.2、优化技巧

1.2.1、主键连续底层分页优化

-- 自增主键的分页查询优化
select * from employees limit 90000,5;  -- 全表扫描不走索引
select * from employees where id > 90000 limit 5; -- 会走索引
注意：这种优化方式要求，主键必须是连续递增的，中间不能又跳行，如果删除90000以下的一条数据，结果将不正确

1.2.2、非主键字段分页排序优化

select * from employees ORDER BY name limit 90000,5;  -- name字段有索引

image.png
注意：name字段并没有走索引，原因：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。
优化：让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，

-- SQL改写如下
select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.id = ed.id;

image.png
注意：通过执行计划发现：优化后的sql使用了索引排序

二、join关联查询优化

2.1、mysql的关联算法

mysql表关联常见的有两种算法

  • nested-loop join算法

  • block nested-loop join算法

    2.2、Nested-Loop Join(NLJ) - 嵌套循环连接算法

    一次一行循环的从第一张表（驱动表）中读取数据，再这行数据中取到关联字段，然后根据关联字段去另一张表里查询相应数据，然后将两次查询的结果合并
    image.png
    注意：表t1、t2的关联字段a有索引

  • 从执行计划看，t2未走索引进行了全表扫描，所以t2为驱动表(mysql优化器的选择，所以再inner join前的表不一定是驱动表)

  • 当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，

    当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

  • 使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算

    法是 NLJ。

    上述sql执行的流程

    a、从t2表中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）
    b、从第一步的数据中，取出关联字段a，到表t1中查找
    c、将上述两步查找处出来的数据合并，返回给客户端
    d、重复上述三步
    所以整个过程mysql的扫描行数为 2 * t2表的数据量，如果关联字段没有索引，使用NLG的效率将比较低，扫描的行数会变成 t1表的数据量 * t2表的数据量（磁盘扫描）__，所以mysql会选择BNL算法

    2.3、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

    把驱动表的数据全部读入到join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟join_buffer的数据做对比
    image.png
    注意：表t1、t2的b字段没有索引，Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

    上述sql执行过程

    a、把t2表的所有数据放入到join_buffer中
    b、把t1表中的每一行数据取出来，跟join_buffer中的数据做对比
    c、返回满足条件的数据

  • 整个过程对t1表、t2表分别做了一次全表扫描，因此扫描的总行数为 t1表数量（10000） + t2表数量（100），并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。

  • join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 10000 = 100万次，这个是*磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

2.4、关联sql优化

  • 关联字段加索引

  • 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去

    mysql优化器自己判断的时间

  • join表不超过3张

straight_join: 功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。

  • straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指

    定了表的执行顺序）

  • 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因

    为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

小表的定义：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

三、in和exists优化

原则：小表驱动大表，即小的数据集驱动大的数据级。

  • in：当B表的数据集小于A表的数据集时，in优于exists

    select * from A where id in (select id from B)
-- 等价于
for(select id from B){
  select * from A where A.id = B.id
}

  • exists：当A表的数据集小于B表的数据集时，exists优于in

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists (select 1 from B where B.id = A.id)
-- 等价于
for(select * from A){
    select * from B where B.id = A.id
}
  • EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
  • EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

  • EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

    四、count(*)优化

    image.png
    image.png
    以上四条sql的执行计划是完全一样的，说明这四个sql执行效率应该差不多
    字段有索引：count()≈count(1)>count(字段)>count(主键 id)
    字段无索引：count(    )≈count(1)>count(主键 id)>count(字段)

    常见优化方法

  • 如果可以使用myisam存储引擎，mysisam存储引擎的总数会被记录到磁盘上，查询不需要计算

  • show table status，值不精确有误差

    image.png

  • 用redis维护总行数

  • 增加数据库表，维护总行数

    五、阿里巴巴mysql规范

    5.1、mysql数据类型选择

    在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：

  • 确定合适的大类型：数字、字符串、时间、二进制；

  • 确定具体的类型：有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。
image.png
image.png
优化建议：

  • 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
    1. 建议使用TINYINT代替ENUM、BITENUM、SET。
    1. 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用

INT。

    1. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
    1. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
    1. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

image.png

  • MySQL能存储的最小时间粒度为秒。
  • 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
  • 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
  • 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
  • TIMESTAMP是UTC时间戳，与时区相关。
  • DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
  • 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般

会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。

  • 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它 — 待商榷吧

image.png
image.png

  • 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。

  • CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些

    要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响        到计算的准确性和完整性。

  • 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。

  • BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
  • BLOB和TEXT都不能有默认值。

注意：INT显示宽度

CREATE TABLE `user`(
 `id` TINYINT(2) UNSIGNED
);
这里的长度并非是TINYINT类型存储的最大长度，而是显示的最大长度。
这里表示user表的id字段的类型是TINYINT，可以存储的最大数值是255。所以，在存储数据时，如果存入值小于等于
255，如200，虽然超过2位，但是没有超出TINYINT类型长度，所以可以正常保存；如果存入值大于255，如500，那么
MySQL会自动保存为TINYINT类型的最大值255。
在查询数据时，不管查询结果为何值，都按实际输出。这里TINYINT(2)中2的作用就是，当需要在查询结果前填充0时，
命令中加上ZEROFILL就可以实现，如：
`id` TINYINT(2) UNSIGNED ZEROFILL

这样，查询结果如果是5，那输出就是05。如果指定TINYINT(5)，那输出就是00005，其实实际存储的值还是5，而且存
储的数据不会超过255，只是MySQL输出数据时在前面填充了0。
换句话说，在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有
用，让查询结果前填充0。