MySQL

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有个阈值，决定了阈值之下使用索引查询，而超过阈值则退化，优化器选择索引下潜。

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

4、MySQL 索引失效的场景

　　1、字段类型不同会造成隐式转换，导致索引失效。