db2数据库建表的时候主键怎么建_源码时代软件测试干货|史上最全的mysql数据库优化方法，软件测试人手必备！...

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) 指表的属性(列)具有原子性， 即表的列的不能再分了。

(2) 不能有重复的列

特殊

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

(4) 常见数据库

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

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

3.2 2NF

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

3.3 3NF(外键)

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

举例:下面是满足3NF

3.4 反3NF

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

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

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

4.1. 构建海量表步骤

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

(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

#创建表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. 海量表带来的问题

看一个案例

4.3. 先使用索引来搞定

l 给empno段添加主键索引

alter table emp add primary key (empno);

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

l 通过测试看效果

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

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 sqlG

l 案例:

l 优化

添加索引。

4.7. mysql的变量查询

mysql>show variables;

mysql>show variables like ‘%xxxx%’;

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

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

5. 索引的详解（重点）

5.1. 索引创建

5.1.1. 主键索引的创建

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

l 直接创建主键索引

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

l 先创建表，再指定主键

增加主键

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

l 主键索引的特点

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

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

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

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

5.1.2. 唯一索引的创建

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

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

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

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

l 唯一索引的特点

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

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

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

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

5.1.3. 普通索引的创建

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

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

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

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

索引的缺点:

增删改速度慢..

优点:

查询速度快…

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

实例:

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

操作日志:

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

不合适建立索引..

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’;

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

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