数据库MySQL常见面试问题

数据库MySQL常见⾯试问题

数据库MySQL常见⾯试问题

1.MySQL 主键与索引的联系与区别

主键是为了标识数据库记录唯⼀性，不允许记录重复，且键值不能为空，主键也是⼀个特殊索引。

数据表中只允许有⼀个主键，但是可以有多个索引。

使⽤主键会数据库会⾃动创建主索引，也可以在⾮主键上创建索引，⽅便查询效率。

索引可以提⾼查询速度，它就相当于字典的⽬录，可以通过它很快查询到想要的结果，⽽不需要进⾏全表扫描。

主键索引外索引的值可以为空。

主键也可以由多个字段组成，组成复合主键，同时主键肯定也是唯⼀索引。

唯⼀索引则表⽰该索引值唯⼀，可以由⼀个或⼏个字段组成，⼀个表可以有多个唯⼀索引。

2.数据库索引是怎么回事？⽤的啥数据结构为什么B+树⽐B树更合适

⼀个索引是存储的表中⼀个特定列的值数据结构（最常见的是B-Tree）。索引是在表的列上创建。所以，要记住的关键点是索引包含⼀个表中列的值，并且这些值存储在⼀个数据结构中。请记住记住这⼀点：索引是⼀种数据结构。

什么样的数据结构可以作为索引？

B-Tree 是最常⽤的⽤于索引的数据结构。因为它们是时间复杂度低，查找、删除、插⼊操作都可以可以在对数时间内完成。另外⼀个重要原因存储在B-Tree中的数据是有序的。数据库管理系统（RDBMS）通常决定索引应该⽤哪些数据结构。但是，在某些情况下，你在创建索引时可以指定索引要使⽤的数据结构。

当我们利⽤索引查询的时候，不可能把整个索引全部加载到内存，只能逐⼀加载每个磁盘页，磁盘页对应索引树的节点。那么Mysql衡量查询效率的标准就是磁盘IO次数。如果我们利⽤作为索引结构，那么磁盘的IO次数和索引树的⾼度是相关的。

那么为了提⾼查询效率，就需要减少磁盘IO数。为了减少磁盘IO的次数，就需要尽量降低树的⾼度，需要把原来“瘦⾼”的树结构变的“矮胖”，树的每层的分叉越多越好，因此B树正好符合我们的要求，这也是B-树的特征之⼀。

B树 B树的节点为关键字和相应的数据（索引等）

B+树 B+树是B树的⼀个变形，⾮叶⼦节点只保存索引，不保存实际的数据，数据都保存在叶⼦节点中，B+树的叶⼦节点为，放数据，⾮叶⼦节点是索引。

对⽐

1.B树和B+树同样适⽤于⾼度越低，查询越快。

2.B树查找节点，B+树只需要查询所有节点（索引），B树查询索引和数据。虽然可能第⼀个就找到，但在极端情况下，需要全查询索引和数据，不如B+树稳定。

3.B+树和B树⽐，B+树的硬盘空间更少，io的读写代价更低。因为B+树节点只有索引，占位更少。在查询的情况下硬盘指针移动更低

索引是怎么⼯作的？

是另外⼀种你可能看到⽤作索引的数据结构-这些索引通常被称为哈希索引。使⽤哈希索引的原因是，在寻找值时效率极⾼。所以，如果使⽤哈希索引，对于⽐较字符串是否相等的查询能够极快的检索出的值。例如之前我们讨论过的这个查询(SELECT * FROM Employee WHERE

Employee_Name = ‘Jesus’) 就可以受益于创建在Employee_Name 列上的哈希索引。哈系索引的⼯作⽅式是将列的值作为索引的键值（key），和键值相对应实际的值（value）是指向该表中相应⾏的指针。因为基本上可以看作是关联数组，⼀个典型的数据项就像“Jesus => 0x28939″，⽽

0x28939是对内存中表中包含Jesus这⼀⾏的引⽤。在哈系索引的中查询⼀个像“Jesus”这样的值，并得到对应⾏的在内存中的引⽤，明显要⽐扫描全表获得值为“Jesus”的⾏的⽅式快很多。

哈希索引的缺点

是⽆顺的数据结构，对于很多类型的查询语句哈希索引都⽆能为⼒。举例来说，假如你想要找出所有⼩于40岁的员⼯。你怎么使⽤使⽤哈希索引进⾏查询？这不可⾏，因为只适合查询键值对-也就是说查询相等的查询（例：like “WHERE name = ‘Jesus’）。的键值映射也暗⽰其键的存储是⽆序的。这就是为什么哈希索引通常不是数据库索引的默认数据结构-因为在作为索引的数据结构时，其不像B-Tree那么灵活

3.创建索引的注意事项

索引可以提⾼数据的访问速度，但同时也增加了插⼊、更新和删除操作的处理时间，解决此问题就是分析应⽤程序的业务处理、数据使⽤，为经常被⽤作查询条件、或者被要求的字段建⽴索引。索引是建⽴在数据库表中的某些列的上⾯。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进⾏连接的表，在连接字段上应该建⽴索引；

4、经常出现在Where⼦句中的字段，特别是⼤表的字段，应该建⽴索引；

5、索引应该建在选择性⾼的字段上；

6、索引应该建在⼩字段上，对于⼤的⽂本字段甚⾄超长字段，不要建索引；

7、复合索引的建⽴需要进⾏仔细分析；尽量考虑⽤单字段索引代替

8、频繁进⾏数据操作的表，不要建⽴太多的索引；

9、删除⽆⽤的索引，避免对执⾏计划造成负⾯影响；

创建索引需要注意的地⽅：

1. 限制表上的索引数⽬。对⼀个存在⼤量更新操作的表，所建索引的数⽬⼀般不要超过3个，最多不要超过5个。索引虽说提⾼了访问速度，但太

多索引会影响数据的更新操作。

2. 避免在取值朝⼀个⽅向增长的字段（例如：⽇期类型的字段）上，建⽴索引；对复合索引，避免将这种类型的字段放置在最前⾯

3. 对复合索引，按照字段在查询条件中出现的频度建⽴索引

4. 删除不再使⽤，或者很少被使⽤的索引。

4.MYSQL事务特性和实现原理

ACID表⽰原⼦性（atomicity）、⼀致性（consistency）、隔离性（isolation）和持久性（durability）。⼀个很好的事务处理系统，必须具备这些标准特性：

原⼦性（atomicity）

⼀个事务必须被视为⼀个不可分割的最⼩⼯作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于⼀个事务来说，不可能只执⾏其中的⼀部分操作，这就是事务的原⼦性

是利⽤Innodb的undo log。undo log名为回滚⽇志，是实现原⼦性的关键，当事务回滚时能够撤销所有已经成功执⾏的sql语句，他需要记录你要回滚的相应⽇志信息。

⼀致性（consistency）

数据库总是从⼀个⼀致性的状态转换到另⼀个⼀致性的状态。（在前⾯的例⼦中，⼀致性确保了，即使在执⾏第三、四条语句之间时系统崩溃，⽀票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

数据库通过原⼦性、隔离性、持久性来保证⼀致性

隔离性（isolation）

通常来说，⼀个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前⾯的例⼦中，当执⾏完第三条语句、第四条语句还未开始时，此时有另外的⼀个账户汇总程序开始运⾏，则其看到⽀票帐户的余额并没有被减去200美元。）

利⽤的是锁和MVCC机制。MVCC,即多版本并发控制(Multi Version Concurrency Control),⼀个⾏记录数据有多个版本对快照数据，这些快照数据在undo log中。如果⼀个事务读取的⾏正在做DELELE或者UPDATE操作，读取操作不会等⾏上的锁释放，⽽是读取该⾏的快照版本。

持久性（durability）

⼀旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。持久性是个有占模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供⾮常强的安全保障，⽽有些则未必，⽽且不可能有能做到100%的持久性保证的策略。）

是利⽤Innodb的redo log。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log⽇志进⾏刷盘(redo log⼀部分在内存中，⼀部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。redo log体积⼩，刷盘快。redo log是⼀直往末尾进⾏追加，属于顺序IO。效率显然⽐随机IO来的快

5.的原理和优点

是⼀个key-value存储系统.和Memcached类似，它⽀持存储的value类型相对更多，包括string(字符串)、list()、set(集合)、zset(sorted set --有序集合)和hashs（哈希类型）

这些数据类型都⽀持push/pop、add/remove及取交集并集和差集及更丰富的操作，⽽且这些操作都是原⼦性的.

在此基础上，⽀持各种不同⽅式的.与memcached⼀样，为了保证效率，数据都是缓存在内存中.区别的是会周期性的把更新的数据写⼊磁盘或者把修改操作写⼊追加的记录⽂件，并且在此基础上实现了master-slave(主从)同步.