MySql优化方法

原创

好有is 2023-12-15 09:34:09 博主文章分类：数据库 ©著作权

文章标签 字段数据 SQL 文章分类 Java 后端开发 yyds干货盘点

©著作权归作者所有：来自51CTO博客作者好有is的原创作品，请联系作者获取转载授权，否则将追究法律责任

(MySql优化方法)

硬件配置

对磁盘进行扩容
将机械硬盘换为SSD
把CPU的核数往上提一些，增强数据库的计算能力
内存扩容，让Bufffer Pool能吃进更多数据

成本高，见效快

参数配置

保证从内存读取
数据预热
降低磁盘的写入次数
- 增大redo log，减少落盘次数
- 通用查询日志、慢查询日志可以不开，binlog可开启
- 写redo log策略 innodb_flush_log_at_trx_commit 设置为 0 或 2
  
  0：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer --> OS cache，刷盘 OS cache --> 磁盘文件），最多丢失 1 秒数据
  
  1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作
  
  2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作
系统调优参数
- back_log
- wait_timeout
- max_user_connection
- thread_concurrency
- skip_name_resolve
- key_buffer_size
- innodb_buffer_pool_size
- innodb_additional_mem_pool_size
- innodb_log_buffer_size
- query_cache_size
- read_buffer_size
- sort_buffer_size
- read_buffer_size
- read_rndn_buffer_size
- record_buffer
- thread_cache_size
- table_cache

表结构设计

设计聚合表
设计冗余字段
分表

分表分为垂直拆分和水平拆分两种。

垂直拆分，适用于字段太多的大表，比如：一个表有100多个字段，那么可以把表中经常不被使用的字段或者存储数据比较多的字段拆出来。

水平拆分，比如：一个表有5千万数据，那按照一定策略拆分成十个表，每个表有500万数据。这种方式，除了可以解决查询性能问题，也可以解决数据写操作的热点征用问题。
字段的设计
- 使用可以存下数据最小的数据类型，合适即可
- 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED；
- VARCHAR的长度只分配真正需要的空间；
- 对于某些文本字段，比如"省份"或者"性别"，使用枚举或整数代替字符串类型；在MySQL中， ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多
- 尽量使用TIMESTAMP而非DATETIME；
- 单表不要有太多字段，建议在20以内；
- 尽可能使用 not null 定义字段，null 占用4字节空间，这样在将来执行查询的时候，数据库不用去比较NULL值。
- 用整型来存IP。
- 尽量少用 text 类型，非用不可时最好考虑拆表。

SQL语句及索引（重要）

注：索引并不是越多越好，要根据查询有针对性的创建

索引创建和使用原则

单表查询：哪个列作查询条件，就在该列创建索引
多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
不要对索引列进行任何操作（计算、函数、类型转换）
索引列中不要使用 !=，<> 非等于
字符字段只建前缀索引，最好不要做主键；
尽量不用UNIQUE，由程序保证约束
不用外键，由程序保证约束
索引列不要为空，且不要使用 is null 或 is not null 判断
索引字段是字符串类型，查询条件的值要加''单引号，避免底层类型自动转换

使用EXPLAIN分析SQL

select_type：查询类型

SIMPLE 简单查询
PRIMARY 最外层查询
UNION union后续查询
SUBQUERY 子查询

type：查询数据时采用的方式

ALL 全表**（性能最差）**
index 基于索引的全表
range 范围（< > in）
ref 非唯一索引单值查询
const 使用主键或者唯一索引等值查询

possible_keys：可能用到的索引

key：真正用到的索引

rows：预估扫描多少行记录

key_len：使用了索引的字节数

Extra：额外信息

Using where 索引回表
Using index 索引直接满足条件
Using filesort 需要排序
Using temprorary 使用到临时表

重点关注的是type，最直观的反映出SQL的性能

SQL语句尽可能简单

一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。

对于连续数值，使用BETWEEN而不是IN

SQL 语句中 IN 包含的值不应过多

SELECT 语句必须指明字段名称

SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性。

当只需要一条数据的时候，使用 limit 1

limit 相当于截断查询。

例如：对于select * from user limit 1; 虽然进行了全表扫描，但是limit截断了全表扫描，从0开始取了1条数据。

排序字段加索引

如果限制条件中其他字段没有索引，尽量少用or

尽量用 union all 代替 union

union和union all的差别就在于union会对数据做一个distinct的动作，而这个distanct动作的速度则取决于现有数据的数量，数量越大则时间也越慢。而对于几个数据集，要确保数据集之间的数据互相不重复，基本是O(n)的算法复杂度。

区分 in 和 exists、not in 和 not exists

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

使用合理的分页方式以提高分页的效率

避免使用 % 前缀模糊查询

例如：like '%name'或者like '%name%'，这种查询会导致索引失效而进行全表扫描。但是可以使用like 'name%'，这种会使用到索引。

避免在 where 子句中对字段进行表达式操作

这种不会使用到索引：

select user_id,user_project from user_base where age*2=36;

可以改为：

select user_id,user_project from user_base where age=36/2;

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

避免隐式类型转换

where 子句中出现的 column 字段要和数据库中的字段类型对应

必要时可以使用 force index 来强制查询走某个索引

使用联合索引时注意范围查询

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

某些情况下，可以使用连接代替子查询

使用JOIN的优化

使用小表驱动大表，例如使用inner join时，优化器会选择小表作为驱动表

小表驱动大表，即小的数据集驱动大的数据集

#当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B)  
#当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)