mysql调优问题清单

myisum索引使用的是非聚集【它的索引文件MYI和数据文件MYD是分离的】

能做到几范式：
explain

spring应用，并发是否实践过，数据库是否优化过，手写单例，快排，算法

mysql索引为何采用B+树

为什么MySQL使用B+而不是使用B树、二叉树、AVL树呢？（来龙去脉的去理解）

最坏情况：全盘扫描。插入不平衡（比如递增的id）
avl树：每次删除一个节点或者增加一个节点的话，需要一次或者多次的左旋，右旋等去维护“平衡”状态【如果节点很多的话，那么这个AVL树的高度还是会很高的，那么查询效率还是会很低，】
b树：B Tree查询的效率不够稳定，键值其实都是分布在整棵树上的节点上的任何一个节点
b+树：【数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。】B+Tree中因为数据都在叶子节点，所以每次查询的时间复杂度是固定的，【B+ Tree有一个很大的改变就是他的每一个非叶子节点的内节点中都没有date这个概念了，都变成了key，因为他的date都放在了叶子节点上，这样的一个最大的好处就利用了局部性原理】，能最大可能的减少磁盘IO的次数，
操作系统和磁盘之间一次数据交换是已页为单位的，一页 = 4K，即每次IO操作系统会将4K数据加载进内存。

二叉树每个节点的结构只保存一个关键字，一个数据区，两个子节点的引用，并不能够填满4K的内容。幸幸苦苦做了一次的IO操作，却只加载了一个关键字，在树的高度很高，恰好又搜索的关键字位于叶子节点或者支节点的时候，取一个关键字要做很多次的IO。因此平衡二叉树也是不太符合MySQL的查询结构的。

数据量大的时候，二叉需要的深度太深了


max.degree=3一个节点的数据不能超过3，比如根节点存了0003和0005后，再有数据过来就要发生旋转了。

先找101（1次），太大了，那就从81开始找，82…83…84…100(总共查了20次找到了)

mysql之索引优化

慢查询：没有加索引，或者查询没有走索引
几百万，上千万，红黑树的深度就比较大，磁盘io次数还是会很多

二叉树纵向很深，那就从横向做文章，做成B树（n叉树）

因为二叉树不管再整样平衡，它每层都只有两个节点，当数据量大时，必然会 树的深度很深。磁盘io次数很多，性能很慢

一次磁盘io，会将一个节点上的数据都读入到内存。节点如果太大，几百上千万数据，那么索引的大小就是500M，甚至1-2G（索引是int型），一次从几百M甚至几个G的内存里面查找一个数据。我常用的就是10%，你一次就遍历所有

B树又可以叫多叉平衡树
data它是磁盘的指针，即数据指针，指向磁盘里面数据的地址，比如一个节点16k【】，那么我如果将data移到叶子节点，那么整个16k都可以用来存key了，那么分叉（更向更宽广）就可以更多，树的高度就可以更小。虽然叶子节点就更大了
非叶子节点做了冗余，并将data数据都移动到了叶子节点，这样可以方便遍历数据，一次io取更多的key

show GLOBAL STATUS LIKE ‘INNODB_page_size’

小节点存储更少的元素，大节点就能存更多的key

主键索引是bigint=8B
下一个磁盘文件的地址是6B
小节点14B，大节点16K，则一个大节点能存16K/14B=1170个索引
假设叶子节点的小节点是1k，大的叶子节点仍是16K，则叶子节点能 16个

h=3能存放2千多万索引，即2千多万行数据，最多3次io就能从2千多万行数据里面找到我们要查找的值。

innodb和myISAM是形容数据表的

myISAM的frm的是表结构的定义
MYD(data)存的是数据行----磁盘文件的指针
MYI(Iindex)是对应索引字段存储的位置
主键自带索引
先是myi定位，再是myd中定位

表结构/表数据/表索引
innodb是frm表结构，ibd（index，data，）

innodb的主键索引就是聚集索引【叶子节点包含了完整的数据记录】

innodb先是找能唯一标识的字段做索引，找不到，它会默认生成rowid做隐藏列来作为索引，需要用主键索引来维护表数据

它要一个个比较大小，找对应元素，如果用uuid作为主键，首先存储容量大，其次字符串比较大小是很慢的uuid还会数字+字母的组合比较。字母比较，首先会转成ASCII码值再比较。故而要用整型的自增【这样插入元素是从后面插入的，不自增就会前插入导致分裂。避免插入到满的叶子节点后导致树的分分裂以及自动平衡】

value存放的是磁盘文件的指针
mysql面试题

普通select查询可以越过锁机制，读到数据
事务隔离级别 使用的是undo log实现

使用超时机制可解决死锁：让一方进行回滚
使用等待图进行死锁检测。

mysql原理

请谈一谈mysql中的锁及应用场景

锁有latch闩锁【互斥量和读写锁—时间是临界资源（用来保证并发线程操作临界资源的正确性/内存的数据结构）】和lock锁【行锁，表锁，意向锁------时间是整个事务过程（保护的是数据库内容的准确性）】

• 行锁
  共享锁（读锁），允许事务读一行数据。
  排他锁（写锁），允许事务删除或更新一行数据。

行锁里面只有读读锁兼容

• 意向锁
  意向共享锁（IS），意向排他锁(IX)
  意向锁的加锁层次结构
  

如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行进行修改之前，会先为表添加意向互斥锁（IX），再为行记录添加互斥锁（X），在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要通过等待意向互斥锁被释放就可以了。

要想使用意向锁完成对记录r的修改。其加锁过程如下：
先对数据库A，表，页，加上意向锁 IX，最后对记录加上X锁。

mysql的innoDB支持的是行级锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

我开始了事务，执行了如下查询：
select * from user lock in share mode;
然后查看trx表：

对一张表锁了两次，故记录数为4，表为2，

mysql事务

mysql数据库，当且仅当引擎是InnoDB，才支持事务；
mysql事务隔离级别的设置语句
mysql中innoBB的事务隔离级别

mysql中innoBB的事务隔离级别文章中的a，b都是开始了事务的，而非像上述，左边窗口开启了事务，而右边窗口却没有开启事务。

事务隔离的实现原理（版本链）

MySQL事务隔离级别的实现原理

事务（id为80），添加了数据1，并提交了。事务81，82，200都开启了事务并对数据1进行了相应的修改，但都未进行事务的提交（此时81，82，200都处于m_ids【他是readview里面的属性，是用来记录那些事务是活跃的】的数组里面），此时如果事务300过来了，它的事务隔离级别是读已提交，它会从上往下开始找寻版本链，发现200，82，81都在m_ids里面，所有都不会从里面读数据，往下走是80，它不在m_ids里面，故读到了80里面的数据1.

MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。

普通的SELECT就是快照读(也就是历史版本)

• 锁定读
  SELECT … LOCK IN SHARE MODE
  给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交（我在读的时候，别人只能读，不能写）
  SELECT … FOR UPDATE
  给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的（我在读的时候，别人不能读，也不能写）

一致非锁定读
Consistent read（一致性非锁定读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的

Record Locks（记录锁）：在索引记录上加锁。
Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。
假设一个索引包含以下几个值：10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
，在修改的时候一定不是快照读，而是当前读。

而且，前面也讲过只有普通的SELECT才是快照读，其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的，同时为了防止幻读的出现还需要加间隙锁。

一致性读保证了可用重复读
间隙锁防止了幻读
回想一下

1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。

调优命令

• 查看执行计划【这个看不了时间】
  

• 查看对应语句执行的时间【统计语句的各个部分执行时所消耗的时间】
  先开启：
  
  开始使用
  
  show profile for query 1;
  
  但该方法已经废除，，用了performance schema来替换
  druid连接池，是为监控而生的，有很多监控信息，

• 查看当前mysql的连接数（监控连接数）
  show processlist;
  

• 查看和设置mysql最大连接数(可查看，可设置)--------默认是151
  show variables like ‘%max_connections%’;
  set global max_connections=1000;