mysql基础篇

blog

By Shelter. 2016/3/29

1.字符集排序规则

一般使用utf8字符集，utf8_general_ci排序规则。
每种字符集都会有相应的排序规则。

utf8_general_ci校对速度快，但准确度稍差。
utf8_unicode_ci准确度高，但校对速度稍慢。

utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。

如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。一般用utf8_general_ci就够了，到现在也没发现问题。。。

2.储存引擎

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

在Oracle 和SQLServer等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

这里就只介绍常用的储存引擎：

• MyISAM：这种引擎是mysql最早提供的。
  
  • 静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
  • 动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
  • 压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
    (但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。)
• InnoDB：InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。但是也存在着表空间管理（InnoDb是以一个虚拟文件系统来管理，且建立后只能增大，不能缩小。不支持冷备份，需要经常使用mysqldump）、存储空间占用量、不支持全文索引和GIS地理数据、COUNT统计速率（高版本可能已经解决）慢等问题。

笔者认为，MYISAM和InnoDB的选择，主要考虑是否需要支持事务即可，因为现在硬件都很发达了，内存不是什么问题。需要事务的，采用InnoDB，否则采用MYISAM。

3.数据类型

数据类型有很多，谷歌可以搜到很多，这里就不多说。常用的有：

char和varchar区别：
1.char类型的字符串检索速度要比varchar类型的快。
2.请参考MySQL数据库中CHAR与VARCHAR之争
3.个人总结：
长度不是经常改变：长度确定char（用户名）/长度不确定varchar（qq空间说说）
长度经常改变：char （qq个性签名）

varchar和text区别：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引效果不明显。

4.命名规则

系统读取mysql数据的时候，是强行统一成小写的。
所以 数据库名，表名，字段名 都采用下划线分割法。

5.设计思路

首先，要先理解什么是三种范式。让我们来举个例子。
假设我们现在要设计一个书籍管理系统。

问题来了，假设有一本书有三个作者呢？

根据范式优化如下：

第一范式

• 内容相似的数据必须消除
• 必须为每一组相关数据创建一张数据表
• 每条记录必须用一个主键来标识

这样子的话，就能自由添加书籍了。但是还是存在很多冗余的数据，占据内存。

第二范式

• 只要数据列出现重复，就意味着可以拆分成多个字表
• 拆分出来的字表要用外键关联起来

book表

author表

book_author表

明显数据冗余性大大降低，扩展性都得到了很大的提升。

第三范式

• 满足了第二范式后，与外键没有直接关系的数据列必须消除（可能考虑到未来的扩展等）

book表

publish表

什么？没看懂？没关系，有另一种思维。

上面中，book 和 出版社publish 就算是多对一，换位思考就是一对多了。
而book 和 author 就算是多对多关系。靠book_author中间表来解决。
是不是简单粗暴？

6.索引

参考YY哥-索引与优化
（此文中提到用内置日期类型储存时间，笔者认为用整型储存时间戳来储存时间 无论是从排序，读取效率或者前端显示自由度来看，都是是最为科学的。）

索引是提升速率的一个好途径。在经常需要where到的字段，可以添加索引。
速度比较：唯一索引 > 普通索引
（主索引和唯一索引唯一的区别就是使用的关键字为PRIMARY而不是UNIQUE）

另外，索引会导致修改操作变慢。
数据库每次数据修改，都会刷新索引表。批量修改的时候，可以使用DELAY_KEY_WRITE——批量修改完毕后，再刷新。
DELAY_KEY_WRITE的详细说明

另外，如果经常需要到 WHERE field1 = xx AND field2 = xx 查询，可以建立组合索引。

限制：

• 如果where子句存在（！=）符号或者函数（day（）），mysql将无法进行索引。
• 在join操作中，只有在主键和外键类型相同时才能进行索引。
• 如果使用了比较字符（LIKE和REGEXP），'asd%' 能进行索引，'%asd' 不行。
• 在ORDER BY 中，只有排序表达式不是一个表达式的情况下才使用索引。
• 如果索引列有很多数据有重复值，那么索引会没有效果。

另外，尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替空值。

7.在空间和时间中找到一个平衡点

这个或许设计里比较难的一个了！
100%根据范式来，是可以，但是在代码逻辑层会比较麻烦。有时候处理起来，反而效率变低了。

因此，一定程度上的数据冗余是允许的。或者通过一些技巧来降低代码层的复杂性。好比：

book表

这样子，就少了一张中间表。不过要注意考虑项目的扩展性，或者以后客户会有什么坑爹的需求，不然后期可能会是一个大坑！

8.垂直切分

场景是这样子的：

教室有自己的特有字段（id_card），学生有自己的特有字段(student_card)。那么就造成了一些数据库内存浪费。

解决方法之一就是垂直切分：

user表

student表

teacher表

• 优点
  
  • 数据库的拆分简单明了，拆分规则明确
  • 应用程序模块清晰明确，整合容易；
  • 数据维护方便易行，容易定位
• 缺点
  
  • 表关联基本在程序中完成，join和事务比较频繁；
  • 对于访问极其频繁且数据量超大的表仍然存在性能瓶颈，不一定能满足要求；
  • 过读切分可能会带来系统复杂而难以维护。

还有一种解决方式就是直接拆分成 teacher 和 student 表，这样子代码层就变得很简单。不过登陆注册等就需要分角色操作。具体看业务需求吧！

9.写在最后

说数据库是一门艺术完全不为过，只有多实践，才能逐渐看的远吧！