alleyf

定位慢查询

内因：

• 聚合查询
• 多表查询
• 表数据量过大查询
• 深度分页查询
  表象：
• 页面加载过慢、接口压测响应时间过长（超过 1s）

方案一：开源工具

调试工具：Arthas
运维工具：Prometheus、Skywalking

方案二：MySQL 自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认 10 秒）的所有 SQL 语句的日志如果要开启慢查询日志，需要在 MySQL 的配置文件（/etc/my.cnf）中配置如下信息：

#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动 MySQL 服务器进行测试，查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log

慢 SQL 分析

可以采用 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息

• possible_key 当前 sql 可能会使用到的索引
• key 当前 sql 实际命中的索引
• key_len 索引占用的大小
• Extra 额外的优化建议
  • Using where; Using Index 查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
  • Using index condition 查找使用了索引，但是需要回表查询数据
• type 这条 sql 的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
  • system：查询系统中的表
  • const：根据主键查询
  • eq_ref：主键索引引查询或唯一索引查询
  • ref：索引查询
  • range：范围查询(最差不要低于该类型)
  • index：索引树扫描
  • all：全盘扫描

1. 通过 key 和 key_len 检查是否命中了索引（索引|本身存在是否有失效的情况)
2. 通过 type 字段查看 sql 是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
3. 通过 extra 建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

索引

索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

1. 索引（index）是帮助 MySQL 高效获取数据的数据结构（有序)
2. 提高数据检索的效率，降低数据库的 lO 成本（不需要全表扫描）
3. 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

底层数据结构：B+树

B+Tree 是在 BTree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构.

1. 阶数更多，路径更短
2. 磁盘读写代价 B+树更低，非叶子节点只存储指针，叶子阶段存储数据
3. B+树便于扫库和区间查询，叶子节点是一个双向链表

B 树与 B+树对比:
①：磁盘读写代价 B+树更低；
②：查询效率 B+树更加稳定；
③：B+树便于扫库和区间查询

聚族索引和非聚族索引

聚集索引（ClusteredIndex): 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引 (Secondary Index): 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键,可以存在多个

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引 l。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则 innoDB 会自动生成一个rowid作为隐藏的聚集索引 l。

回表查询

首先根据二级索引查询到主键，然后根据主键去聚簇索引查询目标数据。

覆盖索引

概念：查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

• 使用 id 查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
• 如果返回的列中没有创建索引，有可能会触发回表查询，尽量 避免使用select*

MYSQL 超大(深)分页处理

优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

select * from tb_sku limit 9000000,10
#优化为
select *
from tb_sku t,
(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

索引创建原则

• 先陈述自己在实际的工作中是怎么用的
• 主键索引
• 唯一索引
• 根据业务创建的索引（复合索引）

1. (!) 针对于数据量较大，且查询比较频繁的表建立索引。单表超过 10 万数据（增加用户体验）
2. (!) 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. (!) 尽量使用联合索引一，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. (!) 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效

1. 违反最左前缀法则
   如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引。

2. 范围查询右边的列，不能使用索引
   根据前面的两个字段name，status查询是走索引的，但是最后一个条件address没有用到索引
   

3. 索引列上进行运算操作
   

4. 字符串不加单引号（类型转换则失效（eg：整形->字符串））
   在查询没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效

5. 以%开头的Like模糊查询
   如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

SQL优化经验

1. 表的设计优化（字段设计，类型、长度、冗余）
2. 索引优化(创建原则、失效场景)
3. SQL语句优化
4. 主从复制、读写分离
5. 分库分表(单表数据量超过500w)

• 表的设计优化（参考阿里开发手册《嵩山版》）
  ①比如设置合适的数值（tinyint int bigint），要根据实际情况选择
  ②比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

• SQL语句优化

  1. SELECT语句务必指明字段名称（避免直接使用select*）
  2. SQL语句要避免造成索引失效的写法
  3. 尽量用union all代替unionunion会多一次过滤，效率低
  4. 避免在where子句中对字段进行表达式操作
  5. Join优化 能用inner join 就不用left join right join，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或right join，不会重新调整顺序(少次多量，以较少的连接次数批处理)

• 主从复制、读写分离
  如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。