mysql
文章平均质量分 90
jeff-y
所有文章用于个人记录,仅供参考,有错误的地方还请指出错误。
展开
-
细品事务机制(一)
细品事务机制(一)什么是事务?事务在每个系统中都会涉及,它存在的意义就时符合预期的期望。且相互关联的数据之间不会产生矛盾,也就是数据状态的一致性。数据库的经典理论需要达成一致性这个目标,需要三方面共同来努力保证:原子性:在同一项业务处理过程中事务保证了各业务正在读,写的数据同时成功,要么同时被撤销。通俗就时 要么完全完成某一件事,要么就要回到这件事情最初状态,就算你做了一般你也要进行“回滚”到最初的状态隔离性:在不同的业务中,事务保证了各业务正在读,写的数据相互独立,不被彼此影响。也许你在此想到了原创 2021-07-18 18:30:08 · 291 阅读 · 1 评论 -
Mysql 5.6 “隐式转换”导致的索引失效和数据不准确
背景在一次进行SQl查询时,我试着对where条件中vachar类型的字段去掉单引号查询,这个时候发现这条本应该很快的语句竟然很慢。这个varchar字段有一个复合索引。其中的总条数有58989,甚至不加单引号查出来的数据不是我们想要的数据。使用的是mysql 5.6版本,innoDB引擎实际情况如下下面我们来看一下执行的结果在上面的描述中我们还得注意就是,你的where条件的字符串不加单引号必须是全数字。不然就会报错还有可能查出来的数据不是我们想要的数据。如下图分析原创 2020-12-06 16:48:34 · 2446 阅读 · 6 评论 -
稀疏索引和稠密索引你了解吗?
背景最近参加了一个面试,面试官先问了mysql的数据库的索引的底层数据接口,我回答了:平时都用的是innodb引擎,所以其底层的索引数据类型是B+树。面试官问我用没用过稀疏索引。当时就懵了,聚集索引,非聚集索引,主键索引,覆盖索引等等,我也没听过什么是稀疏索引。我反问了一下 面试官这个索引类型是mysql新出的吗,我不太了解也没有怎么用过,面试官模糊的给我回答了一下:一个占用空间小查询效率相对低,一个查询效率高,存储空间比较大,用法是在创建索引的时候进行设置参数。我坦白道:不清楚,下去了解一下。稠密索引原创 2020-10-02 19:10:04 · 1101 阅读 · 1 评论 -
细品mysql之Join 语句的执行过程
背景今天优化了一个,join关联查的语句,需要优化join的语句,那我们肯定得了解他的一个执行过程。正所谓知己知彼,百战百胜!!join的查询算法1. Simple Nested-Loop Join(简单的嵌套循环连接)简单嵌套循环算法的查询过程是嵌套查询,这个关联查询语句首先不能确定那个是驱动表,因为使用join的话,mysql的优化器会自己进行索引的选择(这也时一般情况下DBA不让join查询的原因之一)。如果a 和 b字段 在都没有索引的情况下就会出现这种算法查询。查询过程:先在t1表中将原创 2020-09-21 21:37:13 · 1421 阅读 · 0 评论 -
一看就会的mysql索引优化(真实案例)
背景(使用的数据库:MYSQL 5.7 版本,InnoDB 引擎)自从服务加了Skywalking后,将大部分慢接口暴露出来。于是就有了这次慢接口的优化。大概的优化过程,没有详细的具体过程优化前:优化后:优化步骤1. 排查通过skywalking可以清楚的看到慢接口是在哪一步比较慢。通过调用情况可以清楚的看到其链路调用情况如下图:如果说你的服务没有接如这种情况监控服务,那我们可以使用阿里巴巴开源的Arthas来进行链路追踪(使用trace进行查看每一步方法的调用耗时)a原创 2020-08-25 22:46:12 · 1561 阅读 · 0 评论 -
Mysql5.6版本新增列,执行DDL的时候竟然锁表了,这应该是是最好的解决办法之一了!!
背景业务需求需要在数据库表新赠一个字段,执行DDL且在不停服的情况下。实际操作第一次提交SQLalter table tibtc23add `red_flag` varchar(2) DEFAULT '0' COMMENT '红冲状态(1-已红冲,0-未红冲)';预测有效行数有700多万,DBA刚开始执行的时候,说还有用户使用会产生原数据锁,最后会引起锁表,于是推迟到11.多。第二次提交第二次执行的时候已经11.多,用户量很少(偏Tob),但是DBA执行中,反馈是我们有业务原创 2020-08-06 13:25:41 · 1046 阅读 · 0 评论 -
MySQL online DDL 更改Varchar的字段长度
背景修改Varchar 的大小的时候。不用锁表,就可以进行操作DDL。这个还得具体查看版本,还有具体情况。下来我们来看一下INnoDB中的Online DDL各个操作请求。mysql 在DDL权威指南5.6 官方文档 :verision :https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html5.7 官方文档 : https://dev.mysql.com/doc/refman/5.7/en/innod原创 2020-07-23 13:09:27 · 3022 阅读 · 0 评论 -
mysql 中的innoDB 引擎的B+树索引
背景在优化慢接口的时候,遇到一个问题,在通过索引查询数据库表的时候根据时间区间去扫描表的时候,开始时间时表扫描的其实位置吗?或者说根据时间日期B+索引能一次性定位到具体的时间位置吗?是的不能。那为什么不能呢? 接下来我们来看看b+树索引的底层数据结构。InnoDB索引概述innoDB存储引擎支持的索引有:B+树索引全文索引哈希索引在这需要注意的是InnoDB存储引擎支持的hash索引是自适应的,innoDB会根据表的情况自动生成hash索引,不能人为的干预是否在一张表中生成hash索引原创 2020-07-22 01:27:44 · 463 阅读 · 0 评论 -
在Mysql中CHAR和VARCHAR如何选择?给定的长度到底是用来干什么的?
背景同事在设计表,有一个字段为发票号码,在一般来讲发票号码的长度一般是8位,varchar类型, 他的意思是给20位,我的意思是给少点12位就够了。又因为我们在老的业务里给的是12位,出现过存储的字段过长而导致未能存储的问题。但是解决这个问题的方法是在业务逻辑层做check 然后进行截取(目前我的做法)。因为本来超过了就是不对的,所以这样处理。为何要浪费那个空间呢??于是又讨论到了varcha...原创 2020-04-30 02:04:54 · 2107 阅读 · 1 评论 -
MySQL parttion分区,以及分区和分表的区别
MYSQL 官方文档对分区的描述SQL标准在数据存储的物理方面没有提供太多的指南。SQL语言的使用独立于它所使用的任何数据结构或图表、表、行或列下的介质。但是,大部分高级数据库管理系统已经开发了一些根据文件系统、硬件或者这两者来确定将要用于存储特定数据块物理位置的方法。在MySQL中,InnoDB存储引擎长期支持表空间的概念,并且MySQL服务器甚至在分区引入之前,就能配置为存储不同的数据库使用...原创 2020-03-28 13:59:20 · 304 阅读 · 0 评论 -
【MSQL数据库】MySQL中的NULL
mysql中的NUll是什么 ?维基百科是这样说的:空值(Null或NULL)是结构化查询语言中使用的特殊标记,是中对数属性未知或缺失的一种标识,用于指示数据库中不具值。由关系数据库模型的创作者 E.F.科德所引入。SQL空值是用来满足真实关系数据库管理系统(RDBMS)中,支持“缺失信息与不适用的信息”的需求。科德还介绍了在数据库理论中使用小写的希腊字母(ω)符号来表示空值。在 SQL中则是...原创 2020-03-07 20:26:26 · 370 阅读 · 0 评论 -
MySQL innoDB 中的锁升级
什么是锁升级?锁升级是指将当前锁的粒度降低,如一把行锁升级唯一把页锁,或者将页锁升级为表锁,如果在数据库设计中认为锁是一中稀有资源,哪么就会频繁有锁升级的现象发生锁升级的现象当一条SQL语句对一个对象上持有的锁数量超锁了阈值,默认这个阈值为5000,但是对于不同对象不会发生锁升级锁资源占用的内存超过激活内存的百分之40 就会发生锁升级但是!!!!!innoDB 引擎不存在锁升级...原创 2019-12-22 15:14:08 · 2478 阅读 · 7 评论 -
关于使用MySQL innoDB引擎中事务和锁的信息记录表
背景在INNODB 1.0之前查看数据库线程的方式是通过命令行:show full processlist查看inodb的所有进程列表信息字段名说明id一个标识user显示当前用户,如果不是root,这 个命令就只显示你权限范围内的sql语句。host显示这个语句是从哪个ip的哪个端口上发出的db显示 这个进程目前连接的数据库。...原创 2019-12-22 13:25:19 · 236 阅读 · 1 评论 -
mysql join关联查询需注意的问题
1.能不能使用 join 语句?如果可以使用 Index Nested-Loop Join 算法,也就是说可以用上被驱动表上的索引,其实是没问题的;如果使用 Block Nested-Loop Join 算法,扫描行数就会过多。尤其是在大表上的 join 操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源。所以这种 join 尽量不要用。2. 如果要使用 join,应该选择大表做驱...原创 2019-12-19 22:34:38 · 777 阅读 · 0 评论 -
分数排名
分数排名使用mysql进行分数排名:使用窗口函数解决问题专用窗口函数rank, dense_rank, row_number。上面三者有什么区别呢?是如何使用呢?example:select *, rank() over (order by 成绩 desc) as ranking, dense_rank() over (order by 成绩 desc) as dese...原创 2019-11-24 11:14:41 · 407 阅读 · 0 评论 -
关于mysql数据库使用innoDB引擎产生的死锁
在继我上一次一条select语句导致数据库飙升,到这一次一条select 语句导致数据库直接挂掉(当然这一次并不是我做的,绩效自动降一级)一直想了解到底是怎么回事,这几天开始看mysql内幕,个人感觉很不错的一本书。在此我大概描述一下innoDB 中的锁:标准的行级锁1. X锁 (排他锁)允许事物读一行数据2. S锁 (共享锁)允许事物更改或更新一行数据当有一条数据事T1 读取会加上一...原创 2019-09-25 23:57:41 · 212 阅读 · 0 评论 -
关于MYSQL 的日志系统
MySQL中有两个储存日志的地方:server层的bin.log 其主要作用就是为数据库做备份innoDB中的redo.log 主要作用是为数据库的变更状态关于对binlog的大概了解:binlog 是没有大小的他是可以不断追加记录,binlog是记录完整的逻辑操作记录,当数据库重启的时候 可以备份出一份和初始数据库一样的数据库备份binlogbinlog的两种格式: ...原创 2019-08-06 09:38:21 · 145 阅读 · 0 评论 -
什么是索引?(补充)
什么是索引?索引是一种算法(B-tree,HASH等),索引也就是将数据列以某种算法的方式进行排列(如B-tree)本是通过全表查询(ALL) 而通过建立索引而使列数据变为有序的数据 而减少查询行数,也就比如一本书,目录也就是他的索引,如过没有目录你就得一页一页的翻,而通过目录可以大大减少数据检索的范围。索引的分类聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其...原创 2019-08-06 23:46:37 · 595 阅读 · 0 评论 -
MySQL innoDB的事务隔离
关于事务的特性?ACID原子性,一致性 ,隔离性 ,持久性。innoDB 中的事务的隔离级别?读提交: 一个事物在已提交的时候才可以被其他事务看到可重复读: 一个事务在执行过程中看到的数据,总是跟另个一个事物在启动时看到的数据是一致的,只要事务未提交都对其他事务是不可见的。读未提交:这个事务还未提交就可以被其他事物所看到的。串行化:读 会加“读锁” 写会加“写锁” 当事务发生冲突的...原创 2019-08-07 13:55:33 · 160 阅读 · 0 评论 -
分页查询 offset 和 limit 和 limit 的区别
select * from table limit 0,1000;select * from table limit 1000 offset 0;// 上面两种分页查询方式都是从表中的第一条数据(包含第一条)开始查,查出1000条//如果说是page row 的形式传过来你没有page-1 则会漏查一条数据关于分页查询的优化:以前我在mysql中分页都是用的 limit 100000,2...原创 2019-08-14 14:10:03 · 3670 阅读 · 1 评论 -
MySQL EXPLAIN 的使用
EXPLAIN列的解释:table:显示这一行的数据是关于哪张表的type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref > fulltext >...转载 2019-08-14 14:19:11 · 133 阅读 · 0 评论 -
一条select语句引发的惨案!!!!!
日常忙碌的一天,突如其来的报警,让我一脸懵逼!!!!钉钉报警,从早上9.多报警一直到下午3. mysql数据库的cpu占比飙升 原本的 60多到96%。 为什么会持续那么长时间呢? 当时大家都特别忙,我也没在意,leader在忙其他的,当到下午的时候DBA 在群里面再次艾特领导让查那条SQL语句,我就在群里扫了一眼,卧槽 !!!! 这不就是我的那条,我当时慌了,赶紧给DBA 回消息切给领导汇报,...原创 2019-08-31 00:23:30 · 195 阅读 · 0 评论 -
唯一索引和普通索引性能比较
前提条件 有一个数据列不会有重复数据现在需要选择创建索引,我们应该如何去选择呢?1.普通索引查找时,遇第一个不符合条件的才返回。更新时,找到位置直接更新2.唯一性索引查找时,遇到第一个符合条件的直接返回。更新时,先查找是否唯一,再找到位置然后再更新。我们来通过底层分析一下:Select:我们都知道一个索引就是创建一个B+树,如果他是一个普通索引我们现在去查找,当查找到符合的数据...原创 2019-09-07 18:47:55 · 4092 阅读 · 1 评论 -
选择普通索引还是唯一索引?(转)
选择普通索引还是唯一索引?对于查询过程来说:a、普通索引,查到满足条件的第一个记录后,继续查找下一个记录,知道第一个不满足条件的记录b、唯一索引,由于索引唯一性,查到第一个满足条件的记录后,停止检索但是,两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。对于更新过程来说:概念:change buffer当需要更新一个数据页,如果数据页在内存中就直接更新,如果不在内存中,在不...原创 2019-09-07 18:51:02 · 154 阅读 · 0 评论 -
mysql explain用法和结果的含义
转自:http://blog.chinaunix.net/uid-540802-id-3419311.htmlexplain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法,在select语句前加上explain就可以了:如:explain select surname,first_name form a,b where ...转载 2019-09-11 00:06:17 · 155 阅读 · 0 评论 -
mysql复合索引、普通索引总结
( 转 ) mysql复合索引、普通索引总结对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效。下面用几个例子对比查询条件的不同对性能影响.create table t...原创 2019-09-11 09:09:20 · 587 阅读 · 0 评论 -
在mysql中order by是怎样工作的?
先举一个例子:创建一张表: CREATE TABLE `t` ( `id` int(11) NOT NULL, `city` varchar(16) NOT NULL, `name` varchar(16) NOT NULL, `age` int(11) NOT NULL, `addr` varchar(128) DEFAULT NULL, PRIMARY KEY (`...原创 2019-09-18 09:44:43 · 174 阅读 · 0 评论 -
为什么DBA不让给MySQL数据库中加触发器呢?
在一次迭代的时候,我给数据库中增加了两个时间字段: `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',当时是紧急发布在leader的劝说下DBA 允许先上了,DBA当时就提了下个迭代给撤掉。为什么DBA不让给数据库加触发器呢?触发器功能强大...原创 2019-09-22 22:54:15 · 684 阅读 · 0 评论 -
关于mysql给列加索引这个列值中有null的情况
在需求中由于要批量查数据,且表中数据量挺大(2300万条记录) 且查询条件的这两个字段没有加索引,为了增加查询速度,现在需要去为这两个字段添加索引。刚开始加索引想到的问题:是否适合添加索引我们都知道,添加索引都会降低插入和update的效率,现在由于这个是用户表所以说是数据update是不频繁的。 所以是可以加的这个作引应该怎么加由于每个字段的大小是256 所以说这个索引树建下来还是...原创 2019-09-25 00:58:10 · 2244 阅读 · 1 评论 -
SQL语句在MYSQL中的运行过程和各个组件的介绍
一. Mysql的组件有哪些?mysql是由两层组成的:客户端,连接器,查询缓存(MYSQL8.0以后没有这个数据)分析器,优化器,执行期,存储引擎。二.各个组件所负责的责任是什么?连接器:权限校验,查看登录用户是否有权限访问数据库,如果出错就会出现(Access denied for user)然后运行程序就结束了如果连接成功连接器就会去查看这个用户的权限,即以后的权限逻辑都是依赖于...原创 2019-08-03 13:41:40 · 436 阅读 · 0 评论