mysql 数据库相关

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

通俗点讲

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

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

聚簇索引具有唯一性
由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引一个误区：把主键自动设为聚簇索引
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果你已经设置了主键为聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。
此时其他索引只能被定义为非聚簇索引。这个是最大的误区。有的主键还是无意义的自动增量字段，那样的话Clustered index对效率的帮助，完全被浪费了。
刚才说到了，聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。
记住我们的最终目的就是在相同结果集情况下，尽可能减少逻辑IO。结合图再仔细点看
1.
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2.
若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。聚簇索引的优势
看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？
1.
由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2.
辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
3.
聚簇索引适合用在排序的场合，非聚簇索引不适合
4.
取出一定范围数据的时候，使用用聚簇索引
5.
二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
6.
可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势
1.
维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2.
表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，

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

主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）
1.
如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

为什么主键通常建议使用自增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）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。