mysql总结

2021-07-01 19:09:58

文章目录

-----------------------------------事务、锁部分----------------------------------------

1.事务

  • 经典例子：转账
  • A----B，A-100，B+100，必须同时成功
  • 特性：
  • A：原子性，事务是一组操作，要不全部成功，要不全部失败
  • C：一致性，事务执行前后要保持一致性，是指前后状态都是一致性，而不能有中间状态
  • I：隔离性，多个并发的事务之间互不影响，或者说一个事务对其他事务不可见
  • D：持久性，redo log实现，保证了数据库崩溃也不会影响其结果，因为在每次启动时，会先进行redo log日志的恢复：主要是数据页修改的位置、目前日志写入的位置，来进行恢复，读取到引擎层的缓冲池中，等待时机进行刷到数据库中。

2.并发产生的问题

  • 脏读：读到了其他事务未提交的数据，之后其他事务并且进行了回滚
  • 不可重复读：一个事务读取到了数据，其他事务进行了修改，之前按的事务再次读取，发现值变了。
  • 幻读：主要区别在于读取的是一共多少行这种（读取的一个范围的内容），一般需要是MVCC+Next-Key LOCK实现。

3.四种隔离级别

  • 未提交读：以上问题都有
  • 已提交读：解决脏读
  • 可重复读：解决了脏读、不可重复读。一般可用MVCC实现。（Innodb默认的）
  • 可串行化：完美的，但是一帮并发度就很低了。

4.mysql锁

  • 1.行锁、表锁
  • 相比：innodb支持两种，而mysiam只支持表锁；表锁的*粒度大，并发度低，但是维护来说消耗低；行锁的*粒度小，并发读大，但是维护复杂；并且行锁会产生死锁，而表锁不会.
  • 2.读锁、写锁
  • 读锁可以与其他读锁一起使用，而写锁只能自己，也叫排他锁；如看房子例子，所有人可以一起看，而如果一个人买了，其他人就不能去看了。
  • 3.意向锁
  • 是不存在的，是表级锁，当你要想获取读锁或者写锁的时候，必须获得先获得意向锁。
  • 用法：减少锁的扫描，当你想往表上加锁的时候，你之前需要扫描一行行是否有行锁；当有了意向锁，你只需要看这个表是否有意向锁，没有直接加，有，就不能加锁

5.表结构设计

  • 1.尽量设定主键
  • 主键会自动生成聚簇索引，在查询以及修改的时候可以加快查询的速度
  • 2.主键推荐使用自增id
  • 因为在添加数据的时候，会涉及到节点的拆分，所以如果是uuid的话，那么会涉及到比较多的分裂，而自增id顺序插入
  • 3.字段不要为null，定义为not null
  • 因为定义为null，其也是一种数据结构，会有较多的消耗字节，并且在统计的时候，可能会出现一些问题，有个字段为null，导致计数不对。
  • 4.char与varchar
  • 前者是固定长度，不足的时候补空格，而后者是变长的，是你真正存储了多少，而占用多少；一般对于身份证、MD5加密后字段我们使用char，相对比较稳定，查找快
  • 5.int(10)
  • 表示显示的宽度，对于int‘(1) int(10)存储大小是一样的，但是显示来看，长度不一样，并且不足的时候补零。

6.innodb和Myisam的区别？

  • 前者支持行级锁+表级锁，后面支支持表级锁
  • 前者支持外键，后面不支持
  • 前者支持MVCC，后面不支持
  • 前者支持事务，后面不支持
  • 前者支持全文索引，后面不支持
  • 全文索引：基于相似度的查询，比like快

-----------------------------------表优化部分----------------------------------------

1.表的处理？

  • 1.横向分表
    当表的行数很多，成千上万那种，我们可以分成多个表（根据主键），然后按照最后的尾号进行分表，然后在进行不同表的查询。
  • 2.纵向分表
    主要涉及到某些字段数据量比较大，而我们不常用的时候，如：字段id-标题-摘要-内容，对于展示，我们只需要字段id-标题-摘要，而内容数据量太大，我们可以单独拿出来 字段id-内容，然后点击详情的时候，再查找

2.超大分页如何处理？

  • 1.数据库层面
  • limit 100000,10
  • select * from table where age > 20 limit 1000000,10
  • select * from table where id in (select id from table where age > 20 limit 1000000,10)
  • 使用索引覆盖来使得数据量减少
  • 2.缓存方面
  • 提前将内容读取到缓存中
  • 3.需求角度
  • 一般不做类似的需求（我做不了）

3.慢查询-Sql语句很慢？

  • 一般我们可以查看慢查询日志进行查看哪条语句执行的慢
  • 分析：
  • 1.explain，看看是否走了索引
  • 2.数据量是否太大，尝试横向分表、纵向分表
  • 3.load额外的数据，优化sql语句，索引覆盖等等

4.三范式

  • 1.单列不可分
  • 如：地址拆分为“xx省，xx市，xx县”
  • 2.不存在部份依赖，不能依赖于主键的一部分
  • 如：某一行的数据只能和一列相关，但是像订房间：订单编号、房间编号、联系人，当一个人顶了多个房间，那么就会出现联系人这个数据的冗余，所以需要把其单独拿出来
  • 3.不存在传递依赖
  • 如：学号、姓名、学院、学院电话；明显学号----学院-------学院电话，有传递依赖

5.主从复制，双写一致性？

这个见redis，原理差不多。
https://blog.csdn.net/qq_37534947/article/details/120411463?spm=1001.2014.3001.5501

# 6.视图和表的关系? - 视图是一个或者多个基本表（视图）导出的表，而视图是一张虚表，不存储真正的数据 - 优点：1.简化用户的操作；2.让用户可以从多个角度看待同一数据；3.提供一定的数据逻辑独立性 - 缺点：1.修改限制，对于视图的修改，需要涉及到对原数据表的修改，当然对于简单的操作，这是很方便的，但是当涉及到的视图比较复杂，则整体可以修改比较复杂；2.对于一些查询来说，要把视图的查询转换成对应基本表的查询，如果视图是由一个复杂的多表查询定义，则在转换中需要一定的时间。
  • 创建视图：create view XXX as XXXXXXXXXXXXXX;
    mysql总结

7.防止Sql注入的方法？

7.1为什么参数化SQL查询可以防止SQL注入??

  • 一条语句执行，会在mysql的服务层进行进行连接–分析器—优化器，然后执行引擎调用api（引擎层）：
    select count(1) from students where name='张三'
  • name 参数为张三‘ or '1=1 ，这个参数也会被编译器一同编译？不会的
     select count(1) from students where name='张三' or '1=1'
    传参的过程将分开了，此时会先将语句分为：
    第一步：
     select count(1) from students where name=
    第二步：
    传参，此时不会在编译，而是直接拼接（作为参数），这样然后执行，引擎层识别错误。

7.2进行普通用户和管理员的一个权限划分

8.MONyog监控工具

  • 登陆页面
    mysql总结

  • 监控页面
    mysql总结

  • 性能监控仪表
    mysql总结
    Mysql指标：1.Connections，2.Cache Misses，3.Statments，4.Database Throughputs（吞吐量）。

    系统资源指标：1.CPU Usage，2…DISK IO。

  • 线程页面
    mysql总结
    显示当前由MySQL执行的线程数，发送到MySQL的每个查询都在线程中执行。

  • 查询分析器信息展示
    mysql总结
    查询慢日志

参考链接：
https://www.nowcoder.com/discuss/389444?channel=-1&source_id=discuss_terminal_discuss_history_nctrack&ncTraceId=56d60df585ea479caa086fb1c6e658f2.609.16313638423932825
https://www.cnblogs.com/panda-sweets/p/10064368.html

------------------------------------日志部分---------------------------------------------

前提知识：
客户端连接MySQL的过程：
mysql其实是分为服务层和引擎层的：
1.服务层主要包含：客户端进行连接器的连接、然后下一步进行 查询 缓存，但是缓存有一个弊端，就是涉及到update语句缓存就会失效，（这里类似于redis的缓存旁路失效），所以较高的版本，就把mysql缓存去掉了，然后进行分析器（包括语法分析和词法分析）、之后是优化器，以及最后的执行器。
mysql总结

2：引擎层：这里和磁盘进行交互，首先包含一个 行记录缓冲池 ，然后主要是一些引擎的api接口，我们可以调用进行写入和查询；

1.一条更新语句的执行过程

总览：
mysql总结
主要讲一下执行器到引擎层以及和磁盘交互的过程：
mysql总结
1：当缓冲池有数据的时候，我们会进行加锁，访问修改
2：当缓冲池没有数据的时候：

  1. 我们首先要在磁盘文件进行磁盘数据的一个加载，加载到引擎层的缓冲池中。

  2. 然后将其写入uodo log日志中，用于回滚。

  3. 然后进行事务的更新过程，利用执行器进行更行缓冲池的数据，（这时候我们可以称其为脏数据），因为不同于数据库中的数
    如果此时宕机，不用操作，因为没有刷到磁盘中，因为目前都没有提交事务，所以此时宕机都会消失，不会影响磁盘。

  4. redo log日志的写入，这时候：就是记录下来你对数据做了什么修改，比如对“磁盘中某一页的id=10这行记录修改了name字段的值为xxx”，这 就是一个日志。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。

  5. redo log写入缓冲区，在你更新的的时候，有三种策略：
    5.1）0，事务提交的时候，每s进行写入os buffer，然后立即刷新到磁盘。----所以mysql宕机，会有1s数据的丢失
    5.2）1，事务提交的时候，直接写入os buffer中，然后刷新到磁盘。----对于每次提交的事务，都会写到了磁盘，不会有数据的丢失。(推荐，虽然有性能随时，但是保证数据的安全)
    5.3）2，事务提交的时候，直接写入os buffer中，然后，每s刷到磁盘。----mysql宕机数据不会丢失，但是如果整个物理机器宕机，也会有1s的数据丢失。
    mysql总结
    现在来思考

上一篇：数据库日志——binlog、redo log、undo log扫盲


下一篇：提问式复习：图文回顾 redo log 相关知识