【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
一、概述
数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制,所以MySQL自然也不能例外。MySQL数据库由于其自身架构的特点,存在多种数据存储引擎,每种存储引擎所针对的应用场景特点都不太一样,为了满足各自特定应用场景的需求,每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计,所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定,行级锁定和页级锁定。
1.表级锁定（table-level） 表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。 当然,锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,致使并大度大打折扣。 使用表级锁定的主要是MyISAM,MEMORY,CSV等一些非事务性存储引擎。
2.行级锁定（row-level） 行级锁定最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。 虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁。 使用行级锁定的主要是InnoDB存储引擎。
3.页级锁定（page-level） 页级锁定是MySQL中比较独特的一种锁定级别,在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力也同样是介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。 在数据库实现资源锁定的过程中,随着锁定资源颗粒度的减小,锁定相同数据量的数据所需要消耗的内存数量是越来越多的,实现算法也会越来越复杂。不过,随着锁定资源颗粒度的减小,应用程序的访问请求遇到锁等待的可能性也会随之降低,系统整体并发度也随之提升。 使用页级锁定的主要是BerkeleyDB存储引擎。 总的来说,MySQL这3种锁的特性可大致归纳如下： 表级锁：开销小,加锁快；不会出现死锁；锁定粒度大,发生锁冲突的概率最高,并发度最低； 行级锁：开销大,加锁慢；会出现死锁；锁定粒度最小,发生锁冲突的概率最低,并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间,并发度一般。 适用：从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理（OLTP）系统。
二、表级锁定
由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现,所以下面我们将以MyISAM存储引擎作为示例存储引擎。 1.MySQL表级锁的锁模式 MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性： 对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求； 对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作； MyISAM表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。
2.如何加表锁 MyISAM在执行查询语句（SELECT）前,会自动给涉及的所有表加读锁,在执行更新操作（UPDATE、DELETE、INSERT等）前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
3.MyISAM表锁优化建议 对于MyISAM存储引擎,虽然使用表级锁定在锁定实现的过程中比实现行级锁定或者页级锁所带来的附加成本都要小,锁定本身所消耗的资源也是最少。但是由于锁定的颗粒度比较到,所以造成锁定资源的争用情况也会比其他的锁定级别都要多,从而在较大程度上会降低并发处理能力。所以,在优化MyISAM存储引擎锁定问题的时候,最关键的就是如何让其提高并发度。由于锁定级别是不可能改变的了,所以我们首先需要尽可能让锁定的时间变短,然后就是让可能并发进行的操作尽可能的并发。
（1）查询表级锁争用情况 MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况： mysql> show status like 'table_locks_%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 3320 | | Table_locks_waited | 0 | +-----------------------+-------+
这里有两个状态变量记录MySQL内部表级锁定的情况,两个变量说明如下： Table_locks_immediate：产生表级锁定的次数； Table_locks_waited：出现表级锁定争用而发生等待的次数；
两个状态值都是从系统启动后开始记录,出现一次对应的事件则数量加1。如果这里的Table_locks_waited状态值比较高,那么说明系统中表级锁定争用现象比较严重,就需要进一步分析为什么会有较多的锁定资源争用了。
（2）缩短锁定时间 如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query执行时间尽可能的短。 a)尽两减少大的复杂Query,将复杂Query分拆成几个小的Query分布进行； b)尽可能的建立足够高效的索引,让数据检索更迅速； c)尽量让MyISAM存储引擎的表只存放必要的信息,控制字段类型； d)利用合适的机会优化MyISAM表数据文件。
（3）分离能并行的操作 说到MyISAM的表锁,而且是读写互相阻塞的表锁,可能有些人会认为在MyISAM存储引擎的表上就只能是完全的串行化,没办法再并行了。大家不要忘记了,MyISAM的存储引擎还有一个非常有用的特性,那就是ConcurrentInsert（并发插入）的特性。 MyISAM存储引擎有一个控制是否打开Concurrent Insert功能的参数选项：concurrent_insert,可以设置为0,1或者2。三个值的具体说明如下： concurrent_insert=2,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录； concurrent_insert=1,如果MyISAM表中没有空洞（即表的中间没有被删除的行）,MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置； concurrent_insert=0,不允许并发插入。 可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。例如,将concurrent_insert系统变量设为2,总是允许并发插入；同时,通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。
（4）合理利用读写优先级 MyISAM存储引擎的是读写互相阻塞的,那么,一个进程请求某个MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢？ 答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前。 这是因为MySQL的表级锁定对于读和写是有不同优先级设定的,默认情况下是写优先级要大于读优先级。 所以,如果我们可以根据各自系统环境的差异决定读与写的优先级： 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接读比写的优先级高。如果我们的系统是一个以读为主,可以设置此参数,如果以写为主,则不用设置； 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。 虽然上面方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用（如用户登录系统）中,读锁等待严重的问题。 另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。 这里还要强调一点：一些需要长时间运行的查询操作,也会使写进程“饿死”,因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。
三、行级锁定
行级锁定不是MySQL自己实现的锁定方式,而是由其他存储引擎自己所实现的,如广为大家所知的InnoDB存储引擎,以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。考虑到行级锁定君由各个存储引擎自行实现,而且具体实现也各有差别,而InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎,所以这里我们就主要分析一下InnoDB的锁定特性。
1.InnoDB锁定模式及实现机制 考虑到行级锁定君由各个存储引擎自行实现,而且具体实现也各有差别,而InnoDB是目前事务型存储引擎中使用最为广泛的存储引擎,所以这里我们就主要分析一下InnoDB的锁定特性。 总的来说,InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁定同样分为两种类型,共享锁和排他锁,而在锁定机制的实现过程中为了让行级锁定和表级锁定共存,InnoDB也同样使用了意向锁（表级锁定）的概念,也就有了意向共享锁和意向排他锁这两种。 当一个事务需要给自己需要的某个资源加锁的时候,如果遇到一个共享锁正锁定着自己需要的资源的时候,自己可以再加一个共享锁,不过不能加排他锁。但是,如果遇到自己需要锁定的资源已经被一个排他锁占有之后,则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候,如果遇到自己需要的资源已经被排他锁占用的时候,该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁,那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话,则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个,但是意向排他锁同时只能有一个存在。所以,可以说InnoDB的锁定模式实际上可以分为四种： 共享锁（S）,排他锁（X）,意向共享锁（IS）和意向排他锁（IX） ,我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
共享锁（S） 排他锁 （X） 意向共享锁（IS） 意向排他锁（IX）
共享锁（S）
排他锁 （X） | 意向共享锁（IS） | 意向排他锁（IX）
兼容 | 冲突 | 兼容 | 冲突
冲突 | 冲突 | 冲突 | 冲突
兼容 | 冲突 | 兼容 | 兼容
冲突 | 冲突 | 兼容 | 兼容
如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务；反之,如果两者不兼容,该事务就要等待锁释放。 意向锁是InnoDB自动加的,不需用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句,InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。 -- 共享锁（S）： SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE -- 排他锁（X)： SELECT * FROM table_name WHERE ... FOR UPDATE
用SELECT ... IN SHARE MODE获得共享锁,主要用在需要数据依存关系时来确认某行记录是否存在,并确保没有人对这个记录进行UPDATE或者DELETE操作。 但是如果当前事务也需要对该记录进行更新操作,则很有可能造成死锁,对于锁定行记录后需要进行更新操作的应用,应该使用SELECT... FOR UPDATE方式获得排他锁。
2.InnoDB行锁实现方式 InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁 在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。下面通过一些实际例子来加以说明。 （1）在不通过索引条件查询的时候,InnoDB确实使用的是表锁,而不是行锁。 （2）由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。 （3）当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。 （4）即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
3.间隙锁（Next-Key锁） 当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁； 对于键值在条件范围内但并不存在的记录,叫做“间隙（GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁（Next-Key锁）。 例： 假如emp表中只有101条记录,其empid的值分别是 1,2,...,100,101,下面的SQL： select * from emp where empid > 100 for update;
是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101（这些记录并不存在）的“间隙”加锁。 InnoDB使用间隙锁的目的： （1）防止幻读,以满足相关隔离级别的要求。对于上面的例子,要是不使用间隙锁,如果其他事务插入了empid大于100的任何记录,那么本事务如果再次执行上述语句,就会发生幻读； （2）为了满足其恢复和复制的需要。
很显然,在使用范围条件检索并锁定记录时,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。 除了间隙锁给InnoDB带来性能的负面影响之外,通过索引实现锁定的方式还存在其他几个较大的性能隐患： （1）当Query无法利用索引的时候,InnoDB会放弃使用行级别锁定而改用表级别的锁定,造成并发性能的降低； （2）当Query使用的索引并不包含所有过滤条件的时候,数据检索使用到的索引键所只想的数据可能有部分并不属于该Query的结果集的行列,但是也会被锁定,因为间隙锁锁定的是一个范围,而不是具体的索引键； （3）当Query在使用索引定位数据的时候,如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分）,一样会被锁定。 因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。 还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。
4.死锁 上文讲过,MyISAM表锁是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,当两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。 在InnoDB的事务管理和锁定机制中,有专门检测死锁的机制,会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后,InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚,而让另外一个较大的事务成功完成。 那InnoDB是以什么来为标准判定事务的大小的呢？MySQL官方手册中也提到了这个问题,实际上在InnoDB发现死锁之后,会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多,在死锁中就越不会被回滚掉。 但是有一点需要注意的就是,当产生死锁的场景中涉及到不止InnoDB存储引擎的时候,InnoDB是没办法检测到该死锁的,这时候就只能通过锁定超时限制参数InnoDB_lock_wait_timeout来解决。 需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。 通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以及访问数据库的SQL语句,绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法： （1）在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。 （2）在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。 （3）在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。 （4）在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题。 （5）当隔离级别为READ COMMITTED时,如果两个线程都先执行SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁。这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁。
5.什么时候使用表锁 对于InnoDB表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中,也可以考虑使用表级锁： （1）事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。 （2）事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。 当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM表了。 在InnoDB下,使用表锁要注意以下两点。 （1）使用LOCK TABLES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层──MySQL Server负责的,仅当autocommit=0、InnoDB_table_locks=1（默认设置）时,InnoDB层才能知道MySQL加的表锁,MySQL Server也才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁,否则,InnoDB将无法自动检测并处理这种死锁。 （2）在用 LOCK TABLES对InnoDB表加锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁；事务结束前,不要用UNLOCK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁。正确的方式见如下语句： 例如,如果需要写表t1并从表t读,可以按如下做： SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;
6.InnoDB行锁优化建议 InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是,InnoDB的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差。 （1）要想合理利用InnoDB的行级锁定,做到扬长避短,我们必须做好以下工作： a)尽可能让所有的数据检索都通过索引来完成,从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定； b)合理设计索引,让InnoDB在索引键上面加锁的时候尽可能准确,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他Query的执行； c)尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录； d)尽量控制事务的大小,减少锁定的资源量和锁定时间长度； e)在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少MySQL因为实现事务隔离级别所带来的附加成本。 （2）由于InnoDB的行级锁定和事务性,所以肯定会产生死锁,下面是一些比较常用的减少死锁产生概率的小建议： a)类似业务模块中,尽可能按照相同的访问顺序来访问,防止产生死锁； b)在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率； c)对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率。 （3）可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况： mysql> show status like 'InnoDB_row_lock%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 58508 | | Innodb_row_lock_time_avg | 14627 | | Innodb_row_lock_time_max | 25679 | | Innodb_row_lock_waits | 4 | +-------------------------------+-------+
InnoDB 的行级锁定状态变量不仅记录了锁定等待次数,还记录了锁定总时长,每次平均时长,以及最大时长,此外还有一个非累积状态量显示了当前正在等待锁定的等待数量。对各个状态量的说明如下： InnoDB_row_lock_current_waits：当前正在等待锁定的数量； InnoDB_row_lock_time：从系统启动到现在锁定总时间长度； InnoDB_row_lock_time_avg：每次等待所花平均时间； InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间； InnoDB_row_lock_waits：系统启动后到现在总共等待的次数； 对于这5个状态变量,比较重要的主要是InnoDB_row_lock_time_avg（等待平均时长）,InnoDB_row_lock_waits（等待总次数）以及InnoDB_row_lock_time（等待总时长）这三项。尤其是当等待次数很高,而且每次等待时长也不小的时候,我们就需要分析系统中为什么会有如此多的等待,然后根据分析结果着手指定优化计划。 如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,还可以通过设置InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。 锁冲突的表、数据行等,并分析锁争用的原因。具体方法如下： create table InnoDB_monitor(a INT) engine=InnoDB;
然后就可以用下面的语句来进行查看： show engine InnoDB status;
监视器可以通过发出下列语句来停止查看： drop table InnoDB_monitor;
设置监视器后,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等,便于进行进一步的分析和问题的确定。可能会有读者朋友问为什么要先创建一个叫InnoDB_monitor的表呢？因为创建该表实际上就是告诉InnoDB我们开始要监控他的细节状态了,然后InnoDB就会将比较详细的事务以及锁定信息记录进入MySQL的errorlog中,以便我们后面做进一步分析使用。打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.err文件变得非常的巨大,所以用户在确认问题原因之后,要记得删除监控表以关闭监视器,或者通过使用“--console”选项来启动服务器以关闭写日志文件。 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
导读 ： 作者：高鹏（网名八怪）,《深入理解MySQL主从原理32讲》系列文的作者。
能力有限有误请指出。
本文使用源码版本：5.7.22
引擎为：Innodb
排序（filesort）作为DBA绕不开的话题,也经常有朋友讨论它,比如常见的问题如下： 排序的时候,用于排序的数据会不会如Innodb一样压缩空字符存储,比如varchar(30),我只是存储了1个字符是否会压缩,还是按照30个字符计算？ max_length_for_sort_data/max_sort_length 到底是什么含义？ original filesort algorithm（回表排序） 和 modified filesort algorithm（不回表排序） 的根本区别是什么？ 为什么使用到排序的时候慢查询中的Rows_examined会更大,计算方式到底是什么样的？
在MySQL通常有如下算法来完成排序： 内存排序（优先队列 order by limit 返回少量行常用,提高排序效率,但是注意order by limit n,m 如果n过大可能会涉及到排序算法的切换） 内存排序（快速排序） 外部排序（归并排序）
但是由于能力有限本文不解释这些算法,并且本文不考虑优先队列算法的分支逻辑,只以快速排序和归并排序作为基础进行流程剖析。我们在执行计划中如果出现filesort字样通常代表使用到了排序,但是执行计划中看不出来下面问题： 是否使用了临时文件。 是否使用了优先队列。 是original filesort algorithm（回表排序）还是modified filesort algorithm（不回表排序）。
如何查看将在后面进行描述。本文还会给出大量的排序接口供感兴趣的朋友使用,也给自己留下笔记。
一、从一个问题出发
这是最近一个朋友遇到的案例,大概意思就是说我的表在Innodb中只有30G左右,为什么使用如下语句进行排序操作后临时文件居然达到了200多G,当然语句很变态,我们可以先不问为什么会有这样的语句,我们只需要研究原理即可,在本文的第13节会进行原因解释和问题重现。
临时文件如下：
下面是这些案例信息： show create table t\G *************************** 1. row *************************** Table: t CreateTable: CREATE TABLE `t`(` `ID` bigint(20) NOT NULL COMMENT 'ID',` `UNLOAD_TASK_NO` varchar(50) NOT NULL ,` `FORKLIFT_TICKETS_COUNT` bigint(20) DEFAULT NULL COMMENT '叉车票数',` `MANAGE_STATUS` varchar(20) DEFAULT NULL COMMENT '管理状态',` `TRAY_BINDING_TASK_NO` varchar(50) NOT NULL ,` `STATISTIC_STATUS` varchar(50) NOT NULL ,` `CREATE_NO` varchar(50) DEFAULT NULL ,` `UPDATE_NO` varchar(50) DEFAULT NULL ,` `CREATE_NAME` varchar(200) DEFAULT NULL COMMENT '创建人名称',` `UPDATE_NAME` varchar(200) DEFAULT NULL COMMENT '更新人名称',` `CREATE_ORG_CODE` varchar(200) DEFAULT NULL COMMENT '创建组织编号',` `UPDATE_ORG_CODE` varchar(200) DEFAULT NULL COMMENT '更新组织编号',` `CREATE_ORG_NAME` varchar(1000) DEFAULT NULL COMMENT '创建组织名称',` `UPDATE_ORG_NAME` varchar(1000) DEFAULT NULL COMMENT '更新组织名称',` `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',` `UPDATE_TIME` datetime DEFAULT NULL COMMENT '更新时间',` `DATA_STATUS` varchar(50) DEFAULT NULL COMMENT '数据状态',` `OPERATION_DEVICE` varchar(200) DEFAULT NULL COMMENT '操作设备',` `OPERATION_DEVICE_CODE` varchar(200) DEFAULT NULL COMMENT '操作设备编码',` `OPERATION_CODE` varchar(50) DEFAULT NULL COMMENT '操作码',` `OPERATION_ASSIST_CODE` varchar(50) DEFAULT NULL COMMENT '辅助操作码',` `CONTROL_STATUS` varchar(50) DEFAULT NULL COMMENT '控制状态',` `OPERATOR_NO` varchar(50) DEFAULT NULL COMMENT '操作人工号',` `OPERATOR_NAME` varchar(200) DEFAULT NULL COMMENT '操作人名称',` `OPERATION_ORG_CODE` varchar(50) DEFAULT NULL COMMENT '操作部门编号',` `OPERATION_ORG_NAME` varchar(200) DEFAULT NULL COMMENT '操作部门名称',` `OPERATION_TIME` datetime DEFAULT NULL COMMENT '操作时间',` `OPERATOR_DEPT_NO` varchar(50) NOT NULL COMMENT '操作人所属部门编号',` `OPERATOR_DEPT_NAME` varchar(200) NOT NULL COMMENT '操作人所属部门名称',` `FORKLIFT_DRIVER_NAME` varchar(200) DEFAULT NULL ,` `FORKLIFT_DRIVER_NO` varchar(50) DEFAULT NULL ,` `FORKLIFT_DRIVER_DEPT_NAME` varchar(200) DEFAULT NULL ,` `FORKLIFT_DRIVER_DEPT_NO` varchar(50) DEFAULT NULL ,` `FORKLIFT_SCAN_TIME` datetime DEFAULT NULL ,` `OUT_FIELD_CODE` varchar(200) DEFAULT NULL,` PRIMARY KEY (`ID`),` KEY `IDX_TRAY_BINDING_TASK_NO`(`TRAY_BINDING_TASK_NO`),` KEY `IDX_OPERATION_ORG_CODE`(`OPERATION_ORG_CODE`),` KEY `IDX_OPERATION_TIME`(`OPERATION_TIME`)` ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 desc SELECT ID, UNLOAD_TASK_NO, FORKLIFT_TICKETS_COUNT, MANAGE_STATUS, TRAY_BINDING_TASK_NO, STATISTIC_STATUS, CREATE_NO, UPDATE_NO, CREATE_NAME, UPDATE_NAME, CREATE_ORG_CODE, UPDATE_ORG_CODE, CREATE_ORG_NAME, UPDATE_ORG_NAME, CREATE_TIME, UPDATE_TIME, DATA_STATUS, OPERATION_DEVICE, OPERATION_DEVICE_CODE, OPERATION_CODE, OPERATION_ASSIST_CODE, CONTROL_STATUS, OPERATOR_NO, OPERATOR_NAME, OPERATION_ORG_CODE, OPERATION_ORG_NAME, OPERATION_TIME, OPERATOR_DEPT_NO, OPERATOR_DEPT_NAME, FORKLIFT_DRIVER_NAME, FORKLIFT_DRIVER_NO, FORKLIFT_DRIVER_DEPT_NAME, FORKLIFT_DRIVER_DEPT_NO, FORKLIFT_SCAN_TIME, OUT_FIELD_CODE FROM t GROUP BY id , UNLOAD_TASK_NO , FORKLIFT_TICKETS_COUNT , MANAGE_STATUS , TRAY_BINDING_TASK_NO , STATISTIC_STATUS , CREATE_NO , UPDATE_NO , CREATE_NAME , UPDATE_NAME , CREATE_ORG_CODE , UPDATE_ORG_CODE , CREATE_ORG_NAME , UPDATE_ORG_NAME , CREATE_TIME , UPDATE_TIME , DATA_STATUS , OPERATION_DEVICE , OPERATION_DEVICE_CODE , OPERATION_CODE , OPERATION_ASSIST_CODE , CONTROL_STATUS , OPERATOR_NO , OPERATOR_NAME , OPERATION_ORG_CODE , OPERATION_ORG_NAME , OPERATION_TIME , OPERATOR_DEPT_NO , OPERATOR_DEPT_NAME , FORKLIFT_DRIVER_NAME , FORKLIFT_DRIVER_NO , FORKLIFT_DRIVER_DEPT_NAME , FORKLIFT_DRIVER_DEPT_NO , FORKLIFT_SCAN_TIME , OUT_FIELD_CODE; +----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ | 1| SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 5381145| 100.00| Using filesort | +----+-------------+-------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------+ 1 row inset, 1 warning (0.00 sec)
也许你会怀疑这个语句有什么用,我们先不考虑功能,我们只考虑为什么它会生成200G的临时文件这个问题。
接下来我将分阶段进行排序的流程解析,注意了整个排序的流程均处于状态**‘Creating sort index’**下面,我们以filesort函数接口为开始进行分析。
二、测试案例
为了更好的说明后面的流程我们使用2个除了字段长度不同,其他完全一样的表来说明,但是需要注意这两个表数据量很少,不会出现外部排序,如果涉及外部排序的时候我们需要假设它们数据量很大。其次这里根据original filesort algorithm和modified filesort algorithm进行划分,但是这两种方法还没讲述,不用太多理会。 original filesort algorithm（回表排序） mysql> show create table tests1 \G *************************** 1. row *************************** Table: tests1 CreateTable: CREATE TABLE `tests1`( `a1` varchar(300) DEFAULT NULL, `a2` varchar(300) DEFAULT NULL, `a3` varchar(300) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row inset(0.00 sec) mysql> select* from tests1; +------+------+------+ | a1 | a2 | a3 | +------+------+------+ | a | a | a | | a | b | b | | a | c | c | | b | d | d | | b | e | e | | b | f | f | | c | g | g | | c | h | h | +------+------+------+ 8 rows inset(0.00 sec) mysql> desc select* from tests1 where a1='b' order by a2,a3; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1| SIMPLE | tests1 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row inset, 1 warning (0.00 sec) modified filesort algorithm（不回表排序） mysql> desc select* from tests2 where a1='b' order by a2,a3; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row inset, 1 warning (0.00 sec) mysql> show create table tests2 \G *************************** 1. row *************************** Table: tests2 CreateTable: CREATE TABLE `tests2`( `a1` varchar(20) DEFAULT NULL, `a2` varchar(20) DEFAULT NULL, `a3` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row inset(0.00 sec) mysql> select* from tests2; +------+------+------+ | a1 | a2 | a3 | +------+------+------+ | a | a | a | | a | b | b | | a | c | c | | b | d | d | | b | e | e | | b | f | f | | c | g | g | | c | h | h | +------+------+------+ 8 rows inset(0.00 sec) mysql> desc select* from tests2 where a1='b' order by a2,a3; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row inset, 1 warning (0.01 sec)
整个流程我们从 filesort 函数接口开始讨论。下面第3到第10节为排序的主要流程。
三、阶段1:确认排序字段及顺序
这里主要将排序顺序存入到Filesort 类的 sortorder中,比如我们例子中的order by a2,a3就是a2和a3列,主要接口为Filesort::make_sortorder,我们按照源码描述为sort字段（源码中为sort_length）,显然我们在排序的时候除了sort字段以外,还应该包含额外的字段,到底包含哪些字段就与方法 original filesort algorithm（回表排序） 和 modified filesort algorithm（不回表排序）有关了,下面进行讨论。
四、阶段2:计算sort字段的长度
这里主要调用使用 sortlength 函数,这一步将会带入 max_sort_length 参数的设置进行判断,默认情况下max_sort_length为1024字节。
这一步大概步骤为：
1、循环每一个sort字段
2、计算每一个sort字段的长度：公式为 ≈ 定义长度 * 2
比如这里例子中我定义了a1 varchar(300),那么它的计算长度 ≈ 300 * 2（600）,为什么是*2呢,这应该是和Unicode编码有关,这一步可以参考函数my_strnxfrmlen_utf8。同时需要注意这里是约等于,因为源码中还是其他的考虑,比如字符是否为空,但是占用不多不考虑了。
3、带入max_sort_length参数进行计算
好了有了上面一个sort字段的长度,那么这里就和max_sort_length进行比较,如果这个这个sort字段大于max_sort_length的值,那么以max_sort_length设置为准,这步代码如下： set_if_smaller(sortorder->length, thd->variables.max_sort_length);
因此,如果sort字段的某个字段的超过了max_sort_length设置,那么排序可能不那么精确了。
到了这里每个sort字段的长度以及sort字段的总长度已经计算出来,比如前面给的两个不同列子中： （a2 varchar(300) a3 varchar(300) order by a2,a3）：每个sort字段约为300*2字节,两个字段的总长度约为1200字节。 （a2 varchar(20) a3 varchar(20) order by a2,a3）：每个sort字段约为20*2字节,两个字段的总长度约为80字节。
并且值得注意的是,这里是按照定义大小,如varchar(300) ,以300个字符来计算长度的,而不是我们通常看到的Innodb中实际占用的字符数量。这是排序使用空间大于Innodb实际数据文件大小的一个原因。
下面我们以（a2 varchar(300) a3 varchar(300) order by a2,a3）为例实际看看debug的结果如下： (gdb) p sortorder->field->field_name $4 = 0x7ffe7800fadf"a3" (gdb) p sortorder->length $5 = 600 (gdb) p total_length $6 = 1202（这里a2,a3 可以为NULL各自加了1个字节） (gdb)
可以看出没有问题。
4、循环结束,计算出sort字段的总长度。
后面我们会看到 sort字段不能使用压缩（pack）技术 。
五、阶段3:计算额外字段的空间
对于排序而言,我们很清楚除了 sort字段以外 ,通常我们需要的是实际的数据,那么无外乎两种方式如下： original filesort algorithm：只存储rowid或者主键做为额外的字段,然后进行回表抽取数据。我们按照源码的描述,将这种关联回表的字段叫做 ref字段 （源码中变量叫做ref_length）。 modified filesort algorithm：将处于read_set（需要读取的字段）全部放到额外字段中,这样不需要回表读取数据了。我们按照源码的描述,将这些额外存储的字段叫做 addon字段 （源码中变量叫做addon_length）。
这里一步就是要来判断到底使用那种算法,其主要标准就是参数max_length_for_sort_data,其默认大小为1024字节,但是后面会看到这里的计算为（ sort字段长度+addon字段 的总和）是否超过了max_length_for_sort_data。其次如果使用了modified filesort algorithm算法,那么将会对 addon字段的每个字段做一个pack（打包） ,主要目的在于压缩那些为空的字节,节省空间。
这一步的主要入口函数为 Filesort::get_addon_fields 下面是步骤解析。
1、循环本表全部字段
2、根据read_set过滤出不需要存储的字段
这里如果不需要访问到的字段自然不会包含在其中,下面这段源码过滤代码： if(!bitmap_is_set(read_set, field->field_index)) //是否在read set中 continue;
3、获取字段的长度
这里就是实际的长度了比如我们的a1 varchar(300),且字符集为UTF8,那么其长度≈ 300*3 （900）。
4、获取可以pack（打包）字段的长度
和上面不同,对于int这些固定长度类型的字段,只有可变长度的类型的字段才需要进行打包技术。
5、循环结束,获取addon字段的总长度,获取可以pack（打包）字段的总长度
循环结束后可以获取addon字段的总长度,但是需要注意addon字段和sort字段可能包含重复的字段,比如例2中sort字段为a2、a3,addon字段为a1、a2、a3。
如果满足如下条件： addon字段的总长度+sort字段的总长度 > max_length_for_sort_data
那么将使用original filesort algorithm（回表排序）的方式,否则使用modified filesort algorithm的方式进行。下面是这一句代码： if(total_length + sortlength > max_length_for_sort_data) //如果长度大于了max_length_for_sort_data 则退出了 { DBUG_ASSERT(addon_fields == NULL); return NULL; //返回NULL值 不打包了 使用 original filesort algorithm（回表排序） }
我们在回到第2节例子中的第1个案例,因为我们对a1,a2,a3都是需要访问的,且他们的大小均为varchar(300) UTF8,那么addon字段长度大约为300 * 3 * 3=2700字节 ,其次我们前面计算了sort字段大约为1202字节,因此 2700+1202 是远远大于max_length_for_sort_data的默认设置1024字节的,因此会使用original filesort algorithm方式进行排序。
如果是第2节例子中的第2个案例呢,显然要小很多了（每个字段varchar（20））,大约就是20 * 3 * 3（addon字段）+82（sort字段） 它是小于1024字节的,因此会使用modified filesort algorithm的排序方式,并且这些addon字段基本都可以使用打包（pack）技术,来节省空间。 但是需要注意的是无论如何（sort字段）是不能进行打包（pack）的 ,而固定长度类型不需要打包（pack）压缩空间。
六、阶段4:确认每行的长度
有了上面的就计算后每一行的长度（如果可以打包是打包前的长度）,下面就是这个计算过程。 if(using_addon_fields()) //如果使用了 打包技术 检测 addon_fields 数组是否存在 使用modified filesort algorithm算法 不回表排序 { res_length= addon_length; //总的长度 3个 varchar(300) uft8 为 3*300*3 } else//使用original filesort algorithm算法 { res_length= ref_length; //rowid(主键长度) /* The reference to the record is considered as an additional sorted field */ sort_length+= ref_length; //实际上就是rowid(主键) +排序字段长度 回表排序 } /* Add hash at the end of sort key to order cut values correctly. Needed for GROUPing, rather than for ORDERing. */ if(use_hash) sort_length+= sizeof(ulonglong); rec_length= sort_length + addon_length; //modified filesort algorithm sort_length 为排序键长度 addon_lenth 为访问字段长度,original filesort algorithm rowid(主键) +排序字段长度 ,因为addon_length为0
好了我们稍微总结一下： original filesort algorithm：每行长度为 sort字段 的总长度+ ref字段 长度（主键或者rowid）。 modified filesort algorithm：每行的长度为 sort字段 的总长度+ addon字段 的长度（需要访问的字段总长度）。
当然到底使用那种算法参考上一节。但是要注意了对于varchar这种可变长度是以定义的大小为准了,比如UTF8 varchar（300）就是300*3= 900 而不是实际存储的大小,而固定长度没有变化。好了,还是回头看看第2节的两个例子,分别计算它们的行长度： 例子1：根据我们的计算,它将使用 original filesort algorithm 排序方式,最终的计算行长度应该为（sort字段长度+rowid长度）及 ≈ 1202+6 字节,下面是debug的结果： (gdb) p rec_length $1 = 1208 例子2：根据我们的计算,它将使用 modified filesort algorithm 排序方式,最终计算行长度应该为（sort字段长度+addon字段长度）及 ≈ 82 + 20 * 3 * 3 （结果为262）,注意这里是约等于没有计算非空等因素和可变长度因素,下面是debug的结果： (gdb) p rec_length $2 = 266
可以看出误差不大。
七、阶段5:确认最大内存分配
这里的分配内存就和参数sort_buffer_size大小有关了。但是是不是每次都会分配至少sort_buffer_size大小的内存的呢？其实不是,MySQL会判断是否表很小的情况,也就是做一个简单的运算,目的在于节省内存的开销,这里我们将来描述。
1、大概计算出Innodb层主键叶子结点的行数
这一步主要通过（聚集索引叶子结点的空间大小/聚集索引每行大小 * 2）计算出一个行的上限,调入函数ha_innobase::estimate_rows_upper_bound,源码如下： num_rows= table->file->estimate_rows_upper_bound(); //上限来自于Innodb 叶子聚集索引叶子结点/聚集索引长度 *2
然后将结果存储起来,如果表很小那么这个值会非常小。
2、根据前面计算的每行长度计算出sort buffer可以容下的最大行数
这一步将计算sort buffer可以容纳的最大行数如下： ha_rows keys= memory_available / (param.rec_length + sizeof(char*)); //可以排序的 行数 sort buffer 中最大 可以排序的行数
3、对比两者的最小值,作为分配内存的标准
然后对比两个值以小值为准,如下： param.max_keys_per_buffer= (uint) min(num_rows > 0? num_rows : 1, keys); //存储行数上限 和 可以排序 行数的 小值
4、根据结果分配内存
分配如下： table_sort.alloc_sort_buffer(param.max_keys_per_buffer, param.rec_length);
也就是根据总的 计算出的行长度 和 计算出的行数 进行分配。
八、阶段6:读取数据,进行内存排序
到这里准备工作已经完成了,接下就是以行为单位读取数据了,然后对过滤掉where条件的剩下的数据进行排序。如果需要排序的数据很多,那么等排序内存写满后会进行内存排序,然后将排序的内容写入到排序临时文件中,等待下一步做外部的归并排序。
作为归并排序而言,每一个归并的文件片段必须是排序好的,否则归并排序是不能完成的,因此写满排序内存后需要做内存排序。如果写不满呢,那么做一次内存排序就好了。下面我们来看看这个过程,整个过程集中在 find_all_keys 函数中。
1、读取需要的数据
实际上在这一步之前还会做read_set的更改,因为对于original filesort algorithm（回表排序）的算法来讲不会读取全部需要的字段,为了简单起见不做描述了。这一步就是读取一行数据了,这里会进入Innodb层读取数据,具体流程不做解释了,下面是这一行代码： error= file->ha_rnd_next(sort_form->record[0]); //读取一行数据
2、将Rows_examined 加1
这里这个指标对应的就是慢查中的Rows_examined了,这个指标在有排序的情况下会出现重复计算的情况,但是这里还是正确的,重复的部分后面再说。
3、过滤掉where条件
这里将会过滤掉where条件中不满足条件的行,代码如下： if(!error && !qep_tab->skip_record(thd, &skip_record) && !skip_record) //这里做where过滤条件 的比较
**4、将行数据写入到sort buffer中 **
这一步将会把数据写入到sort buffer中,需要注意这里不涉及排序操作,只是存储数据到内存中。其中分为了2部分： 写入sort字段。如果是 original filesort algorithm 那么rowid（主键）也包含在其中了。 写入addon字段,这是 modified filesort algorithm 才会有的,在写入之前还会调用Field::pack对可以打包（pack）的字段进行压缩操作。对于varchar字段的打包函数就是 Field_varstring::pack ,简单的说存储的是实际的大小,而非定义的大小。
整个过程位于 find_all_keys->Sort_param::make_sortkey 函数中。这一步还涉及到了我们非常关心的一个问题, 到底排序的数据如何存储的问题 ,需要仔细阅读。下面我们就debug一下第2节中两个例子的不同存储方式。
既然要去看内存中的数据,我们只要看它最终拷贝的内存数据是什么就好了,那么真相将会大白,我们只需要将断点放到find_all_keys函数上,做完一行数据的Sort_param::make_sortkey操作后看内存就行了,如下： 例子1（字段都是varchar（300））：它将使用**original filesort algorithm（回表排序）**的方式,最终应该存储的是sort字段（a2,a3）+rowid。排序的结果如下： mysql> select* from test.tests1 where a1='b' order by a2,a3; +------+------+------+ | a1 | a2 | a3 | +------+------+------+ | b | d | d | | b | e | e | | b | f | f | +------+------+------+ 3 rows inset(9.06 sec) 我们以第二行为查看目标
由于篇幅的关系,我展示其中的一部分,因为这里大约有1200多个字节,如下： (gdb) x/1300bx start_of_rec 0x7ffe7ca79998: 0x01 0x00 0x45 0x00 0x20 0x00 0x20 0x00 0x7ffe7ca799a0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7ca799a8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7ca799b0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7ca799b8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7ca799c0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7ca799c8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 ... 这后面还有大量的 0X20 0X00
我们看到了大量的0X20 0X00,这正是占位符号,实际有用的数据也就只有0x45 0x00这两个字节了,而0x45正是我们的大写字母E,也就是数据中的e,这和比较字符集有关。这里的0X20 0X00占用了大量的空间,我们最初计算sort 字段大约为1200字节,实际上只有少量的几个字节有用。
这里对于sort字段而言,比实际存储的数据大得多。 例子2（字段都是varchar（20））：它将使用modified filesort algorithm,最终应该存储的是sort字段（a2,a3）+addon字段（需要的字段,这里就是a1,a2,a3）
排序的结果如下： mysql> select* from test.tests2 where a1='b' order by a2,a3; +------+------+------+ | a1 | a2 | a3 | +------+------+------+ | b | d | d | | b | e | e | | b | f | f | +------+------+------+ 我们以第一行为查看目标
这里数据不大,通过压缩后只有91个字节了,我们整体查看如下： (gdb) p rec_sz $6 = 91 gdb) x/91x start_of_rec 0x7ffe7c991bc0: 0x01 0x00 0x44 0x00 0x20 0x00 0x20 0x00 0x7ffe7c991bc8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7c991bd0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7c991bd8: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7c991be0: 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x7ffe7c991be8: 0x20 0x01 0x00 0x44 0x00 0x20 0x00 0x20 0x7ffe7c991bf0: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x7ffe7c991bf8: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x7ffe7c991c00: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x7ffe7c991c08: 0x00 0x20 0x00 0x20 0x00 0x20 0x00 0x20 0x7ffe7c991c10: 0x00 0x20 0x07 0x00 0x00 0x01 0x62 0x01 0x7ffe7c991c18: 0x64 0x01 0x64
这就是整行记录了,我们发现对于sort字段而言没有压缩,依旧是0x20 0x00占位,而对于addon字段（需要的字段,这里就是a1,a2,a3）而言,这里小了很多,因为做了打包（pack）即：
0x01 0x62：数据b0x01 0x64：数据d0x01 0x64：数据d而0x01应该就是长度了。
不管怎么说, 对于sort字段而言依旧比实际存储的数据大很多 。
**5、如果sort buffer存满,对sort buffer中的数据进行排序,然后写入到临时文件 **
如果需要排序的数据量很大的话,那么sort buffer肯定是不能容下的,因此如果写满后就进行一次内存排序操作,然后将排序好的数据写入到外部排序文件中去,这叫做一个chunk。外部文件的位置由tmpdir参数指定,名字以 MY 开头,注意外部排序通常需要2个临时文件,这里是第1个用于存储内存排序结果的临时文件,以chunk的方式写入。如下： if(fs_info->isfull()) //如果sort buffer满了 并且sort buffer已经排序完成 { if(write_keys(param, fs_info, idx, chunk_file, tempfile)) //写入到物理文件 完成内存排序 如果内存不会满这里不会做 会在create_sort_index 中排序完成 { num_records= HA_POS_ERROR; goto cleanup; } idx= 0; indexpos++; }
最终会调入 write_keys 函数进行排序和写入外部排序文件,这里核心就是先排序,然后循环每条排序文件写入到外部排序文件。下面我来验证一下写入临时文件的长度,我将第2节中的例子2数据扩大了N倍后,让其使用外部文件排序,下面是验证结果,断点write_keys即可： 1161if(my_b_write(tempfile, record, rec_length)) (gdb) p rec_length $8 = 91
可以每行的长度还是91字节（打包压缩后）,和前面看到的长度一致,说明这些数据会完完整整的写入到外部排序文件,这显然会比我们想象的大得多。
好了到这里数据已经找出来了,如果超过sort buffer的大小,外部排序需要的结果已经存储在临时文件1了,并且它是分片（chunk）存储到临时文件的,它以MY开头。
九、阶段7:排序方式总结输出
这里对上面的排序过程做了一个阶段性的总结,代码如下： Opt_trace_object(trace, "filesort_summary") .add("rows", num_rows) .add("examined_rows", param.examined_rows) .add("number_of_tmp_files", num_chunks) .add("sort_buffer_size", table_sort.sort_buffer_size()) .add_alnum("sort_mode", param.using_packed_addons() ? "": param.using_addon_fields() ? "": "");
我们解析一下： rows：排序的行数,也就是应用where过滤条件后剩下的行数。 examined_rows：Innodb层扫描的行数,注意这不是慢查询中的Rows_examined,这里是准确的结果,没有重复计数。 number_of_tmp_files：外部排序时,用于保存结果的临时文件的chunk数量,每次sort buffer满排序后写入到一个chunk,但是所有chunk共同存在于一个临时文件中。 sort_buffer_size：内部排序使用的内存大小,并不一定是sort_buffer_size参数指定的大小。 sort_mode：这里解释如下
1、sort_key, packed_additional_fields：使用了modified filesort algorithm（不回表排序） ,并且有打包（pack）的字段,通常为可变字段比如varchar。
2、sort_key, additional_fields：使用了modified filesort algorithm（不回表排序）,但是没有需要打包（pack）的字段,比如都是固定长度字段。
3、sort_key, rowid：使用了original filesort algorithm（回表排序）。
十、阶段8:进行最终排序
这里涉及2个部分如下： 如果sort buffer不满,则这里开始进行排序,调入函数save_index。 如果sort buffer满了,则进行归并排序,调入函数merge_many_buff->merge_buffers,最后调入merge_index完成归并排序。
对于归并排序来讲,这里可能会生成另外2个临时文件用于存储最终排序的结果,它们依然以MY开头,且依然是存储在tmpdir参数指定的位置。因此在外部排序中将可能会生成3个临时文件,总结如下： 临时文件1：用于存储内存排序的结果,以chunk为单位,一个chunk的大小就是sort buffer的大小。 临时文件2：以前面的临时文件1为基础,做归并排序。 临时文件3：将最后的归并排序结果存储,去掉sort字段,只保留 addon字段（需要访问的字段） 或者 ref字段（ROWID或者主键） ,因此它一般会比前面2个临时文件小。
但是它们不会同时存在,要么 临时文件1和临时文件2存在,要么 临时文件2和临时文件3存在。
这个很容易验证,将断点放到merge_buffers和merge_index上就可以验证了,如下： 临时文件1和临时文件2同时存在： [root@gp1 test]# lsof|grep tmp/MY mysqld 8769 mysql 70u REG 252,3 79167488 2249135/mysqldata/mysql3340/tmp/MYt1QIvr(deleted) mysqld 8769 mysql 71u REG 252,3 58327040 2249242/mysqldata/mysql3340/tmp/MY4CrO4m (deleted) 临时文件2和临时文件3共同存在： [root@gp1 test]# lsof|grep tmp/MY mysqld 8769 mysql 70u REG 252,3 360448 2249135/mysqldata/mysql3340/tmp/MYg109Wp(deleted) mysqld 8769 mysql 71u REG 252,3 79167488 2249242/mysqldata/mysql3340/tmp/MY4CrO4m (deleted)
但是由于能力有限对于归并排序的具体过程我并没有仔细学习了,这里给一个大概的接口。注意这里每次调用merge_buffers将会增加 Sort_merge_passes 1次,应该是归并的次数,这个值增量的大小可以侧面反映出外部排序使用临时文件的大小。
十一、排序的其他问题
这里将描述2个额外的排序问题。
1、original filesort algorithm（回表排序）的回表
最后对于original filesort algorithm（回表排序）排序方式而言,可能还需要做一个回表获取数据的操作,这一步可能会用到参数 read_rnd_buffer_size 定义的内存大小。
比如我们第2节中第1个例子将会使用到original filesort algorithm（回表排序）,但是对于回表操作有如下标准： 如果没有使用到外部排序临时文件则说明排序量不大,则使用普通的回表方式,调入函数rr_from_pointers,也就是单行回表方式。 如果使用到了外部排序临时文件则说明排序量较大,需要使用到批量回表方式,这个时候大概的步骤就是读取rowid（主键）排序,然后批量回表,这将会在read_rnd_buffer_size指定的内存中完成,调入函数rr_from_cache。这也是一种优化方式,因为回表一般是散列的,代价很大。
2、关于排序中Rows_examined的计算
首先这个值我说的是慢查询的中的Rows_examined,在排序中会出现重复计数的可能,前面第8节已经说明了一下,这个值在第8节还是正确的,但是最后符合where条件的数据在返回的时候还会调用函数evaluate_join_record,结果Rows_examined会增加符合where条件的行数。还是以我们第2节的两个例子为例： mysql> select* from test.tests1 where a1='b' order by a2,a3; +------+------+------+ | a1 | a2 | a3 | +------+------+------+ | b | d | d | | b | e | e | | b | f | f | +------+------+------+ 3 rows inset(5.11 sec) mysql> select* from test.tests2 where a1='b' order by a2,a3; +------+------+------+ | a1 | a2 | a3 | +------+------+------+ | b | d | d | | b | e | e | | b | f | f | +------+------+------+ 3 rows inset(5.28 sec) mysql> desc select* from tests2 where a1='b' order by a2,a3; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row inset, 1 warning (0.00 sec) 8 rows inset(0.00 sec) mysql> desc select* from tests2 where a1='b' order by a2,a3; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1| SIMPLE | tests2 | NULL | ALL | NULL | NULL | NULL | NULL | 8| 12.50| Usingwhere; Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row inset, 1 warning (0.01 sec)
慢查询如下,不要纠结时间（因为我故意debug停止了一会）,我们只关注Rows_examined,如下： # Time: 2019-12-23T12:03:26.108529+08:00 # User@Host: root[root] @ localhost [] Id: 4 # Schema: Last_errno: 0 Killed: 0 # Query_time: 5.118098 Lock_time: 0.000716 Rows_sent: 3 Rows_examined: 11 Rows_affected: 0 # Bytes_sent: 184 SET timestamp=1577073806; select* from test.tests1 where a1='b' order by a2,a3; # Time: 2019-12-23T12:03:36.138274+08:00 # User@Host: root[root] @ localhost [] Id: 4 # Schema: Last_errno: 0 Killed: 0 # Query_time: 5.285573 Lock_time: 0.000640 Rows_sent: 3 Rows_examined: 11 Rows_affected: 0 # Bytes_sent: 184 SET timestamp=1577073816; select* from test.tests2 where a1='b' order by a2,a3;
我们可以看到Rows_examined都是11,为什么是11呢？显然我们要扫描总的行数为8（这里是全表扫描,表总共8行数据）,然后过滤后需要排序的结果为3条数据,这3条数据会重复计数一次。因此就是8+3=11,也就是说有3条数据重复计数了。
十二、通过OPTIMIZER_TRACE查看排序结果
要使用OPTIMIZER_TRACE只需要“SET optimizer_trace="enabled=on";”,跑完语句后查看information_schema.OPTIMIZER_TRACE即可。前面第9节我们解释了排序方式总结输出的含义,这里我们来看看具体的结果,我们还是以第2节的2个例子为例： 例1： "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 8, "number_of_tmp_files": 0, "sort_buffer_size": 1285312, "sort_mode": "" 例2： "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 8, "number_of_tmp_files": 0, "sort_buffer_size": 322920, "sort_mode": ""
现在我们清楚了,这些总结实际上是在执行阶段生成的,需要注意几点如下： 这里的examined_rows和慢查询中的Rows_examined不一样,因为这里不会有重复计数,是准确的。 这里还会说明是否使用了优先队列排序即“filesort_priority_queue_optimization”部分。 通过“sort_buffer_size”可以发现,这里并没有分配参数sort_buffer_size指定的大小,节约了内存,这在第7节说明了。
其他指标在第9节已经说明过了,不在描述。
十三、回到问题本身
好了,大概的流程我描述了一遍,这些流程都是主要流程,实际上的流程复杂很多。那么我们回到最开始的案例上来。他的max_sort_length和max_length_for_sort_data均为默认值1024。
案例中的group by实际就是一个排序操作,我们从执行计划可以看出来,那么先分析一下它的 sort字段 。很显然group by 后的都是sort字段,其中字段CREATE_ORG_NAME其定义为 varchar（1000）,它的占用空间为（1000 * 2）及2000字节,但是超过了max_sort_length的大小,因此为1024字节,相同的还有UPDATE_ORG_NAME字段也是varchar（1000）,也会做同样处理,其他字段不会超过max_sort_length的限制,并且在第5节说过sort 字段是不会进行压缩的。
我大概算了一下sort字段的全部大小约为 （3900 * 2) 字节,可以看到一行数据的sort字段基本达到了8K的容量,而addon字段的长度（未打包压缩前）会更大,显然超过max_length_for_sort_data的设置,因此对于这样的排序显然不可能使用modified filesort algorithm（不回表排序了）,使用的是original filesort algorithm（回表排序）,因此一行的记录就是（sort 字段+主键）了,主键大小可以忽略,最终一行记录的大小就是8K左右,这个值通常会远远大于Innodb压缩后存储varchar字段的大小,这也是为什么本例中虽然表只有30G左右但是临时文件达到了200G以上的原因了。
好了,我们来重现一下问题,我们使用第2节的例1,我们将其数据增多,原理上我们的例1会使用到original filesort algorithm（回表排序）的方式,因为这里 sort字段（a2,a3）的总长度+addon字段（a1,a2,a3） 的长度约为300 * 2 * 2+300 * 3 * 3 这显示大于了max_length_for_sort_data的长度, 因此这个排序一行的长度就是 sort字段（a2,a3）+ref字段（ROWID） ,大约就是300 * 2 * 2+6=1206字节了。下面是这个表的总数据和Innodb文件大小（我这里叫做bgtest5表）： mysql> show create table bgtest5 \G *************************** 1. row *************************** Table: bgtest5 CreateTable: CREATE TABLE `bgtest5`( `a1` varchar(300) DEFAULT NULL, `a2` varchar(300) DEFAULT NULL, `a3` varchar(300) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row inset(0.01 sec) mysql> SELECT COUNT(*) FROM bgtest5; +----------+ | COUNT(*) | +----------+ | 65536| +----------+ 1 row inset(5.91 sec) mysql> desc select* from bgtest5 order by a2,a3; +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref| rows | filtered | Extra| +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | 1| SIMPLE | bgtest5 | NULL | ALL | NULL | NULL | NULL | NULL | 66034| 100.00| Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ 1 row inset, 1 warning (0.00 sec)
注意这里是全表排序了,没有where过滤条件了,下面是这个表ibd文件的大小： [root@gp1 test]# du -hs bgtest5.ibd 11M bgtest5.ibd [root@gp1 test]#
下面我们就需要将gdb的断点打在merge_many_buff,我们的目的就是观察临时文件1的大小,这个文件前面说过了是存储内存排序结果的,如下： [root@gp1 test]# lsof|grep tmp/MY mysqld 8769 mysql 69u REG 252,3 79101952 2249135/mysqldata/mysql3340/tmp/MYzfek5x(deleted)
可以看到这个文件的大小为79101952字节,即80M左右,这和我们计算的总量1206（每行大小） * 65535（行数） 约为 80M 结果一致。这远远超过了ibd文件的大小11M,并且要知道,随后还会生成一个大小差不多的文件来存储归并排序的结果如下： [root@gp1 test]# lsof|grep tmp/MY mysqld 8769 mysql 69u REG 252,3 79167488 2249135/mysqldata/mysql3340/tmp/MYzfek5x(deleted) mysqld 8769 mysql 70u REG 252,3 58327040 2249242/mysqldata/mysql3340/tmp/MY8UOLKa (deleted)
因此得到证明 ,排序的临时文件远远大于ibd文件的现象是可能出现的。
十四、全文总结
本文写了很多,这里需要做一个详细的总结：
总结1 ：排序中一行记录如何组织？ 一行排序记录,由 sort字段+addon字段 组成,其中 sort字段 为order by 后面的字段,而addon字段为需要访问的字段,比如‘select a1,a2,a3 from test order by a2,a3’,其中 sort字段 为‘a2,a3’, addon字段 为‘a1,a2,a3’。 sort字段 中的可变长度字段不能打包（pack）压缩,比如varchar,使用的是定义的大小计算空间,注意这是排序使用空间较大的一个重要因素。 如果在计算 sort字段 空间的时候,某个字段的空间大小大于了max_sort_length大小则按照max_sort_length指定的大小计算。 一行排序记录,如果 sort字段+addon字段 的长度大于了max_length_for_sort_data的大小,那么 addon字段 将不会存储,而使用 sort字段+ref字段 代替, ref字段 为主键或者ROWID,这个时候就会使用original filesort algorithm（回表排序）的方式了。 如果 addon字段 包含可变字段比如varchar字段,则会使用打包（pack）技术进行压缩,节省空间。可以参考第3、第4、第5、第6、第8节。
总结2：排序使用什么样的方法进行？ original filesort algorithm（回表排序）
如果使用的是 sort字段+ref字段 进行排序,那么必须要回表获取需要的数据,如果排序使用了临时文件（也就是说使用外部归并排序,排序量较大）则会使用 批量回表 ,批量回表会涉及到read_rnd_buffer_size参数指定的内存大小,主要用于排序和结果返回。
如果排序没有使用临时文件（内存排序就可以完成,排序量较小）则采用 单行回表 。 modified filesort algorithm（不回表排序）
如果使用的是 sort字段+addon字段 进行排序,那么使用不回表排序,所有需要的字段均在排序过程中进行存储, addon字段 中的可变长度字段可以进行打包（pack）压缩节省空间。
其次 sort字段 和 addon字段 中可能有重复的字段,比如例2中, sort字段 为a2、a3, addon字段 为a1、a2、a3,这是排序使用空间较大的另外一个原因。
在OPTIMIZER_TRACE中可以查看到使用了那种方法,参考12节。
总结3：每次排序一定会分配sort_buffer_size参数指定的内存大小吗？
不是这样的,MySQL会做一个初步的计算,通过比较Innodb中聚集索引可能存储的行上限和sort_buffer_size参数指定大小内存可以容纳的行上限,获取它们小值进行确认最终内存分配的大小,目的在于节省内存空间。
在OPTIMIZER_TRACE中可以看到使用的内存大小,参考第8、第12节。
总结4：关于OPTIMIZER_TRACE中的examined_rows和慢查询中的Rows_examined有什么区别？ 慢查询中的Rows_examined包含了重复计数,重复的部分为where条件过滤后做了排序的部分。 OPTIMIZER_TRACE中的examined_rows不包含重复计数,为实际Innodb层扫描的行数。
可以参考11节。
总结5：外部排序临时文件的使用是什么样的？
实际上一个语句的临时文件不止一个,但是它们都以MY开头,并且都放到了tmpdir目录下,lsof可以看到这种文件。 临时文件1：用于存储内存排序的结果,以chunk为单位,一个chunk的大小就是sort buffer的大小。 临时文件2：以前面的临时文件1为基础,做归并排序。 临时文件3：将最后的归并排序结果存储,去掉 sort字段 ,只 保留addon字段（需要访问的字段） 或者 ref字段（ROWID或者主键） ,因此它一般会比前面2个临时文件小。
但是它们不会同时存在,要么临时文件1和临时文件2存在,要么临时文件2和临时文件3存在。对于临时文件的使用可以查看 Sort_merge_passes ,本值多少会侧面反应出外部排序量的大小。
可以参考第10节。
总结6：排序使用了哪种算法？
虽然本文不涉及算法,但是内部排序有2种算法需要知道： 内存排序（优先队列 order by limit 返回少量行常用,提高排序效率,但是注意order by limit n,m 如果n过大可能会涉及到排序算法的切换） 内存排序（快速排序）在通过OPTIMIZER_TRACE可以查看是否使用使用了优先队列算法,参考12节。
总结7：“Creating sort index”到底是什么状态？
我们前面讲的全部排序流程都会包含在这个状态下,包括： 获取排序需要的数据（比如例子中全表扫描从Innodb层获取数据） 根据where条件过滤数据 内存排序 外部排序
总结8：如何避免临时文件过大的情况？
首先应该考虑是否可以使用索引来避免排序,如果不能则需要考虑下面的要点： order by 后面的字段满足需求即可,尽可能的少。 order by 后面涉及的字段尽量为固定长度的字段类型,而不是可变字段类型如varchar。因为 sort字段 不能压缩。 不要过大的定义可变字段长度,应该合理定义,例如varchar（10）能够满足需求不要使用varchar（50）,这些空间虽然在Innodb层存储会压缩,但是MySQL层确可能使用全长度（比如sort字段）。 在查询中尽量不要用（select *） 而使用需要查询的字段,这将会减少addon字段的个数,在我另外一个文章还讲述了（select *）的其他的缺点
参考： https://www.jianshu.com/p/ce063e2024ad 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
发展前景
近年来,IT领域呈现火箭式发展,技术上的理想主义和家国情怀鼓舞着国产数据库的自主创新,得益于国家政策的支持,国产数据库已经百花齐放。尽管数据库产品已经经过了实战应用,但是要得到业界的广泛认可,走出国门跻身世界,甚至向传统主流数据库发出挑战,就需要选取国际国内公认的、权威的基准进行测试证明。
数据库是一个企业的核心,无论企业的业务是怎样的,数据库只能识别任务,不明就里,这些任务主要分两类：一类是交易处理,一类是智能分析。针对交易处理,可以选取国际国内认可的TPC-E基准进行测试。
基准介绍
TPC-E基准测试 是以股票交易业务场景为载体的测试,选取股票交易业务场景的初衷是它作为金融行业的典型,较其他行业有更高的性能要求,并且操作类型丰富,具有广泛的代表性,是经典的OLTP系统。TPC-E基准测试使用统一的数据模型进行测试,也就是说,测试并不在乎被测数据库本身的数据,而是使用相同的原则生成股票行业数据。
TPC-E基准 基于现实股票交易操作抽象出10种数据库事务及其混合比,描述了客户、证券公司、股票交易所之间的关系,如下：
解析
从表面上看,整个架构以证券公司为核心,证券公司的操作始终代表客户的意愿,所以本质上,测试还是围绕客户买卖股票行为展开,客户提出股票交易请求,证券公司收到请求后代表客户将请求发送到股票交易所,并将收到的交易结果反馈给客户。为了保持测试前干净的环境和测试时数据的更新,基准还提供了2种与业务场景无关的事务,即交易清理事务、数据维护事务。
客户主体充当了非常重要的作用,它决定了数据规模,测试前根据数据库能力合理设置用户数,但应满足客户数不小于5000并且是1000的整数倍。客户使用账户购买股票,每个客户平均有5个账户,每个账户交易的股票平均数为10只,故每个客户交易的股票平均数为50只。客户提出交易请求的订单中有60%是市价订单（其中30%买入,30%卖出）表示以现在的市场价进行交易；另外40%是限价订单（其中20%买入,20%卖出）,表示达到指定价格后再交易。每成功提交一个订单,相应的就会产生一个结果,TPC-E基准测试就是用产生结果的速率来衡量系统性能,称其为事务吞吐率。 我们不断强调TPC-E基准是高度仿真的标准,其仿真性体现在丰富的事务种类、合理的混合比、严格的响应时间、以及事务吞吐率的有效性等约束条件上。另一方面,TPC-E基准又是一个基础标准,测试的是在该配置下,数据库是否具备这个基础能力,而不是测试数据库的能力到底有多强,这也是为什么其结果必须满足标准吞吐率的80%-102%范围才视为有效。
实际应用系统数据库中的数据可能与股票行业相去甚远,TPC-E基准测试使用股票交易行业数据进行测试,却说体现的是实际应用系统中数据库的性能,该如何理解？ 数据库是一种技术,数据库性能测试体现的是数据库的能力,无论什么样的应用系统,对于数据库来说,都是增删改查,所以选取股票交易行业进行测试,操作丰富足以展现能力,能够科学全面的评估数据库的性能,而且使用统一的数据进行测试结果具有可比性。
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
最近把个人博客搭建好了,链接在这里： tobe的呓语 ,文章会先在博客和公众号更新~ 希望大家多多收藏啊
所谓事务（Transaction）,就是通过确保成批的操作要么完全执行,要么完全不执行,来维护数据库的完整性。举一个烂大街的例子：A 向 B 转账 1000 元,对应的 SQL 语句为：（没有显式定义事务） UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A'; UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';
运行后的结果如下： mysql> SELECT * FROM deposit_table; +------+---------+ | name | deposit | +------+---------+ | A | 3000 | | B | 5000 | +------+---------+
这样做可能遇到问题,比如执行完第一条语句之后,数据库崩溃了,最后的结果就 可能 会是这样（ 毕竟咱不会模拟这种故障 ）： +------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 5000 | +------+---------+
A 的 1000 块钱平白无故消失了,这肯定不合适。事务就是为了解决类似的问题而出现的,如果使用事务来处理转账,对应的 SQL 就是： START TRANSACTION; UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A'; UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B'; COMMIT;
仅仅是在这原先的两条 SQL 语句前后加上了 START TRANSACTION 和 COMMIT ,就可以保证即使转账操作失败,A 的余额也不会减少。
仔细想一想发现这个例子不是特别合适,因为数据库的故障恢复技术（以后会谈到）会影响最终的结果,也不容易模拟这种故障,最后结果只能靠猜 : ) 但我也想不出其它更加合适的例子。。。如果你们有更好的例子欢迎留言讨论。
接下来就详细讨论事务的一些特性和（某些）实现细节。
ACID A：Atomicity（原子性） C：Consistency（一致性） I：Isolation（隔离性） D：Durability（持久性）
Atomicity（原子性）
先谈两个重要的概念： 提交（commit）和回滚（rollback） ,当我们执行提交操作后,将对数据库进行 永久性 的修改,执行回滚操作,意味着数据库将 撤销正在进行的所有没有提交的修改 。注意这里的永久性并不意味这事务一完成就把数据刷到磁盘上,即使没有刷入磁盘,MySQL 也有 日志机制 来保证修改不会丢失。
事务是支持 提交和回滚 的工作单元,原子性,就是说事务对数据库进行多次更改时, 要么在提交事务的时候所有更改都成功,要么在回滚事务的时候撤销所有更改 。这是 官方文档 的表述,但有的人似乎错误理解了 commit 语句,实际上,哪怕事务里某一语句出现了错误,一旦你执行 commit,前面正常的修改仍然会被提交, MySQL 不会自动判断事务中的 SQL 执行成功与否。
我们接下来用例子来看看 commit 和 rollback： mysql> SELECT * FROM deposit_table; +------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 6000 | +------+---------+ 2 rows in set (0.04 sec) mysql> START TRANSACTION; INSERT INTO deposit_table VALUES('C', 7000); INSERT INTO deposit_table VALUES('D', 8000); #再次插入 D,由于主键的唯一性,该语句会执行失败 INSERT INTO deposit_table VALUES('D', 9000); COMMIT; #提交事务 Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) 1062 - Duplicate entry 'D' for key 'PRIMARY' Query OK, 0 rows affected (0.07 sec) mysql> SELECT * FROM deposit_table; +------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 6000 | | C | 7000 | | D | 8000 | +------+---------+ 4 rows in set (0.04 sec)
我们可以看到,在执行 INSERT INTO deposit_table VALUES('D', 9000) 的时候,由于前一条语句已经插入了 D,所以这一句 SQL 语句执行失败,报出 1062 - Duplicate entry 'D' for key 'PRIMARY' 错误,但执行 COMMIT 后,前面的修改仍然得到了提交,这显然是不符合我们的预期的。
注意：如果你是使用 Navicat 的查询界面,将执行不到 COMMIT 语句,只能执行到报错的地方,建议使用命令行来执行。
所以在实际情况中,我们需要根据 MySQL 的错误返回值来确定,是使用 ROLLBACK 还是 COMMIT 。就像这样： # 创建一个存储过程 CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`() BEGIN # 创建一个标志符,出现错误就将其置为 1 DECLARE err_flg INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1; START TRANSACTION; INSERT INTO deposit_table VALUES('C', 7000); INSERT INTO deposit_table VALUES('D', 8000); INSERT INTO deposit_table VALUES('D', 9000); # 发生错误,回滚事务 IF err_flg = 1 THEN SELECT 'SQL Err Invoked'; # 错误提示信息 ROLLBACK; SELECT * FROM deposit_table; # 没有发生错误,直接提交 ELSE SELECT 'TRANSACTION Success'; COMMIT; SELECT * FROM deposit_table; END IF; END
接下来我们调用该存储过程： mysql> call insert_test(); +-----------------+ | SQL Err Invoked | +-----------------+ | SQL Err Invoked | +-----------------+ 1 row in set (0.04 sec) +------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 6000 | +------+---------+ 2 rows in set (0.09 sec) Query OK, 0 rows affected (0.00 sec)
结果里打印出了错误信息 SQL Err Invoked 表的内容也没有更改,表明我们的 ROLLBACK 成功回滚了事务,达到我们的预期。如果你是使用其他语言调用 MySQL 的接口,也只需要获取错误标志,相应的执行 ROLLBACK 或者 COMMIT 。
Consistency（一致性）
官网给出的解释 如下： The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.
翻译过来就是： 在每次提交或回滚之后以及正在进行的事务处理期间,数据库始终保持一致状态,如果跨多个表更新了相关数据,则查询将看到所有旧值或所有新值,而不是新旧值的混合 。
举个例子： # 表 a,b 的定义略过 START TRANSACTION; UPDATE a SET name = 'a_new' WHERE name = 'a_old'; UPDATE b SET name = 'b_new' WHERE name = 'b_old'; COMMIT;
这个例子里的一致性,就是说,如果此时有查询 SELECT a.name, b.name FROM a, b; 得到的结果要么是 a_old b_old （表明事务已回滚或者正在执行）,要么是 a_new b_new （表明事务已经成功提交）,而不会出现 a_old b_new 以及 a_new b_old 这两种情况。
有的博客将一致性解释为“数据 符合现实世界中的约束 ,比如唯一性约束等等。” 我个人还是倾向于官方文档的解释,这点见仁见智吧,纠结这些概念意义不大。
Isolation（隔离性）
事务的隔离性是说, 事务之间不能互相干扰,也不能看到彼此的未提交数据 。这种隔离是通过锁机制实现的。我们在操作系统里也了解过,使用锁,往往就意味着并发性能的下降,因为可能会发生阻塞,甚至死锁现象。
当然,用户在 确定事务确实不会相互干扰时,可以调整隔离级别,牺牲部分隔离性以提高性能和并发性 ,至于使用哪种隔离级别（ isolation level ）这就需要你自己做 trade off。
因为隔离性涉及的的内容很多,我把它放到下一篇文章详细解释。
Durability（持久性）
事务的持久性是说,一旦提交操作成功,该事务所做的更改就不会因为一些意外而丢失,比如电源断电,系统崩溃等潜在威胁。MySQL 提供了很多机制,比如日志技术, doublewrite buffer 等等。
MySQL 的日志恢复技术我将单独写一篇文章,这里说说 doublewrite buffer 技术。
虽然这个技术名字叫做 buffer,但实际上 该缓冲区并不位于内存,而是位于磁盘 。这可能听起来很诡异——既然是把数据放入磁盘,为啥不直接写入到 data file,反而多此一举？
这是因为 InnoDB 的 Page Size 一般是 16kb,其数据校验也是针对页来计算的,在将数据刷入磁盘的过程中,如果发生断电等故障,该页可能只写入了一部分（partial page write）。这种情况是 redo 日志无法解决的,因为 redo 日志中记录的是对页的物理操作,如果页本身发生了损坏,再对其进行 redo 是没有意义的。所以我们需要一个副本,在发生这种情况时还原该页。
而且缓冲区是顺序写的,开销相对随机读写要小很多,所以 doublewrite 后,性能也不是降为原来的 50%。
事务中的常用语句 START TRANSACTION / BEGIN 显式开启一个事务 COMMIT 提交事务,永久性修改数据库 SAVEPOINT 在事务里创建保存点 RELEASE SAVAPOINT 移除某保存点 ROLLBACK 回滚事务,撤回所有未提交的更改,事务会终止 ROLLBACK TO [SAVEPOINT] 回滚到给定保存点,但事务不终止,另外,该 保存点后的行锁不会被释放 ,详见 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements ： InnoDB does not release the row locks that were stored in memory after the savepoint . (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) SET TRANSACTION 设置事务隔离级别 SET autocommit 0/1 是否自动提交（默认自动提交）
强调一下 autocommit 参数,默认情况下,如果不显式使用 START TRANSACTION / BEGIN ,MySQL 会把每一句 SQL 当做独立的事务,举个例子：
原来的表结构： mysql> SELECT * FROM deposit_table; +------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 6000 | +------+---------+ 2 rows in set (0.04 sec)
新的存储过程（仅仅删除了 START TRANSACTION ）： CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`() BEGIN #Routine body goes here... DECLARE err_flg INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1; # START TRANSACTION; INSERT INTO deposit_table VALUES('C', 7000); INSERT INTO deposit_table VALUES('D', 8000); INSERT INTO deposit_table VALUES('D', 9000); IF err_flg = 1 THEN SELECT 'SQL Err Invoked'; ROLLBACK; SELECT * FROM deposit_table; ELSE SELECT 'TRANSACTION Success'; COMMIT; SELECT * FROM deposit_table; END IF; END
调用的结果： mysql> call insert_test(); +-----------------+ | SQL Err Invoked | +-----------------+ | SQL Err Invoked | +-----------------+ 1 row in set (0.24 sec) +------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 6000 | | C | 7000 | | D | 8000 | +------+---------+ 4 rows in set (0.28 sec) Query OK, 0 rows affected (0.21 sec)
在这里,我们看到尽管确实执行了 ROLLBACK,但 C 和 D 仍然插入到了 deposit_table 。这是因为没有显式标明事务,MySQL 会进行隐式事务,自动提交每次的修改,所以就无法进行回滚了。
事务的基本概念就介绍这么多,以后我将会讲到事务的隔离机制,范式设计等内容,敬请期待！
希望你在看完我的文章之后有所收获,期待你的赞和转发！ 如果本文对你有帮助,欢迎关注我的公众号 tobe的呓语 ,带你深入计算机的世界～ 公众号后台回复关键词【计算机】有惊喜哦~
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>> 前言 最近挺焦虑的,不知道未来该做什么,方向又是什么。只能用 别慌,月亮也正在大海的某处迷茫。 来安慰下自己。不过学习的初心咱们还是不要忘记。今天我们学习的是enum分片算法。 1.hash分区算法 2.stringhash分区算法 3.enum分区算法 4.numberrange分区算法 5.patternrange分区算法 6.date分区算法 7.jumpstringhash算法 enum分区算法的配置 code func_enum partition.txt 0 0 enum和之前的hash算法一样。需要在rule.xml中配置tableRule和function。 tableRule标签,name对应的是规则的名字,而rule标签中的columns则对应的分片字段,这个字段必须和表中的字段一致。algorithm则代表了执行分片函数的名字。 function标签,name代表分片算法的名字,算法的名字要和上面的tableRule中的 标签相对应。class：指定分片算法实现类。property指定了对应分片算法的参数。不同的算法参数不同。 mapFile:指定配置文件名。其格式将在下面做详细说明。 defaultNode：指定默认节点号。默认值为-1,不指定默认节点。 type：指定配置文件中key的类型。0：整型； 其它：字符串。
mapfile文件格式配置如下：
a.type=0,
int1=node0
int2=node1
a.type=其他 string1=node0 string2=node1
1.启动加载配置
当启动的时候,会先根据type的值判断是字符串还是数字。然后把mapfile中配置的值加载到内存中,形成一个映射表。 例如上面的配置中type=0,就可以判断是数字,然后查看mapFile对应的文件partition.txt,可以查到:
10000=0
10010=1
也就是枚举值10000,就存放在分片1上,而枚举值10010,就存放在分片2上。
2.运行过程
当在运行的过程中,如果有用户通过查询code=10000或者是code=10001的时候,就会访问这个枚举算法。根据上面的映射表直接查询得到分片的编号。
3.我们建表来测试一下。
通过创建test_enum表,我们插入三条数据,分别是code=10000,10010,10020,可以看到10000被存放在分片1上,10010被存放在分片2上。这个和我们在partition.txt中配置的文件一样。当我们插入10020的时候,因为枚举值不存在,它会选择默认的分片节点dn1。这里不会因为错误而报错。
枚举在使用的时候,需要把已知的全部罗列出来。但是也有劣势,就是可能罗列不全,在这个时候把不在枚举定义范围的数字存放到默认节点是一个没有办法的办法,如果突然因为某个新版本上线,出现一些新的枚举类型而没有及时更新,会导致默认节点数据快速膨胀。此时就需要进行扩容,然后实现局部数据迁移。
注意事项 mapfile文件不包含“=”的行将被跳过. 重复的枚举值的分区数据节点以最后一个配置为准。 分片字段为该枚举类型。 分片字段为NULL时,数据落在defaultNode节点上,若此时defaultNode没有配置,则会报错;当真实存在于mysql的字段值为not null的时候,报错 "Sharding column can't be null when the table in MySQL column is not null"
后记
今天介绍的枚举算法较为简单。后续将继续带来剩下几种算法,谢谢支持！ 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
钢铁是怎样炼成的,这本书下发下来已经一个月了,这部书可以说是当代党人加强思想意识培养的启蒙书。通读了钢铁是怎样炼成的,我学到了很多,那一句人：一生只有一次,人生下来就应该拥有崇高理想并为之不懈奋斗让我对未来生活和工作充满干劲儿。作为一名党员,在学习中不断受到鼓舞。通过对保尔.柯察金的学习,学习他身上对革命的执着精神,让我看到了一名敢于同反派做斗争,英勇无畏的英雄。
其中最精彩的那句话:人最宝贵的是生命,生命属于人只有一次,人的一生应当这样度过,当他回首往事的时候,不会因为碌碌无为虚度年华而悔恨,也不会因为人卑劣生活庸俗而羞愧,这样在临终的时候,他就能够说,我已经把自己整个的生命和全部的精力献给了世界上最壮丽的事业,为人类的解放而奋斗。这本书我读了不止一遍,不管是小学初中还是高中,他都是培养坚强品格的理想读,当人生面临选择,我就喜欢读一读这本书,从书中的语言文字中可以找到力量让我充满拼搏的斗志和昂扬向上的动力。
作为一名青年党员,在生活中就应该向保尔学习,经得起生活中的各种考验,哪怕是在战争的火线上也总是勇往直前,人的良好品格不是一蹴而就,它需要不断锤炼自己,就像这本书的名字,钢铁的炼成就必须经过高温,然后不断煅打,我们要在生活的考验面前坚持不倒,从中得到锻炼,才能在现实生活中不被困难所击败,勇往直前的精神一直是我们所需要的,爱党爱国爱家,忘我工作,锲而不舍。
在不断学习中,我总认为保尔和他身边的战士或者是说所有的革命战士,都应该拥有像保尔一样坚强不屈的品格,良好的生活面貌和精神面貌。保尔的一生就像他喜欢的《牛虻》一样,斗争精神和拼搏精神是必不可少的,不论在哪个时代遇到困难就要勇敢去面对,这需要坚定初心和拥有坚强不屈的毅力以及顽强拼搏的精神。
初心是一个人的起点,理想是一个人的追求,没有初心,那么他不知道他的根在何方,当他迷失方向,他就会一直迷失下去,如果他有出息,那么他可以回到起点,在此向前从头开始继续努力,没有理想,那么这个人就没有追求,他的一生一定是碌碌无为,求索作文网（https://www.isanxia.com/）就像书中说的虚度年华,到了晚年也会在回首往事时而感到羞愧。我们的初心使命就像那个年代的革命先辈一样,为了让中国复兴,为了让人民更加幸福,这是我们的理想信念,也是我们的初心。总书记说,牢记人民对美好生活的向往,就是我们的奋斗目标,党员要时刻不忘我们的初心和使命。
有了初心还不行,还要不断学习,在人的成长中理想和信念要始终保持不动摇,不滑坡,自觉成为坚定的,有远大理想的党的信仰者。坚持学习,就像保尔一样坚持下去,坚持不断学习党的基本理论,特别是总书记新时代中国特色社会主义思想。通过读书我也深刻学习到要始终保持党性修养的纯洁。在书中有不少人在斗争中迷失了自我,最后消失在时代的长河里。他们的人生不禁令我慨叹,他们在自己的人生路上走错了一步,最后失去了所有。或许他们一开始也拥有顽强的斗志,但是时光的消磨让他们迷失方向。
总书记曾经说,无数志士仁人为实现民族复兴而奋起抗争,但一次又一次的失败了,中国党成立后团结带领人民前赴后继,顽强奋斗,把贫穷落后的旧中国变成日益走向繁荣富强的新中国,中华民族伟大复兴展现出前所未有的光明前景。我们党我们国家我们民族取得的胜利,是经过无数革命先辈们的努力付出和辛勤劳作换来的这里边或者他们的热血和青春。我们手中接过传承棒,要传给子孙后代的不仅仅是一句国家富强民族振兴的口号,而是实实在在的强国交给他们。革命的生活永不熄灭,社会主义的伟大事业持续向前推进。我们一定要不忘初心继续前行。
在保尔的身上我也学到对待工作的态度,纵然他的毅力惊人,坚持工作,但是我们也应该学到他对待工作的态度,是那样忘我。在这本书中,我看到他忘我工作,有机会休假仍然工作,我不禁感叹毅力真是一种锲而不舍的精神。同保尔柯察金相比,我感觉自己的工作态度不值他的十之一二。想起工作上的种种做事情总是没有耐心,一遇到困难也总爱发些牢骚,然后就想着放弃,或者是用求助的眼神希望能够得到同事们的帮助,对照保尔柯察金想想自己,纵然是寒冷的冬天我的脸上也是感觉一阵阵发热。
保尔柯察金在全身瘫痪的情况下,仍然具有那种知难而上为理想而奋斗的精神,而我现有的条件比保尔柯察金不知道要好上多少倍,想想他想想自己,我又有什么理由为了一点点困难而放弃了,今后我一定好好学习努力工作,向保尔柯察金学习,从他的身上汲取更多的力量,严格要求自己。
人生难免有挫折对照保尔柯察金,他一个在身处绝境的人,仍然凭借惊人的毅力和顽强的精神完成了巨作。在苏维埃革命风暴中不断锻炼自己时,自己成为一个坚定的无产阶级革命者,他身上的压力,和我们现在在社会上拼搏奋斗的压力相比,不足九牛之一毛,他是一个英雄,或许我们不能和他相比,但是他是我们的榜样,我要向他学习。
为了生活,保尔柯察金洗过碗,做过电工。其实读到这里时,我发觉他的成功是拼搏和积累,纵然有际遇也是和他的努力拼搏干劲儿分不开。从他的身上我学到了对待生活的态度,生活中的困难并不可怕,可怕是自己给自己施加压力压倒自己。我从中学到酸甜苦辣也要勇敢面对,纵然离别时是伤心,但是久别重逢后的喜悦也更值得我们珍惜。没有人可以一步登天,没有人可以一蹴而就,坚持拼搏在任何时候都必不可少,有毅力的人面对考验,能勇往直前能持之以恒,没有毅力的人,只能“悬崖勒马”原路返回。总的来说这本书很精彩,但是我的学习深度还不足以领会全书的精神,我会在接下来的时间里继续学习,感悟总是在不断学习中积累,而每一点积累都将鼓励我明天更加努力。 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
备注：防止网络过慢,文末 Q 群可以获取到所需文件。
报错： Traceback (most recent call last): 2: from ./redis-trib.rb:25:in `' 1: from /usr/local/ruby/lib/ruby/2.6.0/rubygems/core_ext/kernel_require.rb:54:in `require' /usr/local/ruby/lib/ruby/2.6.0/rubygems/core_ext/kernel_require.rb:54:in `require': cannot load such file -- redis (LoadError)
原因：
缺少 redis 的相关依赖,需要通过 gem 安装
解决： # gem install redis
安装成功后重新执行创建指令即可成功。如果执行命令报错,则继续向下进行。
重新安装高版本的ruby
安装基础依赖环境 # yum -y install gcc gcc-c++ autoconf automake libtool make zlib zlib-devel openssl openssl-devel pcre-devel p7zip tcl
准备安装包 # wget https://cache.ruby-lang.org/pub/ruby/2.6/ruby-2.6.5.tar.gz # tar zxf ruby-2.6.5.tar.gz # ./configure --prefix=/usr/local/ruby # make && make install //时间估计在5分钟左右 # gem sources --remove https://rubygems.org/ //删除原有源 # gem sources -a https://gems.ruby-china.com/ //使用国内源 # gem sources –l # gem install net-ssh -v 2.10.1.rc1 # gem install net-sftp # gem install net-scp # gem install redis //安装redis依赖
加群备注：Wybaron
群内：文件->00_安装包->搜索“ruby”即可 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>> 根据官方指导 官方文档 下载 yum 仓库 仓库网址 ,点击想要下载的 download 右键复制链接 然后通过 wget 下载对应文件: wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm 安装 sudo yum localinstall mysql80-community-release-el8-1.noarch.rpm 不是所有的包都适用于每一个系统,所以可以自行下载。 查看可供下载的版本 sudo yum --disablerepo=* --enablerepo='mysql*-community*' list available yum repolist all | grep mysql 然后下载对应的包就可以 安装 yum repolist enabled | grep mysql -y 仅安装 client yum install mysql
然后使用参数进行连接其他的数据库。
mysql -h ip -P port -p password -u username 启动对应服务 >service mysqld start
或者 [根据系统选择](https://dev.mysql.com/downloads/mysql/) 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>> -- 查看事务日志 ： show engine innodb status\G; -- 查看日志文件设置状态 show variables like 'innodb_%';
事务日志文件
innodb_log_files_in_group:DB中设置几组事务日志,默认是2； innodb_log_group_home_dir:事务日志存放目录,不设置,ib_logfile0...存在在数据文件目录下
Innodb存储引擎可将所有数据存放于ibdata*的共享表空间,也可将每张表存放于独立的.ibd文件的独立表空间
注意：在MySQL中对于数据来说, 最为重要的是日志文件
redo log => ib_logfile0
undo log => ibdata
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
Choose two
Examine this SQL statement:
DELETE FROM employees e
WHERE EXISTS
(SELECT 'dummy'
FROM emp_history
WHERE employee_id = e.employee_id)
Which two are true?
A) The subquery is executed for every row in the EMPLOYEES table.
B) The subquery is not a correlated subquery.
C) The subquery is executed before the DELETE statement is executed.
D) All existing rows in the EMPLOYEEE table are deleted.
E) The DELETE statement executes successfully even if the subquery selects multiple rows.
Answer：：AE
(解析：这又是一个关联子查询的考题,出现过多次,A 答案大家要注意。)
关联子查询：
1、 先执行主查询,对于主查询返回的每一行数据,都会造成子查询执行一次
2、 然后子查询返回的结果又传给主查询
3、 主查询根据返回的记录做出判断
)
注意这道题答案 E 是对的,与前面第 14 题的答案 D 上有区别,要分别对待。
create table emp2 as select * from emp where deptno=20;
update emp2 set empno=7934 where ename=’SCOTT’;
DELETE FROM emp e
WHERE EXISTS
(SELECT 'empno'
FROM emp2
WHERE empno = e.empno);
UPDATE emp e
SET ename =
(SELECT ename
FROM emp2
WHERE empno = e.empno); 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
今天就是520啦,不知道大家有没有对心爱的人表白～据说拿着荣耀30Pro去表白成功的几率会扩大好几倍呢！昨天看荣耀手机官方微博发了一组流光幻境新配色的图片,简直让我这个直女的心颤抖liao～我想说：我的钛空银买早了！
今天给大家安利一下为什么说520送礼一定要选择荣耀30Pro流光幻境。这个配色的颜值实在太高,因为它的颜值百变多样,不是一成不变。上图小姐姐手持荣耀30Pro的样子像极了大家的初恋吧？荣耀30Pro流光幻境机身折射出来粉红柔和的光芒,简直太美liao～
这张图,近距离一看,原来荣耀30Pro流光幻境是银色？到底是什么颜色！我也有些迷茫了～荣耀30Pro流光幻境太百变,据说还没有一个人可以准确的拿捏出来它的颜值,不妨送给你心爱的人,让Ta来猜猜看荣耀30Pro流光幻境到底是什么颜色～
如果你送给心爱的人其他手机,那么获得的只是一个固定手机的颜值,而荣耀30Pro流光幻境的机身可以折射出不同的景色,仿佛一个镜子,物美它也美,物动它也动。年轻人喜欢永远保持新鲜,荣耀30Pro流光幻境就可以随时给你新鲜感。当然,配置方面也不含糊,不然就只是个“花瓶”了, 荣耀30Pro绝对不是花瓶手机！
荣耀30Pro采用麒麟990芯片,集成5G技术这在5G手机中十分罕见,可以说已经占领5G的技术高地,高通那边旗舰处理器还是外挂基带,势必会造成一定的信号损失,而荣耀30Pro可以保证你们的爱情永不掉线！在拍照方面全系配备50倍潜望式长焦镜头,就算你们相隔很远也可以拍摄最清晰的彼此,40W超级安全快充为你们的联系保驾护航,荣耀30Pro流光幻境绝对是520情人节最好的礼物,一定会获得满满好评。不要问我为什么安利它,因为我男朋友送我的也是荣耀30Pro这款手机呀～ 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
字符串类型
类型 范围 说明 | char(M) | M=1~255 字符 | 固定长度 | varchar(M) | 一行中所有 varchar 的列所占用的字节数不能超过 65535 字节 | 存储可变长度的 M 个字符 |
|---|
| tinytext | 最大长度 255 | 可变长度 | | text | 最大长度 65535 | 可变长度 | | mediumtext | 最大长度 16777215 | 可变长度
longtext | enum
最大长度 4294967295 | 集合最大数目为 65535
可变长度 | 只能插入列表中的值 |
时间类型
类型 存储空间（字节） 格式 取值范围 | date | 3 | YYYY-MM-DD | 1000-01-01~9999-12-31 | time | 3~6 | HH:MM:SS『微秒值』 | -838:59:59~838:59:59
year | datetime | timestamp
1 | 5~8 | 4~7
YYYY | YYYY-MM-DD HH:MM:SS『微秒值』 | YYYY-MM-DD HH:MM:SS『微秒值』
1907~2155 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC |
|---|
微秒 存储空间（字节） | 0 | 0 整数类型
类型 存储空间（字节） 属性 取值范围 | tinyint | 1 | signed/unsigned | -128~127/0~255 | smallint | 2 | signed/unsigned | -32768~32767/0~65535
mediumint | int | bigint
3 | 4 | 8
signed/unsigned | signed/unsigned | signed/unsigned
-8388608~8388607/0~16777215 | -2147483648~2147483647/0~4294967295 | -9223372036854775808~9223372036854775807/0~18446744073709551615 |
|---|
浮点类型
类型 存储空间（字节） 是否精确类型 | float | 4 | 否
double | decimal
8 | 每 4 个字节存 9 个数字,小数点占一个字节
否 | 是
欢迎扫描下方二维码, https://www.phpst.cn ,持续关注：
互联网工程师（id:phpstcn）,我们一起学习,一起进步 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
越来越多的人对HMS Core不再陌生,都知道它是华为终端云服务开放能力的合集,是一个面向开发者开放诸多能力的平台。HMS Core的能力和服务能够帮助开发者的应用获得更多的用户和加速变现。对于用户而言,华为HMS Core让开发者专注于创新,为使用者带来了更美好的全场景智慧生活体验。
HMS Core是华为终端云服务开放能力的合集
HMS Core对开发者开放多项服务与能力大概可以分为四大类。分别为基础功能类、安全功能类、智慧功能类和价值类。今天来和大家聊聊我认为最重要的一类,安全功能类。我们在使用应用时总是会忍不住担心有没有安全防护措施。
面对这些安全问题HMS Core有自身安全功能类来应对：数字版权服务能够给开发者提供内容的数字版权保护,包括硬件与软件级的DRM能力,客户端证书在线申请,多种内容加密格式与加密算法,在线与离线播放等多种场景。线上快速身份验证服务给开发者的应用提供生物特征认证和FIDO2用户身份认证能力,对开发者提供安全易用的免密认证服务。安全检测服务提供系统完整性检测、恶意URL检测、应用安全检测、虚假用户检测、恶意WiFi检测,可帮助开发者构建应用安全。
俄新社接入HMS Core使用华为安全检测服务
良禽择木而栖,HMS Core具有如此出众的能力,与之合作的开发者也是数不胜数。例如全球知名的新闻资讯类APP—俄新社。还有越南热门的PDF文件工具—PDF Reader,通过集成HMS Core华为安全检测服务,识别应用运行环境是否安全,当用户手机处于被Root、解锁、提权等风险状态,运用风险提示,保障用户财产安全。
HMS Core用独特的安全功能类为开发者提供安全开发环境,让更多的开发者接入HMS Core。HMS Core的安全功能类像是一道坚不可摧的防火墙,时刻保护用户使用应用安全,守护用户信息财产安全。 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
介绍
Redis 是一个开源的内存中键值数据存储。Redis有几个命令,可让您即时更改Redis服务器的配置设置。本教程将介绍其中一些命令,并说明如何使这些配置更改永久生效。
如何使用本指南
本指南以备有完整示例的备忘单形式编写。我们鼓励您跳至与您要完成的任务相关的任何部分。
本指南中显示的命令已在运行Redis版本4.0.9的Ubuntu 18.04服务器上进行了测试。要设置类似的环境,您可以按照我们的指南 如何在Ubuntu 18.04上安装和保护Redis的 步骤1 进行操作。我们将通过使用Redis命令行界面运行它们来演示这些命令的行为。请注意,如果您使用其他Redis界面（例如 Redli） ,则某些命令的确切输出可能会有所不同。 redis-cli
请注意,托管Redis数据库通常不允许用户更改配置文件。如果您正在使用DigitalOcean的托管数据库,则本指南中概述的命令将导致错误。
更改Redis的配置
本节中概述的命令将仅在当前会话期间或直到您运行之前更改Redis服务器的行为, config rewrite 这将使它们永久化。您可以通过使用首选文本编辑器打开和编辑Redis配置文件来直接更改它。例如,您可以 nano 这样做： sudo nano /etc/redis/redis.conf
警告： 该 config set 命令 被认为是危险的 。通过更改Redis配置文件,有可能导致Redis服务器以意外或不良方式运行。我们建议仅在 config set 测试命令的行为或绝对确定要对Redis配置进行更改时才运行该命令。
您可能希望 将此命令重命名 为不太可能意外运行的 命令 。
config set 允许您在运行时重新配置Redis,而无需重新启动服务。它使用以下语法： config set parameter value
例如,如果要更改运行 save 命令后Redis将产生的数据库转储文件的名称,则可以运行如下命令： config set "dbfilename" "new_file.rdb"
如果配置更改有效,则命令将返回 OK 。否则将返回错误。
**注意：**并非 redis.conf 文件中的每个参数都可以通过 config set 操作来更改。例如,您不能更改 requirepass 参数定义的身份验证密码。
永久进行配置更改
config set 不会永久更改Redis实例的配置文件；它仅在运行时更改Redis的行为。要 redis.conf 在运行 config-set 命令后进行编辑并使当前会话的配置永久化,请运行 config rewrite ： config rewrite
此命令将尽最大努力保留原始 redis.conf 文件的注释和整体结构,而只需进行最小的更改即可匹配服务器当前使用的设置。
就像 config set ,如果重写成功 config rewrite 将返回 OK 。
检查Redis的配置
要读取Redis服务器的当前配置参数,请运行 config get 命令。 config get 只有一个参数,其可以是在使用的参数中的任一个完全匹配 redis.conf 或 水珠图案 。例如： config get repl*
根据您的Redis配置,此命令可能返回： Output 1) "repl-ping-slave-period" 2) "10" 3) "repl-timeout" 4) "60" 5) "repl-backlog-size" 6) "1048576" 7) "repl-backlog-ttl" 8) "3600" 9) "repl-diskless-sync-delay" 10) "5" 11) "repl-disable-tcp-nodelay" 12) "no" 13) "repl-diskless-sync" 14) "no"
您还可以 config set 通过运行返回所有支持的配置参数 config get * 。
结论
本指南详细介绍了 redis-cli 用于动态更改Redis服务器的配置文件的命令。如果您想在本指南中概述其他相关的命令,参数或过程,请在下面的评论中提出疑问或提出建议。
有关Redis命令的更多信息,请参阅关于 如何管理Redis数据库的 系列教程。 如何在ubuntu18.04上安装和保护redis 如何连接到Redis数据库 如何管理Redis数据库和Keys 如何在Redis中管理副本和客户端 如何在Redis中管理字符串 如何在Redis中管理list 如何在Redis中管理Hashes 如何在Redis中管理Sets 如何在Redis中管理Sorted Sets 如何在Redis中运行事务 如何使Redis中的Key失效 如何解决Redis中的故障 如何从命令行更改Redis的配置 Redis数据类型简介 作者： 分布式编程 出处： https://zthinker.com/ 如果你喜欢本文,请长按二维码,关注 分布式编程 . 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
六：PostgreSQL 12.2企业级应用公开课(6)：可见性验证
1、Transaction ids Structure
2、Tuples Structure
3、dml操作PG操作原理
4、Transaction Status有哪些
5、什么是Transaction Snapshot
6、行可见性检查规则
由于PG数据库在update的时候是把原来的行逻辑上删除,重新插入新行,这样子就导致块中包含有很多不可用的行,我们在查询的时候PG通过什么样的规则去判断哪些行可见,哪些行不可见呢本课我们进行深入的研究。
首宜求其旨意,次必寻其脉络,然后乃可以探骊得珠也。
本课适合PG管理员和PG开发工程师。
时间：2020-04-16 20:00-21:00
地址：https://ke.qq.com/course/1466978
视频：联系CUUG咨询老师要下载链接
注：PostgresSQL 12.2 企业级课程系列公开课,是由北京CUUG的陈老师制作讲解,内容涉及到企业中常用到的一些技术,比如备份恢复、PITR、流复制、双机热备、单表查询成本估算、可见性验证、Autovacuum调优、事务隔离级别、并行查询调优、等一系列内容,免费公开课,欢迎大家参与学习。
关于PGCA和PGCE认证,这是PostgreSQL数据库的初级和中级认证,CUUG是指定的培训及考试中心,如想考PG认证,可以联系CUUG咨询老师。 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
Oracle时间转换 select DeptCode,IHCount from view_tobis_zycount where 1=1 and 执行时间 between to_date(:DateBegin,'yyyy-MM-dd hh24:mi:ss') and to_date(:DateEnd,'yyyy-MM-dd hh24:mi:ss') and rownum <100
select t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB'; select * from v_pt_收入分析 where 日期 between to_date(sysdate-30,'yyyy-mm-dd') and to_date(sysdate,'yyyy-mm-dd'); select 类别,收入分类 ,sum(金额) from v_pt_收入分析 where 日期 between sysdate-30 and sysdate group by 类别,收入分类 //注：分类列一定要记全,除了统计列的其他 select extract(year from sysdate) as 今年 from dual --今年 select ( to_char(sysdate-30,'YYYY/MM')) as 年月 from dual --今年上个月 select to_char(sysdate,'MM')-1 as 月份 from dual --本月 ADD_MONTHS(sysdate, -12) --去年今天 v_pt_门诊人次（每一天的每个科室每种号别的门诊人次） v_pt_住院人次（每一天的每个科室入院数/出院数/现人数等） v_pt_收入分析（每一天的门诊/住院 每个科室每种收入分类的金额）
一、最近一个月的“在院人次”、“床位使用率”、“药品比例” select sysdate-30 as 起始日期,sysdate as 截止日期,'在院人次' as 统计项目,count( 在院人数) as 值 from v_pt_住院人次 where 日期 between sysdate-30 and sysdate union all select sysdate-30 as 起始日期,sysdate as 截止日期,'床位使用率' as 统计项目,(count( 实际占用床位数)/count(实际开放床位数)) as 值 from v_pt_住院人次 where 日期 between sysdate-30 and sysdate union all select distinct sysdate-30 as 起始日期,sysdate as 截止日期,'药品比例' as 统计项目,round((select count(收入分类) from v_pt_收入分析 where 收入分类='西药费' or 收入分类='中成药' ) / (select count(收入分类) from v_pt_收入分析),3) as 值 from v_pt_收入分析 where 日期 between sysdate-30 and sysdate
二、最近3个月的“门诊人次”和前一年的相同月份的“门诊人次” select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-1) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-30,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-2) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-60,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-3) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-90,'YYYY/MM') union all select extract(year from sysdate)-1as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-1) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -12),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-2) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -13),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-3) as 月份,'门诊人次' as 统计项目,count(门诊人次) from v_pt_门诊人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -14),'YYYY/MM')
三、最近一个月的“门诊收入” select sysdate-30 as 起始日期,sysdate as 截止日期,收入分类 as 统计项目,count(金额) from v_pt_收入分析 where 类别='门诊' and 日期 between sysdate-30 and sysdate group by 收入分类
五、最近一个月的“住院收入” select sysdate-30 as 起始日期,sysdate as 截止日期,收入分类 as 统计项目,count(金额) from v_pt_收入分析 where 类别='住院' and 日期 between sysdate-30 and sysdate group by 收入分类
四、最近3个月的“出院人数”和前一年的相同月份的“出院人数” select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-1) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-30,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-2) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-60,'YYYY/MM') union all select extract(year from sysdate) as 年份,(to_char(sysdate,'MM')-3) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(sysdate-90,'YYYY/MM') union all select extract(year from sysdate)-1as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-1) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -12),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-2) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -13),'YYYY/MM') union all select extract(year from sysdate)-1 as 年份,(to_char(ADD_MONTHS(sysdate, -12),'MM')-3) as 月份,'出院人数' as 统计项目,count(出院人数) from v_pt_住院人次 where to_char(日期,'YYYY/MM') = to_char(ADD_MONTHS(sysdate, -14),'YYYY/MM')
存储过程执行插入数据 begin execute zl_人员表_新增( 人员表_ID.nextval ,:编号 , :姓名 , '', :身份证号 , To_Date( :出生日期,'yyyy-MM-dd') , :性别 , '' , null , :联系电话 , '' , '' , '' , '' , '' , '' , '' , '' , 0, '', '' , '' , '' , '554', '' , '' , '' , '' , '' , '' , '' , '' , '' , To_Number( :联系电话 ) , 0, Sysdate ); end;
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>> MySQL 8 记录死锁关的几张表有所变化,重新写一个脚本,便于在出现问题的时候快速处置问题。
死锁示意图
死锁相关的表 information_schema.INNODB_TRX `performance_schema`.data_lock_waits `performance_schema`.threads `performance_schema`.data_locks -- 可不使用
相关说明 -- request 被阻塞的 -- block 引起阻塞的 等效字段 `performance_schema`.threads.PROCESSLIST_ID = `information_schema`.innodb_trx.trx_mysql_thread_id = information_schema.`PROCESSLIST`.id `performance_schema`.threads.thread_id = `performance_schema`.data_lock_waits.REQUESTING_THREAD_ID
检测脚本 SELECT a.*,c.trx_state as block_trx_state,c.trx_started as block_trx_started,c.trx_query as block_trx_query,c.trx_mysql_thread_id as block_trx_mysql_thread_id,d.thread_id as block_thread_id,d.PROCESSLIST_USER as block_user,d.PROCESSLIST_HOST as block_host from (SELECT b.trx_id as req_trx_id,b.trx_state as req_trx_state,b.trx_started as req_trx_started,b.trx_query as req_trx_query,b.trx_mysql_thread_id as req_trx_mysql_thread_id,c.thread_id as req_thread_id,c.PROCESSLIST_USER as req_user,c.PROCESSLIST_HOST as req_host from `performance_schema`.data_lock_waits a INNER JOIN information_schema.INNODB_TRX b on a.REQUESTING_ENGINE_TRANSACTION_ID=b.trx_id and a.REQUESTING_ENGINE_LOCK_ID = b.trx_requested_lock_id INNER JOIN `performance_schema`.threads c on a.REQUESTING_THREAD_ID = c.THREAD_ID ) a INNER JOIN `performance_schema`.data_lock_waits b on a.req_trx_id = b.REQUESTING_ENGINE_TRANSACTION_ID and a.req_thread_id = b.REQUESTING_THREAD_ID INNER JOIN information_schema.INNODB_TRX c on b.BLOCKING_ENGINE_TRANSACTION_ID = c.TRX_ID INNER JOIN `performance_schema`.threads d on b.BLOCKING_THREAD_ID = d.THREAD_ID
检测结果示意
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
一.分类：
二.创建示例表：
create table mylock(
id int not null primary key auto_increment,
name varchar(20)
) engine myisam;
insert into mylock(name)values('a');
insert into mylock(name)values('b');
insert into mylock(name)values('c');
insert into mylock(name)values('d');
insert into mylock(name)values('e');
select * from mylock;
三.表锁（偏读）示例
偏向MyISAM存储引擎,开销小,加锁快；无死锁；锁定力度大(表锁）发生锁冲突的概率最高,并发度最低。
3.1.读锁：
会话1
lock table mylock read;
该 会话能执行的操作：
1.select * from mylock;
不能执行的操作：
1.update mylock set name='a2' where id=1; 不能更新该表
2.select * from dept; 不能查询其他表
其他会话
可以查询mylock表
不可以更新mylock表（会进入阻塞状态,直到锁释放）
3.2写锁：
会话1
lock table mylock write;
该 会话能执行的操作：
1.select * from mylock; 可以查询本表
2.update mylock set name='a2' where id=1; 更新该表
不能执行的操作：
1.select * from dept; 查询其他表
其他会话
不能执行任何操作：
1.select * from mylock;
2.update mylock set name ="a3" where id=1;
简而言之：就是读锁会阻塞写,但是不会阻塞读,而写锁会把读和写都阻塞掉
看看那些表被加锁了：
mysql>show open tables;
如何分析锁锁定：
可以通过查看table_lockes _waited和table_lock_immediate状态变量来分析系统上的表锁定
SQL:show status like 'table_locks%';
Table_locks_immediate：产生表级锁定的次数,表示可以立即获取锁的查询次数,每立即获取锁值加1
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数,每等待一次锁值加1),此值高说明存在严重的表级锁占用情况。
此外Myisam的读写锁调度是写优先,这也是myisam不适合做写为主表的引擎。因为写锁后,其他线程无法进行任何操作,大量的更新会使查询很难得到锁,从而造成永久阻塞
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>> 递归查询 在Oracle中 建立一个表 create table nayi_180328_connect_test( dept_id varchar2(50), parent_id varchar2(50), dept_name varchar2(100), dept_rank varchar2(2000), val number); 插入语句 insert into nayi_180328_connect_test select 'root', '', '全国', '', 0 from dual union all select 'root_1', 'root', '北京市', '', 2000 from dual union all select 'ln_root', 'root', '辽宁省', '', 200 from dual union all select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual union all select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual union all select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual union all select 'jl_root', 'root', '吉林省', '', 0 from dual union all select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual union all select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual ; Oracle的递归查询语句如下 select t1.*,CONNECT_BY_ROOT(dept_name) root_name from nayi_180328_connect_test t1 where 1=1 connect by prior t1.dept_id = t1.parent_id start with t1.dept_id = 'root' ; 结果如下 迁移MySQL 8 ,建表如下 create table nayi_180328_connect_test( dept_id varchar(50), parent_id varchar(50), dept_name varchar(100), dept_rank varchar(2000), val int, PRIMARY key (dept_id) ) 插入语句与Oracle相同 insert into nayi_180328_connect_test select 'root', '', '全国', '', 0 from dual union all select 'root_1', 'root', '北京市', '', 2000 from dual union all select 'ln_root', 'root', '辽宁省', '', 200 from dual union all select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual union all select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual union all select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual union all select 'jl_root', 'root', '吉林省', '', 0 from dual union all select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual union all select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual MySQL 8递归查询语句如下 with recursive t1(dept_id,parent_id,dept_name,dept_rank,val,root_name,rownum, order_str) as ( select t0.*,t0.dept_name,@rownum := 1 rn, cast(@rownum as char) sdfsf from nayi_180328_connect_test t0 where t0.dept_id='root' union all select t2.*,t1.root_name,@rownum := @rownum + 1 rn, concat(t1.order_str, '-', @rownum) st from nayi_180328_connect_test t2,t1 where t2.parent_id = t1.dept_id ) select * from t1 order by order_str 结果如下 日期转换字符串 Oracle中 select to_char(sysdate,'yyyy-mm-dd') from dual 结果 2020-05-07 MySQL 8中 select date_format(now(),'%Y-%m-%d') from dual 结果 2020-05-07 字符串转换日期 Oracle中 select to_date('2020-01-01','yyyy-mm-dd') from dual 结果 2020-01-01 00:00:00 MySQL 8中 select str_to_date('2020-01-01','%Y-%m-%d %h:%i:%s') from dual 结果 2020-01-01 00:00:00 判断为空,用其他值代替 Oracle中 select nvl(parent_id,'boot') from nayi_180328_connect_test where dept_id='root' 结果 boot MySQL 8中 select ifnull(parent_id,'boot') from nayi_180328_connect_test where dept_id='root' 结果 boot 条件判断取值 Oracle中 select decode(parent_id,null,'全国','root','省市','地区') from nayi_180328_connect_test 结果 MySQL 8中 select case when parent_id is null then '全国' when parent_id='root' then '省市' else '地区' end from nayi_180328_connect_test 结果 聚合字段拼接 在Oracle中 WITH TEMP AS( SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL ) SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY) AS CITIES FROM TEMP GROUP BY NATION 结果 在MySQL 8中 WITH TEMP AS( SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL ) SELECT NATION,GROUP_CONCAT(city order by city) cities FROM TEMP GROUP BY NATION 结果 截取字符串 在Oracle中 select substr('HelloWorld',0,3) value from dual; select substr('HelloWorld',1,3) value from dual; 以上执行结果相同 Hel 在MySQL 8中 select substr('HelloWorld',1,3) value from dual; 在MySQL中,substr()的首索引不能为0 结果 Hel 数字格式化字符串 在Oracle中 select to_char(12345678.657,'999,999,999,999.99') from dual; 结果 12,345,678.66 在MySQL 8中 select format(12345678.657,2) from dual; 结果 12,345,678.66 跨库查询 在Oracle中 select 字段名 from 表名@库名 在MySQL 8中 select 字段名 from 库名.表名 拼接字符串 在Oracle中 select 'ABC' || 'EFG' from dual 结果 ABCEFG 在MySQL 8中 select concat('ABC','EFG') from dual 结果 ABCEFG 表分区 在Oracle中 我们先创建两个表空间 create tablespace CUS_TS01 datafile '/home/oracle/app/oracle/oradata/helowin/cus01.dbf' size 20m; create tablespace CUS_TS02 datafile '/home/oracle/app/oracle/oradata/helowin/cus02.dbf' size 20m; 此时查询表空间 select * from dba_tablespaces; 结果 我们可以看到最后两个表空间是我们刚生成的 CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 ) 我们创建一个表,并生成两个表分区CUS_PART1,CUS_PART2.关于分区的分类可以参考 https://www.cnblogs.com/wnlja/p/3979684.html 插入一条数据 insert into customer values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1') 由于该分区是以数据的数目来进行分区的,前10W行在第一个分区,后10W行在第二个分区,所以我们按分区来进行查询时,只能在第一个分区查到该数据,而第二个分区是没有的 select * from customer partition(CUS_PART1) select * from customer partition(CUS_PART2) 在MySQL 8中 创建同样的表,同样的分区,关于MySQL表分区的分类可以参考 https://www.cnblogs.com/zhouguowei/p/9360136.html CREATE TABLE CUSTOMER ( CUSTOMER_ID int NOT NULL, FIRST_NAME VARCHAR(30) NOT NULL, LAST_NAME VARCHAR(30) NOT NULL, PHONE VARCHAR(15) NOT NULL, EMAIL VARCHAR(80), STATUS CHAR(1), primary key (CUSTOMER_ID) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000), PARTITION CUS_PART2 VALUES LESS THAN (200000) ) 插入同样的数据 insert into CUSTOMER values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1') 进行分区查询 select * from CUSTOMER partition(CUS_PART1) select * from CUSTOMER partition(CUS_PART2) 日期相减 在Oracle中 很多人喜欢用 trunc(日期1)-trunc(日期2) 因为trunc(日期)可以去掉时间部分,比如 select sysdate from dual 结果 2020-05-14 09:59:27 select trunc(sysdate) from dual 结果 2020-05-14 00:00:00 注:trunc(日期,'dd')与trunc(日期)意义相同 select trunc(sysdate ,'dd') from dual ; 结果 2020-05-15 00:00:00 在MySQL 8中 mysql中没有trunc函数,直接用 日期1-日期2 但如果日期带了时间部分,比如 select now() from dual 结果 2020-05-14 02:15:14 如果此时用带时间的日期相减会出错,可以用如下方式处理 select str_to_date(now(),'%Y-%m-%d')-str_to_date('2020-05-01','%Y-%m-%d') from dual 结果 13 为了保险起见,可以将Oracle中的trunc(日期)转换成str_to_date(日期,'%Y-%m-%d') 获取当前年份的第一天 在Oracle中 select TRUNC(SYSDATE, 'yyyy') from dual 结果 2020-01-01 00:00:00 在MySQL 8中 SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY) from dual 结果 2020-01-01 date_sub() 从日期减去指定的时间间隔。 格式： DATE_SUB(date,INTERVAL expr type) CURDATE() 函数返回当前的日期,不带时间 select curdate() from dual 结果 2020-05-14 DAYOFYEAR() 函数返回指定日期在一年中的位置 select dayofyear('2020-05-13') from dual 结果 134 DATE_SUB()函数的type为以下类型 Type值 MICROSECOND | SECOND |
|---|
| MINUTE | | HOUR | | DAY | | WEEK | | MONTH | | QUARTER | | YEAR | | SECOND_MICROSECOND | | MINUTE_MICROSECOND | | MINUTE_SECOND | | HOUR_MICROSECOND | | HOUR_SECOND | | HOUR_MINUTE | | DAY_MICROSECOND | | DAY_SECOND | DAY_MINUTE
DAY_HOUR | YEAR_MONTH
几个月之后(之前)的某天
在Oracle中 select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , 3) from dual union all select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , -3) from dual
结果
2020-04-01 00:00:00
2019-10-01 00:00:00
在MySQL 8中 SELECT ADDDATE('2020-01-01', INTERVAL 3 MONTH) from dual union all SELECT ADDDATE('2020-01-01', INTERVAL -3 MONTH) from dual
结果
2020-04-01
2019-10-01 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
1.将一张表的某一个字段（多条单据）合并成一个字符串输出（逗号分隔） + ",(select LISTAGG(mo.model_code,',') within group(order by mo.model_code) from srm_pos_resource_model mo where mo.supplier_id=t.supplier_id) modelCodeList\n"
2.根据表的某个字段查询出最新的一条单据 ——根据supplierId查询最新的一条单据 select * from ( select t.supplier_id,t.introduction_audit_id,t.creation_date,row_number() over(partition by t.supplier_id order by t.creation_date desc) AS code_id from srm_pos_introduction_audit t ) where code_id=1; --控制只输出最新一条记录
3.前30天的数据 select * from 表 where 日期=to_date(to_char(sysdate-30,'yyyy/mm/dd'),'yyyy/mm/dd');
4.删除指定的表,模糊查询 declare v_sql varchar2(200); v_cnt number(10); begin for v_tab in (select table_name from user_tables t where table_name like 'Test%') loop v_sql := 'drop table '|| v_tab.table_name; execute immediate v_sql ; end loop; end;
5.创建表空空间 ----查看表空间 select * from Dba_Tablespaces; ----创建表空间 create tablespace APPS_TS_TX_DATA logging datafile 'D:/software/softwareWorkspace/professionalSoftwareWorkspace/Oracle/oraclexe/app/oracle/oradata/XE/APPS_TS_TX_DATA.dbf' size 200m autoextend on next 100m maxsize 20480m extent management local;
6. 拆分以逗号分隔的字符串为多行 SELECT REGEXP_SUBSTR ('26238,26239,55198', '[^,]+', 1,rownum) from dual connect by rownum<=LENGTH ('26238,26239,55198') - LENGTH (regexp_replace('26238,26239,55198', ',', ''))+1;
7、oracle的年份比较——去年年份 select t.supplier_id,t.supplier_type,t.creation_date from srm_pos_supplier_info t where 1=1 and to_char(t.creation_date,'yyyy')=(SELECT to_char(sysdate,'yyyy')-1 FROM dual); --取年份 Select to_number(to_char(sysdate,'yyyy')) from dual; select extract (year from sysdate) from dual; --取月份 select to_number(to_char(sysdate,'mm')) from dual; select extract (month from sysdate) from dual; --取日期 Select to_number(to_char(sysdate,'dd')) from dual; select extract (day from sysdate) from dual; --获取当前年月日： Select to_char(sysdate,'yyyy') from dual; Select to_char(sysdate,'MM') from dual; Select to_char(sysdate,'dd') from dual; Select to_char(sysdate,'yyyy-mm') from dual; Select to_char(sysdate,'yyyy-MM-dd') from dual; Select Extract(year from sysdate) from dual; select extract (month from sysdate) from dual; select extract (day from sysdate) from dual;
8.取两个日期中的最大日期 select decode(sign(t.last_update_date-sysdate),-1,sysdate,t.last_update_date) from srm_pos_scene_manage t;
9.取上一个月,去年的日期 select to_char(add_months(sysdate,-1), 'yyyy-MM') from dual; select to_char(add_months(sysdate,-1), 'MM') from dual; SELECT to_char(sysdate,'yyyy')-1 FROM dual;
10.批量生成表的所有字段注释SQL declare --创建时间 creation_date varchar2(20) := '2020-04-15'; --创建人 created_by varchar2(20) :='创建者'; ---业务描述 description varchar2(100) :='品类保存方法'; --开发方式——创建、modify method varchar2(30) :='创建'; ---输入表名 cursor vdata is select t.column_name,t.comments,t.TABLE_NAME from all_col_comments t where t.table_name = upper('srm_base_categories') ---表名 and instr(lower(t.column_name), 'attribute') = 0 and instr(lower(t.COLUMN_NAME),'last_update') = 0 and instr(lower(t.COLUMN_NAME),'version_num') = 0 and instr(lower(t.COLUMN_NAME),'created_by') = 0 and instr(lower(t.COLUMN_NAME),'creation_date') = 0; column_name varchar2(100); data_type varchar2(20); nullable varchar2(1); --声明变量,记录一行数 v vdata%rowtype; begin dbms_output.put_line('/**'); dbms_output.put_line('* Description：'||description); dbms_output.put_line('*'); dbms_output.put_line('* ======================================================================='); dbms_output.put_line('* 参数名称 参数描述 数据类型 是否必填'); --打开游标 open vdata; --循环遍历取数据 loop fetch vdata into v; exit when vdata%NOTFOUND; --处理驼峰式字段 select substr(replace(initcap('a' || lower(v.column_name)), '_', ''), 2) into column_name from dual; --获取字段类型与是否必填 select t.DATA_TYPE,case when t.NULLABLE = 'Y' then 'N' ELSE 'Y' END into data_type,nullable from ALL_TAB_COLUMNS t where t.TABLE_NAME=upper(v.table_name) and t.COLUMN_NAME=v.column_name and rownum<=1; dbms_output.put_line('* '||column_name|| ' ' ||v.comments || ' '||data_type || ' '|| nullable); end loop; --关闭游标 close vdata; dbms_output.put_line('*'); dbms_output.put_line('* Update History'); dbms_output.put_line('* ======================================================================='); dbms_output.put_line('* Version Date Updated By Description'); dbms_output.put_line('* ------- ----------- ----------- ---------------'); dbms_output.put_line('* V1.0 '||creation_date||' '||created_by||' '||method); dbms_output.put_line('* ======================================================================='); dbms_output.put_line('*/'); end;
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
远程桌面连接不上 vps群控远程桌面是微软公司为了便于网络管理员管理维护服务器推出的一项服务。从windows 2000 server版本开始引入,网络管理员时候远程桌面连接器连接到网络任意一台开启了远程桌面控制功能的计算机上,就像是自己操作该计算机一样,运行程序,维护数据库等。远程桌面采用的是一种类似TELNET的技术,它是从TELNET协议发展而来的,通俗的讲远程桌面就是图形化的TELNET。那么如何批量管理我们的多台服务器呢？下面向大家介绍一款远程桌面连接工具——IIS7远程桌面连接工具。
查看地址： iis7远程桌面管理工具下载
首先,下载解压软件：
点击右上角的【添加】添加服务器的相关信息：
下面是你必须要添加的服务器信息：
【注意】 1 、 输入服务器端口后用冒号分隔再填写端口号（一般默认为 3389 ）； 2 、 服务器账号一般默认为 administrator ； 3 、 服务器密码就是在购买服务器时所给的密码或者自己设置的密码。 其他信息是为了方便大批量管理服务器信息的时候添加的分组信息,根据个人情况和喜好做分类。 添加完毕核对无误后就可以点击右下角的添加,就可以看到添加的服务器信息,双击就可以打开啦！ 添加多台服务器之后打开：
批量打开之后,最厉害的功能就是它可以同时控制全部的电脑： 同时鼠标操作,键盘操作等等：
这就是这个软件如何批量管理服务器,vps,云服务器的方法,以后还会做到更好,请大家多多关注！ 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
背景：kettle转换,将oracle中数据导入到postgresql数据库中,中文乱码。
解决方法：在转换中,添加一个中间步骤-字段选择（转换里面找到字段选择）
在字段选择里面,选择和修改里面点击获取选择的字段,然后再元数据里面,再点击一下获取改变的字段,获取到字段后,在乱码字段后面encoding里面,设置编码（utf8或者gb2312,可以自行尝试）,保存重新启动转换即可。
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
MySQL 里字段的属性很多,对性能来说,影响也是可大可小,所以针对其属性这一块有必要进行一次探究。
一、NULL / NOT NULL
NULL 对 外部程序 来说,具体为不知道、不确切的、无法表述的值。所以在很多家公司的开发规范里都明确规定了, 必须为 NOT NULL 。 其实用到 NULL 的场景都可以转换为有意义的字符或者数值,一是有利用数据的易读性以及后期的易维护性；二是降低 SQL 语句的编写难度。
关于 NULL 的特性如下：
1. 参与 NULL 字段拼接的结果都为 NULL,预期的可能会有差异
预想把字段 r1 做个拼接,再插入到新的表 t3 里,结果 t3 表的记录全为 NULL,跟预期不符。 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `r1` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `r1` varchar(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> create table t3 like t1; Query OK, 0 rows affected (0.04 sec) mysql> insert into t3 select concat(r1,'database') from t1 limit 2; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t3; +------+ | r1 | +------+ | NULL | | NULL | +------+ 2 rows in set (0.00 sec)
那正确的方法如下,对 NULL 用函数 IFNULL 特殊处理。 mysql> insert into t3 select concat(ifnull(r1,''),'database') from t1 limit 2; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t3; +----------+ | r1 | +----------+ | database | | database | +----------+ 2 rows in set (0.00 sec)
2. 对于包含 NULL 列的求 COUNT 值也不准确
t1 和 t2 的记录数是一样的,但是字段 r1 包含了 NULL,这导致结果忽略了这些值。 mysql> select count(r1) as rc from t1; +-------+ | rc | +-------+ | 16384 | +-------+ 1 row in set (0.01 sec) mysql> select count(r1) as rc from t2; +-------+ | rc | +-------+ | 32768 | +-------+ 1 row in set (0.03 sec)
这时候我们可能想到了,正确的方法是用 NULL 相关函数处理, mysql> select count(ifnull(r1,'')) as rc from t1; +-------+ | rc | +-------+ | 32768 | +-------+ 1 row in set (0.03 sec)
或者是直接用 COUNT(*) 包含了所有可能的值 mysql> select count(*) as rc from t1; +-------+ | rc | +-------+ | 32768 | +-------+ 1 row in set (0.02 sec)
当然了不仅仅是 COUNT,除了 NULL 相关的函数,大部分对 NULL 处理都不友好。所以其实从上面两点来看,NULL 的处理都得特殊处理,额外增加了编写 SQL 的难度。
3. 包含 NULL 的索引列
对包含 NULL 列建立索引,比不包含的 NULL 的字段,要多占用一个 BIT 位来存储。 示例 key_len 分别为 43 和 42,t1 比 t2 多了一个字节,那这里可能有人要问了,不是说占了一个 BIT 位吗？那为什么多了一个字节？可以关注我 上一篇文章（第02期：MySQL 数据类型的艺术 - 大对象字段） 关于 BIT 的详细描述。 mysql> pager grep -i 'key_len' PAGER set to 'grep -i 'key_len'' mysql> explain select * from t1 where r1 = ''\G key_len: 43 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t2 where r1 = ''\G key_len: 42 1 row in set, 1 warning (0.00 sec)
4. 各存储引擎相关的对 NULL 的处理
在 MySQL 8.0 发布后,仅有 InnoDB、MyISAM 和 Memory 支持对包含 NULL 列的索引,其他引擎不支持。比如 NDB。
二、AUTO_INCREMENT
列的自增属性,一般用来设置整数列根据一定步长逐步增长的值,类似于其他数据库的序列。不过这里的“序列”是基于特定一张表的。关于自增属性的相关特性如下：
1. 控制自增属性性能的变量：innodb_autoinc_lock_mode innodb_autoinc_lock_mode=0
代表传统模式,也就是说,在对有自增属性的字段插入记录时,会持续持有一个表级别的自增锁,直到语句执行结束为止。比如说下面两条语句,SQL 1 在执行期间,一直持有对表 f1 的表级自增锁,接下来 SQL 2 执行时锁超时。 innodb_autoinc_lock_mode=1
代表连续模式,和传统模式差不多,不同的点在于对于简单的插入语句,比如 SQL 2,只在分配新的 ID 过程中持有一个轻量级的互斥锁（线程级别,而不是事务级别）,而不是直到语句结束才释放的表锁。 -- SQL 1 mysql> insert into f1(c2) select rpad(uuid(),100,uuid()) from t1; Query OK, 16777216 rows affected (3 min 35.92 sec) Records: 16777216 Duplicates: 0 Warnings: 0 -- SQL 2 mysql> insert into f1(c2) select 'database'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction innodb_autoinc_lock_mode=2
代表交错模式。这个模式下放弃了自增表锁,产生的值会不连续。不过这是性能最高的模式,多条插入语句可以并发执行。MySQL 8.0 默认就是交错模式。 -- SQL 1 mysql> insert into f1(c2) select rpad(uuid(),100,uuid()) from t1; Query OK, 16777216 rows affected (3 min 35.92 sec) Records: 16777216 Duplicates: 0 Warnings: 0 -- SQL 2 mysql> insert into f1(c2) select 'sql2'; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0
那针对复制安全来说,以上三种模式,0 和 1 对语句级别安全,也就是产生的二进制日志复制到任何其他机器都数据都一致；2 对于语句级别不安全；三种模式对二进制日志格式为行的都安全。
2. 控制自增属性的步长以及偏移量
一般用在主主复制架构或者多源复制架构里,主动规避主键冲突。 auto_increment_increment 控制步长auto_increment_offset 控制偏移量
3. 对于要立刻获取插入值的需求
就是说要获取一张表任何时候的最大值,应该时刻执行以下 SQL 3 ,而不是 SQL 2。SQL 2 里的函数 last_insert_id() 只获取上一条语句最开始的 ID,只适合简单的 INSERT。 -- SQL 1 mysql> insert into f1(c2) values ('xx1'),('xx2'),('xx3'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 -- SQL 2 mysql> select last_insert_id() as last_insert_id; +----------------+ | last_insert_id | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) -- SQL 3 mysql> select max(c1) as last_insert_id from f1; +----------------+ | last_insert_id | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) -- SQL 4 mysql> select * from f1; +----+------+ | c1 | c2 | +----+------+ | 1 | xx1 | | 2 | xx2 | | 3 | xx3 | +----+------+ 3 rows in set (0.00 sec)
4. 自增列溢出现象
自增属性的列如果到了此列数据类型的最大值,会发生值溢出。比如变更表 f1 的自增属性列为 tinyint。SQL 2 显式插入最大值 127, SQL 3 就报错了。所以这点上建议提前规划好自增列的字段类型,提前了解上限值。 mysql> drop table f1; Query OK, 0 rows affected (0.04 sec) mysql> create table f1(c1 tinyint auto_increment primary key); Query OK, 0 rows affected (0.05 sec) -- SQL 1 mysql> insert into f1 values (127); Query OK, 1 row affected (0.01 sec) -- SQL 2 mysql> select * from f1; +-----+ | c1 | +-----+ | 127 | +-----+ 1 row in set (0.00 sec) -- SQL 3 mysql> insert into f1 select null; ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
5. 自增列也可以显式插入有符号的值 mysql> insert into f1 values (-10),(-20),(-30); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from f1; +-----+ | c1 | +-----+ | -30 | | -20 | | -10 | | 127 | +-----+ 4 rows in set (0.00 sec)
那针对这样的,建议在请求到达数据库前规避掉。
总结
本文讲了一个是字段是否应该为 NOT NULL,这时候应该很清楚了,字段最好是 NOT NULL；另外一个是字段的自增属性,其中关于性能与用法的相关示例。希望对大家有帮助。
关于 MySQL 的技术内容,你们还有什么想知道的吗？赶紧留言告诉小编吧！ 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
前不久刚换了一部新手机,正当我满心欢喜地准备开启我的游戏之旅时,才发现原来耳机和充电口是一个,这对于我这个游戏党来说可是致命的缺陷啊,插上耳机就充不了电,如果充电的话又听不清游戏里的声音,没办法,只能考虑配一个蓝牙耳机了。为了买到合适的耳机,我看遍了京东上的降噪耳机以及各种开箱评测。终于让我发现了这款性价比超高的荣耀FlyPods3。
在荣耀FlyPods3出现之前,我一直迟迟没有剁手的原因主要还是价格。作为一个学生党,动辄千元的降噪耳机让我望而却步,而价格低廉的蓝牙耳机音质效果又不好,让我一度陷入纠结。荣耀FlyPods3虽然只有799元,降噪效果却不输1999元的AirPods Pro,这点让我很惊奇,果断入手！
在铃兰白和知更鸟蓝中,我偏爱知更鸟蓝,因为这种蓝色给我纯净辽阔的感觉,就像是以前仰头见到的一片蔚蓝的天空,搭配亮面镀层的非金属材充电盒,手感轻盈,质感出色。而且,入耳式的设计佩戴起来也非常舒适,戴着它玩游戏两个小时耳朵也不会痛。
除了玩游戏,我平时还很喜欢听歌。在实际体验中,这款无线耳机也带来了比较不错的降噪效果。戴上这款耳机之后,我在家码字时,机械键盘的青轴所带来的啪啪声都会轻柔很多。在户外场景下,我佩戴荣耀FlyPods3坐公交和地铁时,车子启动和站点播报的声音均减弱了很多,尤其是环境音的削弱极为明显。让我感到惊喜的是,荣耀FlyPods3的降噪是可以自行选择开启和关闭,通过长按耳机腔体位置即可实现。不得不说,这枚耳机让我非常满意。
如果你也和我一样正面对选择哪个品牌的无线耳机发愁,那么我可以很负责任地说,这款荣耀FlyPods3降噪耳机绝对是一个不错的选择！ 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
荣耀30S发布已经有一段时间,自始至终这款手机的焦点都是在麒麟820处理器上,那么这款处理器对荣耀30S和荣耀手机在哪些方面有影响呢?
首先从时间节点上看,荣耀30S是荣耀2020的开年之作,麒麟820也是麒麟8系列的首款5G芯片,是华为继麒麟990后的第二款5G SoC,在战略布局上有着不一般的意义。麒麟820可以说是通过技术进步,抬高中等价位手机的普遍水准,降低最普通的消费者想要用一款“好”手机的门槛。综合麒麟820的各项性能表现和荣耀30S 2399元起的价格来看,这款芯片都完全有实力带来很不错的市场表现。另外,作为一款价位仅在两千元的5G芯片,麒麟820很有可能将改变5G手机价格高、用户覆盖面少的市场格局,凭借亲民的价格推动5G手机的普及。
从实际体验上来看,麒麟820芯片也是相当好用。流畅的使用体验、高帧率的游戏效果、低能耗下的5G搜网驻网能力,都能给用户很不一般的使用体验。根据笔者使用荣耀30S的体验来说,玩王者荣耀效果全开,FPS稳居56帧率以上,即使是团战,最后攻打敌人水晶,FPS依然基本保持在60FPS左右,排除掉网络干扰的情况下,整个游戏下来没有一秒的卡顿。运行和平精英也是十分流畅,支持各种游戏特效,完全没有问题。
作为一款5G手机,它的5G网络能力当然也至关重要。在与其他品牌同级别的5G手机进行对比测试中,绝大部分时间,只有荣耀30S提前搜索到5G,并且保持了很长一段驻网时间。在驻网环节的下行网速,接近电信套餐限速,荣耀 30s的5G信号边缘区域的切换速度和驻网能力算是一个越级体验。
当每个人都能只花一两千就能享受到真正“好用”的手机时,当每个人都能以低廉的成本享受“好”的使用体验的时候,我们必须要为做到这件事的厂商喝个彩,麒麟820就是这样,值得给荣耀一个点赞。 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
一、存储引擎简介
1.文件系统： 1.1 操作系统组织和存取数据的一种机制。 1.2 文件系统是一种软件。
2.文件系统类型：ext2 3 4 ,xfs 数据 2.1 不管使用什么文件系统,数据内容不会变化 2.2 不同的是,存储空间、大小、速度。
3.MySQL引擎： 可以理解为,MySQL的“文件系统”,只不过功能更加强大。
4.MySQL引擎功能： 除了可以提供基本的存取功能,还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。
总之,存储引擎的各项特性就是为了保障数据库的安全和性能设计结构。
二MySQL自带的存储引擎类型 MySQL 提供以下存储引擎: ①InnoDB
②MyISAM
③MEMORY
④ARCHIVE
⑤FEDERATED
⑥EXAMPLE
⑦BLACKHOLE
⑧MERGE
⑨NDBCLUSTER
⑩CSV
还可以使用第三方存储引擎: ①MySQL当中插件式的存储引擎类型
②MySQL的两个分支
③perconaDB
④mariaDB
#查看当前MySQL支持的存储引擎类型 mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) #查看innodb的表有哪些 mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb'; +--------------+----------------------+--------+ | table_schema | table_name | engine | +--------------+----------------------+--------+ | mysql | innodb_index_stats | InnoDB | | mysql | innodb_table_stats | InnoDB | | mysql | slave_master_info | InnoDB | | mysql | slave_relay_log_info | InnoDB | | mysql | slave_worker_info | InnoDB | +--------------+----------------------+--------+ 20 rows in set (0.03 sec) #查看myisam的表有哪些 mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam'; +--------------------+---------------------------+--------+ | table_schema | table_name | engine | +--------------------+---------------------------+--------+ | information_schema | COLUMNS | MyISAM | | information_schema | EVENTS | MyISAM | | mysql | help_category | MyISAM | | mysql | ndb_binlog_index | MyISAM | +--------------------+---------------------------+--------+ 33 rows in set (0.01 sec)
1、innodb和myisam的区别
物理上的区别：
#进入mysql目录 [root@db01~l]# cd /application/mysql/data/mysql #myisam [root@db01 mysql]# ll user.* -rw-rw---- 1 mysql mysql 10684 Mar 6 2017 user.frm -rw-rw---- 1 mysql mysql 960 Aug 14 01:15 user.MYD -rw-rw---- 1 mysql mysql 2048 Aug 14 01:15 user.MYI #进入word目录 [root@db01 world]# cd /application/mysql/data/world/ #innodb [root@db01 world]# ll city.* -rw-rw---- 1 mysql mysql 8710 Aug 14 16:23 city.frm -rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd
2.innodb存储引擎的简介
在MySQL5.5版本之后,默认的存储引擎,提供高可靠性和高性能。 优点:
01）事务安全（遵从 ACID）
02）MVCC（Multi-Versioning Concurrency Control,多版本并发控制）
03）InnoDB 行级别锁定
04）Oracle 样式一致非锁定读取
05）表数据进行整理来优化基于主键的查询
06）支持外键引用完整性约束
07）大型数据卷上的最大性能
08）将对表的查询与不同存储引擎混合
09）出现故障后快速自动恢复
10）用于在内存中缓存数据和索引的缓冲区池

innodb核心特性 重点:
MVCC
事务
行级锁
热备份
Crash Safe Recovery（自动故障恢复）
3.查看存储引擎
1）使用 SELECT 确认会话存储引擎
#查询默认存储引擎 mysql> SELECT @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
2）使用 SHOW 确认每个表的存储引擎
#查看表的存储引擎 mysql> show create table city\G *************************** 1. row *************************** ... ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show table status like 'city'\G *************************** 1. row *************************** Name: city Engine: InnoDB ...
3）使用 INFORMATION_SCHEMA 确认每个表的存储引擎
#查看表的存储引擎 mysql> select table_name,engine from information_schema.tables where table_name='city' and table_schema='world'\G *************************** 1. row *************************** table_name: city engine: InnoDB 1 row in set (0.00 sec)
4.存储引擎的设置
1）在启动配置文件中设置服务器存储引擎
#在配置文件的[mysqld]标签下添加/etc/my.cnf [mysqld] default-storage-engine=innodb
2）使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置 mysql> SET @@storage_engine=myisam; Query OK, 0 rows affected, 1 warning (0.00 sec) #查看 mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | MyISAM | +--------------------------+ 1 row in set (0.00 sec)
（3）在 CREATE TABLE 语句指定
#建表的时候指定存储引擎 create table t (i INT) engine = ; #如：建test1表,指定存储引擎为myisam mysql> create table test1(id int) engine=myisam; Query OK, 0 rows affected (0.02 sec)
三.真实企业案例
项目背景：
公司原有的架构：一个展示型的网站,LAMT,MySQL5.1.77版本（MYISAM）,50M数据量。
小问题不断：
1.表级锁：对表中任意一行数据修改类操作时,整个表都会锁定,对其他行的操作都不能同时进行。
2.不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。
如何解决：
1、提建议将现有的MYISAM引擎替换为Innodb,将版本替换为5.6.38 1）如果使用MYISAM会产生”小问题”,性能安全不能得到保证,使用innodb可以解决这个问题。 2）5.1.77版本对于innodb引擎支持不够完善,5.6.38版本对innodb支持非常完善了。
2、实施过程和注意要素
1）备份生产库数据（mysqldump）
[root@db01 test]# mysql -uroot -p1 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | student4 | | tlbb2 | +----------------+ 2 rows in set (0.00 sec) #测试环境（先简单创几个存储引擎为myisam） mysql> create table test1(id int) engine=myisam; Query OK, 0 rows affected (0.02 sec) mysql> create table test2(id int) engine=myisam;; Query OK, 0 rows affected (0.00 sec) mysql> create table test3(id int) engine=myisam;; Query OK, 0 rows affected (0.01 sec)
2）准备一个5.6.44版本的新数据库
#先导库,准备一个环境 [root@db01 test]# mysqldump -uroot -p1 -B test >/tmp/full.sql
3）对备份数据进行处理（将engine字段替换）
#方法一 [root@db01 ~]# sed -i 's#ENGINE=MyISAM#ENGINE=InnoDB#g' /tmp/full.sql #方法二 [root@db01 ~]# vim /tmp/full.sql :%s#MyISAM#InnoDB#g
4）将修改后的备份恢复到新库
#方法一 [root@db01 test]# mysql -uroot -p123 -h 10.0.0.52 < /tmp/full.sql
5）应用测试环境连接新库,测试所有功能
#连接 [root@db02 ~]# mysql -uroot -p123 #查看存储引擎 mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
6）停应用,将备份之后的生产库发生的新变化,补偿到新库
7）应用割接到新数据库
项目结果：
*解决了”小问题” *
四.Innodb存储引擎——表空间介绍
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
5.6版本中默认的是独立表空间
1、共享表空间
1）查看共享表空间
#物理查看 [root@db01 ~]# ll /application/mysql/data/ -rw-rw---- 1 mysql mysql 79691776 Aug 14 16:23 ibdata1 #命令行查看 mysql> show variables like '%path%'; +-----------------------+------------------------------------+ | Variable_name | Value | +-----------------------+------------------------------------+ | innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend | | ssl_capath | | | ssl_crlpath | | +-----------------------+------------------------------------+ 3 rows in set (0.00 sec) #查看大小 [root@db01 data]# du -sh ibdata1 76M ibdata1 5.6版本中默认存储:
①系统数据
②undo
③临时表
5.7版本中默认会将undo和临时表独立出来,5.6版本也可以独立,只不过需要在初始化的时候进行配置
2）设置方法
#编辑配置文件 [root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
2、独立表空间
对于用户自主创建的表,会采用此种模式,每个表由一个独立的表空间进行管理
查看独立表空间
#物理查看 [root@db01 ~]# ll /application/mysql/data/world/ -rw-rw---- 1 mysql mysql 688128 Aug 14 16:23 city.ibd #命令行查看 mysql> show variables like '%per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.01 sec)
企业案例
在没有备份数据的情况下,突然断电导致表损坏,打不开数据库。
1）拷贝库目录到新库中（先准备一个和原表结构一样的环境）
[root@db01 data]# tar zcf world1.tgz world/ #传到测试环境 [root@db01 data]# scp world1.tgz 172.16.1.52:/application/mysql/data/ #解压 [root@db02 data]# tar xf world1.tgz
2）启动新数据库
[root@db01 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
3）登陆数据库查看r
#查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | world | +--------------------+ 5 rows in set (0.00 sec)
4）查询表中数据
mysql> select * from city; ERROR 1146 (42S02): Table 'world.city' doesn't exist （表不存在）
5）找到以前的表结构在新库中创建表
mysql> show create table world.city; #删掉外键创建语句 CREATE TABLE `city1` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`) #CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
6）删除表空间文件
#删除表空间文件 mysql> alter table city1 discard tablespace; #在表的物理结构查看 [root@db02 world]# ll total 1000 -rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city1.frm -rw-rw---- 1 mysql mysql 8710 Nov 4 10:26 city.frm -rw-rw---- 1 mysql mysql 589824 Nov 4 10:26 city.ibd
7）拷贝旧表空间文件
[root@db02 world1]# cp city.ibd city1.ibd
8）授权
[root@db01 world]# chown -R mysql.mysql *
9）导入表空间
#查看,会报错 mysql> select * from city1; #导入表空间 mysql> alter table city_new import tablespace; #再次查看 mysql> select * from city1;
改表名
#改表名 mysql> alter table city1 rename city; #再次查看 mysql> show tables; +------------------+ | Tables_in_world1 | +------------------+ | city | | country | | countrylanguage | +------------------+ 3 rows in set (0.00 sec)
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
水平分片表 |
1. 某张表的数据容量超过50GB
2. 某张表的并发事务数超过128个
3. 具有数据关联的表的数据容量加起来会超过单机的存储容量或并发能力（注：单机存储容量默认为2TB 以内,500个并发以内）,例如：A表、B表 、C表 之间通过某个字段关联,也即都存储该关联字段的值
4. 如日志信息表、流水表、基本账户表等
全局表 | 1. 表的数据总容量小于 1G,总行数小于100万（注：已考虑未来增长的最大值） 2. 具有数据更新特征,如：批量INSERT ,极少被修改 3. 该表同水平分片表存在较多的JOIN关联查询或者查询极其频繁 4. 如配置信息表等
垂直分片表 | 1. 表的数据总容量较小,总行数增长可控范围（注：数据行数和数据容量不会有大的变化） 2. 该表同水平分片表关联操作较少或没有（注：无JOIN查询,无事务关联） 5. 垂直分片表的总数据容量不得超过单机的处理能力,垂直分片表的TPS不得超过单机的处理能力（注：TPS小于4000,数据容量小于2TB ） 6. 垂直分片表适用于复杂查询较多的场景,例如：大量子查询,大量JOIN 7. 需要JOIN的垂直分片表安排在同一数据节点
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
触发器是一种特殊的存储过程,它在插入、删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
和存储过程一样,很少使用。
(1)、触发器的作用
1.可在写入数据表前,强制检验或转换数据。
2.触发器发生错误时,异动的结果会被撤销。
3.部分数据库管理系统可以针对数据定义语言（DDL）使用触发器,称为DDL触发器。
4.可依照特定的情况,替换异动的指令 (INSTEAD OF)。
(2)、创建触发器
创建测试环境
mysql> create database test_db; Query OK, 1 row affected (0.00 sec) mysql> use test_db; Database changed mysql> create table user_tb(id int,name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> create table job_tb(uid int,job varchar(20)); Query OK, 0 rows affected (0.04 sec)
创建触发器语句如下：
create trigger [触发器名称] [before|after] [insert|update|delete] on [表名] for each row [触发的动作,一个update或insert或delete的SQL语句;] [新的结束字符]
before和after是触发时间,insert、update和delete是监视事件。
并且因为包含一个触发动作的SQL语句,所以也要使用delimiter [新字符]。
实例
mysql> delimiter // mysql> create trigger insert_job after insert on user_tb for each row -> insert into job_tb values (1,'老师'); -> // Query OK, 0 rows affected (0.00 sec) mysql> insert into user_tb values(1,'张三')// Query OK, 1 row affected (0.00 sec) mysql> select * from user_tb// +------+--------+ | id | name | +------+--------+ | 1 | 张三 | +------+--------+ 1 row in set (0.00 sec) mysql> select * from job_tb// +------+--------+ | uid | job | +------+--------+ | 1 | 老师 | +------+--------+ 1 row in set (0.00 sec)
注意： 触发器与监视表不能是同一个,否则会报1442的错误。
(3)、查看触发器
show create trigger [触发器名称]; 查看指定触发器
show triggers; 查看所有触发器
mysql> show create trigger insert_job\G //查看指定触发器 *************************** 1. row *************************** Trigger: insert_job sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger insert_job after insert on user_tb for each row insert into job_tb values (1,'老师') character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci Created: 2019-06-13 11:51:28.87 1 row in set (0.00 sec) mysql> show triggers\G //查看所有触发器 *************************** 1. row *************************** Trigger: insert_job Event: INSERT Table: user_tb Statement: insert into job_tb values (1,'老师') Timing: AFTER Created: 2019-06-13 11:51:28.87 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: root@localhost character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
(4)、删除触发器
drop trigger [触发器名称];
mysql> drop trigger insert_job// Query OK, 0 rows affected (0.00 sec) mysql> show triggers\G Empty set (0.00 sec)
(5)、扩展
由于触发器其实是一种特殊的存储过程,所以触发动作其实可以使用begin [SQL语句集] end来扩展其使用方法！
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>> Lua基本语法 表类型 函数 Redis执行脚本 KEYS与ARGV 沙盒与随机数 脚本相关命令 原子性和执行时间
Lua是一种高效的轻量级脚本语言,能够方便地嵌入到其他语言中使用。在Redis中,借助Lua脚本可以自定义扩展命令。
Lua基本语法
数据类型 空(nil),没有赋值的变量或表的字段值都是nil 布尔(boolean) 数字(number),整数或浮点数 字符串(string),字符串可以用单引号或双引号表示,可以包含转义字符如\n \r等 表(table),表类型是Lua语言中唯一的数据结构,既可以当数组又可以当字典,十分灵活 函数(function),函数在Lua中是一等值(first-class-value),可以存储在变量中、作为函数的参数或返回结果。
变量
Lua的变量分为全局变量和局部变量,全局变量无需声明就可以直接使用,默认值是nil。 全局变量： a=1 -- 为全局变量a赋值 print(b) -- 无需声明即可使用,默认值是nil
局部变量： local c -- 声明一个局部变量c,默认值是nil local d=1 -- 声明一个局部变量d并赋值为1 local e,f -- 可以同时声明多个局部变量
但在Redis中,为了防止脚本之间相互影响,只允许使用局部变量。
赋值
Lua支持多重赋值,如： local a,b=1,2 --a的值是1,b的值是2 local c,d=1,2,3 --c的值是1,d的值是2,3被舍弃了 local e,f =1 --e的值是1,f的值是nil
操作符 数学操作符,包括常见的+ - * \ %(取模) -(一元操作符,取负)和幂运算符号^。 比较操作符,包括== ~=(不等于) > < >= <=。 比较操作符不会对两边的操作数进行自动类型转换： pring(1=='1') --结果为false print({'a'}=={'a'}) -false,表类型比较的是二者的引用 逻辑操作符 包括下面三个： not,根据操作数的真和假相应地返回false和true； and,a and b中如果a是真则返回b,否则返回a； or,a or b中,如果a是真则返回a,否则返回b。 这些根据操作符短路的原理可以推断出。 print(1 and 5) --5 print(1 or 5) --1 print(not 0) --false print('' or 1) --''
只要操作数不是nil或false,逻辑操作符就认为操作数是真,否则是假。而且即使是0或空字符串也被当作真,所以上面的代码中print(not 0)的结果为false,print('' or 1)的结果为''。 连接操作符 Lua中的连接操作符为'..',用来连接两个字符串。 取长度操作符 print(#'hello') --5
if语句
Lua中if语句的格式为 if condition then ... else if condition then ... else ... end
由于Lua中只有nil和false才认为是假,这里也需要注意避坑,比如Redis中EXISTS命令返回1和0分别表示存在或不存在,类似下面的写法if条件将始终为true： if redis.call('EXISTS','key1') then ...
所以需要写成： if redis.call('EXISTS','key1')==1 then ...
循环语句
Lua中的循环语句有四种形式： while condition do ... end repeat ... until condition for i=初值, 终值, 步长 do ... end
其中步长为1时可以省略。 for 变量1,变量2,...,变量N in 迭代器 do ... end
表类型
表是Lua中唯一的数据结构,可以理解为关联数组,除nil之外的任何类型的值都可以作为表的索引。
表的定义和赋值 -- 表的定义 a={} --将变量a赋值为一个空表 -- 表的赋值 a['field']='value' --将field字段赋值为value print(a.field) --a['field']可以简化为a.field -- 定义的同时赋值 b={ name='bom', age=7 } -- 取值 print(b['age']) print(b.age)
当索引为整数的时候表和传统的数组一样,但需要注意的是Lua的索引是从1开始的。 a={} a[1]='bob' a[2]='daffy'
上面的定义和赋值的过程可以直接简化为： a={'bob','daffy'}
取值： print(a[1])
表的遍历
之前介绍的这种类型的for循环可以用于表的遍历： for 变量1,变量2,...,变量N in 迭代器 do ... end a={'bob','daffy'} for index,value in ipairs(a) do print(index) print(value) end
ipairs用于数组的遍历,index和value分别为元素的索引和值,变量名不是必须为index和value,可以自定义。 或者： for i=1, #a do print(i) print(a[i]) end
通过#a可以去到数组a的长度。
对于非数组的遍历,可以使用pairs b={ name='bom', age=7 } for key,value in pairs(b) do print(key) print(value) end
变量名不是必须为key和value,可以自定义。
函数
函数的定义为： function(参数列表) ... end
实际使用中可以将其赋值给一个局部变量,如： local square=function(num) return num * num end
还可以简化为： local function square(num) return num * num end
如果实参的个数小于形参的个数,则没有匹配到的形参的值为nil；如果实参的个数大于形参的个数,则多出的实参会被忽略。如果希望参数可变,可以用...表示形参。
在脚本中调用Redis命令
在脚本中使用redis.call可以调用Redis命令 redis.call('SET','foo','bar')
redis.call的返回值就是Redis命令的执行结果。针对Redis的不同返回类型,redis.call会将其转换为对应的Lua的数据类型,两者的对应关系为： Redis返回类型 | Lua数据类型 | - 整数回复 | 数字类型 字符串回复 | 字符串类型 多行字符串回复 | 表类型(数组形式) 状态回复 | 表类型(只有一个ok字段存储状态信息) 错误回复 | 表类型(只有一个err字段存储错误信息)
Redis的nil回复会被转换为false。
Lua脚本执行完毕后可以通过return将结果返回给Redis客户端,这是又会将Lua的数据类型转换为Redis的返回类型,过程与上面的表格相反。
redis.pcall函数与redis.call的功能相同,但redis.pcall在执行出错时会记录错误并继续执行,而redis.call则会中断执行。
Redis执行脚本
EVAL
在Redis客户端通过EVAL命令可以调用脚本,其格式为： EVAL 脚本内容 key参数的数量 [key...] [arg...]
例如用脚本来设置键的值,就是这样的： EVAL "return redis.call('SET',KEYS[1],ARGV[1])" 1 foo bar
通过key和arg这两类参数向脚本传递数据,它们的值可以在脚本中分别使用KEYS和ARGV两个表类型的全局变量访问。key参数的数量是必须指定的,没有key参数时必须设为0,EVAL会依据这个数值将传入的参数分别存入KEYS和ARGV两个表类型的全局变量。
EVALSHA
如果脚本比较长,每次调用脚本都将整个脚本传给Redis会占用较多的带宽。而使用EVALSHA命令可以脚本内容的SHA1摘要来执行脚本,该命令的用法和EVAL一样,只不过是将脚本内容替换成脚本内容的SHA1摘要。Redis在执行EVAL命令时会计算脚本的SHA1摘要并记录在脚本缓存中,执行EVALSHA命令时Redis会根据提供的摘要从脚本缓存中查找对应的脚本内容,如果找到了则执行脚本,否则会返回错误：“NOSCRIPT No matching script. Please use EVAL.”。
具体使用时,可以先计算脚本的SHA1摘要,并用EVALSHA命令执行脚本,如果返回NOSCRIPT错误,就用EVAL重新执行脚本。
KEYS与ARGV
前面提到过向脚本传递的参数分为KEYS和ARGV两类,前者表示要操作的键名,后者表示非键名参数。但这一要求并不输强制的,比如设置键值的脚本： EVAL "return redis.call('SET',KEYS[1],ARGV[1])" 1 foo bar
也可以写成： EVAL "return redis.call('SET',ARGV[1],ARGV[2])" 0 foo bar
虽然规则不是强制的,但不遵守这样的规则可能会为后续带来不必要的麻烦。比如Redis 3.0之后支持集群功能,开启集群后会将键发布到不同的节点上,所以在脚本执行前就需要知道脚本会操作哪些键以便找到对应的节点,而如果脚本中的键名没有使用KEYS参数传递则无法兼容集群。
沙盒与随机数
Redis限制脚本只能在沙盒中运行,只允许脚本对Redis的数据进行处理,而禁止使用Lua标准库中与文件或系统调用相关的函数,Redis还通过禁用脚本的全局变量的方式保证每个脚本都是相对隔离、不会互相干扰的。
使用沙盒一方面可保证服务器的安全性,还可确保可以重现（脚本执行的结果只和脚本本身以及传递的参数有关）。
Redis还替换了math.random和math.randomseed函数,使得每次执行脚本时生成的随机数列都相同。如果希望获得不同的随机数序列,可以采用提前生成随机数并通过参数传递给脚本,或者提前生成随机数种子的方式。
集合类型和散列类型的字段是无序的,所以SMEMBERS和HKEYS命令原本会返回随机结果,但在脚本中调用这些命令时,Redis会对结果按照字典顺序排序。
对于会产生随机结果但无法排序的命令,比如SPOP,SRANDMEMBER, RANDOMKEY, TIME,Redis会在这类命令执行后将该脚本状态标记为lua_random_dirty,此后只允许调用只读命令,不允许修改数据库的值,否则会返回错误：“Write commands not allowed after non deterministic commands.”
脚本相关命令
SCRIPT LOAD
EVAL命令会执行脚本,并将脚本计算SHA1、加入到脚本缓存中,如果只是希望缓存脚本而不执行,就可以使用SCRIPT LOAD,返回值是脚本的SHA1结果： > SCRIPT LOAD "return redis.call('SET',KEYS[1],ARGV[1])" "cf63a54c34e159e75e5a3fe4794bb2ea636ee005"
SCRIPT EXISTS
通过SHA1查询某个脚本是否被缓存,可以查询多个SHA1。参数必须是完整的SHA1,而不能像docker只输前几位。返回结果1表示存在。
SCRIPT FLUSH
Redis将脚本加入到缓存后会永久保留,如果要清空缓存可以使用SCRIPT FLUSH。
SCRIPT KILL
用于终止正在执行的脚本
原子性和执行时间
Redis的脚本执行是原子的,脚本执行期间其他命令不会被执行,必须等待上一个脚本执行完成。
但为了防止某个脚本执行时间过长导致Redis无法提供服务（比如陷入死循环）,Redis提供了lua-time-limit参数限制脚本的最长运行时间,默认为5秒钟。当脚本运行时间超过这一限制后,Redis将开始接受其他命令,但为了确保脚本的原子性,新的脚本仍然不会执行,而是会返回“BUSY”错误。
可以打开两个redis-cli实例A和B来验证,首先在A执行一个死循环脚本： EVAL "while true do end" 0
这时在实例B执行GET key1会返回： (error) BUSY Redis is busy running a script. You can only call SCRIPT KILL or SHUTDOWN NOSAVE.
如果按照错误提示,在B执行SCRIPT KILL,这时在实例A的脚本会被终止,并返回： (error) ERR Error running script (call to f_694a5fe1ddb97a4c6a1bf299d9537c7d3d0f84e7): @user_script:1: Script killed by user with SCRIPT KILL...
但如果A已经对Redis的数据做了修改,则SCRIPT KILL无法将其终止,A执行： EVAL "redis.call('SET','foo','bar') while true do end" 0
如果在B尝试KILL脚本,会返回错误： (error) UNKILLABLE Sorry the script already executed write commands against the dataset. You can either wait the script termination or kill the server in a hard way using the SHUTDOWN NOSAVE command.
这时就只能通过SHUTDOWN NOSAVE命令强行终止Redis。SHUTDOWN NOSAVE与SHUTDOWN命令的区别在于,SHUTDOWN NOSAVE将不会进行持久化操作,所有发生在上一次快照后的数据库修改都会丢失！ 【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里？>>>
1. 基本分页
一条select rownum 只能使用 < 和 <= （rownum是在查询语句扫描每条记录时产生）
-- 分页
--- 基本分页
--1.查询所有台账表
select * from t_account
--2.查询所有台账表,含rownum
select rownum r, a.* from t_account a
--3.第一页
select rownum, a.* from t_account a where rownum <=10
--4.第二页 , rownum 在处理每行数据时生产,不能使用大于号>
--select t.* from A t
--where t.r > 10 and t.r <=20
select t.* from ( select rownum r, a.* from t_account a ) t
where t.r > 10 and t.r <=20
分页 + 排序
--- 排序 + 分页
--1. 查询所有 + 排序
select * from t_account order by usenum desc
--2. 结果 --> + rownum
-- select rownum r, t.* from () t
select rownum r , t.* from (
select * from t_account order by usenum desc
) t
--3. 结果 --> 分页条件
-- select * from B t2
-- where t2.r > 10 and t2.r <= 20
select * from (
select rownum r , t.* from (
select * from t_account order by usenum desc
) t
) t2
where t2.r > 10 and t2.r <= 20
2 单行函数
1.1 字符函数
--- 字符函数
--1 字符串拼接 dual
select concat('abc','d') from dual;
select 'abc' || '123' from dual;
--2 首字母大写：将一个单词首字母转换成大写,其他字母转换成小写
select initcap('hEllo') from dual;
--3 找出字符串位置,从1开始计数
select instr('hello','e') from dual;
--4 字符串长度
select length('abcd') from dual;
--5 替换 repacle(字符串, 被替换内容, 替换内容)
select replace('hello hello','e','x') from dual;
--6 截取字符串
-- substr('字符串',起始位置) 从‘起始位置’截取到最后
-- substr('字符串',起始位置,长度)
select substr('abcde',2) from dual;
select substr('abcde',2,2) from dual;
--7 去除两端空格
select length(' abcde ') from dual;
select length( trim(' abcde ') ) from dual;
--8 转换小写
select lower('hEllo') from dual;
--9 转换大写
select upper('hEllo') from dual;
1.2 数值函数
--- 数值函数
--1 ceil 向上取整
select ceil(12.1) from dual;
select ceil(12.9) from dual;
--2 floor 向下取整
select floor(12.1) from dual;
select floor(12.9) from dual;
--3 round 四舍五入
-- round(数值) 对数值四舍五入,没有小数位
-- round(数值,精度) 保留小数位数进行四舍五入
select round(12.1) from dual;
select round(12.9) from dual;
-- 12.35
select round(12.3456,2) from dual;
--4 取模 10 % 3
select mod(10,3) from dual;
--5 截取
-- 12.34
select trunc(12.3456,2) from dual;
1.3 日期函数
--- 日期函数
--1 当前系统时间
select sysdate from dual;
--2 add_months 添加count个月
select add_months(sysdate,-2) from dual;
--3 last_day 本月最后一天
select last_day(sysdate) from dual;
--4 to_date 将字符串转换日期
select to_date('20200401','yyyymmdd') from dual;
select '20200401' from dual;
-- 4月份最后一天
select last_day(to_date('20200401','yyyymmdd')) from dual;
--5 months_between 获得两个日期之间月数
select months_between( to_date('2020-05-31 23:59:59','yyyy-mm-dd hh24:mi:ss') , to_date('20200401','yyyymmdd') ) from dual;
-- 你今年几岁了？
-- 1) 获得出生日期距离今日月数
select months_between( sysdate , to_date('20000624','yyyymmdd') ) from dual;
-- 2) 获得出生日期距离今日年数（含小数位）
select months_between( sysdate , to_date('20000624','yyyymmdd') ) / 12 from dual;
-- 3) 向上取值 19.8 --> 20
select ceil ( months_between( sysdate , to_date('20000624','yyyymmdd') ) / 12 ) from dual;
-- 4) 拼接“岁”字符串
select ceil ( months_between( sysdate , to_date('20000624','yyyymmdd') ) / 12 ) || '岁' from dual;
-- 6 下一个指定日期 next_day(日期, 星期)
-- 星期日为一周的第一天,可以使用1表示
select next_day( sysdate , '星期日') from dual;
select next_day( sysdate , 1) from dual;
-- 7 四舍五入日期格式化
-- yyyy 四舍五入年,返回日期,有效数据是年,不需要考虑月和日
select round( sysdate , 'yyyy') from dual;
-- mm 四舍五入月,返回日期,有效数据是月,不需要考虑日
select round( sysdate , 'mm') from dual;
-- 8 格式化
-- 一年的第一天
select trunc( sysdate , 'yyyy') from dual;
-- 这个月的第一天
select trunc( sysdate , 'mm') from dual;
-- 这个星期的第一天
select trunc( sysdate , 'day') from dual;
1.4 转换函数
---转换函数
-- 1. to_char 将日期转换字符串
-- yyyy 年
-- mm 月
-- dd 日
-- hh 时(12小时制)
-- hh24 时
-- mi 分
-- ss 秒
select to_char(1024) from dual;
select to_char( sysdate , 'yyyy-mm-dd hh:mi:ss') from dual;
select to_char( to_date('2020-05-27 20:27:42', 'yyyy-mm-dd hh24:mi:ss') , 'yyyy-mm-dd hh24:mi:ss') from dual;
-- 2. to_date 将字符串转换日期
-- 3. to_number 将数字字符串转换数字
select to_number('1024') from dual;
select to_number('1024.24') from dual;
1.5 其他函数
--- 其他函数
-- 1 nvl(字段, 如果为null显示数据 )
select nvl(t.usenum,0), t.* from t_account t
-- 2 nvl2(字段, 不为null显示,为null显示)
select nvl2(t.usenum,100, 0), t.* from t_account t;
-- nvl2(t.usenum,t.usenum, 0) 等效 nvl(t.usenum,0)
select nvl2(t.usenum,t.usenum, 0), t.* from t_account t
-- 3 decode 相当于 java switch
select decode(id,1,'哈哈',2,'嘿嘿',3,'嘿嘿嘿') from t_area;
--select decode(sex,1,'男',0,'女') from t_user;
2 分析函数
-- 分析函数--排名
insert into book(bid,title) values(1,'100');
insert into book(bid,title) values(2,'98');
insert into book(bid,title) values(3,'98');
insert into book(bid,title) values(4,'95');
insert into book(bid,title) values(5,'98');
insert into book(bid,title) values(6,'95');
commit;
-- 1 查询所有数据
select * from book;
-- 2 排名跳跃
select rank() over( order by b.title desc ) , b.* from book b;
-- 3 排名连续
select dense_rank() over( order by b.title desc ) , b.* from book b;
-- 4 连续的排序
select row_number() over( order by b.title desc ) , b.* from book b;
|
| | 
.png)
大数据引擎.png)
大数据应用.png)
大数据底层技术.png)
ForeSpider软件
.png)
采集服务.png)
软件学习.png)
智能分析.png)
特征提取.png)
智能计算.png)
数据可视化.png)
数据分析应用.png)
系统集成服务.png)
代码工具.png)
金融方案.png)
制造业&物流.png)
企业数字化.png)
医疗方案.png)
政务方案.png)
实时监测.png)
智能分析.png)
数据智能挖掘.png)
全网自动采集.png)
场景智慧采集.png)
主题识别采集
022-2345 2937
185 2247 0110
business@forenose.com
022-2345 2937
185 2247 0110
business@forenose.com
前嗅大数据
津公网安备12010102001008号