源码时代软件测试干货|史上最全的mysql数据库优化方法，软件测试人手必备！史上最全的mysql数据库优化方法，软件测试人手必备！

亦凉 2024-04-17 05:28 24阅读 0赞

**1. 课程介绍**

Ø 1.介绍什么是mysql优化

Ø 2.mysql优化方法

Ø 3.Mysql索引的使用

Ø 4.分表技术

**2. mysql优化概述**

概述: 前面我们学习了页面静态化和redis,它们是通过不操作mysql数据库达到提速目的。但是某些功能是一定要操作数据库的，这就要求我们必须对mysql本身进行优化。

**mysql数据库优化的常见方法****:**

1. 表的设计要合理(满足3NF) 3范式

2. 创建适当索引\[主键索引|唯一索引|普通索引|全文索引|空间索引\]

3. 对SQL语句优化---->定位慢查询(explain)

4. 使用分表技术(重点【水平分表，垂直分表】), 分区技术(了解)

5. 读写分离(配置)

6. 创建适当存储过程，函数，触发器

7. 对my.ini优化，优化配置

8. 软件硬件升级

**3. 表的设计满足3NF**

概述: 目前我们的表的设计，最高级别的范式是"6NF",对PHP程序员而言，我们的表满足3NF即可。

**3.1. 1NF**

所谓1NF,就是

(1) 指表的属性(列)具有原子性， 即表的列的不能再分了。

![20190822130033612.png][]

(2) 不能有重复的列

特殊

(1) 只要是关系型数据库，就天然的满足1NF

(2) 常见数据库

关系型数据库(mysql, oracle, sql server,informix, db2 , postgres)

非关系型数据(Nosql类型的数据库由Redis, MongoDB)

**3.2. 2NF**

所谓2NF,就是指我们的表中不能有完全重复的一条记录（行）.一般情况下通过设置一个主键来搞定，而且该主键是自增的。

**3.3. 3NF****(****外键****)**

所谓3NF就是指，如果列的内容可以被推导(显式推导，隐式推导)出，那么我们就不要单独的用一列存放。

举例:下面是满足3NF

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70][]

**3.4. 反3NF**

在通常情况下，我们的表的设计要严格的遵守3NF,但也有例外。有时为了提高查询的效率，我们需要违反3NF。举例:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 1][]

**4. 构建海量表,定位慢查询**

为了讲解这个优化，我们需要构建一个海量表(8000000),而且每条数据不一样。

**4.1. 构建海量表步骤**

(1) 创建一个测试数据库

![20190822130611692.png][]

(2) 创建表

CREATE TABLE dept( /\*部门表\*/

deptno   MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,

dname VARCHAR(20)  NOT NULL  DEFAULT "",

loc VARCHAR(13) NOT NULL DEFAULT ""

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

加入数据: dept.sql

![20190822130638531.png][]

\#创建表EMP雇员

CREATE TABLE emp

(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /\*编号\*/

ename VARCHAR(20) NOT NULL DEFAULT "", /\*名字\*/

job VARCHAR(9) NOT NULL DEFAULT "",/\*工作\*/

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/\*上级编号\*/

hiredate DATE NOT NULL,/\*入职时间\*/

sal DECIMAL(7,2)  NOT NULL,/\*薪水\*/

comm DECIMAL(7,2) NOT NULL,/\*红利\*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /\*部门编号\*/

)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

加入数据:emp.sql

\#工资级别表

CREATE TABLE salgrade

(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2)  NOT NULL,

hisal DECIMAL(17,2)  NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

加入数据: salgrade.sql

**4.2. 海量表带来的问题**

看一个案例

![20190822130657435.png][]

**4.3. 先使用索引来搞定**

l 给empno段添加主键索引

alter table emp add primary key (empno);

![20190822130716375.png][]

一个表(存储引擎是MyISAM),对应三个文件 xx.frm 表结构 xx.MYD 数据文件 xx.MYI 索引文件

l 通过测试看效果

![20190822130731575.png][]

l 删除emp表的主键索引

alter table emp drop primary key

**4.4. 如何定位慢查询(slow query)**

介绍: 在默认情况下，mysql 是不会记录慢查询的，所以我们在测试时，可以指定mysql记录慢查询.

开启慢查询的两种方法:

l 启动时，这样启动

cmd>bin/mysqld.exe --safe-mode \--slow-query-log

或者是

在my.ini的\[mysqld\]下添加一下代码并且重启

log-slow-queries = D:/server/mysql/mysqlslowquery.log(注意斜杠)

注：mysql5.6版本slow-query-log-file

long\_query\_time = 1 指定超过1秒算慢查询

l 为了测试，我们修改 long\_query\_time

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 2][]

l 记录下慢查询

<table style="margin-left:4.65pt;width:328pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:328pt;"> &nbsp; # Time: 141122 10:39:45 # User@Host: root[root] @ localhost [127.0.0.1] # Query_time: 1.625093 &nbsp;Lock_time: 0.001000 Rows_sent: 0 &nbsp;Rows_examined: 8000000 use testdb; SET timestamp=1416623985; select * from emp where ename='IUYTOPUYQWE'; </td> 
 </tr> 
 </tbody> 
</table>

说明: Query\_time是查询的时间

Lock\_time：等待时间

**4.5. 开启慢查询牺牲sql的执行效率**

如何使用慢查询?

1. 系统上线之后,将慢查询开启一个星期.

2. 当你认为系统所由于数据增多导致系统执行缓慢.再开启慢查询找到执行慢的sql语句,然后在优化它.

**4.6. 如何分析一个sql语句的问题****-explain** **工具**

l 基本用法:

explain  sql\\G

l 案例:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 3][]

l 优化

添加索引。

**4.7. mysql的变量查询**

mysql>show variables;

mysql>show variables like ‘%xxxx%’;

show  tables  like  '数据表名';//查询一个数据表是否存在

如果需要知道每个变量的具体含义，可以查询手册.

**5. ****索引的详解（重点）**

**5.1. 索引创建**

**5.1.1. ****主键索引的创建**

主键索引的创建有两种形式, 1.在创建表的时候，直接指定某列或者某几列为主键,这时就有主键索引, 2. 添加表后，再指定主键索引

l 直接创建主键索引

注意:如果是自增, 该主键不能够删除

![20190822131702862.png][]

l 先创建表，再指定主键

![20190822131716684.png][]

增加主键

ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列名2..)

l 主键索引的特点

1. 一个表最多只能有一个主键

2. 一个主键可以指向多列(复合主键)

3. 主键索引的效率是最高，因此我们应该给id,一般id是自增.

4. 主键索引列是不能重复，也不能为null

**5.1.2. ****唯一索引的创建**

l 直接在创建表的时候，指定某列或某几列为唯一索引

![20190822131732336.png][]

l 把表创建好后，再指定某列或者某几列为唯一索引

![20190822131741915.png][]

说明: 使用 create unique index 指令，必须指定索引名。

![20190822131750839.png][]

说明: 使用alter table 指令，可以指定索引名，也可以不指定。

l 唯一索引的特点

1) 一张表可以有多个唯一索引

2) 唯一索引不能重复，但是如果你没有指定not null ，唯一索引列可以为null,而且可以有多个.

3) 什么时候使用唯一索引，当某列数据不会重复，才能使用

4) 唯一索引效率也很高，可以考虑优先使用

**5.1.3. ****普通索引的创建**

l 在创建表时指定索引,通过key或者index

![20190822131805119.png][]

l 把表创建好后，再指定某列或者某几列为索引

![20190822131813277.png][]

l 添加普通索引(2种方式)

![20190822131823463.png][]

![20190822131844426.png][]

l 特点

1) 一张表中可以有多个普通索引，一个普通索引页可以指向多列

2) 普通索引列的数据可以重复

3) 效率相对而言低.

**5.2. 索引的查询**

l desc 表名

l show keys from 表名\\G

l show index from 表名\\G

l show indexes from 表名\\G

**5.3. 索引的修改**

先删除，再添加。

**5.4. 索引的删除**

DROP  INDEX  索引名  ON  表;

ALTER  TABLE 表名 DROP INDEX 索引名；

**5.5. 索引的注意事项**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 4][]

**索引的缺点:**

增删改速度慢..

**优点:**

查询速度快…

建立索引一定要根据自己的需求来…

实例:

登录用户名是否适合建索引? 用户名适合建立索引

操作日志:

用户名    操作的哪个控制器的哪个方法    操作时间记录

不合适建立索引..

**6. sql语句的优化和正确使用索引**

**6.1. 对于创建的多列****(****复合****)****索引，只要查询条件使用了最左边的列，索引一般就会被使用**

name  email

alter table xxx  add index (name,email)

select \* from xx where name = ‘xxx’;

select \* from xx where email = ‘xxx’;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 5][]

说明: dname是左边的列，因此我们发现使用到dname,就使用到索引，而下面的sql语句，没有使用到索引。

**6.2. 对于使用like的查询，查询如果是‘****%aaa****’‘****\_aa'** **不会使用到索引‘****aaa%****’会使用到索引**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 6][]

说明: 在like语句中，如果 '' 中最前有 \_ 或者 %就使用不到索引，如果在中间或者最后有 \_ 或者 %可以使用到索引。

**6.3. 如果条件中有****or****，则要求****or****的所有字段都必须有索引，否则不会使用索引**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 7][]

说明：因为 deptno 没有索引，所以整个sql语句就没有使用到索引。

![20190822132409459.png][]

如果在 deptno上也创建索引，就可以使用到索引了.

如果mysql认为全表扫描效率更高，就不会使用索引，而会全表扫描

**6.4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 9][]

**6.5. 有些情况下，可以使用连接来替代子查询。因为使用****join****，****MySQL****不需要在内存中创建临时表**

子查询：select \* from emp where deptno in (select deptno from dept)

连接：select \* from emp left join dept on emp.deptno=dept.deptno where emp.deptno=dept.deptno

**6.6. 管理员在导入大量数据，可以这样提高速度**

大批量插入数据(MySql管理员) 了解

对于MyISAM：

alter table table\_name disable keys;

执行insert语句导入

alter table table\_name enable keys;

对于Innodb：

1， 将要导入的数据按照主键排序

2， set unique\_checks=0,关闭唯一性校验。

3， set autocommit=0,关闭自动提交。

**6.7. 如何选择存储引擎**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 10][]

l 如何选择的原则

(1) MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子/信息表/新闻/商品表表)

(2) InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高，则使用innodb)。\[账户，积分,余额\]

**6.8. 如何选择正确的数据类型**

**6.8.1. ****在满足需求的情况下尽量选择小的类型.**

**6.8.2. ****在精度要求高的应用中，建议使用定点数来存储数值，以保证结果的准确性。decimal 不要用****float.**

举例:

![20190822132527482.png][]

说明: 这里我们看的 float(10,2) , 和 decimal(10,2) decimal 更精准。所以我们对精度高的列，要使用decimal 类型。

**6.8.3. ****对存储引擎是MyISAM的表，要定时碎片整理**

举例说明：当我们在users表中有大量数据时，我们delete 数据后，我们发现磁盘空间没有回收，因此我们需要定时的进行碎片整理.如下:

创建表:

![20190822132609829.png][]

复制大量数据到同一个表中:

![20190822132618428.png][]

optimize: 该命令可以使表中的数据彻底从数据文件中删除.

![20190822132630329.png][]

（本文由源码时代技术老师原创发布，转载请注明来源。）

[20190822130033612.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/129394ed19974ab88f20199b8c8262b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/8246daf52b4b48338dc7274eb18bab9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/98db9c1d505841398de7f41b9352f016.png
[20190822130611692.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/b14c5496927b424684228551b31ecfd8.png
[20190822130638531.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/019fc7ed90d7494391727b0ebdfa7faf.png
[20190822130657435.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/22e94a43749843608f94199b450f352c.png
[20190822130716375.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/f9d427926b8944c19882a94bf4df0cff.png
[20190822130731575.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/58347676032c47ce8e7b9082e1d49703.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/d455f5e5951b4332bcfb8a3f861d3114.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/ff7380af33b34ccdaeffbbbd311ec9c5.png
[20190822131702862.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/b61b2ee03ccc4cfea5d398783b7dcced.png
[20190822131716684.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/4a8f4c18a91944bd8e6e347888532cd5.png
[20190822131732336.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/439c63659da34b44a2937ce2bb415802.png
[20190822131741915.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/cd466b9150dc4c209477dc7dc1c3ea96.png
[20190822131750839.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/a8db47a4d10249bf974267765d8ab262.png
[20190822131805119.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/fd5278ea51d64930ba8c05bb6ae392be.png
[20190822131813277.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/8dace681d002458b8b0d6f244ed943fe.png
[20190822131823463.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/bb95ee56ffef4d02bc18bccea7821163.png
[20190822131844426.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/dcc6dd507c1a4815863d11773533d1b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/01695de3f8ae446cbbd6d259c2c8c072.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/f98e9ce019a64f23ba389dcdf539b68a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/36d5d910a2ae4c9e94134cec18a0b044.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/0dd413350fbb42c0b74212e32ae13029.png
[20190822132409459.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/c566e93acbb348bba2ca40bf0380750f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/7a4c38600e1b4bd3806384ea9ad8efaf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/8f70fd9d73054c9c8a6bf85b558afa76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3ZpbGxhaW55MTM1Nzk_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/bbbcae8a162840208be3477fe4e4be87.png
[20190822132527482.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/4d58151e723c456fa6a27555428ec5ee.png
[20190822132609829.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/6ad4942aaf5043bfa2fefbe928bdbaa3.png
[20190822132618428.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/833efcd62dff48039b69704c04f493ce.png
[20190822132630329.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/11/671a4c02071b4b7b8421afd85d19bbe3.png