一文学会MySQL数据库性能优化:创建表、设计表、SQL优化

数据库 MySQL 新闻
数据库的优化肯定是先分析再具体优化,前面已经介绍了在对mysql数据库sql做优化时的一些方法,今天主要从创建表、设计表及具体sql优化几个方面来介绍一些性能优化的方法。

 概述

数据库的优化肯定是先分析再具体优化,前面已经介绍了在对mysql数据库sql做优化时的一些方法,今天主要从创建表、设计表及具体sql优化几个方面来介绍一些性能优化的方法。

[[271829]]

01.创建表时的性能优化

1. 永远为每张表设置一个 ID

每张表都应该设置一个 ID 字段为主键,该主键应为 INT 或 UNSIGNED 类型,并设置上自动增加的 AUTO_INCREMENT 标志。因为使用 VARCHAR 类型的主键,会使得性能下降。

这里,只有一个情况是例外,那就是 “关联表” 的 “外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做 “外键”。比如:有一个 “学生表” 有学生的 ID,有一个 “课程表” 有课程 ID,那么,“成绩表” 就是 “关联表” 了,其关联了学生表和课程表,在成绩表中,学生 ID 和课程 ID 叫 “外键” 其共同组成主键。

2. 为搜索字段建索引

这个简单来说就是创建表时,如果后面针对这个表的查询总会涉及到某个字段,或者在代码里面写好了的字段,这种就可以考虑去建索引。

3. 使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段,比如 “国家”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。

ENUM 是 MySQL 数据库特有的字段类型,使用后会影响迁移到其它数据库。所以,如果以后要改数据库的情况,一定要慎用。

4. 尽可能的使用 NOT NULL

应该总是让你的字段保持 NOT NULL,这样相对比较节省空间(NULL 也是需要空间的)。

5. 把IP地址存成 UNSIGNED INT

如果使用整形来存放 IP 而不是 VARCHAR(15) 字段,节省了很多的空间(需要写一个 IP 转换的函数)。

6.必须使用UTF8字符集

万国码,无需转码,无乱码风险,节省空间

02.设计表时的性能优化

1. 选择正确的存储引擎

一文学会mysql数据库性能优化--创建表、设计表、sql优化

MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要 update 一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。

相对来说支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高,所以大多数情况下还是用innodb引擎吧。

2. 固定长度的表会更快

表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能,因为 MySQL 搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。

3. 垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(因为目前的公司经常一张表有很多个字段,太过复杂,这个也是后面需要去做分割的)

例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大量的时候,对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。

例二: 有一个叫 “last_login” 的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。

另外,需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。

03.优化 SQL 语句

1. 使用查询缓存

查看是否开启缓存:

mysql> select @@query_cache_type;

一文学会mysql数据库性能优化--创建表、设计表、sql优化

开启缓存,修改 my.cnf,在末尾加入,重启MySQL生效:

query_cache_type = 1query_cache_size = 600000

启用MySQL查询缓存可以极大地减低数据库服务器的CPU使用率,实际使用情况是:开启前CPU使用率120%左右,开启后降到了10%。不过使用查询的缓存的限制非常多。当使用场景中以只读为主,很少有更新的情况时,再考虑开启查询缓存。

2. 当只要一行数据时使用 LIMIT 1

在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL 数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

3. 在 JOIN 表的时候使用相当类型的例,并将其索引

如果有很多 JOIN 的操作,JOIN 的字段应该加索引,同时保证这些字段的类型一致。

4. 避免 SELECT *

从数据库里读出越多的数据,那么查询就会变得越慢。所以,应该养成需要什么就取什么的好的习惯。

5. 拆分大的 DELETE 或 INSERT 语句

如果你需要在一个在线的网站上去执行一个大量的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

执行这种大量的 DELETE 和 INSERT,可以分成几部分执行,每执行一部分就暂停一下再执行。

04.其它

1. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

查看 rows 列可以让我们找到潜在的性能问题。

2. 从 PROCEDURE ANALYSE() 取得建议

PROCEDURE ANALYSE() 会让 MySQL 去分析字段和其实际的数据,并会提供一些有用的建议(只是建议)。只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。

mysql>select * from mysql.user procedure analyse();

一文学会mysql数据库性能优化--创建表、设计表、sql优化
责任编辑:华轩 来源: 今日头条
相关推荐

2020-08-03 08:01:50

爬虫技巧

2022-08-01 14:59:57

Web前端后端

2018-03-30 14:30:10

数据库SQL语句性能优化

2018-03-30 13:59:22

数据库SQL语句性能优化

2023-03-13 00:01:10

数据库性能MySQL

2021-06-28 14:13:34

OOM内存事故

2020-04-20 10:47:57

Redis数据开发

2019-09-17 08:23:35

MySQL数据库容量

2020-12-22 10:02:53

ZabbixMySQL数据库

2020-08-31 06:54:37

注解脱敏ELK

2021-06-26 09:26:01

Jupyter主题目录

2013-09-17 10:32:08

Android性能优化数据库

2023-09-11 08:38:38

Oracle数据库

2021-02-06 13:45:59

SQL子查询数据库

2021-04-30 07:33:35

效率提升技巧

2021-04-28 07:22:13

HiveJson数组

2023-07-12 08:55:16

PawSQL数据库

2021-03-29 08:24:18

KubeadmKubernetes1运维

2009-06-30 22:31:23

关键参数MySQL性能优化

2011-03-03 17:56:52

MySQL数据库优化
点赞
收藏

51CTO技术栈公众号