MYSQL知识点详解

一、基础架构

关于查询缓存一般不推荐使用。

当一个sql查询语句中where条件语句存在一个不在表中的字段，会在哪个环节检测出错误。答：分析器。

二、存储引擎

1、myisam和innodb存储引擎

mysql的特点是支持多存储引擎，大概有十几种，常见的为myisam、innodb和memory。
myisam和innodb的索引结构为B+树。
myisam支持非聚族索引，索引文件和数据文件分离，索引存放在keuBuffer中，工作在内存，所以myisam查询速度很快，keyBuffer默认大小512M。
innodb支持事务，支持外键，支持行锁，为聚族索引，索引文件和数据文件存在在一起。
使用场景：
在集群应用中，myisam适合读密集的表，innodb适合写密集的表

2、memory

数据存储在内存当中，默认采取哈希索引，等值查询的情况下，查询速度很快。

三、日志

MySQL中有七种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。下面主要介绍redo log、undo log和bin log。

1、redo log（重做日志）

innodb引擎支持redo log，innodb独有；

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做（简记先写日志，在写磁盘）；

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

2、bin log（二进制日志也称作归档日志）

bin log工作在sever层，是整个数据库都存在的日志，记录了语句的原始逻辑。

因为redo log是innodb独有的，所有bin log必须存在，redo log是物理的，bin log逻辑的

3、保证两个日志文件的一致性

采取二阶段提交的方式。

保证数据库的一致性，必须要保证2份日志一致，使用的2阶段式提交；其实感觉像事务，不是成功就是失败，不能让中间环节出现，也就是一个成功，一个失败

4、undo log(回滚日志)

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。

四、索引

索引是一种能够提高数据库查询效率的数据结构，常见的的索引数据结构有序数组，哈希表、红黑树、B树和B+树等。innodb和myisam采用的是B+树。

1、索引的常见模型

哈希：是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。查找的时间复杂度o(1)~o(n)。适合于等值查询的场景。缺点：不支持基于范围的查找，基于索引的排序，模糊查询l如ike,联合索引。

有序数组：在等值查询和范围查询场景中的性能都非常优秀，只适用于静态存储引擎（数据不变）

二叉树：一般为二叉搜索树，每个节点的左儿子小于父节点，父节点又小于右儿子，查询时间复杂度O(log(N))，更新时间复杂度O(log(N))，.数据库存储大多不适用二叉树，因为树高过高，会适用N叉树

B树：平衡多路查找树，由于是多叉结构，对于元素数量非常多的情况下，树的深度不会像二叉结构那么大，可以保证查询效率。

B+树：B+是是B树的一种变形。

特点：（1）所有叶子结点包含全部关键字信息，及指向含有这些关键字记录的指针，且叶子节点中关键字进行有序链接。

（2）非叶子结点相当于是叶子节点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层

2、常见的索引类型

普通索引：最基本的索引，没有任何限制，可为空，可重复

CREATE INDEX index_name ON table_name (column_name) ;//创建普通索引

唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它 是一种特殊的唯一索引，不允许有空值。 一般是在建表的时候同时创建主键索引

组合索引：也叫联合索引，为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。

举例：建立组合索引（a,b,c）,生效的组合索引的组合为：(a,b,c),(a,b),(a)

create index indexName on  tableName(column1,column2,...,columnN)，如create idx_un_userid_username on user(id,name)

组合索引数据结构：通俗的理解：这个B+树的叶子节点包括一条具有a,b,c和主键的记录 。如果你要select a,b,c from table;那么不用进行回表，直接可以从索引表中获得结果，如果你要select * from table，那么当从索引表中获得主键后，还要再去一趟聚簇索引拿整条记录值。

3、INNODB和MYISAM为何用B+树不用B树

1）B+树的磁盘读写代价更低

B+树的内部结点没有指向关键字具体信息的指针，因此内部结点相对B树更小。如果把所有同一内部结点的关键字放在同一块磁盘中，盘块所能容纳的关键字数量也就越多，一次性读入内存中的需要查找的关键字也就越多，相对IO读写次数降低。

2）B+树的查询效率更加稳定

由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每个数据的查询效率相当

3）B+树只要遍历叶子结点就可以实现整棵树的遍历，支持基于范围的查询。

叶子节点保存了所有关键字的信息

4、建立索引的时机

1）在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。

2）某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引

缺点：

1）索引也作为一种文件保存在磁盘中，占用空间

2）降低了更新表的速度，维护索引文件

3）索引失效会导致全表扫描，索引失效的情况：列值为null、like语句、列上进行运算、not in和<>操作等

5、索引优化

 

 

结尾

持续更新中，不定时