MySql数据库相关

聚簇索引与非聚簇索引（也叫二级索引）

通俗点讲

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。何时使用聚簇索引与非聚簇索引

聚簇索引具有唯一性
由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引一个误区：把主键自动设为聚簇索引
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。
此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段，那样的话Clustered index对效率的帮助，完全被浪费了。
刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。
记住我们的最终目的就是在相同结果集情况下，尽可能减少逻辑IO。结合图再仔细点看

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。聚簇索引的优势
看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

聚簇索引适合用在排序的场合，非聚簇索引不适合

取出一定范围数据的时候，使用用聚簇索引

二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据

可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片

表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，

所以建议使用int的auto_increment作为主键

主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）

如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

为什么主键通常建议使用自增id
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）
不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。mysql中聚簇索引的设定
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

mysql数据隔离级别

脏读：事务A读取了事务B未提交的数据。
不可重复度：事务A多次读取同一份数据，事务B在此过程中对数据修改并提交，导致事务A多次读取同一份数据的结果不一致。
幻读：事务A修改数据的同时，事务B插入了一条数据，当事务A提交后发现还有数据没被修改，产生了幻觉。
不可重复读侧重于update操作，幻读侧重于insert或delete。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED（解决赃读 select）	No	Yes	Yes
REPEATABLE READ（update 解决不可重复读的问题只需锁住满足条件的行）	No	No	Yes 可能（MySQLInnoDB不可能）
SERIALIZABLE（解决幻读 insert 解决幻读需要锁表）	No	No	No

InnoDB并发控制MVCC特性
InnonDB是一个支持行锁的存储引擎，为了提供更好支持的并发，使用了非锁定读，不需要等待访问数据上的锁释放，而是读取行的一个快照，该方法是通过InnonDB MVCC特性实现的。
MVCC是Multi-Version Concurrency Control的简称，即多版本并发控制，作用是让事务在并行发生时，在一定隔离级别前提下，可以保证在某个事务中能实现一致性读，也就是该事务启动时根据某个条件读取到的数据，直到事务结束时，再次执行相同条件，还是读到同一份数据，不会发生变化。
MVCC的好处
读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，可以增加系统的并发性能。

READ COMMITTED
在READ COMMITTED读已提交下，一致读快照（Read View）是在每次SELECT后都会生成最新的Read View，即每次SELECT都能读取到已COMMIT的数据，就会存在不可重复读、幻读 现象。
REPEATABLE READ
REPEATABLE READ默认的隔离级别，一致读快照（Read View）是在第一次SELECT发起时建立，之后不会再发生变化。如果在同一个事务中发出多个非 锁定SELECT语句，那么这些SELECT语句在事务提交前返回的结果是一致的。

当开启事务执行更新语句（insert/update/deeldte），会经过Server层的处理生成执行计划，然后调用存储引擎层接口去读写数据，用户没有触发COMMIT或ROLLBACK之前，这些Uncommitted Data的数据称为前镜像（Post Image），数据存储在Undo Log，以便用户回滚或者MySQL Server Crash的恢复，同时Undo Log是循环覆盖使用。

回滚记录
insert：反向操作是delete，undo里记录的是delete相关信息，存储主键id即可。
udpate：反向操作是update，undo里记录的是update前的相关数据。
delete：反向操作是insert，undo里记录的是insert values(…..)相关的记录。
从这里可以知道，更新操作占用Undo空间的大小排序如下：
delete > update > insert
所以不建议物理delete删除数据，会产生大量的Undo Log，Undo快被写满就会发生切换，在次期间会有大量的IO操作，导致业务的DML都会变得很慢。

快照读解决了不可重复读问题。不可重复读，即前后多次读取，数据内容不一致
普通读是通过 undo log + MVCC 来实现的

在执行修改数据的时候，首先会执行当前读，然后把返回的数据加锁，之后执行修改数据。加锁是防止别的事务在这个时候对这条记录做什么，默认加的是排他锁，也就是你读都不可以，这样就可以保证数据不会出错了。
当前读是通过 next-key 锁(行记录锁+间隙锁)来是实现的。
如果一个事务执行当前读操作且不提交，另一个事务是无法执行当前读操作的。这也说明了当前读加了锁。
next-key lock的确是解决了幻读问题，但是next-key lock在并发情况下也经常会造成死锁。死锁检测和处理也会花费时间，一定程度上影响到并发量。

缓存

内存中(buffer pool)未刷到磁盘的数据称为脏数据(dirty data)。由于数据和日志都以页的形式存在，所以脏页表示脏数据和脏日志。
上一节介绍了日志是何时刷到磁盘的，不仅仅是日志需要刷盘，脏数据页也一样需要刷盘。
在innodb中，数据刷盘的规则只有一个：checkpoint。但是触发checkpoint的情况却有几种。不管怎样，checkpoint触发后，会将buffer中脏数据页和脏日志页都刷到磁盘。
innodb存储引擎中checkpoint分为两种：
sharp checkpoint：在重用redo log文件(例如切换日志文件)的时候，将所有已记录到redo log中对应的脏数据刷到磁盘。

fuzzy checkpoint：一次只刷一小部分的日志到磁盘，而非将所有脏日志刷盘。有以下几种情况会触发该检查点：
master thread checkpoint：由master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。
flush_lru_list checkpoint：从MySQL5.6开始可通过 innodb_page_cleaners 变量指定专门负责脏页刷盘的page cleaner线程的个数，该线程的目的是为了保证lru列表有可用的空闲页。
async/sync flush checkpoint：同步刷盘还是异步刷盘。例如还有非常多的脏页没刷到磁盘(非常多是多少，有比例控制)，这时候会选择同步刷到磁盘，但这很少出现；如果脏页不是很多，可以选择异步刷到磁盘，如果脏页很少，可以暂时不刷脏页到磁盘
dirty page too much checkpoint：脏页太多时强制触发检查点，目的是为了保证缓存有足够的空闲空间。too much的比例由变量 innodb_max_dirty_pages_pct 控制，MySQL 5.6默认的值为75，即当脏页占缓冲池的百分之75后，就强制刷一部分脏页到磁盘。
由于刷脏页需要一定的时间来完成，所以记录检查点的位置是在每次刷盘结束之后才在redo log中标记的。
MySQL停止时是否将脏数据和脏日志刷入磁盘，由变量innodb_fast_shutdown={ 0|1|2 }控制，默认值为1，即停止时只做一部分purge，忽略大多数flush操作(但至少会刷日志)，在下次启动的时候再flush剩余的内容，实现fast shutdown。

mysql的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，这两个是是控制MySQL 磁盘写入策略以及数据安全性的关键参数。下面从参数含义，性能，安全角度阐述两个参数为不同的值时对db 性能,数据的影响。
一、参数意义

innodb_flush_log_at_trx_commit
如果innodb_flush_log_at_trx_commit设置为0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作;
如果innodb_flush_log_at_trx_commit设置为1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去;
如果innodb_flush_log_at_trx_commit设置为2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
注意：由于进程调度策略问题,这个"每秒执行一次 flush(刷到磁盘)操作"并不是保证100%的"每秒"。

sync_binlog
sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。
注意：如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。
由此可见，当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。

InnoDB双写缓冲

两次写（double write）
InnoDB使用了一种叫做double write的特殊文件flush技术
1）在把pages写到data files之前，InnoDB先把它们写到一个叫doublewrite buffer的连续区域内
2）在写doublewrite buffer完成后，InnoDB才会把pages写到data file的适当的位置。
如果在写page的过程中发生意外崩溃，InnoDB在稍后的恢复过程中在doublewrite buffer中找到完好的page副本用于恢复。
回到顶部
partial page write问题
由于InnoDB和操作系统的页大小不一致，InnoDB页大小一般为16k,操作系统页大小为4k，导致InnoDB回写dirty数据到操作
系统中，一个页面需要写4次，写入过程出现问题无法保持原子性。写的过程如果崩溃或者断电，可能导致只有一部分写回
到操作系统文件系统中，整个数据只是部分成功，其实数据是损坏的。
redolog 不能解决
redolog记录的是数据页的物理操作：对 XXX表空间中的XXX数据页XXX偏移量的地方做了XXX更新。如果页都损坏了，是
无法进行任何恢复操作的。所以我们需要页的一个副本，如果服务器宕机了，可以通过副本把原来的数据页还原回来。这就
是doublewrite的作用。
回到顶部
doublewrite buffer
双写缓冲位于系统表空间上，128个页（2个区）大小是2MB。
1）将脏数据复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分2次，每次写入1MB到共享表空间，然后马
上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，在这个过程中，doublewrite是顺序写，开销并不大
2）在完成doublewrite写入后，再将double write buffer写入各表空间文件，这时是离散写入。
所以在正常的情况下, MySQL写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是从doublewrite
buffer写到真正的数据文件中。
如果发生了极端情况（断电），InnoDB再次启动后，发现了一个page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。
回到顶部
doublewrite的缺点
位于共享表空间上的doublewrite buffer实际上也是一个文件，写共享表空间会导致系统有更多的fsync操作, 而硬盘的fsync性能因素会降低
MySQL的整体性能，但是并不会降低到原来的50%。这主要是因为：

1. doublewrite是在一个连续的存储空间, 所以硬盘在写数据的时候是顺序写，而不是随机写，这样性能更高。
2. 将数据从doublewrite buffer写到真正的segment中的时候，系统会自动合并连接空间刷新的方式，每次可以刷新多个pages。
   回到顶部
   是否一定需要doublewrite
   在一些情况下可以关闭doublewrite以获取更高的性能。比如在slave上可以关闭，因为即使出现了partial page write问题，数据还是可以从中继日志中恢复。设置InnoDB_doublewrite=0即可关闭doublewrite buffer。

数据库通常借助日志来实现事务，常见的有undo log、redo log，undo/redo log都能保证事务特性，undolog实现事务原子性，redolog实现事务的持久性。

数据库3范式

一、第一范式
1NF是对属性的原子性，要求属性具有原子性，不可再分解；二、第二范式
2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖；三、第三范式
3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。简而言之，第一范式就是无重复的域。
满足第二范式必须先满足第一范式。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。