1、回表查询与索引覆盖
聚簇索引：主键索引一般是聚簇索引。聚簇索引的非叶子结点记录的是索引，叶子结点记录了对应的数据行（所有的字段）。
非聚簇索引：叶子结点记录的是普通索引列与主键的对应关系（不记录别的字段）。
如果走了普通索引，但想要 select 走的索引之外的字段，那么在根据非聚簇查完之后，就要根据行号去聚集索引进行回表
索引覆盖：条件和想要查的字段建立联合索引
select id,name,sex from user where name='shenjian';
将单列索引(name)升级为联合索引(name, sex)，即可避免回表。
https://blog.csdn.net/guorui_java/article/details/111302542
2、利用延迟关联（就是子查询？）或者子查询优化超多分页场景
在具有大量数据的场景下，使用传统的 LIMIT offset, N 分页查询可能会导致性能问题。原因是MySQL不会跳过前 offset 行，而是获取 offset + N 行数据，然后丢弃前 offset 行，返回 N 行。
SELECT id,name FROM user_info WHERE dep_id = 1 LIMIT 100000,20
因为子查询没有获取 a.Name ，所以可以比一次查询少【取然后丢弃前 offset 的 a.Name 数据】
SELECT a.id,a.NAME FROM user_info AS a, ( SELECT id FROM user_info WHERE dep_id = 1 LIMIT 100000,20 ) AS b WHERE a.id = b.id;
3、MySQL 索引访问方式
1、const： 通过主键或者【唯一】二级索引列与常数的等值比较来定位一条记录的访问方法
2、ref ： 搜索条件为二级索引列与常数进行等值比较，形成的扫描区间为单点扫描区间，采用二级索引来执行查询的访问方法。由于二级索引不会限制索引列的唯一性，所以通过二级索引查询的记录可能有多条，此时使用二级索引执行查询的代价就取决于扫描区间中的记录条数。
3、ref_or_null：搜索条件为二级索引列与常数进行等值比较，并且条件里还有 is null
4、range：使用索引执行查询时，对应的扫描区间为若干个单点扫描区间或者范围扫描区间的访问方法称为range（仅包含一个单点扫描区间的访问方法不能称为range访问方法，扫描区间为（负无穷，正无穷）的访问方法也不能称为range）
5、index：扫描【全部】二级索引记录的访问方法
6、all: 全表扫描
至少要达到 range 级别，要求是ref级别，如果可以是 const最好。
4、COUNT()
COUNT(*)、COUNT(1) 都表示返回满足条件的结果集的总行数，与是否为NULL无关
COUNT(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数
COUNT(DISTINCT col) 计算该列除 NULL之外的不重复行数。
COUNT(DISTINCT col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为 0
当某一列的值全是NULL时，COUNT(col)的返回结果为 0，但SUM(col)的返回结果为NULL，因此使用 SUM()时需注意NPE（空指针）问题。可用 IFNULL(SUM(field), 0)
5、强制使用 IS NULL() 判断 null 值
NULL与任何值的直接比较都为 NULL。
1） NULL<>NULL的返回结果是 NULL，而不是 false。
2） NULL=NULL的返回结果是 NULL，而不是 true。
3） NULL<>1的返回结果是 NULL，而不是 true。
6、EXISTS 和 IN 小表驱动大表
类似循环嵌套循环，外层循环是驱动，内层循环是被驱动
for(int i=5;.......) { for(int j=1000;......) {} }
如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。
- 1 LEFT JOIN 左连接,左边为驱动表,右边为被驱动表.
- 2 RIGHT JOIN 右连接,右边为驱动表,左边为被驱动表.
- 3 INNER JOIN 内连接, mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表.
- 4 IN 子查询是驱动表，外层查询是被驱动表
- 5 EXISTS 外层查询是驱动表，内层查询是被驱动表
可通过EXPLANIN查看SQL语句的执行计划,EXPLANIN分析的第一行的表即是驱动表
select count(1) from orders o where o.user_id in(select u.id from users u); select count(1) from orders o where exists (select 1 from users u where u.id = o.user_id);
1.in：先查询in后面的users表，然后再去orders中过滤，也就是先执行子查询，结果出来后，再遍历主查询，遍历主查询是根据user_id和id相等查询的。
2.exists：主查询是外层循环，先查询出orders，查询orders就是外层循环，然后会判断是不是存在order_id和 users表中的id相等，相等才保留数据，查询users表就是内层循环。
7、IN 中数据过多，导致索引失效
当 in 中是常量时，IN肯定会走索引，但是当IN的取值范围较大时会导致索引失效，走全表扫描。
那么 mysql 怎么评估 in 的取值范围呢？
参考：
https://article.itxueyuan.com/X0naM
两种评估影响行数的方式：
index dive：在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为index dive。
index statistics：如果IN子句对应的范围区间比较多，这样就不能采用index dive的方式去真正的访问二级索引idx_key1（因为那将耗费大量的时间），而是根据索引的统计数值进行估算，例如索引统计信息计算出每个等值影响100条数据，那么IN条件中包含5个等值则影响5*100条记录（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。
什么时候采用index dive的统计方式，什么时候采用index statistic的统计方式呢？取决于系统变量eq_range_index_dive_limit的值。
这个值在5.6版本默认是10，5.7版本默认是200
8、条件中对于同一个字段使用到OR的SQL语句必须改写成用IN()
MySQL 中OR的效率比IN低很多
WHERE id = 1 OR id = 2 OR id = 3; WHERE id IN ( 1, 2, 3 );
8、UNION 和 UNION ALL
UNION 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果，如果表数据量大的话可能会导致用磁盘进行排序。
UNION ALL操作只是简单的将两个结果合并后就返回，所以可能存在重复记录。 需要结合业务需求分析使用UNION ALL的可行性。
9、SELECT ... FOR UPDATE
对于以下场景：
有一套出货系统，在出货时必须确认还有货，如果第一步 select 出的 quantity > 0 ，会再执行 update 操作使库存 quantity 减一。那我们会有下面这样的 SQL
SELECT quantity FROM product WHERE product_id = 5; UPDATE product SET quantity = quantity - 1 WHERE product_id = 5;
这样 select 和 update 操作的原子性无法保障。即使事务的隔离级别是可重复读，那么 transactionA 第一条select施加的是读锁（共享锁），其它事务不可以对 product_id = 5 施加写锁（排他锁），但是可以施加读锁（共享锁），这样有可能出现下图这种情况，两个事务同时 select 到还有库存 1，接着先后去 update 使库存减一， transactionA 使库存减到0后就提交了事务，这时候 transactionB 再去更新，就会使库存减到 -1
| transactionA | transactionB | |
| transactionA begin | ||
| 共享锁 | select (quantity = 1) | transactionB begin |
| 共享锁 | select (quantity = 1) | |
| 排他锁 | update (quantity = 0) | |
| 共享锁 | transactionA commit | |
| 排他锁 | update (quantity = -1) | |
| transactionB commit |
SELECT ... FOR UPDATE 就是用来避免这种情况的发生！
Oracle 、 PostgreSQL 、 MySQL 都有 SELECT ... FOR UPDATE 語法，但要特別注意的是 MySQL 只有 Storage Engine 是 InnoDB 的情况下才可以使用。下面就用 MySQL 语法示范使用 SELECT ... FOR UPDATE ︰
START TRANSACTION; SELECT quantity FROM product WHERE product_id = 5 FOR UPDATE; UPDATE product SET quantity = quantity - 1 WHERE product_id = 5; COMMIT;
按照 MySQL 的官方文件，使用 SELECT ... FOR UPDATE 会在被读取的 row 加上 写锁-排它锁，而写锁-排它锁可以避免其他事务对这条记录施加 读锁-共享锁（读取这条数据），这样就不会有两个事务同时 select 到库存为 1 的记录。
不过 select ... for update 语句，相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
这是一种悲观锁的解决方案，每次 select 库存的时候就把记录锁住。
乐观锁解决方案， transactionA 和 transactionB 可以同时 select 到库存为 1 的记录，然后 transactionA 去更新这条记录到库存为0，transactionA 提交后，transactionB 再去更新时，添加判断 quantity > 0 , 或者限定 quantity = 之前 select 出来的 quantity 数量即可。
SELECT quantity FROM product WHERE product_id = 5; UPDATE product SET quantity = quantity - 1 WHERE product_id = 5 and quantity > 0 （或者限定 quantity = cur_quantity）;
4、MySQL 索引失效的场景
1、字段类型不同会造成隐式转换，导致索引失效。
2、in 里面的数量过多，造成索引失效
