数据库系统概论（二）数据库设计--数据库物理设计--存取结构、索引、聚簇、存储结构
数据库物理设计
定义：为一个给定的逻辑模型设计一个最适合应用要求的物理结构
数据库在物理设备上的存储结构和存取方法称为数据库的物理结构
物理设计涉及到的评价标准
数据存储：
①数据量
②存取频度
③存取方法：批处理/联机处理；检索/更新；顺序检索/随机检索
处理要求：
处理频度要求，单位时间处理多少事务、多少数据量和响应要求等
以上两个要求的信息都涉及到上节需求分析中的数据字典
为关系模式选择存取方法（建立存取路径）
数据库常用存取方法：索引存取方法、聚簇存取方法、hash存取方法
1.索引存取方法
根据应用要求确定：
对哪些属性列建立索引
对哪些索引设计为唯一索引，组合（复合）索引
选择合适的索引方法（B+树索引，hash索引）
建立索引的方法：
①建表时建立
CREATE TABLE 表名(
字段名 数据类型 [完整性约束条件],
……，
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]
);
②建表后建立
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；
或
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；
对什么列要建立索引：①主键自动建立索引
②经常作为查询条件在WHERE和ORDER BY的语句中出现的列
③外键建立索引
④用于聚合函数的列，如max（column_1）中的column_1
对什么列不要建立索引：①经常删改的列
②有大量重复的列
③表记录太少不要建立索引
索引失效的情况：①在组合索引中不能有列值为NULL的情况
②在一个语句中索引只能用一次，如果在WHERE中用了那么在ORDER BY中就不会走索引
③LIKE操作中，'%aaa%'不会走索引，'aaa%'会走索引
对于其他通配符_ [charlist] [!charlist]也是仅当第一个字符不为通配符才走索引
④在索引的列上使用表达式或函数会使索引失效，如select * from users where YEAR(date)< 2007，这将导致进行全表扫描。
⑤只有在where子句中出现，mysql才会去使用索引
⑥如果条件中有or，即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
⑦对于复合索引，如果不使用前列，后续列也将无法使用
⑧如果mysql估计使用全表扫描要比使用索引快,则不使用索引（数据量小的表）
⑨存在索引列的数据类型隐形转换，则不走索引，比如WHERE email=77777 不走索引
B+树索引和hash索引
1.B+树索引
在B+树上，所有实际需要的数据（可以存储整行的数据故不需要表扫描（二次查找））都存放于 Tree 的 Leaf Node ，到任何一个 Leaf Node的最短路径的长度都是完全相同的。
可以支持顺序扫描，利用双向指针快速左右移动，效率非常高。
BTree索引是最常用的mysql数据库索引算法，因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量
2.hash索引
hash索引检索时只需要进行一次hash算法就能定位到相应的hash值链表中，得到行指针再进行表访问，一般来说速度优于BTree索引
①hash索引不能范围查询只能等值查询（包括＝、 IN 、<=>）
②hash索引不能用于排序oder by，因为hash是按hash值存储的，原值不同可能hash值也是相同的
③hash索引不能避免表扫描（二次查找），即hash索引本身并不存储信息（元组）仅存储行指针，所以还要返回表中找到对应行
④hash索引不支持组合索引的部分索引查找（也不支持最左匹配），组合索引是各部分一起计算hash值存储在hash表中的，只能用于组合索引所有部分都出现的等值查找的情况
⑤hash碰撞问题。当有大量重复键的时候hash索引性能极低，可能低于BTree
索引的缺点
索引的缺点
◆维护索引的开销：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
◆存储索引的开销：建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
最左匹配原则
最左匹配原则仅存在于复合索引：
以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的
mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。
所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。
那么什么时候才能用到呢?
当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。
这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。
---------------------------------------------------------------------------------------------------------------------------
所以对于你的这条sql查询：
EXPLAIN SELECT * FROM student WHERE cid=1 AND name='小红';没有错，而且复合索引中的两个索引字段都能很好的利用到了！因为语句中最左面的name字段进行了等值匹配，所以cid是有序的，也可以利用到索引了。
你可能会问：我建的索引是(name,cid)。而我查询的语句是cid=1 AND name='小红'; 我是先查询cid，再查询name的，不是先从最左面查的呀？
好吧，我再解释一下这个问题：首先可以肯定的是把条件判断反过来变成这样 name='小红' and cid=1; 最后所查询的结果是一样的。
那么问题产生了？既然结果是一样的，到底以何种顺序的查询方式最好呢？
所以，而此时那就是我们的mysql查询优化器该登场了，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。
2.聚簇存取方法
聚簇索引，实际存储的循序结构与数据存储的物理机构是一致的，所以通常来说物理顺序结构只有一种，那么一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引。
当然有人说我不想拿主键作为聚簇索引，我需要用其他字段作为索引，当然这也是可以的，这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引，然后再设置主键，这样就木有问题啦。
总而言之，聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条；
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
InnoDB本质上是没有hash索引的，hash索引是“索引的索引”，InnoDB为了加速索引的查找会建立自适应(adaptive)hash表。
hash索引常用于内存数据库Memory/Heap引擎。
MySQL早期采用的MyISAM引擎和后来采用的InnoDB：
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
使用聚簇索引的优势：
1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。
为关系、索引、日志、备份等数据库文件选择存储结构
存储的对象：关系、索引、日志、备份、数据库缓冲区
存储的方式：内存/磁盘，行/列存储，集中/分散存储，随机/顺序/聚簇存储
选择存储结构的基本原则
①易变部分与稳定部分分开存放
②经常存取部分与存取频率低部分分开存放
③日志文件与数据库对线（表、索引）分开存放
DBMS一般都提供了一些存储分配参数：同时用户数、同时对象数、内存分配参数、缓冲区分配参数、锁数目……
