MySQL 性能优化

  • SQL优化理论
  • 为什么要优化 SQL
  • SQL 执行过程
  • 数据库索引
  • SQL 执行计划
  • SQL 优化实战
  • 适当使用索引
  • 避免索引失效（忽略查询器优化）MySQL5.5
  • 优化不合理的需求
  • 实时更新

SQL优化理论

为什么要优化 SQL

  1. 查询性能低
  2. 执行时间过长
  3. 等待时间过长
  4. SQL写的太差（多体现在多表查询上）
  5. 索引失效
  6. 服务器参数（缓存、线程数）设置不合理
  7. 项目需求不合理
  8. …等等

SQL 执行过程

MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行，是需要经过一系列复杂的流程，最终转变成二进制的机器码，才能被执行的，我们需要对执行的SQL进行优化，那么就有必要先了解下，一个 SQL 语句的执行有哪些主要环节，以查询的 SQL 来举例。

SQL查询语句的执行过程


update mysql 关联表 mysql 多表关联更新_mysql 查看表是否存在


查询语句的执行过程

  • 通过网络的通讯协议接收客户传入的SQL
  • 查看该SQL对应的结果在查询缓存中是否存在
  • 存在直接返回结果
  • 不存在继续往下走
  • 由解析器来解析当前SQL，最终形成初步的解析树
  • 再由预处理器对解析树进行调整，完成占位符赋值等操作
  • 查询优化器对最终的解析树进行优化，包括调整SQL顺序等
  • 根据优化后的结果得出查询语句的执行计划，就是查询数据的具体实施方案，交给查询的执行引擎
  • 查询执行引擎调用存储引擎提供的API，最后由存储引擎来完成数据的查询，然后返回结果。

MySQL 分为 「Server 层」和「存储引擎层」两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnDB。从 MySQL 5.5.5版本成为了默认存储引擎。

连接器

第一步，你会先连接到这个数据库上，接待你的就是连接器 。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

查询缓存

连接建立完成后，就可以执行 SELECT 语句，此时执行逻辑就会到第二步：查询缓存。

MySQL 拿到一个查询请求后 ，会先去查询缓存看看，之前是否有执行过这条语句。之前执行过的语句及其结果可能会以Key-Value对 的形式,被直接缓存在内存中。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

不建议使用查询缓存，因为只有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的表来说，查询缓存的命中率非常低。除非表的数据很久更新一次，比如系统配置表，更新少，查询多才适用查询缓存。MySQL 8.0版本剔除了查询缓存功能。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。

分析器会先做"「词法分析」"。一条SQL语句是由多个字符和空格组成。需要识别里面的字符串分别是什么，代表什么。

SELECT * FROM T WHERE ID = 1;

MySQL通过SELECT关键字识别出这是查询语句，字符串T识别成表名T,字符串ID识别成列ID。

根据词法分析的结果，「语法分析器」会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。如果语句不对就会收到“You have an error in your SQL syntax”的错误提醒；

优化器

经过了分析器，MySQL知道了你要做什么了。在开始执行之前，还要经过优化器的处理。 优化器是表有多个索引时，决定使用哪个索引；又或者SQL语句有多表关联查询（join）时，决定各个表的连接顺序。

❝ mysql> select * from t1 join t2 using (ID) where t1.c=10 and t2.d=20;
既可以先从表t1里面取出c=10的记录的ID值,再根据ID值关联到表2,再判断2里面d的值是否等于20。 也可以先从表t2里面取出d=20的记录的ID值,再根据ID值关联到1,再判断t1里面c的值是否等于10。
❞

关联查询小表写左边，大表写右边，虽然优化器能调整，但不要过于依赖优化器，增加开销。

执行器

MySQL通过分析器知道了你要做什么，优化器知道了要怎么做，于是就进入执行阶器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询权限，如果没有，则立即返回查询权限错误。

如果有权限，就打开表继续执行。打开表的时候，执行时就会根据表引擎定义，去使用这个引擎提供的接口。

比如，例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

  1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存到结果集中。
  2. 调用InnoDB引擎接口取"下一行"，重复相同的判断逻辑，直到取到这个表的最后一行。
  3. 执行器将上述遍历过程中所有满足条件的行组成的记录作为结果集返回给客户端。

对于有索引的表，执行的逻辑也差不多。第一次调用的是"取满足条件的第一行"这个接口，之后循环取"满足条件的下一行"这个接口，这些接口都是引擎中已经定义好的。

数据库索引

什么是索引？

索引是一种帮助数据库获得高效查询效率的数据库对象，该数据库对象使用了特殊的数据结构，以「B树」和「Hash树」最为常见。「MySQL中索引默认使用的是B树。」

简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

一本500页的书，如果想找到其中的某一个知识点，在没有目录的情况下，你需要每页查询，同样，对于数据库的表而言，索引其实就是它的"目录"。

索引作用

索引的作用是减少IO操作次数，从而达到提升性能的目的。

Btree 数据结构

索引利弊

索引的利

  • 减少IO操作次数，提供查询效率
  • 降低CPU使用率(在排序操作中尤为明显)

索引的弊

  • 占据大量的硬盘存储空间
  • 不适用索引的情况
  • 数据量小的表
  • 频繁变动的字段
  • 不经常查询的字段
  • 降低DML操作的效率

索引分类

主键索引

只在建立主键约束时自动添加 ，特点：非空且唯一。

单列索引

单独对表中的某个列数据建立索引。

唯一索引

在该索引中没有重复的数据，都是唯一的。

复合索引

多个列的值组成的索引，当第一个列的值重复时，按照后面的组合必须查找数据。

复合索引相当于字典的二级目录，当前一个值一致时，再使用后一个值做筛选。

❝ 项目中多使用复合索引，在实际的需要中，我们往往都是按照多个条件做查询，而MySQL做查询时只能选择一个索引来使用，因此复合索引比较有优势。
❞

Navicat for MySQL 操作索引


update mysql 关联表 mysql 多表关联更新_mysql 查看表是否存在_02


MySQL 创建索引、删除索引、修改索引、查看索引


#添加普通索引(INDEX)
ALTER TABLE table_name ADD INDEX index_name (column);

#添加复合索引(多列索引)
ALTER TABLE table_name ADD INDEX index_name (column1,column2);

#添加主键索引(PRIMARY KEY)
ALTER TABLE table_name ADD PRIMARY KEY (column);

#添加唯一索引(UNIQUE)
ALTER TABLE table_name ADD UNIQUE index_name (column);

#添加全文索引(FULLTEXT)
ALTER TABLE table_name ADD FULLTEXT index_name (column);

#删除索引
ALTER TABLE table_name DROP INDEX index_name;

#查看索引
SHOW INDEXES FROM table_name;

#修改索引名称
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name

#索引修改，先删除原索引，再根据需要创建同名索引
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX index_name (column1,column2);


SQL 执行计划

SQL执行计划，就是一条SQL语句，在数据库中实际执行的时候，一步步的分别都做了什么。就是我们用EXPLAIN分析一条SQL语句时展示出来的那些信息。

查询优化器影响

expain 关键字

explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这些都可以通过explain命令来查看。


-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';


update mysql 关联表 mysql 多表关联更新_mysql加字段先判断是否存在_03


id 列

select 查询的序列号,包含了一组可以重复的数字,表示查询中mysql执行sql语句的顺序,一般有三种情况：

  1. id相同时，执行顺序由上至下。
  2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
  3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

select_type 列

select_type 表示对应行是是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种。

  1. SIMPLE(简单SELECT，不使用UNION或子查询等)
  2. PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
  3. UNION(UNION中的第二个或后面的SELECT语句)
  4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
  5. UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
  6. SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
  7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
  8. DERIVED(派生表的SELECT, FROM子句的子查询)
  9. UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table 列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select 行id。

type 列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。
  • index: Full Index Scan，index与ALL区别为index类型只遍历索引树。
  • range:只检索给定范围的行，使用一个索引来选择行。
  • ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
  • eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件。
  • const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system。
  • NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys 列

表示该查询可能会用到的索引,但是实际中不一定会用到,在该字段会现在索引的名称。

key 列

显示查询语句中实际使用的索引,如果没有使用则为null。

key_len 列

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref 列

显示索引的哪一列或常量被用于查找索引列的值。

rows 列

根据表统计信息及索引选用情况,大致估算出找到所需的记录锁需要读取的行数,值越大越不好。

extra 列

这一列展示的是额外信息。常见的重要值如下：

  • 「distinct」: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
  • 「Using index」：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
  • 「Using where」：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
  • 「Using temporary」：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
  • 「Using filesort」：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

SQL 优化实战

适当使用索引

建立索引的原则

  1. 较频繁的作为查询条件的字段应该创建索引.如:登录操作;外键也要建立索引
  2. 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列:比如(性别,状态不多的状态列)
  3. 更新非常频繁的字段不适合创建索引,原因:索引有维护成本
  4. 不会出现在where子句中的字段不该创建索引
  5. 索引不是越多越好:只为必要的列创建索引)

join的原则

注意不要使用隐式连接,原因在于SQL的执行顺序

  1. 数据量小的表写在join的左边,数据量大的表写在join的右边.MySQL中join都是通过Nested Loop Join来实现,简单理解为循环嵌套,应该数据量少的表作为外层循环,数据量大的表作为内层循环,然后合并结果.
  2. 优先优化Nested Loop的内层循环.
  3. 保证join语句中被作为连接条件的字段已经建立了索引.
  4. 扩大缓冲区的大小,容纳更大的查询数据(在配置文件中配置.DBA来做).

避免索引失效（忽略查询器优化）MySQL5.5

  1. wherei语句中索引列参与算术计算,该索引失效
  2. where语句中索引列参与函数运算,该索引失效
  3. where语句中使用in运算符有时会让索引失效
  4. where语句中做不等于(!=,<>)运行,该索引失效
  5. where语句中发生类型转发,该索引失效
  6. where语句中模糊查询时以%开头,该索引失效
  7. 在复合索引的使用时跟声明时顺序不一致或者中间有列的缺失,该索引失效 如:声明了(a.b.c)的复合索引.但是在用的时候中间有列的缺失where a =xx and c=xx中间缺失了b,所以无法使用该复合索引只要不是缺失中间列其他情况索引均有效

优化不合理的需求

对于程序员来讲,代码写完了测试能运行通过了,没问题,就可以算是完成工作了,但是往往在该功能上线后没多久就突然间挂了,此时程序员都是一面懵逼,不知道为什么会挂掉,其实问题就出在程序员只知道到功能测试,不清楚除此之外还有其他的方面需要测,比如:压力测试,性能测试等等,当压力测试和性能测试不达标时就会凸显出一些问题,比如这个需求提出是否合理,是否有其他效果相同的替換方面。

实时更新

该功能在社交系统中比较常见,如:点赞数量,评论等这些内容真的是实时更新的吗?

优化方案

  1. 我们无非就是要显示一个点赞数,这个点赞的数量对功能的使用有影响吗?显然是没有的,那么就完全没有必要做成实时更新,我们可以在网页中显示一个大概的数,然后每隔一段时间更新1次即可
  2. 单独使用一张表来存储统计数量的,一旦数据有变动,就直接在独立的表中操作,最后也就只要在独立的表中查询的数据即可