复习数据库2

三范式

第一范式：每个列都不可再分。

第二范式：，非主键列完全依赖于主键，而不是依赖于主键一部分。

第三范式：，非主键列只依赖于主键，不依赖于其他非主键。

权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

MySQL的binlog录入格式：

• statement模式下，每一条会修改数据的sql都会记录在binlog中。
• row级别下，
• mixed

存储引擎

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

索引

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

索引是一个文件，它是要占据物理空间的。

• 加快检索速度；使用优化隐藏器，提高系统性能；
• 创建索引、维护索引需要耗费时间；需要动态维护，降低增改删的执行效率；
• 需要占用物理空间。

alter table Student_innodb1 add sex char(1); 增加一个没有建立索引的字段sex

创建唯一索引
alter table table_name ADD unique (column1, colum2);
创建普通索引
alter table table_name ADD index index_name (colimn1, column2,c3);
create INDEX index_name ON table_name (column_list);
#创建表
create table user_t2(
	id INT auto_increment PRIMARY KEY,
    first_name varchar(16),
    last_name VARCHAR(16),
    id_card varchar(18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

全文索引
alter table table_name ADD fulltext (column); 

索引覆盖

1. B树索引
   1. 
2. hash索引
   1. 在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快。
   2. 直接定址法、平方取中法、折叠法、除数取余法、随机数法;
   3. 将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。