史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试

发布时间:2020-07-31 13:56:36 作者:wx5d51393e4bd0e
来源:网络 阅读:389

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) 指表的属性(列)具有原子性, 即表的列的不能再分了。
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
(2) 不能有重复的列
 
特殊
(1) 只要是关系型数据库,就天然的满足1NF
(2) 常见数据库
关系型数据库(mysql, oracle, sql server,informix, db2 , postgres)
非关系型数据(Nosql类型的数据库由Redis, MongoDB)
3.2. 2NF
所谓2NF,就是指我们的表中不能有完全重复的一条记录(行).一般情况下通过设置一个主键来搞定,而且该主键是自增的。
3.3. 3NF(外键)
所谓3NF就是指,如果列的内容可以被推导(显式推导,隐式推导)出,那么我们就不要单独的用一列存放。
举例:下面是满足3NF

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
3.4. 反3NF
在通常情况下,我们的表的设计要严格的遵守3NF,但也有例外。有时为了提高查询的效率,我们需要违反3NF。举例:
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
4. 构建海量表,定位慢查询
为了讲解这个优化,我们需要构建一个海量表(8000000),而且每条数据不一样。
4.1. 构建海量表步骤
(1) 创建一个测试数据库

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
(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
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
#创建表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. 海量表带来的问题
看一个案例
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
 
4.3. 先使用索引来搞定
l 给empno段添加主键索引
alter table emp add primary key (empno);

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
一个表(存储引擎是MyISAM),对应三个文件 xx.frm 表结构 xx.MYD 数据文件 xx.MYI 索引文件
l 通过测试看效果

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
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

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
l 记录下慢查询
 

Time: 141122 10:39:45

User@Host: root[root] @ localhost [127.0.0.1]

Query_time: 1.625093  Lock_time: 0.001000 Rows_sent: 0  Rows_examined: 8000000

use testdb;
SET timestamp=1416623985;
select * from emp where ename='IUYTOPUYQWE';
 
说明: Query_time是查询的时间
Lock_time:等待时间
 
4.5. 开启慢查询牺牲sql的执行效率
如何使用慢查询?

  1. 系统上线之后,将慢查询开启一个星期.
  2. 当你认为系统所由于数据增多导致系统执行缓慢.再开启慢查询找到执行慢的sql语句,然后在优化它.
     
    4.6. 如何分析一个sql语句的问题-explain 工具
    l 基本用法:
    explain  sql\G
    l 案例:

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
l 优化
添加索引。
 
4.7. mysql的变量查询
mysql>show variables;
mysql>show variables like ‘%xxxx%’;
show  tables  like  '数据表名';//查询一个数据表是否存在
如果需要知道每个变量的具体含义,可以查询手册.
5. 索引的详解(重点)
5.1. 索引创建
5.1.1. 主键索引的创建
主键索引的创建有两种形式, 1.在创建表的时候,直接指定某列或者某几列为主键,这时就有主键索引, 2. 添加表后,再指定主键索引
l 直接创建主键索引
注意:如果是自增, 该主键不能够删除
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
l 先创建表,再指定主键

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
增加主键
ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列名2..)
l 主键索引的特点
1. 一个表最多只能有一个主键
2. 一个主键可以指向多列(复合主键)
3. 主键索引的效率是最高,因此我们应该给id,一般id是自增.
4. 主键索引列是不能重复,也不能为null
5.1.2. 唯一索引的创建
l 直接在创建表的时候,指定某列或某几列为唯一索引
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
l 把表创建好后,再指定某列或者某几列为唯一索引
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
说明: 使用 create unique index 指令,必须指定索引名。
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
说明: 使用alter table 指令,可以指定索引名,也可以不指定。
l 唯一索引的特点
1) 一张表可以有多个唯一索引
2) 唯一索引不能重复,但是如果你没有指定not null ,唯一索引列可以为null,而且可以有多个.
3) 什么时候使用唯一索引,当某列数据不会重复,才能使用
4) 唯一索引效率也很高,可以考虑优先使用
5.1.3. 普通索引的创建
l 在创建表时指定索引,通过key或者index

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
l 把表创建好后,再指定某列或者某几列为索引
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
l 添加普通索引(2种方式)

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试

 
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. 索引的注意事项

史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
索引的缺点:
增删改速度慢..
优点:
查询速度快…
 
建立索引一定要根据自己的需求来…
实例:
登录用户名是否适合建索引? 用户名适合建立索引
 
操作日志:
用户名    操作的哪个控制器的哪个方法    操作时间记录
不合适建立索引..
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’;
 
 
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
说明: dname是左边的列,因此我们发现使用到dname,就使用到索引,而下面的sql语句,没有使用到索引。
6.2. 对于使用like的查询,查询如果是‘%aaa’‘_aa' 不会使用到索引‘aaa%’会使用到索引
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
说明: 在like语句中,如果 '' 中最前有 或者 %就使用不到索引,如果在中间或者最后有 或者 %可以使用到索引。
6.3. 如果条件中有or,则要求or的所有字段都必须有索引,否则不会使用索引

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
说明:因为 deptno 没有索引,所以整个sql语句就没有使用到索引。
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
如果在 deptno上也创建索引,就可以使用到索引了.
如果mysql认为全表扫描效率更高,就不会使用索引,而会全表扫描
6.4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
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. 如何选择存储引擎

 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
l 如何选择的原则
(1) MyISAM:默认的MySQL存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子/信息表/新闻/商品表表)
(2) InnoDB:提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM,写的处理效率差一些并且会占用更多的磁盘空间(如果对安全要求高,则使用innodb)。[账户,积分,余额]
6.8. 如何选择正确的数据类型
6.8.1. 在满足需求的情况下尽量选择小的类型.
6.8.2. 在精度要求高的应用中,建议使用定点数来存储数值,以保证结果的准确性。decimal 不要用float.
举例:
 史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试

 
说明: 这里我们看的 float(10,2) , 和 decimal(10,2) decimal 更精准。所以我们对精度高的列,要使用decimal 类型。
6.8.3. 对存储引擎是MyISAM的表,要定时碎片整理
举例说明:当我们在users表中有大量数据时,我们delete 数据后,我们发现磁盘空间没有回收,因此我们需要定时的进行碎片整理.如下:
创建表:
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 
复制大量数据到同一个表中:
史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试
 

史上最全的mysql数据库优化方法,软件测试人手必备! 史上最全的mysql数据库优化方法,软件测试 
optimize: 该命令可以使表中的数据彻底从数据文件中删除.

(本文由源码时代技术老师原创发布,转载请注明来源。)

推荐阅读:
  1. 软件测试最全的视频源码,最新软件测试学习路线图分享
  2. 史上最全安装proxmox教程(基于vmware workstation)

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 人手

上一篇:安装python第三方库的方式有哪些

下一篇:python注解一段代码的方法

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》