MySQL复习笔记（未完待续）

1. 数据库三大范式

1. 第一范式（原子性）：每个列不可再拆分
2. 第二范式（完全依赖主键）：在第一范式的基础上，非主键列完全依赖与主键，而不能是依赖于主键的一部分
3. 第三范式（直接依赖主键）：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

2. MySQL权限表

user	记录允许连接到副武器的用户账号信息
db	记录各个账号在各个数据库上的操作权限
table_priv	记录数据表级的操作权限
Columns_priv	记录数据列级的操作权限
Host	配合DB权限表对给定主机上数据库级操作权限作更细致的控制

3. 存储引擎MyISAM、InnoDB、Memory的区别

• InnoDB：提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束，它的设计目标是处理大数据容量的数据库系统

• MyISAM：不提供事务支持，不支持行级锁和外键

• 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 是索引组织表
  哈希索引	不支持	支持
  全文索引	支持	不支持

4. MyISAM与InnoDB索引的区别

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

5. 什么是聚簇索引/非聚簇索引？

按照索引中索引的逻辑顺序与磁盘上的物理存储顺序是否相同来判定，相同则为聚簇索引。

何时使用？

6. InnoDB的四大特性

1. 插入缓冲
2. 二次写
3. 自适应哈希索引
4. 预读

7. 使用场景选择

• 如果没有特殊需求，使用默认的InnoDB
• 以读写插入为主的应用程序，比如博客等，使用MyISAM
• 更新操作频率高，保证数据的完整性，并发量高，需要支持事务和外键，比如OA，使用InnoDB

8. 什么是索引？

索引是一种数据结构，相当于目录，包含着对数据表中所有记录的引用指针，以协助查询数据。

9. 索引优缺点

优点：

• 加快数据检索速度
• 查询过程中优化隐藏器，提高系统性能

缺点：

• 时间：创建索引、维护索引需要时间；在对数据进行CRU的时候索引也要动态维护，会降低CRU执行效率
• 空间：索引需要占用物理空间

10. 索引的类型

1. 主键索引

   数据列不允许重复，不允许为Null，一个表只能有一个主键

2. 唯一索引

   数据列不允许重复，允许为Null，一个表允许多个列创建唯一索引

   创建方法：

   • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
   • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

3. 普通索引

   基本的索引类型，没有唯一性的限制，允许为Null

   创建方法：

   • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
   • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

4. 全文索引

   是目前搜索引擎使用的一种关键技术，可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

11. 索引的基本原理：把无序的数据变成有序的数据

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

12. 索引设计原则

1. 适合索引的列是出现在where子句中的列或者是连接子句中指定的列
2. 基数较小的类，索引效果较差，没必要建立
3. 使用短索引，如果对长字符串列进行索引，应指定一个前缀长度用于节省空间
4. 不要过度索引

13. 创建索引的原则

1. 最左前缀匹配原则

   最左优先，在创建多列索引时，where子句中使用最频繁的一列放在最左边。

   建立索引时，对于索引字段，mysql会一直向右匹配直到遇到==>、<、between、like==就停止匹配，例如对于a=1 and b=2 and c>3 and d=4建立（a,b,c,d）顺序的索引，检测到>，到c就停止了，d是用不到索引的，所以要把d提左，建立（a,b,d,c）索引，a,b,d的顺序可以任意调整

2. 较频繁作为查询条件的字段才去创建索引

3. 更新频繁字段不适合创建索引

4. 不能有效区分数据的列不适合作为索引列

5. 尽量去扩展索引，而不要新建索引

6. 定义有外键的数据列一定要建立索引

7. 对于查询中很少涉及的列和重复值比较多的列不要建立索引

8. 对于定义为text、image和bit的数据类型的列不要建立索引

14. 创建索引的三种方式

1. 在执行CREATE TABLE时创建索引
2. 使用ALTER TABLE命令创建索引（创建普通索引、UNIQUE索引或PRIMARY KEY索引）
3. 使用CREATE INDEX命令创建索引（创建普通索引或UNIQUE索引）

15. 删除索引的方式

• 删除普通索引、唯一索引、全文索引：ALTER TABLE [TABLE NAME] DROP KEY [INDEX NAME]
• 删除主键索引：ALTER TABLE [TABLE NAME] DROP PRIMARY KEY，注意：如果主键自增长，不能直接执行此操作，需要取消自增长再删除

16. 未完待续……