2021-05-26 [MySQL] MySQL中的那些优化

Mysql中的那些索引和优化

增删改查优化

INSERT语句

1. 当有大量语句要插入时：

将n条

INSERT INTO Table (id,  name, score) VALUES ('1', '张三',78)

改为n/3条

INSERT INTO Table (id,  name, score) VALUES ('1', '张三',78), ('2','李四'，80),('3','王五',90)

这样就节省了解析的时间。

2. 插入时尽量按照主键的顺序执行
3. 考虑使用replace语句代替insert

DELETE语句

1. 使用truncate

truncate和delete的区别：

a. truncate更快，且不会记录mysql日志，无法恢复

b. 若没有外键关联 truncate会先drop table然后创建一个空表，速度很快

c. truncate能重新利用释放的阴盘空间

2. 先删除索引，再删除数据，再重新创建索引

UPDATE语句

1. 尽量不要修改主键

2. 修改VARCHAR类型时，尽量使用相同长度的内容

3. 避免update建有很多索引的列

REPLACE

replace语句等于delete+insert

若表中不存在该主键或唯一索引，则直接插入，若存在则删除后再插入。

查询优化

1. 先使用EXPLAIN语句查看MySQL如何处理你的SQL语句（关注：查询类型、可能索引、使用的索引、读取的行数）

2. 不使用 SELECT *

3. 开启查询缓存

4. 尽量使用连接操作代替子查询（inner/left/right/full join）

5. 慎用 in、not in

6. 尽量使用数字代替字符

分页优化

1. 使用子查询先定位偏移，然后往后查询
2. 如果要分页全部列，则先分页索引列，然后连接/子查询
3. 建立唯一索引，计算偏移值然后使用limit
4. 再大就只能分库分表了

索引优化

创建索引的原则

1. 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询
2. 频繁作为查询条件的字段才创建索引
3. 更新频繁字段不适合创建索引
4. 区分度地的字段不适合做索引
5. 对于text、image、bit的数据类型不要建立索引
6. 外键列要定义索引（否则，当该行数据更新或删除时，外键所在的表会锁主而不是仅仅锁住行）
7. 对 where,on,group by,order by 中出现的列使用索引

表的设计和优化

三大范式

第一范式：字段要求不可再分

第二范式：每一个字段都应该和主键相关

第三范式：每一个字段都应该和主键直接相关

我们应该尽量满足这三大范式

字段属性优化

1. 尽量使用数值型字段，尽量不使用double（精确性有问题，应使用DECIMAL：DECIMAL(P,D)）
2. 建议使用varchar（char固定长度：速度快、varchar可变长度：省空间）
3. 不要使用null（索引不会存储null值）
4. TEXT和BLOB, TEXT用于存储文本数据，BLOB用于存储2进制数据
5. TIMESTAMP（4）代替DATETIME（8）（存储时间戳，4字节，插入时自动为当前系统时间）DATE精确到天（3字节）

库表的拆分

1. 垂直拆分

优点：直观 缺点：会冗余数据，拆分力度有限

2. 水平拆分

优点：拆分力度大 缺点：复杂

范围拆分（业务相关，数据倾斜）

哈希拆分（业务无关，扩容复杂） 一致性哈希 虚拟槽