Mysql精简
几种图了解Mysql
引擎相关问题：
-------------------------------
怎么理解数据库的锁？ 一般锁分别哪几种？
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，
其他的事务不能对它读取和修改。
加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
深入理解数据库行锁与表锁？
数据库是怎样隔离事务的呢。这时候就牵连到了数据库锁。当插入数据时，就锁定表，这叫做”锁表”；当更新数据时，就锁定行，这叫做”锁行”。
行锁
就是一锁锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引，行锁的特征：锁冲突概率低，并发性高，但是会有死锁的情况出现
表锁
顾名思义，表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，sql语句可以通过执行计划看出扫描了多少条记录。
由于表锁每次都是锁一整张表，所以表锁的锁冲突几率特别高，表锁不会出现死锁的情况。
MySQL中InnoDB引擎的行锁是怎么实现的？
答：InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起
Innodb是如何实现事务的？
即ACID （A原子性C一致性I隔离性D持久性）
是通过Redo log（重做日志）和Undo log 实现的ACD三个特性。 而隔离性是通过锁来实现的。
第一是重做日志用来实现事务的持久性，即D特性。
第二部分是Undo log，它可以实现如下两个功能：
1.实现事务回滚
2.实现MVCC（MVCC是多版本并发控制机制，顾名思义支持MVCC的数据库表中每一行数据都可能存在多个版本，
对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，通过读写数据时读不同的版本来避免加锁阻塞。）
undo log和redo log记录物理日志不一样，它是逻辑日志。
InnoDB存储引擎为什么选择B+树构建索引？
B+树磁盘读写次数更低
因为B+树非叶子结点只是相当于一个索引，会将所有关键字具体信息都存储叶子结点，也就导致B+树能够存储更加多的关键字数量，构造的树更加矮胖，一次性读入内存的关键字数量增加，IO读写次数减少
B+树由于所有数据都在叶子结点，不用跨层，同时链表有序只要找到首尾，就可以定位到所有符合条件的数据。这就是B+树比B树更优的地方。
Hash更快，为什么数据库还用B+树作索引？与业务场景有关，如果只选一个数据，hash确实更快，但是select经常要选择多条，
这时由于B+树索引有序并且又有链表相连，它的查询效率比hash更快。而且数据库的索引是存储在磁盘上的，数据量大的情况下无法一次性装入内存，
B+树的多路设计可以允许索引数据分批加载到内存，树的高度也很低，提高查找效率。（这也可以算是hash索引和B+树索引的大概区别，面试问题）
InNoDB四大特性？
1）插入缓存：正常插入自增ID速度非常快，但是如果使用UUID（防止注入式攻击，当我们使用主键自增的时候，需要删除一个东西的时候，一般都是id=？。这样的话我就可以在url中修改这个id的值，这样可能就被人删除了其他东西，UUID这个就是给主键id加上一层锁，使它不暴露给用户）者中随机插入（非连续）的值，此时需要离散的访问非聚集索引页，不是直接插入索引页，而是判断非索引也是否在缓冲池中，
若在，则直接插入，若没在则放入Insert Buffer中，在Insert Buffer 中合并后插入到索引页，提高了插入性能
InsertBuffer使用条件
1 索引是辅助索引
2 索引不是唯一的
（2）两次写
避免在处理事务时候发生宕机，虽然说事务操作可以日志恢复，但是如果宕机的时候完全删除当前页，如果，doubleWrite，一部分为内存中doublewrite buffer ,大小为2M，另一部分物理磁盘表空间中连续的128个页。缓冲池的脏页刷新的时候，首先会刷新到doublewrite buffer中，之后才会写入到各个表空间文件，遇到宕机，只需要从共享空间doublewrite中找到该页的副本恢复，之后重做日志
（3）自适应hash索引
InNoDB会监控对表上的各索引页查询，如果观察到建立hash索引可以带来速度提升，则建立hash索引，自动根据访问评率和模式来自动为某些热点也建立索引
（4）异步io
AIO可以将多个IO请求进行IO Merge，当一个IO请求发出后，可以立即发出另一个IO请求，当所有IO请求发送完毕，等待IO操作完成
------------------------------------
索引相关问题：
数据库SQL索引什么时候会失效？
1对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引
alter table student add index my_index(name, age) // name左边的列， age 右边的列
select * from student where name = 'aaa' // 会用到索引
select * from student where age = 18 // 不会使用索引
2对于使用 like 查询， 查询如果是 ‘%aaa’ 不会使用索引，而 ‘aaa%’ 会使用到索引。
select * from student where name like 'aaa%' // 会用到索引
select * from student where name like '%aaa' 或者 '_aaa' // 不会使用索引
3如果条件中有 or， 有条件没有使用索引，即使其中有条件带索引也不会使用，换言之， 就是要求使用的所有字段，都必须单独使用时能使用索引。
4如果列类型是字符串，那么一定要在条件中使用引号引用起来，否则不使用索引。
5如果mysql认为全表扫面要比使用索引快，则不使用索引。
如：表里只有一条数据。
怎么使用索引才能提高索引的命中？
1>如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
2>对于多列索引,不是使用的第一部分(第一个),则不会使用索引
3>如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
4>如果mysql估计使用全表扫描要比使用索引快,则不使用索引。
唯一索引与主键索引的区别？
(1) 对于主键/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
(2) 主键不一定只包含一个字段，所以在主键的其中一个字段建唯一索引还是有必要的；
(3) 主键可作外键，唯一索引不可；
(4) 主键不可为空，唯一索引可；
(5) 主键可是多个字段的组合；
(6) 主键与唯一索引不同的是：
a.有not null属性；
b.每个表只能有一个。
(7) 主键索引一定是唯一索引， 唯一索引不是主键索引
(8) 主键可以与外键 构成 参照完整性约束， 防止数据不一致
如何让 like %abc 走索引查询？
我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，
可以使用 REVERSE() 函数来创建一个函数索引，查询脚本
如下：
select * from t where reverse(f) like reverse(’%abc’);
联合索引的作用是什么？
用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、key(a,b,c) 等三个索引，
我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销；
聚簇索引与非聚簇索引的区别？
聚簇索引：节点页只包含了索引列，叶子页包含了行的全部数据。聚簇索引“就是表”，因此可以不需要独立的行存储。将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
聚簇索引类似于电话簿或者字典形式，后者按姓氏排列数据。由于聚簇索引规定数据在表中的物理存 储顺序，因此一个表只能包含一个聚簇索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用， 在汉语字典里，索引项是字母+声调，字典正文也是按照先字母再声调的顺序排列。
聚簇索引和非聚簇索引，在查询数据的时候有区别吗？
A：聚簇索引查询会更快
索引优化？
EXPLAIN +SQL语句查询优化索引。（接上面的如何提高命中）
唯一索引如何变为主键索引，变成了之后本质上有什么不同？
--------------------------------------------------------------------
事务相关问题：
MySQL 是如何保证一致性、原子性和持久性的？
原子性-----利用Innodb的undo log。undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。
持久性-----利用Innodb的redo log。正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
怎么解决这个问题？简单啊，事务提交前直接把数据写入磁盘就行啊。
这么做有什么问题？只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。
于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。
当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
Myql中的事务回滚机制概述？
事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。
要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，
而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚
并发事务有什么什么问题？应该如何解决？
并发事务可能造成：脏读、不可重复读和幻读等问题 ，这些问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，解决方案如下：
加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
提供数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁，
通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取，
从用户的角度来看，好象是数据库可以提供同一数据的多个版本。
InnoDB 如何开启手动提交事务？
InnoDB 默认是自动提交事务的，每一次 SQL 操作（非 select 操作）都会自动提交一个事务，
如果要手动开启事务需要设置 set autocommit=0 禁止自动提交事务，相当于开启手动提交事务。
什么是自动提交？
MySQL默认采用自动提交AUTOCOMMIT模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。
对于MyISAM或者内存表这些事务型的表，修改AUTOCOMMIT不会有任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，
也可以说是相当于一直处于AUTOCOMMIT启用的模式。
如何手动操作事务？或者（事务控制语法知道吗？）
使用 begin 开启事务；rollback 回滚事务；commit 提交事务。具体使用示例如下：
begin;
insert person(uname,age) values('laowang',18);
rollback;
commit;
如何实现事务的隔离？
每行数据其实在数据库都是多个版本的，可能同一时间有很多事务在更新一条数据，事务在开始的时候会申请一个id即（一个版本号），
这个id是严格随着时间递增的，先开始的事务id总是小的，数据库的版本就是事务id的版本。
所以：
1.读未提交：每次读的都是最新版本，这样速度是最快的，使用中的业务场景基本上没有
2.读已提交: 如果当前数据版本的号（最新事务对这条数据的操作）比事务的id大，就会根据版本的id查看事务是否提交了，如果提交了，就会承认这条数据，如果查到这个事务还没有提交，就会查看上个版本，直到找到已提交的版本，获取那个版本的数据，那有没有读到的版本是已提交的，上个版本还没提交呢，当然是不会的，更新的时候会加上一个行锁，上个事务如果没有提交，这个事务是不可能提交的
3.可重复读: 可重读读在事务启动的时候获取一个数组，记录未提交的事务，可重复读取数据的时候多了一个验证，如果事务提交了但是数据的版本号（操作这个数据事务的id）比当前事务高，说明这个事务是在当前事务启动后启动并且提交的，这条数据是不会被承认的，如果当数据的版本号比当前事务id低的话，说明操作是在当前事务开启之前就开启了，这条数据是被当前事务承认的。
可以发现InoDB引擎是通过MVCC解决了幻读的问题。
4.串行化:用加锁的方式来避免并行访问
------------------------------------------------
操作相关问题：
数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER
主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。
数据查询语言DQL（Data Query Language）SELECT
这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。
数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。
数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK
SQL 约束有哪几种？
NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。
where后面不能直接跟分组函数即聚集函数。
分组函数有五种count、sum、avg、max、min。
聚集函数从多个输入行中计算出一个结果。 比如，我们有在一个行集合上计算 count（数目）， sum（和），avg（均值）， max（最大值）和min（最小值）的函数。
为什么他们不能跟在where后面？
这几个函数在使用时一般都和group by联合使用，原理是先分组在进行函数计算。
当然我们在平常使用时，有这种情况。
select max(age) from t_student
这个sql语句中并没有group by，但还是可以使用分组函数max。这是因为当sql语句种没有手动使用group by时，那整个调用的表默认是一个分组。max函数是在这个大分组中取的最大值。所以我们就可以知道，应该是先分组，再调用分组函数，这样的逻辑。
sql语句的执行顺序为
from子句
where 子句
group by 子句
having 子句
order by 子句
select 子句
不过，我们通常都可以用其它方法实现我们的目的；这里我们就可以使用子查询：又比如
从学生表t_student中筛选出3年级年龄最大的女孩儿们，结果按照学号id升序
select max(age) from t_student where grade = 3 group by sex having sex = girl order by studentID asc
当我们遇到这种情况时，就要改变思路，用子查询即可，where后面跟一个子查询就迎刃而解了。
为什么尽量选择单调递增数值类型的主键？
InnoDB中数据记录本身被存于主索引（B+树）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的结点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页。
如果使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引结点的后续位置，当一页写满，就会自动开辟一个新的页，这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新纪录都要被插入到现有索引页的中间某个位置，此时MySQL不得不为了将新记录查到合适位置而移动元素，甚至目标页可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，
这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。
简单的说：
索引树只能定位到某一页，每一页内的插入还是需要通过比较、移动插入的。所以有序主键可以提升插入效率。
MySQL 内连接、外连接、左连接、右连接、全连接？
一张图搞定；
内连接：两个表的交集（阴影）部分。
关键字：inner join on
语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;
左连接（左外连接）：左表(a_table)的记录将会全部表示出来（左边加中间都有，右边看情况），而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL
关键字：left join on / left outer join on
语句：SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;
右连接 right join 左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来即（右部分加中间都有，左边看情况）
全连接 union，MySQL目前不支持此种方式，可以用其他方式替代解决。
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毫秒）。
drop、truncate和delete的区别？
delete
1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。
二、truncate
1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。
4、truncatetable不能用于参与了索引视图的表。
三、drop
1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。
DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
TRUNCATE 只能对TABLE； DELETE可以是table和view
总结：
1、在速度上，一般来说，drop> truncate > delete。
2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
如果想删除表，当然用drop；
如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
如果和事务有关，或者想触发trigger，还是用delete；
如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
隔离级别介绍
当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non reapeatable read）、幻读（phantom read）的问题，
为了解决这些问题，就有了“隔离级别”的概念。
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，
这时，另外一个事务也访问这个数据，然后使用了这个数据。
所以就对应那张图，如果隔离级别是读未提交就没了。正好可以脏读，而读已提交正好解决脏读。
不可重复读
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。
那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，第二个事务已经提交。那么第一个事务两次读到的的数据可能是不一样的。
这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
而刚好就是读已提交隔离级别产生的问题，所以只有可重复读隔离级别（mysql默认）才能解决
幻读 :
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。
同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。
那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样
InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题
标准的隔离级别有：读未提交（read uncommitted）、读已提交（read commited）、可重复读（repeatable read）串行化。
其中隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找一个平衡点。
1.读写锁
最简单直接的的事务隔离实现方式，每次读操作需要获取一个共享锁，每次写操作需要获取一个写锁。共享锁之间不会产生互斥，共享锁和写锁之间、以及写锁与写锁之间会产生互斥。当产生锁竞争时，需要等待其中一个操作释放锁后，另一个操作才能获取到锁。
2. MVCC
在读写锁中，读和写的排斥作用大大降低了事务的并发效率，于是人们又提出了能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照
的方式将数据保存下来，这样读锁就和写锁不冲突了。不同的事务session会看到自己特定版本的数据，即使其他的事务更新了数据，
但是对本事务仍然不可见，本事务看到的数据始终是第一次查询到的数据。在数据库中，这个快照的处理方式叫多版本并发控制（Multi-Version Concurrency Control）。这种方式真正实现了非阻塞读，只有在写操作时才需要加行级锁，因此并发效率更高。
