《高性能Mysql》读书笔记

不归SUN
375 阅读21分钟

目的：

查询速度更快？锁是否会减慢程序的速度？内存使用情况改变了吗？等待磁盘的时间改变了吗？

一、schema的设计

1.原则：

  1. 尽量使用能够正确存储和表示数据的最小数据类型。因为更小的数据类型通常更快，占用的磁盘、内存和cpu缓存的空间更少。
  2. 简单的数据类型的操作通常需要更少的cpu周期。例如，整型数据比字符数据的比较操作代价更低。应该将日期和时间存储为mysql的内置类型而不是字符类型。应该用整型数据存储IP地址。
  3. 尽量避免存储null。因为，如果查询中包含可为null的列，会使得索引、索引统计和值比较都更复杂，null列会使用更多的存储空间。（但是在调优时此项带来的性能提升比较小，没必要首先调优）
数据类型的使用注意事项：
  1. 日期和时间类型：DATETIME和TIMESTAMP。DATETIME的存储空间8字节，范围从1000年到9999年，与时区无关。TIMESTAMP的存储空间4字节，范围从1970年到2038年1月19日，与时区有关。
  2. IPv4地址，通常是VARCHAR(15)来存储，用小数点分隔方便阅读。然而，实际上是32位无符号整数，而不是字符串，可以将IP地址存储为无符号整数。使用的空间从VARCHAR(15)的约16字节缩减到无符号32位整数的4字节。MySql提供了INET_ATON() 和 INET_NTON() 函数来在这两种表示形式之间进行转换。
  3. ENUM 类型：（没遇到过，记录一下，慎用）创建包含枚举的表，当插入数据到 ENUM 列时，必须使用定义时指定的合法值之一。如果尝试插入一个未定义的值，MySQL 将会报错。
CREATE TABLE example_table (
    id INT AUTO_INCREMENT,
    status ENUM('open', 'closed', 'pending') NOT NULL
);

尽管 ENUM 值是字符串形式的，但 MySQL 实际上是按照它们在列表中的位置来存储这些值的。例如，'open' 可能会被存储为 1，'closed' 为 2，'pending' 为 3。这种存储方式使得 ENUM 类型比其他字符串类型更节省空间。

使用 ENUM 的注意事项

ENUM 的顺序很重要，因为它们是按定义时的顺序来索引的。

如果需要更改 ENUM 的选项，需要先确保不会影响已有的数据。

虽然 ENUM 类型可以节省存储空间，但在某些情况下可能会降低查询性能，特别是当 ENUM 有大量选项且经常用于查询条件时。

在比较 ENUM 类型时，MySQL 会根据定义时的顺序进行比较，而不是字典序。

  1. 字符串类型：

避免使用字符串类型作为标识符的数据类型，因为消耗空间大，通常比整型慢。

随机的字符串，比如uuid, 会减慢插入和查询的速度，因为插入的值会写到索引的随机位置，使得查询也变慢。

绕过存储通用唯一标识符uuid，应该删除破折号。

2.表设计中的坑：

  1. 太多的列

究其原因还是因为MySQL的工作机制：

MySQL核心服务和存储引擎之间会通过缓冲格式来拷贝数据，核心服务会对API返回的内容进行解码，而解码的过程开销是非常高的。当然也不可太过于极端，为了减少列的数量而导致更多的表关联，得不偿失。这里的列太多一般指数百千个字段，其实正常的表不会有什么大问题，对于过多极少用到的列，可以考虑另建一张表，做冷热列的分离。

  1. 太多的联接

这个可以理解为上面太多列的反向操作，把数据拆分的过于细，导致想要获取一条完整的业务数据需要关联N张表，而MySQL单次最多只能关联61张表，建议一次关联查询控制在12张表以内。对于关联过多的笔者之前的做法都是查一些冗余数据到内存里用代码去过滤，仅供参考。

  1. 过度使用枚举

二、创建高性能的索引

1.B-tree索引

InnoDB存储引擎：B+tree结构上的索引，索引加快数据访问速度，在查询时不走全表扫描，而是从索引的根节点开始搜索，根节点存放指向子节点的指针，根据指针向下层查找。

按照顺序存储数据，B-tree会将相关的列值存储在一起，因为索引中存储了实际的列值，所以某些查询只使用索引可以完成全部的查询。使用索引优点：

索引大大减少了服务器需要扫描的数据量

索引可以帮助服务器避免排序和临时表

索引可以将随机I/O 变为顺序 I/O

1.自适应哈希索引

是一种优化机制，提高对索引的查询性能。它基于InnoDB存储引擎，在原有的B-tree索引上创建，主要特点包括：

  1. 自动创建：当查询频繁访问某个索引时，InnoDB会自动创建一个哈希索引来加速查询操作。
  2. 内存中的哈希索引：哈希索引存储在内存中，通过哈希表快速定位数据行。
  3. 提高查询速度：对于点查找或相等条件的查询，自适应哈希索引可以显著减少查找时间。
  4. 动态调整：哈希索引会根据访问模式动态创建和调整，但并不会持久化到磁盘。
2.使用B-tree索引的查询类型

全值匹配，匹配最左前缀，匹配列前缀，匹配范围值

1.全值匹配

  对于精确匹配查询（如使用 = 或 IN），B-tree索引可以快速定位到对应的记录。例如：

SELECT * FROM employees WHERE id = 123;
  1. 匹配范围值

  B-tree索引能够有效地处理范围查询，因为存储的数据是有序的，可以快速找到范围的起始和结束位置。例如：

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 100000;

3.匹配列前缀

对于 LIKE 查询，例如：

SELECT * FROM employees WHERE name LIKE 'John%';

这里，LIKE 'John%' 使用了前缀匹配，B-tree索引能够快速找到所有以 'John' 开头的记录。

  注意：如果查询条件是 LIKE 'a%'，B-tree索引能够有效使用。但对于 LIKE '%a'，B-tree索引可能无法有效利用，特别是当 % 出现在前缀时。

4.匹配最左前缀

使用索引的第一列，最左前缀原则。

2.全文索引

查找的是文本中的关键词，而不是直接比较索引中的值。类似于搜索引擎，而不是简单的where条件匹配。

创建全文索引：

CREATE FULLTEXT INDEX idx_content ON articles(content);

查询使用全文索引：

  1.   MATCH() AGAINST()：用于在全文索引中进行搜索。
SELECT * FROM articles
WHERE MATCH(content) AGAINST('search term');

支持的查询类型：

  1.   自然语言模式：默认模式，根据自然语言的相关性进行搜索。
  1.   布尔模式：允许使用布尔操作符（如 +, -, *）进行更精确的搜索。

注意事项：

  1.   最小词长：默认情况下，MySQL忽略小于一定长度的词（如3个字符），这个长度可以通过调整系统变量 ft_min_word_len 进行修改。
  1.   停用词：MySQL在全文索引中使用停用词列表来忽略常见词，这些停用词可以通过调整 ft_stopword_file 进行配置。
  1.   性能：全文索引在大数据量的文本搜索时性能较好，但创建和维护全文索引可能会消耗较多的资源。

3.聚簇索引

聚簇索引：不是索引类型，而是一种数据存储方式。InnoDB的聚簇索引在同一个结构中保存了B+tree索引和数据行。叶节点包含了完整的数据记录，这种索引叫做聚集索引。（注意：根节点只包含了索引列）数据行和相邻的键值紧凑的存储在一起。一个表只能有一个聚簇索引，InnoDB会根据主键聚簇数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。

注意点：

  1. 基于聚簇索引的表，插入速度严重依赖与插入顺序，按照主键顺序插入最快。或者主键被更新导致需要移动行的时候，可能面临页分裂导致表占用更多的磁盘空间。（页分裂是指在数据库中，当一个数据页已满并且需要插入新数据时，数据库系统会将现有数据页分裂成两个页。这是为了容纳新的数据行并保持数据的有序性。这个过程会导致数据页的数量增加，从而可能导致磁盘空间的增加）
  2. 当行比较稀疏，或者由于页分裂导致数据存储不连续时，聚簇索引可能导致全表查询扫描变慢。
  3. 避免随机的（不连续且值分布范围非常大）的聚簇索引。例如：uuid作为聚簇索引，插入完全随机，插入花费时间长，索引占用空间大。因为新插入的的主键值不一定比之前插入的主键大，无法简单的把新纪录插入到索引的最后，需要为新纪录查找合适的位置并且分配空间，会导致频繁的页分裂操作。
  4. 使用InnoDB时应该尽可能的按主键顺序插入数据。

4.覆盖索引

通常会根据where条件来创建索引，这也可以优化。如果使用索引直接获取列的数据，这样就不需要再回表查询，因为索引的叶子节点包含了要查询的数据。如果一个索引包含所有需要查询的字段的值，称为覆盖索引。

InnoDB 的二级索引在叶子节点保存了记录的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

  1. 索引包含了查询的所有列：查询中涉及的所有列（包括 SELECT 列、WHERE 子句中的列、JOIN 列、ORDER BY 列等）都必须包含在索引中。
  2. 选择合适的索引：为了使索引覆盖查询，创建索引时需要考虑查询的所有要求。通常，可以通过创建一个包含多个列的复合索引来实现。
  3. 在InnoDB存储引擎中，如果索引列不包含主键列id, 但在查询的列中有二级索引，也会使用索引。

例如：表employees在（depart_id）上建有一个索引：

Select id, depart_id from employees  where depart_id=1;

覆盖索引的优点：

  1. 减少 I/O 操作：由于数据可以直接从索引中获取，减少了对表数据行的访问，降低了磁盘 I/O。
  2. 提高查询性能：在查询过程中，数据库可以更快地找到所需的数据，从而提高查询效率。

例外情况：

  1. 索引过大：如果索引包含的列较多，可能会导致索引本身变得非常大，影响性能。
  2. 更新开销：当索引中的列被更新时，可能需要额外的开销来维护索引的正确性。
1.使用索引扫描排序

  1. 只有当索引的顺序和ORDER BY 子句的顺序完全一致，并且所有列排序方向（倒序或者正序）都一样时，才能使用索引来对结果排序。

  2. 如果查询联接多张表，则只有当ORDER BY 子句引用的字段在全部在第一个表时，才能使用索引排序。

  3. ORDER BY 子句需要满足索引的最左前缀的原则。

  4. 特殊情况，如果前导列为常量时，在WHERE或者JOIN 子句中将前导列指定为常量时， ORDER BY子句可以不满足最左前缀的原则。

    1.   例如：
    2.   表employees在（depart_id、name、job_id）上建有一个索引：
    3. Select depart_id、name、job_id from employees  where depart_id=1 ORDER BY name、job_id;
    4.   即使ORDER BY子句不满足最左前缀的原则，也使用了索引排序，因为索引第一列指定为一个常数。

  5. 索引列的第一列上使用范围查询（大于，小于，多个等于），无法使用索引。

2.冗余和重复索引

指在相同的列上按照相同顺序创建多个相同类型的索引。

会影响性能，浪费磁盘空间，避免这样做。

表的索引越多，会导致UPDATE, DELETE, INSERT等操作的速度变慢。

例如：有索引(A,B)，再创建索引(A) 就是重复索引。再创建索引(B)、(B,A)不是重复索引，因为不满足最左前缀列。

另外，新建的是不同类型的索引（哈希索引或者全文索引），无论覆盖了哪些索引列，也不是重复索引。

总结

创建索引时列的顺序选择，依赖于查询语句，索引列的顺序意味着索引先按照最左列进行排序，多列索引的列顺序很重要。

索引是保证MySQL高效查询的利器，合理使用能大大加快查询效率，但是如果设置不合理的索引可能只会白白增加开销，数据量越大，索引效果越明显。

索引不是孤立的，而是和查询成对出现，最优的索引甚至比好的索引快两个数量级，最优索引常常伴随着查询的重写。

三、查询性能优化

1.慢查询原因

1.向数据库请求了不需要的数据，访问的数据太多，访问了太多的行，或者太多的列：

查询不需要的记录，比如：页面只需10条记录，但是查询返回了100条记录。解决方法加上LIMIT子句分页。

多表联接时返回全部列。

总是select * 取出全部列，这可能会导致无法走覆盖索引，给服务器带来更多的消耗。

重复查询相同的数据，解决方法结合业务开发中可以查一次后进行缓存。

2.MySQL扫描了额外的数据：

   响应时间（服务时间和排队时间）长，服务时间指数据库处理查询真正花费的时间，排队时间指等待某些资

   源、I/O操作、存储引擎的表锁行锁、高并发资源竞争等。

理想情况下扫描的行数和返回的行数是相同的。但是，比如在做一个联接查询时，服务器要扫描多行才能形成 一行数据。扫描的行数与返回的行数的比率很低。

2.查询访问类型

全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用等，这些访问类型速度从慢到快，扫描的行数从多到少。

3.查询执行：

执行流程：服务端进行sql语句解析（验证语法规则，关键字的顺序，引号使用）、预处理（检查数据表/列名是否存在，名字或别名是否有歧义，验证权限）生成解析树、再由优化器生成对应的执行计划、调用存储引擎的API执行查询。

客户端向服务器发送一条sql语句，Mysql的通信协议是”半双工“（客户端和服务端在同一个时刻不能同时发送数据），一旦一端发送消息，另一端只能等待接收完完整消息才能响应。Mysql需要等所有的数据全部发送给客户端才能释放这条查询所占用的资源。所以，当查询语句很长或者返回特别多的数据时耗时长。

查询状态

可以使用 SHOW FULL PROCESSLIST 命令查看当前状态。

Sleep 线程在等待客户端发起新的请求

Query 线程正在执行或者正在将结果发给客户端

Locked 正在等待表锁

Analyzing and statistics 正在检查存储引擎的统计信息并优化查询

Copiny to tmp table 正在将结果复制到一个临时表中，比如group by操作，或者进行UNION操作等。

Sorting result 正在对结果集进行排序

查询优化器

在sql语句解析和预处理生成的解析树合法后，会由优化器生成对应的查询执行计划。一条查询会有多种执行方式，最后返回相同的结果。优化器的作用是找到最好的执行计划。

Mysql中的最优的执行计划标准不是耗时长短，而是基于成本的优化。成本的最小单位是随机读取一个4KB数据页的成本。

数据页顺序读或者页面在内存中，访问成本低。

优化器考虑多个因素来生成执行计划，包括：

  表的大小：表的行数和数据量。

  索引：可用的索引及其选择性（唯一性）。

  统计信息：关于数据分布的统计信息，如行数、值的范围等。

  连接类型：不同表之间的连接方式（如内连接、外连接等）。

优化策略类型：

静态优化：

  静态优化是在查询执行前进行的优化。这种优化不依赖于运行时的信息，而是根据已知的元数据和结构来生成执行计划。使用 EXPLAIN 语句查看查询的执行计划，优化器基于已知的表结构和索引生成执行计划。

  特点：

    预先分析：在解析 SQL 查询时，优化器会根据表的结构、索引和统计信息等来生成执行计划。

    不考虑动态变化：静态优化不会考虑运行时的数据状态或系统负载，这意味着它可能无法适应数据的实时变化。

    性能稳定性：由于优化是在查询执行前完成，因此对于大多数固定模式的查询，静态优化能够提供稳定的性能。

动态优化：

  动态优化是在查询执行过程中，根据当前的运行时状态和环境动态调整执行计划的策略。这种方法允许优化器在执行阶段根据实际数据和系统状态做出调整。

  特点：

    实时反馈：动态优化能够根据实时数据的分布、系统负载等因素做出即时调整，从而选择更优的执行路径。

    适应性强：这种策略特别适合处理数据量变化较大或查询模式多样化的场景。

    复杂度高：动态优化通常需要更多的计算资源，因为它需要在执行期间监控和评估各种可能的执行路径。

  能够处理的优化类型：重新定义联接表的顺序，将外联接转换为内联接，使用代数等价变换规则，优化count(),min(),max()，预估并转换为常数表达式。

  示例：

  在某些数据库系统中，执行计划可以根据实时的行数统计或索引使用情况在查询执行过程中进行调整。

联接查询优化器

选择合适的索引、决定表联接顺序等

一般是使查询在第一个表中检查更少的行，能够在第二个和第三个表中执行快速索引查找，执行的索引查找次数不一样。表联接顺序使用explain关键字查看

性能考虑：

  索引的使用：合理的索引能够显著提高联接查询的性能。确保联接字段上有索引，可以加快查找速度。

  联接顺序：小表在前，大表在后，通常能提高整体执行效率。

  数据量：对于大量数据的联接，考虑使用子查询或视图来简化复杂的查询逻辑，有助于提高可读性和维护性。

联合表的访问方法：优化器决定使用哪种方法来访问和联接表。例如：

  嵌套循环联接：一种简单且常用的方法，通过外层表中的每一行与内层表进行匹配。

  哈希联接：先将其中一个表的数据放入哈希表，然后再扫描另一个表进行匹配，适用于较大的数据集。

  排序合并联接：两个表都进行排序后，合并相同的键值，通常在进行大范围联接时使用。

排序优化：

  索引：如果排序的列上有索引，MySQL 可以更有效地执行排序操作，从而提高性能。

  内存限制：MySQL 在排序时会使用内存，如果数据量很大，可能会导致使用临时文件存储排序结果，从而影响性能。

  内存临时表：如果排序的数据量小，MySQL 会使用内存来存储临时表。

  磁盘临时表：如果数据量超过了内存限制，MySQL 会将临时表写入磁盘，这会降低性能。

  如果order by子句中所有列都是来自联接的第一个表，那么在处理第一个表的时候进行文件排序。

  反之，会先将联接的结果存入临时表中，所有联接结束后再进行文件排序。

  如果有LIMIT, LIMIT会在文件排序后应用，所以即使需要返回少量数据，临时表和需要排序的数据量也非常大。

4.优化特定类型的查询

优化count()查询：

如果在count()括号里指定了列，则忽略null值，统计的是该列有值的结果数。count()则是直接忽略所有列统计结果中的行数。所以如果想知道结果的行数使用count(), count(id)也可以但是比前者慢。

查询同一列不同值得数量：

//分别统计蓝色，红色得数量
select sum(color='bule',1,0)) AS blue,sum(color='red',1,0)) AS red from items;
select count(color='blue' or null) as blue,count(color='red' or null) as red from items;

优化联接查询：

1.确保ON 或者 USING 子句中的列有索引。创建索引需考虑联接顺序，一般在联接顺序中第二个表的相应列上创建索引（原因上面有）。比如A和B表用C列联接，如果优化器的联接顺序为B,A，那么在A表的C列上创建索引。

2.确保Group by 和 Order by 中的表达式中的列只有一个表中的列，这样会使用索引。

优化limit 和 offset 子句：

1.id偏移量，有对应的索引

重构查询方法

1.将一个复杂大查询分解为多个简单小查询（在大查询耗时特别大时使用）。意味着会返回不同的结果集，修改应用代码和查询，以获得更好的性能。

2.切分查询，每个查询的功能完全一样，只完成一小部分，每次返回一小部分的查询结果。

3.分解联接查询，对每一个表进行一次单表查询，然后将结果在应用程序中进行联接。将查询分解后，执行单个查询可以减少锁的竞争。

加快查询总结：

1.在索引中使用where条件来过滤不匹配的记录。

2.如果查询确实需要扫描大量数据当只返回少量数据，建议使用索引覆盖扫描。

3.重写查询，改变逻辑和思想。

4.避免使用select *,明确选择需要的列，有助于减少数据传输量。

//后续待补充...

avatar
文章
阅读
粉丝