MySQL
文章平均质量分 83
MySQL
A minor
本来无一物,何处惹尘埃
展开
-
MYSQL自增主键丢失问题
(1)如果mysql重启了,那么innodb表在启动后,AUTO_INCREMENT值会自动检测出、并重置为当前表中自增列的最大值+1。(2)假如一个表里AUTO_INCREMENT计数器的值是10,此时执行update table set id = 15 where id = 9后,如果这时再继续插入数据,到了自增ID=15的时候是会报错。但是这个时候继续插入,就不会报错。因为刚才即使报错了,AUTO_INCREMENT的值依旧会增加。转载 2023-02-17 20:34:36 · 930 阅读 · 0 评论 -
【MySQL】基础使用(一):支持的数据类型
建表原则:使用可以正确存储数据的最小数据类型。为每一列选择合适的字段类型:1.数值类型1.1 整型整数类型占用字节范围TINYINT1有符号:[-128,127] 或无符号:[0,255]SMALLINT2有符号:[-32768,32767]或无符号:[0,65535]MEDIUMINT3有符号:[-8333608,8388607]或无符号:[0,1677215]INT、INTEGER4有符号:[-21亿多,21亿多]或无符号:[0,42亿多.原创 2021-02-24 00:07:11 · 180 阅读 · 0 评论 -
【MySQL】基础使用(二):常用 SQL 语句大全
1.库操作登录数据库mysql -u root -p // 之后输入密码1.1 增create database 库名1.2 删drop database 库名1.3 查show databses1.4 用use 库名2.表操作2.1 增create table 表名( 列名 类型 约束 )egCREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT,原创 2020-10-26 22:02:15 · 1291 阅读 · 0 评论 -
【MySQL】基础使用(三):视图操作及其作用
https://blog.csdn.net/buhuikanjian/article/details/53105416视图(子查询):是从一个或多个表导出的虚拟的表,其内容由查询定义。具有普通表的结构,但是不实现数据存储。对视图的修改:单表视图一般用于查询和修改,会改变基本表的数据,多表视图一般用于查询,不会改变基本表的数据。–创建视图–create or replace view v_student as select * from student;–从视图中检索数据–select * fr原创 2021-06-24 23:40:47 · 1313 阅读 · 0 评论 -
【MySQL】基础使用(四):用户及权限操作
查看用户、权限、角色的命令和视图查看所有用户:select * from dba_users;select * from all_users;select * from user_users;2.查看用户系统权限:select * from dba_sys_privs;select * from session_privs;select * from user_sys_privs;3.查看用户对象权限:select * from dba_tab_privs;select * fro原创 2021-06-24 23:42:14 · 386 阅读 · 0 评论 -
【MySQL】left join、right join、inner join 区别对比
MySQL 的发展历史和版本分支:时间里程碑1996 年MySQL1.0 发布。它的历史可以追溯到 1979 年,作者 Monty 用 BASIC 设计的一个报表工具。1996 年 10 月3.11.1 发布。MySQL 没有 2.x 版本。2000 年ISAM 升级成 MyISAM 引擎。MySQL 开源。2003 年MySQL4.0 发布,集成 InnoDB 存储引擎2005 年MySQL5.0 版本发布,提供了视图、存储过程等功能。2008转载 2021-02-24 00:08:11 · 227 阅读 · 1 评论 -
【MySQL】SQL 查询中 in 和 exists 的区别分析
原文链接:https://www.jianshu.com/p/f212527d76ff来看两条 SQL 语句:select * from A where id in (select id from B);select * from A where exists (select 1 from B where A.id=B.id);对于以上两种情况,in 是在内存里遍历比较,而 exists 需要查询数据库,所以当 B 表数据量较大时,exists 效率优于in。1.IN()语句内部工作原理.转载 2021-04-28 18:44:33 · 300 阅读 · 0 评论 -
【MySQL】基础使用(五):几条运行监控命令
1.查看运行线程:show processlistshow processlist;这是很重要的一个命令,用于显示用户运行线程。可以根据id号kill线程。也可以查表,效果一样:select * from information_schema.processlist;列含义Id线程的唯一标志,可以根据它 kill 线程User启动这个线程的用户,普通用户只能看到自己的线程Host哪个 IP 端口发起的连接db操作的数据库Command线程的原创 2021-02-24 00:52:34 · 256 阅读 · 1 评论 -
【MySQL】运行原理(一):整体架构
原文链接:https://segmentfault.com/a/1190000017369618哈,好久没更新文章了,今天来说说关于mySQL那些年的小事。说到mySQL啊,用了挺久的了,但是有个问题一直在困扰着我,就是left join、join、right join和inner join等等各种join的区别。网上搜,最常见的就是一张图解图,如下:真的是一张图道清所有join的区别啊,可惜我还是看不懂,可能人比较懒,然后基本一个left join给我就是够用的了,所以就没怎么去仔细研究了,但是.原创 2021-02-23 11:41:06 · 301 阅读 · 1 评论 -
【MySQL】运行原理(二):查询 SQL 的执行过程分析
MySQL的发展历史和版本分支:时间里程碑1996 年MySQL1.0 发布。它的历史可以追溯到 1979 年,作者 Monty 用 BASIC 设计的一个报表工具。1996 年 10 月3.11.1 发布。MySQL 没有 2.x 版本。2000 年ISAM 升级成 MyISAM 引擎。MySQL 开源。2003 年MySQL4.0 发布,集成 InnoDB 存储引擎2005 年MySQL5.0 版本发布,提供了视图、存储过程等功能。2008原创 2020-10-28 01:50:38 · 2049 阅读 · 1 评论 -
【MySQL】运行原理(三):增删改 SQL 执行过程分析
前一篇讲完了查询流程,我们是不是再讲讲更新流程、插入流程和删除流程?在数据库里面,我们说的update操作其实包括了更新、插入和删除。如果大家有看过MyBatis的源码,应该知道Executor里面也只有doQuery()和doUpdate()的方法,没有doDelete()和doInsert()。更新流程和查询流程有什么不同呢?基本流程也是一致的,也就是说,它也要经过解析器、优化器的处理,最后交给执行器。区别就在于拿到符合条件的数据之后的操作。缓冲池 Buffer Pool首先,InnnoDB的数据原创 2020-10-28 01:51:05 · 2263 阅读 · 2 评论 -
【MySQL】存储引擎(一):存储引擎介绍
首先来想一个问题,在关系型数据库里面,数据是放在什么结构里面的?答:放在表Table里面的,我们可以把这个表理解成Excel电子表格的形式。所以,我们的表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。在MySQL里面,支持多种存储引擎,他们是可以替换的,所以叫做插件式的存储引擎。为什么要搞这么多存储引擎呢?一种还不够用吗?这个问题先留着。1.查看存储引擎比如我们数据库里面已经存在的表,我们怎么查看它们的存储引擎呢?show t原创 2021-02-23 13:28:25 · 537 阅读 · 0 评论 -
【MySQL】存储引擎(二):InnoDB 内存结构
Innodb 的内存结构主要分为 3 个部分: Buffer Pool、Change Buffer、Adaptive HashIndex,另外还有一个(redo)log buffer。我们可到再官网看到 InnoDB 的内存结构和磁盘结构。1.Buffer Pool(缓存池)首先,InnnoDB 的数据都是放在磁盘上的,InnoDB 操作数据有一个最小的逻辑单位,叫做页(索引页和数据页)。我们对于数据的操作,不是每次都直接操作磁盘,因为磁盘的速度太慢了。 InnoDB使用了一种缓冲池的技术,也就是把磁原创 2021-02-23 13:28:52 · 517 阅读 · 0 评论 -
【MySQL】存储引擎(三):InnoDB 磁盘结构
MySQL的存储结构分为5级:表空间、段、簇、页、行。1.表空间 TableSpace上篇【MySQL】从InnoDB的内存结构、磁盘结构到update sql执行过程分析 在磁盘结构部分就说过了,表空间可以看做是InnoDB 存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。分为:系统表空间、独占表空间、通用表空间、临时表空间、Undo表空间。2.段 Segment表空间是由各个段组成的,常见的段有数据段、索引段、回滚段等,段是一个逻辑的概念。一个ibd文件(独立表空间文件)里面会由很多个段原创 2020-10-28 22:10:59 · 2390 阅读 · 1 评论 -
【MySQL】存储引擎(四):InnoDB 磁盘结构(表空间)
磁盘结构里面主要是各种各样的表空间,叫做Table space。表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层,所有的数据都存放在表空间中。InnoDB的表空间分为5大类。1.system tablespace(系统表空间)在默认情况下 InnoDB 存储引擎有一个共享表空间(对应文件/var/lib/mysql/ibdata1),也叫系统表空间。InnoDB 系统表空间包含InnoDB数据字典和双写缓冲区,ChangeBuffer和UndoLogs),如果没有指定file-per-tab原创 2021-02-23 13:29:13 · 571 阅读 · 0 评论 -
【MySQL】存储引擎(五):重做日志(redo log),回滚日志(undo log),二进制日志(binlog)
MySQL中有六种日志文件,分别是:重做日志(redo log)、回滚日志(undo log)、二进制日志(binlog)、错误日志(errorlog)、慢查询日志(slow query log)、一般查询日志(general log),中继日志(relay log)。其中重做日志和回滚日志与事务操作息息相关,二进制日志也与事务操作有一定的关系,这三种日志,对理解MySQL中的事务操作有着重要的意义。这里简单总结一下这三者具有一定相关性的日志。1.重做日志(redo log)1.1 作用确保事务的持原创 2020-10-30 14:58:11 · 1207 阅读 · 1 评论 -
【MySQL】索引原理(一):索引的存储结构是什么?数组?链表?树?
索引是什么?维基百科对数据库索引的定义:数据库索引,是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询、更新数据库表中数据怎么理解这个定义呢?首先数据是以文件的形式存放在磁盘上面的,每一行数据都有它的磁盘地址。如果没有索引的话,要从500万行数据里面检索一条数据,只能依次遍历这张表的全部数据,直到找到这条数据。但是有了索引之后,只需要在索引里面去检索这条数据就行了,因为它是一种特殊的专门用来快速检索的数据结构,我们找到数据存放的磁盘地址以后,就可以拿到数据了。就像我们从一本50原创 2020-10-28 22:52:25 · 4106 阅读 · 3 评论 -
【MySQL】索引原理(二):B+Tree索引的实现,MyISAM 和 InnoDB
在查询sql的执行过程及MySQL架构分析我们说过了,MySQL是一个支持插件式存储引擎的数据库。在MySQL里面,每个表在创建的时候都可以指定它所使用的存储引擎。这里我们主要关注一下最常用的两个存储引擎,MyISAM和InnoDB的索引的实现。首先,MySQL的数据都是文件的形式存放在磁盘中的,我们可以找到这个数据目录的地址。在MySQL中有这么一个参数,我们来看一下:show VARIABLES LIKE 'datadir';每张InnoDB 的表有两个文件(.frm和.ibd),MyISAM原创 2020-10-29 00:34:10 · 1723 阅读 · 0 评论 -
【MySQL】索引原理(三):联合索引(最左前缀原则),覆盖索引,索引条件下推
我们容易有以一个误区,就是在经常使用的查询条件上都建立索引,索引越多越好,那到底是不是这样呢?在讲正文之前,我们先来看一个重要的属性列的离散度,公式如下:count(distinct(column_name)) : count(*) --列的全部不同值和所有数据行的比例数据行数相同的情况下,分子越大,列的离散度就越高。简单来说,如果列的重复值越多,离散度就越低,重复值越少,离散度就越高。了解了离散度的概念之后,我们再来思考一个问题。如下图(注:这是一张已经建好的表user_innodb,后面的演示原创 2020-10-29 17:11:37 · 12700 阅读 · 11 评论 -
【MySQL】索引原理(四):创建索引的几点建议,列的离散度,前缀索引
我们容易有以一个误区,就是在经常使用的查询条件上都建立索引,索引越多越好,那到底是不是这样呢?因为索引对于改善查询性能的作用是巨大的,所以我们的目标是尽量使用索引。但这并不意味着索引越多越好,因为索引会占用内存,还需要维护,并且索引还会影响增删改速度。所以我们创建索引时有什么能参考的属性,或者要遵守的原则呢?1.列的离散度我们先来看一个重要的属性列的离散度,公式如下:count(distinct(column_name)) : count(*) -- 列的全部不同值个数:所有数据行行数数据行数原创 2020-11-04 15:29:36 · 3847 阅读 · 3 评论 -
【MySQL】事务与锁(一):详解数据库事务及并发时可能出现的问题
在项目里面,什么地方会开启事务,或者配置了事务?无论是在方法上加注解,还是配置切面。<tx:adviceid="txAdvice"transaction-manager="transactionManager"> <tx:attributes> <tx:methodname="save*" rollback-for="Throwable"/> <tx:methodname="add*" rollback-for="Thr原创 2020-10-31 22:25:34 · 3310 阅读 · 0 评论 -
【MySQL】事务与锁(二):事务并发控制方案(LBCC + MVCC)
前言:【MySQL】事务与锁(二):事务并发控制方案(MVCC)【MySQL】事务与锁(三):事务并发控制方案(LBCC)既然要保证前后两次读取数据一致,那么我读取数据的时候,锁定我要操作的数据,不允许其他的事务修改就行了。这种方案我们叫做基于锁的并发控制 Lock Based Concurrency Control(LBCC)。如果仅仅是基于锁来实现事务隔离,一个事务读取的时候不允许其他时候修改,那就意味着不支持并发的读写操作,而我们的大多数应用都是读多写少的,这样会极大地影响操作数据的效.原创 2021-03-10 22:28:59 · 427 阅读 · 0 评论 -
【MySQL】事务与锁(三):事务并发控制方案(MVCC)
大家想一下,如果要解决读一致性的问题,保证一个事务中前后两次读取数据结果一致,实现事务隔离,应该怎么做?或者说如何实现并发控制?我们有哪一些方法呢?总体上来说,我们有两大类的方案。1.方案一:LBCC第一种,我既然要保证前后两次读取数据一致,那么我读取数据的时候,锁定我要操作的数据,不允许其他的事务修改就行了。这种方案我们叫做基于锁的并发控制Lock Based Concurrency Control(LBCC)。如果仅仅是基于锁来实现事务隔离,一个事务读取的时候不允许其他时候修改,那就意味着不支持并原创 2020-11-01 23:03:05 · 2520 阅读 · 4 评论 -
【MySQL】事务与锁(四):事务并发控制方案(LBCC)
官网 把锁分成了8 类。我们一般把前面的两个行级别的锁(Shared and ExclusiveLocks),和两个表级别的锁(Intention Locks)称为锁的基本模式。后面三个RecordLocks、GapLocks、Next-KeyLocks,我们把它们叫做锁的算法,也就是分别在什么情况下锁定什么范围。1.锁的粒度InnoDB 里面既有行级别的锁,又有表级别的锁,我们先来分析一下这两种锁定粒度的一些差异。表锁,顾名思义,是锁住一张表;行锁就是锁住表里面的一行数据。锁定粒度,表锁肯定是大于行原创 2020-11-01 03:43:35 · 1356 阅读 · 1 评论 -
【MySQL】事务与锁(五):行锁到底锁住的是什么?记录?字段?索引?
首先我们有三张表t1,t2,t3,它们都是只有两个字段, int类型的id和varchar类型的name;区别是t1没有索引,t2有主键索引,t3有唯一索引。再强调一次,在实验前必须提前关闭自动提交,set autocommit=off。然后show variables like 'autocommit'查看自动提交是否是off。我们先假设InnoDB的锁锁住了是一行数据或者一条记录。1.假设锁住记录1.1 实验一:没有索引的表(t1)这个实验操作是操作没有索引的t1,t1里面有4条数据:1原创 2020-11-01 21:52:48 · 5841 阅读 · 8 评论 -
【MySQL】事务与锁(六):死锁问题分析
在我们使用锁的时候,有一个问题是需要注意和避免的,我们知道,排它锁有互斥的特性。一个事务或者说一个线程持有锁的时候,会阻止其他的线程获取锁,这个时候会造成阻塞等待,如果循环等待,会有可能造成死锁。这个问题我们需要从几个方面来分析,一个是锁为什么不释放,第二个是被阻塞了怎么办,第三个死锁是怎么发生的,怎么避免。1.锁的释放与阻塞锁什么时候释放?事务结束(commit,rollback)或客户端连接断开。如果一个事务一直未释放锁,其他事务会被阻塞多久?会不会永远等待下去?如果是,在并发访问比较高的情况下原创 2021-02-24 00:07:44 · 827 阅读 · 0 评论 -
【MySQL】性能调优(一):配置。连接配置优化
我们说到性能调优,大部分时候想要实现的目标是让我们的查询更快。一个查询的动作又是由很多个环节组成的,每个环节都会消耗时间,我们在【MySQL】查询sql的执行过程及MySQL架构分析分析过了。我们要减少查询所消耗的时间,就要从每一个环节入手。1.配置优化第一个环节是客户端连接到服务端,连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error1040: Too many connections 的错误。我们可以从两个方面来解决连接原创 2020-11-03 21:58:03 · 2288 阅读 · 2 评论 -
【MySQL】性能调优(二):表结构。建表及存储引擎几点建议
1.存储引擎的选择为不同的业务表选择不同的存储引擎,例如:查询插入操作多的业务表,用MyISAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。2.MySQL支持的数据类型原则:使用可以正确存储数据的最小数据类型。为每一列选择合适的字段类型:2.1 数值类型1.整型整数类型占用字节范围TINYINT1有符号:[-128,127] 或无符号:[0,255]SMALLINT2有符号:[-32768,32767]或无符号:[0,6553原创 2020-11-04 15:16:54 · 2480 阅读 · 0 评论 -
【MySQL】性能调优(三):SQL。慢查询日志及SQL优化建议
优化器就是对我们的SQL语句进行分析,生成执行计划。问题:在我们做项目的时候,如何找到那些耗时比较长的查询语句呢?我们的服务层每天执行了这么多SQL语句,它怎么知道哪些SQL语句比较慢呢?第一步,我们要把SQL执行情况记录下来 ⇒ 慢查询日志 slow query log .1.开启慢日志因为开启慢查询日志是有代价的(跟bin log、optimizer-trace一样),所以它默认是关闭的:show variables like 'slow_query%';set GLOBAL slow_que原创 2020-11-03 23:26:05 · 1814 阅读 · 1 评论 -
【MySQL】性能调优(四):SQL。EXPLAIN 执行计划详解
使用EXPLAIN关键字可以模拟优化器执行SQL语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈,另外explain extend + show warnings:可以查看mysql优化后的sql语句explain partitions:相比 explain 多了个 partitions 字段,如果查询是基于分区表的话,会显示查询将访问的分区。在看执行计划前,我们先创建三张表。一张课程表,一张老师表,一张老师联系方式表(三张表没有任何索引):-- 课程表D原创 2020-11-04 14:44:14 · 1318 阅读 · 0 评论 -
【MySQL】性能调优(五):架构。集群及分库分表
2.架构优化2.1 引入缓存在应用系统的并发数非常大的情况下,如果没有缓存,会造成两个问题:一方面是会给数据库带来很大的压力。另一方面,从应用的层面来说,操作数据的速度也会受到影响。我们可以用第三方的缓存服务来解决这个问题,例如Redis。运行独立的缓存服务,属于架构层面的优化。为了减少单台数据库服务器的读写压力,在架构层面我们还可以做其他哪些优化措施?2.2 主从复制如果单台数据库服务满足不了访问需求,那我们可以做数据库的集群方案。集群的话必然会面临一个问题,就是不同的节点之间数据一致性的问题原创 2021-02-24 00:45:48 · 459 阅读 · 1 评论 -
【MySQL】性能调优(六):业务层。减轻数据库压力
除了对于 SQL语句、表定义、架构、配置优化之外,业务层面的优化也不能忽视。举几个例子:在某一年的双十一,为什么会做一个充值到余额宝和余额有奖金的活动(充300送50)?因为使用余额或者余额宝付款是记录本地或者内部数据库,而使用银行卡付款,需要调用接口,操作内部数据库肯定更快。在去年的双十一,为什么在凌晨禁止查询今天之外的账单?这是一种降级措施,用来保证当前最核心的业务。最近几年的双十一,为什么提前一个多星期就已经有双十一当天的价格了?预售分流。在应用层面同样有很多其他的方案来优化,达到尽量减轻原创 2021-02-24 00:45:33 · 436 阅读 · 0 评论