Explain

MySQL查询优化器可以帮助我们自动对SQL的执行顺序进行优化，选取代价最低的方式进行查询。

编写 SQL 关注点

为什么不用select *

1. 不需要的列会增加数据传输时间和网络开销
2. 对于无用的大字段，如varchar、blob、text 会增加io操作
   长度超过728字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增 一 次io操作
3. 失去了使用覆盖索引的可能性，需要进行大量的回表操作，导致性能很低。

   like 关键字

   like语句一般业务的要求都是%关键字%（完全无法用索引），但要思考能否用关键字%的方式去替代产品的要求。
   编码规范：严禁使用左模糊 或者 全模糊，要搜索引擎来解决

字段类型不同会导致索引失效

select * from user_coll where pid = '1'
// 显示走索引
select * from user_coll where pid =  1
// 扫描索引树
pid字段有相应索引，且格式为varchar

count(*) 还是 count(id)

编码规范：不要使用count ( 列名 ) 或者 count( 常量 )来替代count()
count() 会统计NULL的行，count(列名)不会统计此列为NULL值的行

用union all 代替union

union 可以获取排重后的数据
union all ，关键字可以获取所有数据，包含重复的数据

反例：
(select * from user where id = 1)
union  // 会进行排重
(select * from user where id = 2)
排重的过程需要遍历、排序和比较，消耗CPU资源
//正例
(select * from user where id = 1)
union  all // 不排重
(select * from user where id = 2)
// 业务场景中不允许产生重复数据的，可以使用union

小表驱动大表

用小表的数据驱动大表的数据集
假如有order 和 user 两张表，order 有10000条数据，user 表有100条数据
业务场景：查所有有效用户的订单列表

select * from order 
where user_id in (select id from user where status = 1)
大表   in    小表
// 优先执行里面的子查询语句，然后在执行in外面的语句。如果in里面的数据量很少，那么查询速度很快
select * from order
where exists (select 1 from user where order.user_id = user.id and status = 1)
小表   exists 大表
// 优先执行exists左边的语句（主查询语句），然后把它作为条件，去跟右边语句匹配
匹配上，则查询出数据，匹配不上，数据就被过滤掉了
该业务场景： order 大表，user 是小表，因此用in 更好

批量操作

场景：需要插入一批数据

// 提供 批量插入数据的方法
orderMapper.insertBatch(list)
insert into  order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101)
// 限度为500 比较合适，多于500，则采取分批处理

多用limit

场景：查询某个用户的首个订单的时间

//反例：
select id,create_date from order where user_id = 123 order by create_date asc
List<Order> list = orderMapper.getOrderList();
Order order = list.get(0);
//浪费资源
select id,create_date from order where user_id = 123 order by create_date asc limit 1;
//只返回该用户下单时间最小的那条数据即可

延迟关联
先通过where 条件取出主键，在将该表与原数据表关联，通过主键id提取数据行，而不是通过原来的二级索引提取数据行

select a.* from table a,( select id from table where type = 2 and level = 9 
                         order by id asc limit 1000000,10)b where a.id = b.ib

深分页

索引(col_c)

select * from table where col_c = 1 limit 1000,10

MySQL 的 limit 不会传导到引擎层，只是在服务层sever进行过滤，查询数据时，
先由引擎层过滤一批数据(索引过滤)，然后服务层进行二次过滤(非索引过滤)

引擎层过滤后将获取的数据暂存，服务层一条一条数据获取，获取时引擎层回表获得完成数据
交给服务层，服务层判断数据是否满足条件，满足则继续判断是否满足limit限制的数据范围
符合且范围内的数据都查完了才返回

深分页，会导致大量的无效回表(前1000条进行了回表，实际上只需要1000-1010的数据)
优化方式是避免深分页带来的额外回表

select * from task_result LIMIT 20000000, 10;
// MySQl 并不是只查10条数据，而是查了200000010条，然后只返回10条，所以这条SQL会很慢

SELECT a.* FROM task_result a, (select id from task_result LIMIT 20000000, 10) b where a.id = b.id;
// 优化拆成子查询


// 反例
select id,name,age from user limit 1000000,20;

select id,name,age from user where id > 100000 limit 20;

// 使用between 优化分页
select id,name,age from user where id between 100000 and 1000020

明确知道数据表情况，某个业务的where条件筛选下只有一条数据时，用limit 限制，否则会造成全表扫描

in 值太多

批量查询接口，通常会使用 in 关键字过滤出数据，想通过一些指定的一些id，批量查询出用户信息

select id,name from category where id in(1,2,3...1000000)

select id,name from category where id in (1,2,3..100)

//更多的是对业务代码加限制

public List<Category> getCategory(List<Long> ids){

    if(colltionUtils.isEmpty(ids)){
      return null;
  }
    if(ids.size() > 500){
       throw new BusinessException("一次最多只能查询500条记录“)
  }
    return mapper.getCategoryList(ids)
}

增量查询

通过远程接口查询数据，然后同步到另外一个数据库

//反例：
select  *  from user;
直接获取所有数据然后同步过去，但是性能很差

select * from user where id > #{lastId} and create_time >= #{lastCreateTime} limit 100

按id 和 时间升序，每次只同步一批数据这批数据只有100条，每次同步完成后，保存100条中最大的id和时间
给同步下一批数据的时候用。

用连接查询代替子查询

MySQl 一般从两张以上的表查询出数据的话，一般有两种实现方式
子查询 
// MySQL执行子查询时，需要创建临时表，查询完毕后，需要在删除这些临时表，消耗额外性能
改成连接查询
select o.* from order o inner join user u on o.user_id = u.id
where u.status = 1

Join 的表不宜过多

join的表不宜过多，不宜超过3个
如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。
并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。

Inner join 关联，MySQL会自动选择两张表中的小表去驱动大表，所以性能上不会有太大问题
left join ，会默认选择左表去驱动右边的表，若是左表的数据量很大，就会出现性能问题

a left join b where a.cid = 100

a 表为 驱动表，b表为被驱动表
如果 不存在 a的where 条件，意味着a表每一行都要去匹配，则a表要全表扫描；b表会走索引
因为执行过程是， 先通过where 条件筛选a表数据在去匹配b表数据

提升group by的效率

// 反例
select user_id,user_name from order group by user_id having user_id <= 200
缺陷是先分组后过滤用户id，但是分组是一个相对耗时的操作，为什么不先缩小数据范围后，再分组呢

//正例：
select user_id,user_name from order where user_id <= 200 group by user_id
where 条件在分组前，就把多余的数据过滤掉了，这样分组的效率就高

尽量避免使用否定形式

<>、!=、NOT IN
所以以下SQL会导致全表扫描

select *  from table where col_l <> 100

//优化

select *  from table where col_l > 100 or col_l < 100

select *  from table where c <> 'aaa'
//改成c > 'aaa' or c < 'aaa'

适当使用前缀索引

索引越长占用的磁盘空间就越大，相同数据页中，能放下的索引值就越少，意味着搜索索引需要的查询时间就越长，可以适当使用前缀索引，减少空间占用和提高查询效率。
类似于邮箱 “@xxx.com”,类似这种后面几位为固定值的字段就非常适合定义前缀索引，使用前缀索引，定义好长度，既节省空间，又不用额外增加太多的查询成本。
缺点是前缀索引没有办法使用覆盖索引，也无法做order by 和 group by 操作

1. 最左匹配截断

index(col_a,col_b) 组合索引是从左往右匹配，如果要使用col_b字段走索引，查询条件必须要携 带col_a字段；如果要col_b作为排序字段走索引，只要保证组合索引中的在col_b 前面的字段包 含在过滤条件中或排序条件中即可

select * from table order by col_a,col_b 
那么两者都可以走索引

但如果col_b作为过滤条件，那么 col_b前面的字段都应该在查询条件中

select * from table where col_b = 1 order by col_a 
那么col_a  和 col_b 都不走索引，因为 where 执行在 order by 前

1. 隐式转换 ```sql 字段类型 col_a (varchar) col_b (DATETIME)

索引： index1(col_a) index2(col_b)

SQL: select from table where col_a = 1 select from table where col_b = 1603296000000

失效原因 col_a 是字符类型，使用了数字类型进行查询。 col_b 是datetime类型，针对datetime 、date、time 类型，MySQl 增删查改都基于字符串形式 日期去处理，否在MySLQ就需要进行额外的转换。 MySQl 底层存储的是数字类型，但不是存储的时间戳，而是同意将外部传入的字符串进行转换， 如将date类型通过将”2021 - 12 - 01” 字符串转数字 20211201 形式存储。

3. in + order by 导致排序失效

index(col_a,col_b)
```sql
select * from table where col_a in(1,2) order by col_b
// 会导致索引失效，col_b 不走索引

SQL 建议优化为只查询id，然后在根据id去查询对应的数据

范围查询阻断联合索引

index(col_a,col_b)

select * from table where col_a > '2021 - 12 - 01' and col_b = 10
调整为 index(col_b,col_a)

OR 导致索引失效,部分有索引，部分无索引

index(col_a,col_b)

select * from table where col_a = 1 or col_b = ''

or 会导致索引失效，可以将col_a 和 col_b 分别建立索引，利用MySQL 的 index merge(索引合并)进行优化
本质上是MySQL分别基于两个字段索引去查询对应的数据，再将数据进行合并

1. 不等于，不包含（只用到ICP）

索引index（col_a,col_b,col_c）
SQL

select *  from table where col a = 1 and  col_b not in (1,2)
select * from table where col_a = 1 and col_b != 1

可以利用索引下推，直接先判断是否满足 筛选条件，满足才返回。

1. 选择性过低，直接走全表

选择性过低的索引，通过二级索引查询后还得回表，开销很大，会导致索引失效。如果MySQl判 断走该索引的开销，比全表扫描还大，那么会放弃索 引，直接全表扫描。根据 表大小、IO块大 小、行数等信息评估。

1. asc 和 desc 混用

   索引：index(col_a,col_b,col_c)
   select  * from table where ol_a = 1 order by col_b desc,col_c asc
   desc 和asc混用时会导致索引失效，不建议混用。
   

记录慢查询日志

slow_query_log ：这个参数设置为on，可以捕获执行时间超过一定数值的sql语句
slow_query_log_file ： 记录日志的文件名
long_query_time ： 当sql语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短

建立索引

不要包含选择性过低字段

该字段只能过滤掉少部分的数据,比如逻辑删除字

select *  from my_table where col_a=1 and col_b = 1，
index(col_a,col_b) 
col_b 是逻辑删除的字段，包含 0 和 1，数据库中很少量部分是逻辑删除的，
业务中一般只查未删除的，那么这种情况可以把 col_b从组合索引中去掉

选择性高的字段前置或者单独建立索引

假设选择性col_c>col_b>col_a，抛开业务本身需要，组合索引建立的顺序尽可能建为index(col_c,col_b,col_a)
组合索引底层的存储是先按照第一个进行排序，当第一个字段相同再按照第二个字段排序。因此
选择性高的字段放在前面，相对而言会减少IO次数。
如果col_c 的字段选择性特别高，用col_c过滤后只剩下极少数的数据，那么考虑单独建立索引

覆盖索引

Select sum(col_c) from table where col_a = 1 and col_b = 1
index(col_a,col_b)

如果 col_a 和 col_b 过滤后还有大量数据，那么建议建一个index(col_a,col_b,col_c)索引，避免大量回表去查col_c的数据去求和

索引数量

阿里巴巴开发规定，单标索引尽量控制在5个以内，并且单个索引字段不超过5个。
在执行insert 、 update 、delete 时，需要更新操作B+树索引，耗费性能。
并发量高德系统，务必遵守单表索引数量不要超过5个。

选择合理的字段类型

char 固定字符串类型，该类型的字段存储空间是固定的，会浪费存储空间。
适合存储类似用户手机号等，固定长度的数据
varchar 表示变长字符串类型，该类型会根据实际数据的长度来调整，不会浪费存储空间
适合存储类似企业名称这种不固定长度的字符串类型。

遵循的原则：
能用数字类型，不用字符串，因为字符的处理往往要比数字慢。
尽可能使用小的类型，如：用bit存布尔值，用tinyint存枚举值
金额用decimal，避免进度丢失。但是公司是用int存储，把金额转化为分存储。