索引优化与查询优化

1.哪些维度可以进行数据库调优

• 索引失效、没有充分利用到索引–》索引建立
• 关联查询太多JOIN (设计缺陷或不得已的需求)–》SQL优化
• 服务器调优及各个参数设置(缓冲、线程数等)–》调整my.cnf
• 数据过多–》分库分表

大方向上完全可以分成 物理查询优化和 逻辑查询优化 两大块

• 物理查询优化是通过 索引和 表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
• 逻辑查询优化就是通过 SQL 等价变换 提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

2.索引失效案例

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

• 使用索引可以 快速地定位 表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
• 如果查询时没有使用索引，查询语句就会 扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

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

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

2.1全值匹配我最爱

创建如下三个索引

CREATE INDEX idx_age ON student(age);

CREATE INDEX idx_age_classid ON student(age,classId);

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

执行以下查询时，可以看出默认使用idx_age_classid_name索引，导致了idx_age、idx_age_classid索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

2.2最佳左前缀法则

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

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

2.3类型转换索引失效

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

举例1：创建idx_name索引

CREATE INDEX idx_name ON student(name);

对比以下两条SQL执行效率，可以发现使用函数导致索引失效

举例2：创建idx_sno索引

CREATE INDEX idx_sno ON student(stuno);

对比以下两条SQL执行效率，可以发现使用计算导致索引失效

类型转换导致索引失效

结论：设计实体类属性时，一定要与数据库字段类型相对应。否则，就会出现类型转换的情况。

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

创建索引idx_age_classid_name

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

idx_age_classid_name索引还能正常使用吗？

• 不能，范围右边的列不能使用。比如<、<=、>、>=和between等；

• 如果这种SQL较多，应建立：

  CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);
  

• 将范围查询条件放置语句最后：

PS：应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。(创建的联合索引中，务必把范围涉及到的字段写在最后)|

2.5不等于索引失效

创建索引idx_name

CREATE INDEX idx_name ON student(NAME);

以下情况均不适用索引

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

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

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

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

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

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

查询语句使用OR关键字的情况:

创建索引idx_name、idx_age

CREATE INDEX idx_name ON student(name);

CREATE INDEX idx_age ON student(age);

未使用到索引：因为classid字段上没有索引，所以下述查询语句没有使用索引

使用索引：因为age字段和name字段上都有索引，所以查询中使用了索引，可以看到这里使用到了index_merge，简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果进行了合并，这样做的好处就是避免了全表扫描。

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

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

2.9一般性建议

• 对于单列索引，尽量选择针对当前query过滤性更好的索引；
• 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好；
• 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引；
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面；

3.关联查询优化

以下直接说明结论，具体原因可自行校验

• 对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
• 对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
• 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。“小表驱动大表”
• 对于外连接来说，优化器有可能会将外连接优化为内连接，既然是内连接了所以谁是驱动表都是有可能的

3.1JOIN的底层原理

简单嵌套循环连接（Simple Nested-Loop Join）

算法相当简单，从表A中取出一条数据1，遍历表B，将匹配到的数据放到result…以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断:

可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据10o0条计算，则A*B= 10万次。开销统计如下:

开销统计	SNLJ
外表扫描次数:	1（A表仅需扫描一次）
内表扫描次数:	A（B表与A表每条记录对比，因此扫描A次）
读取记录数:	A+B*A（A表每条记录读取+B表所有数据读取与A表对比）
JOIN比较次数:	A*B（A表每条数据与B表所有数据对比）
回表读取记录次数：	0（无需回表，对比成功取出数据即可）

索引嵌套循环连接 （Index Nested-Loop Join）

Index Nested-Loop Join其优化的思路主要是为了 减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数。

驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表(外表)。

开销统计	SNLJ	INLJ
外表扫描次数:	1	1（A表扫描一次）
内表扫描次数:	A	0（B表通过索引检索，无需扫描）
读取记录数:	A+B*A	A+B(match)（A表每条记录+B表索引匹配到的记录）
JOIN比较次数:	A*B	A*Index(Height)（A表每条记录与B表索引对比）
回表读取记录次数：	0	B(match) (if possible)（B表索引匹配到的数据需回表）

如果被驱动表加索引，效率是非常高的，但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高。

块嵌套循环连接 （Block Nested-Loop Join）

如果存在索引，那么会使用index的方式进行join，如果join的列没有索引，被驱动表要扫描的次数太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取一条与其匹配，匹配结束后清除内存，然后再从驱动表中加载一条记录，然后把被驱动表的记录在加载到内存匹配，这样周而复始，大大增加了10的次数。为了减少被驱动表的10次数，就出现了Block Nested-Loop Join的方式。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制)缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和ioinbuffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

注意：

这里缓存的不只是关联表的列，select 后面的列也会缓存起来。

在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的列。

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数:	1	1	1（扫描一次，依次读取数据）
内表扫描次数:	A	0	A*used_column_size/join_buffer _size+1（需要几个缓冲区，B表就需要扫描几次）
读取记录数:	A+B*A	A+B	A+B*(A*used_column_size/join_buffer_size)（A表每条数据+B表所有数据*需要读取次数）
JOIN比较次数:	A*B	A*Index(Height)	B*A（A表每条数据+B表每条数据）
回表读取记录次数：	0	B(match) (if possible)	0（无需回表，取数据即可）

参数设置：

block_nested_loop

通过 show variables like%optimizer_switch%'查看block_nested_loop 状态。默认是开启的

join_buffer_size

驱动表能不能一次加载完，要看join buffer能不能存所有的数据，默认情况下 join_buffer_size=256k

join_buffer_size的最大值在32位系统可以申请4G，而在64位操做系统下可以申请大于4G的Join Buffer空间 (64位Windows除外，其大值会被截断为4GB并发出警告)

Hash JOIN

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

• Nested Loop

  对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。

• Hash Join是做 大数据集连接 时的常用方式，优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行。

  • 这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。
  • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成 若千不同的分区，不能放入内存的部分0就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/0 的性能。
  • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join只能应用于等值连接(如WHEREA.COL1=B.COL2)，这是由Hash的特点决定的。

类别	Nested Loop	Hash Join
使用条件	任何条件	等值连接 (=)
相关资源	CPU、磁盘I/0	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。	当缺乏索引或者索引条件模糊时，Hash Join比Nested Loop有效。在数搪仓库环境下，如果表的纪录数多，效率高。
缺点	当索引丢失或者查询条件限制不够时，效率很低;当表的纪录数多时，效率低。	为建立哈希表，需要大量内存。第一次的结果返回较慢。

3.2JOIN小结

1. 整体效率比较: INLJ> BNLJ> SNLJ
2. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量) (小的度量单位指的是 表行数“每行大小)
3. 为被驱动表匹配的条件增加索(减少内层表的循环匹配次数)
4. 增大join bufer size的大小(一次缓存的数据越多，那么内层包的扫表次数就越少)
5. 减少驱动表不必要的字段查询 (字段越少，join buffer 所缓存的数据就越多)

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

• 保证被驱动表的JOIN字段已经创建了索引
• 需要JOIN 的字段，数据类型保持绝对一致。
• LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数
• INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表。选择相信MySQL优化策略
• 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
• 不建议使用子查询，建议将子查询SQL扳开结合程序多次查询，或使用JOIN 来代替子查询
• 衍生表建不了索引

4.子查询优化

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

1. 执行子查询时，MSOL需要为内层查询语句的查询结果 建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撒销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询；
2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引，所以查询性能会受到一定的影响。
3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

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

5.排序优化

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

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

• Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
• FileSort 排序则一般在 内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件/0 到磁盘进行排序的情况，效率较低。

优化建议：

1. SOL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FleSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用index完成ORDER BY排序。如果 WHERE和ORDER BY后面是相同的列就使用单索引列;如果不同就使用联合索引。
3. 无法使用Index时，需要对 FileSot 方式进行调优。

小结：

INDEX a_b_c(a,b,c)

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,C DESC	#需同时升序或同时降序

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a= const AND b = const ORDER BY C
- WHERE a = const ORDER BY b,C
- WHERE a= const AND b > const ORDER BY b,c

不能使用索引进行排序
- ORDER BY a ASC,b DESC，C DESC	/* 排序不一致 */
- WHERE g = const ORDER BY b,c	/*丢失a索引*/
- WHERE a= const ORDER BY C		/*丢失b索引*/
- WHERE a = const ORDER BY a,d	/*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

结论：

1. 两个索引同时存在，mysql自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的；
2. 当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

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

排序的字段若如果不在索引列上，则filesort会有两种算法: 双路排序和单路排序

双路排序 (慢)

• MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序 (快)

从磁盘读取查询需要的所有列，按照order by列在bufer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并目把随机10变成了顺序I0，但是它会使用更多的空间，因为它把每行都保存在内存中了。

结论及引申出的问题

• 由于单路是后出的，总体而言好过双路
• 但是用单路有问题
  • 在sort_buffer中，单路比多路要 多占用很多空间，因为单路是把所有字段都取出,所以有可能取出的数据的总大小超出了 sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并)，排完再取sort_buffer容量大小，再排…从而多次I/0。
  • 单路本来想省一次I/0操作，反而导致了大量的I/0操作，反而得不偿失。

优化策略

1.尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程(connection)的1M-8M之间调整。 MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%';

2.尝试提高 max_length_for_sort_data

• 提高这个参数，会增加用改进算法的概率

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

• 但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max length_for_sort_data，使用双路算法，否则使用单路算法1024-8192字节之间调整

3.Order by 时select* 是一个大忌。最好只Query需要的字段

• 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT BLOB 类型时，会用改进后的算法一一单路排序，否则用老算法一一多路排序。
• 两种算法的数据都有可能超出sort_bufer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次l/0，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

6.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、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否group by、则SQL会很慢。

7.分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10此时需要MySOL排序前2000010 记录，仅仅返回2000000-2000010 的记录，其他记录丢弃，查询排序的代价非常大

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

优化思路二

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询

8.优先考虑覆盖索引

什么是覆盖索引

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

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

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

覆盖索引的利弊

好处：

1. 避免Innodb表进行索引的二次查询 (回表)

   Innodb是以聚集索引的顺序来存储的，对于lnnodb来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过二次查询才能获取我们真实所需的数据。

   在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。

2. 可以把随机10变成顺序IO加快查询效率

   由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的 随机读取的IO转变成索引查找的 顺序IO。

   由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段.

弊端：

索引字段的维护 总是有代价的。因此，在建立几余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或
者称为业务数据架构师的工作。

9.索引下推（索引条件下推）

使用前后对比

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

• 如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估 WHERE 后面的条件是否保留行
• 启用ICP 后，如果部分 WHERE 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分WHERE 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。
  • 好处：ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数
  • 但是，ICP的加速效果取决于在存储引擎内通过 ICP筛选掉的数据的比例。

ICP的开启/关闭

默认情况下启用索引条件下推。可以通过设置系统变量 optimizer_switch 控制

#打开索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

当使用索引条件下推时，EXPLAIN语句输出结果中Extra 列内容显示为 Using index condition。

ICP的使用条件

不需要回表的话，ICP就用不上了。

1. 如果表访问的类型为 range、 ref、 eq_ref和ref_or_null 可以使用ICP
2. ICP可以用于InnoDB 和MyISAM 表，包括分区表InnoDB 和 MISAM表
3. 对于InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少I/0 操作
4. 当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP不会减少I/0
5. 相关子查询的条件不能使用ICP

10.其他查询优化策略

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

问:在MySQL 中统计数据表的行数，可以使用三种方式: SELECT COUNT(*)、SELECT COUNT(1)和SELECTCOUNT(具体字段)，使用这三者之间的查询效率是怎样的?

环节1: COUNT(*)和COUNT(1)都是对所有结果进行 COUNT，COUNT(*)和 COUNT(1)本质上并没有区别(二者执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的)。如果有 WHERE 子句，则是对所有符合筛选条件的数据行进行统计;如果没有 WHERE 子句，则是对数据表的数据行数进行统计。

环节2:如果是 MyISAM 存储引擎，统计数据表的行数只需要 O(1)的复杂度，这是为每张 MyISAM 的数据表都有一个meta 信息存储了 row_count 值，而一致性则由表级锁来保证。

如果是nnoDB 存储引擎，因为lnnoDB 支持事务，采用行级锁和MVCC机制，所以无法像 MyISAM 一样，维护一个row_count变量，因此需要采用扫描全表，进行循环+计数的方式来完成统计，复杂度为O(n)。

环节3：在InnoDB引擎中，如果采用COUNT(具体字段)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引(非聚族索引)。对于 COUNT(*)和 COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。

关于SELECT(*)

在表查询中，建议明确字段，不要使用*作为查询的字段列表，推荐使用SELECT<字段列表>查询。原因：

1. MySOL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间；
2. 无法使用覆盖索引；

LIMIT 1对优化的影响

针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上LIMIT 1的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上LIMIT1了。

多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为COMMIT 所释放的资源而减少。

COMMIT所释放的资源：

• 回滚段上用于恢复数据的信息
• 被程序语句获得的锁
• redo/undo log buffer 中的空间
• 管理上述3种资源中的内部花费

11.主键设计

自增ID的问题

自增ID做主键，简单易懂，几乎所有数据库都支持自增类型，只是实现上各自有所不同而已。自增ID除了简单其他都是缺点，总体来看存在以下几方面的问题：

1. 可靠性不高

   存在自增ID回溯的问题，这个问题直到最新版本的MySQL 8.0才修复。

2. 安全性不高

   对外暴露的接口可以非常容易猜测对应的信息。比如:/ser/1/这样的接口，可以非常容易猜测用户ID的值为多少总用户数量有多少，也可以非常容易地通过接口进行数据的爬取。

3. 性能差

   自增ID的性能较差，需要在数据库服务器端生成。

4. 交互多

   业务还需要额外执行一次类似 last_insert_id()的函数才能知道刚才插入的自增值，这需要多一次的网络交互。在海量并发的系统中，多1条SQL，就多一次性能上的开销。

5. 局部唯一性

   最重要的一点，自增ID是局部唯一，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器间都是唯的。对于目前分布式系统来说，这简直就是噩梦。

推荐的主键设计

非核心业务：对应表的主键自增ID，如告警、日志、监控等信息。

核心业务：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能

这里推荐最简单的一种主键设计: UUID。

UUID的特点

全局唯一，占用36字节，数据无序，插入性能差。

认识UUID

MySQL数据库的UUID组成如下所示：

UUID = 时间 + UUID版本(16字节) - 时钟序列 (4字节) - MAC地址(12字节)

我们以UUID值e0ea12d4-6473-11eb-943c-00155dbaa39d举例:

为什么UUID是全局唯一的?

在UUID中时间部分占用60位，存储的类似TIMESTAMP的时间戳，但表示的是从1582-10-15 00:00: 00.00到现在的100ns的计数。可以看到UUID存储的时间精度比TIMESTAMPE更高，时间维度发生重复的概率降低到1/100ns。

时钟序列是为了避免时钟被回拨导致产生时间重复的可能性。MAC地址用于全局唯一。

为什么UUID占用36个字节?

UUID根据字符串进行存储，设计时还带有无用"-"字符串，因此总共需要36个字节。

为什么UUID是随机无序的呢?

因为UUID的设计中，将时间低位放在最前面，而这部分的数据是一直在变化的，并且是无序。

改造UUID

若将时间高低位互换，则时间就是单调递增的了，也就变得单调递增了。MySQL 8.0可以更换时间低位和时间高位的存储方式，这样UUID就是有序的UUID了。

MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。

可以通过MvSOL8.0提供的uuid_to_bin函数实现上述功能，同样的，MySOL也提供了bin_to_uuid函数进行转化：

通过函数uuid_to_bin(@uuid.true)将UUID转化为有序UUID了。全局唯一+单调递增，这不就是我们想要的主键!