数据库精选60道面试题

　　大家好，我是贺同学。金三银四到了，给大家整理一些数据库必知必会的面试题。
　　基础相关1、关系型和非关系型数据库的区别？
　　关系型数据库的优点容易理解，因为它采用了关系模型来组织数据。可以保持数据的一致性。数据更新的开销比较小。支持复杂查询（带where子句的查询）
　　非关系型数据库（NOSQL）的优点无需经过SQL层的解析，读写效率高。基于键值对，读写性能很高，易于扩展可以支持多种类型数据的存储，如图片，文档等等。扩展（可分为内存性数据库以及文档型数据库，比如Redis，MongoDB，HBase等，适合场景：数据量大高可用的日志系统地理位置存储系统）。2、详细说一下一条MySQL语句执行的步骤
　　Server层按顺序执行SQL的步骤为：客户端请求连接器（验证用户身份，给予权限）查询缓存（存在缓存则直接返回，不存在则执行后续操作）分析器（对SQL进行词法分析和语法分析操作）优化器（主要对执行的SQL优化选择最优的执行方案方法）执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）索引相关3、MySQL使用索引的原因？
　　根本原因索引的出现，就是为了提高数据查询的效率，就像书的目录一样。对于数据库的表而言，索引其实就是它的目录。
　　扩展创建唯一性索引，可以保证数据库表中每一行数据的唯一性。帮助引擎层避免排序和临时表将随机IO变为顺序IO，加速表和表之间的连接。4、索引的三种常见底层数据结构以及优缺点
　　三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。哈希表这种适用于等值查询的场景，比如memcached以及其它一些NoSQL引擎，不适合范围查询。有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。N叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。扩展（以InnoDB的一个整数字段索引为例，这个N差不多是1200。棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。）5、索引的常见类型以及它是如何发挥作用的？
　　根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的整行数据，在InnoDB里也被称为聚簇索引。非主键索引叶子节点存的主键的值，在InnoDB里也被称为二级索引。6、MyISAM和InnoDB实现B树索引方式的区别是什么？InnoDB存储引擎：B树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。MyISAM存储引擎：B树索引的叶子节点保存数据的物理地址，叶节点的data域存放的是数据记录的地址，索引文件和数据文件是分离的。7、InnoDB为什么设计B树索引？
　　两个考虑因素：InnoDB需要执行的场景和功能需要在特定查询上拥有较强的性能。CPU将磁盘上的数据加载到内存中需要花费大量时间。
　　为什么选择B树：哈希索引虽然能提供O（1）复杂度查询，但对范围查询和排序却无法很好的支持，最终会导致全表扫描。B树能够在非叶子节点存储数据，但会导致在查询连续数据可能带来更多的随机IO。而B树的所有叶节点可以通过指针来相互连接，减少顺序遍历带来的随机IO。普通索引还是唯一索引？由于唯一索引用不上changebuffer的优化机制，因此如果业务可以接受，从性能角度出发建议你优先考虑非唯一索引。8、什么是覆盖索引和索引下推？
　　覆盖索引：在某个查询里面，索引k已经覆盖了我们的查询需求，称为覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
　　索引下推：MySQL5。6引入的索引下推优化（indexconditionpushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。9、哪些操作会导致索引失效？对索引使用左或者左右模糊匹配，也就是likexx或者likexx这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。对索引进行函数对索引进行表达式计算，因为索引保持的是索引字段的原始值，而不是经过函数计算的值，自然就没办法走索引。对索引进行隐式转换相当于使用了新函数。WHERE子句中的OR语句，只要有条件列不是索引列，就会进行全表扫描。10、字符串加索引直接创建完整索引，这样可能会比较占用空间。创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题。创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。日志相关11、MySQL的changebuffer是什么？当需要更新一个数据页时，如果数据页在内存中就直接更新；而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在changebuffer中。这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行changebuffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。注意唯一索引的更新就不能使用changebuffer，实际上也只有普通索引可以使用。适用场景：对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时changebuffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在changebuffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了changebuffer的维护代价。12、MySQL是如何判断一行扫描数的？MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条。而只能根据统计信息来估算记录数。这个统计信息就是索引的区分度。13、MySQL的redolog和binlog区别？
　　14、为什么需要redolog？redolog主要用于MySQL异常重启后的一种数据恢复手段，确保了数据的一致性。其实是为了配合MySQL的WAL机制。因为MySQL进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在crash后内存数据丢失的隐患，而redolog具备crashsafe的能力。15、为什么redolog具有crashsafe的能力，是binlog无法替代的？
　　第一点：redolog可确保innoDB判断哪些数据已经刷盘，哪些数据还没有redolog和binlog有一个很大的区别就是，一个是循环写，一个是追加写。也就是说redolog只会记录未刷盘的日志，已经刷入磁盘的数据都会从redolog这个有限大小的日志文件里删除。binlog是追加日志，保存的是全量的日志。当数据库crash后，想要恢复未刷盘但已经写入redolog和binlog的数据到内存时，binlog是无法恢复的。虽然binlog拥有全量的日志，但没有一个标志让innoDB判断哪些数据已经刷盘，哪些数据还没有。但redolog不一样，只要刷入磁盘的数据，都会从redolog中抹掉，因为是循环写！数据库重启后，直接把redolog中的数据都恢复至内存就可以了。
　　第二点：如果redolog写入失败，说明此次操作失败，事务也不可能提交redolog每次更新操作完成后，就一定会写入日志，如果写入失败，说明此次操作失败，事务也不可能提交。redolog内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redolog进行重放，就可以恢复数据。这就是为什么redolog具有crashsafe的能力，而binlog不具备。16、当数据库crash后，如何恢复未刷盘的数据到内存中？
　　根据redolog和binlog的两阶段提交，未持久化的数据分为几种情况：changebuffer写入，redolog虽然做了fsync但未commit，binlog未fsync到磁盘，这部分数据丢失。changebuffer写入，redologfsync未commit，binlog已经fsync到磁盘，先从binlog恢复redolog，再从redolog恢复changebuffer。changebuffer写入，redolog和binlog都已经fsync，直接从redolog里恢复。17、redolog写入方式？
　　redolog包括两部分内容，分别是内存中的日志缓冲（redologbuffer）和磁盘上的日志文件（redologfile）。
　　MySQL每执行一条DML语句，会先把记录写入redologbuffer（用户空间），再保存到内核空间的缓冲区OSbuffer中，后续某个时间点再一次性将多个操作记录写到redologfile（刷盘）。这种先写日志，再写磁盘的技术，就是WAL。
　　可以发现，redologbuffer写入到redologfile，是经过OSbuffer中转的。其实可以通过参数innodbflushlogattrxcommit进行配置，参数值含义如下：0：称为延迟写，事务提交时不会将redologbuffer中日志写入到OSbuffer，而是每秒写入OSbuffer并调用写入到redologfile中。1：称为实时写，实时刷，事务每次提交都会将redologbuffer中的日志写入OSbuffer并保存到redologfile中。2：称为实时写，延迟刷。每次事务提交写入到OSbuffer，然后是每秒将日志写入到redologfile。18、redolog的执行流程？
　　我们来看下Redolog的执行流程，假设执行的SQL如下：updateTseta1whereid666
　　MySQL客户端将请求语句updateTseta1whereid666，发往MySQLServer层。MySQLServer层接收到SQL请求后，对其进行分析、优化、执行等处理工作，将生成的SQL执行计划发到InnoDB存储引擎层执行。InnoDB存储引擎层将a修改为1的这个操作记录到内存中。记录到内存以后会修改redolog的记录，会在添加一行记录，其内容是需要在哪个数据页上做什么修改。此后，将事务的状态设置为prepare，说明已经准备好提交事务了。等到MySQLServer层处理完事务以后，会将事务的状态设置为commit，也就是提交该事务。在收到事务提交的请求以后，redolog会把刚才写入内存中的操作记录写入到磁盘中，从而完成整个日志的记录过程。19、binlog的概念是什么，起到什么作用，可以保证crashsafe吗？binlog是归档日志，属于MySQLServer层的日志。可以实现主从复制和数据恢复两个作用。当需要恢复数据时，可以取出某个时间范围内的binlog进行重放恢复。但是binlog不可以做crashsafe，因为crash之前，binlog可能没有写入完全MySQL就挂了。所以需要配合redolog才可以进行crashsafe。20、什么是两阶段提交？
　　MySQL将redolog的写入拆成了两个步骤：prepare和commit，中间再穿插写入binlog，这就是两阶段提交。
　　而两阶段提交就是让这两个状态保持逻辑上的一致。redolog用于恢复主机故障时的未更新的物理数据，binlog用于备份操作。两者本身就是两个独立的个体，要想保持一致，就必须使用分布式事务的解决方案来处理。
　　为什么需要两阶段提交呢？如果不用两阶段提交的话，可能会出现这样情况先写redolog，crash后binlog备份恢复时少了一次更新，与当前数据不一致。先写binlog，crash后，由于redolog没写入，事务无效，所以后续binlog备份恢复时，数据不一致。两阶段提交就是为了保证redolog和binlog数据的安全一致性。只有在这两个日志文件逻辑上高度一致了才能放心的使用。
　　在恢复数据时，redolog状态为commit则说明binlog也成功，直接恢复数据；如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。21、MySQL怎么知道binlog是完整的？
　　一个事务的binlog是有完整格式的：statement格式的binlog，最后会有COMMIT；row格式的binlog，最后会有一个XIDevent。22、什么是WAL技术，有什么优点？
　　WAL，中文全称是WriteAheadLogging，它的关键点就是日志先写内存，再写磁盘。MySQL执行更新操作后，在真正把数据写入到磁盘前，先记录日志。
　　好处是不用每一次操作都实时把数据写盘，就算crash后也可以通过redolog恢复，所以能够实现快速响应SQL语句。23、binlog日志的三种格式
　　binlog日志有三种格式Statement：基于SQL语句的复制（（statementbasedreplication，SBR））Row：基于行的复制。（rowbasedreplication，RBR）Mixed：混合模式复制。（mixedbasedreplication，MBR）
　　Statement格式
　　每一条会修改数据的SQL都会记录在binlog中优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。
　　Row格式
　　不记录SQL语句上下文相关信息，仅保存哪条记录被修改。优点：binlog中可以不记录执行的SQL语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点：可能会产生大量的日志内容。
　　Mixed格式
　　实际上就是Statement与Row的结合。一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式。24、redolog日志格式
　　redologbuffer（内存中）是由首尾相连的四个文件组成的，它们分别是：iblogfile1、iblogfile2、iblogfile3、iblogfile4。writepos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。writepos和checkpoint之间的是粉板上还空着的部分，可以用来记录新的操作。如果writepos追上checkpoint，表示粉板满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。有了redolog，当数据库发生宕机重启后，可通过redolog将未落盘的数据（checkpoint之后的数据）恢复，保证已经提交的事务记录不会丢失，这种能力称为crashsafe。25、原本可以执行得很快的SQL语句，执行速度却比预期的慢很多，原因是什么？如何解决？
　　原因：从大到小可分为四种情况MySQL数据库本身被堵住了，比如：系统或网络资源不够。SQL语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的SQL语句。确实是索引使用不当，没有走索引。表中数据的特点导致的，走了索引，但回表次数庞大。
　　解决：考虑采用forceindex强行选择一个索引考虑修改语句，引导MySQL使用我们期望的索引。比如把orderbyblimit1改成orderbyb，alimit1，语义的逻辑是相同的。第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。如果确定是索引根本没必要，可以考虑删除索引。26、InnoDB数据页结构
　　一个数据页大致划分七个部分FileHeader：表示页的一些通用信息，占固定的38字节。pageHeader：表示数据页专有信息，占固定的56字节。inimumSupermum：两个虚拟的伪记录，分别表示页中的最小记录和最大记录，占固定的26字节。UserRecords：真正存储我们插入的数据，大小不固定。FreeSpace：页中尚未使用的部分，大小不固定。PageDirectory：页中某些记录的相对位置，也就是各个槽对应的记录在页面中的地址偏移量。FileTrailer：用于检验页是否完整，占固定大小8字节。数据相关27、MySQL是如何保证数据不丢失的？只要redolog和binlog保证持久化磁盘就能确保MySQL异常重启后回复数据在恢复数据时，redolog状态为commit则说明binlog也成功，直接恢复数据；如果redolog是prepare，则需要查询对应的binlog事务是否成功，决定是回滚还是执行。28、误删数据怎么办？
　　DBA的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：权限控制与分配（数据库和服务器权限）制作操作规范定期给开发进行培训搭建延迟备库做好SQL审计，只要是对线上数据有更改操作的语句（DML和DDL）都需要进行审核做好备份。备份的话又分为两个点（1）如果数据量比较大，用物理备份xtrabackup。定期对数据库进行全量备份，也可以做增量备份。（2）如果数据量较少，用mysqldump或者mysqldumper。再利用binlog来恢复或者搭建主从的方式来恢复数据。定期备份binlog文件也是很有必要的如果发生了数据删除的操作，又可以从以下几个点来恢复：DML误操作语句造成数据不完整或者丢失。可以通过flashback，美团的myflash，也是一个不错的工具，本质都差不多都是先解析binlogevent，然后在进行反转。把delete反转为insert，insert反转为delete，update前后image对调。所以必须设置binlogformatrow和binlogrowimagefull，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。DDL语句误操作（truncate和drop），由于DDL语句不管binlogformat是row还是statement，在binlog里都只记录语句，不记录image所以恢复起来相对要麻烦得多。只能通过全量备份应用binlog的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长rm删除：使用备份跨机房，或者最好是跨城市保存。29、drop、truncate和delete的区别DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATETABLE则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。drop语句将表所占用的空间全释放掉。在速度上，一般来说，droptruncatedelete。如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；如果和事务有关，或者想触发trigger，还是用delete；如果是整理表内部的碎片，可以用truncate跟上reusestroage，再重新导入插入数据。30、在MySQL中有两个kill命令一个是killquery线程id，表示终止这个线程中正在执行的语句一个是killconnection线程id，这里connection可缺省，表示断开这个线程的连接
　　kill不掉的原因kill命令被堵了，还没到位kill命令到位了，但是没被立刻触发kill命令被触发了，但执行完也需要时间31、如何理解MySQL的边读边发如果客户端接受慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间会很长。服务端并不需要保存一个完整的结果集，取数据和发数据的流程都是通过一个nextbuffer来操作的。内存的数据页都是在BufferPool中操作的。InnoDB管理BufferPool使用的是改进的LRU算法，使用链表实现，实现上，按照5：3的比例把整个LRU链表分成了young区域和old区域。32、MySQL的大表查询为什么不会爆内存？由于MySQL是边读变发，因此对于数据量很大的查询结果来说，不会再server端保存完整的结果集，所以，如果客户端读结果不及时，会堵住MySQL的查询过程，但是不会把内存打爆。InnoDB引擎内部，由于有淘汰策略，InnoDB管理BufferPool使用的是改进的LRU算法，使用链表实现，实现上，按照5：3的比例把整个LRU链表分成了young区域和old区域。对冷数据的全扫描，影响也能做到可控制。33、MySQL临时表的用法和特性只对当前session可见。可以与普通表重名。增删改查用的是临时表。showtables不显示普通表。在实际应用中，临时表一般用于处理比较复杂的计算逻辑。由于临时表是每个线程自己可见的，所以不需要考虑多个线程执行同一个处理时临时表的重名问题，在线程退出的时候，临时表会自动删除。34、MySQL存储引擎介绍（InnoDB、MyISAM、MEMORY）InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL5。5。5之后，InnoDB作为默认存储引擎MyISAM基于ISAM的存储引擎，并对其进行扩展。它是在Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。在MySQL5。5。5之前的版本中，MyISAM是默认存储引擎MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。35、都说InnoDB好，那还要不要使用MEMORY引擎？内存表就是使用memory引擎创建的表为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面：锁粒度问题；数据持久化问题。由于重启会丢数据，如果一个备库重启，会导致主备同步线程停止；如果主库跟这个备库是双M架构，还可能导致主库的内存表数据被删掉。36、如果数据库误操作，如何执行数据恢复？
　　数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的binlog，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。主从备份相关37、MySQL是如何保证主备同步？
　　主备关系的建立：一开始创建主备关系的时候，是由备库指定的，比如基于位点的主备关系，备库说我要从binlog文件A的位置P开始同步，主库就从这个指定的位置开始往后发。而主备关系搭建之后，是主库决定要发给数据给备库的，所以主库有新的日志也会发给备库。
　　MySQL主备切换流程：客户端读写都是直接访问A，而节点B是备库，只要将A的更新都同步过来，到本地执行就可以保证数据是相同的。当需要切换的时候就把节点换一下，A的节点B的备库
　　一个事务完整的同步过程：备库B和主库A建立来了长链接，主库A内部专门线程用于维护了这个长链接。在备库B上通过changemaster命令设置主库A的IP端口用户名密码以及从哪个位置开始请求binlog包括文件名和日志偏移量在备库B上执行startslave命令备库会启动两个线程：iothread和sqlthread分别负责建立连接和读取中转日志进行解析执行备库读取主库传过来的binlog文件备库收到文件写到本地成为中转日志后来由于多线程复制方案的引入，sqlthread演化成了多个线程。38、什么是主备延迟
　　主库和备库在执行同一个事务的时候出现时间差的问题，主要原因有：有些部署条件下，备库所在机器的性能要比主库性能差。备库的压力较大。大事务，一个主库上语句执行10分钟，那么这个事务可能会导致从库延迟10分钟。39、为什么要有多线程复制策略？因为单线程复制的能力全面低于多线程复制，对于更新压力较大的主库，备库可能是一直追不上主库的，带来的现象就是备库上secondsbehindmaster值越来越大。在实际应用中，建议使用可靠性优先策略，减少主备延迟，提升系统可用性，尽量减少大事务操作，把大事务拆分小事务。40、MySQL的并行策略有哪些？按表分发策略：如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个worker不会更新同一行。缺点：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个worker中，就变成单线程复制了。按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并行。如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求binlog格式必须是row。缺点：相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。41、MySQL的一主一备和一主多从有什么区别？
　　在一主一备的双M架构里，主备切换只需要把客户端流量切到备库；而在一主多从架构里，主备切换除了要把客户端流量切到备库外，还需要把从库接到新主库上。42、主库出问题如何解决？基于位点的主备切换：存在找同步位点这个问题MySQL5。6版本引入了GTID，彻底解决了这个困难。那么，GTID到底是什么意思，又是如何解决找同步位点这个问题呢？GTID：全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识；它由两部分组成，格式是：GTIDserveruuid：gno每个MySQL实例都维护了一个GTID集合，用来对应这个实例执行过的所有事务。在基于GTID的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例B需要的日志已经不存在，A’就拒绝把日志发给B。43、MySQL读写分离涉及到过期读问题的几种解决方案？强制走主库方案sleep方案判断主备无延迟方案配合semisync方案等主库位点方案GTID方案。实际生产中，先客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等GTID或等位点的方案。44、MySQL的并发链接和并发查询有什么区别？在执行showprocesslist的结果里，看到了几千个连接，指的是并发连接。而当前正在执行的语句，才是并发查询。并发连接数多影响的是内存，并发查询太高对CPU不利。一个机器的CPU核数有限，线程全冲进来，上下文切换的成本就会太高。所以需要设置参数：innodbthreadconcurrency用来限制线程数，当线程数达到该参数，InnoDB就会认为线程数用完了，会阻止其他语句进入引擎执行。性能相关45、短时间提高MySQL性能的方法第一种方法：先处理掉那些占着连接但是不工作的线程。或者再考虑断开事务内空闲太久的连接。killconnectionid第二种方法：减少连接过程的消耗：慢查询性能问题在MySQL中，会引发性能问题的慢查询，大体有以下三种可能：索引没有设计好；SQL语句没写好；MySQL选错了索引（forceindex）。46、为什么MySQL自增主键ID不连续？唯一键冲突事务回滚自增主键的批量申请深层次原因是：MySQL不判断自增主键是否存在，从而减少加锁的时间范围和粒度，这样能保持更高的性能，确保自增主键不能回退，所以才有自增主键不连续。自增主键怎么做到唯一性？自增值加1来通过自增锁控制并发。47、InnoDB为什么要用自增ID作为主键？自增主键的插入模式，符合递增插入，每次都是追加操作，不涉及挪动记录，也不会触发叶子节点的分裂。每次插入新的记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。而有业务逻辑的字段做主键，不容易保证有序插入，由于每次插入主键的值近似于随机因此每次新纪录都要被插到现有索引页得中间某个位置，频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，写数据成本较高。48、如何最快的复制一张表？为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表一种方法是，使用mysqldump命令将数据导出成一组INSERT语句另一种方法是直接将结果导出成。csv文件。MySQL提供语法，用来将查询结果导出到服务端本地目录：selectfromdb1。twherea900intooutfileservertmpt。csv；得到。csv导出文件后，你就可以用下面的loaddata命令将数据导入到目标表db2。t中：loaddatainfileservertmpt。csvintotabledb2。t；物理拷贝：在MySQL5。6版本引入了可传输表空间（transportabletablespace）的方法，可以通过导出导入表空间的方式，实现物理拷贝表的功能。49、grant和flushprivileges语句grant语句会同时修改数据表和内存，判断权限的时候使用的内存数据，因此，规范使用是不需要加上flushprivileges语句。flushprivileges语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。50、要不要使用分区表？分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区，要及时的drop掉。51、join用法使用leftjoin左边的表不一定是驱动表如果需要leftjoin的语义，就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断，必须都写在on里面标准的groupby语句，是需要在select部分加一个聚合函数，比如selecta，count（）fromtgroupbyaorderbynull；52、MySQL有哪些自增ID？各自场景是什么？表的自增ID达到上限之后，在申请值不会变化，进而导致联系插入数据的时候报主键冲突错误。rowid达到上限之后，归0在重新递增，如果出现相同的rowid后写的数据会覆盖之前的数据。Xid只需要不在同一个binlog文件出现重复值即可，理论上会出现重复值，但概率极小可忽略不计。InnoDB的maxtrxid递增值每次MySQL重启会保存起来。Xid是由server层维护的。InnoDB内部使用Xid，就是为了能够在InnoDB事务和server之间做关联。但是，InnoDB自己的trxid，是另外维护的。threadid是我们使用中最常见的，而且也是处理得最好的一个自增id逻辑了。使用了insertunique算法53、Xid在MySQL内部是怎么生成的呢？
　　MySQL内部维护了一个全局变量globalqueryid，每次执行语句（包括select语句）的时候将它赋值给Queryid，然后给这个变量加1。如果当前语句是这个事务执行的第一条语句，那么MySQL还会同时把Queryid赋值给这个事务的Xid。
　　而globalqueryid是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的Xid也是有可能相同的。但是MySQL重启之后会重新生成新的binlog文件，这就保证了，同一个binlog文件里，Xid一定是惟一的。锁相关54、说一下MySQL的锁MySQL在server层和存储引擎层都运用了大量的锁MySQLserver层需要讲两种锁，第一种是MDL（metadatalock）元数据锁，第二种则TableLock表锁。MDL又名元数据锁，那么什么是元数据呢，任何描述数据库的内容就是元数据，比如我们的表结构、库结构等都是元数据。那为什么需要MDL呢？主要解决两个问题：事务隔离问题；数据复制问题InnoDB有五种表级锁：IS（意向读锁）；IX（意向写锁）；S（读）；X（写）；AUTOINC在对表进行selectinsertdeleteupdate语句时候不会加表级锁IS和IX的作用是为了判断表中是否有已经被加锁的记录自增主键的保障就是有AUTOINC锁，是语句级别的：为表的某个列添加AUTOINCREMENT属性，之后在插记录时，可以不指定该列的值，系统会动为它赋上单调递增的值。InnoDB4种行级锁RecordLock：记录锁GapLock：间隙锁解决幻读；前一次查询不存在的东西在下一次查询出现了，其实就是事务A中的两次查询之间事务B执行插入操作被事务A感知了NextKeyLock：锁住某条记录又想阻止其它事务在改记录前面的间隙插入新纪录InsertIntentionLock：插入意向锁；如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置则无须等待行锁和表锁的抉择全表扫描用行级锁55、什么是幻读？
　　值在同一个事务中，存在前后两次查询同一个范围的数据，第二次看到了第一次没有查询到的数据。
　　幻读出现的场景：事务的隔离级别是可重复读，且是当前读。幻读指新插入的行。
　　幻读带来的问题：对行锁语义的破坏破坏了数据一致性
　　解决：加间隙锁，锁住行与行之间的间隙，阻塞新插入的操作。带来的问题：降低并发度，可能导致死锁。其它为什么系列56、为什么MySQL会抖一下？脏页会被后台线程自动flush，也会由于数据页淘汰而触发flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。57、为什么删除了表，表文件的大小还是没变？数据项删除之后InnoDB某个页pageA会被标记为可复用。delete命令把整个表的数据删除，结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。经过大量增删改的表，都是可能是存在空洞的。这些空洞也占空间所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。重建表，就可以达到这样的目的。可以使用altertableAengineInnoDB命令来重建表。58、count（）实现方式以及各种count对比对于count（主键id）来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。对于count（1）来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字1进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count（1）执行得要比count（主键id）快。因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。对于count（字段）来说：如果这个字段是定义为notnull的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；如果这个字段定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。但是count是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count（）肯定不是null，按行累加。所以结论是：按照效率排序的话，count（字段）count（主键id）count（1）count（），所以建议尽量使用count（）。59、orderby排序内部原理MySQL会为每个线程分配一个内存（sortbuffer）用于排序该内存大小为sortbuffersize；如果排序的数据量小于sortbuffersize，排序就会在内存中完成；内部排序分为两种全字段排序：到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sortbuffer然后进行快速排序rowid排序：通过控制排序的行数据的长度来让sortbuffer中尽可能多的存放数据如果数据量很大，内存中无法存下这么多，就会使用磁盘临时文件来辅助排序，称为外部排序；外部排序，MySQL会分为好几份单独的临时文件来存放排序后的数据，一般是磁盘文件中进行归并，然后将这些文件合并成一个大文件；60、如何高效的使用MySQL显式随机消息随机取出Y1，Y2，Y3之后，算出Ymax，Ymin得到id集后算出Y1、Y2、Y3对应的三个id最后selectfromtwhereidin（id1，id2，id3）这样扫描的行数应该是CYmax3mysqlselectcount（）intoCfromt；
　　setY1floor（Crand（））；
　　setY2floor（Crand（））；
　　setY3floor（Crand（））；
　　Ymaxmax（Y1，Y2，Y3）
　　Yminmin（Y1，Y2，Y3）
　　selectidfromtlimitYmin，（YmaxYmin）
　　持续更新中。
　　参考：极客时间《MySQL实战45讲》https：www。nowcoder。comdiscuss744934？type1order0pos25page1ncTraceIdchannel1sourceiddiscusstagnctrack
　　转载自：微信公众号，herongwei