MYSQL学习笔记——数据库范式及MYSQL优化整体思路
一、数据库范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
1.1、第一范式（1NF：每一列不可包含多个值）
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
1.2、第二范式（2NF：非主属性部分依赖于主关键字）
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性部分依赖于主关键字。
1.3、第三范式（3NF：属性不依赖于其它非主属性）
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。
1.4、反三范式（反3NF：为了性能，增加冗余）
3NF提出目的是为了降低冗余，减少不必要的存储，这对于存储设备昂贵的过去是很有必要的，但是随着存储设备的降价以及人们对性能的不断提高，又有人提出反三范式。
所谓反三范式就是为了性能，增加冗余。以部门信息表为例，每个部门有部门编号、部门名称、部门简介等信息，按照3NF的要求，为了避免冗余，我们在员工表中就不应该加入部门名称、部门简介等部门有关信息，带来的代价是每次都要查询两次数据库。反三范式允许我们冗余重要信息到员工表中，例如部门名称，这样我们每次取员工信息时就能直接取出部门名称，不需要查询两次数据库，提升了程序性能。
二、MYSQL优化整体思路
MYSQL优化首先应该定位问题，可能导致MYSQL低性能的原因有：业务逻辑过多的查询、表结构不合理、sql语句优化以及硬件优化，从优化效果来看，这四个优化点的优化效果依次降低：理清业务逻辑能够帮助我们避免不必要的查询，合理设计表结构也能帮助我们少查询数据库。对于sql语句优化，我们可以先使用慢查询日志（慢查询日志的使用：http://www.cnblogs.com/timlearn/p/4052523.html）定位慢查询，然后针对该查询进行优化，最常见且最有效的优化范式就是增加合理的索引，这个在上篇博客http://www.cnblogs.com/timlearn/p/4055512.html已经讲解，本篇博客将讲解其他一些优化手段或者注意点。
2.1、谨慎使用TEXT/BLOB类型
当列类型是TEXT或者BLOB时，我们应该特别注意，因为当选择的字段有 text/blob 类型的时候，无法创建内存表，只能创建硬盘临时表，而硬盘临时表的性能比内存表的性能差，所以如果非要使用TEXT/BLOB类型，应该单独建表，不要把TEXT/BLOB类型与核心属性混合在一张表中。对此，做一个实验如下：
//创建数据表
create table t1 (
num int,
intro text(1000)
); //插入数据
insert into t1 values (3,'this is USA') , (4,'China'); //查询临时表创建情况
//注意，这里Created_tmp_disk_tables=4，Created_tmp_tables=10
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4 |
| Created_tmp_files | 9 |
| Created_tmp_tables | 10 |
+-------------------------+-------+ //使用group by查询数据
mysql> select * from t1 group by num;
+------+-------------+
| num | intro |
+------+-------------+
| 3 | this is USA |
| 4 | China |
+------+-------------+
2 rows in set (0.05 sec) //再次查询临时表创建情况
//现在，Created_tmp_disk_tables=5，Created_tmp_tables=11
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 5 |
| Created_tmp_files | 9 |
| Created_tmp_tables | 11 |
+-------------------------+-------+
2.2、慎用子查询
几乎所有子查询都可以改写为连接查询，有时候，连接查询的效率要比子查询高，所以把子查询改写成连接查询是一个不错的注意。如果一条使用子查询的select语句执行时间过长，那么就应该尝试把它改写为连接查询，看他是不是执行的更好。
下例中，我们使用子查询耗时5s，清空缓存后，改用连接查询，只需要0.02s：
//子查询耗时5s
mysql> select * from emp where ename in (select ename from ename);
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 2 | AsoqNR | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 466 |
| 3 | cAuvTj | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 155 |
| 6 | oOeekL | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 13 |
| 9 | MFPixN | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 225 |
| 103219 | MfpiXn | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 251 |
| 318098 | mFpIxn | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 480 |
| 333225 | ASOqnr | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 466 |
| 443919 | AsoqNR | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 446 |
| 458077 | OoeEKL | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 266 |
| 473649 | AsoqNR | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 448 |
| 769138 | CAUVTJ | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 252 |
| 826307 | MFPixN | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 242 |
+--------+--------+----------+-----+------------+---------+--------+--------+
12 rows in set (5.04 sec) //清空缓存
reset query cache; //连接查询耗时0.02s
mysql> select emp.* from emp inner join ename on emp.ename=ename.ename;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 6 | oOeekL | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 13 |
| 458077 | OoeEKL | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 266 |
| 9 | MFPixN | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 225 |
| 103219 | MfpiXn | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 251 |
| 318098 | mFpIxn | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 480 |
| 826307 | MFPixN | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 242 |
| 3 | cAuvTj | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 155 |
| 769138 | CAUVTJ | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 252 |
| 2 | AsoqNR | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 466 |
| 333225 | ASOqnr | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 466 |
| 443919 | AsoqNR | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 446 |
| 473649 | AsoqNR | SALESMAN | 1 | 2014-10-29 | 2000.00 | 400.00 | 448 |
+--------+--------+----------+-----+------------+---------+--------+--------+
12 rows in set (0.02 sec)
2.3、在DISTINCT列上增加索引
如果在distinct列上不增加索引，那么mysql在查询时将创建临时表；如果我们在该列上增加索引，可以避免使用临时表：
create table message(
user_id int,
group_id int
); //插入10条数据
insert into message values(24,67),(15,76),(134,986),(6,98),(46,988),(13,2),(12,89),(17,34),(63,19),(92,74); //没加索引，extra中有using temporary
mysql> explain select distinct user_id from message where group_id=2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: message
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where; Using temporary
1 row in set (0.01 sec) //增加索引
create index KEY_GID_UID on message (group_id, user_id); //加了索引之后，终于不用创建临时表了
mysql> explain select distinct user_id from message where group_id=2\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: message
type: ref
possible_keys: KEY_GID_UID
key: KEY_GID_UID
key_len: 5
ref: const
rows: 1
Extra: Using where; Using index
1 row in set (0.00 sec)
2.4、在group by后面增加order by null
在使用group by分组查询时，默认分组后，还会排序，可能会降低速度。如果不需要排序，那么可以在group by后面增加order by null，这样可以避免分组后排序：
//注意，没加order by null时，extra中有Using filesort
mysql> explain select * from emp group by deptno\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4296846
Extra: Using temporary; Using filesort //加了order by null后，extra中就没有Using filesort了
mysql> explain select * from emp group by deptno order by null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4354494
Extra: Using temporary
1 row in set (0.00 sec)
最新文章
- iOS 10 ：用 UIViewPropertyAnimator 编写动画
- java字符编码和oracle乱码
- 纯JSP实现简单登录跳转
- HDU1102--最小生成树
- HDU 1693 Eat the Trees（插头DP、棋盘哈密顿回路数）+ URAL 1519 Formula 1（插头DP、棋盘哈密顿单回路数）
- 条件查询，有input和select框，当查询条件获取焦点时支持摁下enter键查询
- javascript预解析和作用域
- qq互联登陆开发流程
- @Resource和@Autowired
- 解决IE6下 PNG图片有背景问题
- linux下insmod lsmod rmmod
- LeetCode 141. Linked List Cycle 判断链表是否有环 C++/Java
- dataTransfer对象
- history新增方法
- C++ 友元类使用 (friend)
- php 建立 搜索 分词树
- RHEL6.4 xclock安装小记
- MapReduce中文翻译
- 第9课：备份mysql数据库、重写父类、unittest框架、多线程
- lol佐伊美图
热门文章
- Count on a tree(树上路径第K小)
- 在 iTerm2 终端使用 command + ；会弹出最近使用的命令列表
- wowza 降低延迟
- javascript中for循环和标签元素赋值问题总结
- Android.mk【转】
- Oracle-buffer cache、shared pool
- grep匹配单词, 匹配单词开始, 匹配^ 的区别
- 几种 MyBatis 增强插件
- 阶段1 语言基础+高级_1-3-Java语言高级_1-常用API_1_第8节 Math类_19_Math练习：小学数学真题
- 解决旋转屏幕闪退在androidManifest.template.xml里，activity项添加：