MySQL面试重点
基础
1.1 为什么要使⽤数据库
**数据保存在内存**
优点： 存取速度快
缺点： 数据不能永久保存
**数据保存在⽂件**
优点： 数据永久保存
缺点：1）速度⽐内存操作慢，频繁的IO操作。2）查询数据不⽅便
**数据保存在数据库**
1）数据永久保存
2）使⽤SQL语句，查询⽅便效率⾼。
3）管理数据⽅便1.2 什么是SQL？
结构化查询语⾔(Structured Query Language)简称SQL，是⼀种数据库查询语⾔。
作⽤：⽤于存取数据、查询、更新和管理关系数据库系统。1.3 什么是MySQL?
MySQL是⼀个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流⾏的
关系型数据库管理系统之⼀，在 WEB 应⽤⽅⾯，MySQL是最好的 RDBMS (Relational Database Management
System，关系数据库管理系统) 应⽤软件之⼀。在Java企业级开发中⾮常常⽤，因为 MySQL 是开源免费的，并且
⽅便扩展。1.4 数据库三⼤范式是什么
第⼀范式：每个列都不可以再拆分。
第⼆范式：在第⼀范式的基础上，⾮主键列完全依赖于主键，⽽不能是依赖于主键的⼀部分。
第三范式：在第⼆范式的基础上，⾮主键列只依赖于主键，不依赖于其他⾮主键。
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有⾜够的理由。⽐如性能。事实上我们经常会为
了性能⽽妥协数据库的设计。1.5 mysql有关权限的表都有哪⼏个
MySQL服务器通过权限表来控制⽤户对数据库的访问，权限表存放在mysql数据库⾥，由mysql_install_db脚本初
始化。这些权限表分别user，db，table_priv，columns_priv和host。下⾯分别介绍⼀下这些表的结构和内容：
user权限表：记录允许连接到服务器的⽤户帐号信息，⾥⾯的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和
REVOKE语句的影响。1.6 MySQL的binlog有有⼏种录⼊格式？分别有什么区别？
有三种格式，statement，row和mixed。statement模式下，每⼀条会修改数据的sql都会记录在binlog中。不需要记录每⼀⾏的变化，减少了binlog⽇志量，节约了IO，提⾼性能。由于sql的执⾏是有上下⽂的，因此在保存的时候需要保存相关的信息，同时还有⼀些使⽤了函数之类的语句⽆法被记录复制。
row级别下，不记录sql语句上下⽂相关信息，仅保存哪条记录被修改。记录单元为每⼀⾏的改动，基本是可以
全部记下来但是由于很多操作，会导致⼤量⾏的改动(⽐如alter table)，因此这种模式的⽂件保存的信息太多，⽇志量太⼤。
mixed，⼀种折中的⽅案，普通操作使⽤statement记录，当⽆法使⽤statement的时候使⽤row。
此外，新版的MySQL中对row级别也做了⼀些优化，当表结构发⽣变化的时候，会记录语句⽽不是逐⾏记录。⼆ 数据类型和引擎分类
**类型名称** **说明****整数类型**
tinyInt 很⼩的整数(8位⼆进制)
smallint ⼩的整数(16位⼆进制)
mediumint 中等⼤⼩的整数(24位⼆进制)
int(integer) 普通⼤⼩的整数(32位⼆进制)
**⼩数类型**
float 单精度浮点数
double 双精度浮点数
decimal(m,d) 压缩严格的定点数
**⽇期类型**
year
YYYY 1901~2155
time
HH:MM:SS -838:59:59~838:59:59
date
YYYY-MM-DD 1000-01-01~9999-12-3
datetime
YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
timestamp
YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
**⽂本、⼆进制类**
**型**
CHAR(M) M为0~255之间的整数
VARCHAR(M) M为0~65535之间的整数
TINYBLOB 允许⻓度0~255字节
BLOB 允许⻓度0~65535字节
MEDIUMBLOB 允许⻓度0~167772150字节
LONGBLOB 允许⻓度0~4294967295字节
TINYTEXT 允许⻓度0~255字节
TEXT 允许⻓度0~65535字节
MEDIUMTEXT 允许⻓度0~167772150字节
LONGTEXT 允许⻓度0~4294967295字节
VARBINARY(M) 允许⻓度0~M个字节的变⻓字节字符串
BINARY(M) 允许⻓度0~M个字节的定⻓字节字符串2.1 mysql有哪些数据类型
1、整数类型 ，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4
字节、8字节整数。任何整数类型都可以加上UNSIGNED属性，表示数据是⽆符号的，即⾮负整数。
⻓度 ：整数类型可以被指定⻓度，例如：INT(11)表示⻓度为11的INT类型。⻓度在⼤多数场景是没有意义的，
它不会限制值的合法范围，只会影响显示字符的个数，⽽且需要和UNSIGNED ZEROFILL属性配合使⽤才有意
义。
例⼦ ，假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果⽤户插⼊的数据为12的话，那么数据库实
际存储数据为00012。
2、实数类型 ，包括FLOAT、DOUBLE、DECIMAL。
DECIMAL可以⽤于存储⽐BIGINT还⼤的整型，能存储精确的⼩数。⽽FLOAT和DOUBLE是有取值范围的，并⽀持使⽤标准的浮点进⾏近似计算。
计算时FLOAT和DOUBLE相⽐DECIMAL效率更⾼⼀些，DECIMAL你可以理解成是⽤字符串进⾏处理。
3、字符串类型 ，包括VARCHAR、CHAR、TEXT、BLOB
VARCHAR⽤于存储可变⻓字符串，它⽐定⻓类型更节省空间。
VARCHAR使⽤额外1或2个字节存储字符串⻓度。列⻓度⼩于255字节时，使⽤1字节表示，否则使⽤2字节表
示。
VARCHAR存储的内容超出设置的⻓度时，内容会被截断。
CHAR是定⻓的，根据定义的字符串⻓度分配⾜够的空间。
CHAR会根据需要使⽤空格进⾏填充⽅便⽐较。
CHAR适合存储很短的字符串，或者所有值都接近同⼀个⻓度。
CHAR存储的内容超出设置的⻓度时，内容同样会被截断。
**使⽤策略：**
对于经常变更的数据来说，CHAR⽐VARCHAR更好，因为CHAR不容易产⽣碎⽚。
对于⾮常短的列，CHAR⽐VARCHAR在存储空间上更有效率。
使⽤时要注意只分配需要的空间，更⻓的列排序时会消耗更多内存。尽量避免使⽤TEXT/BLOB类型，查询时会使⽤临时表，导致严重的性能开销。
4、枚举类型（ENUM） ，把不重复的数据存储为⼀个预定义的集合。
有时可以使⽤ENUM代替常⽤的字符串类型。
ENUM存储⾮常紧凑，会把列表值压缩到⼀个或两个字节。
ENUM在内部存储时，其实存的是整数。
尽量避免使⽤数字作为ENUM枚举的常量，因为容易混乱。
排序是按照内部存储的整数
5、⽇期和时间类型 ，尽量使⽤timestamp，空间效率⾼于datetime，
⽤整数保存时间戳通常不⽅便处理。如果需要存储微妙，可以使⽤bigint存储。2.2 MySQL存储引擎MyISAM与InnoDB区别
存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是⼀套⽂件系统的实现。
常⽤的存储引擎有以下：
**Innodb**引擎：Innodb引擎提供了对数据库ACID事务的⽀持。并且还提供了⾏级锁和外键的约束。它的设计的
⽬标就是处理⼤数据容量的数据库系统。
**MyIASM**引擎(原本Mysql的默认引擎)：不提供事务的⽀持，也不⽀持⾏级锁和外键。
**MEMORY**引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不⾼。
MyISAM与InnoDB区别MyISAM
**Innodb**
存储结构
每张表被存放在三个⽂件：frm-表格定义、MYD(MYData)-数据⽂件、MYI(MYIndex)-索引⽂件
所有的表都保存在同⼀个数据⽂件中（也可能是多个⽂件，或者是独⽴的表空间⽂件），InnoDB表的⼤⼩只受限于操作系统⽂件的⼤⼩，⼀般为2GB存储空间 MyISAM可被压缩，存储空间较⼩ InnoDB的表需要更多的内存和存储，它会在主内存中建⽴其专⽤的缓冲池⽤于⾼速缓冲
数据和索引可移植性、备份及恢复
由于MyISAM的数据是以⽂件的形式存储，所以在跨平台的数据转移中会很⽅便。在备份和恢复时可单独针对某个表进⾏操作免费的⽅案可以是拷⻉数据⽂件、备份binlog，或者⽤ mysqldump，在数据量达到⼏⼗G的时候就相对痛苦了
⽂件格式
数据和索引是分别存储的，数据 .MYD ，索引 .MYI数据和索引是集中存储的， .ibd记录存储顺序 按记录插⼊顺序保存 按主键⼤⼩有序插⼊
外键 不⽀持
⽀持事务
锁⽀持（锁是避免资源争⽤的⼀个机制，MySQL锁对⽤户⼏乎是透明的）
表级锁定 ⾏级锁定、表级锁定，锁定⼒度⼩并发能⼒⾼
SELECT MyISAM更优
INSERT、UPDATE、DELETEInnoDB更优
select count(*)
myisam更快，因为myisam内部维护了⼀个计数器，可以直接调取。
索引的实现⽅式
B+树索引，myisam 是堆表
B+树索引，Innodb 是索引组织表2.3 MyISAM索引与InnoDB索引的区别？
InnoDB索引是聚簇索引，MyISAM索引是⾮聚簇索引。
InnoDB的主键索引的叶⼦节点存储着⾏数据，因此主键索引⾮常⾼效。
MyISAM索引的叶⼦节点存储的是⾏数据地址，需要再寻址⼀次才能得到数据。
InnoDB⾮主键索引的叶⼦节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会⾮常⾼效。**2.4 InnoDB****引擎的****4****⼤特性**
插⼊缓冲（insert buffer)
⼆次写(double write)
⾃适应哈希索引(ahi)
预读(read ahead)
**存储引擎选择**
如果没有特别的需求，使⽤默认的 Innodb 即可。
MyISAM：以读写插⼊为主的应⽤程序，⽐如博客系统、新闻⻔户⽹站。
Innodb：更新（删除）操作频率也⾼，或者要保证数据的完整性；并发量⾼，⽀持事务和外键。⽐如OA⾃动化办
公系统。三 索引
3.1 什么是索引？
索引是⼀种特殊的⽂件(InnoDB数据表上的索引是表空间的⼀个组成部分)，它们包含着对数据表⾥所有记录的引⽤
指针。
索引是⼀种数据结构。数据库索引，是数据库管理系统中⼀个排序的数据结构，以协助快速查询、更新数据库表中
数据。索引的实现通常使⽤B树及其变种B+树。
更通俗的说，索引就相当于⽬录。为了⽅便查找书中的内容，通过对内容建⽴索引形成⽬录。索引是⼀个⽂件，它
是要占据物理空间的。3.2 索引有哪些优缺点？
索引的优点
可以⼤⼤加快数据的检索速度，这也是创建索引的最主要的原因。
通过使⽤索引，可以在查询的过程中，使⽤优化隐藏器，提⾼系统的性能。
索引的缺点
时间⽅⾯：创建索引和维护索引要耗费时间，具体地，当对表中的数据进⾏增加、删除和修改的时候，索引也
要动态的维护，会降低增/改/删的执⾏效率；
空间⽅⾯：索引需要占物理空间。3.3 索引使⽤场景（重点）
### where
上图中，根据 id 查询记录，因为 id 字段仅建⽴了主键索引，因此此SQL执⾏可选的索引只有主键索引，如果有
多个，最终会选⼀个较优的作为检索的依据。
可以尝试在⼀个字段未建⽴索引时，根据该字段查询的效率，然后对该字段建⽴索引（ alter table 表
名 add index(字段名) ），同样的SQL执⾏的效率，你会发现查询效率会有明显的提升（数据量越⼤越明
显）。
### order by
当我们使⽤ order by 将查询结果按照某个字段排序时，如果该字段没有建⽴索引，那么执⾏计划会将查询出的所
有数据使⽤外部排序（将数据从硬盘分批读取到内存使⽤内部排序，最后合并排序结果），这个操作是很影响性能
的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过⼤或者数据量过多都会降低效率），更
⽆论读到内存之后的排序了。
但是如果我们对该字段建⽴索引 alter table 表名 add index(字段名) ，那么由于索引本身是有序的，因此直接按
照索引的顺序和映射关系逐条取出数据即可。⽽且如果分⻚的，那么只⽤**取出索引表某个范围内的索引对应的数**
**据**，⽽不⽤像上述那**取出所有数据**进⾏排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）
-- 增加⼀个没有建⽴索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';
### join
对 join 语句匹配关系（ on ）涉及的字段建⽴索引能够提⾼效率
索引覆盖
如果要查询的字段都建⽴过索引，那么引擎会直接在索引表中查询⽽不会访问原始数据（否则只要有⼀个字段没有
建⽴索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在 select 后只写必要的查询字段，以增加索
引覆盖的⼏率。
这⾥值得注意的是不要想着为每个字段建⽴索引，因为优先使⽤索引的优势就在于其体积⼩。3.4 索引有哪种类型？
**主键索引****:** 数据列不允许重复，不允许为NULL，⼀个表只能有⼀个主键。
**唯⼀索引****:** 数据列不允许重复，允许为NULL值，⼀个表允许多个列创建唯⼀索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯⼀索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯⼀组合索引
**普通索引****:** 基本的索引类型，没有唯⼀性的限制，允许为NULL值。
可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引
可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3); 创建组合索引
**全⽂索引：** 是⽬前搜索引擎使⽤的⼀种关键技术。
可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全⽂索引3.5 索引的数据结构（b树，hash）
索引的数据结构和具体存储引擎的实现有关，在MySQL中使⽤较多的索引有**Hash****索引**，**B+****树索引**等，⽽我们经常
使⽤的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在
绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索
引。
1）B树索引
mysql通过存储引擎取数据，基本上90%的⼈⽤的就是InnoDB了，按照实现⽅式分，InnoDB的索引类型⽬前只有
两种：BTREE（
B树）索引和HASH索引。B树索引是Mysql数据库中使⽤最频繁的索引类型，基本所有存储引擎都
⽀持BTree索引。通常我们说的索引不出意外指的就是（
B树）索引（实际是⽤B+树实现的，因为在查看表索引
时，mysql⼀律打印BTREE，所以简称为B树索引）查询⽅式：
主键索引区:PI(关联保存的时数据的地址)按主键查询,
普通索引区:si(关联的id的地址,然后再到达上⾯的地址)。所以按主键查询,速度最快
B+tree性质：
1.）n棵⼦tree的节点包含n个关键字，不⽤来保存数据⽽是保存数据的索引。
2.）所有的叶⼦结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶⼦结点本身依关键字的⼤
⼩⾃⼩⽽⼤顺序链接。
3.）所有的⾮终端结点可以看成是索引部分，结点中仅含其⼦树中的最⼤（或最⼩）关键字。
4.）B+ 树中，数据对象的插⼊和删除仅在叶节点上进⾏。
5.）B+树有2个头指针，⼀个是树的根节点，⼀个是最⼩关键码的叶节点。
2）哈希索引
简要说下，类似于数据结构中简单实现的HASH表（散列表）⼀样，当我们在mysql中⽤哈希索引时，主要就是通
过Hash算法（常⻅的Hash算法有直接定址法、平⽅取中法、折叠法、除数取余法、随机数法），将数据库字段数
据转换成定⻓的Hash值，与这条数据的⾏指针⼀并存⼊Hash表的对应位置；如果发⽣Hash碰撞（两个不同关键字
的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。**3.6** **索引的基本原理**
索引⽤来快速地寻找那些具有特定值的记录。如果没有索引，⼀般来说执⾏查询时遍历整张表。
索引的原理很简单，就是把⽆序的数据变成有序的查询
\1. 把创建了索引的列的内容进⾏排序
\2. 对排序结果⽣成倒排表
\3. 在倒排表内容上拼上数据地址链
\4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从⽽拿到具体数据3.7 索引算法有哪些？
索引算法有 BTree算法和Hash算法
**BTree****算法**
BTree是最常⽤的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被⽤在=,>,>=,<,<=和between这
些⽐较操作符上，⽽且还可以⽤于like操作符，只要它的查询条件是⼀个不以通配符开头的常量， 例如：
**Hash****算法**
-- 只要它的查询条件是⼀个不以通配符开头的常量
select * from user where name like 'jack%';
-- 如果⼀通配符开头，或者没有使⽤常量，则不会使⽤索引，例如：
select * from user where name like '%jack';
1234Hash Hash索引只能⽤于对等⽐较，例如=,<=>（相当于=）操作符。由于是⼀次定位数据，不像BTree索引需要从
根节点到枝节点，最后才能访问到⻚节点这样多次IO访问，所以检索效率远⾼于BTree索引。3.8 索引设计的原则？
\1. 适合索引的列是出现在where⼦句中的列，或者连接⼦句中指定的列
\2. 基数较⼩的类，索引效果较差，没有必要在此列建⽴索引
\3. 使⽤短索引，如果对⻓字符串列进⾏索引，应该指定⼀个前缀⻓度，这样能够节省⼤量索引空间
\4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进⾏更新甚⾄
重构，索引列越多，这个时间就会越⻓。所以只保持需要的索引有利于查询即可。3.9 创建索引的原则（重中之重）
索引虽好，但也不是⽆限制的使⽤，最好符合⼀下⼏个原则
1） 最左前缀匹配原则，组合索引⾮常重要的原则，mysql会⼀直向右匹配直到遇到范围查询(>、<、between、
like)就停⽌匹配，⽐如a = 1 and b = 2 and c > 3 and d = 4 如果建⽴(a,b,c,d)顺序的索引，d是⽤不到索引的，如果
建⽴(a,b,d,c)的索引则都可以⽤到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男⼥未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。⽐如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引
即可。
6）定义有外键的数据列⼀定要建⽴索引。
7）对于那些查询中很少涉及的列，重复值⽐较多的列不要建⽴索引。
8）对于定义为text、image和bit的数据类型的列不要建⽴索引。3.10 创建索引的三种⽅式，删除索引
第⼀种⽅式：在执⾏CREATE TABLE时创建索引CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
12345678910
第⼆种⽅式：使⽤ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
1
ALTER TABLE⽤来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进⾏索引，多列时各列之间⽤逗号分隔。
索引名index_name可⾃⼰命名，缺省时，MySQL将根据第⼀个索引列赋⼀个名称。另外，ALTER TABLE允许在单
个语句中更改多个表，因此可以在同时创建多个索引。
第三种⽅式：使⽤CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);
1
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）
删除索引
根据索引名删除普通索引、唯⼀索引、全⽂索引： alter table 表名 drop KEY 索引名
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
123删除主键索引： alter table 表名 drop primary key （因为主键只有⼀个）。这⾥值得注意的是，如果主键⾃增
⻓，那么不能直接执⾏此操作（⾃增⻓依赖于主键索引）：
需要取消⾃增⻓再⾏删除：
但通常不会删除主键，因为设计主键⼀定与业务逻辑⽆关。3.11 创建索引时需要注意什么？
⾮空字段：应该指定列为NOT NULL，除⾮你想存储NULL。在mysql中，含有空值的列很难进⾏查询优化，因
为它们使得索引、索引的统计信息以及⽐较运算更加复杂。你应该⽤0、⼀个特殊的值或者⼀个空串代替空
值；
取值离散⼤的字段：（变量各个取值之间的差异程度）的列放到联合索引的前⾯，可以通过count()函数查看字
段的差异值，返回值越⼤说明字段的唯⼀值越多字段的离散程度⾼；
索引字段越⼩越好：数据库的数据存储以⻚为单位⼀⻚存储的数据越多⼀次IO操作获取的数据越⼤效率越⾼。3.12 使⽤索引查询⼀定能提⾼查询的性能吗？为什么
通常，通过索引查询数据⽐全表扫描要快。但是我们也必须注意到它的代价。
索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。
这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储
空间和处理，那些不必要的索引反⽽会使查询反应时间变慢。使⽤索引查询不⼀定能提⾼查询性能，索引范围
查询(INDEX RANGE SCAN)适⽤于两种情况:
基于⼀个范围的检索，⼀般查询返回结果集⼩于表中记录数的30%
基于⾮唯⼀性索引的检索alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY3.13 百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引⽂件是单独存在的⽂件,所以当我们对数据的增加,修改,删除,都
会产⽣额外的对索引⽂件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执⾏效率。所以，在我们删除数据库
百万级别数据的时候，查询MySQL官⽅⼿册得知删除数据的速度和创建的索引数量是成正⽐的。
\1. 所以我们想要删除百万数据的时候可以先删除索引（此时⼤概耗时三分多钟）
\2. 然后删除其中⽆⽤数据（此过程需要不到两分钟）
\3. 删除完成后重新创建索引(此时数据较少了)创建索引也⾮常快，约⼗分钟左右。
\4. 与之前的直接删除绝对是要快速很多，更别说万⼀删除中断,⼀切删除会回滚。那更是坑了。3.14 前缀索引
语法： index(field(10)) ，使⽤字段值的前10个字符建⽴索引，默认是使⽤字段的全部内容建⽴索引。
前提：前缀的标识度⾼。⽐如密码就适合建⽴前缀索引，因为密码⼏乎各不相同。
实操的难度：在于前缀截取的⻓度。
我们可以利⽤ select count(*)/count(distinct left(password,prefixLen)); ，通过从调整 prefixLen 的值
（从1⾃增）查看不同前缀⻓度的⼀个平均匹配度，接近1时就可以了（表示⼀个密码的前 prefixLen 个字符⼏乎
能确定唯⼀⼀条记录）3.15 什么是最左前缀原则？什么是最左匹配原则
顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where⼦句中使⽤最频繁的⼀列放在最左
边。
最左前缀匹配原则，⾮常重要的原则，mysql会⼀直向右匹配直到遇到范围查询(>、<、between、like)就停⽌
匹配，⽐如a = 1 and b = 2 and c > 3 and d = 4 如果建⽴(a,b,c,d)顺序的索引，d是⽤不到索引的，如果建⽴
(a,b,d,c)的索引则都可以⽤到，a,b,d的顺序可以任意调整。
=和in可以乱序，⽐如a = 1 and b = 2 and c = 3 建⽴(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化
成索引可以识别的形式3.16 B树和B+树的区别
在B树中，你可以将键和值存放在内部节点和叶⼦节点；但在B+树中，内部节点都是键，没有值，叶⼦节点同
时存放键和值。
B+树的叶⼦节点有⼀条链相连，⽽B树的叶⼦节点各⾃独⽴。**3.17** **使⽤****B****树的好处**
B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地⽅将会⼤⼤提⾼热点数据的查
询效率。这种特性使得B树在特定数据重复多次查询的场景中更加⾼效。3.18 使⽤B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，⼀次读取，可以在内存⻚中获取更多的键，有利于更快地缩⼩查
找范围。 B+树的叶节点由⼀条链相连，因此，当需要进⾏⼀次全数据遍历的时候，B+树只需要使⽤O(logN)时间找
到最⼩的⼀个节点，然后通过链进⾏O(N)的顺序遍历即可。⽽B树则需要对树的每⼀层进⾏遍历，这会需要更多的
内存置换次数，因此也就需要花费更多的时间3.19 Hash索引和B+树所有有什么区别或者说优劣呢?
⾸先要知道Hash索引和B+树索引的底层实现原理：
hash索引底层就是hash表，进⾏查找时，调⽤⼀次hash函数就可以获取到相应的键值，之后进⾏回表查询获得实
际数据。B+树底层实现是多路平衡查找树。对于每⼀次的查询都是从根节点出发，查找到叶⼦节点⽅可以获得所查
键值，然后根据查询判断是否需要回表查询数据。
那么可以看出他们有以下的不同：
hash索引进⾏等值查询更快(⼀般情况下)，但是却⽆法进⾏范围查询。
因为在hash索引中经过hash函数建⽴索引之后，索引的顺序与原顺序⽆法保持⼀致，不能⽀持范围查询。⽽B+树
的的所有节点皆遵循(左节点⼩于⽗节点，右节点⼤于⽗节点，多叉树也类似)，天然⽀持范围。
hash索引不⽀持使⽤索引进⾏排序，原理同上。
hash索引不⽀持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB
的索引没有相关性。
hash索引任何时候都避免不了回表查询数据，⽽B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通
过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在⼤量重复的时候，发⽣hash碰撞，此时效率可能极差。⽽B+树的查询效率⽐较稳定，对于所有的查询都是从根节点到叶⼦节点，且树的⾼度
较低。
因此，在⼤多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。⽽不需要使⽤hash索引。3.20 数据库为什么使⽤B+树⽽不是B树
B树只适合随机检索，⽽B+树同时⽀持随机检索和顺序检索；
B+树空间利⽤率更⾼，可减少I/O次数，磁盘读写代价更低。⼀般来说，索引本身也很⼤，不可能全部存储在
内存中，因此索引往往以索引⽂件的形式存储的磁盘上。这样的话，索引查找过程中就要产⽣磁盘I/O消耗。
B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使⽤，其内部结点⽐B树⼩，盘块能容纳的
结点中关键字数量更多，⼀次性读⼊内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。⽽
IO读写次数是影响索引检索效率的最⼤因素；
B+树的查询效率更加稳定。B树搜索有可能会在⾮叶⼦结点结束，越靠近根节点的记录查找时间越短，只要找
到关键字即可确定记录的存在，其性能等价于在关键字全集内做⼀次⼆分查找。⽽在B+树中，顺序检索⽐较明
显，随机检索时，任何关键字的查找都必须⾛⼀条从根节点到叶节点的路，所有关键字的查找路径⻓度相同，
导致每⼀个关键字的查询效率相当。
B-树在提⾼了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶⼦节点使⽤指针顺序连接在
⼀起，只要遍历叶⼦节点就可以实现整棵树的遍历。⽽且在数据库中基于范围的查询是⾮常频繁的，⽽B树不
⽀持这样的操作。
增删⽂件（节点）时，效率更⾼。因为B+树的叶⼦节点包含所有关键字，并以有序的链表结构存储，这样可很
好提⾼增删效率。**3.21 B+**树在满⾜聚簇索引和覆盖索引的时候不需要回表查询数据
在B+树的索引中，叶⼦节点可能存储了当前的key值，也可能存储了当前的key值以及整⾏的数据，这就是聚簇索
引和⾮聚簇索引。 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选⼀个唯⼀键建⽴聚簇索引。如
果没有唯⼀键，则隐式的⽣成⼀个键来建⽴聚簇索引。
当查询使⽤聚簇索引时，在对应的叶⼦节点，可以获取到整⾏数据，因此不⽤再次进⾏回表查询。3.22 什么是聚簇索引？何时使⽤聚簇索引与⾮聚簇索引
聚簇索引：将数据存储与索引放到了⼀块，找到索引也就找到了数据
⾮聚簇索引：将数据存储于索引分开结构，索引结构的叶⼦节点指向了数据的对应⾏，myisam通过key_buffer
把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找
到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清⼀个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要⼆次查找，⾮
聚簇索引都是辅助索引，像复合索引、前缀索引、唯⼀索引，辅助索引叶⼦节点存储的不再是⾏的物理位置，⽽是
主键值3.23 ⾮聚簇索引⼀定会回表查询吗？
不⼀定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进⾏回表查
询。
举个简单的例⼦，假设我们在员⼯表的年龄上建⽴了索引，那么当进⾏ select age from employee where age <
20 的查询时，在索引的叶⼦节点上，已经包含了age信息，不会再次进⾏回表查询。3.24 联合索引是什么？为什么需要注意联合索引中的顺序？
MySQL可以使⽤多个字段同时建⽴⼀个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建⽴索
引时的字段顺序挨个使⽤，否则⽆法命中索引。
具体原因为:
MySQL使⽤索引时需要索引有序，假设现在建⽴了"name，age，school"的联合索引，那么索引的排序为: 先按照
name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进⾏排序。
当进⾏查询时，此时索引仅仅按照name严格有序，因此必须⾸先使⽤name字段进⾏等值查询，之后对于匹配到的
列⽽⾔，其按照age字段严格有序，此时可以使⽤age字段⽤做索引查找，以此类推。因此在建⽴联合索引的时候应
该注意索引列的顺序，⼀般情况下，将查询需求频繁或者字段选择性⾼的列放在前⾯。此外可以根据特例的查询或
者表结构进⾏单独的调整。四 事务四 事务
4.1 什么是数据库事务？
事务是⼀个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执⾏的结果必须使数据库从⼀种⼀致
性状态变到另⼀种⼀致性状态。事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。
事务最经典也经常被拿出来说例⼦就是转账了。
假如⼩明要给⼩红转账1000元，这个转账会涉及到两个关键操作就是：将⼩明的余额减少1000元，将⼩红的余额
增加1000元。万⼀在这两个操作之间突然出现错误⽐如银⾏系统崩溃，导致⼩明余额减少⽽⼩红的余额没有增加，
这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。4.2 事物的四⼤特性(ACID)介绍⼀下?
关系性数据库需要遵循ACID规则，具体内容如下：
\1. **原⼦性：** 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作⽤；
\2. **⼀致性：** 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
\3. **隔离性：** 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
\4. **持久性：** ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何
影响。4.3 什么是脏读？幻读？不可重复读？
脏读(Drity Read)：某个事务已更新⼀份数据，另⼀个事务在此时读取了同⼀份数据，由于某些原因，前⼀个
RollBack了操作，则后⼀个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在⼀个事务的两次查询之中数据不⼀致，这可能是两次查询过程中间插⼊了
⼀个事务更新的原有的数据。
幻读(Phantom Read):在⼀个事务的两次查询中数据笔数不⼀致，例如有⼀个事务查询了⼏列(Row)数据，⽽另
⼀个事务却在此时插⼊了新的⼏列数据，先前的事务在接下来的查询中，就会发现有⼏列数据是它先前所没有
的。4.4 什么是事务的隔离级别？MySQL的默认隔离级别是什么？
为了达到事务的四⼤特性，数据库定义了4种不同的事务隔离级别，由低到⾼依次为Read uncommitted、Read
committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这⼏类问题。**隔离级别**
**脏读**
**不可重复读**
**幻影读**
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
**SQL** **标准定义了四个隔离级别：**
**READ-UNCOMMITTED(****读取未提交****)****：** 最低的隔离级别，允许读取尚未提交的数据变更，**可能会导致脏读、**
**幻读或不可重复读**。
**READ-COMMITTED(****读取已提交****)****：** 允许读取并发事务已经提交的数据，**可以阻⽌脏读，但是幻读或不可重复**
**读仍有可能发⽣**。
**REPEATABLE-READ(****可重复读****)****：** 对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修
改，**可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣**。
**SERIALIZABLE(****可串⾏化****)****：** 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事
务之间就完全不可能产⽣⼲扰，也就是说，**该级别可以防⽌脏读、不可重复读以及幻读**。
这⾥需要注意的是：Mysql 默认采⽤的 REPEATABLE_READ隔离级别 Oracle 默认采⽤的 READ_COMMITTED隔离
级别
事务隔离机制的实现基于锁机制和并发调度。其中并发调度使⽤的是MVVC（多版本并发控制），通过保存修改的
旧版本信息来⽀持并发⼀致性读和回滚等特性。
因为隔离级别越低，事务请求的锁越少，所以⼤部分数据库系统的隔离级别都是**READ-COMMITTED(****读取提交内**
**容****):**，但是你要知道的是InnoDB 存储引擎默认使⽤ **REPEATABLE-READ****（可重读）**并不会有任何性能损失。
InnoDB 存储引擎在 **分布式事务** 的情况下⼀般会⽤到**SERIALIZABLE(****可串⾏化****)**隔离级别。五 锁
5.1 对MySQL的锁了解吗
当数据库有并发事务的时候，可能会产⽣数据的不⼀致，这时候需要⼀些机制来保证访问的次序，锁机制就是这样
的⼀个机制。
就像酒店的房间，如果⼤家随意进出，就会出现多⼈抢夺同⼀个房间的情况，⽽在房间上装上锁，申请到钥匙的⼈
才可以⼊住并且将房间锁起来，其他⼈只有等他使⽤完毕才可以再次使⽤。5.2 隔离级别与锁的关系
在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
在Read Committed级别下，读操作需要加共享锁，但是在语句执⾏完以后释放共享锁；
在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执
⾏完毕以后才释放共享锁。
SERIALIZABLE 是限制性最强的隔离级别，因为该级别**锁定整个范围的键**，并⼀直持有锁，直到事务完成。5.3 按照锁的粒度分数据库锁有哪些？锁机制与InnoDB锁算法
在关系型数据库中，可以**按照锁的粒度把数据库锁分**为⾏级锁(INNODB引擎)、表级锁(MYISAM引擎)和⻚级锁(BDB
引擎 )。
**MyISAM****和****InnoDB****存储引擎使⽤的锁：**
MyISAM采⽤表级锁(table-level locking)。
InnoDB⽀持⾏级锁(row-level locking)和表级锁，默认为⾏级锁
⾏级锁，表级锁和⻚级锁对⽐
**⾏级锁** ⾏级锁是Mysql中锁定粒度最细的⼀种锁，表示只针对当前操作的⾏进⾏加锁。⾏级锁能⼤⼤减少数据库操
作的冲突。其加锁粒度最⼩，但加锁的开销也最⼤。⾏级锁分为共享锁 和 排他锁。
特点：开销⼤，加锁慢；会出现死锁；锁定粒度最⼩，发⽣锁冲突的概率最低，并发度也最⾼。
**表级锁** 表级锁是MySQL中锁定粒度最⼤的⼀种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，
被⼤部分MySQL引擎⽀持。最常使⽤的MYISAM与INNODB都⽀持表级锁定。表级锁定分为表共享读锁（共享锁）
与表独占写锁（排他锁）。
特点：开销⼩，加锁快；不会出现死锁；锁定粒度⼤，发出锁冲突的概率最⾼，并发度最低。
**⻚级锁** ⻚级锁是MySQL中锁定粒度介于⾏级锁和表级锁中间的⼀种锁。表级锁速度快，但冲突多，⾏级冲突少，
但速度慢。所以取了折衷的⻚级，⼀次锁定相邻的⼀组记录。
特点：开销和加锁时间界于表锁和⾏锁之间；会出现死锁；锁定粒度界于表锁和⾏锁之间，并发度⼀般5.4 从锁的类别上分MySQL都有哪些锁呢？像上⾯那样⼦进⾏锁定岂不是有点阻碍并发效率了
**从锁的类别上来讲**，有共享锁和排他锁。
共享锁: ⼜叫做读锁。 当⽤户要进⾏数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。排他锁: ⼜叫做写锁。 当⽤户要进⾏数据的写⼊时，对数据加上排他锁。排他锁只可以加⼀个，他和其他的排他
锁，共享锁都相斥。
⽤上⾯的例⼦来说就是⽤户的⾏为有两种，⼀种是来看房，多个⽤户⼀起看房是可以接受的。 ⼀种是真正的⼊住⼀
晚，在这期间，⽆论是想⼊住的还是想看房的都不可以。
锁的粒度取决于具体的存储引擎，InnoDB实现了⾏级锁，⻚级锁，表级锁。
他们的加锁开销从⼤到⼩，并发能⼒也是从⼤到⼩。5.5 MySQL中InnoDB引擎的⾏锁是怎么实现的
答：InnoDB是基于索引来完成⾏锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成⾏锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，
并发将⽆从谈起5.6 InnoDB存储引擎的锁的算法有三种
Record lock：单个⾏记录上的锁
Gap lock：间隙锁，锁定⼀个范围，不包括记录本身
Next-key lock：record+gap 锁定⼀个范围，包含记录本身
**相关知识点：**
\1. innodb对于⾏的查询使⽤next-key lock
\2. Next-locking keying为了解决Phantom Problem幻读问题
\3. 当查询的索引含有唯⼀属性时，将next-key lock降级为record key
\4. Gap锁设计的⽬的是为了阻⽌多个事务将记录插⼊到同⼀范围内，⽽这会导致幻读问题的产⽣
\5. 有两种⽅式显式关闭gap锁：（除了外键约束和唯⼀性检查外，其余情况仅使⽤record lock） A. 将事务隔离级
别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为15.7 什么是死锁？怎么解决？
死锁是指两个或多个事务在同⼀资源上相互占⽤，并请求锁定对⽅的资源，从⽽导致恶性循环的现象。
常⻅的解决死锁的⽅法
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以⼤⼤降低死锁机会。
2、在同⼀个事务中，尽可能做到⼀次锁定所需要的所有资源，减少死锁产⽣概率；
3、对于⾮常容易产⽣死锁的业务部分，可以尝试使⽤升级锁定颗粒度，通过表级锁定来减少死锁产⽣的概率；如果业务处理不好可以⽤分布式事务锁或者使⽤乐观锁5.8 数据库的乐观锁和悲观锁是什么？怎么实现的？
数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同⼀数据时不破坏事务的隔离
性和统⼀性以及数据库的统⼀性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采⽤的技术
⼿段。
**悲观锁**：假定会发⽣并发冲突，屏蔽⼀切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到
提交事务。实现⽅式：使⽤数据库中的锁机制
**乐观锁**：假设不会发⽣并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通
过version的⽅式来进⾏锁定。实现⽅式：乐⼀般会使⽤版本号机制或CAS算法实现。
**两种锁的使⽤场景**
从上⾯对两种锁的介绍，我们知道两种锁各有优缺点，不可认为⼀种好于另⼀种，像**乐观锁适⽤于写⽐较少的情况**
**下（多读场景）**，即冲突真的很少发⽣的时候，这样可以省去了锁的开销，加⼤了系统的整个吞吐量。
但如果是多写的情况，⼀般会经常产⽣冲突，这就会导致上层应⽤会不断的进⾏retry，这样反倒是降低了性能，所
以**⼀般多写的场景下⽤悲观锁就⽐较合适。**六 视图
6.1 为什么要使⽤视图？什么是视图？
为了提⾼复杂SQL语句的复⽤性和表操作的安全性，MySQL数据库管理系统提供了视图特性。所谓视图，本质上是
⼀种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含⼀系列带有名称的列和⾏数据。但是，视图并不
在数据库中以储存的数据值形式存在。⾏和列数据来⾃定义视图的查询所引⽤基本表，并且在具体引⽤视图时动态
⽣成。
视图使开发者只关⼼感兴趣的某些特定数据和所负责的特定任务，只能看到视图中所定义的数据，⽽不是视图所引
⽤表中的数据，从⽽提⾼了数据库中数据的安全性。6.2 视图有哪些特点？
视图的特点如下:
视图的列可以来⾃不同的表，是表的抽象和在逻辑意义上建⽴的新关系。
视图是由基本表(实表)产⽣的表(虚表)。
视图的建⽴和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来⾃多个基本表时，不允许添加和删除数据。视图的操作包括创建视图，查看视图，删除视图和修改视图。6.3 视图的使⽤场景有哪些？
视图根本⽤途：简化sql查询，提⾼开发效率。如果说还有另外⼀个⽤途那就是兼容⽼的表结构。
下⾯是视图的常⻅使⽤场景：
重⽤SQL语句；
简化复杂的SQL操作。在编写查询后，可以⽅便的重⽤它⽽不必知道它的基本查询细节；
使⽤表的组成部分⽽不是整个表；
保护数据。可以给⽤户授予表的特定部分的访问权限⽽不是整个表的访问权限；
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。6.4 视图的优点
\1. 查询简单化。视图能简化⽤户的操作
\2. 数据安全性。视图使⽤户能以多种⻆度看待同⼀数据，能够对机密数据提供安全保护
\3. 逻辑数据独⽴性。视图对重构数据库提供了⼀定程度的逻辑独⽴性6.5 视图的缺点
\1. 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由⼀个复杂的多表查询所定义，那
么，即使是视图的⼀个简单查询，数据库也把它变成⼀个复杂的结合体，需要花费⼀定的时间。
\2. 修改限制。当⽤户试图修改视图的某些⾏时，数据库必须把它转化为对基本表的某些⾏的修改。事实上，当从
视图中插⼊或者删除时，情况也是这样。对于简单视图来说，这是很⽅便的，但是，对于⽐较复杂的视图，可
能是不可修改的
这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY⼦句的视图。3.有诸如
AVG\SUM\MAX等聚合函数的视图。 4.使⽤DISTINCT关键字的视图。5.连接表的视图（其中有些例外）6.6 什么是游标？
游标是系统为⽤户开设的⼀个数据缓冲区，存放SQL语句的执⾏结果，每个游标区都有⼀个名字。⽤户可以通过游
标逐⼀获取记录并赋给主变量，交由主语⾔进⼀步处理。七 存储过程与函数
7.1 什么是存储过程？有哪些优缺点？
存储过程是⼀个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建⼀次，以后在该程序中就可以调
⽤多次。如果某次操作需要执⾏多次SQL，使⽤存储过程⽐单纯SQL语句执⾏要快。
**优点**
1）存储过程是预编译过的，执⾏效率⾼。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调⽤，减少⽹络通讯。
3）安全性⾼，执⾏存储过程需要有⼀定权限的⽤户。
4）存储过程可以重复使⽤，减少数据库开发⼈员的⼯作量。
**缺点**
1）调试麻烦，但是⽤ PL/SQL Developer 调试很⽅便！弥补这个缺点。
2）移植问题，数据库端代码当然是与数据库相关的。但是如果是做⼯程型项⽬，基本不存在移植问题。
3）重新编译问题，因为后端代码是运⾏前编译的，如果带有引⽤关系的对象发⽣改变时，受影响的存储过程、包
将需要重新编译（不过也可以设置成运⾏时刻⾃动编译）。
4）如果在⼀个程序系统中⼤量的使⽤存储过程，到程序交付使⽤的时候随着⽤户需求的增加会导致数据结构的变
化，接着就是系统的相关问题了，最后如果⽤户想维护该系统可以说是很难很难、⽽且代价是空前的，维护起来更
麻烦。7.2 什么是触发器？触发器的使⽤场景有哪些？
触发器是⽤户定义在关系表上的⼀类由事件驱动的特殊的存储过程。触发器是指⼀段代码，当触发某个事件时，⾃
动执⾏这些代码。
使⽤场景
可以通过数据库中的相关表实现级联更改。
实时监控某张表中的某个字段的更改⽽需要做出相应的处理。
例如可以⽣成某些业务的编号。
注意不要滥⽤，否则会造成数据库及应⽤程序的维护困难。
⼤家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和
MyISAM的区别。7.3 MySQL中都有哪些触发器？
在MySQL数据库中有如下六种触发器：
Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete⼋ 常⽤SQL语句
8.1 SQL语句主要分为哪⼏类
数据定义语⾔DDL（Data Ddefinition Language）CREATE，DROP，ALTER
主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。
数据查询语⾔DQL（Data Query Language）SELECT
这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。
数据操纵语⾔DML（Data Manipulation Language）INSERT，UPDATE，DELETE
主要为以上操作 即对数据进⾏操作的，对应上⾯所说的查询操作 DQL与DML共同构建了多数初级程序员常⽤的增
删改查操作。⽽查询是较为特殊的⼀种 被划分到DQL中。
数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK
主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。8.2 超键、候选键、主键、外键分别是什么？
超键：在关系中能唯⼀标识元组的属性集称为关系模式的超键。⼀个属性可以为作为⼀个超键，多个属性组合
在⼀起也可以作为⼀个超键。超键包含候选键和主键。
候选键：是最⼩超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯⼀和完整标识的数据列或属性的组合。⼀个数据列只能有⼀个主键，
且主键的取值不能缺失，即不能为空值（Null）。
外键：在⼀个表中存在的另⼀个表的主键称此表的外键。8.3 SQL 约束有哪⼏种？
NOT NULL: ⽤于控制字段的内容⼀定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，⼀个表允许有多个 Unique 约束。
PRIMARY KEY: 也是⽤于控件字段内容不能重复，但它在⼀个表只允许出现⼀个。
FOREIGN KEY: ⽤于预防破坏表之间连接的动作，也能防⽌⾮法数据插⼊外键列，因为它必须是它指向的那个
表中的值之⼀。
CHECK: ⽤于控制字段的值范围。8.4 六种关联查询
交叉连接（CROSS JOIN）
内连接（INNER JOIN）
外连接（LEFT JOIN/RIGHT JOIN）
联合查询（UNION与UNION ALL）
全连接（FULL JOIN）
交叉连接（CROSS JOIN）
内连接分为三类
等值连接：ON A.id=B.id
不等值连接：ON A.id > B.id
⾃连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid
外连接（LEFT JOIN/RIGHT JOIN）
左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的
⽤NULL填充，可以简写成LEFT JOIN
右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的
⽤NULL填充，可以简写成RIGHT JOIN
联合查询（UNION与UNION ALL）
就是把多个结果集集中在⼀起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录⾏
SELECT * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)#没有任何关联条件，
结果是笛卡尔积，结果集会很⼤，没有意义，很少使⽤内连接（INNER JOIN）SELECT * FROM A,B WHERE
A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集
合，INNER JOIN可以缩写为JOIN
如果使⽤UNION ALL，不会合并重复的记录⾏
效率 UNION ⾼于 UNION ALL
全连接（FULL JOIN）
MySQL不⽀持全连接
可以使⽤LEFT JOIN 和UNION和RIGHT JOIN联合使⽤
\1. 内连接结果：
select r. * ,s. * from r inner join s on r.c=s.c
\1. 左连接结果：
select r. * ,s. * from r left join s on r.c=s.c
\1. 右连接结果：
select r. * ,s. * from r right join s on r.c=s.cA
\1. 全表连接的结果（MySql不⽀持，Oracle⽀持）：
select r. * ,s. * from r full join s on r.c=s.c8.5 什么是⼦查询
\1. 条件：⼀条SQL语句的查询结果做为另⼀条查询语句的条件或查询结果
\2. 嵌套：多条SQL语句嵌套使⽤，内部的SQL查询语句称为⼦查询。8.6 ⼦查询的三种情况
\1. ⼦查询是单⾏单列的情况：结果集是⼀个值，⽗查询使⽤：=、 <、 > 等运算符
\1. ⼦查询是多⾏单列的情况：结果集类似于⼀个数组，⽗查询使⽤：in 运算符
\1. ⼦查询是多⾏多列的情况：结果集类似于⼀张虚拟表，不能⽤于where条件，⽤于select⼦句中做为⼦表
-- 查询⼯资最⾼的员⼯是谁？
select * from employee where salary=(select max(salary) from employee);
12
-- 查询⼯资最⾼的员⼯是谁？
select * from employee where salary=(select max(salary) from employee); 8.7 mysql中in 和 exists区别
mysql中的in语句是把外表和内表作hash 连接，⽽exists语句是对外表作loop循环，每次loop循环再对内表进⾏查
询。⼀直⼤家都认为exists⽐in语句的效率要⾼，这种说法其实是不准确的。这个是要区分环境的。
\1. 如果查询的两个表⼤⼩相当，那么⽤in和exists差别不⼤。
\2. 如果两个表中⼀个较⼩，⼀个是⼤表，则⼦查询表⼤的⽤exists，⼦查询表⼩的⽤in。
\3. not in 和not exists：如果查询语句使⽤了not in，那么内外表都进⾏全表扫描，没有⽤到索引；⽽not extsts的
⼦查询依然能⽤到表上的索引。所以⽆论那个表⼤，⽤not exists都⽐not in要快。8.8 varchar与char的区别
**char****的特点**
char表示定⻓字符串，⻓度是固定的；
如果插⼊数据的⻓度⼩于char的固定⻓度时，则⽤空格填充；
因为⻓度固定，所以存取速度要⽐varchar快很多，甚⾄能快50%，但正因为其⻓度固定，所以会占据多余的空
间，是空间换时间的做法；
对于char来说，最多能存放的字符个数为255，和编码⽆关
**varchar****的特点**
varchar表示可变⻓字符串，⻓度是可变的；
插⼊的数据是多⻓，就按照多⻓来存储；
varchar在存取⽅⾯与char相反，它存取慢，因为⻓度不固定，但正因如此，不占据多余的空间，是时间换空间
的做法；
对于varchar来说，最多能存放的字符个数为65532
总之，结合性能⻆度（char更快）和节省磁盘空间⻆度（varchar更⼩），具体情况还需具体来设计数据库才是妥当
的做法。
-- 1) 查询出2011年以后⼊职的员⼯信息
-- 2) 查询所有的部⻔信息，与上⾯的虚拟表中的信息⽐对，找出所有部⻔ID相等的员⼯。
select * from dept d, (select * from employee where join_date > '2011-1-1') e where
e.dept_id = d.id;
-- 使⽤表连接：
select d.*, e.* from dept d inner join employee e on d.id = e.dept_id where
e.join_date > '2011-1-1' 8.9 varchar(50)中50的涵义
最多存放50个字符，varchar(50)和(200)存储hello所占空间⼀样，但后者在排序时会消耗更多内存，因为order by
col采⽤fixed_length计算col⻓度(memory引擎也⼀样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符
数。8.10 int(20)中20的涵义
是指显示字符的⻓度。20表示最⼤显示宽度为20，但仍占4字节存储，存储范围不变；
不影响内部存储，只是影响带 zerofill 定义的 int 时，前⾯补多少个 0，易于报表展示8.11 mysql为什么这么设计
对⼤多数应⽤没有意义，只是规定⼀些⼯具⽤来显示字符的个数；int(1)和int(20)存储和计算均⼀样；8.12 mysql中int(10)和char(10)以及varchar(10)的区别
int(10)的10表示显示的数据的⻓度，不是存储数据的⼤⼩；chart(10)和varchar(10)的10表示存储数据的⼤⼩，
即表示存储多少个字符。
int(10) 10位的数据⻓度 9999999999，占32个字节，int型4位
char(10) 10位固定字符串，不⾜补空格 最多10个字符
varchar(10) 10位可变字符串，不⾜补空格 最多10个字符
char(10)表示存储定⻓的10个字符，不⾜10个就⽤空格补⻬，占⽤更多的存储空间
varchar(10)表示存储10个变⻓的字符，存储多少个就是多少个，空格也按⼀个字符存储，这⼀点是和char(10)
的空格不同的，char(10)的空格表示占位不算⼀个字符8.13 FLOAT和DOUBLE的区别是什么
FLOAT类型数据可以存储⾄多8位⼗进制数，并在内存中占4字节。
DOUBLE类型数据可以存储⾄多18位⼗进制数，并在内存中占8字节。8.15 UNION与UNION ALL的区别？
如果使⽤UNION ALL，不会合并重复的记录⾏
效率 UNION ⾼于 UNION ALL九 SQL优化
9.1 如何定位及优化SQL语句的性能问题？创建的索引有没有被使⽤到?或者说怎么才可以知道这条语句运⾏很慢的原因
对于低性能的SQL语句的定位，最重要也是最有效的⽅法就是使⽤执⾏计划，MySQL提供了explain命令来查看语
句的执⾏计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对⼀条SQL语句进⾏执⾏的过程中都会
做很多相关的优化，**对于查询语句，最重要的优化⽅式就是使⽤索引**。 ⽽**执⾏计划，就是显示数据库引擎对于****SQL**
**语句的执⾏的详细情况，其中包含了是否使⽤索引，使⽤什么索引，使⽤的索引的相关信息等**。
执⾏计划包含的信息 **id** 有⼀组数字组成。表示⼀个查询中各个⼦查询的执⾏顺序;**id**
**select_type**
**description**
1 SIMPLE
不包含任何⼦查询或union等查询
2 PRIMARY
包含⼦查询最外层查询就显示为 PRIMARY
3 SUBQUERY
在select或 where字句中包含的查询
4 DERIVED
from字句中包含的查询
5 UNION
出现在union后的查询语句中
6 UNION RESULT
从UNION中获取结果集，例如上⽂的第三个例⼦
id相同执⾏顺序由上⾄下。
id不同，id值越⼤优先级越⾼，越先被执⾏。
id为null时表示⼀个结果集，不需要使⽤它查询，常出现在包含union等查询语句中。
**select_type** 每个⼦查询的查询类型，⼀些常⻅的查询类型。
**table** 查询的数据表，当从衍⽣表中查数据时会显示 x 表示对应的执⾏计划id **partitions** 表分区、表创建的时候可
以指定通过那个列进⾏表分区。 举个例⼦：
**type**(⾮常重要，可以看到有没有⾛索引) 访问类型
ALL 扫描全表数据
index 遍历索引
range 索引范围查找
index_subquery 在⼦查询中使⽤ ref
unique_subquery 在⼦查询中使⽤ eq_ref
ref_or_null 对Null进⾏索引的优化的 ref
fulltext 使⽤全⽂索引
ref 使⽤⾮唯⼀索引查找数据
eq_ref 在join查询中使⽤PRIMARY KEYorUNIQUE NOT NULL索引关联。
**possible_keys** 可能使⽤的索引，注意不⼀定会使⽤。查询涉及到的字段上若存在索引，则该索引将被列出来。当
该列为 NULL时就要考虑当前的SQL是否需要优化了。
create table tmp (
id int unsigned not null AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;
**key** 显示MySQL在查询中实际使⽤的索引，若没有使⽤索引，显示为NULL。
**TIPS**:查询中若使⽤了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中
**key_length** 索引⻓度
**ref** 表示上述表的连接匹配条件，即哪些列或常量被⽤于查找索引列上的值
**rows** 返回估算的结果集数⽬，并不是⼀个准确的值。
**extra** 的信息⾮常丰富，常⻅的有：
\1. Using index 使⽤覆盖索引
\2. Using where 使⽤了⽤where⼦句来过滤结果集
\3. Using filesort 使⽤⽂件排序，使⽤⾮索引列进⾏排序时出现，⾮常消耗性能，尽量优化。
\4. Using temporary 使⽤了临时表 sql优化的⽬标可以参考阿⾥开发⼿册9.2 SQL的⽣命周期？
\1. 应⽤服务器与数据库服务器建⽴⼀个连接
\2. 数据库进程拿到请求sql
\3. 解析并⽣成执⾏计划，执⾏
\4. 读取数据到内存并进⾏逻辑处理
\5. 通过步骤⼀的连接，发送结果到客户端
\6. 关掉连接，释放资源
【推荐】SQL性能优化的⽬标：⾄少要达到 range 级别，要求是ref级别，如果可以是consts最好。
说明：
1） consts 单表中最多只有⼀个匹配⾏（主键或者唯⼀索引），在优化阶段即可读取到数据。
2） ref 指的是使⽤普通的索引（normal index）。
3） range 对索引进⾏范围检索。
反例：explain表的结果，type=index，索引物理⽂件全扫描，速度⾮常慢，这个index级别⽐较range还低，
与全表扫描是⼩巫⻅⼤巫。9.3 ⼤表数据查询，怎么优化
1. 优化shema、sql语句+索引；
2. 第⼆加缓存，memcached, redis；
3. 主从复制，读写分离；
4. 垂直拆分，根据你模块的耦合度，将⼀个⼤的系统分为多个⼩的系统，也就是分布式系统；
5. ⽔平切分，针对数据量⼤的表，这⼀步最麻烦，最能考验技术⽔平，要选择⼀个合理的sharding key, 为了有好
的查询效率，表结构也要改动，做⼀定的冗余，应⽤也要改，sql中尽量带sharding key，将数据定位到限定的
表上去查，⽽不是扫描全部的表；9.4 超⼤分⻚怎么处理？
超⼤的分⻚⼀般从两个⽅向上来解决.
数据库层⾯,这也是我们主要集中关注的(虽然收效没那么⼤),类似于 select * from table where age > 20
limit 1000000,10 这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢
弃,只取10条当然⽐较慢. 当时我们可以修改为 select * from table where id in (select id from table
where age > 20 limit 1000000,10) .这样虽然也load了⼀百万的数据,但是由于索引覆盖,要查询的所有字段都
在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以 select * from table where id > 1000000
limit 10 ,效率也是不错的,优化的可能性有许多种,但是核⼼思想都⼀样,就是减少load的数据.
从需求的⻆度减少这种请求…主要是不做类似的需求(直接跳转到⼏百万⻚之后的具体某⼀⻚.只允许逐⻚查看
或者按照给定的路线⾛,这样可预测,可缓存)以及防⽌ID泄漏且连续被⼈恶意攻击.
解决超⼤分⻚,其实主要是靠缓存,可预测性的提前查到内容,缓存⾄redis等k-V数据库中,直接返回即可.
在阿⾥巴巴《Java开发⼿册》中,对超⼤分⻚的解决办法是类似于上⾯提到的第⼀种.9.5 mysql 分⻚
LIMIT ⼦句可以被⽤于强制 SELECT 语句返回指定的记录数。LIMIT 接受⼀个或两个数字参数。参数必须是⼀个整
数常量。如果给定两个参数，第⼀个参数指定第⼀个返回记录⾏的偏移量，第⼆个参数指定返回记录⾏的最⼤数
⽬。初始记录⾏的偏移量是 0(⽽不是 1)
为了检索从某⼀个偏移量到记录集的结束所有的记录⾏，可以指定第⼆个参数为 -1：
【推荐】利⽤延迟关联或者⼦查询优化超多分⻚场景。
说明：MySQL并不是跳过offset⾏，⽽是取offset+N⾏，然后返回放弃前offset⾏，返回N⾏，那当offset特
别⼤的时候，效率就⾮常的低下，要么控制返回的总⻚数，要么对超过特定阈值的⻚数进⾏SQL改写。
正例：先快速定位需要获取的id段，然后再关联：
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where
a.id=b.id
1234567
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录⾏ 6-15
1
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录⾏ 96-last.
1如果只给定⼀个参数，它表示返回最⼤的记录⾏数⽬：
换句话说，LIMIT n 等价于 LIMIT 0,n。9.6 慢查询⽇志
⽤于记录执⾏时间超过某个临界值的SQL⽇志，⽤于快速定位慢查询，为我们的优化做参考。
开启慢查询⽇志
配置项： slow_query_log
可以使⽤ show variables like ‘slov_query_log’ 查看是否开启，如果状态值为 OFF ，可以使⽤ set GLOBAL
slow_query_log = on 来开启，它会在 datadir 下产⽣⼀个 xxx-slow.log 的⽂件。
设置临界时间
配置项： long_query_time
查看： show VARIABLES like 'long_query_time' ，单位秒
设置： set long_query_time=0.5
实操时应该从⻓时间设置到短的时间，即将最慢的SQL优化掉
查看⽇志，⼀旦SQL超过了我们设置的临界时间就会被记录到 xxx-slow.log 中9.7 关⼼过业务系统⾥⾯的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？
在业务系统中，除了使⽤主键进⾏的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，
会定期将业务中的慢查询反馈给我们。
慢查询的优化⾸先要搞明⽩慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太
⼤？
所以优化也是针对这三个⽅向来的，
⾸先分析语句，看看是否load了额外的数据，可能是查询了多余的⾏并且抛弃掉了，可能是加载了许多结果中
并不需要的列，对语句进⾏分析以及重写。
分析语句的执⾏计划，然后获得其使⽤索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录⾏索引。
如果对语句的优化已经⽆法进⾏，可以考虑表中的数据量是否太⼤，如果是的话可以进⾏横向或者纵向的分表。9.8 为什么要尽量设定⼀个主键？
主键是数据库确保数据⾏在整张表唯⼀性的保障，即使业务上本张表没有主键，也建议添加⼀个⾃增⻓的ID列作为
主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。9.9 主键使⽤⾃增ID还是UUID
推荐使⽤⾃增ID，不要使⽤UUID。
因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶⼦节点上存储了主键
索引以及全部的数据(按照顺序)，如果主键索引是⾃增ID，那么只需要不断向后排列即可，如果是UUID，由于到来
的ID与原来的⼤⼩不确定，会造成⾮常多的数据插⼊，数据移动，然后导致产⽣很多的内存碎⽚，进⽽造成插⼊性
能的下降。
总之，在数据量⼤⼀些的情况下，⽤⾃增主键性能会好⼀些。
关于主键是聚簇索引，如果没有主键，InnoDB会选择⼀个唯⼀键来作为聚簇索引，如果没有唯⼀键，会⽣成⼀个隐
式的主键。9.10 字段为什么要求定义为not null
null值会占⽤更多的字节，且会在程序中造成很多与预期不符的情况9.11 如果要存储⽤户的密码散列，应该使⽤什么字段进⾏存储？
密码散列，盐，⽤户身份证号等固定⻓度的字符串应该使⽤char⽽不是varchar来存储，这样可以节省空间且提⾼检
索效率。9.12 优化查询过程中的数据访问
访问数据太多导致查询性能下降
确定应⽤程序是否在检索⼤量超过需要的数据，可能是太多⾏或列
确认MySQL服务器是否在分析⼤量不必要的数据⾏
避免犯如下SQL语句错误
查询不需要的数据。解决办法：使⽤limit解决
多表关联返回全部列。解决办法：指定列名
总是返回全部列。解决办法：避免使⽤SELECT *
重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
是否在扫描额外的记录。解决办法：
使⽤explain进⾏分析，如果发现查询需要扫描⼤量的数据，但只返回少数的⾏，可以通过如下技巧去优化：
使⽤索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应⾏就可以返回结果。
改变数据库和表的结构，修改数据表范式重写SQL语句，让优化器可以以更优的⽅式执⾏查询。9.13 优化⻓难的查询语句
⼀个复杂查询还是多个简单查询
MySQL内部每秒能扫描内存中上百万⾏数据，相⽐之下，响应数据给客户端就要慢得多
使⽤尽可能⼩的查询是好的，但是有时将⼀个⼤的查询分解为多个⼩的查询是很有必要的。
切分查询
将⼀个⼤的查询分为多个⼩的相同的查询
⼀次性删除1000万的数据要⽐⼀次删除1万，暂停⼀会的⽅案更加损耗服务器开销。
分解关联查询，让缓存的效率更⾼。执⾏单个查询可以减少锁的竞争。在应⽤层做关联更容易对数据库进⾏拆分。
查询效率会有⼤幅提升。较少冗余记录的查询。9.14 优化特定类型的查询语句
count(*)会忽略所有的列，直接统计所有列数，不要使⽤count(列名)
MyISAM中，没有任何where条件的count(*)⾮常快。
当有where条件时，MyISAM的count统计不⼀定⽐其它引擎快。
可以使⽤explain查询近似值，⽤近似值替代count(*)
增加汇总表使⽤缓存9.15 优化关联查询
确定ON或者USING⼦句中是否有索引。
确保GROUP BY和ORDER BY只有⼀个表中的列，这样MySQL才有可能使⽤索引9.16 优化⼦查询
⽤关联查询替代
优化GROUP BY和DISTINCT
这两种查询据可以使⽤索引来优化，是最有效的优化⽅法
关联查询中，使⽤标识列分组的效率更⾼
如果不需要ORDER BY，进⾏GROUP BY时加ORDER BY NULL，MySQL不会再进⾏⽂件排序。
WITH ROLLUP超级聚合，可以挪到应⽤程序处理9.17 优化LIMIT分⻚
LIMIT偏移量⼤的时候，查询效率较低
可以记录上次查询的最⼤ID，下次查询时直接根据该ID来查询**9.18** **优化****UNION****查询**
UNION ALL的效率⾼于UNION9.19 优化WHERE⼦句
解题⽅法
对于此类考题，先说明如何定位低效SQL语句，然后根据SQL语句可能低效的原因做排查，先从索引着⼿，如果索
引没有问题，考虑以上⼏个⽅⾯，数据访问的问题，⻓难查询句的问题还是⼀些特定类型优化的问题，逐⼀回答。
SQL语句优化的⼀些⽅法？
1.对查询进⾏优化，应尽量避免全表扫描，⾸先应考虑在 where 及 order by 涉及的列上建⽴索引。
2.应尽量避免在 where ⼦句中对字段进⾏ null 值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描，如：
3.应尽量避免在 where ⼦句中使⽤!=或<>操作符，否则引擎将放弃使⽤索引⽽进⾏全表扫描。
4.应尽量避免在 where ⼦句中使⽤or 来连接条件，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描，如：
5.in 和 not in 也要慎⽤，否则会导致全表扫描，如：
6.下⾯的查询也将导致全表扫描：select id from t where name like ‘%李%’若要提⾼效率，可以考虑全⽂检
索。
7.如果在 where ⼦句中使⽤参数，也会导致全表扫描。因为SQL只有在运⾏时才会解析局部变量，但优化程序
不能将访问计划的选择推迟到运⾏时；它必须在编译时进⾏选择。然 ⽽，如果在编译时建⽴访问计划，变量的
值还是未知的，因⽽⽆法作为索引选择的输⼊项。如下⾯语句将进⾏全表扫描：
select id from t where num is null
-- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=
123
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
123
select id from t where num in(1,2,3)
-- 对于连续的数值，能⽤ between 就不要⽤ in 了：
select id from t where num between 1 and 3
1238.应尽量避免在 where ⼦句中对字段进⾏表达式操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。如：
9.应尽量避免在where⼦句中对字段进⾏函数操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。如：
10.不要在 where ⼦句中的“=”左边进⾏函数、算术运算或其他表达式运算，否则系统将可能⽆法正确使⽤索
引。⼗ 数据库优化
10.1 为什么要优化
系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应⽤程序的运⾏，数据库的中的数据会越来越多，处理时间会相应变慢
数据是存放在磁盘上的，读写速度⽆法和内存相⽐
优化原则：减少系统瓶颈，减少资源占⽤，增加系统的反应速度。10.2 数据库结构优化
⼀个好的数据库设计⽅案对于数据库的性能往往会起到事半功倍的效果。
需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多⽅⾯的内容。
**将字段很多的表分解成多个表**
select id from t where num=@num
-- 可以改为强制查询使⽤索引：
select id from t with(index(索引名)) where num=@num
123
select id from t where num/2=100
-- 应改为:
select id from t where num=100*2
123
select id from t where substring(name,1,3)=’abc’
-- name以abc开头的id应改为:
select id from t where name like ‘abc%’
123对于字段较多的表，如果有些字段的使⽤频率很低，可以将这些字段分离出来形成新表。
因为当⼀个表的数据量很⼤时，会由于使⽤频率低的字段的存在⽽变慢。
**增加中间表**
对于需要经常联合查询的表，可以建⽴中间表以提⾼查询效率。
通过建⽴中间表，将需要通过联合查询的数据插⼊到中间表中，然后将原来的联合查询改为对中间表的查询。
**增加冗余字段**
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理
的加⼊冗余字段可以提⾼查询速度。
表的规范化程度越⾼，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
**注意：**
**冗余字段的值在⼀个表中修改了，就要想办法在其他表中更新，否则就会导致数据不⼀致的问题。**10.3 MySQL数据库cpu飙升到500%的话他怎么处理？
当 cpu 飙升到 500%时，先⽤操作系统命令 top 命令观察是不是 mysqld 占⽤导致的，如果不是，找出占⽤⾼的进
程，并进⾏相关处理。
如果是 mysqld 造成的， show processlist，看看⾥⾯跑的 session 情况，是不是有消耗资源的 sql 在运⾏。找出
消耗⾼的 sql，看看执⾏计划是否准确， index 是否缺失，或者实在是数据量太⼤造成。
⼀般来说，肯定要 kill 掉这些线程(同时观察 cpu 使⽤率是否下降)，等进⾏相应的调整(⽐如说加索引、改 sql、改内
存参数)之后，再重新跑这些 SQL。
也有可能是每个 sql 消耗资源并不多，但是突然之间，有⼤量的 session 连进来导致 cpu 飙升，这种情况就需要跟
应⽤⼀起来分析为何连接数会激增，再做出相应的调整，⽐如说限制连接数等10.4 ⼤表怎么优化？
某个表有近千万数据，CRUD⽐较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有⽤到中间件么？他们的原理知道么？
当MySQL单表记录数过⼤时，数据库的CRUD性能会明显下降，⼀些常⻅的优化措施如下：
\1. **限定数据的范围：** 务必禁⽌不带任何限制数据范围条件的查询语句。⽐如：我们当⽤户在查询订单历史的时
候，我们可以控制在⼀个⽉的范围内。；
\2. **读****/****写分离：** 经典的数据库拆分⽅案，主库负责写，从库负责读；
\3. **缓存：** 使⽤MySQL的缓存，另外对重量级、更新少的数据可以考虑使⽤应⽤级别的缓存；
还有就是通过分库分表的⽅式进⾏优化，主要有垂直分表和⽔平分表1. **垂直分区：**
**根据数据库⾥⾯数据表的相关性进⾏拆分。** 例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将
⽤户表拆分成两个单独的表，甚⾄放到单独的库做分库。
**简单来说垂直拆分是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表。** 如下图所示，这样来说⼤家应该
就更容易理解了。
**垂直拆分的优点：** 可以使得⾏数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简
化表的结构，易于维护。
**垂直拆分的缺点：** 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应⽤层进⾏Join来解
决。此外，垂直分区会让事务变得更加复杂；
垂直分表
把主键和⼀些列放在⼀个表，然后把主键和另外的列放在另⼀个表中**适⽤场景**
1、如果⼀个表中某些列常⽤，另外⼀些列不常⽤
2、可以使数据⾏变⼩，⼀个数据⻚能存储更多数据，查询时减少I/O次数
**缺点**
有些分表的策略基于应⽤层的逻辑算法，⼀旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差
对于应⽤层来说，逻辑算法增加开发成本
管理冗余列，查询所有数据需要join操作
\2. **⽔平分区：**
**保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式**
**的⽬的。 ⽔平拆分可以⽀撑⾮常⼤的数据量。**
⽔平拆分是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以把⼀张的表的数据拆成多张表
来存放。举个例⼦：我们可以将⽤户信息表拆分成多个⽤户信息表，这样就可以避免单⼀表数据量过⼤对性能
造成影响。
⽔品拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是:分表仅仅是解决了单⼀表数据过⼤的问题，但由于表的
数据还是在同⼀台机器上，其实对于提升MySQL并发能⼒没有什么意义，所以 **⽔平拆分最好分库** 。
⽔平拆分能够 **⽀持⾮常⼤的数据量存储，应⽤端改造也少**，但 **分⽚事务难以解决** ，跨界点Join性能较差，逻
辑复杂。
《Java⼯程师修炼之道》的作者推荐 **尽量不要对数据进⾏分⽚，因为拆分会带来逻辑、部署、运维的各种复杂**
**度** ，⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚，尽量选择
客户端分⽚架构，这样可以减少⼀次和中间件的⽹络I/O。
⽔平分表：
表很⼤，分割后可以降低在查询时需要读的数据和索引的⻚数，同时也降低了索引的层数，提⾼查询次数**适⽤场景**
1、表中的数据本身就有独⽴性，例如表中分表记录各个地区的数据或者不同时期的数据，特别是有些数据
常⽤，有些不常⽤。
2、需要把数据存放在多个介质上。
**⽔平切分的缺点**
1、给应⽤增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作
2、在许多数据库应⽤中，这种复杂度会超过它带来的优点，查询时会增加读⼀个索引层的磁盘次数
**下⾯补充⼀下数据库分⽚的两种常⻅⽅案：**
**客户端代理： 分⽚逻辑在应⽤端，封装在****jar****包中，通过修改或者封装****JDBC****层来实现。** 当当⽹的
**Sharding-JDBC** 、阿⾥的TDDL是两种⽐较常⽤的实现。
**中间件代理： 在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。** 我们现在谈的
**Mycat** 、360的Atlas、⽹易的DDB等等都是这种架构的实现。
**分库分表后⾯临的问题**
**事务⽀持** 分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执⾏事务，将付出
⾼昂的性能代价； 如果由应⽤程序去协助控制，形成程序逻辑上的事务，⼜会造成编程⽅⾯的负担。
**跨库****join**
只要是进⾏切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发⽣。解决
这⼀问题的普遍做法是分两次查询实现。在第⼀次查询的结果集中找出关联数据的id,根据这些id发起第⼆次请
求得到关联数据。 分库分表⽅案产品
**跨节点的****count,order by,group by****以及聚合函数问题** 这些是⼀类问题，因为它们都需要基于全部数据集合进
⾏计算。多数的代理都不会⾃动处理合并⼯作。解决⽅案：与解决跨节点join问题的类似，分别在各个节点上
得到结果后在应⽤程序端进⾏合并。和join不同的是每个结点的查询可以并⾏执⾏，因此很多时候它的速度要
⽐单⼀⼤表快很多。但如果结果集很⼤，对应⽤程序内存的消耗是⼀个问题。**数据迁移，容量规划，扩容等问题** 来⾃淘宝综合业务平台团队，它利⽤对2的倍数取余具有向前兼容的特性
（如对4取余得1的数对2取余也是1）来分配数据，避免了⾏级别的数据迁移，但是依然需要进⾏表级别的迁
移，同时对扩容规模和分表数量都有限制。总得来说，这些⽅案都不是⼗分的理想，多多少少都存在⼀些缺
点，这也从⼀个侧⾯反映出了Sharding扩容的难度。
**ID****问题**
⼀旦数据库被切分到多个物理结点上，我们将不能再依赖数据库⾃身的主键⽣成机制。⼀⽅⾯，某个分区数据
库⾃⽣成的ID⽆法保证在全局上是唯⼀的；另⼀⽅⾯，应⽤程序在插⼊数据之前需要先获得ID,以便进⾏SQL路
由. ⼀些常⻅的主键⽣成策略
**UUID** 使⽤UUID作主键是最简单的⽅案，但是缺点也是⾮常明显的。由于UUID⾮常的⻓，除占⽤⼤量存储空间
外，最主要的问题是在索引上，在建⽴索引和基于索引进⾏查询时都存在性能问题。 **Twitter****的分布式⾃增****ID****算法**
**Snowflake** 在分布式系统中，需要⽣成全局UID的场合还是⽐较多的，twitter的snowflake解决了这种需求，实现也
还是很简单的，除去配置信息，核⼼代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。
跨分⽚的排序分⻚
般来讲，分⻚时需要按照指定字段进⾏排序。当排序字段就是分⽚字段的时候，我们通过分⽚规则可以⽐较容
易定位到指定的分⽚，⽽当排序字段⾮分⽚字段的时候，情况就会变得⽐较复杂了。为了最终结果的准确性，
我们需要在不同的分⽚节点中将数据进⾏排序并返回，并将不同分⽚返回的结果集进⾏汇总和再次排序10.5 MySQL的复制原理以及流程
主从复制：将主数据库中的DDL和DML操作通过⼆进制⽇志（BINLOG）传输到从数据库上，然后将这些⽇志重新
执⾏（重做）；从⽽使得从数据库的数据与主数据库保持⼀致。
**主从复制的作⽤**
\1. 主数据库出现问题，可以切换到从数据库。
\2. 可以进⾏数据库层⾯的读写分离。
\3. 可以在从数据库上进⾏⽇常备份。
**MySQL****主从复制解决的问题**
数据分布：随意开始或停⽌复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压⼒
⾼可⽤和故障切换：帮助应⽤程序避免单点失败
升级测试：可以⽤更⾼版本的MySQL作为从库
**MySQL****主从复制⼯作原理**
在主库上把数据更⾼记录到⼆进制⽇志
从库将主库的⽇志复制到⾃⼰的中继⽇志
从库读取中继⽇志的事件，将其重放到从库数据中
**基本原理流程，****3****个线程以及之间的关联**
**主**：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
**从**：io线程——在使⽤start slave 之后，负责从master上拉取 binlog 内容，放进⾃⼰的relay log中；
**从**：sql执⾏线程——执⾏relay log中的语句；
**复制过程**Binary log：主数据库的⼆进制⽇志
Relay log：从服务器的中继⽇志
第⼀步：master在每个事务更新数据完成之前，将该操作记录串⾏地写⼊到binlog⽂件中。
第⼆步：salve开启⼀个I/O Thread，该线程在master打开⼀个普通连接，主要⼯作是binlog dump process。如果
读取的进度已经跟上了master，就进⼊睡眠状态并等待master产⽣新的事件。I/O线程最终的⽬的是将这些事件写
⼊到中继⽇志中。
第三步：SQL Thread会读取中继⽇志，并顺序执⾏该⽇志中的SQL事件，从⽽与主数据库中的数据保持⼀致。10.6 读写分离有哪些解决⽅案？
读写分离是依赖于主从复制，⽽主从复制⼜是为读写分离服务的。因为主从复制要求 slave 不能写只能读（如果
对 slave 执⾏写操作，那么 show slave status 将会呈现 Slave_SQL_Running=NO ，此时你需要按照前⾯提到的
⼿动同步⼀下 slave ）。
**⽅案⼀**
使⽤mysql-proxy代理优点：直接实现读写分离和负载均衡，不⽤修改代码，master和slave⽤⼀样的帐号，mysql官⽅不建议实际⽣产中
使⽤
缺点：降低性能， 不⽀持事务
**⽅案⼆**
使⽤AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
如果采⽤了mybatis， 可以将读写分离放在ORM层，⽐如mybatis可以通过mybatis plugin拦截sql语句，所有的
insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。 plugin实现时可以
通过注解或者分析语句是读写⽅法来选定主从库。不过这样依然有⼀个问题， 也就是不⽀持事务， 所以我们还需
要重写⼀下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。
**⽅案三**
使⽤AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以⽀持事务.
缺点：类内部⽅法通过this.xx()⽅式相互调⽤时，aop不会进⾏拦截，需进⾏特殊处理。10.7 备份计划mysqldump以及xtranbackup的实现原理
**(1)****备份计划**
视库的⼤⼩来定，⼀般来说 100G 内的库，可以考虑使⽤ mysqldump 来做，因为 mysqldump更加轻巧灵活，备份
时间选在业务低峰期，可以每天进⾏都进⾏全量备份(mysqldump 备份出来的⽂件⽐较⼩，压缩之后更⼩)。
100G 以上的库，可以考虑⽤ xtranbackup 来做，备份速度明显要⽐ mysqldump 要快。⼀般是选择⼀周⼀个全
备，其余每天进⾏增量备份，备份时间为业务低峰期。
**(2)****备份恢复时间**
物理备份恢复快，逻辑备份恢复慢
这⾥跟机器，尤其是硬盘的速率有关系，以下列举⼏个仅供参考
20G的2分钟（mysqldump）
80G的30分钟(mysqldump)
111G的30分钟（mysqldump)
288G的3⼩时（xtra)
3T的4⼩时（xtra)
逻辑导⼊时间⼀般是备份时间的5倍以上**(3)****备份恢复失败如何处理**
⾸先在恢复之前就应该做⾜准备⼯作，避免恢复的时候出错。⽐如说备份之后的有效性检查、权限检查、空间检查
等。如果万⼀报错，再根据报错的提示来进⾏相应的调整。
**(4)mysqldump****和****xtrabackup****实现原理**
mysqldump
mysqldump 属于逻辑备份。加⼊–single-transaction 选项可以进⾏⼀致性备份。后台进程会先设置 session 的事务
隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ)，之后显式开启⼀个事务
(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */)，这样就保证了该事务⾥读到的数据都是事
务事务时候的快照。之后再把表的数据读取出来。如果加上–master-data=1 的话，在刚开始的时候还会加⼀个数据
库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后，再记录下数据库此时 binlog 的位置(showmaster
status)，⻢上解锁，再读取表的数据。等所有的数据都已经导完，就可以结束事务
Xtrabackup:
xtrabackup 属于物理备份，直接拷⻉表空间⽂件，同时不断扫描产⽣的 redo ⽇志并保存下来。最后完成 innodb
的备份后，会做⼀个 flush engine logs 的操作(⽼版本在有 bug，在5.6 上不做此操作会丢数据)，确保所有的 redo
log 都已经落盘(涉及到事务的两阶段提交
概念，因为 xtrabackup 并不拷⻉ binlog，所以必须保证所有的 redo log 都落盘，否则可能会丢最后⼀组提交事务
的数据)。这个时间点就是 innodb 完成备份的时间点，数据⽂件虽然不是⼀致性的，但是有这段时间的 redo 就可
以让数据⽂件达到⼀致性(恢复的时候做的事
情)。然后还需要 flush tables with read lock，把 myisam 等其他引擎的表给备份出来，备份完后解锁。这样就做到
了完美的热备。
10.8 数据表损坏的修复⽅式有哪些？
使⽤ myisamchk 来修复，具体步骤：
1）修复前将mysql服务停⽌。
2）打开命令⾏⽅式，然后进⼊到mysql的/bin⽬录。
3）执⾏myisamchk –recover 数据库所在路径/*.MYI
使⽤repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE
table_name 优化表 REPAIR TABLE ⽤于修复被破坏的表。 OPTIMIZE TABLE ⽤于回收闲置的数据库空间，当表上
的数据⾏被删除时，所占据的磁盘空间并没有⽴即被回收，使⽤了OPTIMIZE TABLE命令后这些空间将被回收，并
且对磁盘上的数据⾏进⾏重排（注意：是磁盘上，⽽⾮数据库）
