聚簇索引与非聚簇索引(也叫二级索引)
通俗点讲
聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据
非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,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%。这主要是因为:
- doublewrite是在一个连续的存储空间, 所以硬盘在写数据的时候是顺序写,而不是随机写,这样性能更高。
- 将数据从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)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。
网友评论