Mysql的优化（一）

亦凉 2024-03-26 12:06 59阅读 0赞

**理解SQL优化原理 ，首先要搞清楚SQL执行顺序：**

### SELECT语句 ###

####    语法顺序： ####

1.  SELECT
2.  DISTINCT <select\_list>
3.  FROM <left\_table>
4.  <join\_type> JOIN <right\_table>
5.  ON <join\_condition>
6.  WHERE <where\_condition>
7.  GROUP BY <group\_by\_list>
8.  HAVING <having\_condition>
9.  ORDER BY <order\_by\_condition>

#### 执行顺序： ####

1.  FROM<表名> ——选取表，将多个表数据通过笛卡尔积变成一个表。
2.  ON<筛选条件> ——对笛卡尔积的虚表进行筛选
3.  JOIN <join, left join, right join...>
4.  <join表> ——指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
5.  WHERE<where条件>—— 对上述虚表进行筛选
6.  GROUP BY<分组条件> ——分组
7.  <SUM()等聚合函数>—— 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
8.  HAVING<分组筛选> ——对分组后的结果进行聚合筛选
9.  SELECT<返回数据列表> —— 返回的单列必须在group by子句中，聚合函数除外
10. DISTINCT数据除重
11. ORDER BY<排序条件> —— 排序
12. LIMIT<行数限制>

###  ###

### **1.为什么要优化** ###

*  系统的吞吐量瓶颈往往出现在数据库的访问速度上
 *  随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
 *  数据是存放在磁盘上的，读写速度无法和内存相比

### **2.如何优化** ###

*  设计数据库时：数据库表、字段的设计，存储引擎
 *  利用好MySQL自身提供的功能，如索引等
 *  横向扩展：MySQL集群、负载均衡、读写分离
 *  SQL语句的优化（收效甚微）

### **3.字段设计** ###

> 字段类型的选择，设计规范，范式，常见设计案例

#### **原则：尽量使用整型表示字符串** ####

**存储IP**

> `INET_ATON(str)`，address to number
> 
> `INET_NTOA(number)`，number to address

### **4.MySQL内部的枚举类型（单选）和集合（多选）类型** ###

但是因为维护成本较高因此不常使用，使用**关联表**的方式来替代**`enum`**

#### **原则：定长和非定长数据类型的选择** ####

> decimal不会损失精度，存储空间会随数据的增大而增大。

> double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text

### **5.金额** ###

> 对数据的精度要求较高，小数的运算和存储存在精度问题（不能将所有小数转换成二进制）

**定点数decimal**

`price decimal(8,2)`有2位小数的定点数，定点数支持很大的数（甚至是超过`int,bigint`存储范围的数）

**小单位大数额避免出现小数** 元->分

### **6.字符串存储** ###

定长`char`，非定长`varchar、text`（上限65535，其中`varchar`还会消耗1-3字节记录长度，而`text`使用额外空间记录长度）

#### **原则：尽可能选择小的数据类型和指定短的长度** ####

#### **原则：尽可能使用 not null** ####

非`null`字段的处理要比`null`字段的处理高效些！且不需要判断是否为`null`。

`null`在MySQL中，不好处理，存储需要额外空间，运算也需要特殊的运算符。如`select null = null`和`select null <> null`（`<>`为不等号）有着同样的结果，只能通过`is null`和`is not null`来判断字段是否为`null`。

> #### 如何存储？ ####
> 
> MySQL中每条记录都需要额外的存储空间，表示每个字段是否为`null`。因此通常使用特殊的数据进行占位，比如`int not null、 default 0`、`string not null 、default ‘’`

#### **原则：字段注释要完整，见名知意** ####

#### **原则：单表字段不宜过多** ####

二三十个就极限了

#### **原则：可以预留字段** ####

> 在使用以上原则之前首先要满足业务需求

### **7.关联表的设计** ###

> 外键 `foreign key`只能实现一对一或一对多的映射

*  #### **一对多：** 使用外键 ####
 *  #### **多对多：** 单独新建一张表将多对多拆分成两个一对多 ####
 *  #### **一对一** ： **如商品的基本信息（`item`）和商品的详细信息（`item_intro`），通常使用相同的主键或者增加一个外键字段（`item_id`）** ####

### 8.三大范式原理 ###

通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解

1.  第一范式: 1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解; (只要是关系型数据库都满足1NF)
2.  第二范式: 2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性;
3.  第三范式: 3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。

> **表的范式，是首先符合1NF, 才能满足2NF,进一步满足3NF**

后面会详细出一篇三大范式的文章~

### 9.避免不走索引的场景 ###

**1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。**

SELECT * FROM s WHERE username LIKE '%双%'

优化方法：尽量在字段后面使用模糊查询。

如果需求是要在前面使用模糊查询

*  数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
 *  `当表数据量较少（几千条儿那种），可以直接用like '%xx%'。`

**2. 尽量避免使用in 和not in，会导致引擎走全表扫描。**

SELECT * FROM s WHERE id IN (2,3)

优化方法：如果是连续的查询可以使用between...and...代替

SELECT * FROM s WHERE id BETWEEN 2 AND 3