吊打面试官之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是否变化，变化那么就更新数据