第一章 索引及执行过程

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的体系结构是什么样子的(一条查询语句它到底是怎么执行的)?

  1. 查询缓存-》默认是关闭(不是buffer poll)【buffer poll是内存缓存区】-〉它是表级别的缓存
  2. 语法解析分为两步——》词法解析(空格打散)。语法解析(根据sql语法构建树形结构)-〉生成解析树
  3. 解析树-》sql进行语意分析(预处理器)【预处理器:比如这个解析树生成的sql包含表的别名啊,列名啊】-〉生成新的解析树
  4. 新的解析树-》查询优化器(解析执行路径,他会算出最优的cost,哪种执行计划开销最小,就用哪种)-〉进行简单的优化(比如where 1=1 恒等的这种它就会去掉)-》生成执行计划
  5. 执行计划-〉执行引擎(inno db/myisam)等-》buffer poll-〉返回结果

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

假设我们有一条更新的sql语句
update user_innodb set name=‘’ where name=‘’

  1. 先把where后面的数据查询出来加载进buffer poll
  2. 将name字段进行修改-》加载进buffer poll
  3. buffer poll刷脏成功后记录redo log,并且将这行记录状态设置为prepare
  4. 修改mysql数据,修改好了,可以提交事物,
  5. 写binlog日志
  6. 提交事物
  7. 修改redo log状态为commit

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

二叉树:深度特别多,1000w的数据/2

让索引快速的查询会有一个问题,查询时间不要太多
b树:
- 减少io深度,
- 数据在子节点上
b+树:
- 减少io深度,
- 采取顺序读,放在叶子节点上。(提高范围查询效率,双向指针。)
- 查询更稳定
B+Tree 中的 B 是 Balance,是平衡的意思,它在经典 B Tree 的基础上进行了优化,增加了顺序访问指针。
在B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的 B+Tree,这样就提高了区间访问性能:
如果要查询 key 为从 18 到 49 的所有数据记录,当找到 18 后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,极大提到了区间查询效率(无需返回上层父节点重复遍历查找减少 IO 操作)。
索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上,这样的话,索引查找过程中就要产生磁盘 IO 消耗,相对于内存存取,IO 存取的消耗要高几个数量级,所以索引的结构组织要尽量减少查找过程中磁盘 IO 的存取次数,从而提升索引效率。

综合所述,InnDB 只有采取 B+ 树的数据结构存储索引,才能提供数据库整体的操作性能。

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.3

6.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相当于加了锁,加了锁以后,其他事务就不能对它修改,需要等待当前事务修改完之后才可以修改。