说说MySQL有哪些存储引擎、都有哪些区别

  1. 一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。InnoDB 是 MySQL 默认的存储引擎,支持事务、行级锁定和外键。
  2. InnoDB 支持事务,MyISAM不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一;
  3. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败;
  4. InnoDB 是聚簇索引,MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上,因此 InnoDB 必须要有主键,通过主键索引效率很高。 MyISAM 是非聚集索引,数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
  5. InnoDB 不保存表的具体行数,执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
  6. InnoDB 最小的锁粒度是行锁,MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表,导致其他查询和更新都会被阻塞,因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一

一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ?

  1. 如果表的类型是MyISAM,那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中,重启MySQL自增主键的最大ID也不会丢失;
  2. 如果表的类型是InnoDB,那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中,所以重启数据库或对表进行OPTION操作,都会导致最大ID丢失。

哪个存储引擎执行 select count(*) 更快,为什么

  1. MyISAM更快,因为MyISAM内部维护了一个计数器,可以直接调取。在 MyISAM 存储引擎中,把表的总行数存储在磁盘上,当执行 select count(*) from t 时,直接返回总数据。
  2. 在 InnoDB 存储引擎中,跟 MyISAM 不一样,没有将总行数存储在磁盘上,当执行 select count(*) from t 时,会先把数据读出来,一行一行的累加,最后返回总数量。

为什么Mysql索引要用B+树不是B树

用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。

数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。

其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。

为何不采用Hash方式?

因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。所以,哈希索引只适用于等值查询的场景。

而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则,如果有大量重复键值的情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。

通俗地理解三个范式

  通俗地理解三个范式,对于数据库设计大有好处。在数据库设计中,为了更好地应用三个范式,就必须通俗地理解
  三个范式(通俗地理解是够用的理解,并不是最科学最准确的理解)
第一范式:1NF是对属性的原子性约束,要求属性具有原子性,不可再分解;
第二范式:2NF是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
第三范式:3NF是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。

  没有冗余的数据库设计可以做到。但是,没有冗余的数据库未必是最好的数据库,有时为了提高运行效率,就必须降
  低范式标准,适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式,降低范式标准的工作放到物理
  数据模型设计时考虑。降低范式就是增加字段,允许冗余。

MySQL索引分类

  1. 数据结构角度
  • B+树索引
  • Hash索引
  • Full-Text全文索引
  • R-Tree索引
  1. 从物理存储角度
  • 聚集索引(clustered index)
  • 非聚集索引(non-clustered index),也叫辅助索引(secondary index)
  • 聚集索引和非聚集索引都是B+树结构
  1. 从逻辑角度
  • 主键索引:主键索引是一种特殊的唯一索引,不允许有空值
  • 普通索引或者单列索引:每个索引只包含单个列,一个表可以有多个单列索引
  • 多列索引(复合索引、联合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
  • 唯一索引或者非唯一索引
  • 空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

哪些情况需要创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段
  3. 查询中与其他表关联的字段,外键关系建立索引
  4. 单键/组合索引的选择问题,高并发下倾向创建组合索引
  5. 查询中排序的字段,排序字段通过索引访问大幅提高排序速度
  6. 查询中统计或分组字段

哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布均匀的表字段,只应该为最经常查询和最经常排序的数据列建立索引(如果某个数据类包含太多的重复数据,建立索引没有太大意义)
  4. 频繁更新的字段不适合创建索引(会加重IO负担)
  5. where条件里用不到的字段不创建索引

MySQL中 in和 exists 的区别?

  1. exists:exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录;反之,如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false
  2. in:in查询相当于多个or条件的叠加 in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。所以相对内表比较小的时候,in的速度较快。
  3. 如果查询的两个表大小相当,那么用in和exists差别不大。如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in

哪些情况会使索引失效?

  1. 使用不等于(!= 或者<>)不能使用索引
  2. 单独的>,<,(有时会用到,有时不会)
  3. like “%_” 百分号在前.
  4. 单独引用复合索引里非第一位置的索引列.
  5. 字符型字段为数字时在where条件里不添加引号.
  6. 对索引列进行运算.需要建立函数索引.
  7. not in ,not exists.
  8. 当变量采用的是times变量,而表的字段采用的是date变量时.或相反情况。

事务的四大特性(ACID)

原子性(Atomicity)

原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。

一致性(Consistency)

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。

隔离性(Isolation)

隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

持久性(Durability)

持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

  例如我们在使用JDBC操作数据库时,在提交事务方法后,提示用户事务操作完成,当我们程序执行完成直到看到提示后,就可以认定事务已经正确提交,即使这时候数据库出现了问题,也必须要将我们的事务完全执行完成,否则就会造成我们看到提示事务处理完毕,但是数据库因为故障而没有执行事务的重大错误。

不考虑事务的隔离性,可能会发生的几种问题

  1. 脏读
    脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
  2. 不可重复读
    不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。
  3. 幻读
    相同的查询条件首次查询后,其他事务添加或删除了新的数据,再次查询不一致

事务隔离级别

数据库事务的隔离级别有4种,由低到高分别为

  • READ-UNCOMMITTED(未提交读): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
  • READ-COMMITTED(已提交读): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。Oracle的默认事务隔离级别
  • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。 MySQL的默认事务隔离级别。
  • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。 开销过大