2023面试准备之--mysql

mysql

clint ---->server ----> 存储引擎

存储引擎

• Innodb
  • 是MySQL5.5版本及之后默认的存储引擎，存储数据更加安全
  • 支持自适应哈希(自己决定是hash还是B+)
  • 一定有聚簇索引，但是其他索引都是非聚簇索引
• myisam
  • 是MySQL5.5版本之前默认的存储引擎
  • 速度比Innode更快
  • 只有非聚簇索引
• memory
  • 内存引擎（数据全部存放在内存中）断电数据丢失
  • 支持的是hash索引
• blackhole
  • 无论存什么，都立刻消失（黑洞）

索引

• 基础知识补充

操作系统
局部性原理：时间：之前被访问过的数据很有可能再次被访问空间：数据和程序都有聚集成群的倾向
磁盘预读：内存跟磁盘在进行交互的时候有一个最小的逻辑单位，这个单位称之为页，或者datapage，一般是4k或者8k,由操作系统系统决定，我们在进行数据的读取的时候，一般会读取页的整数倍，4k、8k、16k， innodb存储引擎在进行数据加载的时候读区的是16kb的数据。
分块读取：可以理解为是磁盘预读

• 分类：
  • 普通索引：允许被索引的数据列包含重复的值。
  • 唯一索引：可以保证数据记录的唯一性。
  • 主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识记录，使用关键字primary key来创建
  • 联合索引：索引可以覆盖多个数据列
  • 全文索引：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一招难过关键技术。
• 优缺点：
  • 索引可以极大的提高数据的查询速度，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能，但是会降低插入、删除、更新表的速度，因为在执行这些写操作的时候，还要操作索引文件，索引需要占用物理空间，除了数据表占用空间外，每一个索引还要占用物理空间，如果要建立聚簇索引，那么需要的空间会更大，如果非聚簇索引很多，一旦聚簇索引发生改变，那么所有的非聚簇索引都要跟着变。
• 索引的设计原则？
  • 应该保证索引字段占用的空间越小越好
  • 适合索引的列是出现在where字段中的列，或者连接字句中指定的列
  • 基数较小的表，索引效果差，没必要创建索引。
  • 选择列的时候，越短越好，可以指定某些列的一部分，没必要用全部字段的值。
  • 不要给每一个字段都创建索引，并不是索引越多越好
  • 定义有外键的数据列一定要创建索引
  • 更新频繁的字段不要有索引
  • 创建索引的列不要太多，可以创建组合索引，但是组合索引的列的个数不建议太多
  • 大文本、大对象不要创建索引
• 用途：提高查询效率，索引和实际的数据都是存储在磁盘上的，只不过在进行数据读取的时候会优先把索引加载到内存中。
• 如何索引太大如何读区到内存中？
  • 分块读取，分而治之
• 性能消耗主要是io问题，如何解决？ 1是减少io量，2是减少io次数。
• 什么格式的数据？
  • k -v ——> 数据结构——> hash/树。–》最终选择的是B+树, 为什么？
  • 为什么不用hash: 需要比较好的hash算法，如果算法不好的话，会导致hash碰撞，hash冲突，导致数据散列不均匀，当需要进行范查找的时候需要挨个遍历，效率比较低。
  • 为什么不用二叉树、BST、AVL、红黑树：当需要向这些树插入更多的数据的时候，会导致当前树变得非常高，加大读取次数（每次查找都是io操作，io操作降低性能），影响查询速度
  • 所以B树是
    
  • B+树：所以一般3到4层的B+树足以支撑千万级别的数据存储。
    
  • 选择索引的时候，选择int还是varchar？key要尽可能少的占用空间。
  • 前缀索引：根据统计唯一值来确定前缀索引的长度。

聚簇索引和非聚簇索引

• 数据跟索引存储在一起的叫聚簇索引，没有存储在一起的叫非聚簇索引。
• innodb存储引擎在进行数据插入的时候，数据必须和某一个索引存储在一起，这个索引列可以是主键，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的rowid来进行存储。此时数据必定是跟某一个索引绑定在一起的，绑定数据的索引叫做聚簇索引。其他索引的叶子节点中存储的数据不再是整行的记录，而是聚簇索引的id值。
• innodb：中既有聚簇索引也有非聚簇索引
• myisam：只有非聚簇索引
• 回表：

id，name, age, gender
id主键， name普通索引
select*from table where name='zhangsan';
先根据nameB+树匹配到对应的叶子节点， 查询到对应行记录的id值，再根据id值去id的B+树中检索整行的记录，这个过程就称之为回表，要尽量避免回表的操作，从非聚簇索引跳转到聚簇索引的过程称之为回表。

• 索引覆盖：

id，name, age, gender
id主键， name普通索引
select id,name from table where name='zhangsan';
当非聚簇索引的的叶子节点中包含了查询需要的字段的时候，不需要回表，这个过程称之为索引覆盖

• 最左匹配：

id，name, age, gender
id主键， name、age普通索引组合索引
select * from table where name='zhangsan' and age=18;#不失效
select * from table where name='zhangsan';#不失效
select * from table where age=18;#失效
select * from table where age=18 and name='zhangsan';#不失效，因为有内部优化器，会优化代码。
最左匹配原则就是组合索引的时候要有顺序，否则索引失效。
**当表中的全部列都是索引列的时候，无论怎么查询都会用到索引**

• 索引下推：

id，name, age, gender
id主键， name、age普通索引组合索引
select * from table where name='zhangsan' and age=18;
在没有索引下推之前，执行的过程是，先根据name从存储引擎中拉去数据，然后根据age在server中过滤
有了索引下推之后，执行的过程是根据name、age整体的从存储引擎中做数据检索，返回对应的记录，不在server层做任何操作。
将检索数据从server层推到了存储引擎层叫索引下推。

事务

• 定义：一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性。mysql默认情况下，自动提交事务

set autocommit = false;
提交：commit;
回滚：rollback;

部分手动：

1、set autocommit = false; 到关闭连接或者到set autocommit = true;之间全部是手动
2、start transaaction; 中间全部是手动 提交：commit;或回滚：rollback;

• 特性ACID：
  • 原子性(Atomicity)：当前事务的操作要么同时成功，要么同时失败，是由我们的undo log日志来保证。
  • 一致性(Consistency)：使用事物的最终目的，由业务代码正确逻辑保证。
  • 隔离性(Isolation)：在事物并发执行时，他们内部的操作不能互相干扰。
  • 持久性(Durability)：
• 数据库的隔离级别:级别越高事务隔离性越好，但性能越低，而隔离机制是由mysql的各种锁以及MVCC机制来实现的
  • read uncommit(读未提交)：有脏读问题
  • read commit(读已提交)：有不可重复读问题
  • repeatable read(可重复读)：有幻读问题
  • serializable(串行)：解决上面全部问题

锁

• 乐观锁：增加版本号id
• 悲观锁：
• 按照属性分类：
  • 共享锁：又称读锁，当一个事务为数据加上锁以后，其他事务只能对数据加读锁，不能写，作用是为了支持并发数据的读取，读数据的时候不支持修改，避免出现重复读的问题。
  • 排他锁：又称写锁，当一个事务加上写锁时候，其他请求不能加任何锁，直到该锁释放，目的是为了在数据修改的时候，不许其他人同时修改，也不许其他人读取，避免出现脏数据和脏读的问题。
• 按照锁的粒度分类：
  • 表级锁：锁住整个表，当下一个事务访问的时候，必须等前一个事务释放了锁才能对表访问，特点：粒度大，加锁简单，容易冲突
  • 行级锁(innodb)：锁住某一行或者多行，其他事务访问这个表的时候可以访问其他行，特点：粒度小，加锁麻烦，不容易冲突，相比表锁支持的并发要高。
  • 记录锁：是行锁的一种，只锁住一行
  • 页级锁：介于行锁和表锁之间
  • 间歇锁：行锁的一种，表中的某一个空间，当表的相邻id之间出现空隙，就会形成一个空间，左开右闭。
  • 临键锁：间隙锁和记录锁一起，就是左闭右闭。

MVCC机制（类似于copy on write）

• 定义：多版本并发控制，就可以做到读写不堵塞，且避免了类似脏读问题。主要通过undo日志链来实现
  

主从复制

• 过程描述
  • master服务器将数据改变记录到二进制binlog里面，当master上的数据发生改变的时候，则将其改变写入二进制的日志中
  • slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变则开始一个I/OThread请求master二进制事件。
  • 同时主节点为每个I/O线程启动一个dump线程，发送二进制事件并保存到本地的relay日志中，从节点将启动SQL线程读取二进制文件，在本地重放，使得其数据与主节点保持一致，最后I/OThread和SQLThread将进入休眠状态，等待下一次被唤醒。
• 过程简单描述：
  • 从库会生成两个线程，I/OThread和SQLThread
  • I/OThread会去请求主库的binlog，并将得到的binlog写到本地的relay-log(中继日志)中
  • 主库会生成一个log dump线程，用来给从库I/O线程传binlog
  • SQL线程会读取relay-log文件中的日志，并解析成sql语句逐一执行。
    

为什么要主从同步？

• 在复杂的业务系统中，如果有一条sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让从库负责读，就可以保证业务的正常运行了。
• 做数据的热备（主备：当业务激增的时候，可以正常访问）
• 架构的扩展，业务量越大，I/O访问频率越高，单机无法满足，此时做多库的存储，降低磁盘I/O的频率，提高单个机器的I/O性能。（当数据库超过千万级别的时候就要做到分库分表）

怎么处理mysql的慢查询？

• 开启慢查询日志，准确定位到哪个sql语句出现了问题
• 分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表