MYSQL数据库相关问题整理

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。 即开始事务后执行的所有SQL都是一个整体，一个单元，要么都执行，要么都不执行。
在mysql中使用最多的存储引擎是innodb，myisam ,memory。其中innodb支持事务，而 myisam、memory等不支持事务。

2、事务的属性(ACID)：

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么 都发生，要么都不发生。
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另外一个一致性状态 。
3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个 事务内部的操作及使用的数据对并发的其他事务是隔离的，并发 执行的各个事务之间不能互相干扰。
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

2.1、事务的隔离级别

注:Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ。Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READCOMMITED

2.2、不同的隔离级别会产生的问题

脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.

不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了.

幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行.

2.3 MVCC 是如何解决脏读
MVCC 英文全称是 Muitiversion Concurrency Control，多版本并发控制技术，原理是通过数据行的多个版本管理实现数据库的并发控制，通过保存数据的历史版本，可以通过比较版本号决定数据是否显示，读取数据的时候不需要加锁保证事务的隔离效果。

MVCC 解决了一致性读问题，当我们读取某个数据库在时间点的快照时，只能看到时间点之前提交更新的结果，不能看到时间点之后事务提交的更新结果。这样就避免了脏读问题。

3、索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。
优势：

1. 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

1. 索引会占据磁盘空间
2. 索引虽然会提高查询效率，但是会降低更新表的效率。

3.1 索引的数据结构

Hash表
Hash表，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

平衡二叉树
平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。
然而依然存在一些问题：

1. 时间复杂度和树高相关。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差。（1百万的数据量，log2n约等于20次磁盘IO，时间20*10=0.2s）
2. 平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树
假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4*2=16）。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

B树是一种多叉平衡查找树，主要特点：

1. B树的节点中存储着多个元素，每个内节点有多个分叉。
2. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
3. 父节点当中的元素不会出现在子节点中。
4. 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

缺点:

1. B树不支持范围查询的快速查找，如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
2. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树
在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。

• B树：非叶子节点和叶子节点都会存储数据。
• B+树：只有叶子节点才会存储数据（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址），非叶子节点只存储键值(索引字段值)。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

3.1 索引使用场景
创建索引:
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该建立索引
3.查询中排序的字段,若通过索引去访问将大大提高排序速度
4.查询中统计或者分组字段
不要创建索引:
1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表字段
3.2 索引失效场景
1.最佳左前缀法则(查询从索引的最左前列开始并且不跳过索引中间的列)
2.在索引列上不要做任何操作(计算,函数,类型转换)
3.使用不等于(!= 或者<>)的时候无法使用索引 (in > < 情况下可能会走索引)
4.is null, is not null 无法使用索引
5.like以通配符开头(‘%abc’)索引失效会变成全表扫描
6.字符串不加单引号索引失效
7.少用or,用它来连接会索引失效
8.复合索引中有一列含有NULL值,那么这一列对复合索引就是失效的

实例:
select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10 如何优化?

优 化 为 ： select * from (select * from admin where admin_id>10) T1 left join log on T1.admin_id =log.admin_id。
使用 JOIN 时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小如果有条件应该放到左边先处理， right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个 query（多个连表查询效率低，容易到之后锁表和阻塞）。

4.索引类型: