my.oschina.net/u/3803405/blog/3068179
1、为什么要用自增列作为主键
1、如果我们定义了主键（PRIMARY KEY），那么InnoDB会选择主键作为聚集索引。
如果没有显式定义主键，InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引。
如果没有这个唯一索引，InnoDB会选择内置的6字节长的ROWID作为隐式聚集索引（ROWID随着行记录的写入而增加，主键递增。这个ROWID不像ORACLE的那样可引用ROWID.隐式）。
2、数据记录本身存储在主索引（一个B+Tree）的叶子节点上，这就要求同一个叶子节点（一个内存页或磁盘页的大小）中的每条数据记录按顺序存储主键的
因此，每当插入一条新记录时，MySQL都会根据它的主键将其插入到合适的节点和位置，如果页面达到负载因子（InnoDB默认为15/16），就会重新插入一个新的页面（节点）创建
3.如果表使用自增主键，则每插入一条新记录，该记录将依次添加到当前索引节点的后续位置。 当一个页面已满时，将自动打开一个新页面
4.如果使用非自增主键（如身份证号或学号等），由于每次插入的主键值近似随机，每条新记录都要插入到某个位置在现有索引页的中间
这时候MySQL为了将新的记录插入到合适的位置就不得不移动数据，甚至目标页可能已经写回磁盘并从缓存中清除。 这时候还得从磁盘读回，增加了很多开销。
同时，频繁的移动和分页操作造成大量碎片，导致索引结构不够紧凑，随后不得不使用OPTIMIZE TABLE重建表并对填充的页面进行优化。
2、为什么使用数据索引可以提高效率 3、B+树索引和哈希索引的区别
B+树是平衡的多叉树。 根节点到每个叶子节点的高度差不超过1，同级节点之间有指针，是有序的，如下图所示：
哈希索引使用一定的哈希算法将键值转换为新的哈希值。 查找的时候不需要像B+树那样从根节点到叶子节点一步步查找。 只需要一种hash算法，是Unordered，如下图：
四、哈希索引的优点：
对于等价查询，hash索引有绝对的优势（前提是：没有大量重复的key值，如果有大量重复的key值，hash索引的效率很低，因为有一个所谓的散列冲突问题。）
5、哈希索引不适用的场景：
一般来说，B+树索引结构适用于大部分场景，在如下场景使用哈希索引更有优势：
在HEAP表中，如果表中存储的数据重复度低（即基数大），列数据主要用于等值查询，在没有范围查询或排序时oracle更改表空间名字，特别适合使用哈希索引，例如这条SQL：
# 仅等值查询
select id, name from table where name='李明';
常用的InnoDB引擎默认使用B+树索引，会实时监控表上索引的使用情况。
如果你认为构建哈希索引可以提高查询效率，那么在内存中的“自适应哈希索引缓冲区”自动创建哈希索引（InnoDB默认启用自适应哈希索引）。
通过观察搜索模式，MySQL 将使用索引键的前缀来构建哈希索引。 如果一张表的大部分内容都在缓冲池中，那么建立哈希索引可以加快等效查询的速度。
注意：在某些工作负载中，哈希索引查找带来的性能提升远远超过监控索引搜索条件和维护此哈希表结构的额外开销。
但有时在高负载情况下，自适应哈希索引中加入的读写锁也会带来竞争，比如高并发的join操作。 like操作和%通配符操作也不适用于自适应哈希索引，可能需要禁用自适应哈希索引。
六、B树和B+树的区别
1、B树，每个节点存储key和data，所有节点组成这棵树，叶子节点指针为nul，叶子节点不包含任何key信息。
2、B+树，所有的叶子节点都包含了所有关键词的信息，以及指向包含这些关键词的记录的指针，叶子节点本身按照关键词大小从小到大的顺序链接在一起
所有非终端节点都可以看作索引部分，节点只包含其子树根节点中最大（或最小）的键。 （而且B树的非终端节点也包含需要查找的有效信息）
7、为什么说B+在实际应用中比B树更适合操作系统的文件索引和数据库索引？
1、B+的磁盘读写成本更低。
B+的内部结点没有指向关键字具体信息的指针，所以它的内部结点比B树的内部结点要小。
如果同一个内部节点的所有关键字都存储在同一个磁盘块中，则磁盘块可以容纳的关键字数量也更大。 需要查找的关键词越多，一次性读入内存。 相对来说IO读写次数也减少了。
2、B+-tree的查询效率更稳定。
因为非终结点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。 所以任何关键字搜索都必须走一条从根节点到叶节点的路径。 所有关键字查询的路径长度相同，导致每条数据的查询效率相同。
八、MySQL联合索引
1. 联合索引是两个或多个列上的索引。
对于联合索引：Mysql从左到右使用索引中的字段，一次查询只能使用索引的一部分，而且只能使用最左边的部分。
例如，索引是键索引(a,b,c)。 可以支持a,a,b,a,b,c三种组合进行查找，不支持b,c进行查找。 当最左边的字段是常量引用时，索引非常有效。
2. 使用索引中的附加列，可以缩小搜索范围，但使用具有两列的索引与使用两个单独的索引不同。
复合索引的结构类似于电话簿的结构。 一个人的名字由名字和姓氏组成。 电话簿首先对成对的姓氏进行排序，然后按名字对具有相同姓氏的人进行排序。
如果您知道自己的姓氏，电话簿很有用，如果您知道自己的名字和姓氏，则电话簿会更有用，但如果您只知道名字而不知道姓氏，则电话簿毫无用处。
9、什么情况下不应该建索引或少建索引
1.表记录太少
2、频繁插入、删除、修改的表
3. 数据重复均匀分布的表字段。 如果一张表有10万行记录，有一个字段A只有T和F两个值，每个值的分布概率大约为50%，那么对于这个表A字段索引一般不会提高数据库的查询速度。
4.经常和主字段一起查询但主字段索引值较多的表字段
10.什么是表分区？
表分区是指将数据库中的一个表按照一定的规则分解成多个更小的便于管理的部分。 逻辑上只有一张表，但底层是由多个物理分区组成的。
11.表分区和分表的区别
分表：指将一个表通过一定的规则分解成多个不同的表。 比如将用户订单按照时间记录到多个表中。
分表和分表的区别在于分表在逻辑上只有一张表，而分表是将一张表分解成多张表。
12、表分区有什么好处？
1. 存储更多数据。 分区表的数据可以分布在不同的物理设备上，从而有效地利用多个硬件设备。可以存储比单个磁盘或文件系统更多的数据
2.优化查询。 当where语句包含分区条件时，可以只扫描一张或多张分区表，提高查询效率； 当涉及到sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
3、分区表更易于维护。 例如：如果要批量删除大量数据，可以清空整个分区。
4、避免一些特殊的瓶颈，比如InnoDB单索引的互斥访问，ext3系统的inode锁竞争等。
十三、分区表的限制因素
1.一张表最多只能有1024个分区
2、在MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。 MySQL5.5 中提供了对非整数表达式分区的支持。
3. 如果分区字段中有主键列或唯一索引列，则必须包括所有主键列和唯一索引列。 即：分区字段要么不包含主键或索引列，要么包含所有的主键和索引列。
4.分区表不能使用外键约束
5、MySQL分区适用于一个表的所有数据和索引。 不能只分区表数据不分区索引，也不能只分区索引不分区表，也不能只分区表中的部分数据。
14、如何判断当前MySQL是否支持分区？
命令：show variables like '%partition%' 运行结果：
mysql> show variables like '%partition%';
+-------------------+-------+| Variable_name | Value |+-------------------+-------+| have_partitioning | YES |+-------------------+-------+1 row in set (0.00 sec)
have_partintioning值为YES，表示支持分区。
15、MySQL支持的分区类型有哪些？
RANGE分区：这种模式可以让数据被划分到不同的范围内。比如一个表可以按年份划分成几个分区
LIST Partitioning：这种模式允许系统按预定义列表的值对数据进行分区。 根据List中的值分区，与RANGE的区别在于范围分区的区间范围值是连续的。
HASH分区：该模式允许对表的一个或多个列的Hash Key进行计算，最终对Hash码的不同值对应的数据区进行分区 。 例如，您可以创建一个对表的主键进行分区的表。
KEY分区：是上述Hash方式的扩展，Hash Key由MySQL系统生成。
十六、四种隔离级别十七、关于MVVC
MySQL InnoDB存储引擎实现了多版本并发控制协议——MVCC（Multi-Version Concurrency Control）
注：MVCC的对立面是基于锁的并发控制，Lock-Based Concurrency Control
MVCC最大的优点：读无锁，读写无冲突。 在读多写少的OLTP应用中，读写不冲突是非常重要的，这大大提高了系统的并发性能。 现阶段，几乎所有的 RDBMS 都支持 MVCC。
基于多版本并发控制协议。 纯基于锁的并发机制，并发度低。 MVCC是对基于锁的并发控制的改进，主要是增加读操作的并发。
18.在MVCC并发控制中，读操作可以分为两类：
快照读取（snapshot read）：读取记录的可见版本（可能是历史版本），不加锁（没有加共享读锁的锁，所以不会阻塞其他事务的写入）
当前读取：读取最新版本的记录，当前读取返回的记录将被锁定，保证其他事务不会并发修改这条记录
十九、行级锁的优点：
1. 多线程访问不同行时只有少数锁冲突。
2.回滚时只有少量改动
3.可以长期锁定单行。
20、行级锁的缺点：
使用比页级或表级锁定更多的内存。
在表的大部分上使用时比页级或表级锁定慢，因为您必须获取更多锁。
如果你经常对大部分数据执行 GROUP BY 操作或者必须经常扫描整个表，它比其他锁慢得多。
使用高级锁定，您还可以通过支持不同类型的锁定轻松调整您的应用程序，因为锁定成本低于行级锁定。
21. MySQL优化 22. key和index的区别
关键是数据库的物理结构，它包含两层含义和作用，一层是约束（关注约束，规范数据库的结构完整性），二层是索引（用于辅助查询）。包括主要的键、唯一键、外键等。
索引是数据库的物理结构oracle更改表空间名字，它只是为了辅助查询，创建时会以类目录结构存储在另一个表空间（mysql中的innodb表空间）中。 如果要对索引进行分类，可以分为前缀索引、全文索引等；
23、Mysql中MyISAM和InnoDB有什么区别？
区别：
1、InnoDB支持事务，MyISAM不支持
对于InnoDB，默认将每种SQL语言封装成一个事务自动提交，会影响速度，所以最好将多种SQL语言放在begin和commit之间，形成一个事务；
2、InnoDB支持外键，MyISAM不支持。
将包含外键的 InnoDB 表转换为 MYISAM 将失败；
3、InnoDB是聚簇索引，数据文件是绑定在索引上的。 它必须有一个主键，通过主键索引效率非常高。
但是，辅助索引需要两次查询。 首先查询主键，然后通过主键查询数据。 所以主键不宜太大，因为如果主键太大，其他索引也会很大。
而MyISAM是非聚集索引，数据文件是分离的，索引存放的是数据文件的指针。 主键索引和二级索引是独立的。
4、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。 但是，MyISAM 使用一个变量来保存整个表的行数。 执行上述语句时，只需要读取变量即可，速度非常快；
5. Innodb不支持全文索引，而MyISAM支持全文索引，MyISAM查询效率更高；
如何选择：
24.数据库建表注意事项
1、字段名称和字段配置的合理性
2. 系统专业完成后的处理及建议
3、合理配置表结构
4.其他建议
结尾
Java面试题专栏