JAVA 精髓面试题 · MySQL 索引、引擎与 Explain

Mysql 范式范式范式化设计：Normal Form,简称NF。一张数据库的表结构所符合的某种设计标准的级别。第一范式：每个列都不可以再拆分。第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。范式不一定越高越好，只是更加规范了。在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。范式不一定越高越好，只是更加规范了。范式越高，表越精简，冗余越少。范式设计查询关联表多，查询索引命中率低。反范式化设计与实现：为了性能和读取效率而适当的违反对数据库设计范式的要求。反范式化设计与实现：为了性能和读取效率而适当的违反对数据库设计范式的要求。为了查询的性能，允许才能在部分少量的冗余数据。缓存与汇总数据简单的数据，缓存处理，进行冗余。汇总：用户发消息，次数。（用户消息表增加count数据列，存放发送次数）。报表统计计数器设计。网站的点击，用户数量，下载次数，上传次数。 User.count优化 update count set count = count + 1 where id = 1; 存在互斥锁，行锁。一个线程只能改一次。高并发场景，引用槽的概念。分流，增加并发度。 Update count set count = count + 1 where id = 1 and slot = 1; 尽量不要存在null 因为索引，索引统计更加复杂。尽量不要存在null 因为索引，索引统计更加复杂。可为null,占用更多空间。Null列索引，记录一个额外的字节。 mysql有关权限的表都有哪几个？ MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容： user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。 db权限表：记录各个帐号在各个数据库上的操作权限。 table_priv权限表：记录数据表级的操作权限。 columns_priv权限表：记录数据列级的操作权限。 host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。 MySQL的binlog有几种录入格式？分别有什么区别？有三种格式，statement，row和mixed。 statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。 row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来。但是由于很多操作，会导致大量行的改动(比如altertable)，因此这种模式的文件保存的信息太多，日志量太大。 mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。 mysql有哪些数据类型？ mysql有哪些数据类型？整数类型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是无符号的，即非负整数。长度：整数类型可以被指定长度，实数类型，包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。字符串类型，包括VARCHAR、CHAR、TEXT、BLOBVARCHAR用于存储可变长字符串，它比定长类型更节省空间。 VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。 VARCHAR存储的内容超出设置的长度时，内容会被截断。 CHAR是定长的，根据定义的字符串长度分配足够的空间。 CHAR会根据需要使用空格进行填充方便比较。 CHAR适合存储很短的字符串，或者所有值都接近同一个长度。 CHAR存储的内容超出设置的长度时，内容同样会被截断。使用策略：对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。枚举类型（ENUM），把不重复的数据存储为一个预定义的集合。有时可以使用ENUM代替常用的字符串类型。ENUM存储非常紧凑，会把列表值压缩到一个或两个字节。ENUM在内部存储时，其实存的是整数。尽量避免使用数字作为ENUM枚举的常量，因为容易混乱。排序是按照内部存储的整数。日期和时间类型，尽量使用timestamp，空间效率高于datetime，用整数保存时间戳通常不方便处理。如果需要存储微妙，可以使用bigint存储。 MySQL存储引擎MyISAM与InnoDB区别？存储结构 MyISAM 每张表被存放在三个文件：frm- 表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件。MyISAM可被压缩，存储空间较小。 InnoDB 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。可移植性、备份及恢复可移植性、备份及恢复由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump，在数据量达到几十G的时候就相对痛苦了。文件格式 MyISAM数据和索引是分别存储的，数据.MYD，索引.MYI 。按记录插入顺序保存。不支持外键。不支持事务。表级锁。myisam更快，因为myisam内部维护了一个计数器，可以直接调取select count(*) 快。B+树索引，myisam是堆表。不支持哈希索引。支持全文索引。 InnoDB数据和索引是集中存储的，.ibd。按主键大小有序插入。支持外键，支持事务。行级锁定、表级锁定，锁定力度小并发能力高。B+树索引，Innodb是索引组织表。支持哈希索引，不支持全文索引。INSERT、UPDATE、DELETE更优。 InnoDB引擎的4大特性 InnoDB引擎的4大特性插入缓冲（insertbuffer) 二次写(doublewrite) 自适应哈希索引(ahi) 预读(readahead) 存储引擎如何选择？如果没有特别的需求，使用默认的Innodb即可。 MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。 Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。 InnoDB存储引擎的锁的算法 Record lock：单个行记录上的锁 Gap lock：间隙锁，锁定一个范围，不包括记录本身 Next-key lock：record+gap 锁定一个范围，包含记录本身相关知识点：

innodb对于行的查询使用next-key lock
Next-locking keying为了解决Phantom Problem幻读问题
当查询的索引含有唯一属性时，将next-key lock降级为record key
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock）A. 将事务隔离级别设置为RCB. 将参数innodb_locks_unsafe_for_binlog设置为1 索引的基本原理？索引使用场景? 索引（Index）是帮助数据库高效获取数据的数据结构。索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。索引的原理：就是把无序的数据变成有序的查询。 orderby orderby 当我们使用orderby将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。但是如果我们对该字段建立索引altertable表名addindex(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的） join 对join语句匹配关系（on）涉及的字段建立索引能够提高效率索引覆盖如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。 MyISAM索引与InnoDB索引的区别？ MyISAM索引与InnoDB索引的区别？ MyISAM索引与InnoDB索引的区别？ InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。 InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。 MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。 InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。 Mysql聚簇和非聚簇索引的区别 Mysql聚簇和非聚簇索引的区别 Mysql聚簇和非聚簇索引的区别都是B+树的数据结构 ● 聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。 InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。 ● 非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据。对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签( bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。优势：1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高 2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的 3、聚簇索引适合用在排序的场合，非聚簇索引不适合劣势： 1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片 2、表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键 3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间InnoDB中一定有主键，主键一定是聚簇索引，不手动设置、则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值MyISM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。 Mysql索引的数据结构，各自优劣？（b树，hash）索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。 B+树：B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。哈希索引：哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；哈希索引也不支持多列联合索引的最左匹配规则； B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。索引设计的原则？查询更快、占用空间更小
适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的表，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
定义有外键的数据列一定要建立索引。
更新频繁字段不适合创建索引
若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。索引覆盖是什么索引覆盖就是一个SQL在执行时，可以利用索引来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了，所需要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了最左前缀原则是什么使用最频繁的一列放在最左边。最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like) 就停止匹配。当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,b,c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则。当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,b,c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,c三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则。 MySQL索引失效的原理【最佳左前缀法则失效】按我的大白话讲一遍：就好比是你过桥，索引失效了只有桥头生效，桥中和桥尾都没生效，那么你就只按我的大白话讲一遍：就好比是你过桥，索引失效了只有桥头生效，桥中和桥尾都没生效，那么你就只能走到桥头，后面因为索引失效了，后面的桥中没了，过不去了。最左前缀法则失效就是桥头没了，到不了桥中和桥尾只能慢慢游过去[全表扫描]。总结：就是索引条件在左边可以直接去查索引，因为b+树结构[索引从小到大排序]可以进行二分查找很快找到索引位置或者符合第一个要求的范围，然后就可以根据确定的范围去无序的后缀里面找到我们总结：就是索引条件在左边可以直接去查索引，因为b+树结构[索引从小到大排序]可以进行二分查找很快找到索引位置或者符合第一个要求的范围，然后就可以根据确定的范围去无序的后缀里面找到我们要查找的最终数据这样数据就可以很快找到。但是索引失效就是一个条件失效了不去进行索引查找或者缩小范围查找了，就会直接是无序的就是扫描全表的找。因此效率很低。最左前缀法则就是你建索引的时候的顺序要和你查找时候的索引顺序一致最左前缀法则就是你建索引的时候的顺序要和你查找时候的索引顺序一致最左前缀法则就是你建索引的时候的顺序要和你查找时候的索引顺序一致最左前缀法则就是你建索引的时候的顺序要和你查找时候的索引顺序一致只有遵循了最佳左前缀法则索引才可能不会失效只有遵循了最佳左前缀法则索引才可能不会失效比如查询(a,b) 索引失效就是 a去掉了【a失效了】不能先查询a 缩小a开头的结果范围然后就进行对b 全表扫描简述Mysql中索引类型及对数据库的性能的影响简述Mysql中索引类型及对数据库的性能的影响简述Mysql中索引类型及对数据库的性能的影响普通索引：允许被索引的数据列包含重复的值。唯一索引：可以保证数据记录的唯一性。主键索引：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。联合索引：索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。全文索引：通过建立倒排索引 ,可以极大的提升检索效率,解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引索引可以极大的提高数据的查询速度。全文索引：通过建立倒排索引 ,可以极大的提升检索效率,解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引索引可以极大的提高数据的查询速度。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就 Mysql慢查询该如何优化？ Mysql慢查询该如何优化？
检查是否走了索引，如果没有则优化SQL利用索引
检查所利用的索引，是否是最优索引
检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据
检查表中数据是否过多，是否应该进行分库分表了
检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源 MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年, 怎么优化？ MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年, 怎么优化？ ● 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。 ● 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。 ● 选择合适的表字段数据类型和存储引擎，适当的添加索引。 ● mysql库主从读写分离。 ● 找规律分表，减少单表中的数据量提高查询速度。 ● 添加缓存机制，比如memcached， apc等。 ● 不经常改动的页面，生成静态页面。 ● 书写高效率的SQL 。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, fi eld_3 FROM TABLE. eld_3 FROM TABLE.

快照读快照读快照读快照读快照读快照读快照读快照读快照读快照读快照读快照读快照读