吊打面试官系列——MySQL篇面试题总结（一）

事务是什么？

• 事务是指满足ACID特性的一组操作，可以通过commit提交一个事务，通过rollback来回滚一个事务

数据库事务有哪些特性？

• 原子性（Atomicity）

  • 事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

  • 回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。

• 一致性（Consistency ）

  • 举例：转账前后，总金额一致

  • 数据是满足完整性约束的

  • 数据在事务的前后，业务整体一致

• 隔离性（Isolation）

  • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。

• 持久性（Durability）

  • 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

  • 使用重做日志来保证持久性。

非关系型数据库和关系型数据库的区别？

• 关系型数据库：

  • 指采用了关系模型来组织数据的数据库。简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

  • 优点：

    • 容易理解，二维表的结构非常贴近现实世界，二维表格，容易理解。

    • 使用方便，通用的sql语句使得操作关系型数据库非常方便。

    • 易于维护，数据库的ACID属性，大大降低了数据冗余和数据不一致的概率。

  • 缺点：

  1. 海量数据的读写效率。对于网站的并发量高，往往达到每秒上万次的请求，对于传统关系型数据库来说，硬盘I/o是一个很大的挑战。

  2. 高扩展性和可用性。在基于web的结构中，数据库是最难以横向拓展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库没有办法像web Server那样简单的通过添加更多的硬件和服务节点来拓展性能和负载能力。

• 非关系型数据库

  • 主要指那些非关系型的、分布式的，且一般不保证ACID的数据存储系统，主要代表MongoDB，Redis、CouchDB。

  • NoSQL提出了另一种理念，以键值来存储，且结构不稳定，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构，可以减少一些时间和空间的开销。使用这种方式，为了获取用户的不同信息，不需要像关系型数据库中，需要进行多表查询。仅仅需要根据key来取出对应的value值即可。

  • 分类：

    • 面向高性能并发读写的 key-value 数据库：

主要特点是具有极高的并发读写性能，例如Redis、Tokyo Cabint等。

  * 面向海量数据访问的  面向文档  数据库：

特点是，可以在海量的数据库快速的查询数据。例如MongoDB以及CouchDB.

  * 面向可拓展的  分布式数据库  ：

解决的主要问题是传统数据库的扩展性上的缺陷。

* 缺点  ：

  * 但是由于Nosql约束少，所以也不能够像sql那样提供where字段属性的查询。因此适合存储较为简单的数据。

• 对比 ：

  • 存储上 ：关系型数据库以数据表的形式存储。非关系型采用key-value形式。

  • 事务 ： SQL中如果多张表需要同批次被更新，即如果其中一张表 更 新失败的话，其他表也不会更新成功。 这种场景可以通过事务来控制，可以在所有命令完成之后，再统一提交事务。 在Nosql中没有事务这个概念，每一个数据集都是原子级别的。

  • 数据表 VS 数据集 ：关系型是表格型的，存储在数据表的行和列中。彼此关联，容易提取。而非关系型是大块存储在一起。

  • 预定义结构 VS 动态结构 ：

    • 在sql中，必须定义好地段和表结构之后，才能够添加数据，例如定义表的主键、索引、外键等。表结构可以在定义之后更新，但是如果有比较大的结构变更，就会变的比较复杂。

    • 在Nosql数据库中，数据可以在任何时候任何地方添加。不需要预先定义。

  • 存储规范 VS 存储代码 ：

    • 关系型数据库为了规范性，把数据分配成为最小的 逻辑表来存储 避免重复，获得精简的空间利用。但是多个表之间的关系限制，多表管理就有点复杂。

当然精简的存储可以节约宝贵的数据存储，但是现在随着社会的发展，磁盘上付出的代价是微不足知道的。

  * 非关系型是  平面数据集合  中，数据经常可以重复，单个数据库很少被分开，而是存储成为一个整体，这种整块读取数据效率更高。

* 纵向拓展 VS 横向拓展  ：

  * 为了支持更多的并发量，SQL数据采用纵向扩展，提高处理能力，通过提高计算机性能来提高处理能力。

  * NoSql通过横向拓展，非关系型数据库天然是分布式的，所以可以通过集群来实现负载均衡。

数据库的隔离级别有哪些？有什么区别？

• 未提交读（READ UNCOMMITTED）

  • 事务中的修改，即使没有提交，对其它事务也是可见的

• 提交读（READ COMMITTED）

  • 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。

• 可重复读（REPEATABLE READ）

  • 保证在同一个事务中多次读取同样数据的结果是一样的。

• 可串行化（SERIALIZABLE）

  • 强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。

如何加快数据库查询速度？

处理百万级以上的数据提高查询速度的方法：

1.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectid from t where num=0

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20  可以这样查询： select id from t where num=10 union all select id from t where num=20

5.下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between就不要用 in 了：select id from t where num between 1 and 3

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100应改为:select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=’abc’–name以abc开头的id select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id 应改为: select id from t where name like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使
用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择：

selectnum from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段
sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或
update
时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered
索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered
索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会
逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar
，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使 用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log
，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table
，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD
游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时
间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF
。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30.尽量避免大事务操作，提高系统并发能力。

聚集索引和非聚集索引的区别？（主键索引和非主键索引）

• 聚集索引（聚簇索引）（主键索引）：

  • 数据行的 物理顺序 与列值（一般是主键的那一列）的 逻辑顺序 相同，一个表中只能拥有一个聚集索引。

  • 主索引的叶子节点 data 域记录着完整的数据记录。

• 非聚集（unclustered）索引 （非主键索引）:

  • 该索引中索引的 逻辑顺序与磁盘上行的物理存储顺序不同 ，一个表中可以拥有多个非聚集索引。

  • 辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找

什么时候不该使用索引？

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

• 对于中到大型的表，索引就非常有效；

• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术 。

索引底层的数据结构是什么？B+树的结构是怎么样的？

• B+Tree

• 首先B+Tree是从最早的平衡二叉树演化过来的。

• 每个节点中的键值是有序的

• 平衡二叉树每个节点的出度为2，m阶B+Tree的出度最大为m。m的大小取决于磁盘页的大小

• B+Tree的非叶子节点存储键值信息，即表中记录的主键，以及子节点的指针信息。

• 数据记录都存放在叶子节点中。

• 每个节点占用存储引擎的一个页。

• 所有叶子节点之间都有一个链指针。因此对B+Tree可以进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

• B+Tree相对于平衡二叉树而言，增大了节点的出度，减少了树的高度，缩减了查询时间。而且B+Tree在一个节点中存储了多个键值，相比于平衡二叉树减少了节点的个数。

• 数据库引擎在把磁盘数据读入到内存时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

b加树与b树的区别是什么？

• b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；

• b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；

• 对于范围查找和元素遍历来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

• B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

• B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低

• B+树的查询效率更加稳定

• 增删文件（节点）时，效率更高

索引优化方法有哪些？

• 独立的列

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

* 在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

• 多列索引

  • 在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把actor_id 和 film_id 设置为多列索引。

  • SELECT film_id, actor_ id FROM sakila.film_actor
    WHERE actor_id = 1 AND film_id = 1;

• 索引列的顺序

  • 让选择性最强的索引列放在前面。

  • 索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

  • 例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

• 前缀索引

  • 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

  • 前缀长度的选取需要根据索引选择性来确定

• 覆盖索引

* 覆盖索引会直接在索引表中进行查询而不会访问原始数据。加快查询效率

* 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。

* 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

* 所有需要查询的字段信息都包含在了索引里面

* 查询字段中包含了索引列

* 优点：

* 简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

hibernate和mybatis的异同有哪些？

• 相同点：

  • Hibernate 与 MyBatis 都可以是通过 SessionFactoryBuider 由 XML 配置文件生成 SessionFactory，然后由SessionFactory 生成 Session，最后由 Session 来开启执行事务和 SQL 语句。其中SessionFactoryBuider，SessionFactory， Session 的生命周期都是差不多的。

  • Hibernate 和 MyBatis 都支持 JDBC 和 JTA 事务处理。

• 不同：

  • MyBatis 可以进行更为细致的 SQL 优化，可以减少查询字段 。

  • MyBatis 容易掌握，而 Hibernate 门槛较高。

  • Hibernate 的 DAO 层开发比 MyBatis 简单， Mybatis 需要维护 SQL 和结果映射。

  • Hibernate 对对象的维护和缓存要比 MyBatis 好，对增删改查的对象的维护要方便。

  • Hibernate 数据库移植性很好， MyBatis 的数据库移植性不好，不同的数据库需要写不同 SQL。

  • Hibernate 有更好的二级缓存机制，可以使用第三方缓存。MyBatis 本身提供的缓存机制不佳。

  • 由于无须SQL,当多表关联超过3个的时候，通过Hibernate的级联会造成太多性能的丢失

  • 对于性能要求不太苛刻的系统，比如管理系统、ERP 等推荐使用Hibernate;而对于性能要求高、响应快、灵活的系统则推荐使用MyBatis。

数据库的四种连接方式是什么？

• 内连接

  • 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行

  • 

• 左连接

  • 左表的挨个信息去查询,查不到则将右边控制为null进行显示

  • 

• 右连接

  • 右表的挨个信息去查询,查不到则将左边控制为null进行显示

  • 

• 完全连接

  • 交叉联接返回左表中的所有行，左表中的每一行与右表中匹配的所有行组合。交叉联接也称作笛卡尔积。

  • 

  • 

  • mysql中实现全连接的方法

    • SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno
      UNION
      SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno

    • union 去重；union all 不去重

数据库并发一致性问题有哪些？

• 丢失修改

  • T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

• 读脏数据

  • 

• 不可重复读

  • T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

  • 

• 幻影读

  • T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

  • 

可串行化调度如何实现？

• 通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。

• 事务遵循两段锁协议是保证可串行化调度的充分条件。

• 两段锁协议

  • 加锁和解锁分为两个阶段进行。

  • 

关系型数据库设计三范式是什么？

• 第一范式

  • 属性不可分

• 第二范式

  • 非主属性完全函数依赖于主属性

• 第三范式

  • 非主属性不传递函数依赖于主属性

关系型数据库的设计不合范式会有什么异常？

• 冗余数据：例如 学生-2 出现了两次。

• 修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。

• 删除异常：删除一个信息，那么也会丢失其它信息。例如删除了 课程-1 需要删除第一行和第三行，那么 学生-1 的信息就会丢失。

• 插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

关系型数据库设计范式是什么？

• 高级别范式依赖于低级别范式，1NF 是最低级别的范式。

• 第一范式 (1NF)

  • 属性不可分。

• 第二范式 (2NF)

  • 每个非主属性完全函数依赖于键码。

  • 可以通过分解来满足。

• 第三范式 (3NF)

  • 非主属性不传递函数依赖于键码 。

  • 任何非主属性不依赖于其它非主属性

• 概念：

  • 例如：该表中码为：（学号，课程名称）

  • 主属性：码属性组中的所有属性

  • 非主属性：除过码属性组的属性

  • 例如：学号–>系名，系名–>系主任

  • 例如：（学号，课程名称） – > 姓名

  • 例如：（学号，课程名称） --> 分数

  • 例如：学号–>姓名。（学号，课程名称） --> 分数

  • 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A

  • 完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。

  • 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。

  • 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A

  • 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码

• 为了性能可以进行妥协，不按照范式进行数据库设计

数据库存储引擎有哪些？

• InnoDB

  • 是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

  • 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。

  • 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

  • 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

  • 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取

• MyISAM

  • 设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

  • 提供了大量的特性，包括压缩表、空间数据索引等

  • 不支持事务。

  • 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

  • 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

  • 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

• 比较

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

  • 外键：InnoDB 支持外键。

  • 备份：InnoDB 支持在线热备份。

  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

  • 其它特性：MyISAM 支持压缩表和空间数据索引。

• Memory引擎

  • 用来存储只读数据，读取速度要比MyISAM更快

对(a,b,c)加索引，查询ab、ac、bc有没有用？

mysql中关于关联索引的问题——对a,b,c三个字段建立联合索引，那么查询时使用其中的2个作为查询条件，是否还会走索引？

• 对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

• 过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，该字段及其后面的索引字段都无法被使用索引(即使用的索引字段仅为前面的部分字段)

mysql索引相关操作的语句介绍一下？

• ALTER TABLE ：ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

  • ALTER TABLE table_name ADD INDEX index_name (column_list)

  • ALTER TABLE table_name ADD UNIQUE (column_list)

  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

  • 其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

• CREATE INDEX ：CREATE INDEX可对表增加普通索引或UNIQUE索引。

  • CREATE INDEX index_name ON table_name (column_list)

  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

  • table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

• 删除索引 ：

  • DROP INDEX index_name ON table_name

  • ALTER TABLE table_name DROP INDEX index_name

  • ALTER TABLE table_name DROP PRIMARY KEY

• 查看索引

  • mysql> show index from tblname;

  • mysql> show keys from tblname;

  • explain+select可以查看是否使用索引

  • force index可以强制使用某个索引

where和having的异同？

• 用的地方不一样

  • where可以用于select、update、delete和insert into values(select * from table where …)语句中。

  • having只能用于select语句中

• 执行的顺序不一样

  • where的搜索条件是在执行语句进行分组之前应用

  • having的搜索条件是在分组条件后执行的

  • where 早于 group by 早于 having

  • 即如果where和having一起用时，where会先执行，having后执行

• 子句有区别

  • having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以

• 总结：

  1. WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。

  2. GROUP BY 子句用来分组 WHERE 子句的输出。

  3. HAVING 子句用来从分组的结果中筛选行

sql select语句的执行顺序是什么？

1. from 子句组装来自不同数据源的数据；

2. where 子句基于指定的条件对记录行进行筛选；

3. group by 子句将数据划分为多个分组；

4. 使用聚集函数进行计算；

5. 使用 having 子句筛选分组；

6. 计算所有的表达式；

7. select 的字段；

8. 使用 order by 对结果集进行排序。

mysql有几种锁？怎么实现？

• 表级锁定 ：

  • 表独占写锁（Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

  • 表共享读锁（Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

  • MySQL各存储引擎中最大颗粒度的锁定机制

  • 现逻辑非常简单，带来的系统负面影响最小，获取锁和释放锁的速度很快，可以很好的避免困扰我们的死锁问题，但是系统资源争用的概率最高，并发能力差。

  • 主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎使用

  • MyISAM的表级锁定完全是由MySQL提供的表级锁定实现：

  • MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁

• 行级锁定 ：

  • InnoDB的行级锁定同样分为两种类型， 共享锁 和 排他锁 。

  • 在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

  • 

  • 意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。

  • 事务可以通过以下语句显示给记录集加共享锁或排他锁。

  • InnoDB行锁实现方式

  • 间隙锁 （Next-Key锁）：

  • 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
    排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE

  • InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

  • 防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

  • 为了满足其恢复和复制的需要。

  • 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。

  • 对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

  • 目的：

  • 锁定对象的颗粒度很小，发生锁定资源争用的概率也最小，能够给予程序尽可能大的并发能力。但是由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。

  • 此外，行级锁定也最容易发生死锁。

  • 使用行级锁定的主要是InnoDB存储引擎。

  • 行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的

• 页级锁定 ：

  • 特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

  • 另外，页级锁定和行级锁定一样，会发生死锁。

  • 使用页级锁定的主要是BerkeleyDB存储引擎。

• 总结：

  • 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

  • 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

  • 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

mysql mvcc是什么？如何实现？

• mvcc是指多版本并发控制 ，是MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

• 版本号：

  • 系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增

  • 事务版本号：事务开始时的系统版本号。

• 隐藏的列 ：

  • 创建版本号：指示创建一个数据行的快照时的系统版本号；

  • 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

  • MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

Undo日志是什么？如何实现？

• MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。

• 当对某个记录进行更新时，会将当前记录写入 undo log 中，并更新当前记录中 DB_ROLL_PTR 字段值，使其指向刚才的 undo log record，然后更新当前记录相关字段值，同时更新 DB_TRX_ID 字段，记录执行更新操作的事务 ID。

• 实现过程：

  • 将当前事务版本号作为数据行快照的删除版本号。

  • 将当前事务版本号作为更新前的数据行快照的删除版本号，并将当前事务版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

  • 将当前事务版本号作为数据行快照的创建版本号。

  • 多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。(可以通过回滚指针找到历史版本)（当然，只是活跃的事务，如果当前记录没有相关事务在操作，则会清理 undo log，就不能拿到历史版本数据了）

  • 事务所要读取的数据行快照的创建版本号必须小于该事务的事务版本号。

  • 事务读取的数据行的删除版本号必须为未指定或者大于当前事务的版本号

  • 满足上述要求的记录才能被事务查出来

  • 查询：将当前事务版本号作为数据行快照的创建版本号。

  • 插入： 将当前事务版本号作为数据行快照的创建版本号。

  • 更新： 将当前事务版本号作为更新前的数据行快照的删除版本号，并将当前事务版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

  • 删除：将当前事务版本号作为数据行快照的删除版本号。

• 回滚实现：

  • 利用undo 日志，当需要进行回滚的时候，使用回滚指针来反过来执行指令就能将数据库回滚。