Mysql入门

①对Mysql事务理解？（大部分网上内容，未提炼）

答：在mysql中只有InnDB引擎支持事务，事务有着四个特性ACID，其中Atomic原子性，Consistency一致性，Isolation隔离性，Durability持久性；
原子性，要么全部执行，要么全部不执行；如果事务中一个SQL语句执行失败，则已执行的语句也必须回滚，数据库推出到事务前的状态；实现原理，涉及Mysql事务日志undo log（回滚日志），当事务对数据数据进行修改时，InnoDB会生成对应的undo log，如果事务执行失败或者调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的状态。undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作；

一致性，事务的执行使得数据库从一种正确状态转化为另外一种正确状态；一致性是事务追求的最终目录，而其他AID只是手段，都是为了保证数据库状态一致性。

隔离性，在事务正确提交之前，不允许把该事务对数据的任何改变提供给其他事务；事务隔离性讨论，主要可以分为两个方面，写操作（事务）对另外一个写操作的影响，锁机制保证隔离性；写操作（事务）对读操作的影响，MVCC保证隔离性。锁机制基本原理概括为，事务在修改数据之前，需要先获取相应的锁（排他锁、写锁），获取锁之后，事务便可以修改数据，该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。按照粒度，锁可以分为表级锁，行锁（排他锁、共享锁）以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁只锁定需要操作的数据，并发性能好。但是由于加锁本身消耗资源（获取锁、检查锁、释放锁等操作），因此在锁定数据较多情况下使用表锁，可以节省大量资源。Mysql中不同存储引擎支持锁是不一样的，如MyIsam只支持表锁，而InnoDB支持表锁和行锁，且出于性能考虑，绝大多数情况下使用都是行锁。可以通过SQL命令查看锁整体情况。
接着讨论写操作对读操作的影响，脏读，不可重复读，幻读，脏读指当前事务A中可以读到其他事务B未提交的数据，这种现象是脏读；不可重复读指在事务A中先后两次读取同一个数据，两次读取的结果不一样（因为B事务已经提交修改数据了），这种现象称为不可重复读，脏读和不可重复读区别在于前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据；幻读，在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读，不可重复读和幻读的区别可以理解为前者是数据变化了，后者是数据的行数变了。
事务隔离级别，SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：

在实际应用中，RU在并发时会导致很多问题，且性能区别其他隔离级别提高很有限，因此使用比较少；而串行化强制执行事务，并发效率很低，只有要求数据一致性要求极高且可以接受没有并发时使用，因此使用也比较少；大多数数据库系统中，默认隔离解绑是RC（Oracle）或RR，InnoDBb默认的隔离级别是RR，RR无法避免幻读问题（…），但是InnoDB实现避免了幻读问题；RR解决脏读、不可重复读、幻读等问题，使用的是MVCC：MVCC全称Multi-Version Concurrency Control,即多版本的并发控制协议。MVCC的特点是，在同一个时刻，不同的事务读取到的数据可能是不同版本的，MVCC优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列（标记位，列包含了数据行的隐藏ID、事务ID（版本号）、指向undo log（存在额外purge线程，查询比现在古老的事务并删除它们）的回滚指针等）和undo log回滚日志，当读数据时，Mysql可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC；MVCC解决脏读说明，事务A在某时间节点读取事务B修改但未提交事务的数据，通过隐藏列发现，读取数据未提交状态，此时事务A通过数据隐藏列的回滚指针，指向回滚操作，得到事务B修改前的数据，从而避免脏读；解决不可重复读说明，当事务A第一次读取数据，将会记录数据的版本号（数据的版本号是以row为单位记录的），假如版本号为1，当事务B提交后，该行记录增加为2，当事务A再一次读取数据时，发现数据的版本2大于第一次读取的版本号1，因此会通过隐藏列找到undo log日志执行回滚，得到版本1的数据，从而实现了可重复读；而对于幻读，InnoDB实现RR通过next-keylock机制避免了幻读现象，next-keylock是行锁的一种，实现相当于reord lock（记录锁）+ gap lock（间隙锁），特点是不仅会锁住记录本身，还会锁住一个范围；

持久性，事务提交后，其结果永久保存在数据库中；实现原理，涉及Mysql事务日志redo log（重做日志），redo log存在的背景问题是，InnoDB作为Mysql的存储引擎，数据是存放在磁盘中，但是如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存（Buffer Pool）,BP中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲，当从数据库读取数据时，先从BP读取，如果BP中没有，则从磁盘读取后放入BP。当向数据库写入数据时，会首先写入BP，BP中修改的数据会定期刷新到磁盘中（这过程称刷脏）。BP的使用大大提高了读写数据的效率，但是也有新的问题，如果Mysql宕机，而此时BP中修改的数据还没写入磁盘，这样导致数据的丢失，事务的持久性无法保障；于是，redo log日志被引入来解决这个问题，当数据修改时，除了修改BP中数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口
对redo log进行刷盘，如果Mysql宕机，重启时可以读取redo log中的数据，对数据进行恢复，redo log采用的是WAL（预写式日志），所有修改先写入日志，再更新BP，保证数据不会因Mysql宕机而丢失，从而满足持久性；
既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将BP中修改的数据写入磁盘（脏刷）要快呢？答：主要有两个方面原因，脏刷是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。刷脏是以数据页（Page）为单位，Mysql默认页大小是16KB，一个Page上一个小修改都要整页写入，而redo log中只包含真正需要写入的部分，无效IO大大减少。
redo log 与bin log，都是可以记录写操作并用于数据的恢复，但是两者还是有根本不同，其中作用不同，redo log只要用于故障恢复，保证Mysql宕机也不会影响持久性，binlog是用于时间点恢复，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制；层次不同，redo log是InnoDB存储引擎实现的，而binlog是Mysql服务器层实现的，同时支持InnoDB和其他存储引擎；内容不同，redo log是物理日志，内容基于磁盘的Page，binlog是逻辑日志，存储的内容是一条条SQL；写入时机不同，redo log的写入时机相对多元，当事务提交会调用fsync接口对redo log进行刷盘，这是默认策略下，修改
innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性无法保证。除了事务提交时，还有其他刷盘时机，如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快，而binlog在事务提交时写入；

②聚簇索引和非聚簇索引

答：聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据；
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行（主键值）；
聚簇索引按照每张表的主键（设置了主键）构建一颗B+树索引结构，同时叶子节点中存放key值对应表的整行记录，同时聚簇索引的叶子节点成为数据页，当通过聚簇索引找到索值就是找到了数据。
代表就是InnoDB引擎，主键索引通过主键聚集数据，如果没有指定主键，innoDB引擎会选择非空不重复的唯一标识数据记录的列作为主键，如果不存在这种列，则自动生成隐式字段作为主键；辅助索引是在聚簇索引上创建的索引，辅助索引访问整行数据需要二次查询，辅助索引都是非聚簇索引，如复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不是行的物理地址，是主键值；
MyISAM引擎主键索引使用B+树作为索引结构，叶子节点的data域存放的树数据记录的地址RID，而它的辅助索引与主键索引没有区别，只是主键索引要求唯一性
总结，①innoDB引擎，唯一聚簇索引 + 多个非聚簇索引（辅助索引），MyISAM引擎，非簇索引；
②聚簇索引，B+树，叶子节点存储整行数据，非聚簇索引索引，B+树，叶子节点存储（innoDB引擎是主键值，MyISAM引擎行标识符RID，可以说是地址），非聚簇索引索引都是需要回表，二次查询的；

2.1为什么不用建议使用过长的字段或UUID作为主键？
答：在InnoDB引擎中，所有辅助索引都是引用到主索引，过长的主索引会令辅助索引过大，增加了IO；聚簇索引的数据的物理地址与索引顺序是一样的，只有索引相邻，那么对应的数据一定也是相邻存放在磁盘上的。可以想象，如果主键不是自动增长ID，需要不断调整数据的物理地址和分页（是否正确？），会造成磁盘碎片，如果是自增长数据都可以落在索引树的最右边追加记录；在MyISAM引擎中，主或辅助索引都是非聚簇索引，那么它数据的物理地址必然是凌乱的，拿到这些地址，按照合适算法进行IO读取，比起聚簇索引更加耗时。但如果涉及大数据量的排序、全表扫描、count之类的操作，MyISAM引擎会更加占据优势，因为索引占用空间小，这些操作是需要在内存中完成；

2.2为什么用B+树作为索引结构，而不用B树，B-树？
答：B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。这样做起范围扫描会十分简单，对于B树来说，则需要在叶子节点和内部节点不停的往返，做一次中序遍历；
非叶子接的不会带上指向记录的指针，这样，一个块中可以容纳更多的索引项，一可以降低树的高度，二使一个内部节点可以定位更多的叶子节点；

③索引失效

答：1.查询字段本身未建立索引；
2.like查询以%开头，like 前缀匹配失效，后缀走索引；
3.or语句前后没有同时使用索引，当or左右查询字段只有一个索引，该索引失效，只有当or左右查询字段均为索引时，才生效，也就是说or各自走各自索引？
4.组合索引，不按照最左匹配原则，失效；
5.出现数据类型的隐式转化；
6.在索引列上使用is null或is not null
7.在索引列上使用not <> !=操作符不会用到索引；
8.在索引列上使用计算操作，使用函数
expain命令SQL语句分析

④最左匹配原则

答：最左原则顾名思义就是从最左边开始匹配的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，其针对的是组合索引（又名联合索引）
例子：ABC索引组合包含了A、AB、ABC三个索引；
①当查询条件为A、B、C；
EXPLAIN SELECT FROM expain_test WHERE a = 1 and b = “1” and c = “a”
possible_keys：表示查询时，可能使用的索引，abc
②当查询条件为B、C、A或者C、B、A；
EXPLAIN SELECT FROM expain_test WHERE c = “a” and b = “1” and a = 1
possible_keys：abc；
原因：寻找最低成本的执行计划，MYSQL服务器会根据一定规则对SQL进行优化，mysql中会使用Index Merge intersection algorithm算法来调整条件子句顺序；
③当查询条件为A、B和A、C；
EXPLAIN SELECT FROM expain_test WHERE a = 1 and b = “1”
EXPLAIN SELECT FROM expain_test WHERE c = “1” and a = 1
possible_keys：abc；AB组合使用组合索引包含AB索引，AC组合使用组合索引包含的A索引
④当查询条件为B、C；
EXPLAIN SELECT * FROM expain_test WHERE c = “1” and b= “1”
possible_keys：null;全表查询，组合索引的最左匹配原则，mysql会根据A来确定下一步的搜索方向，当没有A时，就只能去全记录去寻找；

⑤mysql引擎

答：①InnoDB存储引擎，InnoDB是MySQL默认的事务型引擎，也是最重要、最广泛的存储引擎。它的设计是用来处理大量短期事务，短期事务大部分是正常提交的，很少回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中，也很流行；InnoDB的数据存储在表空间中，表空间是由InnoDB管理的黑盒文件系统，由一系列系统文件组成，同时InnoDB引擎的索引文件和数据文件是在一个表空间中，因此迁移数据更加便捷也更大，且缓存Buffer Pool中缓存的是数据和索引，一定大小内存下，大数据量扫描MyISAM引擎更占据优势；InnoDB通过间隙锁（next-key locking）防止幻读的出现。InnoDB是基于聚簇索引建立，与其他引擎有很大区别，聚簇索引对主键查询有很高的性能，不过它的二级索引必须包含主键列，所以如果主键列很大的话，索引会很大；InnoDB支持外键完整性约束；
②在5.1之前，MyISAM是默认的引擎，MyISAM有大量的特性，包括全文索引，压缩，空间函数。但是MyISAM不支持事务和行级锁，而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务，但是很多人概念中依然是不支持事务的引擎；MyISAM对于一些只读数据，或者表空间较小，可以忍受恢复操作，可以使用；MyISAM会将表存储在两个文件中：数据文件和索引文件（分开），分别是.MYD、.MYI扩展名。MyISAM表可以包含动态或者静态行？MySQL会根据表定义选择那种行格式。MyISAM表的行记录数（count），取决于磁盘空间和操作空间的单个文件最大尺寸；
在MySQL中，默认配置只能存储256TB的数据。因为指向数据记录的指针长度的6字节。需要修改可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项。两个相乘的最大的大小，会导致重建索引；
MyISAM是对整个表加锁，而不是行锁（InnoDB支持行锁和表锁），读取的时候对表加共享锁，写入的时候加排他锁（与InnoDB相同）。但是在表有读取查询的同时，也可以往表写入记录（原因？）；
对于MyISAM，即使是Blob，Text等等长字段，也可以基于前500字符创建索引，MyISAM支持全文索引，这是一个基于分词创建的索引，也可以支持复杂的查询。
MyISAM可以选择延迟更新索引键，在创建表的时候指定delay_key_write选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是写到缓存区，只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能，但是在主机崩溃时会造成索引损坏，需要执行修复操作。
MyISAM另一个特性是支持压缩表。如果数据在写入后不会修改，那么这个表适合MyISAM压缩表，压缩表是不可以修改数据的，可以极大的减少磁盘占用，因此可以减少磁盘IO，提升性能，压缩表也支持索引，但是索引也是只读的。
由于没有行锁机制，所以在海量写入的时候，会导致所有查询处于Locked状态。
③其他索引：Memroy等

⑥mysql索引类型

1、normal 普通索引；
2、unique 唯一索引，不允许重复的索引，如果该字段保证不会重复时，可以设置为unique；
3、full text 全文索引，full text用于搜索很长的一篇文章的时候，效果最好。如果是短文本，使用普通索引即可；
4、primary 主键索引；

⑦mysql索引方法

1、B+树索引，是最常见索引，被索引列的所有值都是排序过得，每个叶子节点到根节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序；
B+树索引，在MyISAM里的形式和InnoDB稍有不同，MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址，而InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录；
2、哈希索引，仅支持等于=，IN，<=>精确查询，不能使用范围查询；不支持排序；在任何时候都不能避免表扫描；检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从跟节点到枝节点，最后才访问到页节点这样多次IO，所以哈希索引查询效率远远高于B-Tree索引；只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突（哈希冲突）太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引；

问题：
1、在实际操作过程中，应该选取表中哪些字段作为索引？
答：为了使索引使用效率更高，必须考虑在哪些字段建立索引，使用什么类型，有七大原则：
1．选择唯一性索引
2．为经常需要排序、分组和联合操作的字段建立索引
3．为常作为查询条件的字段建立索引
4．限制索引的数目
5．尽量使用数据量少的索引
6．尽量使用前缀来索引
7．删除不再使用或者很少使用的索引

2、为什么MySQL索引使用B+树而不用B-树？什么是B树、B-树、B+树、红黑树、二叉树等？
答：树结构？