本文主要是介绍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;
![在这里插入图片描述](/upload/202109/23/202109231943389226.png)
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监控工具
-
登陆页面
![在这里插入图片描述](/upload/202109/23/202109231943391125.png)
-
监控页面
![在这里插入图片描述](/upload/202109/23/202109231943396970.png)
-
性能监控仪表
![在这里插入图片描述](/upload/202109/23/202109231943400920.png)
Mysql指标:1.Connections,2.Cache Misses,3.Statments,4.Database Throughputs(吞吐量)。
系统资源指标:1.CPU Usage,2…DISK IO。
-
线程页面
![在这里插入图片描述](/upload/202109/23/202109231943403127.png)
显示当前由MySQL执行的线程数,发送到MySQL的每个查询都在线程中执行。
-
查询分析器信息展示
![在这里插入图片描述](/upload/202109/23/202109231943405334.png)
查询慢日志
参考链接:
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缓存去掉了,然后进行分析器(包括语法分析和词法分析)、之后是优化器,以及最后的执行器。
![在这里插入图片描述](/upload/202109/23/202109231943409426.png)
2:引擎层:这里和磁盘进行交互,首先包含一个 行记录缓冲池 ,然后主要是一些引擎的api接口,我们可以调用进行写入和查询;
1.一条更新语句的执行过程
总览:
![在这里插入图片描述](/upload/202109/23/202109231943411643.png)
主要讲一下执行器到引擎层以及和磁盘交互的过程:
![在这里插入图片描述](/upload/202109/23/202109231943414157.png)
1:当缓冲池有数据的时候,我们会进行加锁,访问修改
2:当缓冲池没有数据的时候:
-
我们首先要在磁盘文件进行磁盘数据的一个加载,加载到引擎层的缓冲池中。
-
然后将其写入uodo log日志中,用于回滚。
-
然后进行事务的更新过程,利用执行器进行更行缓冲池的数据,(这时候我们可以称其为脏数据),因为不同于数据库中的数
如果此时宕机,不用操作,因为没有刷到磁盘中,因为目前都没有提交事务,所以此时宕机都会消失,不会影响磁盘。
-
redo log日志的写入,这时候:就是记录下来你对数据做了什么修改,比如对“磁盘中某一页的id=10这行记录修改了name字段的值为xxx”,这 就是一个日志。mysql 每执行一条 DML 语句,先将记录写入 redo log buffer,后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志,再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。
-
redo log写入缓冲区,在你更新的的时候,有三种策略:
5.1)0,事务提交的时候,每s进行写入os buffer,然后立即刷新到磁盘。----所以mysql宕机,会有1s数据的丢失
5.2)1,事务提交的时候,直接写入os buffer中,然后刷新到磁盘。----对于每次提交的事务,都会写到了磁盘,不会有数据的丢失。(推荐,虽然有性能随时,但是保证数据的安全)
5.3)2,事务提交的时候,直接写入os buffer中,然后,每s刷到磁盘。----mysql宕机数据不会丢失,但是如果整个物理机器宕机,也会有1s的数据丢失。
![在这里插入图片描述](/upload/202109/23/202109231943416360.png)
现在来思考
这篇关于mysql总结的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!