mysql-细节

InnoDB和MyISAM对比
InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型，我们重点来看下两者区别。
事务和外键
InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作
MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作
索引结构
InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。
MyISAM使用非聚集索引（非聚簇索引），索引和记录分开。
锁机制
InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现。
MyISAM支持表级锁，锁定整张表。
并发处理能力
MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发

存储文件
InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；
MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从
MySQL5.0开始默认限制是256TB。

msyql 性能的优化

MySQL有哪些存储引擎，各自的优缺点，应用场景

MySQL数据库的索引原理、与慢SQL优化的5大原则
原理：词典，火车位次表
建索引的几大原则
1.最左前缀匹配原则
非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a =
1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的
索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可
以识别的形式
3.尽量选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越
少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以
上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数
据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该
写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
查询优化神器 – explain命令
关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调
rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上
都是在优化rows。

LIKE 和 REGEXP 操作有什么区别？
LIKE 和 REGEXP 运算符用于表示 ％ 和^。

数据库的三范式？
第一范式：数据库表的每一个字段都是不可分割的。
第二范式：数据库表中的非主属性只依赖于主键。
第三范式：不存在非主属性对关键字的传递函数依赖关系。
https://www.cnblogs.com/-mrl/p/13275176.html

MySQL 中有哪几种锁？
MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

MySQL 数据优化。
1）优化数据类型：
尽量避免使用null；
仅可能使用更小的字段；
2）小心字符集转换：
3）优化子查询：
4）优化 UNION

MySQL 的关键字。
主键：primary key
唯一：unique
全局：fulltext
普通：index
多列： index index_name
页级: 引擎 BDB。
表级: 引擎 MyISAM

存储引擎
1）MyISAM：不支持行级锁，不支持事务，查询效率高
2）InnoDB：InnoDB 表类型可以看作是对 MyISAM 的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。
3）archive：这种类型只支持 select 和 insert 语句，而且不支持索引。

MYSQL 数据表在什么情况下容易损坏？
服务器突然断电导致数据文件损坏。
强制关机，没有先关闭 mysql 服务等。

数据库隔离级别有哪些，各自的含义是什么，MySQL 默认的隔离级别是多少。（事务相关）
Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

MySQL 默认的隔离级别是可重复读。

什么是幻读。
幻读是指在同一个事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，
第二次的 SQL 语句可能会返回之前不存在的行。
事务 A 读取与搜索条件相匹配的若干行，事务 B 以插入或删除行等方式来修改
事务 A 的结果集，然后再提交，就会发生幻读。例如第一个事务对一个表中的
数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事
务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后
就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发
生了幻觉一样。
在默认的事务隔离级别下，即 REPEATABLE READ（可重复读）下，InnoDB 存
储引擎采用 Next-Key Locking 机制来避免幻读。
具体看《MySQL 技术内幕-InnoDB 存储引擎》的 6.4.2 小节。

高并发下，如何做到安全的修改同一行数据。
使用悲观锁。本质是当前只有一个线程执行操作，排斥外部请求的修改。
遇到加锁的状态，就必须等待。结束了唤醒其他线程进行处理。但是，
我们的场景是“高并发”。也就是说，会很多这样的修改请求，每个请求
都需要等待“锁”，某些线程可能永远都没有机会抢到这个“锁”，这种请求
就会死在那里。
FIFO（先进先出）缓存队列思路，直接将请求放入队列中，这样就不会
导致某些请求永远获取不到锁。有点强行把多线程变成单线程的感觉。
使用乐观锁。相对于“悲观锁”采用更为宽松的加锁机制，大都是采用带
版本号（Version）更新。实现就是，这个数据所有请求都有资格去修改，
但会获得一个该数据的版本号，只有版本号符合的才能更新成功，其他
的返回抢购失败。

数据库会死锁吗，举一个死锁的例子，mysql 是怎么解决死锁的。
数据库会出现死锁。死锁是指两个或两个以上的事务在执行过程中，因争夺锁
资源而造成的一种相互等待的现象。
举个例子，一个用户 A 访问表 A（锁住了表 A），然后又访问表 B；另一个用
户 B 访问表 B（锁住了表 B），然后企图访问表 A；这时用户 A 由于用户 B 已
经锁住表 B，它必须等待用户 B 释放表 B 才能继续，同样用户 B 要等用户 A 释
放表 A 才能继续，这就死锁就产生了。
解决死锁最简单的方式是不要有等待，将任何的等待都转换为回滚，并且事务
重新开启。但这可能导致并发性能的下降，甚至任何一个事务都不能进行。这
个方法不适用。
另一个简单方法是超时，即当两个事务互相等待时，当一个等待时间超过设置
的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。
除了超时机制外，当前数据库还都普遍采用 wait-for graph（等待图）的方式来
进行死锁检测，这是一种更为主动的死锁检测方式，InnoDB 存储引擎中也采用
这种方式。

某一个表有近千万的数据，CRUD 比较慢，如何优化。
可以做表拆分，减少单表字段数量，优化表结构。
在保证主键有效的情况下，检查主键索引的字段顺序，使得查询语句中条件的字段顺序和主键索引的字段顺序保持一致。
建立合理的索引。
可以结合 Redis、Memcache 等缓存服务，把复杂的 SQL 进行拆分，充分利用二级缓存，减少数据库 IO 操作。
参考链接： 某个表有近千万数据，CRUD 比较慢，如何优化？

如何写 SQL 能够有效地使用到复合索引。
复合索引也叫组合索引，用户可以在多个列上建立索引,这种索引叫做复合索引
(组合索引)。复合索引在数据库操作期间所需的开销更小,可以代替多个单一索
引。
创建复合索引：CREATE INDEX columnId ON table1(col1,col2,col3) ;
使用复合索引：select * from table1 where col1= A and col2= B and col3 = C
对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高。
复合索引可以用到多个 where 条件查询下，比如查询年龄是 12 和性别是男的
所有学生。这样避免了多一次的排序操作。
参考链接： 如何写 sql 能够有效的使用到复合索引
另外，联合索引具有最左匹配原则，即最左优先。比如，我们建立了一个 2
列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2），
解释如下。B+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从
左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，
b+树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较
age 和 sex，最后得到检索的数据；但当(20,F)这样的没有 name 的数据来的时
候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候 name 就是第
一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如
当(张三,F)这样的数据来检索时，b+树可以用 name 来指定搜索方向，但下一个
字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是
F 的数据了（这种情况下无法使用联合索引）。
联合索引的意义在于：
一个顶三个。建了一个(a,b,c)的复合索引，那么实际等于建了
(a),(a,b),(a,b,c)三个索引，因为每多一个索引，都会增加写操作的开销和
磁盘空间的开销。
作为覆盖索引。同样的有复合索引（a,b,c），如果有如下的 sql: select
a,b,c from table where a=1 and b = 1。那么 MySQL 可以直接通过遍历
索引取得数据，而无需回表，这减少了很多的随机 io 操作。
索引列越多，通过索引筛选出的数据越少。
参考链接：Mysql 中联合索引的最左匹配原则

MVCC
多版本控制MVCC，也就是Copy on Write的思想。MVCC除了支持读和读并行，还支持读和写、写和读
的并行，但为了保证一致性，写和写是无法并行的。在事务1开始写操作的时候会copy一个记录的副本，其他事务读操作会读取这个记录副本，因此不会影
响其他事务对此记录的读取，实现写和读并行。
一、MVCC概念
MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的
数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。
多版本控制很巧妙地将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量及读写性能。
如何生成的多版本？每次事务修改操作之前，都会在Undo日志中记录修改之前的数据状态和事务号，
该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。
二、MVCC实现原理
MVCC最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极
大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目
前MVCC只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。
在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。
快照读：读取的是记录的快照版本（有可能是历史版本），不用加锁。（select）
当前读：读取的是记录的最新版本，并且当前读返回的记录，都会加锁，保证其他事务不会再并发
修改这条记录。（select… for update 或lock in share mode，insert/delete/update）
为了让大家更直观地理解 MVCC 的实现原理，举一个记录更新的案例来讲解 MVCC 中多版本的实现。
假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事
务号和回滚指针，如下图所示。

具体的更新过程如下：

假如一条数据是刚 INSERT 的，DB_ROW_ID 为 1，其他两个字段为空。当事务 1 更改该行的数据值
时，会进行如下操作，如下图所示。

用排他锁锁定该行；记录 Redo log；
把该行修改前的值复制到 Undo log，即图中下面的行；
修改当前行的值，填写事务编号，使回滚指针指向 Undo log 中修改前的行。
接下来事务2操作，过程与事务 1 相同，此时 Undo log 中会有两行记录，并且通过回滚指针连在一
起，通过当前记录的回滚指针回溯到该行创建时的初始内容，如下图所示。

MVCC已经实现了读读、读写、写读并发处理，如果想进一步解决写写冲突，可以采用下面两种方案：
乐观锁
悲观锁

什么是回表，覆盖索引有什么作用。
回表是指，数据库根据索引找到了指定的记录所在行后，还需要根据 rowid 再
次到数据块里取数据的操作。在执行计划中，先索引扫描，再通过 rowid 去取
索引中未能提供的数据，即为回表。
避免回表的方法就是将需要的字段放在索引中去，查询的时候避免回表。也就
是覆盖索引。
覆盖索引：一个索引内包含（或覆盖）所有需要查询的字段的值，即只需扫描
索引而无须回表，减少了 IO 操作，提高了效率。

mysql B+树