Mysql相关知识

一、Mysql的锁

1、按粒度分

（1）行锁：锁某行数据，锁粒度最小，并发度高
（2）表锁：锁整张表，锁粒度最大，并发度低
（3）间隙锁：锁的是一个区间

2、读写层面分

（1）共享锁：也就是读锁，一个事务给某行数据加了读锁，其它事务可以读，但不能写
（2）排它锁：也就是写锁，一个事务给某行数据加了写锁，其他事务既不能读，也不能写

3、乐观悲观层面

（1）乐观锁：并不会真正的去锁某行记录，而是通过一个版本号来实现
（2）悲观锁：上述的行锁、表锁等都是悲观锁

---

二、Mysql慢查询如何优化

（1）检查是否索引了，没有则优化SQL，利用索引；有则优化索引，看是否是最优索引
（2）检查是否查了不必要字段，查出来多余数据
（3）检查表中数据是否过分，是否该进行分库分表了
（4）检查数据库所在机器性能配置

---

三、Mysql索引

1、索引意义 ———提高查询效率的数据结构

（1）优点：大大提高查询速度
（2）缺点：维护索引耗费数据库资源；占磁盘空间；增删改时，同时要维护索引，速度会受到影响；

2、索引分类及创建

show index from table名 查询表中的索引

（1）InnoDB
  — a.主键索引:设定为主键后数据库自动建立索引，innodb为聚簇索引 主键索引列表不能有空

主键索引建表自带

  — b.普通索引:（单列/值索引），一个表可以有多个普通索引

create index 自定义索引名 on 表名（字段名）        ——建表后
create table 表名（key(字段名)，key(字段名)，…）    ——建表时

  — c.唯一索引:索引的值必须唯一（并不是说唯一索引只能有一个），可且仅可存在一个null

参考普通索引，只是关键字变成unique

  — d.复合索引：即一个索引包含多个列（这个索引的创建由多个列共同决定，如根据某几个条件                  同时查数据）

参考普通索引，参数变成多个

注意：1.最左前缀原则 2.mysql引擎在查询时为了更好的利用索引，查询时会动态调整查询字段顺序
如：创建复合索引顺序：name,age,bir字段
基于 name bir age 查 ----可以
基于 name age bir 查----可以
基于 bir age 查----不可以
基于 bir age name 查----可以
基于 age name 查----可以

（2）MyISAM
  — e.Full Text（全文索引）

3、索引底层

（1）主键索引自动排序

（2）数据底层进化

① 链表

②数据量过大，分页

③ 页目录（页目录不存储数据，而是把主键和指针拎出来）

④进化成B+树结构（最顶层根节点常驻内存，就是说第一次不需磁盘I/O，减少磁盘I/O，提高效率）

【注意】：B树的区别在于，B+树所有叶子节点之间有一个链指针，B树每个节点不仅包含数据的key值，还有data数据，而我们知道，每页存储空间有限，一旦data数据较大，每个节点（即每一页）能存储的key就少了，当存储数据量过大时就会导致B树深度较大，增大查询时的词磁盘I/O次数，进而影响查询效率。

---

【小结mysql索引底层结构描述】：
【答】：mysql索引底层是一个B+树的数据结构。首先，我们对在表中插入数据时，mysql底层会基于数据做一个排序，排序后会以链表的形式将数据链接起来，同时，mysql还做了进一步优化，基于B+树的结构，对数据进行一页页的存储，每页默认存储16KB，一般三层B+树存储量再8-10亿字节左右，我们的项目一般两层足矣，如果我们基于主键查询，最多一次磁盘I/O，因为顶层是常驻内存的。

---

四、Mysql的隔离级别

• READ-UNCOMMITTED(读取未提交)：最低的隔离级别
• READ-COMMITTED(读取已提交)
• REPEATABLE-READ(可重复读) （InnoDB默认支持的隔离级别）
• SERIALIZABLE(可串行化)

！！可重复读的原理

为了解决不可重复读，MySQL采用了MVVC(多版本并发控制)的方式解决该问题，我们在数据表中看到的一行记录可能实际上有很多版本，还要有一个表示版本的字段，记为row_trx_id，而这个字段就是使其产生的事务的id，事务ID记为transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

在这个级别下，普通的查询同样是使用的“快照读”，但是，和“读提交”不同的是，当事务启动时，就不允许进行“修改操作（Update）”了，而“不可重复读”恰恰是因为两次读取之间进行了数据的修改，因此，“可重复读”能够有效的避免“不可重复读”，但却避免不了“幻读”，因为幻读是由于“插入或者删除操作（Insert or Delete）”而产生的。

换句话说，“读取提交”的隔离级别，在读数据时不用排它锁，而是使用MVVC机制获取当前数据的最新快照，这个机制发生在每次select时，意味着我们前后两次读取的同一个数据会被其他事务修改，出现不可重复读。 而“可重复读”这种隔离级别在使用MVVC机制时，一次事务只在第一次select时生成版本，后续的查询都是在这个版本上进行，以此实现可重复读。