MySQL基础

韧性是一种美德

MySQL是一个关系型数据库,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一

MySQL是开源的免费项目，Oracle数据库产品是闭源同时也是收费的。Mysql是中小型数据库，Oracle是大型的，Oracle的解决方案比较成熟

MySQL的基础围绕4点说起
1、存储引擎
2、索引与数据结构
3、log
4、优化器

存储引擎

InnoDB
MySQL5.5之后的默认存储引擎

应用场景：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用。

优点：提供了具有提交（Commit）、回滚（Rollback）、崩溃恢复能力的事务安全，支持外键。

缺点：相比较于MyISAM，写的处理效率差一点，并且会占用更多的磁盘空间来存储数据和索引

MyISAM
MySQL5.5之前的默认存储引擎

优势：访问速度快

适用场景：对事务的完整性没有要求，或以select、insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛。

区别：

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。跨平台应用更方便（表保存为文件形式）

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）
   那么为什么InnoDB没有了这个变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的

5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁，InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

8、InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有

如何选择Innodb或者myisam：
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

innodb引擎的4大特性
插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

为什么MyISAM会比Innodb的查询速度快？

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多:

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快
(phil 注: myisam 更新频率低,所以 索引变更少 . 所以允许每次更新 即更新主索引,也更新付索引,更新 offset)

3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
MVCC (Multi-Version Concurrency Control)多版本并发控制
(phil 注: 由于没有了多行,不需要判断 选取可见的那行数据)
myisam 表锁.牺牲了写性能,提高了读性能.

索引与数据结构

为什么在磁盘中要使用B+树来进行文件存储呢？

因为B+树的高度低得缘故，磁盘本身是一个顺序读写快，随机读写慢的系统，那么如果想高效的从磁盘中找到数据，势必需要满足一个最重要的条件：减少寻道次数。以平衡二叉树树为例进行对比，就会发现问题所在了，平衡二叉树太高了

Mysql中 B+树通常是3层或4层，并且在内存中是连续的。
一个高度为 3 的 B+ 树大概可以存放 1170 × 1170 × 16 = 21902400 行数据，已经是千万级别的数据量了

平衡二叉树这种结构的好处是，没有空间浪费，不会存在空余的空间，但坏处是需要取出多个节点，且无法预测下一个节点的位置。这种取出的操作，在内存内进行的时候，速度很快，但如果到磁盘，那么就意味着大量随机寻道。基本磁盘就被查死了

b+树，因为其构建过程中引入了有序数组，从而有效的降低了树的高度，一次取出一个连续的数组，这个操作在磁盘上比取出与数组相同数量的离散数据，要便宜的多。因此磁盘上基本都是b树结构。

不过，b+树结构也不是完美的，与二叉树相比，他会耗费更多的空间。在最恶劣的情况下，要有几乎是元数据两倍的格子才能装得下整个数据集（当树的所有节点都进行了分裂后）。

B+树比B树的优势

一个千万量级别，且存储引擎是MyISAM或者InnoDB的表，其索引树的高度在3-5之间。

B+树的索引树的高度就是logNB（logN/logB）

N表示记录数，B表示每个节点有B个索引。

假设有3000W条数据，每个节点保存64个索引。
那么索引的高度就是（log2^25）/log64=25/6=4

is null 是否会走索引？
有可能走，有可能不走。
优化器评估，全表扫描与走索引（索引会回表）的成本哪个执行成本低。按成本低的方式执行

问题排查方式
General_log 开启可以记录所有执行的LOG，可以用于统计
slow query log 可以查看慢日志
showprocesslist 有权限可以看到所有正在执行的线程SQL，以及执行多长时间
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 可以查看正在锁的事务
kill 命令可以杀死指定线程，比如这个线程死锁了

ACID中
redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性。

mysql整理结构