JAVA面试篇（二）一MYSQL_java数据库面试

1.语法基础

1、MySQL有哪些数据库类型？

➢ 数值类型
有包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示 1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。
1）任何整数类型都可以加上 UNSIGNED 属性，表示无符号整数。
2）任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。
还有包括 FLOAT、DOUBLE、DECIMAL 在内的小数类型。

➢ 字符串类型
包括 VARCHAR、CHAR、TEXT、BLOB。
注意：VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数，而是代表字符的个数。

➢ 日期和时间类型
常用于表示日期和时间类型为 DATETIME、DATE 和 TIMESTAMP。
尽量使用 TIMESTAMP，空间效率高于 DATETIME。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

2、CHAR 和 VARCHAR 区别？

1）首先可以明确的是 CHAR 是定长的，而 VARCHAR 是可以变长。
➢ CHAR 会根据声明的字符串长度分配空间，并会使用空格对字符串右边进行尾部填充。所以在检索 CHAR 类型数据时
尾部空格会被删除，如保存的是字符串 'char '，但最后查询到的是 'char'。又因为长度固定，所以存储效率高于 VARCHAR 类型。
➢ VARCHAR 在 MySQL 5.0 之后长度支持到 65535 字节，但会在数据开头使用额外 1~2 个字节存储字符串长度
（列长度小于 255 字节时使用 1 字节表示，否则 2 字节），在结尾使用 1 字节表示字符串结束。

2）再者，在存储方式上，CHAR 对英文字符（ASCII）占用 1 字节，对一个汉字使用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。
虽然 VARCHAR 是根据字符串长度分配存储空间的，但在内存中依旧使用声明长度进行排序等作业，故在使用时仍需综合考量字段长度。

1
2
3
4
5
6
7
8
9

3、CHAR 和 VARCHAR 如何选择？

➢ 1）对于经常变更的数据来说，CHAR 比 VARCHAR更好，因为 CHAR 不容易产生碎片。
➢ 2）对于非常短的列或固定长度的数据（如 MD5），CHAR 比 VARCHAR 在存储空间上更有效率。
➢ 3）使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
➢ 4）尽量避免使用 TEXT/BLOB 类型，查询时会使用临时表，导致严重的性能开销。

1
2
3
4
5

4、CHAR，VARCHAR 和 Text 的区别？

1）长度区别
Char 范围是 0～255。
Varchar 最长是 64k（注意这里的 64k 是整个 row 的长度，要考虑到其它的 column，
还有如果存在 not null 的时候也会占用一位，对不同的字符集，有效长度还不一样，
比如 utf-8 的，最多 21845，还要除去别的column），但 Varchar 在一般情况下存储都够用了。

如果遇到了大文本，考虑使用 Text，最大能到 4G（其中 TEXT 长度 65,535 bytes，约64kb；
MEDIUMTEXT 长度 16,777,215 bytes，约 16 Mb；而 LONGTEXT 长度4,294,967,295 bytes，约 4Gb）。

2）效率区别
效率来说基本是 Char > Varchar > Text，但是如果使用的是 Innodb 引擎的话，推荐使用 Varchar 代替 Char。

3）默认值区别
Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.事物

1、什么是数据库的事务？

数据库的事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性
状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的典型应用场景，如转账。

1
2
3
4
5

2、什么是事务的四大特性（ACID）？

➢ 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用
➢ 一致性： 事务执行前后，数据保持一致，多个事务对同一个数据读取的结果是相同的
➢ 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的
➢ 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

1
2
3
4
5

3、事务的并发会导致那些问题？什么是脏读、幻读和不可重复度？

脏读、幻读和不可重复读。

➢ 脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，
那么 A 读取到的数据是脏数据。

➢ 不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务B 在事务 A 
多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

➢ 幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 
ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现
还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4、事务的隔离级别有哪些？

➢ 读未提交（READ UNCOMMITTED）
➢ 读已提交（READ COMMITTED）
➢ 可重复读（REPEATABLE READ） 默认
➢ 序列化（SERIALIZABLE）

串行化的隔离级别最高，读未提交的级别最低，级别越高，则执行效率就越低，所以在选择隔离级别时应该结合实际情况。

MySQL 支持以上四种隔离级别，默认为 Repeatable read (可重复读)；
而 Oracle 只支持 Serializeble(串行化) 级别和 Read committed(读已提交) 两种，其中默认为读已提交。

1
2
3
4
5
6
7
8
9
10

5、ACID 特性是如何实现的？

从四个维度去理解：
➢ 原子性：通过undolog来实现。
➢ 持久性：通过binlog、redolog来实现。
➢ 隔离性：通过（读写锁+MVCC）来实现。
➢ 一致性：MySQL通过原子性、持久性、隔离性最终实现数据一致性。

对MySQL来说，逻辑备份日志（binlog）、重做日志（redolog）、回滚日志（undolog）、
锁技术 + MVCC就是MySQL实现事务的基础。

1
2
3
4
5
6
7
8
9

3.锁

1、数据库锁的作用以及有哪些锁？

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，
锁机制就是这样的一个机制。即锁的作用是解决并发问题。

➢ 从锁的粒度划分，可以将锁分为表锁、行锁以及页锁。
	○ 行级锁：是锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。
		其加锁粒度最小，但加锁的开销也最大。
		行级锁开销大，加锁慢，且会出现死锁。但锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
	○ 表级锁：是粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。
	○ 页级锁：是粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。
		所以取了折中的页级，一次锁定相邻的一组记录。
	○ 开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

➢ 从使用性质划分，可以分为共享锁、排它锁以及更新锁。
	○ 共享锁（Share Lock）：S 锁，又称读锁，用于所有的只读数据操作。
		S 锁并非独占，允许多个并发事务对同一资源加锁，但加 S 锁的同时不允许加 X 锁，即资源不能被修改。
		S 锁通常读取结束后立即释放，无需等待事务结束。
	○ 排他锁（Exclusive Lock）：X 锁，又称写锁，表示对数据进行写操作。
		X 锁仅允许一个事务对同一资源加锁，且直到事务结束才释放，其他任何事务必须等到 X 锁被释放才能对该页进行访问。
		使用 select * from table_name for update; 语句产生 X 锁。
	○ 更新锁：U 锁，用来预定要对资源施加 X 锁，允许其他事务读，但不允许再施加 U 锁或 X 锁。
		当被读取的页将要被更新时，则升级为 X 锁，U 锁一直到事务结束时才能被释放。
		故 U 锁用来避免使用共享锁造成的死锁现象。
		
➢ 从主观上划分，又可以分为乐观锁和悲观锁。
	○ 乐观锁（Optimistic Lock）：顾名思义，从主观上认定资源是不会被修改的，
		所以不加锁读取数据，仅当更新时用版本号机制等确认资源是否被修改。
		乐观锁适用于多读的应用类型，可以系统提高吞吐量。
	○ 悲观锁（Pessimistic Lock）：正如其名，具有强烈的独占和排它特性，每次读取数据时都会认为会被其它事务修改，
		所以每次操作都需要加上锁。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

2、隔离级别和锁的关系？

➢1）在 Read Uncommitted 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；
➢2）在 Read Committed 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
➢3）在 Repeatable Read 级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，
	也就是必须等待事务执行完毕以后才释放共享锁；
➢4）在 SERIALIZABLE 级别下，限制性最强，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

1
2
3
4
5
6

3、InnoDB 中的锁算法？

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record + gap 锁定一个范围，包含记录本身
ref 锁机制与InnoDB锁算法

1
2
3
4
5

4、什么是快照读和当前读？

快照读就是读取的是快照数据，不加锁的简单 Select 都属于快照读。
SELECT * FROM player WHERE ...

当前读就是读的是最新数据，而不是历史的数据。加锁的 SELECT，或者对数据进行增删改都会进行当前读。
SELECT * FROM player LOCK IN SHARE MODE;
SELECT FROM player FOR UPDATE;
INSERT INTO player values ...
DELETE FROM player WHERE ...
UPDATE player SET ...

1
2
3
4
5
6
7
8
9
10

5、什么是 MVCC 以及实现？

MVCC 的英文全称是 Multiversion Concurrency Control，中文意思是多版本并发控制，
可以做到读写互相不阻塞，主要用于解决不可重复读和幻读问题时提高并发效率。

其原理是通过数据行的多个版本管理来实现数据库的并发控制，简单来说就是保存数据的历史版本。
可以通过比较版本号决定数据是否显示出来。读取数据的时候不需要加锁可以保证事务的隔离效果。
1
2
3
4
5

6、什么时死锁？

死锁是2+个线程在执行过程中， 因争夺资源而造成的相互等待的现象，若无外力作用，它们将无法推进下去。

1
2

7、死锁产生的4个必要条件

➢ 互斥条件
指进程对所分配的资源进行排他性使用，即一段时间内某资源只有一个进程占用，其他的进程请求资源只能等待，
直至被占有资源的进程得到释放。

➢ 请求和保留条件
指进程至少保持占用一个资源，但又提出新的资源请求，而该资源正被其他进程占用，
此时请求进程阻塞，但对以获得的其他资源保持不放。

➢ 不剥夺条件
指进程已获得的资源，在未使用完之前，不能剥夺，只能使用完时由自己释放。

➢ 环路等待条件
值发生死锁时，必然存在一个进程占用资源的环形链，即进程集合（P0，P1，P2, … Pn），
P0等待P1资源释放，P1等待P2资源释放，P3等待 … Pn等待P0资源释放。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

8、如何解决死锁

针对如上死锁案例，分析其对应语句执行计划如下：
通过执行计划可以看出，在查找需要更新的数据时使用的是索引扫描，比较耗费性能，这样就造成锁定资源时间过长，
增加了语句并发执行时产生死锁的概率。

处理方式：
➢ 1．在表上建立一个聚集索引。
➢ 2．对语句更新的相关字段建立包含索引。

优化后该语句执行计划如下：
优化后的执行计划使用了索引查找，将大幅提升该查询语句的性能，降低了锁定资源的时间，同时也减少了锁定资源的范围，
这样就降低了锁资源循环等待事件发生的概率，对于预防死锁的发生会有一定的作用。

死锁是无法完全避免的，但如果应用程序适当处理死锁，对涉及的任何用户及系统其余部分的影响可降至最低
（适当处理是指发生错误1205时，应用程序重新提交批处理，第二次尝试大多能成功。
一个进程被杀死，它的事务被取消，它的锁被释放，死锁中涉及到的另一个进程就可以完成它的工作并释放锁，
所以就不具备产生另一个死锁的条件了。）

--检测死锁；
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

--杀死死锁进程；
kill trx_mysql_thread_id;

--检测现在的死锁状态；
--重新执行需要正确执行的代码，运行成功，成功解决死锁；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

9、如何预防死锁

阻止死锁的途径就是避免满足死锁条件的情况发生，为此我们在开发的过程中需要遵循如下原则：
➢ 1.尽量避免并发的执行涉及到修改数据的语句。
➢ 2.要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。
➢ 3.预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。
	如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。
➢ 4.每个事务的执行时间不可太长，对程序段的事务可考虑将其分割为几个事务。
	在事务中不要求输入，应该在事务之前得到输入，然后快速执行事务。
➢ 5.使用尽可能低的隔离级别。
➢ 6.数据存储空间离散法。该方法是指采用各种手段，将逻辑上在一个表中的数据分散的若干离散的空间上去，
	以便改善对表的访问性能。主要通过将大表按行或者列分解为若干小表，或者按照不同的用户群两种方法实现。
➢ 7.编写应用程序，让进程持有锁的时间尽可能短，这样其它进程就不必花太长的时间等待锁被释放。
➢ 8.保持事务简短并在一个批处理中

1
2
3
4
5
6
7
8
9
10
11
12
13

4.存储引擎

1、有哪些常见的存储引擎？

MyISAM 和 InnoDB
1

2、MyISAM 和 InnoDB 的区别？

➢ 1）InnoDB 支持事务，而 MyISAM 不支持。
➢ 2）InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表 转为 MyISAM 表会失败。
➢ 3）InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。

➢ 4）InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。
	而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。
	那为什么 InnoDB 没有使用这样的变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。
	
➢ 5）InnoDB 支持表、行（默认）级锁，而 MyISAM 支持表级锁。
	InnoDB 的行锁是基于索引实现的，而不是物理行记录上。
	即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。
	
➢ 6）InnoDB 必须有唯一索引（如主键），如果没有指定，就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键，
	而 Myisam 可以没有主键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3、InnoDB 的四大特性?

➢ 插入缓冲insert buffer)
➢ 二次写(double write)
➢ 自适应哈希索引(ahi)
➢ 预读(read ahead)

1
2
3
4
5

4、InnoDB 为何推荐使用自增主键？

自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的，因此相比自定义 ID （如 UUID）可以避免 B+ 树的频繁合并和分裂。
如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

1
2
3

5、如何选择存储引擎？

默认使用 InnoDB，MyISAM 适用以插入为主的程序，比如博客系统、新闻门户。

1
2

5. 索引

首先了解一下什么是索引，索引是对数据库表中一列或多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息。

1、索引的几种类型或分类？

1）从物理结构上可以分为聚集索引和非聚集索引两类：
➢ 聚簇索引：指索引的键值的逻辑顺序与表中相应行的物理顺序一致，
		即每张表只能有一个聚簇索引，一般设置主键索引就为聚簇索引。
		
		在InnoDB中，数据是以B+树的形式来进行存储的，其特点就是数据都存储在叶子节点上，
		非叶子节点值存储索引信息（既Key和指向子节点的指针）。这种索引和数据的存储方式叫做聚簇索引。
		通常来说，“聚簇”的意思是数据行和键紧挨着。我们一般设置主键索引就为聚簇索引。
		
➢ 非聚簇索引：非聚簇索引的逻辑顺序则与数据行的物理顺序不一致。
		非聚簇索引同样是采用平衡树来作为索引的数据结构，索引树结构中各个节点的值来自于表中的索引字段。
		
➢ 聚簇索引和非聚簇索引的区别
	○ 聚集索引可以一次查到需要查找的数据，而通过非聚集索引第一次只能查到记录对应的主键值再去聚簇索引中
		查询所需要的记录，这个过程称为回表。正是由于如此，所以通常来说聚簇索引的查询效率要比非聚簇索引高。
	○ 聚集索引一张表只能有一个，而非聚集索引一张表可以有多个。

2）从应用上可以划分为一下几类：
➢ 普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了提高查询效率。
	通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；
	
➢ 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
	通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；
	
➢ 主键索引：特殊的唯一索引，也成聚簇索引，不允许有空值，并由数据库帮我们自动创建；

➢ 组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；

➢ 全文索引：只有在 MyISAM 引擎上才能使用，同时只支持 CHAR、VARCHAR、TEXT 类型字段上使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

2、索引的优缺点？

➢ 优点：
	○ 创建索引可以大大提高系统的性能。
	○ 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
	○ 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
	○ 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
	○ 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
	○ 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

➢ 缺点：
	○ 创建和维护索引需要耗费时间，这种时间随着数据量的增加而增加，这样就降低了数据的维护速度。
	○ 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
		如果要建立聚簇索引，那么需要的空间就会更大。
	
1
2
3
4
5
6
7
8
9
10
11
12
13

3、索引设计原则？

➢ 选择唯一性索引；
	唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
	
➢ 为常作为查询条件的字段建立索引；
	如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
	因此，为这样的字段建立索引，可以提高整个表的查询速度。
	
➢ 为经常需要排序、分组和联合操作的字段建立索引；
	经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。
	如果为其建立索引，可以有效地避免排序操作。
	
➢ 限制索引的数目；
	每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。
	
➢ 小表不建议索引（如数量级在百万以内）；
	由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
	
➢ 尽量使用数据量少的索引；
	如索引的值很长，那么查询的速度会受到影响。此时尽量使用前缀索引。
	
➢ 删除不再使用或者很少使用的索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

4、索引的数据结构？

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是 Hash 和 B+ 树索引。

➢ Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据

➢ B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，
最后查询判断是否需要回表查询

1
2
3
4
5
6
7

5、Hash 和 B+ 树索引的区别？

➢ Hash
	1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，
		索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。
	2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，
		如 AA 和 AB 的算出的值没有相关性。
	3）Hash 任何时候都避免不了回表查询数据.
	4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，
		此时查询效率反而可能降低。
➢ B+ Tree
	1）B+ 树本质是一棵查找树，自然支持范围查询和排序。
	2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。
	3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。
	
1
2
3
4
5
6
7
8
9
10
11
12
13

6.设计

1、什么是三大范式？

➢ 第一范式（1NF）：字段（或属性）是不可分割的最小单元，即不会有重复的列，体现原子性

➢ 第二范式（2NF）：满足 1NF 前提下，存在一个候选码，非主属性全部依赖该候选码，
	即存在主键，体现唯一性，专业术语则是消除部分函数依赖
	
➢ 第三范式（3NF）：满足 2NF 前提下，非主属性必须互不依赖，消除传递依赖

除了三大范式外，还有BC范式和第四范式，但其规范过于严苛，在生产中往往使用不到。

1
2
3
4
5
6
7
8
9

2、什么是范式和反范式，以及各自优缺点？

范式是符合某一种级别的关系模式的集合。构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。
1

所以在平时工作中，我们通常是将范式和反范式相互结合使用。

1
2

7.sql及优化

1.数据库优化的方案有哪些

➢ (1)查询时，能不用* 就不用，尽量写全字段名。
➢ (2)索引不是越多越好，每个表控制在6个索引以内。范围where条件的情况下，索引不起作用，比如where value<100
➢ (3)大部分情况连接效率远大于子查询，但是有例外。当你对连接查询的效率都感到不能接受的时候可以试试用子查询，
	虽然大部分情况下你会更失望，但总有碰到惊喜的时候不是么...
➢ (4)多用explain 和 profile分析查询语句
➢ (5)有时候可以1条大的SQL可以分成几个小SQL顺序执行，分了吧，速度会快很多。
➢ (6)每隔一段时间用alter table table_name engine=innodb;优化表
➢ (7)连接时注意:小表 jion 大表的原则
➢ (8)学会用explain 和 profile判断是什么原因使你的SQL慢
➢ (9)查看慢查询日志，找出执行时间长的SQL进行优化
➢ (10)如果排序没有用到索引，尽量避免使用order by
➢ (11)因为where子句后面的条件是执行顺序是从左到右，所以尽量把能过滤掉大部分数据的条件放在前面
➢ (12)使用union/union all 代替 or，能用union all就不用union
➢ (13)避免在where子句中对字段进行表达式操作
➢ (14)尽量使用inner join，避免left join：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2、Where 和 Having 的区别？

where 子句的作用是在对查询结果进行分组前，将不符合条件的行去掉，即在分组之前过滤数据，
where条件中不能包含聚组函数，使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，
使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

总结一下条件的过滤顺序：on->join->where->group by->having。

1
2
3
4
5
6
7
8

3、Drop、Delete 和 Truncate 的区别？

虽然通过 delete、truncate、drop 这三个关键字都可以用来删除数据，但场景不同。
从执行速度上讲：drop > truncate >> DELETE。

1
2
3

4、SQL 关键字的执行顺序？

5、In 和 Exists 的区别？

in 适合内表比外表数据小的情况，exists 适合内表比外表数据大的情况。
如果查询的内外表大小相当，则二者效率差别不大。

1
2
3

6、Union 和 Union All 的区别？

➢ Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
➢ Union All：对两个结果集进行并集操作，包括重复行，不进行排序
➢ Union 因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All。

1
2
3
4

7、如何判断 SQL 是否走了索引？

EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法，
使用 EXPLAIN 只需在查询语句开头增加 EXPLAIN 这个关键字即。

其结果中的几个重要参数：
	➢ id
	ID 代表执行 select 子句或操作表的顺序，如果包含子查询，则会出现多个 ID。
	值越大，优先级越高，越先被执行。值相同的按照由上至下的顺序执行。
	
	➢ select_type（查询类型）
	查询类型主要用于区别普通查询、联合查询以及子查询等复杂查询。
	
	➢ type
	查询扫描情况，最好到最差依次是：system>const>eq_ref>ref>range>index>All，
	一般情况下至少保证达到 range 级别，最好能达到 ref。
	
	➢ possible_keys
	显示可能应用在这张表中的索引，一个或多个。查询到的索引不一定是真正被使用。
	
	➢ key
	实际使用的索引，如果为 null 则表示没有使用索引。因此会出现 possible_keys 列有可能被用到的索引，但是 key 列为 null。
	
	➢ key_len
	表示索引中使用的字节数，在不损失精确性的情况下长度越短越好。key_len 显示的值为索引字段的最大可能长度，
	并非实际使用长度。即 key_len 是根据表定义计算而来。
	
	➢ ref
	显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值。
	
	➢ rows
	根据表统计信息及索引选用情况，估算出找到所需的记录所需要读取的行数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

8、索引失效的几种情况？

➢ 1）like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效；
➢ 2）or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有左右查询字段均为索引时，才会生效；
➢ 3）联合索引不使用第一列，索引失效；
➢ 4）数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描；
➢ 5）在索引列上使用 IS NULL 或 IS NOT NULL操作。最好给列设置默认值。
➢ 6）在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 
	优化方法： key<>0 改为 key>0 or key<0。
➢ 7）对索引字段进行计算操作、字段上使用函数。
➢ 8）当 MySQL 觉得全表扫描更快时（数据少）;

1
2
3
4
5
6
7
8
9
10

9、超大分页或深度分页如何处理？

说道 MySQL 的分页，我们首先想到的就是 offset、limit 操作，但随着页数的增加，查询性能指数级增大。
这是由于 MySQL 并不是跳过 offset 的行数，而是取 offset + limit 行，然后丢弃前 offset 行，
返回 limit 行，当offset特别大的时候，效率就非常的低下。

此处我们就可以采用覆盖索引+延迟关联技术来减少偏移量的定位进行优化：

##查询语句
select id from product limit 10000000, 10
##优化方式一
SELECT * FROM product WHERE ID > =(select id from product limit 10000000, 1) limit 10
##优化方式二
SELECT * FROM product a JOIN (select id from product limit 10000000, 10) b ON a.ID = b.id

1
2
3
4
5
6
7
8
9
10
11
12
13

10、大表查询如何优化？

可以从分库分表、读写分离以及缓存三个维度分别阐述。

1
2