SQL优化
1、一条sql语句执行很慢的原因有哪些？ ⚡
一个SQL执行的很慢，我们要分两种情况讨论:
- 大多数情况下很正常，偶尔很慢，则有如下原因：
- 数据库在刷新脏页（内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页），例如redo log 写满了需要同步到磁盘。
- 执行的时候，遇到锁，如表锁、行锁。
- sql语句写的不好。
- 这条SQL语句一直执行的很慢，则有如下原因：
- 没有用上索引或者索引失效：比如该字段没有索引，由于对字段进行运算、函数操作导致无法用索引。
- 有索引可能会走全表扫描：
- 怎样判断是否走全表扫描？
- 某一列中不重复数据的个数称为基数，而数据量大时不可能全部扫描一遍得到基数，而是采样部分数据进行预测，那有可能预测错了，导致走全表扫描。
- MySQL的索引都是排好序的。如果区分度高排序越快，区分度越低，排序慢；
2、慢SQL如何定位呢？
慢SQL的监控主要通过两个途径：
- 慢查询日志 ：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。
- 服务监控 ：可以在业务的基建中加⼊对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。
-- 查看慢查询日志配置信息 show variables like ‘slow_query%’; -- 开启慢查询日志 set global slow_query_log = on;
3、explain执行计划？ ⚡
对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。
explain的作用：
- 描述 MySQL 如何执行查询操作、执行顺序，使用到的索引，以及 MySQL 成功返回结果集需要执行的行数。
- 可以帮助我们分析 select 语句，让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器能够更好的工作。
语法
explain + select 语句;
在执行计划中，我们重点关注以下几列：
1、ID列： 一组数字，表示 sql 语句中 select 的执行顺序，有几个 select 就有几个 id，按照 select 出现的顺序呈现结果。
- id 相同，执行顺序由上而下；
- id 不同，序号会递增。值越大优先级越高，就越先执行。
2、select_type 列：查询语句的类型
- simple 简单查询；
- primary 复杂查询；
- subquery 子查询，在 select 中的子查询（不在 from 子句中）；
- derived 衍生查询，在 from 子句中子查询，MySQL 会将结果存放在一个临时表中，也称为派生表（derived 的英文含义）
3、type 列：表关联类型或访问类型，重要的一列，是判断查询是否高效的一句：也就是 MySQL 决定如何查找表中的行就看这个列。
type类型 |
解释 |
ALL |
全表扫描，性能极差 |
index |
全索引扫描，跟 ALL 差不多，不同的是 index 是扫描整棵索引树，比 ALL 要快些。 |
range |
范围扫描，通常出现在 in (), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。 |
ref |
索引查找，不使用唯一索引，使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。 |
eq_ref |
最多只返回一条符合条件的记录。在使用唯一性索引或主键查找时会出现该值，非常高效。 |
const/system |
该表至多有一个匹配行，在查询开始时读取，或者该表是系统表，只有一行匹配。其中 const 用于在和 primary key 或 unique 索引中有固定值比较的情形。 |
- ALL 是最差的，system 是最好的，性能最佳，阿里巴巴开发规约中要求最差也得到 range 级别，而不能有 index、ALL。
4、Extra 列：额外信息，也非常重要
- Using index：使用覆盖索引，表示查询索引就可查到所需数据，不用回表，说明性能不错。
- Using where：在存储引擎检索行后再进行过滤，就是先读取整行数据，再按 where 条件进行取舍。
- Using temporary：mysql 需要创建一张临时表来处理查询，一般是因为查询语句中有排序、分组、和多表 join 的情况，一般是要进行优化的。
- Using filesort：文件排序，一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O到磁盘进行排序，效率较低
4、MySQL优化 ⚡
索引
- 尽量使用 覆盖索引 进行查询，避免 回表 带来的性能损耗。（一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"）
- 组合索引符合最左匹配原则；
- 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘lO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改;
- 尽量避免索引失效
- 索引建立原则:
- 为列的基数大的列创建索引；
- 索引列的类型尽量小；
- 只为用于搜索、排序或分组的列创建索引；
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
- 为了尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况，建议让主键拥有 AUTO_INCREMENT 属性。
- 对于插入、更新、删除等 DML 操作比较频繁的表，不适合建立索引
SQL语句
- 不使用 SELECT * 查询，而是使用 SELECT <字段列表> 查询；
- WHERE 从句中不对列进行函数转换和计算（避免索引失效）；
- 避免数据类型的隐式转换（ 隐式转换会导致索引失效如: where id = '111'）
- 避免使用 JOIN 关联太多的表（ 对于关联操作来说，会产生临时表操作，影响查询效率 ）
锁
- 使用较低的隔离级别 ，因为隔离级别越低，事务请求的锁越少；
- 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行，这样可以大大的减少死锁的机会；
- 尽量控制事务大小，减少锁定资源量和时间长度；
- 数据查询的时候不是必要，不要使用加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能：MVCC只在committed read（读提交）和 repeatable read （可重复读）两种隔离级别 。
数据库结构优化
- 考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。
- 对于经常联合查询的表，可以考虑建立中间表
5、超大分页查询如何优化？
问题:
select * from table where age > 20 limit 1000000,10; -- age有索引
需要加载一百万条数据，然后基本上全部丢弃，取10条。
说明： MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
如何解决？
利用延迟关联或者主键阈值法优化超多分页场景。
- 延迟关联法
- 我们先查询出符合要求的主键( 由于查询的字段有索引，该索引的叶子节点就是主键，通过索引覆盖我们可以省去一次回表操作)
然后再通过主键索引查询数据，这就省去了遍历数据找初始位置数据的过程。
select * from table where id in (select id from table where age > 20)limit 1000000,10;
2. 主键阈值法
- 如果你的主键是自增的，那么就可以通过条件推算出符合条件的主键最大值&最小值(这里也是通过索引覆盖省去了一次回表操作)
然后再根据阈值，取数据即可，同样省去了遍历数据找初始位置数据的过程 。
select * from table where id >= (select id from table where age > 20 limit 1000000, 1) limit 10;
总结： 无论是延迟关联法，还是主键阈值法。思想都是一样的，先把符合条件的主键找到，然后通过主键去定位符合条件的数据，这里优化了2个点：
- 通过索引覆盖避免了回表;
- 通过主键直接定位数据的方法，省去了在数据集中查询初始位置的过程。
当然还有其他的解决方法，比如说我们可以将数据可预测性的提前缓存到Redis中，等到查询时，直接返回即可。
6、为什么select * 会导致查询效率低？
- 不需要的列会增加数据传输时间和网络开销：
- 用SELECT * 数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
- 增大网络开销，* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。如果DB和应用程序不在同一台机器，这种开销非常明显。即使 MySQL 服务器和客户端是在同一台机器上，使用的协议还是 tcp，通信也是需要额外的时间。
- 对于无用的大字段，如 varchar、blob、text，会增加 io 操作：
- 如果记录中包含超过 728 字节的数据，MySQL 将这些数据存储在一个额外的位置，并在记录中保存一个指向这些数据的指针。这意味着在读取记录时，MySQL 需要执行额外的一次 I/O 操作来获取超过 728 字节的数据。
- 失去MySQL优化器“覆盖索引”策略优化的可能性：
- SELECT * 杜绝了覆盖索引的可能性，而基于MySQL优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式。
- 如果用户使用select *，获取了不需要的数据，则首先通过非聚簇索引过滤数据，然后再通过聚集索引获取所有的列，这就多了一次b+树查询，速度必然会慢很多。
- 由于辅助索引的数据比聚集索引少很多，很多情况下，通过辅助索引进行覆盖索引（通过索引就能获取用户需要的所有列），都不需要读磁盘，直接从内存取，而聚集索引很可能数据在磁盘（外存）中（取决于buffer pool的大小和命中率），这种情况下，一个是内存读，一个是磁盘读，速度差异就很显著了，几乎是数量级的差异。
