mysql总结
第一章 索引及执行过程
1.Bin log是什么，有什么用？（数据库被人干掉了怎么办？）
binlog 数据恢复,主从复制
binlog会以事件的形式记录了所有的ddl和dml语句（它记录的是sql，属于逻辑日志），可以用来数据恢复和主从复制
数据恢复：是基于业务数据的
崩溃恢复：恢复内存中，没有同步的数据
2.什么是预读取
inno db(数据页-默认16k)【参数-innerdb-page-size】
当你的磁盘数据到内存（会有一个预读取的概念）
当你操作磁盘数据的时候比如16k，他会把附近的数据也会加载到内存。
3.什么是buffer poll （性能优化的一个点）【缓存思想】
inno db-》内存缓冲区【内存缓冲池buffer poll】，同步的时间太长，就回导致数据的丢失【脏页页/】-〉mysql有一个机智叫【刷脏】
如果内存缓冲区满了呢？它有一个内存淘汰策略（LRU,LFU）,MYSQL用的是lru(链表尾部的数据淘汰掉)，但是是优化后的lru,mysql将数据分为了热区（5/8）和冷区（3/8），那么问题来了，第一次加载进来以后会先加载到冷区的头部，那什么时候放入热数据的区域呢？mysql设定了一个规则，1s中过后再次对此缓存页进行访问才会将缓存页，放入到热数据的头部。为什么是1s呢？因为通过预读机制还有全表扫描加载进来的数据页通常是1s内就加载了很多，然后对他们访问一下，这些操作都会在1s内完成，如果大于1s，就是慢sql，他们会将存放在冷数据区域等待刷盘清空，基本上不太会有机会放入到热数据的区域，除非1s后有人访问。类似于数据预热
也就是，buffer poll 值越大，脏数据就会越多，丢数据的可能性也就越大，意味着读取数据的效率越高。（当我们的读取的数据库服务器尽可能的将buffer poll size值调大。）
如果说这个刷脏没跟上，我们出现了异常情况，比如数据库宕机，数据是不是就完了，不是的，innodb还有一个日志文件 叫redo log(重做日志)，对内存的所有操作都会保存到redo log。redo log记录的是物理日志，具体位置和具体的值。
4.尽可能的调大mysql数据库的连接数量（性能优化的一个点）
mysql中默认有一个参数wait_timeout(28800)8h,默认8h没有活跃就回断开，应该改一下这个（生产改一下比如20分）
mysql默认最大的连接数量（151个），极限的情况下可以设置为10万。
5.mysql的体系结构是什么样子的（一条查询语句它到底是怎么执行的）？
- 查询缓存-》默认是关闭（不是buffer poll）【buffer poll是内存缓存区】-〉它是表级别的缓存
- 语法解析分为两步——》词法解析（空格打散）。语法解析（根据sql语法构建树形结构）-〉生成解析树
- 解析树-》sql进行语意分析（预处理器）【预处理器：比如这个解析树生成的sql包含表的别名啊，列名啊】-〉生成新的解析树
- 新的解析树-》查询优化器（解析执行路径，他会算出最优的cost，哪种执行计划开销最小，就用哪种）-〉进行简单的优化（比如where 1=1 恒等的这种它就会去掉）-》生成执行计划
- 执行计划-〉执行引擎（inno db/myisam）等-》buffer poll-〉返回结果
6.mysql的体系结构是什么样子的（一条更新语句它到底是怎么执行的）？
假设我们有一条更新的sql语句
update user_innodb set name='' where name=''
- 先把where后面的数据查询出来加载进buffer poll
- 将name字段进行修改-》加载进buffer poll
- buffer poll刷脏成功后记录redo log，并且将这行记录状态设置为prepare
- 修改mysql数据，修改好了，可以提交事物，
- 写binlog日志
- 提交事物
- 修改redo log状态为commit
7.为什么mysql要使用b+树作为索引【还得优化答案】
二叉树：深度特别多，1000w的数据/2
让索引快速的查询会有一个问题，查询时间不要太多
b树：
- 减少io深度，- 数据在子节点上b+树：
- 减少io深度，- 采取顺序读，放在叶子节点上。（提高范围查询效率，双向指针。）- 查询更稳定8.磁盘的顺序读和随机读有什么区别？【暂未补充】
9.索引的使用原则（索引怎么使用才合理）
索引对改善查询性能的作用是巨大的，我们的目标是尽量使用索引，并且我们创建的索引，尽可能的是在where 判断或者order by字段或者join on的字段上创建索引
索引多的话，磁盘顶不住，索引比数据还大。
单列索引
- 列的离散度
- 列的不同的值数量/总行树越接近1 离散度离散度越高，越接近0 离散度越低- 离散度越低 - 就不要使用索引 - 数据量大，查询慢 建议（分表）- 联合索引最左原则
- 字段的出现顺序会影响结果- 我们一定要通过有序的情况进行查询比如（name【有可能重复】,phone），如果name字段相同的时候在比较phone，但如果查询条件没有name就不知道下一个节点在哪里了。如果我们创建了一个联合索引 就不需要给联合索引中的某一个字段在进行创建索引
10.什么时候索引失效
- 在索引的值不确定的情况下都会失效
索引列上使用函数（replace,substr）表示等
字符串不加引号，出现隐士转换
like条件中前面带%
负项查询 not like
11.myisam与innodb
- innodb
- 行锁（支持高并发）- 事物安全acid- myisam
- 表锁（不支持高并发）- 支持全文索引- 不支持外键11.主从复制
主库-》一个线程
- 生成log dump线程从库-》2个线程
- io线程读取主库的beinlog，写入到relay log- sql线程读取日志，写入到从库第二章 事物性能优化
1.acid
原子性（atomicity，或称不可分割性）
一致性（consistency）
隔离性（isolation，又称独立性）
持久性（durability）
2.开启事物的方式
- 默认开启
3.数据库的隔离级别有哪几种
事物的隔离级别越高，事物越安全，但是并发能力越差。
- 读未提交
- 有 脏读，可重复读，幻读都会 问题- 读已提交
- 有 不可重复读，幻读 问题- 可重复读（默认的事物隔离级别）
- 有 存在幻读的mvcc（表的列使用版本号解决）问题- 可串型读
- 都没问题- 解决了脏读，不可重复读，幻读，强制事物串型执行3.事物并发会带来什么问题/数据库的隔离级别
- 脏读
- A (SELECT AGE FROM TABLE)16 -> B (UPDATE TABLE SET AGE=18[没有提交事物])->A(SELECT AGE FROM TABLE)18- 事物中的修改即使没有提交，其他的事物也能看见，事物可以读取到未提交的数据- 不可重复读
- A (SELECT AGE FROM TABLE)16 -> B (UPDATE/del TABLE SET AGE=18[提交事物])->A(SELECT AGE FROM TABLE)18- 同一个事物前后多次读取，不能读到相同的数据内容，中间的事物操作了这个数据- 可重复读/幻读
- A (SELECT AGE FROM TABLE where AGE>15)16 -> B (inster into TABLE values('22')[提交事物])->A(SELECT AGE FROM TABLE where AGE>15)16,22- 当一个事物查询某一个数据的范围，另外的事物又在该范围插入了新的记录，当之前的事物再次读取该范围的记录时，发现两次不一样4.分库分表
- 垂直分库分表
- 根据业务划分- 水平分库分表
- 根据数据量-按照一定的规则进行划分5.慢sql查询
开启慢sql查询，设置慢sql（默认10s），线上设置300(ms)
show variables like 'slow\_query%';
SET GLOBAL slow\_query\_log=TRUE;
show variables like '%long\_query%'
set long\_query\_time=0.36.sql优化的核心字段
- type：连接类型(级别越高越好)
- 如果是常量级别就是const- 只有系统表才是system- 关联查询用到索引才是eq\_ref- 上面三个可遇不可求- 最差就是all- 一般最差也要在range级别，最好能达到ref- 索引
- possible\_keys:可能用到的索引- key:实际用到的索引7.InnoDB的事务是如何实现的
事务的实现是通过两种日志来完成的，分别是undo.log（回滚日志），redo.log（重做日志）。
当开始事务后，每执行一条sql语句，都会对应一条相反的sql语句（insert 对应 delete，update 对应相反 update）写入 undo.log（回滚日志中）。
当事务执行完毕后，会写入一个checkpoint（检查点）到 undo.log 日志中，MySQL 在下次执行事务或者重启MySQL，只会检查最近的checkpoint后面的内容，checkpoint 之前的说明已经执行成功不需要回滚了。
如果执行过程中，服务器突然挂掉，也就是说，执行了部分sql，不确定提没提交，那么这个时候，重启MySQL 时，MySQL 会检查 undo.log，如果最近的检查点后面有内容，则说明有部分sql执行了，但是没有提交，这时，MySQL 会对数据进行回滚。
并执行redo.log内的SQL，进行重做。
第三章锁
1、行锁
行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。2、表锁
表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大，因为同 一张表上任何时刻只能有一个更新在执行。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。3、乐观锁
乐观锁不是数据库层面上的锁，需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现。通常需要自旋操作。4、悲观锁
悲观锁是无论什么情况都先加锁，在语句后边加 for update，在select 语句后边加了for update相当于加了锁，加了锁以后，其他事务就不能对它修改，需要等待当前事务修改完之后才可以修改。