MYSQ学习

三范式

1NF: 字段不可分,确保每列的原子性；

2NF: 表中字段完全依赖主键，而非部分依赖，目标是确保表中的每列都和主键相关；

3NF: 非主键字段互不依赖，目标是确保每列都和主键列直接相关，而不是间接相关；

高级范式必须满足低级范式，2NF满足1NF, 3NF满足2NF。

例：

学号 姓名 地址 系 系主任 课程 分数

表中的主键为（学号，课程）

1NF:

如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式。

其中地址字段可以继续分为：省，市，街道
原表结构字段城市可以再分成：

省 市 街道

2NF

如果一个关系满足第一范式,并且除了主键以外的其它列,都完全依赖于该主键,则满足第二范式.

姓名->学号、系->学号、系主任->学号
存在部分依赖，不满足范式二的条件，需要对原表结构进行分表。
分为：

学号 姓名 地址 系 系主任

和

学号 课程 分数

两张表。

3NF

如果一个关系满足第二范式,并且除了主键以外的其它列都不间接依赖于主键列,则满足第三范式

经过第二范式划分后的表
学号 | 姓名 | 地址 | 系 | 系主任
—-|—-|—-|—-|—-|
中存在间接依赖，系主任->系->学号。
所以，再进行分表：
学号 | 姓名 | 地址 | 系id
—-|—-|—-|—-|—-|
和

系id 系 系主任

总结

应用数据库范式可以带来许多好处，但是最重要的好处归结为三点：

  1.减少数据冗余（这是最主要的好处，其他好处都是由此而附带的）  2.消除异常（插入异常，更新异常，删除异常）  3.让数据组织的更加和谐…

数据类型

数值类型

类型 大小（字节） 范围 tinyint 1 (-128,127)或（0,255） smallint 2 (-32 768，32 767)或(0，65 535) mediumint 3 (-8 388 608，8 388 607)或(0，16 777 215) int 4 (-2 147 483 648，2 147 483 647)或(0，4 294 967 295) bigint 8 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) float 4 double 8 decimal M+2

总结：
1. 一般情况下，应该尽可能使用正确存储数据的最小数据类型。
2. 整数类型有可选的的UNSIGNED属性，可以使上限提高一倍。
3. foat和double类型存储小数时容易产生误差，对精确度要求比较高时，建议使用decimal来存。由于需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。

mysql> create table t1(c1 float(10,2), c3 decimal(10,2));Query OK, 0 rows affected (0.02 sec)mysql> insert into t1 values(1234567.23, 1234567.23);Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;

c1 c3 1234567.20 1234567.23

字符类型

常用的三种：

类型 大小（字节） CHAR 0-255 VARCHAR 0-65535 TEXT 0-65535

CHAR类型和VARCHAR类型相似，在建表时可以指定字符的最大长度，CHAR（5）、VARCHAR（5）最大5个字符。CHAR中存储的字符数小于最大字符时会在尾部添加空格来补齐（在检索操作中那些填补出来的空格字符将被去掉），所以CHAR字符始终占用固定长度内存，而VARCHAR和存储的字符长度有关。
例如：

存储字符 CHAR（5） VARCHAR（5） ” 5字节 1字节 ‘12’ 5字节 3字节

VARCHAR在存储时会多占用1个字节（小于255，大于255时2字节），用来标记字符的长度。

CHAR和VARCHAR选取

1. 根据字符的长度来判断；
2. 考虑其长度的是否相近；
3. 碎片角度进行考虑；
4. 即使使用VARCHAR数据类型，大小应与实际结合。虽然VARCHAR占用存储空间与字符数有关，但是对应字符’hello’，使用VARCHAR（100）和VARCAHR（5）存储使用的内存空间是不同的。更长的列会消耗更多的内存，因为Mysql通常会分配固定大小的内存块来保存内部值。

时间

类型 大小（字节） DATETIME 8 TIME 4

总结

1. datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。
2. timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新（加ON UPDATE CURRENT_TIMESTAMP）。

索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。
表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

建立索引原则

1. 较频繁的作为查询条件的字段应该创建索引。可以考虑使用索引的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列，而不是在SELECT关键字后选择列表的列；
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。索引列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同的值，很容易区分行，而用来记录性别的列，只有”M”和”F”,则对此进行索引没有多大用处，因此不管搜索哪个值，都会得出大约一半的行。
3. 更新非常频繁的字段不适合创建索引。使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度；
4. 不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

MYSQL使用索引情况

在MYSQL中并不是在任何情况下都会使用到索引的：
- 只对以下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)；
- 定义时要符合最左前缀原则（最左前缀原则指的的是在sql where 子句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引）。

create table one (    id smallint(10) not null auto_increment primary key,    username char(8) not null,    password char(4) not null,    `level` tinyint (1) default 0,    last_login char(15) not null,    index (username,password,last_login)    ) engine=innodb;

上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面几个顺序(password,last_login)、(passwrod)、(last_login)—这三者不 从username开始，(username,last_login)—断层，少了password，都可能无法利用到索引。因为B+tree多列索引保存的顺序是按照索引创 建的顺序，检索索引时按照此顺序检索

不能正确的利用索引

• Where 子句表达式虽然顺序是(username,password, last_login)或(username,password)但第一个是有范围’<’、’>’，’<=’， ’>=’等出现

mysql> explain select * from one where username>'abgvwfnt' and password ='123456'and last_login='1338251170';+----+--------+-------+------+------------+------+---------+------+-------+-----+| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |+----+-------+-------+------+-----------+------+---------+------+-------+-----+| 1 | SIMPLE | one | ALL | username | NULL | NULL | NULL | 20146 | Using where |+----+--------+-------+------+---------+------+---------+------+-------+-----+1 row in set (0.00 sec)

• like

mysql> explain select * from one where username like 'abgvwfnt%';+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+| 1 | SIMPLE | one | range | username | username | 24 | NULL | 5 | Using where |+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from one where username like '%abgvwfnt%';+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra |+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+| 1 | SIMPLE | one | ALL | NULL | NULL | NULL | NULL | 20259 | Using where |+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+1 row in set (0.01 sec)对比就知道like操作abgvwfnt%能用到索引，%abgvwfnt%用不到

引用：
https://www.zhihu.com/question/36996520
http://thephper.com/?p=142
http://wulijun.github.io/2012/08/21/mysql-index-implementation-and-optimization.html
https://segmentfault.com/a/1190000003072424