首页
影视
壁纸
留言
关于
今日热榜
推荐
音乐解锁
听歌
阅读器
听歌2
小乞丐
摸鱼
Search
1
Navicat Premium for Mac 中文破解版 (强大的数据库管理工具)
9,632 阅读
2
植物大战僵尸中文版 for Mac (兼容 M1系统) 中文版
5,767 阅读
3
CleanMyMac X 4.15.2 中文破解版 (Mac优化清理工具)
3,296 阅读
4
PDF Expert 中文破解版 (好用的PDF编辑器)
3,044 阅读
5
Parallels Desktop 17 中文版 (PD虚拟机无限试用版本)
2,822 阅读
生活杂记
macOS
编程技术
奇技淫巧
音乐
视频
δ
Search
标签搜索
python
漫画
php
mac
redis
mysql
mac 软件
macOS
音乐
极客爱情
数据库
游戏
吊打面试官
2.0
面试
罗小黑
linux
纯音乐
使用教程
B站
Kain
累计撰写
210
篇文章
累计收到
26
条评论
首页
栏目
生活杂记
macOS
编程技术
奇技淫巧
音乐
视频
δ
页面
影视
壁纸
留言
关于
今日热榜
推荐
音乐解锁
听歌
阅读器
听歌2
小乞丐
摸鱼
搜索到
7
篇与
的结果
一步步搞懂 MySQL 元数据锁(MDL)
2022年09月19日
85 阅读
0 评论
0 点赞
2022-09-19
到底什么是 metadata lock?这个锁等待是如何产生的?会带来什么影响?最后又如何来解决?
MySQL 中删除的数据都去哪儿了?
2021年08月12日
122 阅读
1 评论
1 点赞
2021-08-12
不知道大家有没有想过下面这件事?我们平时调用 `DELETE` 在 MySQL 中删除的数据都去哪儿了?
2021-01-28
教你两种数据库覆盖式数据导入方法
摘要:本文主要介绍如何在数据库中完成覆盖式数据导入的方法。前言众所周知,数据库中INSERT INTO语法是append方式的插入,而最近在处理一些客户数据导入场景时,经常遇到需要覆盖式导入的情况,常见的覆盖式导入主要有下面两种:1、部分覆盖:新老数据根据关键列值匹配,能匹配上则使用新数据覆盖,匹配不上则直接插入。2、完全覆盖:直接删除所有老数据,插入新数据。本文主要介绍如何在数据库中完成覆盖式数据导入的方法。部分覆盖业务场景某业务每天给业务表中导入大数据进行分析,业务表中某列存在主键,当插入数据和已有数据存在主键冲突时,希望能够对该行数据使用新数据覆盖或者说更新,而当新老数据userid不冲突的情况下,直接将新数据插入到数据库中。以将表src中的数据覆盖式导入业务表des中为例:应用方案方案一:使用DELETE+INSERT组合实现(UPDATE也可以,请读者思考)--开启事务 START TRANSACTION; --去除主键冲突数据 DELETE FROM des USING src WHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid); --导入新数据 INSERT INTO des SELECT * FROM src WHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid); --事务提交 COMMIT;方案优点:使用最常见的使用DELETE和INSERT即可实现。方案缺点:1、分了DELETE和INSERT两个步骤,易用性欠缺;2、借助子查询识重,DELETE/INSERT性能受查询性能制约。方案二:使用MERGE INTO功能实现MERGE INTO des USING src ON (des.userid = src.userid) WHEN MATCHED THEN UPDATE SET des.b = src.b WHEN NOT MATCHED THEN INSERT VALUES (src.userid,src.b);方案优点:MERGE INTO单SQL搞定,使用便捷,内部去重效率高。方案缺点:需要数据库产品支持MERGE INTO功能,当前Oracle、GaussDB(DWS)等数据库已支持此功能,mysql的insert into on duplicate key也类似此功能。完全覆盖业务场景某业务每天给业务表中导入一定时间区间的数据进行分析,分析只需要导入时间区间的去除,不需要以往历史数据,这种情况就需要使用到覆盖式导入。应用方案方案一:使用TRUNCATE+INSERT组合实现--开启事务 START TRANSACTION; --清除业务表数据 TRUNCATE des; --插入1月份数据 INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00'; --提交事务 COMMIT;方案优点:简单暴力,先清理在插入直接实现类似覆盖写功能。方案缺点:TRUNCATE清理业务表des数据时对表加8级锁直到事务结束,在因数据量巨大而INSERT时间很长的情况下,des表在很长时间内是不可访问的状态,业务表des相关的业务处于中断状态。方案二:使用创建临时表过渡的方式实现--开启事务 START TRANSACTION; --创建临时表 CREATE TABLE temp(LIKE desc INCLUDING ALL); --数据先导入到临时表中 INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME < '2020-02-01 00:00:00'; --导入完成后删除业务表des DROP TABLE des; --修改临时表名temp->des ALTER TABLE temp RENAME TO des; --提交事务 COMMIT;方案优点:相比方案一,在INSERT期间,业务表des可以继续被访问(老数据),即事务提交前分析业务可继续访问老数据,事务提交后分析业务可以访问新导入的数据。方案缺点:1、组合步骤较多,不易用;2、DROP TABLE操作会删除表的依赖对象,例如视图等,后面依赖对象的还原可能会比较复杂。方案三:使用INSERT OVERWRITE功能INSERT OVERWRITE INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';方案优点:单条SQL搞定,执行便捷,能够支持一键式切换业务查询的新老数据,业务不中断。方案缺点:需要产品支持INSERT OVERWRITE功能,当前impala、GaussDB(DWS)等数据库均已支持此功能。总结随着大数据的场景越来越多,数据导入的场景也越来越丰富,除了本文介绍的覆盖式数据导入,还有其他诸如忽略冲突的INSERT IGNORE导入等等其他的导入方式,这些导入场景可以以使用基础的INSERT、UPDATE、DELETE、TRUNCATE来组合实现,但是也同样会对高级的一键SQL功能有直接诉求,后面有机会再继续介绍。
2021年01月28日
125 阅读
0 评论
0 点赞
2021-01-23
理解完这些基本上能解决面试中MySql的事务问题
越努力,越幸运前言在面试中,基本上都会问到关于数据库的事务问题,如果啥都不会或者只回答到表面的上知识点的话,那面试基本上是没戏了,为了能顺利通过面试,那MySql的事务问题就需要了解,所以就根据网上的资料总结一版Mysql事务的知识点,巩固一下事务的知识。事务事务是指逻辑上的一组操作,要么都执行,要么都不执行,事务的特性(ACID)原子性(Atomicity):事务是不可分割的工作单元,要么都成功,要么都失败, 如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态。一致性(Consistency):事务不能破坏数据的完整性和业务的一致性 。例如在银行转账时,不管事务成功还是失败,双方钱的总额不变隔离性(Isolation):一个事务所操作的数据在提交之前,对其他事务的可见性设定(一般是不可见)持久性(Durability):事务提交之后,所做的修改就会永久保存,不会因为系统故障导致数据丢失严格来说,只有同时满足数据库的事务ACID特性才能算一个完整的事务,但现实中实现能够真正满足的完整的事务特性少之又少,但是在实现中也必须尽量达到事务要求的特性。那么事务ACID特性具体怎么实现的呢?我们来分析看看,首先先看看事务的特性。原子性(Atomicity)首先我们来看看事务的原子性特性,看看其如何实现的?原子性(Atomicity):事务是不可分割的工作单元,要么都成功,要么都失败, 如果事务中一个sql语句执行失败,则已执行的语句也必须回滚,数据库退回到事务前的状态原子性(Atomicity)的实现离不开 MySQL的事务日志 undo log日志类型,当事务需要回滚的时候需要将数据库状态回滚到事务开始前,即需要撤销所有已经成功执行的sql语句。那么undo log起了关键性作用:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。那么undo log是什么呢?每个数据变更操作是怎么被记录下来的呢?undo log( 回滚日志 )undo log (回滚日志):是采用段(segment)*的方式来记录的,每个undo操作在记录的时候占用一个*undo log segment。为什么会在数据更改操作的时候,记录了相对应的undo log呢?其目的在于:为了保证数据的原子性,记录事务发生之前的一个版本,用于回滚,通过mvcc+undo log实现innodb事务可重复读和读取已提交隔离级别。其中,undo log分为:insert undo log : insert操作中产生的undo log,update undo log: 对delete 和update操作产生的undo log数据更改的undo log怎么记录的呢?因为insert操作的记录,只对事务本身可见,对其他事务不可见。故该undo log可以在事务提交后直接删除,不需要进行purge操作,而Delete操作在事务中实际上并不是真正的删除掉数据行,而是一种Delete Mark操作,在记录上标识Delete_Bit,而不删除记录。是一种"假删除",只是做了个标记,真正的删除工作需要后台purge线程去完成。update分为两种情况:update的列是否是主键列。如果不是主键列,在undo log中直接反向记录是如何update的。即update是直接进行的。如果是主键列,update分两部执行:先删除该行,再插入一行目标行。与insert undo log 不同的,update undo log日志,当事务提交的时候,innodb不会立即删除undo log, 会将该事务对应的undo log放入到删除列表中,未来通过purge线程来删除。因为后续还可能会用到undo log,如隔离级别为repeatable read时,事务读取的都是开启事务时的最新提交行版本,只要该事务不结束,该行版本就不能删除(即undo log不能删除),且undo log分配的页可重用减少存储空间和提升性能。Note: purge线程两个主要作用是:清理undo页和清除page里面带有Delete_Bit标识的数据行。接着我们来看看事务的隔离性,看看事务有哪些隔离级别,而且事务并发中会产生什么问题。隔离性(Isolation)隔离性(Isolation),是指事务内部的操作与其他事务是隔离的,并发执行的各个事务之间不能互相干扰 ,一个事务所操作的数据在提交之前,对其他事务的可见性设定(一般是不可见)。事务隔离级别而且数据库为了在并发下有效保证读取数据正确性,数据库提供了四种事务隔离级别,分别为:读未提交(脏读):允许读取尚未提交的数据,允许脏读读已提交( 不可重复读 ):允许读取事务已经提交的数据可重复读( 幻读 ):在同一个事务内的查询结果都是和事务开始时刻查询一致的( InnoDB默认级别 )串行化:所有事务逐个依次执行, 每次读都需要获得表级共享锁,读写相互都会阻塞其中,不同的隔离级别可能会存在在不同并发问题,主要并发问题包括:数据丢失: 两个或多个事务操作相同数据,基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新脏读:读到了其他事务还未提交的数据,事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据不可重复读(重点是修改):在一个事务中,先后进行两次相同的读取,由于另一个事务修改了数据,导致前后两次结果的不一致,事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。幻读(重点是新增、删除): 在一个事务中,先后进行两次相同的读取(一般是范围查询),由于另一个事务新增或删除了数据,导致前后两次结果不一致不可重复读和幻读的区别?不可重复读和幻读最大的区别,就在于如何通过锁机制来解决他们产生的问题,使用锁机制来实现这两种隔离级别,在可重复读中,相同sql第一次读取到数据后就将这些数据加锁,其它事务无法更新操作这些数据来实现可重复读了隔离。但这种处理方式却无法锁住insert的数据,因此会出现当事务A先前读取了数据,事务B再insert数据提交,结果发现事务A就会发现莫名其妙多了些数据,这就是幻读,不能通过行锁来避免 。了解了并发问题后,来看看不同的隔离级别可能会存在在不同并发问题:事务隔离级别脏读不可重复读幻读读未提交是是是不可重复读否是是可重复读否否是串行化否否否为了实现事务隔离,延伸出了数据库锁。其中,innodb事务的隔离级别是由锁机制和MVCC(多版本并发控制)来实现的那我们来先看看锁的原理,怎么使用锁来实现事务隔离的呢?锁机制锁机制的基本工作原理,事务在修改数据之前,需要先获得相应的锁;获得锁之后,事务便可以修改数据;该事务操作期间,这部分数据是锁定的,其他事务如果需要修改数据,需要等待当前事务提交或回滚后释放锁,MySQL主要分成三种类型(级别)的锁机制:表级锁:最大颗粒度的锁机制,锁定资源争用的概率也会最高 ,并发度最低 ,但开销小,加锁快,不会出现死锁,行级锁:最大颗粒度的锁机制很小, 发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能 ,但 开销大,加锁慢;会出现死锁 ,页级锁: 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般而且不同的存储引擎支持不同的的锁机制,主要分析一下InnoDB锁。InnoDB锁InnoDB实现了以下两种类型的行锁共享锁(S锁、行锁):多个事务对同一数据行可以共享一把锁,只能读不能修改排它锁(X锁、行锁):一个事务获取一个数据行的排它锁,那么其他事务将不能再获取该行的锁(共享锁、排它锁), 允许获取排他锁的事务更新数据对于UPDATE,DELETE,INSERT操作, InnoDB会自动给涉及及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁,而且因为InnoDB引擎允许行锁和表锁共存,实现多粒度锁机制,使用意向锁实现表锁机制,意向共享锁(IS锁、表锁):当事务准备给数据行加共享锁时,会先给表加上一个意向共享锁。意向共享锁之间是兼容的意向排它锁(IX锁、表锁):当事务准备给数据行加排它锁时,会先给表加上一个意向排它锁。意向排它锁之间是兼容的意向锁(IS、IX)是InnoDB数据操作之前自动加的,不需要用户干预。它的意义在于:当事务想去进行锁表时,可以先判断意向锁是否存在,存在时则可快速返回该表不能启用表锁,否则就需要等待,其中,四种锁的兼容性如下当前锁模式/是否兼容/请求锁模式XIXSISX冲突冲突冲突冲突IX冲突兼容冲突兼容S冲突冲突兼容兼容IS冲突兼容兼容兼容如果一个事务请求的锁模式与当前的锁兼容,InnoDB就请求的锁授予该事务;反之,如果两者两者不兼容,该事务就要等待锁释放。InnoDB行锁InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据,才能使用行锁,否则将使用表锁(锁住索引的所有记录)临键锁(next-key),可以防止幻读。根据索引,划分为一个个左开右闭的区间。当进行范围查询的时候,若命中索引且能够检索到数据,则锁住记录所在的区间和它的下一个区间,其实,临键锁(Next-Key)=记录锁(Record Locks)+间隙锁(Gap Locks),当我们用范围条件检索数据而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合范围条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做间隙(GAP)。当使用唯一索引,且记录存在的精准查询时,使用Record Locks记录锁具体的使用体现在哪里呢?如下图所示:范围查询,记录存在当记录不存在(不论是等值查询,还是范围查询)时,next-key将退化成Gap Lock(间隙锁)当条件是精准匹配(即为等值查询时)且记录存在时,并且是唯一索引,临键锁(Next-Key)*退化成*Record Lock(记录锁)当条件是精准匹配(即为等值查询时)且记录存在,但不是唯一索引时,临键锁(Next-Key)*会有精准值的数据会增加*Record Lock(记录锁)*和精准值前后的区间的数据会增加*Gap Lock(间隙锁)。如何使用锁解决并发问题利用锁解决脏读、不可重复读、幻读X锁解决脏读S锁解决不可重复读临键锁解决幻读Multiversion concurrency control (MVCC 多版本并发控制)InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的,一个保存了行的事务ID(事务ID就会递增 ),一个保存了行的回滚段的指针 。每开始一个新的事务,都会自动递增产 生一个新的事务id。事务开始时刻的会把事务id放到当前事务影响的行事务id中,而DB_ROLL_PTR表示指向该行回滚段的指针,该行记录上所有版本数据,在undo中都通过链表形式组织,该值实际指向undo中该行的历史记录链表,在并发访问数据库时,对正在事务中的数据做MVCC多版本的管理,以避免写操作阻塞读操作,并且会通过比较版本解决幻读。而且MVCC只在REPEATABLE READ和READ COMMITIED两个隔离级别下才会工作,其中,MVCC实现实质就是保存数据在某个时间点的快照来实现的。 那哪些操作是快照读?快照读和当前读快照读,innodb快照读,数据的读取将由 cache(原本数据) + undo(事务修改前的数据) 两部分组成普通的select,比如 select * from table where ?;当前读,SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改UPDATEDELETEINSERTSELECT … LOCK IN SHARE MODESELECT … FOR UPDATE其中当前读中,只有SELECT … LOCK IN SHARE MODE对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。那么在RR隔离级别下,MVCC具体是如何操作的。RR隔离级别下,MVCC具体操作SELECT操作,InnoDB遵循以后两个规则执行:InnoDB只查找版本早于当前事务版本的数据行(即行的事务编号小于或等于当前事务的事务编号,这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的记录。行的删除版本要么未定义,读取到事务开始之前状态的版本,这可以确保事务读取到的行,在事务开始之前未被删除.只有同时满足的两者的记录,才能返回作为查询结果.INSERT:InnoDB为新插入的每一行保存当前事务编号作为行版本号。DELETE:InnoDB为删除的每一行保存当前事务编号作为行删除标识。UPDATE:InnoDB为插入一行新记录,保存当前事务编号作为行版本号,同时保存当前事务编号到原来的行作为行删除标识。保存这两个额外系统版本号,使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单,性能很好,并且也能保证只会读取到符合标准的行,不足之处是每行记录都需要额外的存储空间,需要做更多的行检查工作,以及一些额外的维护工作。分析完了原子性和隔离性,我们继续看看事务的持久性。持久性(Durability)持久性(Durability):事务提交之后,所做的修改就会永久保存,不会因为系统故障导致数据丢失,而且其实现的关键在于redo log, 在执行SQL时会保存已执行的SQL语句到一个指定的Log文件,当执行recovery时重新执行redo log记录的SQL操作。那么redo log如何实现的呢?redo log当向数据库写入数据时,执行过程会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中(这一过程称为刷脏),这整一过程称为redo log。redo log 分为:Buffer Pool内存中的日志缓冲(redo log buffer),该部分日志是易失性的;磁盘上的重做日志文件(redo log file),该部分日志是持久的。Buffer Pool的使用可以大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据在内存还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。为了确保事务的持久性,在当事务提交时,会调用fsync接口对redo log进行刷盘, (即redo log buffer写日志到磁盘的redo log file中 ),刷新频率由 innodb_flush_log_at_trx_commit变量来控制的:0 : 每秒刷新写入到磁盘中的,当系统崩溃,会丢失1秒钟的数据 ;1: 事务每次提交都写入磁盘;2:每秒刷新写入到磁盘中的,但跟0是有区别的。redo log有更加详细的解读,后续有时间再补上,到现在为止,已经将事务三个特性都理解了,那事务一致性呢?一致性(Consistency)一致性(Consistency):事务不能破坏数据的完整性和业务的一致性 :数据的完整性: 实体完整性、列完整性(如字段的类型、大小、长度要符合要求)、外键约束等业务的一致性:例如在银行转账时,不管事务成功还是失败,双方钱的总额不变。那是如何保证数据一致性的?其实数据一致性是通过事务的原子性、持久性和隔离性来保证的原子性:语句要么全执行,要么全不执行,是事务最核心的特性,事务本身就是以原子性来定义的;实现主要基于undo log持久性:保证事务提交后不会因为宕机等原因导致数据丢失;实现主要基于redo log隔离性:保证事务执行尽可能不受其他事务影响;InnoDB默认的隔离级别是RR,RR的实现主要基于锁机制(包含next-key lock)、MVCC(包括数据的隐藏列、基于undo log的版本链、ReadView)总结其中要同时满足ACID特性,这样的事务少之又少。实际中很多例子都只是满足一些特性,比如:MySQL的NDB Cluster事务不满足持久性和隔离性;InnoDB默认事务隔离级别是可重复读,不满足隔离性;Oracle默认的事务隔离级别为READ COMMITTED,不满足隔离性所以我们只能使用这个四个维度的特性去衡量事务的操作。
2021年01月23日
112 阅读
0 评论
0 点赞
2020-09-07
Mysql Explain 详解[强烈推荐]
1. EXPLAIN简介使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。➤ 通过EXPLAIN,我们可以分析出以下结果:表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询➤ 使用方式如下:EXPLAIN +SQL语句EXPLAIN SELECT * FROM t1执行计划包含的信息2. 执行计划各字段含义2.1 idselect查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序id的结果共有3中情况id相同,执行顺序由上至下[总结] 加载表的顺序如上图table列所示:t1 t3 t2id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行id相同不同,同时存在如上图所示,在id为1时,table显示的是 <derived2> ,这里指的是指向id为2的表,即t3表的衍生表。2.2 select_type常见和常用的值有如下几种:分别用来表示查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。SIMPLE 简单的select查询,查询中不包含子查询或者UNIONPRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARYSUBQUERY 在SELECT或WHERE列表中包含了子查询DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中UNION 若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVEDUNION RESULT 从UNION表获取结果的SELECT2.3 table指的就是当前执行的表2.4 typetype所显示的是查询使用了哪种类型,type包含的类型包括如下图所示的几种:从最好到最差依次是:system > const > eq_ref > ref > range > index > all一般来说,得保证查询至少达到range级别,最好能达到ref。system 表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计const 表示通过索引一次就找到了,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。首先进行子查询得到一个结果的d1临时表,子查询条件为id = 1 是常量,所以type是const,id为1的相当于只查询一条记录,所以type为system。eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。range 只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般就是在你的where语句中出现between、< 、>、in等的查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。index Full Index Scan,Index与All区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘读取的)id是主键,所以存在主键索引all Full Table Scan 将遍历全表以找到匹配的行2.5 possible_keys 和 keypossible_keys 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。key实际使用的索引,如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效)查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中2.6 key_len表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。2.7 ref显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。2.8 rows根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好2.9 Extra包含不适合在其他列中显式但十分重要的额外信息2.9.1 Using filesort(九死一生)说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。2.9.2 Using temporary(十死无生)使用了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。2.9.3 Using index(发财了)表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。2.9.4 Using where表明使用了where过滤2.9.5 Using join buffer表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些。2.9.6 impossible wherewhere子句的值总是false,不能用来获取任何元组SELECT * FROM t_user WHERE id = '1' and id = '2'2.9.7 select tables optimized away在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。2.9.8 distinct优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作3. 实例分析执行顺序1:select_type为UNION,说明第四个select是UNION里的第二个select,最先执行【select name,id from t2】执行顺序2:id为3,是整个查询中第三个select的一部分。因查询包含在from中,所以为DERIVED【select id,name from t1 where other_column=’’】执行顺序3:select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】执行顺序4:id列为1,表示是UNION里的第一个select,select_type列的primary表示该查询为外层查询,table列被标记为<derived3>,表示查询结果来自一个衍生表,其中derived3中的3代表该查询衍生自第三个select查询,即id为3的select。【select d1.name …】执行顺序5:代表从UNION的临时表中读取行的阶段,table列的< union1,4 >表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】
2020年09月07日
148 阅读
0 评论
0 点赞
2020-08-08
教你几招,快速创建 MySQL 五百万级数据
如果你打算好好学习一下 MySQL,性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候,突然发现一个问题摆在眼前,本地数据库中没那么大的数据量啊,几条数据优化个毛线啊。生产库里数据多,但谁敢直接在生产环境动手啊,想被提前优化吗?要知道,程序员从不轻言放弃,没有数据我们就自己创造数据嘛,new 对象这种事情可是我们的拿手好戏,对象都能 new 出来,更别说几百万条数据了。使用官方数据官方显然知道我们需要一些测试数据做个练习什么的,所以准备了一份测试数据给我们。可以到 https://github.com/datacharmer/test_db 上去下载,这个数据库包含约30万条员工记录和280万个薪水条目,文件大小为 167 M。下载完成之后,直接使用 MySQL 客户端运行 sql 文件即可。或者直接使用命令,然后输入密码导入。mysql -u root -p < employees.sql这是最简单的一种方法,只要你能把 sql 文件下载下来就可以了。但是数据量不够大,员工表才 30 万条数据,还不够百万级别,而且字段都是定义好的,不能灵活定制。背景说明创建百万级数据的方式,要到达的目的有两点:定制比较灵活,不能只是一两个字段了事,那没什么实际意义。速度快,不能说弄个几百万数据好几个小时甚至更长,那不能接收。本次目标是创建两个表,一个用户表,另外一个订单表,当然没有真实环境中的表字段那么多,但是对于学习测试来说差不多够了。两个表的表结构如下:# 用户表 CREATE TABLE `user` ( `id` varchar(36) NOT NULL, `user_name` varchar(12) DEFAULT NULL, `age` tinyint(3) DEFAULT NULL, `phone` varchar(11) DEFAULT NULL, `province` varchar(10) DEFAULT NULL, `city` varchar(10) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; # 订单表 CREATE TABLE `order` ( `id` varchar(36) NOT NULL, `user_id` varchar(36) DEFAULT NULL, `product_count` int(11) DEFAULT NULL, `price` decimal(10,0) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4用户表(user)创建 500 万条数据,id 使用 uuid,年龄从 1 到 120 随机,电话号码随机 11 位,省份编码和城市编码随机,创建时间和更新时间在某一时间范围内随机。订单表(order)根据用户表生成,每个用户随机生成 0 到 3 个订单,订单编号采用 uuid,商品数量随机 1 到 5 个,价格随机,创建时间和更新时间在某一时间段内随机。由于每个用户产生 0 到 3 个订单,所以,产生的订单量应该大于 500 万,我在本地跑的时候基本上在 700多万左右。创建总时间和表的字段个数以及字段的生成算法有直接关系,字段越多、算法越复杂,需要的时间就越多,比如使用 uuid 就比使用自增 id 花费更长时间,随机时间就比直接使用当前时间花费更长时间。如果只插入 500 万自增 id 这一个字段,十几秒就能完成,但是无论是模拟线上环境还是自学性能优化技巧都没什么意义。下面就来介绍三种方式来快速创建 500 万用户数据以及大于 500 万的订单数据。写程序批量插入作为一个开发人员,当你打算创建百万条数据的时候,大多数时候首先相当的应该就是写程序,毕竟 CURD 我们最拿手了。用程序的方式插入也分两种情况,第一种就是逐条插入,这也是平时开发中最常用到的方法,直觉上我们可能会认为这样比较快。事实上并不是这样,虽然比起手动一条一条插入是快的多,但是,很有可能你在等待了一段时间后失去耐心,然后结束程序,不管你用哪种数据库连接池都一样,在百万数量级面前仍然慢的离谱。第二种情况就是使用 MySQL 的批量插入方法,我们都知道 MySQL 支持一次性插入多条记录,就是下面这样的形式。insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');这样一来,比你一条一条语句执行要快很多,比如 1000 条记录执行一次 insert,一共执行 5000 次即可,如果是一条一条插入呢,那就要执行 500 万次。由于后面两种方式用到了 Python 生成文件,所以这种方式也用了 Python 实现,实例代码如下。完整代码可在文末给出的 github 上获取。def insert_data(self): cursor = self.conn.cursor() for x in range(5000): insert_user_sql = """ insert into `user` ( `id`,`user_name`,`phone`,`age`, `province`, `city`, `create_time`,`update_time` ) VALUES(%s,%s,%s,%s,%s,%s,%s,%s) """ insert_order_sql = """ insert into `order` ( `id`, `product_count`, `user_id`, `price`, `create_time`, `update_time`) values(%s,%s,%s,%s,%s,%s) """ user_values, order_values = [], [] for i in range(1000): timestamp = self.randomTimestamp() time_local = time.localtime(timestamp) createTime = time.strftime("%Y-%m-%d %H:%M:%S", time_local) user_id = str(uuid.uuid4()) user_values.append( (user_id, "名字" + str(x) + str(i), self.createPhone(), random.randint(1, 120), str(random.randint(1, 26)), str(random.randint(1, 1000)), createTime, createTime)) random_order_count = random.randint(0, 3) if random_order_count > 0: for c in range(random_order_count): timestamp = self.randomTimestamp() time_local = time.localtime(timestamp) order_create_time = time.strftime("%Y-%m-%d %H:%M:%S", time_local) order_values.append((str(uuid.uuid4()), random.randint(1, 5), user_id, random.randint(10, 2000), order_create_time, order_create_time)) cursor.executemany(insert_user_sql, user_values) cursor.executemany(insert_order_sql, order_values) self.conn.commit() cursor.close()经过一段时间时间的等待后,运行完成了,整个运行过程耗时 1823 秒,30分钟。最后成功生成用户记录 500 万条,订单记录 749 万多条。速度还算能接受吧,马马虎虎吧。再想速度快一点,可以开多线程,我用 5 个线程跑了一下,一个线程插入 100万条,最终最长的线程耗时 1294秒,21分钟,也没快多少,线程个数对时间多少有些影响,但是我没有试。生成 SQL 脚本这种方式和上面的方式类似,只不过上面通过程序方式直接将拼接出来的 SQL 语句执行了,而这种方式是将拼接好的 SQL 语句写入文件中。当然还是以一条语句插入多行记录的形式。insert into `table_name` (id,column1) values (1,'value1'),(2,'value2'),(3,'value3');写 500 万用户数据,加上随机的订单数据, sql 文件的过程耗时为 696 秒,11分钟左右。当然这么大数据量拼接出来的脚本文件也很大,用户表脚本 680 多M,订单表脚本 1个G。最后将写好的这两个文件分别在 MySQL 中执行。执行用户表脚本,耗时 3 分钟左右。mysql -uroot -p mast_slave < sql/insert_user_500w.sql执行订单表脚本,耗时 7 分钟左右,订单量 750 多万个。mysql -uroot -p mast_slave < sql/insert_order_500w+.sql一共耗时,20分钟左右,加上中间的手工操作,感觉不如第一种方法中的多线程方式省事。load data infile 方式最后这种方式是使用 load data infile 方式,这是 MySQL 提供的一种从文件快速导入的方式。比如按照特定符号分隔,导入对应的字段中。本文例子中我是按照逗号分隔的,字段之间以逗号分隔,生成 500 万条用户行 和随机订单行。依然是用 Python 脚本生成文件,生成文件的过程耗时 779 秒,12分钟左右。两个文件大小分别是 560 多M 和 900 M。最后执行 load data infile 将文件导入到对应的表中,在执行这个命令后可能会出现下面这个错误提示。ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement这是因为 MySQL 自身的安全配置所致,需要更改 my.cnf,在其中加入下面的配置,然后重启服务。secure_file_priv=等于号后边为空表示允许所有目录下的文件 load,如果要限定某个特定目录,在等于号后边填上对应的文件目录即可。然后执行下面的语句,将用户记录导入到 user 表。load data infile '/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_user_txt_500w.txt' replace into table user FIELDS TERMINATED BY ',';500万条耗时 3分32秒。将订单记录导入到 order 表。load data infile '/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_order_txt_500w+.txt' replace into table `order` FIELDS TERMINATED BY ',';749 万条记录,耗时 8分31秒。整个过程加起来 24 分钟左右。最后好了,现在可以愉快的做各种测试和优化了。有同学看完可能要说了,20多分钟好像也不算快啊。因为数据量确实比较大,再有数据复杂度和导入时间也有很大关系,如果你只是导入一列自增id,别说 500 万,1000万都用不了一分钟就完成了。其实还有一点优化空间的,比如说把数据库引擎改成 MYISAM 会更快一些,尤其是对于批量插入的情景,但是插入完成后还要再改回来,也需要耗费一些时间,而且来回切换也比较麻烦。上面的几种方法都配合了 Python 脚本,当然你可以换成自己熟悉的语言,比如 Java,或者直接写 bash 脚本也可以。脚本已经放到了 github 上,需要的同学请自取。地址:点击获取源码
2020年08月08日
334 阅读
0 评论
0 点赞
2020-06-28
那些年被面试官怼的MySQL索引
之前有过一次面试,关于MySQL索引的原理及使用被面试官怼的体无完肤,立志要总结一番,然后一直没有时间(其实是懒……),准备好了吗?索引是什么?数据库索引,是数据库管理系统(DBMS)中一个排序的数据结构,它可以对数据库表中一列或多列的值进行排序,以协助更加快速的访问数据库表中特定的数据。通俗的说,我们可以把数据库索引比做是一本书前面的目录,它能加快数据库的查询速度。为什么需要索引?思考:如何在一个图书馆中找到一本书? 设想一下,假如在图书馆中没有其他辅助手段,只能一条道走到黑,一本书一本书的找,经过3个小时的连续查找,终于找到了你需要看的那本书,但此时天都黑了。为了避免这样的事情,每个图书馆才都配备了一套图书馆管理系统,大家要找书籍的话,先在系统上查找到书籍所在的房屋编号、图书架编号还有书在图书架几层的那个方位,然后就可以直接大摇大摆的去取书了,就可以很快速的找到我们所需要的书籍。索引就是这个原理,它可以帮助我们快速的检索数据。一般的应用系统对数据库的操作,遇到最多、最容易出问题是一些复杂的查询操作,当数据库中数据量很大时,查找数据就会变得很慢,这样就很影响整个应用系统的效率,我们就可以使用索引来提高数据库的查询效率。B-Tree和B+Tree目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构, 我在这里分别讲一下:B-Tree即B树,注意(不是B减树),B树是一种多路搜索树。使用B-Tree结构可以显著减少定位记录时所经历的中间过程,从而加快存取速度。B-Tree有如下一些特征:定义任意非叶子结点最多只有M个子节点,且M>2。根结点的儿子数为[2, M]。除根结点以外的非叶子结点的儿子数为[M/2, M], 向上取整 。每个结点存放至少M/2-1(取上整)和至多M-1个关键字;(至少2个关键字)。非叶子结点的关键字个数=指向儿子的指针个数-1。非叶子结点的关键字:K[1], K[2], …, K[M-1],且K[i] <= K[i+1]。非叶子结点的指针:P[1], P[2], …,P[M](其中P[1]指向关键字小于K[1]的子树,P[M]指向关键字大于K[M-1]的子树,其它P[i]指向关键字属于(K[i-1], K[i])的子树)。所有叶子结点位于同一层。有关b树的一些特性:关键字集合分布在整颗树的所有结点之中;任何一个关键字出现且只出现在一个结点中;搜索有可能在非叶子结点结束;其搜索性能等价于在关键字全集内做一次二分查找。B树的搜索:从根结点开始,对结点内的关键字(有序)序列进行二分查找,如果命中则结束,否则进入查询关键字所属范围的儿子结点;重复执行这个操作,直到所对应的节点指针为空,或者已经是是叶子结点。例如下面一个B树,那么查找元素43的过程如下:根据根节点指针找到18、37所在节点,把此节点读入内存,进行第一次磁盘IO,此时发现43>37,找到指针p3。根据指针p3,找到42、51所在节点,把此节点读入内存,进行第二次磁盘IO,此时发现42<43<51,找到指针p2。根据指针p2,找到43、46所在节点,把此节点读入内存,进行第三次磁盘IO,此时我们就已经查到了元素43。在此过程总共进行了三次磁盘IO。B+TreeB+Tree属于B-Tree的变种。与B-Tree相比,B+Tree有以下不同点:有n棵子树的非叶子结点中含有n个关键字(B树是n-1个),即非叶子结点的子树指针与关键字个数相同。这些关键字不保存数据,只用来索引,所有数据都保存在叶子节点(B树是每个关键字都保存数据)。所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。所有的非叶子结点可以看成是叶子节点的索引部分。同一个数字会在不同节点中重复出现,根节点的最大元素就是b+树的最大元素。相对B树,B+树做索引的优势B+树的磁盘IO代价更低: B+树非叶子节点没有指向数据行的指针,所以相同的磁盘容量存储的节点数更多,相应的IO读写次数肯定减少了。B+树的查询效率更加稳定:由于所有数据都存于叶子节点。所有关键字查询的路径长度相同,每一个数据的查询效率相当。所有的叶子节点形成了一个有序链表,更加便于查找。关于MySQL的两种常用存储引擎MyISAM和InnoDB的索引均以B+树作为数据结构,二者却有不同(这里只说二者索引的区别)。MyISAM索引和Innodb索引的区别MyISAM使用B+树作为索引结构,叶节点叶节点的data域保存的是存储数据的地址,主键索引key值唯一,辅助索引key可以重复,二者在结构上相同。 因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果要找的Key存在,则取出其data域的值,然后以data域的值为地址,去读取相应数据记录 。因此,索引文件和数据文件是分开的,从索引中检索到的是数据的地址,而不是数据。Innodb也是用B+树作为索引结构,但具体实现方式却与MyISAM截然不同,首先,数据表本身就是按照b+树组织,所以数据文件本身就是主键索引文件。叶节点key值为数据表的主键,data域为完整的数据记录,因此InnoDB表数据文件本身就是主键索引(这也就是MyISAM可以允许没有主键,但是Innodb必须有主键的原因)。第二个与MyISAM索引的不同是InnoDB的辅助索引的data域存储相应数据记录的主键值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。索引类型普通索引:(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。唯一索引: 普通索引允许被索引的数据列包含重复的值,而唯一索引不允许,但是可以为null。所以任务是保证访问速度和避免数据出现重复。主键索引:在主键字段创建的索引,一张表只有一个主键索引。组合索引:多列值组成一个索引,专门用于组合搜索。全文索引:对文本的内容进行分词,进行搜索。(MySQL5.6及以后的版本,MyISAM和InnoDB存储引擎均支持全文索引。)索引的使用策略及优缺点使用索引主键自动建立唯一索引。经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引。查询中与其他表关联的字段,外键关系建立索引。经常用于聚合函数的列要建立索引,如min(),max()等的聚合函数。不使用索引经常增删改的列不要建立索引。有大量重复的列不建立索引。表记录太少不要建立索引,因为数据较少,可能查询全部数据花费的时间比遍历索引的时间还要短,索引就可能不会产生优化效果 。最左匹配原则建立联合索引的时候都会默认从最左边开始,所以索引列的顺序很重要,建立索引的时候就应该把最常用的放在左边,使用select的时候也是这样,从最左边的开始,依次匹配右边的。优点可以保证数据库表中每一行的数据的唯一性。可以大大加快数据的索引速度。加速表与表之间的连接。可以显著的减少查询中分组和排序的时间。缺点创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间,如果需要建立聚簇索引,那么需要占用的空间会更大,其实建立索引就是以空间换时间。表中的数据进行增、删、改的时候,索引也要动态的维护,这就降低了维护效率。验证索引是否能够提升查询性能创建测试表index_test使用python脚本程序通过pymsql模块,向表中添加十万条数据import pymysql def main(): # 创建Connection连接 conn = pymysql.connect(host='localhost', port=3306, database='db_test', user='root', password='deepin', charset='utf8') # 获得Cursor对象 cursor = conn.cursor() # 插入10万次数据 for i in range(100000): cursor.execute("insert into index_test values('haha-%d')" % i) # 提交数据 conn.commit() if __name__ == "__main__": main()在mysql终端开启运行时间监测:set profiling=1;查找第1万条数据ha-99999select * from index_test where name='haha-99999';查看执行的时间:show profiles;为表index_test的name列创建索引:create index name_index on index_test(name(10));再次执行查询语句、查看执行的时间:可以看出合适的索引确实可以明显提高某些字段的查询效率。
2020年06月28日
80 阅读
0 评论
0 点赞