1. 约束条件
-
说明：约束条件与数据类型的宽度一样，都是可选参数
-
作用：用于保证数据的完整性和一致性
-
分类：
-
NOT NULL：标识该字段不能为空
-
DEFAULT：为该字段设置默认值
-
AUTO_INCREMENT：标识该字段的值自动增长（整数类型，而且为主键）
-
PRIMARY KEY (PK)：标识该字段为该表的主键，可以唯一的标识记录
-
UNIQUE KEY (UK)：标识该字段的值是唯一的
-
FOREIGN KEY (FK)：标识该字段为该表的外键
-
UNSIGNED：无符号
-
ZEROFILL：使用0填充
-
not null：
-
是否为空，null表示空，非字符串
-
null：可为空
-
not null：不可为空
-
default：
-
设置字段默认值
-
插入数据时如果不主动设置，会自动添加默认值
-
auto_increment：
-
设置自增长
-
必须为整数类型
-
默认从0开始，也可以手动设置起始值
-
DEFAULT约束只会在使用INSERT语句时体现出来，INSERT语句中，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充
-
primary key：
-
设置字段值是唯一的，并且不能为空
-
一个表中只能有一个主键
-
可以设置多个字段联合主键(约束多个字段同时满足主键约束，其中一个或多个有空值或重复值不受主键约束)
-
unique：
-
设置字段的唯一性
-
为了解决主键存在表中的独立性
-
可以设置多个字段联合唯一
-
当INSERT语句新插入的数据和已有数据重复的时候，如果有UNIQUE约束，则INSERT失败
-
foreign key：
-
为了节省内存资源，不用多次读取重复的信息
-
一个表可以有多个外键，被外键约束的列，取值必须在它参考的列中有对应值
-
删除与修改时的状态：
-
RESTRICT：严格限制约束，外键无法删除与修改操作
-
NO ACTION：外键删除与修改时，对应的数据不会做任何操作
-
CASCADE：外键删除与修改时，对应的数据也会跟着删除与修改
-
SET NULL：外键删除与修改时，对应的数据被修改为NULL
-
一般应用中的外键都是假外键，方便数据库的修改与扩展
2. 索引
-
产生背景：一般的应用系统，读写比例在10:1左右，而且插入操作和修改、删除操作很少出现性能问题，查询操作占用比较多，还会出现一些复杂的查询操作，这个时候就需要针对查询做一些优化与加速了。
-
说明：索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。数据库利用各种各样的快速定位技术，能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍
-
使用索引的误区：索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要。一些开发人员总是在事后才想起添加索引----我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。索引也并不是越多越好，如果添加不合适会造成cpu一直过高，可能会直接 把服务器搞宕机，所以，要合理使用索引才能达到真正意义上的性能提升
-
目的：提高查询效率
-
本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据
-
primary key、unique、联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
-
索引的类型：普通索引、主键索引、唯一索引、联合索引、全文索引
-
普通索引：INDEX(加速查找)
-
主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-
唯一索引：加速查找+约束（不能重复）
-
联合索引：
-
PRIMARY KEY(字段1,字段2...)：联合主键索引
-
UNIQUE(字段1,字段2...)：联合唯一索引
-
INDEX(字段1,字段2...)：联合普通索引
-
全文索引(FULL TEXT)：只有MyISAM引擎支持，而且只有 CHAR、VARCHAR、TEXT的类型可以创建全文索引。FULLTEXT索引是按照分词原理建立索引的。西文中，大部分为字母文字，分词可以很方便的按照空格进行分割，而对于中文需要使用mysql中文分词插件，原理是把中文转为拼音之后再进行分词(不推荐使用)
-
索引的方式：BTREE和HASH
-
hash：
-
说明：hash就是一种（key=>value）形式的键值对,允许多个key对应相同的value，但不允许一个key对应多个value,为某一列或几列建立hash索引，就会利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据。hash索引的key是有序的，可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率
-
优缺点：查询单条快，范围查询慢
-
btree：将索引值按一定的算法，存入一个树形的数据结构中(二叉树)。btree方式也是最常用的索引方式
-
不同的存储引擎支持的索引类型也不一样：
-
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
-
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
-
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引
-
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引
-
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引
-
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型
-
索引的注意事项：
-
避免使用select *
-
count(1)或count(主键列名) 代替 count(*)
-
创建表时尽量 char 代替 varchar
-
表的字段顺序固定长度的字段优先
-
经常使用多个条件查询时，组合索引代替多个单列索引
-
尽量使用短索引
-
使用连接（JOIN）来代替子查询(Sub-Queries)
-
连表时注意条件类型需一致
-
索引散列值(重复少)不适合建索引
-
联合索引与覆盖索引：
-
联合索引：指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处在仅在于有多个索引列。使用联合索引的好处是在第一个键相同的情况下，已经对第二个键进行了排序处理
-
覆盖索引：InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。使用覆盖索引的好处是不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作(覆盖索引技术最早是在InnoDB Plugin中完成并实现，这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0以下的，InnoDB存储引擎不支持覆盖索引特性)
3. 数据库设计三范式
-
第一范式：确保每列保持原子性
-
第二范式：确保表中的每列都和主键相关
-
第三范式：确保表中每一列都和主键列直接相关,而不是间接相关
4. 数据库表关系
-
一对一关系(至少一张表)
-
一对多关系(至少两张表)
-
多对多关系(至少三张表)
5. 分表
-
说明：在数据库表使用过程中，为了减小数据库服务器的负担、缩短查询时间，常常会考虑做分表设计
-
分表可以分两种：纵向分表和横向分表
-
纵向分表：将本来可以在同一个表的内容，人为划分存储在为多个不同结构的表(如:根据活跃度分表、根据重要性分表等)
-
表与表之间资源争用问题
-
锁争用机率小
-
实现核心与非核心的分级存储
-
解决了数据库同步压力问题
-
横向分表：把大的表结构，横向切割为同样结构的不同表(如:根据时间分表)
-
单表过大造成的性能问题
-
单表过大造成的单服务器空间问题
posted @
2017-12-11 17:18
_岩哥
阅读(
164)
评论()
收藏
举报
浙公网安备 33010602011771号