Mysql面试题

可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

原子性 Atomicity：一个事务中的所有操作，要么全部完成，要么全部不完成，最小的执行单位。   
一致性 Consistency： 事务执行前后，都处于一致性状态。
 隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止 多个事务并发执行时由于交叉执行而导致数据的不一致。
 持久性 Durability： 事务执行完成后，对数据的修改就是永久的，即便系统故障也不会丢失。

READ_UNCOMMITTED
这是事务最低的隔离级别，它充许另外一个事务可以看到这个事务未提交的数据。解决第一类丢失更 新的问题，但是会出现脏读、不可重复读、第二类丢失更新的问题，幻读 。
READ_COMMITTED
保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取该事务未提交的数据。解决第一类丢失更新和脏读的问题，但会出现不可重复读、第二类丢失更新的问题，幻读问题    
REPEATABLE_READ
保证一个事务相同条件下前后两次获取的数据是一致的   (注意是 一个事务，可以理解为事务间的数据互不影响）解决第一类丢失更新，脏读、不可重复读、第二类丢失更新的问题，但会出幻读。   
 SERIALIZABLE
事务串行执行，解决了脏读、不可重复读、幻读。但效率很差，所以实际中一般不用。

可重复读  (REPEATABLE-READ

mysql>  select  @@global.tx_isolation; +———————————+ |  @@global.tx_isolation   | +———————————+ |  REPEATABLE-READ                 | +———————————+ 1  row  in  set ,  1  warning  (0.01  sec)

数据库的锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性，这样才能保证在高并发 的情况下，访问数据库的时候，数据不会出现问题。

是指两个或两个以上进程执行过程中，因竞争共享资源造成的相互等待现象。

设置超时时间。超时后自动释放。   发起死锁检测，主动回滚其中一条事务，让其他事务继续执行。

一、 id SQL查询中的序列号。 id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行。

二、select_type

三、 table
显示这一行的数据是关于哪张表的。不一定是实际存在的表名。
可以为如下的值知
.  <unionM,N>: 引用id为M和N UNION后的结果。
.  : 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。
.  : 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。

四、 type
这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为知
system ，const ，eq_ref ，ref ，fulltext ，ref_or_null ，index_merge ，unique_subquery， index_subquery ，range ，index ，ALL

五、 possible_keys
查询可能使用到的索引都会在这里列出来。

六、 Key key列显示MySQL实际使用的键(索引) 要想强制MySQL使用或忽视possible_keys列中的索引，可以使用FORCE INDEX 、USE INDEX或者 IGNORE INDEX。 select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

七、 key_len 表示索引中使用的字节数。 key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。 不损失精确性的情况下，长度越短越好 。

八、 ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

九、 rows rows 也是一个重要的字段。   这是mysql估算的需要扫描的行数(不是精确值）。

十、 Extra 该列包含MySQL解决查询的详细信息,有以下几种情况:    Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过 滤。    Using temporary:表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。    Using filesort : MySQL中无法利用索引完成的排序操作称为“文件排序”。    Using join buffer:改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结 果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。    Impossible where:这个值强调了where语句会导致没有符合条件的行。    Select tables optimized away:这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回 一行。

MyISAM 、 InnoDB 、BDB 、MEMORY 、MERGE 、EXAMPLE 、NDB Cluster 、 ARCHIVE 、CSV、 BLACKHOLE 、FEDERATED。 Tips:InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

1.Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认使用 Myisam。 每个MyISAM 在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是 .frm (存储表定义) 、.MYD (MYData，存储数据) 、.MYI (MYIndex，存储索引)。 数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。

2.InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 Myisam 的存储引 擎， InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

可以, ENGINE=xxx 设置引擎。

代码示例:
create  table  person(
id  int  primary  key  auto_increment,
username  varchar(32)
)  ENGINE=InnoDB

选择标准: 根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择 多种存储引擎 进行组合.

1.  MyISAM: 默认的 MySQL 插件式存储引擎, 它是在 Web 、 数据仓储和其他应用环境下最常使用的存 储引擎之一。

2.  InnoDB:用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。

3.  Memory: 将 所有数据保存在RAM 中，  在 需要快速查找引用和其他类似数据的环境下，可 提供极快 的访问。

4.  Merge:允许 MySQL DBA 或开发人员将一系列等同的 MyISAM 表以逻辑方式组合在一起,并作为 1 个 对象引用它们。对于诸如数据仓储等 VLDB 环境十分适合。

前提: 使用适合存储引擎。 选择原则: 根据选定的存储引擎,确定如何选择合适的数据类型下面的选择方法按存储引擎分类 : 1.  MyISAM 数据存储引擎和数据列 MyISAM数据表，最好使用固定长度的数据列代替可变长度的数据列。 2.  MEMORY存储引擎和数据列 MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关 系。两者都是作为CHAR类型处理的。 3.  InnoDB 存储引擎和数据列 建议使用 VARCHAR类型 对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列(所有数据行 都使用指向数据 列值的头指针) ，因此在本质上，使用固定长度的CHAR列不一定比使 用可变长度VARCHAR列简 单。  因而，  主要的性能因素是数据行使用的存储总量。   由于 CHAR 平均占用的空间多于 VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总 量和磁盘I/O是比较好的。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构， 它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引 的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

1.  搜索的索引列，不一定是所要选择的列。最适合索引的列是出现在WHERE子句中的列，或连接子句 中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。

2.  使用憔一索引。考虑某列中值的分布。   对于憔一值的列，索引的效果最好，而具有多个 重复值的 列，其索引效果最差。

3.  使用短索引。如果对串列进行索引，应该指定一个前缀长度,只要有可能就应该这做样。   例如，如果  有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是憔一的，  那么就不要对整个列进行 索引。

4.  利用最左前缀。在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。   多列索引可   起几个索引的作用，因为可利用索引中最左边的列集来匹配行。   这样的列集 称为最左前缀。   (这与索 引一个列的前缀不同，索引一个列的前缀是利用该的n前个字 符作为索引值 )

5.  不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已 经介绍 过。在修改表的内容时，索引必须进行更新,有时可能需要重构, 因此，  索引越多，所花的时   间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。   此外， MySQL 在生成一 个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的  最好索引。   只保持所需的索引有利于查询优化。   如果想给已索引的表增加索引，   应 该考虑所要增加 的索引是否是现有多列索引的最左索引。

6.  考虑在列上进行的比较类型。   索引可用于“ <” 、“ < = ”、“ = ”、“ > =” 、“ > ”和 BETWEEN 运算。 在模式具有一个直接量前缀时，索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( )) ，对其进行索引没有价值。

数据结构角度

物理存储角度

逻辑角度

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获 得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可 以获得所查键值,然后根据查询判断是否需要回表查询数据. 那么可以看出他们有以下的不同:    hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询. 因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而 B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.    hash索引不支持使用索引进行排序,原理同上.    hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和 AAAAB的索引没有相关性.    hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可 以只通过索引完成查询.    hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash 碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树 的高度较低. 因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回 表查询. 举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select  age  from  employee where  age  <  20的查询时,在索引的叶子节点上, 已经包含了age信息,不会再次进行回表查询.

在普通索引查到主键索引后，再去主键索引定位记录。等于说非主键索引需要多走一个索引树。

索引覆盖被查询的字段。

本质区别，主键是一种约束，唯一索引是一种索引。 主键不能有空值(非空+唯一），唯一索引可以为空。 主键可以是其他表的外键，唯一索引不可以。 一个表只能有一个主键，唯一索引 可以多个。 都可以建立联合主键或联合唯一索引。 主键-》聚簇索引，唯一索引->非聚簇索引。

为什么最好建立一个主键?

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。

使用不等于查询
   NULL值
  列参与了数学运算或者函数
  在字符串like时左边是通配符.比如 %xxx
   当mysql分析全表扫描比使用索引快的时候不使用索引.
   当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

16

show  index  from  table_name;

Xxx% 走索引，   %xxx不走索引。

建议在能够完全满足应用的前提下,尽量使用小的字符集。因为更小的字符集意味着能够节省空间、   减 少网络传输字节数, 同时由于存储空间的较小间接的提高了系统的性能。 有很多字符集可以保存汉字，比如 utf8 、gb2312 、gbk、latin1 等等，但是常用的是 gb2312 和 gbk。因 为 gb2312 字库比 gbk 字库小，有些偏僻字(例如:洛)不能保存，因此 在选择字符集的时候一定要权衡这 些偏僻字在应用出现的几率以及造成的影响，   不能做出肯 定答复的话最好选用 gbk。