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树：

代码语言：txt复制

- 减少io深度，

代码语言：txt复制

- 数据在子节点上

b 树：

代码语言：txt复制

- 减少io深度，

代码语言：txt复制

- 采取顺序读，放在叶子节点上。（提高范围查询效率，双向指针。）

代码语言：txt复制

- 查询更稳定

8.磁盘的顺序读和随机读有什么区别？【暂未补充】

9.索引的使用原则（索引怎么使用才合理）

索引对改善查询性能的作用是巨大的，我们的目标是尽量使用索引，并且我们创建的索引，尽可能的是在where 判断或者order by字段或者join on的字段上创建索引

索引多的话，磁盘顶不住，索引比数据还大。

单列索引

列的离散度

代码语言：txt复制

- 列的不同的值数量/总行树越接近1 离散度离散度越高，越接近0 离散度越低

代码语言：txt复制

- 离散度越低

代码语言：txt复制

    - 就不要使用索引

代码语言：txt复制

    - 数据量大，查询慢 建议（分表）

联合索引最左原则

代码语言：txt复制

- 字段的出现顺序会影响结果

代码语言：txt复制

- 我们一定要通过有序的情况进行查询比如（name【有可能重复】,phone），如果name字段相同的时候在比较phone，但如果查询条件没有name就不知道下一个节点在哪里了。

如果我们创建了一个联合索引就不需要给联合索引中的某一个字段在进行创建索引

10.什么时候索引失效

在索引的值不确定的情况下都会失效

索引列上使用函数（replace,substr）表示等

字符串不加引号，出现隐士转换

like条件中前面带%

负项查询 not like

11.myisam与innodb

innodb

代码语言：txt复制

- 行锁（支持高并发）

代码语言：txt复制

- 事物安全acid

myisam

代码语言：txt复制

- 表锁（不支持高并发）

代码语言：txt复制

- 支持全文索引

代码语言：txt复制

- 不支持外键

11.主从复制

主库-》一个线程

代码语言：txt复制

- 生成log dump线程

从库-》2个线程

代码语言：txt复制

- io线程读取主库的beinlog，写入到relay log

代码语言：txt复制

- sql线程读取日志，写入到从库

第二章事物性能优化

1.acid

原子性（atomicity，或称不可分割性）

一致性（consistency）

隔离性（isolation，又称独立性）

持久性（durability）

2.开启事物的方式

默认开启

3.数据库的隔离级别有哪几种

事物的隔离级别越高，事物越安全，但是并发能力越差。

读未提交

代码语言：txt复制

- 有 脏读，可重复读，幻读都会 问题

读已提交

代码语言：txt复制

-  有 不可重复读，幻读 问题

可重复读（默认的事物隔离级别）

代码语言：txt复制

-  有 存在幻读的mvcc（表的列使用版本号解决）问题

可串型读

代码语言：txt复制

- 都没问题

代码语言：txt复制

- 解决了脏读，不可重复读，幻读，强制事物串型执行

3.事物并发会带来什么问题/数据库的隔离级别

脏读

代码语言：txt复制

- A (SELECT AGE FROM TABLE)16 -> B (UPDATE  TABLE SET AGE=18[没有提交事物])->A(SELECT AGE FROM TABLE)18

代码语言：txt复制

- 事物中的修改即使没有提交，其他的事物也能看见，事物可以读取到未提交的数据

不可重复读

代码语言：txt复制

- A (SELECT AGE FROM TABLE)16 -> B (UPDATE/del  TABLE SET AGE=18[提交事物])->A(SELECT AGE FROM TABLE)18

代码语言：txt复制

- 同一个事物前后多次读取，不能读到相同的数据内容，中间的事物操作了这个数据

可重复读/幻读

代码语言：txt复制

- 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

代码语言：txt复制

- 当一个事物查询某一个数据的范围，另外的事物又在该范围插入了新的记录，当之前的事物再次读取该范围的记录时，发现两次不一样

4.分库分表

垂直分库分表

代码语言：txt复制

- 根据业务划分

水平分库分表

代码语言：txt复制

- 根据数据量-按照一定的规则进行划分

5.慢sql查询

开启慢sql查询，设置慢sql（默认10s），线上设置300(ms)

代码语言：sql复制

show variables like 'slow_query%';

SET GLOBAL slow_query_log=TRUE;

show variables like '%long_query%'

set long_query_time=0.3

6.sql优化的核心字段

type：连接类型(级别越高越好)

代码语言：txt复制

- 如果是常量级别就是const

代码语言：txt复制

- 只有系统表才是system

代码语言：txt复制

- 关联查询用到索引才是eq_ref

代码语言：txt复制

- 上面三个可遇不可求

代码语言：txt复制

- 最差就是all

代码语言：txt复制

- 一般最差也要在range级别，最好能达到ref

索引

代码语言：txt复制

- possible_keys:可能用到的索引

代码语言：txt复制

- 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、行锁

代码语言：text复制

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。 行级锁按照使用方式分为共享锁和排他锁。

2、表锁

代码语言：text复制

表级锁是mysql锁中粒度最大的一种锁，表示当前的操作对整张表加锁，资源开销比行锁少，不会出现死锁的情况，但是发生锁冲突的概率很大，因为同 一张表上任何时刻只能有一个更新在执行。被大部分的mysql引擎支持，MyISAM和InnoDB都支持表级锁，但是InnoDB默认的是行级锁。

3、乐观锁

代码语言：text复制

乐观锁不是数据库层面上的锁，需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现。通常需要自旋操作。

4、悲观锁

代码语言：text复制

悲观锁是无论什么情况都先加锁，在语句后边加 for update，在select 语句后边加了for update相当于加了锁，加了锁以后，其他事务就不能对它修改，需要等待当前事务修改完之后才可以修改。

全文检索存储数据库 sql 云数据库SQLServer

0 人点赞

mysql总结

第一章 索引及执行过程

1.Bin log是什么，有什么用？（数据库被人干掉了怎么办？）

2.什么是预读取

3.什么是buffer poll （性能优化的一个点）【缓存思想】

4.尽可能的调大mysql数据库的连接数量（性能优化的一个点）

5.mysql的体系结构是什么样子的（一条查询语句它到底是怎么执行的）？

6.mysql的体系结构是什么样子的（一条更新语句它到底是怎么执行的）？

7.为什么mysql要使用b 树作为索引【还得优化答案】

8.磁盘的顺序读和随机读有什么区别？【暂未补充】

9.索引的使用原则（索引怎么使用才合理）

10.什么时候索引失效

11.myisam与innodb

11.主从复制

第二章 事物性能优化

1.acid

2.开启事物的方式

3.数据库的隔离级别有哪几种

3.事物并发会带来什么问题/数据库的隔离级别

4.分库分表

5.慢sql查询

6.sql优化的核心字段

7.InnoDB的事务是如何实现的

第三章锁

1、行锁

2、表锁

3、乐观锁

4、悲观锁

第一章索引及执行过程

第二章事物性能优化