mysql总结

2021/9/23 19:43:41

本文主要是介绍mysql总结,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

文章目录

  • -----------------------------------事务、锁部分----------------------------------------
  • 1.事务
  • 2.并发产生的问题
  • 3.四种隔离级别
  • 4.mysql锁
  • 5.表结构设计
  • 6.innodb和Myisam的区别?
  • -----------------------------------表优化部分----------------------------------------
  • 1.表的处理?
  • 2.超大分页如何处理?
  • 3.慢查询-Sql语句很慢?
  • 4.三范式
  • 5.主从复制,双写一致性?
  • 7.防止Sql注入的方法?
    • 7.1为什么参数化SQL查询可以防止SQL注入??
    • 7.2进行普通用户和管理员的一个权限划分
  • 8.MONyog监控工具
  • ------------------------------------日志部分---------------------------------------------
  • 1.一条更新语句的执行过程
  • 2.redo log的记录形式
  • 3.三种日志的比较
  • 4.一条查询语句的执行过程
  • 5.日志最后
  • ------------------------------------索引部分---------------------------------------------
  • 1.hash索引
  • 2.innodb默认不支持哈希索引
  • 3.优点和缺点
  • 4.聚集索引和非聚集索引
  • 5.常见的索引
  • 6.哪些需要创建索引?哪些不需要创建索引?
  • 7.联合索引以及最左匹配原则?
  • 8.如何查看创建的索引有没有被使用到?
  • 9.索引失效原则?
  • 10.索引的优缺点?
  • ------------------------------------补充部分---------------------------------------------
  • 1.主键 超键 候选键 外键
  • 2.drop,delete与truncate的区别
  • 3.内连接、外连接、笛卡尔积


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

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;
    在这里插入图片描述

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指标:1.Connections,2.Cache Misses,3.Statments,4.Database Throughputs(吞吐量)。

    系统资源指标:1.CPU Usage,2…DISK IO。

  • 线程页面
    在这里插入图片描述
    显示当前由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缓存去掉了,然后进行分析器(包括语法分析和词法分析)、之后是优化器,以及最后的执行器。
在这里插入图片描述

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

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

总览:
在这里插入图片描述
主要讲一下执行器到引擎层以及和磁盘交互的过程:
在这里插入图片描述
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总结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程