MySql知识点整理

一、mysql组成

二、btree图：

（1）所有键值分布在整个树中
（2）任何关键字出现且只出现在一个节点中
（3）搜索有可能在非叶子节点结束
（4）在关键字全集内做一次查找，性能逼近二分查找算法

三、b+tree图：

（1）所有关键字存储在叶子节点，非叶子节点不存储真正的data
（2）为所有叶子节点增加了一个链指针

四、回表

先索引扫描，拿到主键去取索引中没有的数据

五、覆盖索引

只需要在一颗索引树上获取所需要的所有数据，不需要回表

六、索引下推

存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
如A、B两个字段组合索引，A=9，B=8，5.6之前是先检索索引树得到A=9的然后回表，索引下推就是在索引树直接匹配A=9，B=8

七、索引匹配方式

全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、只访问索引的查询（本质覆盖索引）

八、聚簇索引和非聚簇索引

聚簇索引指索引和数据存储在一起，非聚簇索引典型的MyISAM。innoDB的主键对应的B+tree就是典型的聚簇索引

九、mysql join三种算法（嵌套循环链接）

Simple Nested-Loop join
join字段没有索引，非驱动表全表扫描

Index Mested-Loop join
join字段有索引，非驱动表走的索引查找

Block Nested-Loop join
join字段没有索引，不会先走Simple Nested-Loop join，优先有Block Nested-Loop join将驱动表里面的join字段放到join buffer，然后一次到非驱动表里面全表扫描

十、explain命令

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

id:

从大到小执行，相同值从上到下执行

type:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system、const:
用于primary key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快
eq_ref:
用于primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录
range:
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
index:
只需扫描索引树
ALL:
全表扫描

possibe_keys:

可能使用到的索引

key:

实际使用到的索引

key_len:

使用索引的字节数，推算出索引的使用哪些字段

rows:

mysql估算索引扫描的行数

ACID

原子性、一致性、隔离性、持久性

十一、事务的隔离级别

读未提交:

一个事务还没提交时，他做的变更就能被别的事务看到

读提交:

一个事务提交后，他做的变更才能被别的事务看到

可重复读:

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据一致的，当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的

串行化:

对于同一行记录，读写都会加锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行

十二、binlog、redo log、undo log

binlog是逻辑日志，二进制日志，不管存储引擎都有，可用于主从复制
redo log是物理日志，用来保证事务安全
undo log是逻辑日志，保存事务发生之前数据的一个版本，可用于回滚

十三、一千万条数据的表，如何分页

select * from table where id>:id limit :limit

十四、binlog主从同步原理

1、主库db的更新事件(update、insert、delete)被写到binlog
2、主库创建一个binlog dump thread，把binlog的内容发送到从库
3、从库启动并发起连接，连接到主库
4、从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5、从库启动之后，创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

十五、innodb的四大特性

1、插入缓冲
使用条件:非聚集索引、非唯一索引
聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引
2、二次写
3、自适应哈希索引
4、预读