批量处理的万级数据的优化方案 业务场景需求：一个【手机号码】的文本list ，需要导入到数据库中 （并在根据这个号码写入一些XX字段、判断一些手机号的一些校验、一些远程请求调用的服务~）一些棘手的问题查询订单在是否存在这个手机号，存在就标记查询订单是否有多次下单，存在再标记查询用户是否vip用户，是是标记根据“手机号”远程调用【用户微服务】接口等，校验用户的真实身份根据用户取到用户手机号，再把用户信息存储redis上........N次的 （20次的查询IO 操作），还做了Redis的查询读写操作...等等测试结果：测试 ，导致100条数据查询 ，都需要30秒。比如 需要处理1-10W的数据，然后批量添加，每个$v的都需要处理很多的sql 查询的IO操作，还要多次的http请求 跨服务的调用，大部分的耗时都卡在“数据库”层面，导致的。目前优化方案每个查询都尽量走索引、减少回表操作，能合理的用到覆盖索引批量拼接sql 一次处理，分批处理 （错误示例 不要再循环中处理每条inster/update 操作）持续优化方案目前执行的慢，原因是卡在DB的IO操作，每条查询都要走20次的查询！！！需要优化sql的连接池 （配置文件连接池根据业务情况设置大一些）看能不能减少多余的判断操作（DB的操作IO ，减少一条是一条）能不走DB ，就走redis，看能不能全走缓存层，去掉db层最好。如果不行，就试试把一些判断数据用布隆过滤器 去走redis的一级缓存大数据的情况下，如果数据是冷数据，不是热数据不经常操作，可以把数据全部存入clickhouse ，然后再查，速度快的一批，缺点：更新删除操作 重新建立树结构。

为什么要用B+ 树，而不用普通二叉树？ 为什么要用 B+ 树，而不用普通二叉树？可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数。为什么不用普通二叉树？普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。为什么不用平衡二叉树呢？读取数据的时候，是从磁盘读到内存。如果树这种数据结构作为索引，那每查找一次数据就需要从磁盘中读取一个节点，也就是一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B+ 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快。为什么用 B+ 树而不用 B 树呢？B+相比较B树，有这些优势：它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题：每个节点存储更多关键字；路数更多扫库、扫表能力更强如果我们要对表进行全表扫描，只需要遍历叶子节点就可以 了，不需要遍历整棵 B+Tree 拿到所有的数据。B+Tree 的磁盘读写能力相对于 B Tree 来说更强，IO次数更少根节点和枝节点不保存数据区， 所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，IO次数更少。排序能力更强因为叶子节点上有下一个数据区的指针，数据形成了链表。效率更加稳定B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的。Hash 索引和 B+ 树索引区别是什么？B+ 树可以进行范围查询，Hash 索引不能。B+ 树支持联合索引的最左侧原则，Hash 索引不支持。B+ 树支持 order by 排序，Hash 索引不支持。Hash 索引在等值查询上比 B+ 树效率更高。B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

吊打面试官之MySQL面试汇总 MySQL面试汇总数据库三大范式是什么第一范式：每个列都不可以再拆分 ， 也就是确保每一列的原子性；第二范式：在第一范式的基础上，非主键列完全依赖于主键， (有一个唯一标识) ，而不能是依赖于主键的一部分， 也就是说一个表只描述一件事情；第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键 （要求字段没有冗余）。在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。怎么区分三大范式？第一范式和第二范式在于有没有分出两张表第二范式是说一张表中包含了所种不同的实体属性，那么要必须分成多张表，第三范式是要求已经分成了多张表，那么一张表中只能有另一张表中的id（主键），而不能有其他的任何信息（其他的信息一律用主键在另一表查询）。MySQL有哪些数据类型？整数类型 ：tinyit 、 smallint 、mediumint 、int小数类型： float 、double 、decaimal日期类型： year 、time 、date 、datetime文本、二进制类型： char 、varchar 、tinyblob 、blob 、mediumblob 、tinyext 、text 、 mediumtext 、longtext事务相关问题::: tip 事务事务相关的面试问题:::数据库中的事务是什么?事务是数据库执行操作的最小逻辑单元事务可以由一个SQL组成也可以由多个SQL组成 （可以是update/inster/delete的数据修改操作）组成事务的SQL要么全执行成功要么全执行失败 （其中一个失败则全部失败）如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。ACID 四大特性,原子性、隔离性、一致性、持久性。事务的ACID特性事务机制要保持数据的原子性、一致性、隔离性 、持久性 ，这4个特性简称为ACID属性。原子性：一个事务中的所有操作要么全部成功，要么全部失败，事务执行后，不允许停留在中间某个状态。一致性：跟并发读写用户的有关系的，由于InnoDB引擎允许支持并发事务，不管在任何给定的时间，并发事务有多少，事务必须保存运行结果的一致性，不会出现数据的歧义。隔离性：隔离性要求事务不受其他并发事务的影响，默认情况下A事务，只能看到日志中该事务的相关数据。持久性：事务一旦提交，结果便是永久性的，即使发生宕机，仍然可以依靠事务日志完成数据的持久化，恢复之后继续同步redo日志数据。并发带来的问题在并发的情况下，很难保证处理事务的顺序。如果不按事务的顺序修改的话，数据会出现问题，都会出现脏读、不可重复读、幻读。理解数据库的脏读、不可重复读、幻读脏读： 一个事务读取了另一个事务未提交的数据，导致不一致。【事务2未提交】不可重复读： 一个事务前后两次读取的同一数据不一致 【事务2已提交】幻读： 指一个事务两次查询的结果集记录数不一致，返回的记录行数有差异。什么是事务隔离级别？有几种事务隔离级别？为了解决这并发带来的3个问题脏读、幻读、不可重读都，InnnoDB又引用了4种隔离级别机制。相同的数据在不同的隔离级别产生的结果不一样。串行化（顺序读）对事务强制的排序，使各个事务顺序执行，这样就避免了各个之间的相互冲突，从而解决脏读、不可重复读、幻读的问题。由于所有的事务是顺序执行，虽然在这隔离性最高，反而并发性是最差的 ， 所以的事务都是顺序执行并不存在并发。可重复读可以 保证在同一个事务中，多次读取同一数据时返回的结果是相同的，可以避免脏读、不可重复读的问题，但是不能避免幻读的问题。如果在Innodb中利用了Next-key引用下一个键索机制，锁定一个范围，并且锁定记录本身,避免幻读的现象。在这个级别不断可以做到顺序读隔离性，同时事务的并发性做到很好的保证。（INNODB默认使用级别）读已提交保证事务只能看到已经被提交的事务的关联数据的修改，只能避免脏读 ，无法避免不可重复读、幻读的产生。隔离性比较差，但是并发性很高，由于查到都是其他事务修改后的数据，有很多人喜欢这种隔离级别，在并发要求性比较高的场景中。sqlserver、PostGreSQL默认使用的一种隔离级别读未提交如名字一样， 一个事务可以读到另一个事务未提交的数据，因此无法避免脏读、不可重复读、幻读的产生。隔离性最差，同时是并发性最高的的隔离级别。怎么事务的保持一致性呢？就是阻止事务之间相互读取临时数据，比如A转给B10块，那么A账号现在应该是490元，但是事务还没提交，如果有人给A转账100块，把A的余额修改为490+100 也就是590，然后再提交事务。这个时候看A转B的10块，如果说事务回滚了，不去同步数据文件，如果A账户是590块那么这个结果是错误的，正确是应该600块钱 ，因为A转B的没有生效，所以A用户余额是600才对。如果事务之间能相互读取到没有提交的临时数据，就真的凭空少了10块，为了出现不歧义的数据，mysql事务之间的运行是相互隔离的。一个事务不能读写其他事务的日志数据 。并发事务为什么会产生阻塞的原因？不同锁之间的兼容关系，造成的一个事务需要等待另一个事务释放其所占用的资源 继续执行的现象。举例：就像现实中高速路上行驶多辆汽车，当车道突然变窄，所有车都全挤在同一个车道，并且这条车道开的很慢，后面的车则排成很长的队，从而造成堵车。【每辆车】可以看成一个事务，【车道】就是占用的资源。阻塞通常是前面的占用的资源线程执行缓慢，从而阻塞了后面线程的执行。所以阻塞造成严重的性能问题。如何处理事务中的阻塞堵塞往往就是出现性能下降，如果大量的阻塞会导致数据库服务器所可用资源占满， 这样造成数据库服务器无法对外提供服务。终止占用资源的事务 （使用kill命令杀掉阻塞的进程 ，这也是一种方法 【治标不治本】）优化占用资源事务的SQL，使其尽快释放资源，从而避免长时间的堵塞。【优化SQL】InnoDB怎么实现的事务ACID特性？redo log 重做日志用来保证事务的持久性undo log 回滚日志保证事务的原子性undo log+redo log 保证事务的一致性锁（共享、排他）用来保证事务的隔离性undo log 实现如下两个功能：1.实现事务回滚 2.实现MVCCundo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。索引相关问题::: tip 索引相关这是列出索引相关的问题:::索引是什么？索引的作用是什么？索引是一种数据结构,可以帮助我们快速的进行数据的查找。就是书籍的目录一样，就主要的作用是告诉去哪里可以查找到我们所需要的数据，快速的定位到所找的位置。索引有哪些优缺点？优点：可以大大加快数据的检索速度，这也是创建索引的最主要的原因通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。缺点：时间方面： 创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；空间方面：索引需要占物理空间。索引有哪几种类型？主键索引、唯一索引、普通索引、全文索引主键索引：数据列不允许重复，不允许为NULL， 一个表只能有一个主键唯一索引： 数据列表不允许重复，允许为NULL， 一个表允许多个列创建唯一索引普通索引： 基本的索引类型，没有唯一性的限制，允许值为NULL全文索引： 是目前索引引擎使用的关键技术（用于文章多词上）索引是个什么样的数据结构呢?索引的数据结构和具体存储引擎的实现有关, 在 MySQL 中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的 InnoDB 存储引擎的默认索引实现为: B+树索引。innodb索引的实现原理是什么？InnoDB 使用的是聚簇索引，将主键组织到一棵 B+ 树中，而行数据就储存在叶子节点上。若使用"where id =14" 这样的条件查找主键，则按照 B+ 树的检索算法即可查找到对应的叶节点，之后获得行数据。若对 Name 列进行条件搜索，则需要两个步骤：第一步在辅助索引 B+ 树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引 B+ 树种再执行一次 B+ 树检索操作，最终到达叶子节点即可获取整行数据。btree和hash类型的索引有什么不同？首先要知道 Hash 索引和 B+ 树索引的底层实现原理 :hash索引底层就是hash表,进行查找时,调用一次 hash 函数就可以获取到相应的键值,之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据。那么可以看出他们有以下的不同:hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围hash索引不支持使用索引进行排序,原理同上。hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB 的索引没有相关性。hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度 , 而不需要使用hash索引。什么是覆盖索引？简单的说，select 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以 mysql只能用 Btree 索引做覆盖索引。B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，什么是聚簇索引?在 B+ 树的索引中,叶子节点可能存储了当前的 key 值,也可能存储了当前的 key 值以及整行的数据,这就是聚簇索引和非聚簇索引。在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引。如果没有唯一键,则隐式的生成一个键来建立聚簇索引。当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询。在建立索引的时候,都有哪些需要考虑的因素呢?建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合。如果需要建立联合索引的话,还需要考虑联合索引中的顺序。此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力。这些都和实际的表结构以及查询方式有关。联合索引/多列索引的注意事项是什么？MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。(遵循最左侧原则)具体原因为:MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为:先按照 name 排序,如果 name 相同,则按照 age 排序,如果 age 的值也相等,则按照 school 进行排序。当进行查询时,此时索引仅仅按照 name 严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,,,以此类推。因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。导致索引失效的原因有哪些？列参与了数学运算或者函数；如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)；对于多列索引，不符合最左匹配的命中规则；like查询是以%开头；如果直接查比用索引快，那么数据库会自动选择最优方式，不用索引；in 和 not in 也要慎用，否则会导致全表扫描。分库分表相关问题::: tip 分库分表相关问题数据库为什么分表，分库等面试问题:::为什么要分表分库答案很简单： 数据库出现性能瓶颈。用大白话来说就是数据库快扛不住了大量请求阻塞 (大量请求都需要操作数据库，导致连接数不够了，请求处于阻塞状态)SQL 操作变慢 (如果数据库中存在一张上亿数据量的表，一条 SQL 没有命中索引会全表扫描)存储出现问题 (业务量剧增，单库数据量越来越大，给存储造成巨大压力)分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。分库分表包括分库和分表两个部分，在生产中通常包括： 垂直分库、垂直分表 、 水平分库、水平分表 四种方式。简述下什么是垂直分表、垂直分库、水平分表、水平分库？垂直分表：将访问频次低的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中带来提升：为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。水平分表：水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中其目的也是为解决单表数据量大的问题 ，与水平分库的思路类似，不过这次操作的目标是表，商品信息及商品描述被分成了两套表。如果商品ID为双数，将此操作映射至商品信息1表；如果商品ID为单数，将操作映射至商品信息2表。此操作要访问表名称的表达式为 商品信息[商品ID%2 + 1] 。带来提升：优化单一表数据量过大而产生的性能问题避免IO争抢并减少锁表的几率垂直分库：垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。因为数据还是始终限制在一台服务器，库内垂直分表只解决了单一表数据量过大的问题，但没有将表分布到不同的服务器上，因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。它带来的提升是：解决业务层面的耦合，业务清晰能对不同业务的数据进行分级管理、维护、监控、扩展等高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈水平分库：水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。经过垂直分库后，数据库性能问题得到一定程度的解决。但是随着业务量的增长，PRODUCT_DB(商品库) 单库存储数据已经超出预估。粗略估计，它带来的提升是：解决了单库大数据，高并发的性能瓶颈。提高了系统的稳定性及可用性。（稳定性体现在IO冲突减少，锁定减少，可用性指某个库出问题，部分可用）当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平分库了。经过水平切分的优化，往往能解决单库存储量及性能瓶颈。但由于同一个表被分配在不同的数据库，需要额外进行数据操作的路由工作，因此大大提升了系统复杂度。分表分库的场景？垂直分表可以把一个宽表的字段按访问频次、是否是大字段的原则拆分为多个表，这样既能使业务清晰，还能提升部分性能。拆分后，尽量从业务角度避免联查，否则性能方面将得不偿失。垂直分库：可以把多个表按业务耦合松紧归类，分别存放在不同的库，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能，同时能提高整体架构的业务清晰度，不同的业务库可根据自身情况定制优化方案。但是它需要解决跨库带来的所有复杂问题。水平分库：可以把一个表的数据(按数据行)分到多个不同的库，每个库只有这个表的部分数据，这些库可以分布在不同服务器，从而使访问压力被多服务器负载，大大提升性能。它不仅需要解决跨库带来的所有复杂问题，还要解决数据路由的问题(数据路由问题后边介绍)。水平分表：可以把一个表的数据(按数据行)分到多个同一个数据库的多张表中，每个表只有这个表的部分数据，这样做能小幅提升性能，它仅仅作为水平分库的一个补充优化。一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案。在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。说说分表分库带来的复杂性？跨库关联查询分布式事务排序、分页、函数计算问题分布式 ID多数据源（1）跨库关联查询在单库未拆分表之前，我们可以很方便使用 join 操作关联多张表查询数据，但是经过分库分表后两张表可能都不在一个数据库中，如何使用 join 呢？有几种方案可以解决：字段冗余：把需要关联的字段放入主表中，避免 join 操作；数据抽象：通过 ETL 等将数据汇合聚集，生成新的表；全局表：比如一些基础表可以在每个数据库中都放一份；应用层组装：将基础数据查出来，通过应用程序计算组装；（2）分布式事务单数据库可以用本地事务搞定，使用多数据库就只能通过分布式事务解决了。常用解决方案有：基于可靠消息（MQ）的解决方案、两阶段事务提交、柔性事务等。（3）排序、分页、函数计算问题在使用 SQL 时 order by、limit 等关键字需要特殊处理，一般来说采用分片的思路：先在每个分片上执行相应的函数，然后将各个分片的结果集进行汇总和再次计算，最终得到结果。（4）分布式 ID如果使用 Mysql 数据库在单库单表可以使用 id 自增作为主键，分库分表了之后就不行了，会出现 id 重复。常用的分布式 ID 解决方案有：UUID基于数据库自增单独维护一张 ID表号段模式Redis 缓存雪花算法（Snowflake）百度 uid-generator美团 Leaf滴滴 Tinyid（5）多数据源分库分表之后可能会面临从多个数据库或多个子表中获取数据，一般的解决思路有：客户端适配和代理层适配。业界常用的中间件有：shardingsphere（前身 sharding-jdbc）Mycat横向分表和纵向分表,可以分别举一个适合他们的例子吗?横向分表：横向分表是按行分表，假设我们有一张用户表，主键是自增ID且同时是用户的ID.数据量较大,有1亿多条，那么此时放在一张表里的查询效果就不太理想。我们可以根据主键ID进行分表，无论是按尾号分，或者按ID的区间分都是可以的，假设按照尾号0-99分为100个表，那么每张表中的数据就仅有100w，这时的查询效率无疑是可以满足要求的。纵向分表 （水平分表）：纵向分表是按列分表。假设我们现在有一张文章表，包含字段 id-摘要-内容，而系统中的展示形式是刷新出一个列表。列表中仅包含标题和摘要，当用户点击某篇文章进入详情时才需要正文内容。此时，如果数据量大,将内容这个很大且不经常使用的列放在一起会拖慢原表的查询速度，我们可以将上面的表分为两张。id-摘要 ,id-内容。当用户点击详情，那主键再来取一次内容即可。而增加的存储量只是很小的主键字段，代价很小。当然,分表其实和业务的关联度很高。在分表之前一定要做好调研以及benchmark.不要按照自己的猜想盲目操作。hash索引的实现原理是什么？哈希索引的实现基于哈希算法。哈希算法是一种常用的算法，时间复杂度为 O(1) 。它不仅应用在索引上，各个数据库应用中也都会使用。InnoDB存储引擎使用哈希算法来对字典进行查找，哈希碰撞采用转链表解决。所以当hash碰撞过多的时候，查询效率就会降低很多。讲一下你理解的B+树索引是怎么实现的？B+ 树中的B不是代表的二叉（Binary） ，而是代表平衡（Balance），因为 B+ 树是从最早的平衡二叉树演化而来，但是 B+ 树不是一个二叉树。一步一步的迭代二叉树：相当于一个二分查找，二叉查找能大大提升查询的效率，但是极端情况下，二叉树会变成一个线性 链表结构。平衡二叉树：通过旋转让失衡二叉树恢复平衡。缺点是数据量达到几百万后，树的高度会很恐怖，导致搜索 效率不足。其二，存储的数据内容太少，没有很好利用操作系统和磁盘数据交换特性。多路平衡查找树(Balance Tree，也叫B-tree)：B-Tree是为磁盘等外存储设备设计的一种平衡查找树。B-Tree结构的数据可以让系统高效的找到数据所在的 磁盘块。 B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效 率。B+TREE：B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。B+Tree相对于B-Tree有几点不同?非叶子节点只存储键值信息。所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中索引是如何存储在磁盘上的？数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的 B+Tree 示例图在数据库中的实现即为聚集索引，聚集索引的 B+Tree 中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于：辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB 存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。如何设计一个高并发的系统① 数据库的优化，包括合理的事务隔离级别、SQL语句优化、索引的优化② 使用缓存，尽量减少数据库 IO③ 分布式数据库、分布式缓存④ 服务器的负载均衡锁的优化策略① 读写分离② 分段加锁③ 减少锁持有的时间④ 多个线程尽量以相同的顺序去获取资源等等，这些都不是绝对原则，都要根据情况，比如不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。这部分跟面试官谈了很久InnoDB支持的索引类型B+Tree索引自适应HASH索引 （自带，为了优化查询性能而自动建立的）全文索引 （5.7+支持，用于搜索引擎、但是对中文不太友好，用第三方）空间索引 （5.7+自持，点切面空间之间的数据类型，具体项目没用过，了解）什么是B+Tree索引？特点是？B+数是一个平衡的二叉树，每一列的节点到根距离都是相同的，并且所有记录节点都是按键值的大小顺序放在同一层叶子节点上。每个叶子节点之间通过指针来进行连接，方便快速查，这就是典型的B+树存储结构。MyISAM索引跟InnoDB的索引的B+Tree区别对于不同的存储引擎具体实现也不同。比如MYISAM的B+tree索引，叶子节点所指向是数据物理地址，InnoDB叶子节点指向数据行的主键位置。简述在MySQL数据库中MyISAM和InnoDB的区别MyISAM特点：读取速度快，适合读多写少的场景MyISAM缺点：但是它不支持事务，所以为了保证数据的一致性，myisam引擎在写入数据的时候进行锁表。不允许其他的并发写入，但是如果想要连续写入多条数据，想要一起回滚是不可能了, 不具备事务机制，在很多业务中是无法上任的。而且在MyISAM引擎在服务器数据崩溃后，数据维护的难度比Innodb引擎要大，所以这个引擎几乎没人使用了。MyISAM 操作数据都是使用的表锁，你更新一条记录就要锁整个表，导致性能较低，并发不高。当然同时它也不会存在死锁问题。InnoDB引擎：现在是mysql默认引擎，创建数据表没有额外的设置。是5.0后才引用的引擎。InnoDB特点：支持事务机制，可以支持行锁，这样一来并发写入就好很多。适合读多写多的场景使用， 而且综合维护成本比MyISAM低。索引的底层实现原理和优化B+树，经过优化的B+树主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引什么情况下设置了索引但无法使用1、以“%”开头的LIKE语句，模糊匹配2、OR语句前后没有同时使用索引3、使用一些聚合统计的系统函数，比如date_format()、max()4、 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）5、还有一些设置了复合索引，并没有按照最左侧原则进行查询，导致走全表扫描。简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。普通索引普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。主键索引是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。唯一索引普通索引允许被索引的数据列包含重复的值。而唯一索引是根据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性联合索引索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。了解XSS攻击吗？如何防止？XSS是跨站脚本攻击，首先是利用跨站脚本漏洞以一个特权模式去执行攻击者构造的脚本，然后利用不安全的Activex控件执行恶意的行为。使用htmlspecialchars()函数对提交的内容进行过滤，使字符串里面的特殊符号实体化。sql注入的主要特点变种极多，攻击简单，危害极大未经授权操作数据库的数据恶意纂改网页私自添加系统账号或者是数据库使用者账号网页挂木马写出三种以上MySQL数据库存储引擎的名称MyISAM、InnoDB、Memory（存储基于内存中）、.BDB（BerkeleyDB）、Merge、Example、Federated、Archive、CSV、Blackhole、MaxDB 等等十几个引擎。SQL语言包括哪几部分？每部分都有哪些操作关键字？SQL语言包括 数据定义(DDL) 、数据操作(DML) ,数据控制(DCL)和数据查询（DQL） 四个部分。数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等数据操纵：Select ,insert,update,delete,数据控制：grant,revoke数据查询：select数据库锁相关问题对MySQL的锁了解吗当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。什么是数据库事务锁？加锁是实现数据库并发控制的一个非常重要的技术。为了在各个事务之间实现隔离，mysql引入了“锁” 的概念，主要作用 保证一个事务不能被另一个事务正在读取或者正在修改的数据进行修改。举例：比如InnoDB采用是行级锁，删改数据的时候，MySQL会锁住记录。对比MyISAM引擎是采用表级锁，当有数据写入的时候，会把数据表整体锁住，其他事务可以读取数据，但是不能往数据表添加/修改/删除操作，这就带来MyISAM的写入数据并发性能比较差劲。InnoDB行级锁不会锁住整张表，只是锁住某个操作的记录。比如操作update语句修改3条，那么mysql就会把这3条记录锁住，不会锁住其他记录。按照锁的粒度分数据库锁有哪些？分为 行级锁 (INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。行级锁又分为有共享锁 、排它锁各个数据库锁的特点以及问题？行级锁 :行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。表级锁:是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。页级锁 ：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般理解事务表锁、行锁、共享锁、排它锁的概念数据库的增删改(update/insert/delete) 操作默认都会加排他锁，而查询(select)不会加任何锁。基本锁类型：表级锁 （更新表操作，整个表就锁住，不运行其他事务操作）行级锁 （更新表某表操作，整个表不会被锁，只会锁住某一行数据）而行级锁有分为以下2种：共享锁 （对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源,不能写操作）排它锁 （对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作，包括读写操作）详细概念：查询需要对资源加 共享锁（S）默认不加共享锁，手动添加共享锁或者事务级别，只能被持有锁的事务被读取，不能修改。其他事务无法进行修改，加共享锁可以进行读取。数据修改需要对资源加 排它锁（X）只能被持有锁的事务读取和修改，其他事务无法读取或者修改，理解什么是乐观锁、悲观锁？本质上，MySQL的乐观锁与悲观锁 主要都是用来解决并发的场景，避免丢失更新问题。针对读操作的不是数据库自带的锁有分为：乐观锁 （ 数据更新操作，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理,但是提交更新会判断其他人是否有更新）悲观锁 （ 跟乐观锁相反，每次会认为操作会导致冲突，在操作更新数据时，使用了拍它锁来实现，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作）更新锁 （更新锁其实就可以看成排他锁的一种变形，只是它也允许其他人读（并且还允许加共享锁））应用场景乐观锁： 适合读多写少，并发访问大的场景下，提高吞吐量。并且不能解决脏读问题，悲观锁：适合写多读少，访问量不大的场景，因为需要再库中产生额外的开销，每次都“先取锁，再访问” 降低并行性。 用于那些库存增减问题。悲观锁和乐观锁的实现悲观锁的实现方式：悲观锁的实现，依靠数据库提供的锁机制。在数据库中，悲观锁的流程如下：1、在对数据修改前，尝试增加排他锁。2、加锁失败，意味着数据正在被修改，进行等待或者抛出异常。3、加锁成功，对数据进行修改，提交事务，锁释放。4、如果我们加锁成功，有其他线程对该数据进行操作或者加排他锁的操作，只能等待或者抛出异常。乐观锁的实现方式:1、一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数2、当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，3、在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新4、否则重试更新操作，直到更新成功。什么是死锁？为什么会导致死锁？并行执行的多个事务相互之间占有了对方所需要的资源举例：好比信号灯坏了，在十字路口交叉行驶的车辆各不相让，最终全堵在一起，谁也走不了。死锁中的的2个事务，如果一方不让路回滚释放所占的资源 ，让另一个事务先执行的话，也会造成2个事务谁都无法上下执行的情况。好在MySQL可以对死锁的监控进行处理，主动回滚2个事务中的占用资源较少的事务，让另一个事务进行执行，不需要人为进行干预。死锁产生的流程：好比举例，事务1执行了更新用户表某个用户的信息。，然后事务2执行了更新用户订单表，然后重点！反过来 ，事务1去执行更新了用户订单表（这时候就进行堵塞了..因为事务2中占用了更新用户订单表）。因为都事务1和事务2没回滚或者提交。事务2再继续更新，去更新用户表某个用户的信息。【更新这个用户信息实际上被事务1所占有，然后报错，死锁就产生了。。error deadlock found when trying to get lock...】【交叉更新，导致占用资源】除了悲观锁去处理冲突，还有什么方法去实现呢？在高并发的场景，悲伤锁只能线程安全问题，每次修改请求，每个都要请求等待锁，某一些请求可能一直都没抢到这个“锁”。其实最终解决并发还是使用缓存、队列，“先进先出” 这样就不会导致某些请求永远获取不到锁。如何减少并发操作的锁冲突？常见解决死锁的方法？解决方案就是 ： 把复杂的SQL语句，拆分多条简单SQL语句 ，因为执行越复杂的SQL那么耗时就越长，以为锁住的时间也越长，如果改成多条SQL语句，执行速度会块，锁住时间更短。（比如子查询效率很低改成join链表查询，效率提升查询更快，锁时间也减少）如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；怎么处理阻塞和死锁的问题由于事务隔离级别会产生阻塞和死锁，阻塞是时常会发生无法避免，我们所有做的尽量减少阻塞的时间、加快sql的数据处理（对sql进行合理的改写优化）其他相关问题数据库五大约束是什么？primary KEY :设置主键约束；UNIQUE：设置唯一性约束，不能有重复值；DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2NOT NULL：设置非空约束，该字段不能为空；FOREIGN key :设置外键约束。主键是什么，怎么设置主键？主键默认非空，默认唯一性约束，只有主键才能设置自动增长，自动增长一定是主键，主键不一定自动增长；在定义列时设置：ID INT PRIMARY KEY在列定义完之后设置：primary KEY（id）数据库的外键是什么？只有 INNODB 的数据库引擎支持外键。不见已使用基于 mysql 的物理外键，这样可能会有超出预期的后果。推荐使用逻辑外键，就是自己做表设计，根据代码逻辑设定的外键，自行实现相关的数据操作。主键使用自增ID还是UUID?推荐使用自增ID,不要使用UUID。因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可。如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降。总之,在数据量大一些的情况下,用自增主键性能会好一些。字段为什么要求定义为not null?null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。drop、delete与truncate分别在什么场景之下使用？Drop、truncate、delete都是删除操作。但是drop、truncate 属于DDL操作，删除清理后不可回滚恢复，删除动作快drop(删除表和结构) ，truncate（清空表数据） 。delete 则是DML 操作，删除可回滚回复，因为删除的每一条都会记录到BinLog日志中，每次都记录，索引删除的时间也久。总结：不再需要一张表的时候，用 drop想删除部分数据行时候，用 delete，并且带上where子句保留表而删除所有数据的时候用 truncateMySQL中的varchar和char有什么区别?char 的长度是不可变的，而varchar的长度是可变的。定义一个char[10]和varchar[10],如果存进去的是‘abcd’,那么char所占的长度依然为10，除了字符‘abcd’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的，char的存取速度比 varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。char适合存储长度固定的数据，varchar适合存储长度不固定的。varchar(10)和int(10)代表什么含义?varchar 的10代表了申请的空间长度,也是可以存储的数据的最大长度,而 int 的10只是代表了展示的长度,不足10位以0填充。也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示。LEFT JOIN 、RIGHT JOIN、INNER JOIN 区别？LEFT JOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录RIGHT JOIN(右连接)： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录INNER JOIN(内连接)：获取两个表中字段匹配关系的记录UNION、UNION ALL区别？union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；union All：对两个结果集进行并集操作，包括重复行，不进行排序；同一个字段，用 int 还是 char 查询效率高?从效率来说，INT 效率更高。查询速度也和是否建立索引，字段长度占用的空间大小有关系。什么叫视图？游标是什么？视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。什么是存储过程？用什么来调用？存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。如何通俗地理解三个范式？第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。。简述范式化设计优缺点？优点:可以尽量得减少数据冗余，使得更新快，体积小缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化反范式化:优点:可以减少表得关联，可以更好得进行索引优化缺点: 数据冗余以及数据异常，数据得修改需要更多的成本试述视图的优点？1、视图能够简化用户的操作2、视图使用户能以多种角度看待同一数据；3、 视图为数据库提供了一定程度的逻辑独立性；4、视图能够对机密数据提供安全保护。主键、外键和索引的区别？主键：唯一标识一条记录，不能有重复的，不允许为空外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值索引：该字段没有重复值，但可以有一个空值作用：：主键：用来保证数据完整性外键：用来和其他表建立联系用的索引：是提高查询排序的速度特点：主键：主键只能有一个外键：一个表可以有多个外键索引：一个表可以有多个唯一索引SQL语句中‘相关子查询’与‘非相关子查询’有什么区别？子查询：嵌套在其他查询中的查询称之。子查询又称内部，而包含子查询的语句称之外部查询（又称主查询）。所有的子查询可以分为两类，即相关子查询和非相关子查询非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。故非相关子查询比相关子查询效率高char和varchar的区别？以及场景是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：1、char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足。（在检索操作中那些填补出来的空格字符将被去掉）2、在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）。varchar的适用场景:字符串列的最大长度比平均长度大很多字符串很少被更新，容易产生存储碎片使用多字节字符集存储字符串Char的场景: 存储具有近似得长度（md5值,身份证，手机号）,长度比较短小得字符串（因为varchar需要额外空间记录字符串长度），更适合经常更新得字符串，更新时不会出现页分裂得情况，避免出现存储碎片，获得更好的io性能MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？1、设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。2、选择合适的表字段数据类型和存储引擎，适当的添加索引。3、mysql库主从读写分离。4、找规律分表，减少单表中的数据量提高查询速度。5、添加缓存机制，比如memcached，apc,redis等。6、不经常改动的页面，生成静态页面。7、书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.为表中得字段选择合适得数据类型（物理设计）字段类型优先级: 整形>date,time>enum,char>varchar>blob,text优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型MYSQL存储时期的字段？Datatime:以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用8个字节得存储空间，datatime类型与时区无关Timestamp:以时间戳格式存储，占用4个字节，范围小1970-1-1到2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改timestamp列得值Date:（生日）占用得字节数比使用字符串.datatime.int储存要少，使用date只需要3个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算Time:存储时间部分得数据注意:不要使用字符串类型来存储日期时间数据（通常比字符串占用得储存空间小，在进行查找过滤可以利用日期得函数）总结：使用int存储日期时间不如使用timestamp类型你是用物理删除的吗？还是逻辑删除？对于一些日志类型非核心的数据做物理删除，可以减少单表的空间体积，对于核心业务表的数据不建议做物理删除，只做状态的变更，比如订单作废、账号禁用、优惠券作废 等等。而且如何核心业务查询比较频繁，比如有分页之类的数据，会造成主键的不连续，导致分页查询变慢。你数据库考虑用UUID做主键吗？考虑过。但是单机的情况下 还是自增主键，顺序增长，而且是整型查询效率较快。如果是集群的话，就不太考虑UUID，所以UUID是可以解决全局主键冲突，但是比较是字符串类型效率较低，用数据库中间件 生成全局唯一数字主键去时间更快。为了方便咋数据迁移到集群，主键该不该使用UUID？用UUID的目的是为了避免每个MYSQL各自生成的主键重复,但UUD不推荐用在集群上，因为UUID主键是字符串类型，索引效率极低，并且不是自增类型，而且占空间16个字节，单节点过度到集群的主键，可以用 MyCat中间件 ，生成全局主键，也就是说依靠全局的中央节点生成连续的数字作为主键值，全局不会重复的。订单号跟流水号是不是一个回事？订单号既是订单的唯一编号，而且经常被用来检索，所以应当是数字类型的主键流水号是打印在购物单据上的字符串，便于阅读，但是不用作查询比如携带商品类型、收货地、城市身份、发货仓库编号、 特别是高度自动化的系统中，通过扫码包裹流水号就大致得知是那些信息商品了。如果系统上线了，你怎么维护表结构？由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作如果不影响业务的维护操作可以直接操作在线执行修改表结构，先做好备份。= 如果影响业务比较量比较大，就需要用到PT工具操作，可以修改表结构并且不会就行锁表操作。系统内部的业务图片是怎么存储的？使用图床服务器：Nginx或者云存储 。如果更专业，使用MongoDB GridFs搭建分布式的集群图床服务器怎么避免并发操作的时间，数据不一致的问题？锁表能解决问题，但是会极大影响并发操作。所以正确的做法，使用乐观锁的机制，在数据库里加个版本号字段，在更新数据的时候比较版本号就避免数据不一致。MySQL 索引使用什么数据结构？为什么用 B+做索引？使用B+树。大部分程序主要的功能都是对数据的处理，写入、查询、转化、输出。最形象的比喻就是树和内容和目录的关系，目录就是索引，我们根据目录能快速拿到想要内容的页码。为什么是B+树，有这个几个理由：如果是用AVL平衡二叉树，树高度太高，索引查询需要访问磁盘，每次访问以节点为单位进行磁盘I/O ，需要尽量减少数据读取的I/O操作，所以树高度一定不能太高，存储千万级别的数据，实践中 B+ 树的高度也就 4或者5。B+树经常用来比较的是B树，B+树相比B树有个很大的特点是B+树所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的，对于范围查找，比如15～50，B树需要中序遍历二叉树，但是B+树直接在叶子节点顺序访问就可以了。什么是最左匹配原则？最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。MySQL 主从同步怎么做的？binlog清楚吗？Master 数据库只要发生变化，立马记录到Binary log 日志文件Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据

Docker搭建Replication分布式集群 什么是Replication集群Replication集群是MySQL自带的数据同步机制原理：MySQL通过读取、执行另外一个MySQL的bin_log日志，实现数据同步。流程：首先要开启MySQL的二进制文件 （bin_log）我们所使用MySQL的命令操作都会被记录到bin_log日志里。另外一个MySQL通过读取bin_log文件，就知道第一个MySQL节点执行了哪些操作。第二个MySQL操作还会在本地也执行一边，因为执行的操作都是相同的，所以2个MySQL的内容完全相同。这就是replication的数据同步原理。总结：Rep集群异步传输的，数据只要在一个节点写入，就算成功了，至于能不能同步到其他节点Replication不做任何保证。Rep集群数据同步是单向的，只能从主节点数据同步到从节点，不能从从节点同步到主节点。如果业务系统是读多写少非常大。我们可以给一个Master设置多个Slave。所有的读操作发送给Slave从节点执行。写操作 发送给Master主节点执行，就可实现数据读写分离的功能注意： 读写分离功能需要配置MyCat才能实现的实例演示这里先实例演示Replication集群的MyCat路由转发因为本地环境，首先先准备2台服务器，分了4个端口，做为4个虚拟主机，我这边使用Vragnt生成了2台Linux服务器。192.168.205.10 （作为主节点）192.168.205.11 （从节点）一、搭建服务器环境Vgrant搭配2台服务相关配置，不熟悉的同学可以查看下Vgrant官网文档，或者用其他虚拟机搭建都可。创建Vagrantfile 文件# -*- mode: ruby -*- # vi: set ft=ruby : Vagrant.require_version ">= 1.6.0" boxes = [ { :name => "swarm-manager", :eth1 => "192.168.205.10", :mem => "1024", :cpu => "1" }, { :name => "swarm-worker1", :eth1 => "192.168.205.11", :mem => "1024", :cpu => "1" } ] Vagrant.configure(2) do |config| config.vm.box = "centos/7" boxes.each do |opts| config.vm.define opts[:name] do |config| config.vm.hostname = opts[:name] config.vm.provider "vmware_fusion" do |v| v.vmx["memsize"] = opts[:mem] v.vmx["numvcpus"] = opts[:cpu] end config.vm.provider "virtualbox" do |v| v.customize ["modifyvm", :id, "--memory", opts[:mem]] v.customize ["modifyvm", :id, "--cpus", opts[:cpu]] end config.vm.network :private_network, ip: opts[:eth1] end end #config.vm.synced_folder "./labs", "/home/vagrant/labs" # config.vm.provision "shell", privileged: true, path: "./setup.sh" end :::然后在当前Vgrantfilefile同目录下，运行启动vagrant up启动完成，输入ssh xxx进入虚拟主机里vagrant ssh swarm-manager进入主机后，配置下SSH，在外部的ssh终端也可远程连接。（进入虚拟主机是Vgrant角色，我们需要用root角色）切换到root账号 （root默认密码是vagrant） ，进入ssh目录，配置sshd_config ，修改root也可远程登录PermitRootLogin yes #root可登录 PubkeyAuthentication yes PasswordAuthentication yes以上这几个注释解开，并且重启sshd服务systemctl restart sshd这样就可以用xhsell 或iTimer2 、Termius等工具去远程连接了。配置Docker环境下载并安装dockeryum install -y docker因为仓库在国外，有些镜像下载比较慢，我们还需要配置镜像加速curl -sSL https://get.daocloud.io/daotools/set-mirror.sh | sh -s http://f1361db2.m.daocloud.io编辑/ect/docker/daemon.json文件，把结尾的逗号去掉（不然启动会报错）集群MySQL需要分布式部署环境，Docker Swarm技术可以自动把异地的Dcoker虚拟机组成一个局域网我们只需要在主节点里，输入docker swarm init --advertise-addr=192.168.205.10如果我们之前有连接相关的集群，需要强制解散swarm集群（包括从节点也要输入 ），输入：docker swarm leave -f 创建个共享网络，作为MySQL的集群网络docker network create -d overlay --attachable swarm_mysql下载Replication镜像 （Oracle没有提供官网镜像，用第三方封装的镜像）docker pull mishamx/mysql docker tag mishamx/mysql rep docker rmi mishamx/mysql搭建Replication集群环境开放防火墙端口，并且重启docker服务firewall-cmd --zone=public --add-port=9003/tcp --permanent firewall-cmd --zone=public --add-port=9004/tcp --permanent firewall-cmd --reload创建主节点容器docker run -d -p 9003:3306 --name rn1 -e MYSQL_MASTER_PORT=3306 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_REPLICATION_USER=backup -e MYSQL_REPLICATION_PASSWORD=backup123456 -v rnv1:/var/lib/mysql --privileged --net=swarm_mysql rep 创建主节点容器，去客户端尝试连接是否成功？创建从节点容器docker run -d -p 9003:3306 --name rn2 -e MYSQL_MASTER_HOST=rn1 -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_REPLICATION_USER=backup -e MYSQL_REPLICATION_PASSWORD=backup123456 -v rn2:/var/lib/mysql --privileged --net=swarm_mysql rep 注意事项： 从节点需要与主节点同步，没有主节点不能创建从节点也去客户端数据库工具尝试是否连接成功。验证主从节点是否同步先去主节点rn1下，创建一个逻辑库，并且创建一个user表，写入数据， 从节点是否有同步过来？数据果真同步过来了。从节点写入数据，主节点会不会同步数据 ? 答案不会，因为rep集群是单向同步的，数据同步必须是由主节点同步给从节点搭建MyCat环境（读写分离）去官网进行下载最新版或者稳定版的Mycat http://dl.mycat.org.cn/2.0/ ，得到一个mycat.tar.gz包安装JavaJDK镜像，因为安装MyCat需要运行在java环境docker pull adoptopenjdk/openjdk8 docker tag adoptopenjdk/openjdk8 openjdk8 docker rm adoptopenjdk/openjdk8创建java容器docker run -d -it --name mycat1 -v mycat1:/root/server --privileged --net=host openjdk8把Mycat包，解压到java目录下/root/server/开放mycat需要用到的端口firewall-cmd --zone=public --add-port=9066/tcp --permanent #数据处理 （传入sql） firewall-cmd --zone=public --add-port=8066/tcp --permanent #获取mycay运行信息 firewall-cmd --reloadMyCat的主要配置目录查看Mycat目录下的文件，MyCat主要的配置文件，一共有3个重要的文件，分别： server.xml 、schema.xml 、rule.xmlserver.xml ： 配置MyCat端口、全局主键生成方式，虚拟逻辑库和账户Schema.xml ： 配置数据库连接，以及数据表用什么切分规则 （最重要）rule.xml ： 用户定制规则切分MyCat配置 Replication集群，需要把写操作发送给主节点执行，读操作发送给从节点执行。配置server.xml可以配置端口号、账户信息、全局主键方式等等<user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">test</property> </user>需要多个逻辑库就可以设置为<user name="root" defaultAccount="true"> <property name="password">123456</property> <property name="schemas">test,shop,db1,db2</property> </user>MyCat不会保存到真实的库里，只会记录MyCat自己的配置文件。只需要配置<user>段，把多余的user删除，要不然启动报错user name ： 启动的角色property password ： 启动登录的密码property schemas ： 需要用到的库 （根据你实际业务看，我这用test库）配置schema.xml在这个文件里，要对每个MySQL节点的连接信息做设置，MyCat管理那么多个节点，首先要对这个节点做好工作上的划分。把读写任务分别发送给不同的节点执行，可以降低单一节点的负载PXC集群不需要设置读写分离，做好负载均衡即可Replication集群主从节点功能明确，需要做读写分离配置虚拟机逻辑库和关系表<schema>标签可以设置虚拟逻辑库，<table>标签可以设置虚拟关系表<schema name="test"> <table name="admin1" dataNode="dn1,dn2" type="global"></table> <table name="user" dataNode="dn1,dn2" type="mod-long"></table> </schema>user表配置了rule主键求模的切分数据。配置dataHost标签这个标签可以配置数据库集群的。 <dataHost name="rep1" maxCon="1000" minCon="10" balance="3" balanceType="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="r1w1" url="192.168.168.205.10:9003" user="root" password="123456"> <readHost host="r1r1" url="192.168.205.11:9003" password="123456" user="root" /> </writeHost> </dataHost>name ： 取别名maxCon 跟 minCon ： 代表最大连接数和最小连接数balance : 设置3，代表写节点和读节点功能分开，写节点负责写入请求，读节点负责读取请求。writeType : 读写请求是否发送可用的写节点 （1-是），MyCat自带心跳检测功能会想mysql定期发送sql语句，如果某一个节点没有响应了，那么会认为这个节点宕机了，就不会向宕机节点发送请求。dbType ： 代表什么数据库的集群dbDriver ： 使用mysql自带的native驱动SwitchType ： 会根据自身带有的心跳检测结果去判断哪一个mysql节点宕机了？slaveThreshold ： 跟rep集群有关，主从复制同步的，设置100代表100秒，如果从库超过主库的100秒就剔除从节点，不会从这个从节点读取数据。Heartbeat ： 监察心跳，连接是否正常的SQL，有返回响应就代表没宕机。writeHost ： 配置mysql写节点的 （有多少个节点就配置多个标签），图上有2个节点。<readHost> ： writeHost嵌套一个3个<readHost> ，代表3个读节点host： 读节点名称url : 读节点mysql 地址user : 读节点用户Password：读节点密码host ： 是节点的别名url : 节点请求mysql url地址user : 登录用户名Password ： 用户密码注意事项： 读节点一定要放入写节点里，如果有多个读节点，那么第一个读节点不能是闭合< />状态。3、配置多个集群分片配置多个集群分片就复制多个<dataHost> ，相当于多个集群。4、配置虚拟库和关系表因为Mycat并不存储数据，只是SQL语句的路由器，并不默认接管MySQL所有的数据表，所有必须要配置可以使用的虚拟逻辑库和关系表。<dataNode name="dn1" dataHost="rep1" database="test"> <dataNode name="dn2" dataHost="rep2" database="test" />意思就是要使用rep1节点的test数据库，并且起名为dn1。意思就是要使用rep2节点的test数据库，并且起名为dn2。我这用到2个分片，一个作为同步，一个作为切分转发配置rule.xmlMyCat默认的mod-long是按照3个分片切分数据，所以我们要求该这个默认值。设置成2个。<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> </function>全局预览图由于我是用2台服务，用端口作为4个不同的虚拟主机节点。注意查看，我这有2个 dataHost 表示部署2个集群（2个分片），rep1=作为同步的， rep2=作为数据切分的rep1集群：<writeHost>写节点是192.168.205.10:9003 是之前容器生成的主节点。从节点是访问192.168.205.11:9003rep2集群192.168.205.10:9004是主启动MycatMyCat日志文件主要有console.log和mycat.log ，存放在logs目录 （不存在就手动创建）。为MyCat/bin 目录中所有的sh命令设置最高权限。chmod -R 777 ./*.sh启动./startup_nowrap.sh查看是否启动成功ps -a 因为mycat是基于是java， 如果包含java就表示启动成功

利用PerconaTookit工具在线修改表结构 在线修改表结构在线修改表结构必须慎重 ，在业务系统运行的过程中随意修改、删改字段，会造成重大事故，而且在修改之前，必须对表做备份。常规的做法是业务停机，维护表结构。（比如半夜做系统维护更新，12306、淘宝等..）但是不影响正常业务的表结构是运行在线修改的。（比如INT不够用换成BIGINT，或者某个字段有唯一性约束，现在去掉唯一性约束，但是也多多少少也会影响性能 ）ALTER TABLE修改表结构的弊病这些修改表是属于DDL语句由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作锁住表禁止一切的读写，如果给1千万数据表中添加一个字段，这个时间会稍微有点长 ，期间不能做任何读写操作，对线上业务影响非常大，数据越多锁表时间越长。如果修改表结构失败，必须还原表结构，所以耗时更长大数据表记录多，修改表结构锁表时间很久PerconaTookit工具利器Percona Toolkit简称pt工具—PT-Tools，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等。pt-archive ----MySQL的在线归档，无影响生产pt-kill -----自定义查杀。确保慢查询及恶性攻击对生产无影响pt-online-schema-change ----在线DDL操作，对上亿的大表加索引加字段且对生产无影响pt-query-digest -----慢查询Log的分析。pt-slave-delay ---就是指定从库比主库延迟多长时间pt-table-checksum & pt-table-sync-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理 这两个一搬是搭配使用pt-find ---找出几天之前建立的表pt-slave-restart -----主从报错，跳过报错pt-summary ---整个系统的的概述pt-mysql-summary ---MySQL的表述，包括配置文件的描述pt-duplicate-key-checker ---检查数据库重复索引今天我们就用 pt-online-schema-change 进行在线修改表结构。1、安装PerconaTookit依赖包安装第三方依赖包yum install -y perl-DBI yum install -y perl-DBD-mysql yum install -y perl-IO-Socket-SSL yum install -y perl-Digest-MD5 yum install -y perl-TermReadKey2、安装PerconaTookit工具在线下载地址： https://www.percona.com/downloads/percona-toolkit/LATEST/wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm rpm -ivh *.rpm3、PT工具在线修改结构些参数pt-online-schema-change OPTIONS DSN参数实际含义--hostIP地址--user用户名--password密码--port端口号--alert修改语句--execute执行修改--dry-run测试执行--print打印过程t数据表D逻辑库sql举例1： my_shop库中的品牌表custom_address中的name字段varchar(200)改成varchar(20)pt-online-schema-change --host=192.168.10.101 --port=3306 --user=root --password=123456 --alter "MODIFY `name` VARCHAR(20) NOT NULL COMMENT '名称'" D=my_shop,t=t_brand --print --execute最后一个参数execute是真正修改执行，而--dry-run只是模拟执行的意思。4、实例操作修改库表mysql8.0以下无需执行，在执行之前还要执行以下2个sql语句：ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER; ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password by '123456'为什么要执行呢？因为在pt工具中连接mysql数据库用的是老的连接方式，mysql8.0引入了新的密码认证方式，PT工具是暂时不支持的，所以只能在数据库端做手脚，把数据库密码验证方式改成旧的认证方式。再执行：pt-online-schema-change --host=192.168.10.101 --port=3306 --user=root --password=123456 --alter "MODIFY name VARCHAR（20） NOT NULL COMMENT '收货人'" D=my_shop,t=t_customer_address --print --execute出现Successsfully就代表成功了 。再看数据库发现修改成功，过程中都没有锁表。更多PT修改字段操作#添加字段 --alter "add age varchar(10) NOT NULL DEFAULT '' COMMENT '年龄'" #修改默认值 -alter status set DEFAULT '0' #删除列 drop age 反正跟mysql的DDL操作语句一样在线修改表结构的原理怎么在不锁表正常修改表结构的呢？比如我现在想修改订单表的结构。PT工具先拷贝订单表结构，创建一个新的数据表。然后再新表上做结构修改再之后PT程序会在原来的表上创建触发器，（只要原来表有数据修改、添加、删除）动作就会同步执行到新的订单表还会把原表的数据拷贝到新表上这样双管齐下，数据同步后并且原订单没有写入的时候，把原表删除，把新名改回原表。全称没有锁表，没有耽误数据的读写操作。

熟悉mysql的共享锁、排它锁、悲观锁、乐观锁以及使用场景 sql前文数据库的增删改(update/insert/delete) 操作默认都会加排他锁，而查询(select)不会加任何锁。表级锁（锁定整个表）页级锁（锁定一页）行级锁（锁定一行）共享锁（S锁，MyISAM 叫做读锁）排他锁（X锁，MyISAM 叫做写锁）悲观锁（抽象性，不真实存在这个锁）乐观锁（抽象性，不真实存在这个锁）*共享（S)锁：多个事务可封锁一个共享页；任何事务都不能修改该页； 通常是该页被读取完毕，S锁立即被释放。排它（X)锁：仅允许一个事务封锁此页；其他任何事务必须等到X锁被释放才能对该页进行访问；X锁一直到事务结束才能被释放。更新（U)锁：用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的页将要被更新时，则升级为X锁；U锁一直到事务结束时才能被释放。先了解InnoDB与MyISAMMysql 在5.5之前默认使用 MyISAM 存储引擎，之后使用 InnoDB 。查看当前存储引擎：show variables like '%storage_engine%';MyISAM 操作数据都是使用的表锁，你更新一条记录就要锁整个表，导致性能较低，并发不高。当然同时它也不会存在死锁问题。而 InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。在 Mysql 中，行级锁并不是直接锁记录，行锁，由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql 语句操作了主键索引，Mysql 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。InnoDB 行锁是通过给索引项加锁实现的，如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表锁一样。因为没有了索引，找到某一条记录就得扫描全表，要扫描全表，就得锁定表。共享锁(Share Lock)共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。白话文：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后用法SELECT ... LOCK IN SHARE MODE;在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。排他锁（EXclusive Lock）排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。白话文：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作用法SELECT ... FOR UPDATE;在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。共享锁跟排它锁的场景例子这里用T1代表一个数据库执行请求，T2代表另一个请求，也可以理解为T1为一个线程，T2 为另一个线程。例子1：共享锁、排他锁并执行T1： （假设查询会花很长时间，下面的例子也都这么假设）select * from table lock in share modeT2：update table set column1='hello'过程：T1运行（并加共享锁)T2运行If T1还没执行完T2等......else 锁被释放T2执行end ifT2 之所以要等，是因为 T2 在执行 update 前，试图对 table 表加一个排他锁，而数据库规定同一资源上不能同时共存共享锁和排他锁。所以 T2 必须等 T1 执行完，释放了共享锁，才能加上排他锁，然后才能开始执行 update 语句。例子2：2条共享锁并执行T1:select * from table lock in share modeT2:select * from table lock in share mode这里T2不用等待T1执行完，而是可以马上执行。分析：T1运行，则 table 被加锁，比如叫lockA，T2运行，再对 table 加一个共享锁，比如叫lockB，两个锁是可以同时存在于同一资源上的（比如同一个表上）。这被称为共享锁与共享锁兼容。这意味着共享锁不阻止其它人同时读资源，但阻止其它人修改资源。例子3： 2条共享锁跟排他锁并执行T1：select * from table lock in share modeT2:select * from table lock in share modeT3:update table set column1='hello'T2 不用等 T1 运行完就能运行，T2跟T1都是共享锁兼容可以一并执行，T3 却要等 T1 和 T2 都运行完才能运行。因为 T3 必须等 T1 和 T2 的共享锁全部释放才能进行加排他锁然后执行 update 操作。（T3是update语句是加排他锁）例子4： 死锁产生T1：begin tran select * from table lock in share mode update table set column1='hello'T2:begin tran select * from table lock in share mode update table set column1='world'假设 T1 和 T2 同时达到 select，T1 对 table 加共享锁，T2 也对 table 加共享锁 。当 T1 的 select 执行完，准备执行 update 时,根据锁机制，T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前，必须等 table 上的其它共享锁（T2）释放，同理，T2 也在等 T1 的共享锁释放。于是死锁产生了。T1等T2共享锁释放，T2等T1的共享锁释放，互相等待..导致死锁，互相占用资源。解决方案：就是其中一个释放资源或者KILL掉，排查下哪里的SQL互相占用资源例子5：2条排他锁执行并发T1：begin tran update table set column1='hello' where id=10T2:begin tran update table set column1='world' where id=20这种语句虽然最为常见，很多人觉得它有机会产生死锁，但实际上要看情况。答案：不会产生死锁，因为InnoDB是行级锁，只能对主键索引进行锁住，2个操作都不是同一行。如果id是主键（默认有主键索引），那么T1会一下子找到该条记录(id=10的记录），然后对该条记录加排他锁。T2，同样，一下子通过索引定位到记录，然后对id=20的记录加排他锁，这样T1和T2各更新各的，互不影响。T2也不需要等。如果id是普通的一列，没有索引。那么当T1对id=10这一行加排他锁后，T2为了找到id=20，需要对全表扫描。但因为T1已经为一条记录加了排他锁，导致T2的全表扫描进行不下去（其实是因为T1加了排他锁，数据库默认会为该表加意向锁，T2要扫描全表，就得等该意向锁释放，也就是T1执行完成），就导致T2等待。例子6：死锁解决T1：begin tran select * from table for update update table set column1='hello'T2:begin tran select * from table for update update table set column1='world'这样，当 T1 的 select 执行时，加上for update 直接对表加上了排他锁，T2 在执行 select 时，就需要等 T1 事物完全执行完才能执行。排除了死锁发生。但当第三个 user 过来想执行一个查询(select)语句时，也因为排他锁的存在而不得不等待，第四个、第五个 user 也会因此而等待。在大并发情况下，让大家等待显得性能就太友好了。所以，有些数据库这里引入了更新锁（如Mssql，注意：Mysql不存在更新锁）。更新锁（Update Lock）T1：begin tran select * from table [加更新锁操作] update table set column1='hello'T2:begin tran select * from table [加更新锁操作] update table set column1='world'更新锁其实就可以看成排他锁的一种变形，只是它也允许其他人读（并且还允许加共享锁）。但不允许其他操作，除非我释放了更新锁。T1 执行 select，加更新锁。T2 运行，准备加更新锁，但发现已经有一个更新锁在那儿了，只好等。当后来有 user3、user4...需要查询 table 表中的数据时，并不会因为 T1 的 select 在执行就被阻塞，照样能查询，相比起例6，这提高了效率。后面还有意向锁和计划锁：计划锁，和程序员关系不大，就没去了解。意向锁（innodb特有）分意向共享锁和意向排他锁。意向共享锁：表示事务获取行共享锁时，必须先得获取该表的意向共享锁；意向排他锁：表示事务获取行排他锁时，必须先得获取该表的意向排他锁；我们知道，如果要对整个表加锁，需保证该表内目前不存在任何锁。因此，如果需要对整个表加锁，那么就可以根据：检查意向锁是否被占用，来知道表内目前是否存在共享锁或排他锁了。而不需要再一行行地去检查每一行是否被加锁。乐观锁和悲观锁首先说明，乐观锁和悲观锁都是针对读（select）来说的。概念乐观锁：乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。悲观锁：与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。案例场景：某商品，用户购买后库存数应-1，而某两个或多个用户同时购买，此时三个执行程序均同时读得库存为“n”，之后进行了一些操作，最后将均执行update table set 库存数=n-1，那么，很显然这是错误的。解决：使用悲观锁（其实说白了也就是排他锁）程序A在查询库存数时使用排他锁 select * from table where id=10 for update然后进行后续的操作，包括更新库存数，最后提交事务。程序B在查询库存数时，如果A还未释放排他锁，它将等待……程序C同B……（也等待中..）使用乐观锁（靠表设计和代码来实现）一般是在该商品表添加version版本字段或者timestamp时间戳字段程序A查询后，执行更新变成了：update table set num=num-1 where id=10 and version=23这样，保证了修改的数据是和它查询出来的数据是一致的（其他执行程序肯定未进行修改）。当然，如果更新失败，表示在更新操作之前，有其他执行程序已经更新了该库存数，那么就可以尝试重试来保证更新成功。为了尽可能避免更新失败，可以合理调整重试次数（阿里巴巴开发手册规定重试次数不低于三次）。区别总结：对于以上，可以看得出来乐观锁和悲观锁的区别：悲观锁：悲观锁实际使用了排他锁来实现（select **** for update）。文章开头说到，innodb加行锁的前提是：必须是通过索引条件来检索数据，否则会切换为表锁。因此，悲观锁在未通过索引条件检索数据时，会锁定整张表。导致其他程序不允许“加锁的查询操作”，影响吞吐。故如果在查询居多的情况下，推荐使用乐观锁。“加锁的查询操作” ：加过排他锁的数据行在其他事务中是不能修改的，也不能通过for update或lock in share mode的加锁方式查询，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。乐观锁：乐观锁更新有可能会失败，甚至是更新几次都失败，这是有风险的。所以如果写入居多，对吞吐要求不高，可使用悲观锁。也就是一句话：读用乐观锁，写用悲观锁。

MySQL如何优化超大的分页查询？ 前文在你开发程序的时候，使用 LIMIT 子句做分页查询可能是非常频繁的，这是一个非常常见的业务场景。那你在使用 limit 子句的时候有没有遇到过什么问题呢，比如说性能不好？曾经遇到过不少由于分页查询性能差，需要优化的案例。那常见的原因基本上也都是分页过多。过大的分页查询为什么会慢？下面我们先构造一个例子：/**生成3百万行的测试数据**/ CREATE TABLE `limit_optimize_tbl` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account` varchar(50) NOT NULL, `order_id` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2000002 DEFAULT CHARSET=utf8 create index idx_account on limit_optimize_tbl(account); create index idx_order_id on limit_optimize_tbl(order_id); DELIMITER CREATE PROCEDURE limit_optimize_test() BEGIN DECLARE i INT; SET i=1000000; WHILE i<=3000000 DO INSERT INTO limit_optimize_tbl(account,order_id) VALUES('test_123',concat('order', i)); SET i=i+1; END WHILE; END DELIMITER ; call limit_optimize_test();下面的这行 SQL 是一个常见的分页查询的语句：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;这种写法是最简单的，但同时也是最容易出问题的。曾经有人做过调查，用户在浏览前端页面时，假如这个页面是分页浏览的（例如淘宝），用户只会浏览前面几页，一般翻页超过 10 页很多人就开始表现的不耐烦了。在翻页比较少的情况下，LIMIT 子句并不会表现出性能问题。但是假如用户要直接跳到最后一页呢？通常情况下，由于要保证所有的页面都可以正常跳转，因此可能不会使用如下这种语句：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;而是继续采用正序顺序做分页查询：mysql> select * from limit_optimize_tbl order by order_id limit 1000000,10;采用这种 SQL 查询的话，此时从 MySQL 中取出这 10 行数据的代价是非常大的，需要先排序出前面 1000010 条记录，然后抛弃前面的 1000000 条。查询数据和排序的代价非常高。我们再来看一下上面这个 SQL 语句的执行计划：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 1000000,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec)从执行计划中你可以看到，在大分页的时候，MySQL 并没有走索引扫描，而是使用了全表扫描的方式。那这又是为什么呢？MySQL 数据库采用了基于代价的查询优化器，而查询代价的估算是基于 CPU 代价和 IO 代价。由于现在机械硬盘还没有被完全淘汰掉，因此在类似这种局部扫描的动作中，随机 IO 的代价，仍然被MySQL 的查询优化器认为是非常高的。对于局部扫描，MySQL 会根据数据量的情况和数据获取的条件，去做代价估算，决定是采用顺序扫描还是随机读取存储系统。如果 MySQL 在查询代价估算中，认为采取顺序扫描方式比局部随机扫描的效率更高的话，就会放弃索引，转向顺序扫描的方式。这就是为什么在大分页中 MySQL 数据库走了全表扫描的原因。下面我们还是使用刚刚的 SQL 语句，再来实验一下：mysql> explain select * from limit_optimize_tbl order by order_id limit 5660,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 5670 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from limit_optimize_tbl order by order_id limit 5661,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1994816 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) 从上面的实验你可以看到2条SQL，在这个临界点上，MySQL 分别采用了索引扫描和全表扫描的查询优化方式。你可以自行运行下这两个 SQL 语句，比较下执行时间。由于 MySQL 的查询优化器的算法核心我们是无法人工干预的，因此我们的优化思路应该着眼于如何让分页维持在最佳的效率区间。下面我们就来看下如何优化。优化可以从两个角度进行分析：SQL 改写优化业务角度优化。SQL改写优化方案1：索引覆盖由于索引是有序的，因此这种优化方式的思路是直接在索引上完成排序和分页的操作。先来说说什么是覆盖索引。如果一个 SQL 语句，通过索引即可直接获取查询结果，而不再需要回表查询，就称这个索引覆盖了这条SQL 语句。也就是平时所说的不需要回表操作。在 MySQL 数据库中使用执行计划查看，如果 extra 这一列中显示 Using index ，就表示这条 SQL 语句使用了覆盖索引。下面我们看下刚刚的那条 SQL 语句：mysql> explain select * from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select order_id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec) mysql> explain select id from limit_optimize_tbl order by order_id limit 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: limit_optimize_tbl partitions: NULL type: index possible_keys: NULL key: idx_order_id key_len: 302 ref: NULL rows: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 sec)从这个实验中可以看到，除了 select * 的那条 SQL 语句，其他两个都使用了覆盖索引。你也可以对比一下使用了覆盖索引的大分页和和没有使用覆盖索引的性能差异。在我的环境中执行实验中的 “LIMIT 1000000,10” 的分页查询，没有使用覆盖索引的 SQL 语句执行时间是 2.51s使用了覆盖索引的 SQL 语句执行时间是 0.16s优化效果还是非常明显的。方案2：子查询优化由于在 SELECT 语句中我们很少会只查询某一两个列，因此上述覆盖索引的适用范围就比较有限。可以通过将分页的 SQL 语句改写成子查询的方法获得性能的提升。示例如下：mysql> select * from limit_optimize_tbl where id >= (select id from limit_optimize_tbl order by order_id limit 1000000,1) limit 10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.16 sec)执行时间和上一节的使用了覆盖索引的 SQL 语句基本一致。不知道你有没有观察到，这种优化方法也有其局限性：首先，分页的数据必须是连续的其次，WHERE 子句里面不能再添加别的条件方案3：延迟关联和上述子查询的做法类似，我们也可以使用 JOIN 的语法，先在索引上完成分页的操作，然后再回表获取需要的数据列。示例如下：mysql> select a.* from limit_optimize_tbl a inner join (select id from limit_optimize_tbl order by order_id limit 1000000,10) b on a.id=b.id; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.15 sec)你可以和上一小节的子查询优化的方式做个对比，在采用了 JOIN 语法改写之后，上面的两个限制都解除了，并且 SQL 的执行效率没有损失方案4： 记录书签和上述使用覆盖索引的思路不同，记录书签的优化思路是使用书签记录上一页数据的位置，下次分页时直接从这个书签的位置开始扫描，从而避免 MySQL 扫描大量的数据行再丢弃的操作。示例如下：mysql> select * from limit_optimize_tbl where id>=1000001 limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.00 sec)从上面的 SQL 语句你可以看到，由于使用了主键索引来做分页的操作，SQL 语句的性能是极佳的。使用其他列做书签也是可以的：mysql> select * from limit_optimize_tbl where order_id>='order2000000' limit 0,10; + | id | account | order_id | + | 1000001 | test_123 | order2000000 | | 1000002 | test_123 | order2000001 | | 1000003 | test_123 | order2000002 | | 1000004 | test_123 | order2000003 | | 1000005 | test_123 | order2000004 | | 1000006 | test_123 | order2000005 | | 1000007 | test_123 | order2000006 | | 1000008 | test_123 | order2000007 | | 1000009 | test_123 | order2000008 | | 1000010 | test_123 | order2000009 | + 10 rows in set (0.01 sec)这里要给你提个醒，如果没有使用主键索引或唯一索引做这个书签，排序的字段有大量重复值的情况下，输出的结果不一定是准确的，不适合使用这种写法。方案5： 反向查找反向查找即我们在本文的开头提到的例子：mysql> select * from limit_optimize_tbl order by order_id desc limit 0,10;这种优化的思路来自于二分查找，也就是说，当偏移量超过记录数的一半时，就可以使用这种写法来获得性能的提升不过这种方法需要在分页前知道符合条件的总的记录条数，但是在 InnoDB 存储引擎中，COUNT (*) 的开销其实也不小。因此建议你仅在一些特殊情况下选用，例如直接跳到尾页。业务角度优化下面我们一起来看看从业务角度如何优化大分页查询。其实这个优化思路要看具体的业务内容，业务是千变万化的，因此本文中提到的这几个案例，不一定就适合你们公司的业务类型，只是起个抛砖引玉的作用。翻页限制不允许翻过多的页 , 一言以蔽之，就是不给你查了。把 LIMIT 分页的偏移量做一个限制，超过某个阈值就停止。我们以淘宝网为例，使用比较热门的 “男鞋” 的关键词进行搜索，网站仅仅提供了 100 个数据页。很多大型互联网公司由于数据量巨大，都有使用这种方法。流式分页这种分页方式比较适用于移动端，即只能一页一页的向前或向后加载，不提供跳转的功能。可以在上一级入口中提供业务列表给用户选择，从而减少分页。这种分页方式在电商和新闻类 APP 上应用的非常广泛，你也可以试试。总结总的来说，大分页的优化思路就是让分页的SQL语句尽量在最佳的性能区间内执行，不要触发全表顺序扫描，也不要扫描太多的数据行。SQL 的优化有两个方向，一个是 SQL 语义的优化，另一个是从业务角度对 SQL 语句进行优化。SQL 语义的优化所能发挥的功力有限，在 SQL 优化的工作中大概只有3成的SQL 能通过 SQL 的改写完成优化；而从业务角度的调优占了有 7 成。因此在对 SQL 语句进行优化时，不妨多从业务角度入手，想想看有没有好的解决方案。参考资料《数据库查询优化器的艺术》，李海翔著《高性能 MySQL》第三版慕课专栏-MySQL 开发高频面试题精选

InnoDB的几种事务隔离级别 前文首先理解事务的ACID的隔离性 并发带来的问题，脏读 、不可重复读 、幻读 的问题。脏读： 一个事务读取了另一个事务未提交的数据，导致数据不一致。【事务2未提交】不可重复读： 一个事务前后两次读取的同一数据不一致 【事务2已提交】幻读： 一个事务两次查询的结果集记录数不一致，返回的记录行数有差异不可重复读跟脏读的区别不可重读就是第二个事务提交了对数据的修改，脏读是在第二个事务未提交回滚了对数据的修改。虽然在不可重复读前后数据不一样，由于事务2最终提交了修改，在事务1第二次读到数据，实际上是有效的。而不像脏读 事务2最终是回滚对数据修改，导致事务1第二次读取数据是无效的数据。所以说不可重复读是还可以接受的。InnoDB的几种事务隔离级别支持4中隔离级别，一个事务对另一个事务并发进行修改时，可以相互影响的程度。隔离级别脏读不可重复读幻读隔离性并发性串行化（SERIALIZABLE）NNN最高最低可重复读（PEREATABLE READ）NNY/N 读以提交（READ COMMITTED）NYY 读未提交（READ UNCOMMITTED）YYY最低最高4.1 串行化（顺序读）对事务强制的排序，使各个事务顺序执行，这样就避免了各个之间的相互冲突，从而解决脏读、不可重复读、幻读的问题。由于所有的事务是顺序执行，虽然在这隔离性最高，反而并发性是最差的 ， 所以的事务都是顺序执行并不存在并发。4.2 可重复读可以 保证在同一个事务中，多次读取同一数据时返回的结果是相同的，可以避免脏读、不可重复读的问题，但是不能避免幻读的问题。如果在Innodb中利用了Next-key引用下一个键索机制，锁定一个范围，并且锁定记录本身,避免幻读的现象。在这个级别不断可以做到顺序读隔离性，同时事务的并发性做到很好的保证。（INNODB默认使用级别）4.3 读以提交保证事务只能看到已经被提交的事务的关联数据的修改，只能避免脏读 ，无法避免不可重复读、幻读的产生。隔离性比较差，但是并发性很高，由于查到都是其他事务修改后的数据，有很多人喜欢这种隔离级别，在并发要求性比较高的场景中。sqlserver、PostGreSQL 默认使用的一种隔离级别4.4 读未提交如名字一样， 一个事务可以读到另一个事务未提交的数据，因此无法避免脏读、不可重复读、幻读的产生。隔离性最差，同时是并发性最高的的隔离级别。小结对事务来说，在不同的隔离级别下所执行的结果也不一样，隔离级别越高，数据的完整性一致性越好，同时并发性也越差。session：当前会话，也就是当前连接立即生效。global：全局，不包含当前连接，之后新获取的连接都会生效。修改（当前会话）事务隔离级别set session TRANSACTION ISOLATION LEVEL [SERIALIZABLE | PEREATABLE READ | READ COMMITTED | READ UNCOMMITTED ] #修改隔离级别查看事务里级别show variables like "%iso%" #查看事务隔离级别