此笔记为尚硅谷MySQL高级篇部分内容

一、索引失效案例

二、关联查询优化

1、采用左外连接

2、采用内连接

3、join语句原理

1.驱动表和被驱动表

2.Simple Nested-Loop Join(简单嵌套循环连接)

3.Index Nested-Loop Join(索引嵌套循环连接)

4.Block Nested-Loop Join(块嵌套循环连接)

5.Join小结

6.Hash Join

4、小结

三、子查询优化

四、排序优化

filesort算法:双路排序和单路排序

五、GROUP BY优化

六、优化分页查询

七、优先考虑覆盖索引

1、什么是覆盖索引？

2、覆盖索引的利弊

八、如何给字符串添加索引

1、前缀索引

2、前缀索引对覆盖索引的影响

九、索引下推 ICP

1、索引条件下推使用前后

2、ICP的开启/关闭

3、 ICP的使用条件

十、普通索引 vs 唯一索引

1、查询过程

2、更新过程

3、change buffer的使用场景

十一、其它查询优化策略

1、EXISTS 和 IN 的区分

2、COUNT(*)与COUNT(具体字段)效率

3、关于SELECT(*)

4、LIMIT 1 对优化的影响

5、多使用COMMIT

十二、淘宝数据库，主键如何设计的？

1、自增ID的问题

2、业务字段做主键

3、淘宝的主键设计

4、推荐的主键设计

都有哪些维度可以进行数据库调优?简言之:

索引失效、没有充分利用到索引——索引建立

关联查询太多JOIN (设计缺陷或不得已的需求)——SQL优化

服务器调优及各个参数设置(缓冲、线程数等)———调整my.cnf。

数据过多――分库分表

关于数据库调优的知识点非常分散。不同的DBMS，不同的公司，不同的职位，不同的项目遇到的问题都不尽相同。这里我们分为三个章节进行细致讲解。

虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块。

物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。

逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

一、索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

1、全值匹配我最爱

意思是创建联合索引多个索引同时生效。

2、最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

结论:MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合）索引不会被使用。

拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3、主键插入顺序

4、计算、函数、类型转换(自动或手动)导致索引失效

5、类型转换导致索引失效

6、范围条件右边的列索引失效

7、不等于(!= 或者<>)索引失效

8、is null可以使用索引，is not null无法使用索引

结论: 最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串。

拓展: 同理，在查询中使用not like 也无法使用索引，导致全表扫描

9、like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引就不会起作用。只有“%"不在第一个位置，索引才会起作用。

拓展：Alibaba《Java开发手册》【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

10、OR 前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

因为OR的含义就是两个只要满足一个即可，因此只有一个条伴列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

11、数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行 转换 会造成索引失效。

12、练习及一般性建议

练习:假设:index(a,b,c)

一般性建议:

对于单列索引,l尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。。在选择组合

索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。

在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，书写SQL语句时，尽量避免造成索引失效的情况。

二、关联查询优化

1、采用左外连接

2、采用内连接

内连接 主被驱动表是由优化器决定的。优化器认为哪个成本比较小，就采用哪种作为驱动表。

如果两张表只有一个有索引，那有索引的表作为被驱动表。

原因：驱动表要全查出来。有没有索引你都得全查出来。

两个索引都存在的情况下，数据量大的作为被驱动表（小表驱动大表）

原因：驱动表要全部查出来，而大表可以通过索引加快查找

3、join语句原理

1.驱动表和被驱动表

2.Simple Nested-Loop Join(简单嵌套循环连接)

3.Index Nested-Loop Join(索引嵌套循环连接)

4.Block Nested-Loop Join(块嵌套循环连接)

5.Join小结

1、整体效率比较:INLJ > BNLJ > SNLJ

2、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是表行

3、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)

4、增大join buffer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)

5、减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多)

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

6.Hash Join

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

4、小结

保证被驱动表的JOIN字段已经创建了索引

需要JOIN 的字段，数据类型保持绝对一致。

LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。

能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

衍生表建不了索引

三、子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。

原因:

①执行子查询时MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

②子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。

③对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好

四、排序优化

问题: 在WHERE 条件字段上加索引但是为什么在ORDER BY字段上还要加索引呢?

回答:

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。

FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议:

SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列;如果不同就使用联合索引。

无法使用Index时，需要对FileSort方式进行调优。

filesort算法:双路排序和单路排序

五、GROUP BY优化

group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。.

group by先排序再分组，遵照索引建的最佳左前缀法则

当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置

where效率高于having，能写在where限定的条件就不要写在having中了

减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做

Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

六、优化分页查询

七、优先考虑覆盖索引

1、什么是覆盖索引？

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。

简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列

2、覆盖索引的利弊

八、如何给字符串添加索引

1、前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

2、前缀索引对覆盖索引的影响

结论：
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

九、索引下推 ICP

面试常问

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。

如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给MySQL服务器，由MySQL服务器评估WHERE后面的条件是否保留行。

启用ICP后，如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

好处: ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

但是，ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

1、索引条件下推使用前后

看个灵魂画图