您现在的位置是：技术博客 > 数据库数据库

MYSQL优化

Lucas2023-02-28 20:37【代码】427人已围观

简介Mysql优化

数据库优化可以从架构优化，硬件优化，DB 优化，SQL 优化四个维度入手。

此上而下，位置越靠前优化越明显，对数据库的性能提升越高。我们常说的 SQL 优化反而是对性能提高最小的优化。
这里先从最细的SQL优化来说。

### 1、字段

#### 1.1、类型设置

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。

从快到慢排序如下：

第一：tinyint，smallint，mediumint，int，bigint
第二：char，varchar
第三：NULL

举几个正确的例子：

（1）“省份”或者“性别”，可以设置为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。

（2）“房屋描述”或者“产品详情”尽量使用varchar来代替text。但是当varchar大于某些数值的时候，其会自动转换为text，大概规则如下：

大于varchar（255）变为 tinytext，大于varchar（500）变为 text，大于varchar（20000）变为 mediumtext。

再举几个错误的例子

#### 1.2、宽度设置

（1）已知存储的是定长字符数据，尽量使用char（长度），例如：身份证号可以设为char(18)，手机号，可以设置为char(11)，真实姓名char(4)。

（2）年龄，可以设为tinyint(3) unsigned，长度（0 ~ 255）

（3）金额，可以设为decimal(10,2) unsigned，长度（0 ~ 99999999）

（4）时间，可以设尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。也可以使用int(10)来存储，优点是存储小，比较方便，缺点是维护时不够直观，读取时通常需要转化。

timestamp 记录经常变化的更新 / 创建 / 发布 / 日志时间 / 购买时间 / 登录时间 / 注册时间等，时区自动处理，比如说做海外购或者业务可能拓展到海外。

datetime 记录固定时间如服务器执行计划任务时间 / 健身锻炼计划时间等，在任何时区都是需要一个固定的时间要做某个事情。超出 timestamp 的时间，如果需要时区必须记得时区处理。

#### 1.3、非NULL设置

在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。

设置null的劣势：

（1）不好比较，null是一种类型，比较时只能用专门的is null 和 is not null来比较碰到运算符,一律返回null。

（2）效率不高,影响提高索引效果.
因此,在建表时可以使用 not null  default， ‘’或0来实现。

### 2、索引
索引是提高数据库性能的常用方法，用索引检索特定的行，速度相较没有索引快很多，尤其是在查询语句当中包含有MAX()，MIN()和ORDER BY这些命令的时候，性能提高更为明显。

该对那些字段进行索引？

#### 2.1、唯一性索引

唯一性索引指的是保证数据在表中的唯一性，唯一性约束可以放在一个或者多个列上。

举几个例子如下：

（1）给合同表的合同编号创建唯一索引，以保证合同编号不会重复：alter table table_contract add unique key `contract_number` (`contract_number`);

（2）给管理员的所属单位和管理员登录账号创建一个唯一索引，以保证同一个单位登录的用户名必须是唯一的alter table table_admin add unique key `inst_admin_name` (`inst_id`,`name`);

### 2.2、普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一的作用就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。

一般来说，索引应该建立在那些将用于join，where判断和order by排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引，对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。

实际案例如下：

```
SELECT
	`con`.contract_number, `h`.`house_number`,
	`h`.`address`,
	`h`.`house_door`,
	`h`.`inst_id`,
	`h`.`use_id`,
	`t`.`tenant_type`,
	`t`.`tenant_number`
FROM
	`zhwf_deal_contract` `con`
LEFT JOIN `zhwf_archive_house` `h` ON `con`.`house_id` = `h`.`house_id`
LEFT JOIN `zhwf_archive_tenant` `t` ON `con`.`tenant_id` = `t`.`tenant_id`
LEFT JOIN `zhwf_archive_tenant_sign_record` `r` ON `con`.`sign_record_id` = `r`.`sign_record_id`
WHERE
	(
		`h`.`inst_id` IN (1, 2, 3, 4, 5, 6, 7, 9, 10, 12, 13)
		AND `contract_sign_img` <> ''
		AND `contract_status` <> 3
		AND `change_status` = 1
	)
AND `con`.`delete_time` = 0
ORDER BY
	`con`.`contract_id` DESC
LIMIT 0,
 10;

alter table `kj_data`.`zhwf_deal_contract`
add index index_0(  `contract_sign_img`,
    `contract_status`, `delete_time`, `change_status`);

```

#### ### 2.3、主键索引

主键索引是一种特殊的唯一索引，不允许值重复或者值为空，创建主键索引通常使用 PRIMARY KEY 关键字。理论上讲每张表都必须创建主键。单用主键查询的速度是最快的。

#### 2.4、全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

案例如下：

CREATE FULLTEXT INDEX index_user ON tb_user(name);
给用户表的姓名字段创建全文索引，name 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

### 3、查询语句

#### 3.1、避免使用select *
使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

避免回表查询：使用覆盖索引(也是普通索引)，所谓覆盖索引就是指索引中包含了查询中的所有字段。

#### 3.2、避免复杂SQL语句
提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理。

#### 3.3、多表关联查询
在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

#### 3.4、多表使用表的别名
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

#### 3.5、where替换HAVING
避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。where和having的区别：where后面不能使用组函数。

#### 3.6、union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据

#### 3.7、尽量少用or
or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

#### 3.8、union all代替union
在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

#### 3.9、分段查询

在MySQL中，在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。

这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。扫描的行数成百万级以上的时候就可以使用分段查询。

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

#### 3.11、不建议使用%前缀模糊查询
在LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

#### 3.12、IN包含的值不应过多
在MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。

#### 3.13、分组统计可以禁止排序
默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

#### 3.14、避免随机取记录
MySQL不支持函数索引，会导致全表扫描。

很赞哦！ ( 0 )

上一篇：Mysql用户及权限

下一篇：mysql重置主键id