SQL优化2023版最新干货总结 - MySQL_方案一:开源工具 调试工具:arthas 运维工具:prometheus、skywalking

---

前言

---

一、Mysql如何定位慢查询

  Mysql一搬造成慢查询一般在聚合查询，多表查询，表数据量过大查询，深度分页查询几种情况下造成，导致页面加载过慢，接口测试响应时间过长。

方法一：开源工具

• 调试工具：Arthas
• 运维工具：Prometheus 、Skywalking
  -
  方法二：MySQL自带慢日志
  慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志
  如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

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

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

/var/lib/mysql/localhost-slow.log。

二、Mysql使用(Explain)查询并分析执行计划

聚合查询，多表查询，表数据量过大查询导致导致的慢SQL，我们根据执行计划来分析和查询。

一个SQL语句执行很慢, 如何分析

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

语法：
-直接在select语句之前加上EXPLAIN 或者 DESC关键字
Explain select 字段名字 FROM 表名 where 条件；

• possible_key 当前sql可能会使用到的索引
• key 当前sql实际命中的索引
• key_len 索引占用的大小
• Extra 额外的优化建议

type不同值的意思（2种主要导致的原因）：

• type 这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、 index、all
• system：查询系统中的表
• const：根据主键查询
• eq_ref：主键索引查询或唯一索引查询
• ref：索引查询
• range：范围查询
• index：索引树扫描
• all：全盘扫描

三、Mysql索引

什么是索引：

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

索引的优点：
磁盘IO效率低

存储格式：key（索引列的值）-value（行记录）

1、存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

MySQL体系结构

存储引擎特点：

MYSQL支持的存储引擎有哪些：

在mysql中提供了很多的存储引擎，比较常见有InnoDB、MyISAM、Memory

• InnoDB存储引擎是mysql5.5之后是默认的引擎，它支持事务、外键、表级锁和行级锁。
• MyISAM是早期的引擎，它不支持事务、只有表级锁、也没有外键，用的不多。
• Memory主要把数据存储在内存，支持表级锁，没有外键和事务，用的也不多。

InnoDB

介绍
  InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。
特点
  DML操作遵循ACID模型，支持，事务
  行级锁，提高并发性能。
  支持外键，FOREIGN KEY约束，保证数据的完整性和正确性
文件
  xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
  xxx.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）

2、索引底层数据结构

MySQL默认使用的索引底层数据结构是B+树。再聊B+树之前，我们先聊聊二叉树和B树

数据结构对比：




innodb的空间大小是16384字节=16kb

有序性和二叉树

例如：索引8个字节，bigint是6字节，16kb/14字节=1170索引 （主键索引）（高度为3的B+树）总共是2000W索引（1170X1170x16个索引元素（16KB一个索引占用1KB））

3、聚簇和非聚簇索引

聚集索引选取规则:

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

索引覆盖：

索引下推5.6新增的(默认开启的)

id（主键）name,age,gender
(name,age)是组合索引
select * from table where name=‘zhangsan’ and age = 10;
没有索引下推之前：先根据name的值去存储引擎中拉取所有符合条件的数据，将数据返回给server层之后再根据age去做数据的筛选
有索引下推之后：直接根据name，age的值再存储引擎中做数据筛选，把符合的结果直接返回,减少了数据回表

4、索引创建原则

原则：

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

5、索引失效场景

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

2.违法最左前缀法则 ， 索引失效（回表）：

3.不要在索引列上进行运算操作， 索引将失效。

4.字符串不加单引号，造成索引失效。

  由于，在查询是，没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效

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

四、Mysql优化经验

• 表的设计优化
• 索引优化(参考索引)
• SQL语句优化
• 主从复制、读写分离
• 分库分表

设计优化：

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

SQL语句优化：

1. SELECT语句务必指明字段名称（避免直接使用select * ）
2. SQL语句要避免造成索引失效的写法
3. 尽量用union all代替union union会多一次过滤，效率低
4. 避免在where子句中对字段进行表达式操作
5. Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动

主从复制、读写分离:

分库分表：
分库分表的时机：

单表的数据量达1000W或20G以后
优化已解决不了性能问题（主从读写分离、查询索引…）
IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

拆分策略:

（1）垂直拆分-垂直分库:
以表为依据，根据业务将不同表拆分到不同库中。
特点：

1. 按业务对数据分级管理、维护、监控、扩展
2. 在高并发下，提高磁盘IO和数据量连接数

（2）垂直拆分-垂直分表:
以字段为依据，根据字段属性将不同字段拆分到不同表中。
特点：
1，冷热数据分离
2，减少IO过渡争抢，两表互不影响

（3）水平拆分-水平分库:
将一个库的数据拆分到多个库中。
特点：

1. 解决了单库大数量，高并发的性能瓶颈问题
2. 提高了系统的稳定性和可用性

（3）水平拆分-水平分表:
将一个表的数据拆分到多个表中(可以在同一个库内)。
特点：

1. 优化单一表数据量过大而产生的性能问题;
2. 避免IO争抢并减少锁表的几率;

分库之后的问题：

• 分布式事务一致性问题
• 跨节点关联查询
• 跨节点分页、排序函数
• 主键避重

分库分表中间件：

• sharding-sphere
• mycat

MYSQL超大分页处理：

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

---

总结