索引优化与查询优化

数据库调优

1、优化方面

(1)物理查询优化:通过索引、表连接方式等优化

(2)逻辑查询优化:通过 SQL 等价变换,提升查询效率

2、例

(1)索引失效、没有充分利用到索引:索引建立

(2)关联查询太多 JOIN:SQL优化

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

(4)数据过多:分库分表

 

索引失效的情况(以下)

1、联合索引不满足最佳左前缀法则

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

(2)MySQL 可以为多个字段创建索引,一个索引可以包括 16 个字段

(3)对于多列索引,过滤条件若使用索引,必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用

(4)如果查询条件中,没有使用这些字段中第 1 个字段时,多列(联合)索引不会被使用

(5)索引文件具有 B 树的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引

(6)建议:在创建联合索引时,WHERE 使用最频繁的字段放在组合索引的最左侧

2、使用 SELECT *(全值匹配)

(1)增加查询分析器解析成本;增减字段容易与 resultMap 配置不一致;无用字段增加网络 消耗,尤其是 text 类型字段

(2)建议:禁止使用 SELECT *,某些情况下触发覆盖索引;在联合索引下,尽量使用明确的查询列,尽可能触发覆盖索引

3、索引列参与运算、函数

(1)不仅索引失效,还导致全表扫描,增加数据库的计算负担

(2)建议:先在内存中,进行计算好预期值,或在 SQL 语句条件的右侧进行参数值的计算

4、错误使用 LIKE

(1)模糊查询时,占位符 % 位于条件首部

(2)索引本身相当于目录,从左到右逐个排序,而条件的左侧使用占位符,导致无法按照正常的目录进行匹配

5、类型自动 / 手动转换

(1)参数类型与字段类型不匹配,导致类型发生隐式转换,索引失效

(2)添加 '' 或 "",可将数值转换为字符串

(3)特例:如果字段类型为 int 类型,而查询条件添加 '' 或 "",则 Mysql 将参数转化为 int 类型

6、错误使用 OR

(1)OR 其中一个字段没有创建索引,则导致整个查询语句索引失效

(2)OR 两边为 > 和 < 范围查询时,索引失效

7、两列做比较

(1)在比较表达式中,左右两方都创建索引,索引失效

8、不等于比较

(1)使用 <>或 !=作为条件查询,可能不使用索引

(2)与结果集与总体的占比有关:当查询结果集,占比较小时,使用索引,占比较大时,不使用索引

9、主键插入顺序

(1)使用 InnoDB 的表,在没有显示的创建索引时,表中的数据实际存储在聚簇索引的叶子节点,而记录又存储在数据页中,数据页和记录按照记录主键值,从小到大的顺序进行排序

(2)如果插入记录的主键值是依次增大,则每插满一个数据页,就换到下一个数据页继续插入;如果插入的主键值忽小忽大,在某个数据页存储的记录已满情况下,造成页面分裂和记录移位

(3)建议:使主键具有 AUTO_INCREMENT,让存储引擎为表生成主键,而不是手动插入,这样的主键占用空间小,顺序写入,减少页分裂、记录唯一

10、IS NOT NULL

(1)一般情况下, 条件 IS NOT NULL 得到太多数据,导致回表的次数太多,成本太高,总的成本可能超过全表扫描

(2)同理,在查询中使用 NOT LIKE,导致全表扫描

(3)建议:设计数据表时,就将字段设置为 NOT NULL 约束,且设置默认值

11、参数不同的范围查询

(1)范围查询,比如:>、< 、>=、<=、IN 等条件,同样的查询语句,只是查询的参数值不同,导致索引失效

(2)根据场景不同,参数的临界值有所不同

(3)因为索引指向的数据在磁盘中并不是顺序的,所以会产生大量的随机 I/O,不如直接全表扫描,因为全表扫描是顺序 I/O

(4)若索引扫描的行记录数超过全表 10% ~ 30% 时,优化器可能会放弃使用索引,自动变成全表扫描,某些场景下即便强制 SQL 语句使用索引,同样失效

(5)优化器发现全表扫描比走索引效率更高,因此放弃索引,目的是减少数量庞大的随机 I/O

(6)建议:建立索引时,尽量不要建立在数据比较集中的字段上

12、字符集转换

(1)不同字符集进行比较前,需要进行转换,造成索引失效

(2)建议:数据库和表的字符集统一使用 utf8mb4,兼容性更好

 

更好地创建和使用索引

1、只为用于搜索、排序或分组的列创建索引

2、考虑索引列中不重复值的个数

(1)通过二级索引 + 回表的方式执行查询时,某个扫描区间中包含的二级索引记录数量越多,就会导致回表操作的代价越大

(2)在为某个列创建索引时,需要考虑该列中不重复值的个数占全部记录条数的比例

(3)如果比例太低,则说明该列包含过多重复值,那么在通过二级索引 + 回表的方式执行查询时,就有可能执行太多次回表操作

3、索引列的类型所占用的存储空间尽量小

(1)因为数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以存放更多的记录,磁盘 IO 带来的性能损耗也就越小(一次页面 IO 可以将更多的记录加载到内存中),读写效率也就越高

(2)这个建议对于表的主键来说更加适用,因为不仅聚族索引会存储主键值,其他所有的二级
索引的节点都会存储一份记录的主键值。如果主键使用更小的数据类型,也就意味着能节省更
多的存储空间

(3)当列中不重复值的个数在总记录条数中的占比很大时,才为列建立索引

4、只为索引列前缀创建索引,以减小索引占用的存储空间

(1)只将字符串的前几个字符存放到索引中,即在二级索引的记录中只保留字符串的前几个字符

(2)只为列前缀建立索引的方式,无法支持使用索引进行排序的需求,只能使用全表扫描 + 文件排序的方式来执行

5、使用覆盖索引进行查询,以避免因表操作带来性能损耗 

(1)索引中已经包含所有需要读取的列的查询方式

(2)排序操作优先使用覆盖索引进行查询

6、让索引列以列名的形式在搜索条件中单独出现

7、让插入记录的主键值依次递增(AUTO INCREMENT),避免页分裂的性能损耗

8、删除表中冗余和重复索引

 

一般性优化

1、对于单列索引,尽量选择针对当前查询过滤性更好的索引

2、在选择组合索引时

(1)当前查询中过滤性最好的字段,在索引字段顺序中,位置越靠前越好

(2)组合索引的字段,与 WHERE 中的字段,尽可能交集

(3)如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后

 

连接查询优化

1、外连接

(1)在驱动表、被驱动表的连接字段上都不建立索引:两表都是全表扫描,访问类型为 ALL,查询效率最低

(2)在驱动表的连接字段上建立索引,触发覆盖索引扫描,访问类型为 index,扫描行数不变,但查询效率得到优化

(3)在被驱动表连接字段上建索引,访问类型为 ref,扫描行数减少,查询效率得到优化

2、内连接

(1)查询优化器可以决定哪个表为驱动表,哪个表作为被驱动表

(2)如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表

(3)在两个表的连接条件都存在索引的情况下,或都不存在索引的情况下,选择小表作为驱动表

 

驱动表、被驱动表

1、当连接查询没有 WHERE 时

(1)左连接查询时,前面的表是驱动表,后面的表是被驱动表,右连接查询时相反

(2)内连接查询时,哪张表的数据较少,哪张表为驱动表

2、当连接查询有 WHERE 时,带 WHERE 条件的表是驱动表,否则是被驱动表

 

JOIN 原理

1、本质:各个表之间数据的循环匹配

2、MySQL 5.5 版本之前:MySQL 只支持一种表间关联方式,索引嵌套循环(Index Nested Loop Join),如果关联表的数据量很大,则关联的执行时间会非常长

2、MySQL 5.5 版本之后,MySQL 引入 BNLJ 算法来优化嵌套执行

 

简单嵌套循环连接

1、Simple Nested-Loop Join

2、算法简单,从驱动表中取出一条数据 1,遍历表 B,将匹配到的数据放到结果集,以此类推,驱动表中的每一条记录,与被驱动表的记录进行判断

 

索引嵌套循环连接

1、Index Nested-Loop Join

2、目的:减少内层表数据的匹配次数

3、要求:被驱动表上必须有索引

4、优化思路:通过外层表匹配条件,直接与内层表索引进行匹配,避免和内层表的每条记录去进行比较

5、若没有索引,算法退化到 SNLJ

 

块嵌套循环连接

1、Block Nested-Loop Join

2、优化思路

(1)不再以条为单位获取驱动表的数据,而是以块为单位获取

(2)引入 join buffer,将驱动表 join 相关的部分数据列(大小受 join buffer 限制)缓存到 join buffer 中

(3)全表扫描被驱动表,被驱动表的每一条记录,在内存中,一次性和 join buffer 中的所有驱动表记录,进行匹配

(4)将简单嵌套循环中的多次比较,合并成一次,降低被驱动表的访问频率

3、事项

(1)在一个有 N 个 JOIN 关联的 SQL 中,会分配 N-1 个 join buffer

(2)缓存不只是关联表的列,也缓存 SELECT 后面的列,所以查询时,尽量减少不必要的字段,可以让 join buffer 中可以存放更多的列,避免 SELECT *

4、查看 block_nested_loop 状态,默认开启

SHOW VARIABLES LIKE '%optimizer_switch%';

5、默认情况下 join_buffer_size=256k

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

 

Hash Join

1、从 MySQL 8.0.20 版本开始,废弃 BNLJ,

2、从 MySQL 8.0.18 版本开始,加入 Hash Join ,且默认使用

3、对比

(1)Nested Loop:对于被连接的数据子集较小的情况下,Nested Loop 是较好选择

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

4、应用场景

(1)适用于较小的表完全可以放入内存中的情况,总成本就是访问两个表的成本之和

(2)在表很大的情况下,并不能完全放入内存,此时优化器会将它分割成若干不同的分区,不能放入内存的部分,就把该分区写入磁盘的临时段,此时要求有较大的临时段,从而尽量提高 I/O 性能

(3)工作于没有索引的大表和并行查询的环境中,并提供最好的性能

(4)由于 Hash 特性限制,只能应用于等值连接

 

子查询优化

1、子查询的执行效率不高的原因

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

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

(3)对于返回结果集比较大的子查询,其对查询性能的影响也就越大

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

3、尽量不使用 NOT IN 或 NOT EXISTS,用 LEFT JOIN xxx ON xxx WHERE xxx IS NULL 替代

 

ORDER BY 优化

1、在 MySQL 中,支持两种排序方式:FileSort、Index

(1)Index 排序:索引可以保证数据的有序性,不需要再进行排序,效率更高

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

2、SQL 中,在 WHERE 条件字段上加索引,同时在 ORDER BY 字段加索引

(1)目的:在 WHERE 子句中避免全表扫描,在 ORDER BY 子句避免使用 FileSort 排序

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

(3)无法使用 Index 时,需要对 FileSort 方式进行调优

3、减少数据量,减少回表次数

(1)LIMIT

(2)覆盖索引

4、索引失效

(1)ORDER BY 需要遵循索引最佳左前缀法则,否则索引失效

(2)ASC、DESC 混用可能打乱索引顺序,造成索引失效,MySQL 8.0 引入 Descending Index 特性,可以支持 ORDER BY 子句中 ASC、DESC 混用 

(3)排序列包含非同一个索引的列

(4)排序列是某个联合索引的索引列,但是这些排序列在联合索引中并不连续

(5)用来形成扫描区间的索引列与排序列不同

(6)排序列不是以单独列名的形式出现在 ORDER BY 子句中

5、如果 WHERE 使用索引的最左前缀定义为常量,则 ORDER BY 能使用索引

6、FileSort 排序并非效率一定低

(1)原因:所有的排序都是在条件过滤之后才执行,如果条件过滤掉大部分数据,对小数据量排序并不很消耗性能,即使索引优化排序,但实际提升性能很有限

(2)当范围条件和 GROUP BY / ORDER BY 字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上,反之,选择使用排序索引

(3)两个索引同时存在,MySQL 自动选择最优的方案,随着数据量的变化,选择的索引也会随之变化

 

Filesort 算法

1、排序的字段若如果不在索引列上,则 Filesort 有两种算法:双路排序、单路排序

2、双路排序(慢)

(1)MySQL 4.1 之前使用双路排序

(2)扫描两次磁盘,最终得到数据:从磁盘取排序字段,在内存中的 buffer 进行排序,再从磁盘取其他字段

(3)读取行指针和 ORDER BY 列,加载到内存 buffer,对数据进行排序

(4)然后扫描已经排序好的列表,按照列表中的值,再次扫描磁盘的完整表数据,读取其他所需列数据,并输出

3、单路排序(快)

(1)MySQL 4.1 之后使用单路排序

(2)只扫描一次磁盘

(3)从磁盘读取查询需要的所有列 ,按照 ORDER BY 列在 buffer 对所有所需列进行排序,然后扫描排序后的列表进行输出

(4)效率更快,避免第二次读取数据,并且把随机 I/O 变成顺序 I/O

(5)缺陷:有可能取出的数据的总大小,超出 sort_buffer 容量,导致每次只能取 sort_buffer 容量大小的数据,导致大量 I/O 操作

4、优化策略

(1)不管使用哪种算法,提高 sort_buffer_size 都会提高效率,要根据系统能力提高,因为该参数是针对每个进程 1M-8M 之间调整

(2)MySQL 5.7,InnoDB 默认值为 1048576字节(1MB)

(3)两种算法的数据都有可能超出 sort_buffer_size,超出后,会创建 tmp 文件进行合并排序,导致多次 I/O,单路排序算法的风险会更大

(4)查看 sort_buffer_size 大小

SHOW VARIABLES LIKE '%sort_buffer_size%';

(5)提高 max_length_for_sort_data,增加使用改进算法的概率,在 1024字节~8192字节之间调整

(6)查看 max_length_for_sort_data,默认1024 字节

SHOW VARIABLES LIKE '%max_length_for_sort_data%';

(7)单路排序为将要排序的每一行创建固定缓冲区,VARCHAR 列的最大长度是 max_length_for_sort_data 规定的值,而不是排序数据的实际大小

(8)当 MySQL 不得不对 text、blob 列进行排序时,只会使用前缀并忽略剩余的值,因为不得不分配固定大小的结构来容纳数据,并且从外部存储中将前缀拷贝回结构中,可以使用 max_sort_length_data 定义前缀大小

(9)MySQL 不会真正的显示使用的是哪种算法,如果增大 max_length_for_sort_data 值,并且磁盘使用率上升,CPU 使用率下降,sort_merge_passes 值比以前增加的更快,可能使用单路排序算法

(10)若过高,数据总容量超出 sort_buffer_size,影响:高磁盘 I/O 活动,CPU 使用率低

(11)不使用 SELECT *,只查询需要的字段

(12)当查询字段大小总和,小于 max_length_for_sort_data,而且排序字段不是 TEXT / BLOB 类型时,使用单路算法,否则使用双路算法

 

 

GROUP BY 优化

1、GROUP BY 使用索引原则与 ORDER BY 几乎相同,GROUP BY 即使没有过滤条件使用索引,也可以直接使用索引

2、GROUP BY 先排序再分组,遵照索引的最佳左前缀法则

3、当无法使用索引列,可以增大 max_length_for_sort_data、sort_buffer_size

4、WHERE 效率高于 HAVING

5、减少使用 ORDER BY、GROUP BY、DISTINCT,减少耗费 CPU 资源

6、包含 ORDER BY、GROUP BY、DISTINCT 语句,WHERE 条件过滤的结果集保持在 1000 行以内,否则 SQL 很慢

 

分页查询优化

1、一般分页查询时,通过创建覆盖索引能够比较好地提高性能

2、优化思路

(1)只应用于主键自增的表,把 LIMIT 查询转换为某个位置的查询,即 LIMIT a, b 转换为 WHERE > a LIMIT b

(2)若表的主键非自增,则在索引上完成排序分页操作,最后根据主键关联回原表,查询所需要的其他列内容

SELECT * FROM1 别名1,(SELECT 主键 FROM1 ORDER BY 主键 LIMIT 第一行偏移量,返回最大行数) 别名2
WHERE 别名1.字段 = 别名2.字段;

 

覆盖索引

1、只从索引取得数据列,不必从数据表中读取

2、一个索引包含 / 覆盖满足查询语句、查询条件所涉及的字段

3、优点

(1)避免 InnoDB 表进行索引的二次查询(回表):在覆盖索引中,二级索引的键值中可以获取所要的数据,避免对主键的二次查询,减少 I/O 操作,提升查询效率

(2)可以把随机 I/O 变成顺序 I/O 加快查询效率:覆盖索引是按键值的顺序存储,对于 I/O 密集型的范围查找,对比随机 l/O 效率高

(3)减少树的搜索次数

4、缺点:索引字段维护有代价,需要冗余索引来支持覆盖索引

 

前缀索引

1、为字符串创建前缀索引

(1)假设字符串很长,那存储一个字符串就需要占用很大的存储空间

(2)B+ 树索引中的记录存储该列的完整字符串,更费时,而且字符串越长,在索引中占用的存储空间越大

(3)B+ 树索引中索引列存储的字符串很长,则在字符串比较时,占用更多的时间

(4)前缀索引:通过截取字段的前面一部分内容建立索引

(5)在查找记录时,不能精确定位到记录的位置,但能定位到相应前缀所在位置,然后根据前缀相同的记录的主键值,回表查询完整的字符串值

(6)节约空间,减少字符串的比较时间,解决排序问题

(7)计算不同长度下的区分度的公式,索引长度即截取前缀长度,结果越接近于 1,区分度更好

SELECT COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*) AS 区分度
FROM 表名;

(8)在 VARCHAR 字段上建立索引时,必须指定索引长度,不必对全字段建立索引,根据实际文本区分度决定索引长度

(9)索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上

(10)索引列前缀对排序的影响:前缀长度与 LIMIT 长度相同,且恰好存在多个相同前缀,则 ORDER BY 不能得到正确结果

2、区分度高(散列性高)的字段

(1)列的基数:某一列中不重复数据的个数

(2)在记录行数一定的情况下,列的基数越大,该列中的值越分散;列的基数越小,该列中的值越集中

(3)列的基数直接影响是否能有效利用索引

(4)以列的基数大的列建立索引

(5)区分度公式,结果越接近 1 越好,一般超过 33% 算较高效索引

SELECT COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*) AS 区分度
FROM 表名;

(6)联合索引把区分度高(散列性高)的列放在前面

 

索引条件下推

1、ICP:Index Condition Pushdown

2、MySQL 5.6 中新特性,在存储引擎层使用索引过滤数据的一种优化方式:尽可能利用二级索引,筛除不符合 WHERE 条件的记录,减少需要回表继续判断的次数

3、对比

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

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

4、ICP 可以减少存储引擎必须访问基表的次数,和 MySQL 服务器必须访问存储引擎的次数,但 ICP 加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例

5、设置 ICP

(1)默认情况下启用 IPC

(2)通过设置系统变量 optimizer_switch,控制 index_condition_pushdown

(3)开启 IPC

SET optimizer_switch ='index_condition_pushdown=on';

(4)关闭 IPC

SET optimizer_switch='index_condition_pushdown=off';

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

7、使用条件

(1)如果表访问的类型为 range、ref、eq_ref、ref_or_null,则可以使用 ICP

(2)ICP 可以用于 InnoDB 和 MyISAM 表,MySQL 5.7 支持分区表使用 ICP

(3)对于 InnoDB 表,ICP 仅用于二级索引,因为主键索引没有回表

(4)当 SQL 使用覆盖索引时,不支持 ICP,因为覆盖索引没有回表

(5)相关子查询的条件不能使用 ICP

(6)并非全部 WHERE 都可以用 ICP 筛选,如果 WHERE 条件的字段不在索引列中,仍要读取整表的记录到服务器进行 WHERE 过滤

8、不使用 ICP 扫描过程

(1)storage 层:只将满足 index key 条件的索引记录对应的整行记录取出,返回给 server 层

(2)server 层:对返回的数据,使用 WHERE 条件过滤,直至返回最后一行

9、使用 ICP 扫描过程

(1)storage 层:首先确定 index key 条件满足的索引记录区间,然后在索引上使用 index filter 进行过滤,将满足 index filter 条件的索引记录,才去回表取出整行记录,返回 server 层,不满足 index filter 条件的索引记录丢弃,不回表、也不会返回 server层

(2)server 层:对返回的数据,使用 table filter 条件做最后的过滤

10、成本对比

(1)没有使用 ICP,存储层多返回了需要被 index filter 过滤掉的整行记录

(2)使用 ICP 后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。 ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。

 

普通索引、唯一索引

1、查询

(1)对于普通索引,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录,停止检索

(2)对于唯一索引,索引定义唯一性,查找到第一个满足条件的记录后,就会停止继续检索

(3)性能差距可以忽略不计

2、更新

(1)唯一索引的更新不能使用 change buffer ,只有普通索引可以使用

(2)唯一索引在 INSERT 或 UPDATE 时,需要判断索引记录的唯一性,而判断唯一性必须要在内存中判断,所以数据页从磁盘被加载到内存中,而 change buffer 

 

change buffer

1、作用

(1)当需要更新一个数据页时,如果数据页在内存中就直接更新

(2)如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下, InooDB 将这些更新操作缓存在 change buffer 中 ,不需要从磁盘中读入这个数据页

(3)在下次查询需要访问这个数据页时,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作

(4)通过这种方式,能保证这个数据逻辑的正确性

2、merge:将 change buffer 中的操作应用到原数据页,得到最新结果的过程

3、触发 merge

(1)访问这个数据页

(2)系统有后台线程会定期 merge

(3)在数据库正常关闭(shutdown) 的过程中,会执行 merge 操作

4、优点:将更新操作先记录在 change buffer,减少读磁盘 ,提升语句执行速度,避免占用内存 ,提高内存利用率

5、使用场景

(1)普通索引配合 change buffer 使用,对于数据量大的表的更新优化明显

(2)如果所有的更新后面,都马上伴随着对这个记录的查询 ,那么应该关闭 change buffer,而在其他情况下,change buffer 都能提升更新性能

(3)由于唯一索引不能使用 change buffer,若业务可以接受 ,从性能角度,优先考虑非唯一索引

(4)业务不确定:以业务正确性优先 ,在业务代码已保证不会写入重复数据的前提下,讨论性能问题,若业务不能保证,或业务要求数据库必须约束,则必须创建唯一索引

(5)归档库场景:可以考虑使用普通索引,比如:线上数据只需要保留半年,然后历史数据保存在归档库,此时归档数据已经确保没有唯一键冲突,要提高归档效率,可以把表里面的唯一索引改成普通索引

 

EXISTS 和 IN 的区分

1、根据表的大小选择索引,小表驱动大表,效率最高

2、在子查询中

(1)子查询得出的结果集记录较少,主查询中的表较大且又有索引时,使用 IN

(2)主查询记录较少,子查询中的表较大且有索引时,使用 EXISTS

3、驱动顺序的改变是性能变化的关键

(1)如果是 EXISTS,以外层表为驱动表,先被访问,后执行子查询

(2)如果是 IN,先执行子查询,后执行主查询

(3)以驱动表的快速返回为目标,考虑到索引及结果集的关系,另外 IN 不对 NULL 进行处理

(4)IN 是把外表和内表作 hash 连接,而 EXISTS 是对外表作 loop 循环,每次 loop 循环再对内表进行查询

4、NOT IN、NOT EXISTS

(1)如果查询语句使用 NOT IN,那么内外表都进行全表扫描,没有使用索引

(2)而 NOT EXISTS 子查询依然能使用表上的索引

(3)不论哪个表大,使用 NOT EXISTS 都比 NOT IN 要快

 

COUNT(*)、COUNT(具体字段)、COUNT(1)

1、前提:如果你要统计的是某个字段的非空数据行数,则另当别论,毕竟比较执行效率的前提是结果一样才可以。

1、COUNT(*)、COUNT(1) 都是对所有结果进行 COUNT,本质上没有区别,执行效率是相等

(1)如果有 WHERE 子句,则是对所有符合筛选条件的数据行进行统计

(2)如果没有 WHERE 子句,则是对数据表的数据行数进行统计

2、不同存储引擎情况下

(1)MyISAM:统计数据表的行数的时间复杂度为 O(1),因为每张 MyISAM 数据表都有一个 meta 信息存储 row_count 值,而一致性则是由表级锁来保证的

(2)InnoDB:因为 InnoDB 支持事务,采用行级锁和 MVCC 机制,所以无法像 MyISAM 一样,维护一个 row_count 变量,因此需要采用扫描全表,时间复杂度为 O(n),循环加计数的方式来完成统计

3、InnoDB

(1)若使用 COUNT(具体字段),尽量采用二级索引,因为主键采用的索引是聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)

(2)对于 COUNT(*)、COUNT(1),不需要查找具体的行,只是统计行数,系统会自动采用占用空间更小的二级索引来进行统计

(3)如果有多个二级索引,会使用 key_len 小的二级索引进行扫描

(4)当没有二级索引时,才会采用主键索引来进行统计

 

SELECT(*)

1、在表查询中,建议明确字段,不要使用 * 作为查询的字段列表

2、性能降低

(1)MySQL 在解析的过程中,会通过查询数据字典将 * 按序转换成所有列名,耗费资源和时间

(2)无法使用覆盖索引

 

LIMIT 1 对优化的影响

1、针对扫描全表的 SQL 语句,若确定确定结果集只有一条,则加上 LIMIT 1 时,当找到一条结果时,不会继续扫描,加快查询速度

2、如果数据表已经对字段建立唯一索引,那么可以通过索引进行查询,即不会全表扫描,则不需要加上 LIMIT 1

 

COMMIT

1、尽量多使用 COMMIT,提高程序性能,需求因为 COMMIT 所释放的资源而减少

2、COMMIT 所释放的资源

(1)回滚段上用于恢复数据的信息

(2)被程序语句获得的锁

(3)redo / undo log buffer 中的空间

(4)管理上述 3 种资源中的内部花费

 

自增 ID 做主键的缺点

1、可靠性不高:存在自增 ID 回溯的问题,MySQL 8.0 中被修复

2、安全性不高:对外暴露的接口可以非常容易猜测对应的信息

3、性能差:自增 ID 性能较差,需要在数据库服务器端生成

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

5、局部唯一性:自增 ID 是局部唯一,只在当前数据库实例中唯一,而不是全局唯一,不是在任意服务器间都是唯一

 

主键设计

1、不使用跟业务有关的字段做主键,无法预测在项目的整个生命周期中,哪个业务字段会因为项目的业务需求而有重复,或者重用之类的情况出现

2、非核心业务:对应表的主键可以选择使用自增 ID,如告警、日志、监控等信息

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

4、UUID

(1)特点:全局唯一,占用 36 字节,数据无序,插入性能差,MySQL 8.0 

(2)MySQL 数据库 UUID 组成:时间 + UUID 版本(16 字节)- 时钟序列(4 字节)- MAC 地址(12 字节)

(3)全局唯一:在 UUID 中时间部分占用 60 位,存储的类似时间戳,但表示的是从 1582-10-15 00:00:00.00 到现在的 100ns 计数,UUID 存储的时间精度比 TIMESTAMPE 更高,时间维度发生重复的概率降低到 1/100ns,时钟序列是为了避免时钟被回拨导致产生时间重复的可能性,MAC地址用于全局唯一

(4)UUID 根据字符串进行存储,4 字节存储无用的 - 字符串,总共需要36个字节。

(5)随机无序:将时间低位放在最前面,而这部分的数据是一直在变化的,并且是无序

(6)若将时间高低位互换,则时间就是单调递增,即 UUID 单调递增,MySQL 8.0 可以更换时间高低位的存储方式,使 UUID 变有序

(7)MySQL 8.0 解决 UUID 存在的空间占用的问题,除去 UUID 字符串中无意义的 - 字符串,并且将字符串用二进制类型保存,存储空间降低为 16 字节

(8)MySQL 8.0 提供函数:UUID_TO_BIN:对 UUID 字符串进行二进制压缩,32 字符 -> 16 bit;BIN_TO_UUID:解压操作,16 bit -> 32 字符

posted @   半条咸鱼  阅读(903)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示