MySQL（五）------数据库性能优化

灰太狼 2023-07-14 13:59 36阅读 0赞

## 1、SQL优化 ##

[MySQL（四）------使用慢查询日志定位SQL][MySQL_------_SQL]

[MySQL（三）------MySQL通过explain 命令查看SQL语句执行效率][MySQL_------MySQL_explain _SQL]

**1、避免select \* 写法**

执行SQL时优化器需要将 \* 转成具体的列；每次查询都要回表，不能走覆盖索引。

**2、避免复杂SQL语句**

提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理

**3、使用like进行模糊查询时应避免左模糊查询**

#左模糊查询会导致全表扫描
    select*from table where username like ‘%kewordk%’

**4、避免在 where 子句中对字段进行 null 值判断**

导致引擎放弃使用索引而进行全表扫描

select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

**5、避免在 where 子句中使用 != 或 <> 操作符**

导致引擎放弃使用索引而进行全表扫描。

在mysql数据库中where 子句中使用 != 或 <> 操作符，引擎不会放弃使用索引。

**6、避免在 where 子句中使用 or 来连接条件**

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描

**7、in 和 not in 也要慎用，否则会导致全表扫描**

在mysql数据库中where 子句中对索引字段使用 in 和 not in操作符，引擎不会放弃使用索引。

在mysql数据库中where 子句中对不是索引字段使用 in 和 not in操作符，会导致全表扫描。

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

导致引擎放弃使用索引而进行全表扫描

select id from t where num/2 = 100
    
    #可以优化为
    select id from t where num = 100*2

**9、避免在where子句中对字段进行函数操作**

导致引擎放弃使用索引而进行全表扫描

select id from t where substring(name,1,3) = ’abc’

**10、 where 及 order by 涉及的列上最好建立索引**

对查询进行优化，要尽量避免全表扫描

**11、 where 及 order by 涉及到复合索引字段，应注意顺序性**

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

**12、Update 语句，如无必要，不要全修改**

如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

**13、只需要一条数据时使用LIMIT 1**

若已经知道结果只有一条的时候，一定要使用limit 1 ，这样一来，MySQL在查询到一条数据之后，会立即停止搜索

## 2、创建索引 ##

**1、索引并不是越多越好**

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

**2、应尽可能的避免更新 clustered 索引数据列**

聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）

> 聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据物理排列顺序无关  
> 聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储只能有一个顺序

因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

## 3、数据库表设计优化 ##

**1、数据库表字段类型**

尽量用int型，固定长度用char，使用varchar的范围尽量贴合实际，能用tinyint就不要用int和smallint，最好给字段设置默认值，默认值不为null；

**2、字段尽量避免空（NULL）**

MySQL难以优化了使用了可空列的查询，它会使索引、索引统计和值更加复杂。

并且可空列需要更多的存储空间

即使要在表中存储可为空的字段，也是有办法不使用NULL的，可以考虑使用0，特殊值或字符串来代替它。

**3、尽量少用text类型，非用不可时最好考虑分表**

**4、尽量使用数字型字段**

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

## 5、数据库性能优化，如数据库配置优化、硬件配置 ##

MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。

表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。

行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。

根据以上看来，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本中默认存储引擎。

加大物理内存，提高文件系统性能

## 6、数据库架构扩展 ##

随着业务量越来越大，单台数据库服务器性能已无法满足业务需求

**1、主从复制与读写分离**

因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作

**2、分库、分表**

> 分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。如果业务量很大，还可将切分后的库做主从架构，进一步避免单个库压力过大。

> 数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你就该考虑是否把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。

[MySQL_------_SQL]: https://blog.csdn.net/qq_29914837/article/details/104867630
[MySQL_------MySQL_explain _SQL]: https://blog.csdn.net/qq_29914837/article/details/104867485

发表评论取消回复

表情：

评论列表（有 0 条评论，36人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql数据库之性能优化

一、架构设计层面优化。 MySQL是一个磁盘IO访问非常频繁的关系型数据库，在高并发和高性能的场景中，MySQL数据库必然会承受巨大的并发压力。在此时，我们优化的方式主要可以

迷南。/ 2023年10月11日 20:54/ 0 赞/ 138 阅读

相关 MySQL性能优化(五)：分表

一：为什么要分表？如果一个表的每条记录的内容很大，那么就需要更多的IO操作，如果字段值比较大，而使用频率相对比较低，可以将大字段移到另一张表中，当查询不查大

浅浅的花香味﹌/ 2023年07月21日 05:44/ 0 赞/ 88 阅读

相关 MySQL（五）------数据库性能优化

1、SQL优化 [MySQL（四）------使用慢查询日志定位SQL][MySQL_------_SQL] [MySQL（三）------MySQL通过explain

灰太狼/ 2023年07月14日 13:59/ 0 赞/ 37 阅读

相关 MySQL编程优化篇（五）优化数据库对象

优化表的数据类型在MySQL中，可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用

青旅半醒/ 2023年06月23日 10:58/ 0 赞/ 51 阅读

相关 MYSQL-mysql数据库性能及查询优化

MYSQL-mysql数据库性能及查询优化 1. 查询下mysql服务器的状态 / mysql数据库的性能在mysql长时间运行，并且有大量用户进行频繁操作时得

水深无声/ 2022年10月29日 09:18/ 0 赞/ 120 阅读

相关熟悉mysql数据库设计和性能优化_Mysql数据库性能优化

Mysql数据库性能优化，可以从下面三点入手：数据库设计 SQL语句优化架构优化一.数据库设计优化 1.适度的违反范式，适度遵循三大范式就会带来查询时经

小鱼儿/ 2022年10月28日 03:48/ 0 赞/ 343 阅读

相关 MySQL数据库inset性能优化

我们在使用中MySQL的时候难免会遇到大批量数据inset的情况，通常最简单的方法就是写一个insert，然后通过循环给变量赋值，批量插入数据库： //s

小灰灰/ 2022年08月18日 01:51/ 0 赞/ 316 阅读

相关 MySQL数据库性能优化-利用sql执行计划预先查看执行性能（五）

总结之前的内容，MySQL数据库性能优化的途径有以下几个方式 1. 提高硬件方面的配置，比如SSD固态硬盘等。 2. 选择合适的存储引擎，一般使用Innodb（支持事务处

ゞ浴缸里的玫瑰/ 2022年06月03日 08:27/ 0 赞/ 245 阅读

相关 MySQL性能优化（优化查询、优化数据库结构、优化MySQL服务器）

1) 优化查询（结合本文上16题，explain详解的使用）（1）分析查询语句 ![70][] ![70 1][] ![70 2][] （2）

冷不防/ 2022年05月26日 02:55/ 0 赞/ 446 阅读

相关 MySQL详解（五）：SQL性能优化

SQL性能优化 1.优化简介 MySQL优化，一方面是需要找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的

刺骨的言语ヽ痛彻心扉/ 2022年05月18日 05:34/ 0 赞/ 375 阅读