前言
博文：构建优化之城：MySQL 数据建模、数据类型优化与索引常识全面解析
博文：MySQL 数据结构优化与索引细节解析：打造高效数据库的优化秘笈
简要说明了一些索引的基本知识及分类、技术名词，索引的数据结构、生产如何调优、调优的细节如何处理、如何避免生产慢 SQL，该篇博文会继续从以下几点来对 MySQL 调优部分进行分析：
- 为什么会查询慢？
- 优化数据访问、执行过程
- 大数据量查询优化
- 海量数据解耦优化处理
为什么会查询慢？
查询慢的原因有很多种，不一定是数据库设计不合理或索引使用不正当或 SQL 编写有问题等等
一般情况下，表里数据量不是特别多时，其实改变 SQL 语句的差别不大，但是当表中的数据量形成了一定规模数以后，查询慢的情况就会经常发生，以下是查询慢可能会发生的原因
- 网络：网络对 MySQL 影响非常大，在进行数据访问时，很多情况下都是在数据中心进行存放的；当需要读取跨异地的数据时，网络将会成为一个至关重要的影响点，特别是在分布式环境中，影响更为突出，因此要尽量减少网络对于数据访问的影响；比如：数据库服务节点与部署服务的节点尽量保持在同一个地域内，不要一个在海外，一个在国内等
- CPU：在执行不同的操作时，通过 CPU 轮转来完成各个任务的执行，所以时间片的分配会在一定程度下影响数据库的查询效率，
尽量减少在业务代码中循环获取数据库连接读取数据 - IO：在进行 SQL 调优，最关键的点其实就是要优化 IO 成本量
- 上下文切换：N 多个线程在执行，某个进程的时间片用完以后，就会切换到另外一个进程去执行，切换时会比较浪费时间
- 系统调用：操作系统内核中的核心概念，涉及到 IO 模型，一般是由具体的 IO 框架来控制的，无法进行优化
- 生成统计信息：MySQL 中 > show profiles、performance schema 这些统计信息的生成，都需要占用一定的资源，此时也会影响数据的查询
- 锁等待时间：在并发场景中，锁是非常麻烦的一个问题；在 MySQL 有表锁、行锁，锁机制是与存储引擎相关的；经常用的存储引擎是 MyISAM、InnoDB，MyISAM 里面有两种锁：共享读锁、独占写锁，锁名称不同，内部实现也不一样；MyISAM 在加锁时只会锁别；
InnoDB 可以锁表也可以锁行，但是需要注意的是，InnoDB 锁的是索引，若没有对应的索引可以加锁的话，那么就会由行锁退化为表锁
优化数据访问
查询数据低效原因
从数据方面看，查询性能低下的主要原因：访问的数据太多，某些查询不可避免的需要筛选大量的数据，也就是说，这是 IO 问题，因为我们知道大部分的数据都是持久化到磁盘中的，有时候就算加了索引也不一定可以用到索引
确认应用程序是否在检索大量超过需要的数据（数据量超过 30% 会触发 filesort）或者说你通过索引已经过滤了一部分数据，但过滤后的这部分数据你又因为排序的原因导致这部分数据又造成了 filesort，可以通过观察执行计划得知，如下索引：
mysql> select count(*) from rental; +----------+ | count(*) | +----------+ | 16044 | +----------+ 1 row in set (0.00 sec) mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+ | 1 | SIMPLE | rental | NULL | ALL | rental_date | NULL | NULL | NULL | 16008 | 50.00 | Using where; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
如上，可以得出检索了数据行 16008 记录，过滤的行数太多了，完全可以通过改变条件来保证检索的数据行减少，如下：
mysql> explain select rental_id,staff_id from rental where rental_date>'2006-05-25' order by rental_date,inventory_id;\ +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | rental | NULL | range | rental_date | rental_date | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
如上可以看到只是改变了简单的查询条件，查询的行数就改变了很多，所以有时候就可以通过适当的调整来减少数据访问的行数；比如：当用户表数据量比较多时，默认我只展示最近一个月的用户出来
是否请求了不需要的数据
查询不需要的记录
在工作中经常会误以为 MySQL 只会返回需要的数据，实际上 MySQL 会先返回所有的结果再进行计算，在日常的开发习惯中，经常会先用 select 语句查询大量的结果集，然后获取前面的 N 行数据后关闭结果集，
优化方式其实在查询后面添加 LIMIT > 限制结果集数量
多表关联时返回全部列字段
查询时尽量不要使用 SELECT *，用到什么列就查什么列；多表关联尤其不要用
*，建议表民后追加别名，也就是说你有两张以上的表进行关联时，不要写*
重复查询相同的字段值
若需要不断重复执行相同的查询，且每次返回相同的数据，基于这样的应用场景，可以将这部分数据缓存起来，能够提高查询效率（
Redis、Spring Session，当然本地缓存不是很推荐，数据量大或占用的空间大小多时对 JVM 年轻代、老年代会带来负担）
优化执行过程
查询缓存
在解析查询语句之前，若查询缓存是开启的，那么 MySQL 会优先检查这个查询是否命中查询缓存中的数据，若查询恰好命中了查询缓存，那么在返回结果之前会先检查用户权限，若权限没有问题，MySQL 会跳过所有的阶段，就会直接从缓存中拿到结果后就返回给客户端；虽然查询缓存在 MySQL 8 里面给去掉了，但是在 5.x 还是有的，对于某些不经常改变的字典表数据完全可以使用查询缓存来加快查询的访问效率
查询优化处理
MySQL 查询完缓存之后会经过以下步骤 > 解析 SQL、预处理、优化 SQL 执行计划，在这其中某个步骤出现了问题，都可能会终止查询操作，两块：语法解析器、查询优化器
语法解析器
所谓语法解析器 > MySQL 通过关键字将 SQL 语句进行解析，生成一颗解析树，解析器会将使用 MySQL 语法规则验证后解析查询，例如：验证使用了错误的关键字或者顺序是否正确等等，预处理器会进一步检查解析树是否合法；例如：表名、列名是否存在，是否存在歧义，还会验证权限等等；
AST(抽象语法树)没必要自己解析的，可以使用Apache Calcite开源项目组件进行解析，Calcite > 一款开源 SQL 解析工具，可以将各种 SQL 语句解析成抽象语法树，之后再通过 AST 就可以把 SQL 中所需要的表达式算法和关系体现在具体的代码中
当语法没有问题后，相应的是由优化器将其转换为执行计划，一条查询语句可以使用非常多的执行方式，最后都可以得到对应的结果，但是不同的执行方式带来的效率是不同的，优化器最主要的目的就是要选择最有效的执行计划
查询优化器
MySQL 使用的是基于成本的优化器
在优化时会尝试预测一个查询使用某种查询计划时的成本，并选择其中成本最小的一个（使用不到正确的索引与成本也有很大关系），如下：
mysql> select count(*) from film_actor; +----------+ | count(*) | +----------+ | 5462 | +----------+ 1 row in set (0.00 sec) mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1104.399000 | +-----------------+-------------+
通过以上查询可以看出，执行了一个 COUNT 操作，一共查询出了 5462 条记录，耗费的成本是 1104.3990，show status like 'last_query_cost' 这是在当前会话中返回最后一条 SQL 语句执行的耗费时间成本，若换另外一条语句来执行，如下：
mysql> select count(*) from film; +----------+ | count(*) | +----------+ | 1000 | +----------+ 1 row in set (0.01 sec) mysql> show status like 'last_query_cost'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | Last_query_cost | 211.999000 | +-----------------+------------+
不同的 SQL 语句在执行时所耗费的时间成本是不同的，当得到这个结果值之后，可以适当调整我们的 SQL 语句，以达到最优的方式
在实际业务开发中，COUNT(1) 计数的方式尽量少用，除非是我们那些需要逻辑分页的查询场景下，其他的业务下，我们可以将 O(N) 计数的方式变为 O(1)，也就是说我们提前把这个数计算好，而不是每次要查询时在去单独统计这个数出来
以上时间成本的参数值即 > IO_COST、CPU_COST 开销总和，它通常也是评价 SQL 查询执行效率的一个常用指标
- 它作为比较各个查询之间开销依据
- 它只能检测比较简单的查询开销，对于包含子查询、union 查询是测试不出来的
- 当我们执行查询时，MySQL 会自动生成一个执行计划，也就是
Query Plan，通常会有很多种不同实现方式，它会选择最低成本的那个，而这个 COST 值就是开销最低的那一个
在很多情况下，MySQL 会选择错误的执行计划，产生此问题的根本原因在于以下几点：
- 统计信息不准确，InnoDB 因为其 MVCC(多版本并发控制) 架构，并不能维护一个数据表行数的精确统计信息，在某些情况下进行了大量的增删改查操作之后，可能会导致统计的信息不精确
mysql> show index from film_actor; +------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | film_actor | 0 | PRIMARY | 1 | actor_id | A | 200 | NULL | NULL | | BTREE | | | | film_actor | 0 | PRIMARY | 2 | film_id | A | 5462 | NULL | NULL | | BTREE | | | | film_actor | 1 | idx_fk_film_id | 1 | film_id | A | 997 | NULL | NULL | | BTREE | | | +------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
如上表示的 film_actor 表中的索引统计信息，当进行大量的查询之后，可能会造成基数 Cardinality 统计不精确，那么就会有可能产生错误的选择
