mysql原⼦操作_MySQL是如何保证⼀致性、原⼦性和持久性
的！
来源于    t/developer/article/1600883
编辑：业余草
今天，我们来简单的看⼀下 MySQL 的⼀致性、原⼦性和持久性问题。后⾯还扩展了 15 个简单的⾯试题，希望⼤家喜欢！
1、Mysql怎么保证⼀致性的？
OK，这个问题分为两个层⾯来说。正则表达式匹配非字符串
从数据库层⾯，数据库通过原⼦性、隔离性、持久性来保证⼀致性。也就是说ACID四⼤特性之中，C(⼀致性)是⽬的，A(原⼦性)、I(隔离性)、D(持久性)是⼿段，是为了保证⼀致性，数据库提供的⼿段。数据库必须要实现AID三⼤特性，才有可能实现⼀致性。例如，原⼦性⽆法保证，显然⼀致性也⽆法保证。
但是，如果你在事务⾥故意写出违反约束的代码，⼀致性还是⽆法保证的。例如，你在转账的例⼦中，你的代码⾥故意不给B账户加钱，那⼀致性还是⽆法保证。因此，还必须从应⽤层⾓度考虑。
从应⽤层⾯，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！
2、Mysql怎么保证原⼦性的？
OK，是利⽤Innodb的undo log。
undo log名为回滚⽇志，是实现原⼦性的关键，当事务回滚时能够撤销所有已经成功执⾏的sql语句，他需要记录你要回滚的相应⽇志信息。
例如
(1)当你delete⼀条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你update⼀条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执⾏update操作
(3)当年insert⼀条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执⾏delete操
undo log记录了这些回滚需要的信息，当事务执⾏失败或调⽤了rollback，导致事务需要回滚，便可以利⽤undo log中的信息将数据回滚到修改之前的样⼦。
ps：具体的undo log⽇志长啥样，这个可以写⼀篇⽂章了。⽽且写出来，看的⼈也不多，姑且先这么
简单的理解吧。
3、Mysql怎么保证持久性的？
OK，是利⽤Innodb的redo log。
正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进⾏修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
怎么解决这个问题？
简单啊，事务提交前直接把数据写⼊磁盘就⾏啊。
这么做有什么问题？
只修改⼀个页⾯⾥的⼀个字节，就要将整个页⾯刷⼊磁盘，太浪费资源了。毕竟⼀个页⾯16kb⼤⼩，你只改其中⼀点点东西，就要将16kb 的内容刷⼊磁盘，听着也不合理。两张表格如何使用vlookup
毕竟⼀个事务⾥的SQL可能牵涉到多个数据页的修改，⽽这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会⽐较慢。
于是，决定采⽤redo log解决上⾯的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log⽇志进⾏刷盘(redo log⼀部分在内存中，⼀部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
4. UNION ALL 与 UNION 的区别
UNION和UNION ALL关键字都是将两个结果集合并为⼀个。
UNION在进⾏表链接后会筛选掉重复的记录，所以在表链接后会对所产⽣的结果集进⾏排序运算，删除重复的记录再返回结果。
⽽UNION ALL只是简单的将两个结果合并后就返回。
由于UNION需要排序去重，所以 UNION ALL 的效率⽐ UNION 好很多。
5. TRUNCATE 与 DELETE 区别
TRUNCATE 是DDL语句，⽽ DELETE 是DML语句。
TRUNCATE 是先把整张表drop调，然后重建该表。⽽ DELETE 是⼀⾏⼀⾏的删除，所以 TRUNCATE 的速度肯定⽐ DELETE 速度快。
TRUNCATE 不可以回滚，DELETE 可以。
TRUNCATE 执⾏结果只是返回0 rows affected，可以解释为没有返回结果。
TRUNCATE 会重置⽔平线(⾃增长列起始位)，DELETE 不会。
TRUNCATE 只能清理整张表，DELETE 可以按照条件删除。
⼀般情景下，TRUNCATE性能⽐DELETE好⼀点。
6. TIMESTAMP 与 DATETIME 的区别
相同点
TIMESTAMP 列的显⽰格式与 DATETIME 列相同。显⽰列宽固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。
不同点
weight词转TIMESTAMP
4个字节存储，时间范围：1970-01-01 08:00:01~2038-01-19 11:14:07。
值以UTC格式保存，涉及时区转化，存储时对当前的时区进⾏转换，检索时再转换回当前的时区。
DATETIME
8个字节存储，时间范围：1000-10-01 00:00:00~9999-12-31 23:59:59。
实际格式存储，与时区⽆关。
7. 什么是联合索引
两个或更多个列上的索引被称作联合索引，联合索引⼜叫复合索引。
8. 为什么要使⽤联合索引
减少开销：建⼀个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。减少磁盘空间的开销。
覆盖索引：对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL 可以直接通过遍历索引取得数据，⽽⽆需回表，这减少了很多的随机io操作。覆盖索引是主要的提升性能的优化⼿段之⼀。
效率⾼：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql select from table where col1=1 and col2=2 and col3=3，假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W * 10%=100w条数据，然后再回表从100w条数据中到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w * 10% * 10% * 10%=1w，效率得到明显提升。
9. MySQL 联合索引最左匹配原则
在 MySQL 建⽴联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
MySQL 会⼀直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建⽴(a,b,c,d)顺序的索引，d是⽤不到索引的，如果建⽴(a,b,d,c)的索引则都可以⽤到，a,b,d的顺序可以任意调整。
= 和 in 可以乱序，⽐如a = 1 and b = 2 and c = 3 建⽴(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
10. 什么是聚集和⾮聚集索引
聚集索引就是以主键创建的索引。
源代码皮肤有几个
⾮聚集索引就是以⾮主键创建的索引。
11. 什么是覆盖索引
mysql面试题sql
覆盖索引(covering index)指⼀个查询语句的执⾏只⽤从索引页中就能够取得(如果不是聚集索引，叶⼦节点存储的是主键+列值，最终还是要回表，也就是要通过主键再查⼀次)，避免了查到索引后，再做回表操作，减少I/O提⾼效率。
可以结合第10个问题更容易理解。
12. 什么是前缀索引
前缀索引就是对⽂本的前⼏个字符(具体是⼏个字符在创建索引时指定)创建索引，这样创建起来的索引更⼩。但是MySQL不能在ORDER BY或GROUP BY中使⽤前缀索引，也不能把它们⽤作覆盖索引。
创建前缀索引的语法：
ALTER TABLE table_name ADD
KEY(column_name(prefix_length))
13. InnoDB 与 MyISAM 索引存储结构的区别
MyISAM索引⽂件和数据⽂件是分离的，索引⽂件仅保存数据记录的地址。
⽽在InnoDB中，表数据⽂件本⾝就是按B+Tree组织的⼀个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据⽂件本⾝就是主索引，所以必须有主键，如果没有显⽰定义，⾃动为⽣成⼀个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。
InnoDB的辅助索引(Secondary Index，也就是⾮主键索引)存储的只是主键列和索引列，如果主键定义的⽐较⼤，其他索引也将很⼤。
MyISAM引擎使⽤B+Tree作为索引结构，索引⽂件叶节点的data域存放的是数据记录的地址，指向数据⽂件中对应的值，每个节点只有该索引列的值。
MyISAM主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求key是唯⼀的，辅
助索引可以重复，(由于MyISAM 辅助索引在叶⼦节点上存储的是数据记录的地址，和主键索引⼀样，所以不需要再遍历⼀次主键索引)。
简单的说：
主索引的区别：InnoDB的数据⽂件本⾝就是索引⽂件。⽽MyISAM的索引和数据是分开的。
辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值⽽不是地址。⽽MyISAM的辅助索引和主索引没有多⼤区别。
14. 为什么尽量选择单调递增数值类型的主键
InnoDB中数据记录本⾝被存于主索引(B+树)的叶⼦节点上。这就要求同⼀个叶⼦节点内(⼤⼩为⼀个内存页或磁盘页)的各条数据记录按主键顺序存放，因此每当有⼀条新的记录插⼊时，MySQL会根据其主键将其插⼊适当的结点和位置，如果页⾯达到装载因⼦(InnoDB默认为15/16)，则开辟⼀个新的页。
如果使⽤⾃增主键，那么每次插⼊新的记录，记录就会顺序添加到当前索引结点的后续位置，当⼀页写满，就会⾃动开辟⼀个新的页，这样就会形成⼀个紧凑的索引结构，近似顺序填满。由于每次插⼊时也不需要移动已有数据，因此效率很⾼，也不会增加很多开销在维护索引上。
如果使⽤⾮⾃增主键，由于每次插⼊主键的值近似于随机，因此每次新纪录都要被插⼊到现有索引页的中间某个位置，此时MySQL不得不为了将新记录查到合适位置⽽移动元素，甚⾄⽬标页可能已经被回写到磁盘上⽽从缓存中清掉，此时⼜要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了⼤量的碎⽚，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页⾯。
简单的说：
索引树只能定位到某⼀页，每⼀页内的插⼊还是需要通过⽐较、移动插⼊的。所以有序主键可以提升插⼊效率。
15. 建表时，int 后⾯的长度的意义
int占多少个字节，已经是固定的了，长度代表了显⽰的最⼤宽度。如果不够会⽤0在左边填充，但必须搭配zerofill使⽤。也就是说，int的长度并不影响数据的存储精度，长度只和显⽰有关。
16. SHOW INDEX 结果字段代表什么意思
Table：
表名。
Non_unique：
0：该索引不含重复值。
1：该索引可含有重复值。
Key_name：
索引名称，如果是注解索引，名称总是为PRIMARY。
Seq_in_index：
该列在索引中的序号，从 1 开始。例如：存在联合索引 idx_a_b_c (a,b,c)，则a的Seq_in_index=1，b=2，c=3。
Column_name：
列名。
Collation：
索引的排列顺序：A(ascending)，D (descending)，NULL (not sorted)。
Cardinality：
⼀个衡量该索引的唯⼀程度的值，可以使⽤ANALYZE TABLE(INNODB) 或者 myisamchk -a(MyISAM)更新该值。
如果表记录太少，该字段的意义不⼤。⼀般情况下，该值越⼤，索引效率越⾼。
Sub_part：
对于前缀索引，⽤于索引的字符个数。如果整个字段都加上了索引，则显⽰为NULL。
Null：
YES：该列允许NULL值。
''：该列不允许NULL值。
Index_type：
索引类型，包括(BTREE, FULLTEXT, HASH, RTREE)。
如何解决like'%字符串%'时索引失效？
LIKE问题：like 以通配符开头 ('%abc…')，mysql索引失效会变成全表扫描的操作。
罪魁祸⾸是%，不是LIKE，LIKE 条件是 type = range 级别
%xxx%：全表扫描
%xxx：全表扫描
xxx%：range
解决办法：
使⽤覆盖索引，可以由 ALL 变为INDEX，为啥呢？覆盖索引之后就能使⽤使⽤索引进⾏全表扫描。这⾥要注意⼀下，使⽤符合索引的时候，命中⼀个字段就可以，不⽤全部命中。
17. MySQL⾼效分页
存在SQL：SELECT * FROM ttl_product_info ORDER BY id LIMIT N,M。其中 LIMIT N,M 存在的问题最⼤：取出N+M⾏，丢弃前N ⾏，返回 N ~ N+M ⾏的记录，如果N值⾮常⼤，效率极差(表记录1500w，N=10000000,M=30 需要9秒)。
解决办法：SQL：SELECT id FROM ttl_product_info WHERE id > N LIMIT M，id 列是索引列，id > N属于 range 级别，效率⾃然⾼，然后从位置开始取30条记录，效率极⾼(表记录1500w，N=10000000,M=30，需要0.9毫秒)。
当然想要实现上述效果的前提是：
shirley英文名俗吗
id是唯⼀索引，⽽且单调递增。
N 的值是上⼀次查询的记录的最后⼀条id，(需要前端保存⼀下，不能直接⽤传统的⽅法获得)
不⽀持跨页查询，只能按照第1，2，3，4页这样查询逐页查询。
总结
知识都是在于平时的积累，养成良好的习惯，多阅读源码，年薪百万不是梦！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。