MySQL
Thxxxxxx
然鹅我不想简介自己
展开
-
mysql语法中的LIMIT的用法
select * from table limit m,n其中m是指记录开始的index,从0开始,表示第一条记录,n是指从第m+1条开始,取n条。Example:select * from tablename limit 2,4;即取出第3条至第6条,4条记录。...原创 2019-04-20 19:58:15 · 552 阅读 · 0 评论 -
可重复读:为什么你改了我看不到呢?
在探索问题之前,先得明白如下知识点InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。 而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且 把 transaction id 赋值给这个数据版本的事务 ID,记为 row trx_id。还没懂的话...转载 2019-05-07 22:03:13 · 433 阅读 · 0 评论 -
RC隔离级别下的GAP间隙锁
delete from t1 where id = 10; RC RR id主键 结论:id是主键时,此SQL只需要在id=10这条记录上加X锁即可。 id唯一索引 此组合中,id是unique索引,而主键是name列。此时,加锁的情况由于组合一有所...转载 2019-05-11 10:34:12 · 3085 阅读 · 1 评论 -
快照读和当前读
快照读:简单的select操作,属于快照读,不加锁。(当然,也有例外) select * from table where ?; 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。 select * from table where ? lock in share mode; select * from table where...原创 2019-05-11 14:36:39 · 1462 阅读 · 0 评论 -
使用组合索引你得注意了
如果不是按照索引的最左列开始查找,则无法使用索引。 不能跳过索引中的列。 如果查询中有个列范围查询(例如使用like),则其右边的所有列都无法使用索引优化查找。...原创 2019-05-25 20:03:47 · 572 阅读 · 0 评论 -
索引的优点
减少扫描行数 随机IO变为顺序IO 避免临时表和排序原创 2019-05-25 21:33:21 · 106 阅读 · 0 评论 -
什么原因可能导致主备延迟?
主备延迟最直接的表现是,备库消费中转日志(relay log)的速度,比主库生产 binlog 的速度要慢。备库所在机器的性能要比主库所在的机器性能差。这种部署现在比较少了。因为主备可能发生切换,备库随时可能变成主库,所以主备库选 用相同规格的机器,并且做对称部署,是现在比较常见的情况。 备库的压力大。一般的想法是,主库既然提供了写能力,那么备 库可以提供一些读能力。或者一些运营后台需要的分...转载 2019-05-13 10:28:43 · 583 阅读 · 0 评论 -
如何避免选错索引
首先说下,索引选择的标准有:扫面行数,是否创建临时表,是否排序等。来看下面的例子。选择了B列上索引,所以扫描了5万多行。采用 force index 强行选择一个索引弊端:一来这么写不优美,二来如果索引改了名字,这个语 句也得改,显得很麻烦。而且如果以后迁移到别的数据库的话,这个语法还可能会不兼容。修改语句,引导 MySQL 使用我们期望的索引选择索引b的原因是因为语句...原创 2019-05-27 21:10:13 · 176 阅读 · 0 评论 -
为什么delete表,还会占磁盘空间?
使用delete命令删除整张表时,你得注意了所有的数据页都会被标为可复用页,在磁盘上,文件的大小并不会改变。原创 2019-05-30 20:59:42 · 365 阅读 · 0 评论 -
Online DDL
1. 建立一个临时文件,扫描表 A 主键的所有数据页;2. 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;3. 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中,对应的是 图中 state2 的状态;4. 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同 的数据文件,对应的就是图中 state3 ...转载 2019-05-30 21:13:42 · 265 阅读 · 0 评论 -
count(id)count(1)count(*)count(字段)
对于 count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一 个数字“1”进去,判断是不可能为空的,按行累加。 单看这两个用法的差别的话,你能对比出来,cou...转载 2019-05-30 21:42:34 · 2265 阅读 · 2 评论 -
InnoDB和MyISAM有哪些不同
MyISAM使用前缀压缩技术使得索引更小,但InnoDB则暗战原数据格式进行存储MyISAM索引通过数据的物理地址位置引用被索引的行,而InnoDB则根据主键引用被索引的行MyISAM支持空间索引,可以用作地理数据数据。MylSAM不支持事务,最小锁范围为表锁,InnoDB支持事务,并且支持行锁MylSAM对于count(*) 的值有固定存储地址,InnoDB对于count(*)需...原创 2019-06-22 09:44:45 · 174 阅读 · 0 评论 -
Mysql可重复读实现原理分析
InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的,是按申请顺序严格递增的。一个事务只需要在启动的时候,找到所有已经提交的事务 ID 的最大值,记为 up_limit_id;而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id 赋值给这个...原创 2019-07-12 10:03:55 · 3465 阅读 · 0 评论 -
Mysql主备延迟的原因
1.备库所在的机器性能比主库所在的机器相差很多解决办法:换机器2.备库压力大解决办法:一主多从3.大事务,新增的的binlog文件过大,在备库中执行时间过长解决办法:避免大事务,一次性删除大量数据就是大事务...原创 2019-07-21 14:06:19 · 717 阅读 · 0 评论 -
为什么B+树比B树更适合做数据库索引
(1)B+树的磁盘读写的代价更低B+树内部结构没有指向关键字具体信息的指针,这样内部结点相对B树更小(2)B+树的查询更加稳定因为非终端结点并不是最终指向文件内容的结点,仅仅是作为叶子结点中关键字的索引。这样所有的关键字的查找都会走一条从根到叶子结点的路径。所有的关键字查询长度是相同的,查询效率高...转载 2019-07-25 18:26:29 · 737 阅读 · 0 评论 -
Inndb和Memory
1. InnoDB 表的数据总是有序存放的,而内存表的数据就是按照写入顺序存放的;2. 当数据文件有空洞的时候,InnoDB 表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而内存表找到空位就可以插入新值;3. 数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而内存表需要修改所有索引;4. InnoDB 表用主键索引查询时需要走一次索引查找,用普通索引查...转载 2019-07-21 21:44:45 · 160 阅读 · 0 评论 -
数据库设计范式
常用的范式有:第一范式,第二范式,第三范式,来说说 我对他们的理解第一范式:保证每一列数据的原子性例子:存在一个列名为学校的属性,但其保存了 例如为 (北京,清华大学)(上海,复旦大学),,此时不满足第一范式的要求,应拆分为学校地址与学校。第二范式:在第一范式的基础上,每个非主键列属性要完全依赖于主键属性。例子:直接在网上找的例子,实在想不出什么好例子。。。 第三...转载 2019-08-26 10:26:15 · 153 阅读 · 0 评论 -
行级锁
InnoDB行锁是通过给索引上的索引项加锁来实现的。所以,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。其他注意事项:在不通过索引条件查询的时候,InnoDB使用的是表锁,而不是行锁。 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以即使是访问不同行的记录,如果使用了相同的索引键,也是会出现锁冲突的。 当表有多个索引的时候,不同的事务可以使...转载 2019-05-07 21:13:42 · 1026 阅读 · 0 评论 -
为什么不要使用长事务
比如,在某个时刻(今天上午9:00)开启了一个事务A(对于可重复读隔离级别,此时一个视图read-view A也创建了),这是一个很长的事务……事务A在今天上午9:20的时候,查询了一个记录R1的一个字段f1的值为1……今天上午9:25的时候,一个事务B(随之而来的read-view B)也被开启了,它更新了R1.f1的值为2(同时也创建了一个由2到1的回滚日志),这是一个短事务,事务随后就被...原创 2019-05-07 20:49:35 · 1298 阅读 · 0 评论 -
普通索引和唯一索引,傻傻分不清楚?
两种索引对查询语句和更新语句的性能影响来进行分析查询过程假设,执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过 程,先是通过 B+ 树从树根开始,按层搜索到叶子节点,也就是图中右下角的这个数据页,然后 可以认为数据页内部通过二分法来定位记录。 对于普通索引来说,查找到满足条件的第一个记录 (5,500) 后,需要查找下一个记录,直到 碰...转载 2019-05-10 10:04:31 · 208 阅读 · 0 评论 -
sql超低级用法(防遗忘)
通配符 描述 % 替代一个或多个字符 _ 仅替代一个字符 [charlist] 字符列中的任何单一字符 [^charlist] 或者 [!charlist] 不在字符列中的任何单一字符 SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFRO...原创 2019-04-21 11:12:24 · 118 阅读 · 0 评论 -
高级sql语句(更新中)
+----+--------+| Id | Salary |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 |+----+--------+ +------------------------+| getNthHighestSalary(2) |+------------------------+| 200 ...原创 2019-04-21 08:19:51 · 783 阅读 · 0 评论 -
Mysql剖析单条查询三种方法
三种方法: SHOW PROFILE SHOW STATUE 检查慢查询日志 SHOW PROFILE能帮助我们定位到哪些活动花费了最多的时间,但并不会告诉我们为什么会这样。SHOW STATUE是一个有用的工具,但并不是一款剖析工具,其大部分结果都只是一个计数器,可以显示某些活动如读索引的频繁程度,但无法给出消耗了多少时间。。尽管无法提供基于时间的统计,但对于在...转载 2019-05-03 20:39:53 · 530 阅读 · 0 评论 -
存储引擎放弃使用索引的方式
where 子句中对字段进行 null 值判断 在 where 子句中使用 != 在 where 子句中使用 or 来连接条件 在 where 子句中使用in 和 not in 在 where 子句中使用 like ‘%123%’ 在 where 子句中对字段进行表达式操作 在where子句中对字段进行函数操作 在 wh...原创 2019-05-04 09:41:12 · 311 阅读 · 0 评论 -
一条SQL语句执行得很慢的原因有哪些?
一、开始装逼:分类讨论一条SQL语句执行的很慢,那是每次执行都很慢呢?还是大多数情况下是正常的,偶尔出现很慢呢?所以我觉得,我们还得分以下两种情况来讨论。1、大多数情况是正常的,只是偶尔会出现很慢的情况。2、在数据量不变的情况下,这条SQL语句一直以来都执行的很慢。针对这两种情况,我们来分析下可能是哪些原因导致的。二、针对偶尔很慢的情况一条SQL大多数情况正常,偶尔...转载 2019-04-30 09:18:34 · 3039 阅读 · 0 评论 -
一条SQL查询语句是如何执行的?
第一步,你会先连接到这个数据库上,这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的:mysql -h$ip -P$port -u$user -p。连接建立完成后,你就可以执行 select 语句了。执行逻辑就会来到第二步:查询缓存。MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 k...转载 2019-05-05 20:46:39 · 153 阅读 · 0 评论 -
一条SQL更新语句是如何执行的?
update T set c=c+1 where ID=2;执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。 执行器拿到引擎给的行数据,把这个值加上 1,比如原来是 N,现在就是 N+1,得到新的一行数据,再调用引擎接口写入这行新数据。 引擎将这行...转载 2019-05-05 21:58:47 · 169 阅读 · 0 评论 -
redo日志写入为什么“俩阶段提交”
先写 redo log 后写 binlog假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启。由于我们前面说过的,redo log 写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了,这时候 binlog 里面就没有记录这个语句。因此,之后备份日志的时候,存起来的 binlog...转载 2019-05-05 22:04:40 · 1464 阅读 · 0 评论 -
你懂change buffer吗
首先说下change buffer 是个啥?为了减少对数据库的IO操作,将更新操作用change buffer保存起来。如果要访问刚刚更新的数据页,就会将数据页读取到内存中,然后执行 change buffer 中与这个页有关的操作。这个东西在不同索引的情况下表现不同,让我们来瞅瞅。对于唯一索引来说,所有的更新操作都要先判断这个操作是否违反唯一性约束。比如,要插入 (4,400) 这个...转载 2019-05-08 16:15:19 · 496 阅读 · 0 评论 -
MySQL为什么有时候会选错索引?
选择索引是优化器的工作。。。优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。通过三个指标进行选择:扫描行数 是否使用临时表 是否排序扫描行数是怎么判断的?MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根 据统计信息来估算记录数。 这个统计信息就是索引的“区分度”。显然,一个索引上不同的值越多,这个索引的区分度就越 ...转载 2019-05-08 21:05:53 · 303 阅读 · 0 评论 -
举例说明事务隔离级别
若隔离级别是“读未提交”, 则 V1 的值就是 2。这时候事务 B 虽然还没有提交,但是结果已经被 A 看到了。因此,V2、V3 也都是 2。 若隔离级别是“读提交”,则 V1 是 1,V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以, V3 的值也是 2。 若隔离级别是“可重复读”,则...原创 2019-05-06 20:41:48 · 476 阅读 · 1 评论 -
InnoDB 的索引模型
在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织 表。InnoDB 使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中 的。 每一个索引在 InnoDB 里面对应一棵 B+ 树。假设,我们有一个主键列为 ID 的表,表中有字段 k,并且在 k 上有索引。 这个表的建表语句是: 表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,...原创 2019-05-06 21:31:59 · 542 阅读 · 0 评论 -
全字段排序 VS rowid 排序
CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` int(11) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`id`), KEY `city` (`city`...转载 2019-05-09 15:30:51 · 1160 阅读 · 0 评论 -
覆盖索引&&最左前缀原则&&索引下推
覆盖索引如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查 询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。 由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能 优化手段。...转载 2019-05-06 22:28:06 · 663 阅读 · 2 评论 -
Mysql俩种表级锁
MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放 锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的 读写外,也限定了本线程接下来的操作对象。 举个例子, 如果在某个...转载 2019-05-07 15:34:49 · 202 阅读 · 0 评论 -
MDL了解一下
在 MySQL 5.5 版本中引入了 MDL,当对一个表做增删改查操作的时候,加 MDL 读 锁;当要对表做结构变更操作的时候,加 MDL 写锁。读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。 读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线 程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。 虽然 MDL 锁是系统默认会加的,但却是...转载 2019-05-07 15:47:31 · 1133 阅读 · 0 评论 -
extra字段
using index :使用覆盖索引的时候就会出现using where:在查找使用索引的情况下,需要回表去查询所需的数据using index condition:查找使用了索引,但是需要回表查询数据using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据...原创 2019-08-29 09:50:28 · 303 阅读 · 0 评论