mysql题目和详细答案（2021数据库考试题及答案）

1.非聚簇索引一定会回表查询吗?

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子：假设我们在员工表的年龄上建立了索引,那么当进行的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

select age from employee where age < 20

2.mysql删除表几种方式？有什么区别？

1. delete : 仅删除表数据，支持where条件过滤，支持回滚。记录日志。因此比较慢。delete from links;
2. truncate: 仅删除所有数据，不支持where条件过滤，不支持回滚。不记录日志，效率高于delete。truncate table links;
3. drop:删除表数据同时删除表结构。将表所占的空间都释放掉。删除效率最高。drop table links;

3.mysql自增id不连续问题？

1. 唯一键冲突 假设执行 sql 的时候 user 表 id = 10，此时在内存中的自增 id 为11，此时发生唯一键冲突写库失败，则 user 表没有 id = 10 这条记录，之后 id 从11开始写入，因此 id 是不连续的。
2. 事务回滚 假设同时需要对 user、staff 表进行写库操作，执行 sql 的时候 user 表 id = 10，此时在内存中的自增 id 为11；staff 表 id = 20，此时内存中的自增 id 为21，一旦事务执行失败，事务回滚，写库失败，则 user 表没有 id = 10 这条记录，staff 表没有 id = 20 这条记录，user 表从11开始写入，staff 表从21开始写入，如此产生 id 不连续的现象。
3. 批量写库操作 对于批量插入数据的语句，mysql 有一个批量申请自增 id 的策略： 1. 语句执行过程中，第一次申请自增 id，会分配 1 个；
   2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个； 3. 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个； 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。 假设批量往 user 表中写入四条记录，则这四条记录将分为三次申请id， 第一次分配到 id = 1，第二次分配到 id = 2、3 ，第三次分配到 id = 4、5、6、7，当批量写入四条记录之后，id = 1、2、3、4将会入库，但是 id = 5、6、7就被废弃了，下一个 id 从8开始。

4.选择合适的存储引擎?

在选择存储引擎时，应根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

下面是常用存储引擎的适用环境。

1. myisam：5.1及之前版本默认存储引擎。如果应用是以读操作和插入操作为主，=只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存 储引擎是非常适合的。myisam 是在 web、数据仓储和其他应用环境下最常使用的存储引擎 之一。
2. innodb：5.5及之后默认存储引擎。用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、 删除操作，那么 innodb 存储引擎应该是比较合适的选择。innodb 存储引擎除了有效地降低 由于删除和更新导致的锁定，还可以确保事务的完整提交（commit）和回滚（rollback）， 对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，innodb 都是合适的选 择。
3. memory：将所有数据保存在 ram 中，在需要快速定位记录和其他类似数据的环境 下，可提供极快的访问。memory 的缺陷是对表的大小有限制，太大的表无法 cache 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。memory 表通常用于更新不太频繁的小表，用以快速得到访问结果。
4. merge：用于将一系列等同的 myisam 表以逻辑方式组合在一起，并作为一个对象引用它们。merge 表的优点在于可以突破对单个 myisam 表大小的限制，并且通过将不同 的表分布在多个磁盘上，可以有效地改善merge表的访问效率。这对于诸如数据仓储等vldb 环境十分适合。

5.选择合适的数据类型?

前提: 使用适合存储引擎。

选择原则,根据选定的存储引擎,确定如何选择合适的数据类型,下面的选择方法按存储引擎分类 :

1. myisam 数据存储引擎和数据列

myisam数据表，最好使用固定长度的数据列代替可变长度的数据列。

2. memory存储引擎和数据列memory数据表目前都使用固定长度的数据行存储，因此无论使用char或varchar列都没有关系。两者都是作为char类型处理的。
3. innodb 存储引擎和数据列建议使用 varchar类型对于innodb数据表，内部的行存储格式没有区分固定长度和可变长度列(所有数据行 都使用指向数据列值的头指针) ，因此在本质上，使用固定长度的char列不一定比使 用可变长度varchar列简单。因而， 主要的性能因 是数据行使用的存储总量。由于 char 平均占用的空间多于varchar，因此使用varchar来最小化需要处理的数据行的存储总量和年盘i/o是比较好的。

6.聊聊mysql字符集!

mysql服务器可以支持多种字符集（可以用show character set命令查看所有mysql支持的字符集），在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，mysql明显存在更大的灵活性。

mysql的字符集包括字符集（character）和校对规则（collation）两个概念。字符集是用来定义mysql存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系, mysql支持30多种字符集的70多种校对规则。

每个字符集至少对应一个校对规则。可以用show collation like ‘utf8%’;命令查看相关字符集的校对规则

7.如何选择字符集?

对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储，处理性能，以及日后系统的移植，推广都会有影响。mysql5.6目前支持几十种字符集，包括ucs2，utf16，utf16le,utf32，utf8和utf8mb4等unicode字符集。根据应用的需求，考虑以下几方面的因素。

1. 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择unicode字符集。对mysql来说，目前就是utf8，如果要存储emoji表情需使用utf8mb4。
2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是gbk文字，如果选择gb2312-80为数据库字符集，就很有可能出现某些文字无法正确导入的问题
3. 如果数据库只支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节长编码的中文字符集，比如gbk。因为，相对于utf8而言，gbk比较“小”，每个汉字只占2个字节，而utf8汉字编码需要3个字节，这样可以减少磁盘i/o，数据库cache以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择utf8更好，因为gbk,ucs2，utf16的西文字符编码都是2个字节，会造成很多不必要的开销。
4. 如果数据库需要做大量的字符运算，如比较，排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
5. 如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集，这样可以避免因字符集转换带来的性能开销和数据损失。

建议在能够完全满足应用的前提下,尽量使用小的字为集。因为更小的字为集意味着能够节省空间然 减少网络传输字节数,同时由于存储空间的较小间接的提高了系统的性能。

8.如何查询最后一行记录?

select * from links order by id desc limit 1;

9.sql注入问题？

sql注入攻击（sql injection），简称为注入攻击，sql注入，被广泛用于非法获取网站控制权。这是在应用程序的数据库层中发生的安全漏洞。在设计程序中，忽略了对输入字符串中包含的sql命令的检查，并且将数据库误认为是要运行的常规sql命令，这导致数据库受到攻击，从而可能导致盗窃，修改和删除数据，并进一步导致网站嵌入恶意代码，植入后门程序的危害等。

注射位置包括

1. 表单提交，主要是post请求，还有get请求。
2. url参数提交，主要是get请求参数。
3. cookie参数的提交。
4. http请求标头中的一些可修改值，例如referer，user_agent等。
5. 一些边缘输入点，例如.mp3文件的某些文件信息。

sql注入的危害

sql注入的危害不仅体现在数据库级别，而且还可能危及托管数据库的操作系统。如果将sql注入用于挂马，还可能用来传播恶意软件等，这些危害包括但不局限于：

1. 数据库信息泄漏：泄漏用户存储在数据库中的私人信息。作为数据存储中心，各种类型的私人信息通常存储在数据库中。sql注入攻击能导致这些隐私信息透明于攻击者。
2. 篡改网页：通过操作数据库来篡改特定网页。
3. 网站被挂马，传播恶意软件：修改数据库一些字段的值，嵌入网马链接，进行挂马攻击。
4. 数据库被恶意操作：数据库服务器受到攻击，数据库系统管理员帐户被篡改。
5. 服务器受远程控制，并安装了后门。经由数据库服务器提供的操作系统支持，让黑客得以修改或控制操作系统。
6. 破坏硬盘数据并使整个系统瘫痪。

如何解决sql注入

解决sql注入问题的关键是严格检查可能来自用户输入的所有数据，并使用最小特权原则进行数据库配置。常用的方案有：

1. 所有查询语句都使用数据库提供的参数化查询接口，并且参数化语句使用参数，而不是将用户输入变量嵌入sql语句中。几乎所有当前的数据库系统都提供参数化的sql语句执行接口。使用此接口可以有效地防止sql注入攻击。 string sql=”select * from user where username=? and password=?”; //使用?代替参数，预先设置好sql格式，就算在输入sql关键字也不会被sql识别
   preparedstatement pstat=conn.preparestatement(sql);
   pstat.setstring(1,username); //设置问号的值
   pstat.setstring(2,password);
   pstat.executequery();
2. 对进入数据库的特殊字符（’”<>&*;等）进行转义处理，或编码转换。
3. 确认每个数据的类型。例如，数字数据必须是数字，并且数据库中的存储字段必须与int类型相对应。
4. 应严格规定数据长度，以防在一定程度上正确执行较长的sql注入语句。
5. 网站每个数据层的编码是统一的。建议使用utf-8编码。上下层编码不一致可能会导致某些过滤模型被绕过。
6. 严格限制网站用户数据库的操作权限，并向该用户提供只能满足其工作要求的权限，从而最大程度地减少了注入攻击对数据库的危害。
7. 阻止网站显示sql错误消息，例如类型错误，字段不匹配等，以防止攻击者使用这些错误消息进行判断。
8. 在网站发布之前，建议使用一些专业的sql注入检测工具来及时检测和修补这些sql注入漏洞。

10.null和空串判断?

mysql 中的空值包含 null 和空字符串。当匹配 null 值条件时，使用 is null 和 is not null，当匹配空字符串时，使用“=”“<>”“!=”。

本文使用一张名为 t_goods 的数据表，该表用来记录商品信息，它的记录如下：

+----+---------------+-----------------+-------------+---------+---------+---------------------+
| id | t_category_id | t_category     | t_name     | t_price | t_stock | t_upper_time       |
+----+---------------+-----------------+-------------+---------+---------+---------------------+
|  1 |             1 | 女装/女士精品   | t恤         |   39.90 |   1000 | 2020-11-10 00:00:00 |
|  2 |             1 | 女装/女士精品   | 连衣裙     |   79.90 |   2500 | 2020-11-10 00:00:00 |
|  3 |             1 | 女装/女士精品   | 卫衣       |   79.90 |   1500 | 2020-11-10 00:00:00 |
|  4 |             1 | 女装/女士精品   | 牛仔裤     |   89.90 |   3500 | 2020-11-10 00:00:00 |
|  5 |             1 | 女装/女士精品   | 百褶裙     |   29.90 |    500 | 2020-11-10 00:00:00 |
|  6 |             1 | 女装/女士精品   | 呢绒外套   |  399.90 |   1200 | 2020-11-10 00:00:00 |
|  7 |             2 | 户外运动       | 自行车     |  399.90 |   1000 | 2020-11-10 00:00:00 |
|  8 |             2 | 户外运动       | 山地自行车 | 1399.90 |   2500 | 2020-11-10 00:00:00 |
|  9 |             2 | 户外运动       | 登山杖     |   59.90 |   1500 | 2020-11-10 00:00:00 |
| 10 |             2 | 户外运动       | 骑行装备   |  399.90 |   3500 | 2020-11-10 00:00:00 |
| 11 |             2 | 户外运动       | 户外运动外套|  799.90 |    500 | 2020-11-10 00:00:00 |
| 12 |             2 | 户外运动       | 滑板       |  499.90 |   1200 | 2020-11-10 00:00:00 |
+----+---------------+-----------------+-------------+---------+---------+---------------------+

向 t_goods 数据表中插入两条名称为空字符串，上架时间为 null 的数据记录。

mysql> insert into t_goods
    -> (t_category_id, t_category, t_name, t_price, t_stock, t_upper_time)
    -> values
    -> (1, '女装/女士精品', '', 399.90, 1200, null),
    -> (2, '户外运动', '', 499.90, 1200, null);
query ok, 2 rows affected (0.01 sec)
records: 2  duplicates: 0  warnings: 0

sql语句执行成功。

匹配 null 值

例如，查询 t_goods 数据表中上架时间为 null的 数据。

mysql> select id, t_category, t_name, t_price
    -> from t_goods
    -> where t_upper_time is null;
+----+-----------------+-------------+---------+
| id | t_category      | t_name      | t_price |
+----+-----------------+-------------+---------+
| 13 | 女装/女士精品   |              |  399.90 |
| 14 | 户外运动        |              |  499.90 |
+----+-----------------+-------------+---------+
2 rows in set (0.00 sec)

is not null 与 is null 相反，用于查询数据表中某个字段的值不是 null 的数据记录。

例如，查询 t_goods 数据表中上架时间不为 null 的数据。

mysql> select id, t_category, t_name, t_price
    -> from t_goods
    -> where t_upper_time is not null;
+----+----------------+-------------+---------+
| id | t_category     | t_name       | t_price |
+----+----------------+-------------+---------+
|  1 | 女装/女士精品  | t恤         |   39.90 |
|  2 | 女装/女士精品  | 连衣裙       |   79.90 |
|  3 | 女装/女士精品  | 卫衣         |   79.90 |
|  4 | 女装/女士精品  | 牛仔裤       |   89.90 |
|  5 | 女装/女士精品  | 百褶裙       |   29.90 |
|  6 | 女装/女士精品  | 呢绒外套     |  399.90 |
|  7 | 户外运动       | 自行车       |  399.90 |
|  8 | 户外运动       | 山地自行车   | 1399.90 |
|  9 | 户外运动       | 登山杖       |   59.90 |
| 10 | 户外运动       | 骑行装备     |  399.90 |
| 11 | 户外运动       | 户外运动外套 |  799.90 |
| 12 | 户外运动       | 滑板         |  499.90 |
+----+----------------+-------------+---------+
12 rows in set (0.00 sec)

匹配空字符串

例如，查询 t_goods 数据表中名称为空字符串的数据。

mysql> select id, t_category, t_name, t_price
    -> from t_goods
    -> where t_name = '';
+----+-----------------+-------------+---------+
| id | t_category      | t_name      | t_price |
+----+-----------------+-------------+---------+
| 13 | 女装/女士精品   |             |  399.90 |
| 14 | 户外运动        |             |  499.90 |
+----+-----------------+-------------+---------+
2 rows in set (0.00 sec)

使用“<>”或“!=”运算符能够查询数据表中某个字段的值不是空字符串的数据。例如，查询 t_goods 数据表中名称不是空字符串的数据。

mysql> select id, t_category, t_name, t_price
    -> from t_goods
    -> where t_name <> '';
+----+----------------+-------------+---------+
| id | t_category     | t_name      | t_price |
+----+----------------+-------------+---------+
|  1 | 女装/女士精品  | t恤         |   39.90 |
|  2 | 女装/女士精品  | 连衣裙      |   79.90 |
|  3 | 女装/女士精品  | 卫衣        |   79.90 |
|  4 | 女装/女士精品  | 牛仔裤      |   89.90 |
|  5 | 女装/女士精品  | 百褶裙      |   29.90 |
|  6 | 女装/女士精品  | 呢绒外套    |  399.90 |
|  7 | 户外运动       | 自行车      |  399.90 |
|  8 | 户外运动       | 山地自行车  | 1399.90 |
|  9 | 户外运动       | 登山杖      |   59.90 |
| 10 | 户外运动       | 骑行装备    |  399.90 |
| 11 | 户外运动       | 户外运动外套|  799.90 |
| 12 | 户外运动       | 滑板        |  499.90 |
+----+----------------+-------------+---------+
12 rows in set (0.00 sec)

11.innodb默认事务隔离级别?如何查看事务隔离级别?

• 可重复读取（repeatable-read）
• 查看事务隔离级别：

12.什么是回表查询？

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供mysql查询速度。

13.如何避免回表？

1. 尽量使用主键
2. 如果必须使用辅助索引，那么尽量避免使用*，直接列出所需列名使用索引覆盖。如果所需数据仅包含辅助索引列，那么它将不读取主键，因为辅助索引包含辅助索引列的值

14.索引覆盖是什么？

索引覆盖是一种避免回表查询的优化策略。具体的做法就是将要查询的数据作为索引列建立普通索引（可以是单列索引，也可以一个索引语句定义所有要查询的列，即联合索引），这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。

覆盖索引的定义与注意事项

如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询，也就提高了查询的效率。

要注意的是，不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值，而哈希索引、空间索引和全文索引等都不存储索引列值，索引mysql只能使用b-tree索引做覆盖索引。

另外，当发起一个被索引覆盖的查询（索引覆盖查询）时，在explain（执行计划）的extra列可以看到【using index】的信息。

覆盖索引的优点

1.索引条目通常远小于数据行的大小，因为覆盖索引只需要读取索引，极大地减少了数据的访问量。

2.索引是按照列值顺序存储的，对于io密集的范围查找会比随机从磁盘读取每一行数据的io小很多。

3.一些存储引擎比如myisam在内存中只缓存索引，数据则依赖操作系统来缓存，因此要访问数据的话需要一次系统调用，使用覆盖索引则避免了这一点。

4.由于innodb的聚簇索引，覆盖索引对innodb引擎下的数据库表特别有用。因为innodb的二级索引在叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就避免了对主键索引的二次查询。

15.视图的优缺点？

优点

1.简化了操作：此时我们完全不用关心视图是怎么处理数据的，我们只需要知道如何使用这个结果集即可，视图相当于一个中间层。

2.更加安全：比如我们可以让用户有权去访问某个视图，但是不能访问原表，这样就可以起到保护原表中某些数据的作用。

3.管理权限是无法细致到某一个列的，通过视图，则很容易实现。

4.降低耦合：假如我们以后要修改原表的结构，那么我们可以通过修改视图的定义即可，而不用修改应用程序，对访问者是不会造成影响的，一般来说，这样代价会更小。

缺点

1.性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的。

2.表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

16.主键和唯一索引区别？

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

1. 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
2. 唯一性索引列允许空值，而主键列不允许为空值。
3. 主键列在创建时，已经默认为非空值 + 唯一索引了。
4. 主键可以被其他表引用为外键，而唯一索引不能。
5. 一个表最多只能创建一个主键，但可以创建多个唯一索引。
6. 主键和唯一索引都可以有多列。
7. 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
8. 在 rbo 模式下，主键的执行计划优先级要高于唯一索引。两者可以提高查询的速度。

索引是一种特殊的文件（innodb数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。

总体来说：主键相当于一本书的页码，索引相当于书的目录。

其实主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中，主键一般都要建，主要是用来避免一张表中有相同的记录，索引一般可以不建，但如果需要对该表进行查询操作，则最好建，这样可以加快检索的速度。

17.如何随机获取一条记录？

select * from posts order by rand() limit 1;

18.查看当前表有哪些索引？

show index from posts;

19.什么情况下索引不生效？

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

20.聊聊mvvc？

本质

多版本并发控制(multiversion concurrency control， mcc 或 mvcc)，是数据库管理系统常用的一种并发控制，也用于程序设计语言实现事务内存。

乐观并发控制和悲观并发控制都是通过延迟或者终止相应的事务来解决事务之间的竞争条件来保证事务的可串行化；这两种并发控制机制确实能够从根本上解决并发事务的可串行化的问题，但是其实都是在解决写冲突的问题，两者区别在于对写冲突的乐观程度不同(悲观锁也能解决读写冲突问题，但是性能就一般了)。而在实际使用过程中，数据库读请求是写请求的很多倍，我们如果能解决读写并发的问题的话，就能更大地提高数据库的读性能，而这就是多版本并发控制所能做到的事情。

与悲观并发控制和乐观并发控制不同的是，mvcc是为了解决读写锁造成的多个、长时间的读操作饿死写操作问题，也就是解决读写冲突的问题。mvcc 可以与前两者中的任意一种机制结合使用，以提高数据库的读性能。

数据库的悲观锁基于提升并发性能的考虑，一般都同时实现了多版本并发控制。不仅是mysql，包括oracle、postgresql等其他数据库系统也都实现了mvcc，但各自的实现机制不尽相同，因为mvcc没有一个统一的实现标准。

总的来说，mvcc的出现就是数据库不满用悲观锁去解决读-写冲突问题，因性能不高而提出的解决方案。

实现方式

mvcc的实现，是通过保存数据在某个时间点的快照来实现的。每个事务读到的数据项都是一个历史快照，被称为快照读，不同于当前读的是快照读读到的数据可能不是最新的，但是快照隔离能使得在整个事务看到的数据都是它启动时的数据状态。而写操作不覆盖已有数据项，而是创建一个新的版本，直至所在事务提交时才变为可见。

当前读和快照读

什么是mysql innodb下的当前读和快照读?

• 当前读像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
• 快照读像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是未提交读和串行化级别，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁。

优缺点

mvcc 使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

适用场景

1. 解决读-写冲突的无锁并发控制。
2. 与上面两者结合，提升它们的读性能。
3. 可以解决脏读，幻读，不可重复读等事务问题，更新丢失问题除外。

21.sql语句的执行流程!

1. client和server建立连接，client发送sql至server（对应连接器这一过程）
2. server如果在查询缓存中发现了该sql，则直接使用查询缓存的结果返回给client，如果查询缓存中没有，则进入下面的步骤（对应查询缓存这一过程，8.0这一过程已经不存在了，8.0完全抛弃了这个功能）
3. server对sql进行语法分析，识别出sql里面的字符串是否符合标准，比如select关键字不能写错（对应分析器这一过程）
4. server对sql进行语义分析，识别出sql里面的字符串的具体意思是什么，比如t1是表名，c1是列名（对应分析器这一过程。3、4步其实解析的过程，这个解析的过程是分析器的工作不是优化器的工作）
5. server确定sql的执行路径，比如走索引还是全表，多表连接时哪张表先走哪张表后走，当你的where条件的可读性和效率有冲突时mysql内部还会自动进行优化，也就是大家理解的重写where子句（对应优化器这一过程）
6. server对执行sql的用户进行权限检查，比如对表是否有权限执行(对应执行器这一过程)
7. server执行该sql语句，发送结果给client(对应执行器这一过程)

连接器–>查询缓存–>分析器–>优化器–>执行器

如果表 t1 中没有字段 c1，而执行select * from t1 where c1=1会报错不存在c1这个列，这个过程对应上面第4个过程，对应分析器这一过程

如果用户对t1表没有权限，而执行select * from t1 where c1=1会报错对表t1没有权限，这个过程对应上面第6个过程，对应执行器这一过程

22.聊聊select 语句执行计划!

我们都知道用explain xxx分析sql语句的性能，但是具体从explain的结果怎么分析性能以及每个字段的含义你清楚吗？这里我做下总结记录，也是供自己以后参考。

首先需要注意：mysql 5.6.3以前只能explain select; mysql5.6.3以后就可以explain select,update,delete

---

explain结果示例：

mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | simple      | staff | all  | null          | null | null    | null |    2 | null  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

---

先上一个官方文档表格的中文版：

column	含义
id	查询序号
select_type	查询类型
table	表名
partitions	匹配的分区
type	join类型
prossible_keys	可能会选择的索引
key	实际选择的索引
key_len	索引的长度
ref	与索引作比较的列
rows	要检索的行数(估算值)
filtered	查询条件过滤的行数的百分比
extra	额外信息

这是explain结果的各个字段，分别解释下含义：

1. id

sql查询中的序列号。

id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

2. select_type

查询的类型，可以是下表的任何一种类型：

select_type	类型说明
simple	简单select(不使用union或子查询)
primary	最外层的select
union	union中第二个或之后的select语句
dependent union	union中第二个或之后的select语句取决于外面的查询
union result	union的结果
subquery	子查询中的第一个select
dependent subquery	子查询中的第一个select, 取决于外面的查询
derived	衍生表(from子句中的子查询)
materialized	物化子查询
uncacheable subquery	结果集无法缓存的子查询，必须重新评估外部查询的每一行
uncacheable union	union中第二个或之后的select，属于无法缓存的子查询

dependent 意味着使用了关联子查询。

3. table

查询的表名。不一定是实际存在的表名。 可以为如下的值：

• <unionm,n>: 引用id为m和n union后的结果。
• <derivedn>: 引用id为n的结果派生出的表。派生表可以是一个结果集，例如派生自from中子查询的结果。
• <subqueryn>: 引用id为n的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。

4. type（重要）

这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：

system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，all

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

• 1、system

表中只有一行数据或者是空表，这是const类型的一个特例。且只能用于myisam和memory表。如果是innodb引擎表，type列在这个情况通常都是all或者index

• 2、const

最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描

• 3、eq_ref

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非null索引的所有字段都被用作join联接时会使用此类型。

eq_ref可用于使用’=’操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。

相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。eq_ref只能找到一行，而ref能找到多行。

• 4、ref

对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型（也就是说，此联接能够匹配多行记录）。

ref可用于使用’=’或'<=>’操作符作比较的索引列。

• 5、 fulltext

使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

• 6、ref_or_null

跟ref类型类似，只是增加了null值的比较。实际用的不多。

eg.
select * from ref_table
where key_column=expr or key_column is null;

• 7、index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range

• 8、unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。 该类型替换了下面形式的in子查询的ref： value in (select primary_key from single_table where some_expr)

• 9、index_subquery

该联接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。

• 10、range

索引范围查询，常见于使用 =, <>, >, >=, <, <=, is null, <=>, between, in()或者like等运算符的查询中。

select * from tbl_name
  where key_column between 10 and 20;

select * from tbl_name
  where key_column in (10,20,30);

• 11、index

索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况： 一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示using index，反之，如果在索引上进行全表扫描，没有using index的提示。

# 此表见有一个name列索引。
# 因为查询的列name上建有索引，所以如果这样type走的是index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | simple      | testa | index | null          | idx_name | 33      | null |    2 | using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因为查询的列cusno没有建索引，或者查询的列包含没有索引的列，这样查询就会走all扫描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | simple      | testa | all  | null          | null | null    | null |    2 | null  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# 包含有未见索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | simple      | testa | all  | null          | null | null    | null |    2 | null  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

• 12、all

全表扫描，性能最差。

5. partitions

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

6. possible_keys

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

7. key

查询真正使用到的索引。 select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

8. key_len

查询用到的索引长度（字节数）。 如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

9. ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

10. rows（重要）

rows 也是一个重要的字段。这是mysql估算的需要扫描的行数（不是精确值）。 这个值非常直观显示 sql 的效率好坏, 原则上 rows 越少越好.

11. filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。这个字段不重要

12. extra（重要）

explain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:

• distinct：在select部分使用了distinc关键字
• using filesort：当 extra 中有 using filesort 时, 表示 mysql 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 using filesort, 都建议优化去掉, 因为这样的查询 cpu 资源消耗大.

# 例如下面的例子:

mysql> explain select * from order_info order by product_name g
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: order_info
   partitions: null
         type: index
possible_keys: null
          key: user_product_detail_index
      key_len: 253
          ref: null
         rows: 9
     filtered: 100.00
        extra: using index; using filesort
1 row in set, 1 warning (0.00 sec)
我们的索引是

key `user_product_detail_index` (`user_id`, `product_name`, `productor`)
但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 using filesort.
如果我们将排序依据改为 order by user_id, product_name, 那么就不会出现 using filesort 了. 例如:

mysql> explain select * from order_info order by user_id, product_name g
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: order_info
   partitions: null
         type: index
possible_keys: null
          key: user_product_detail_index
      key_len: 253
          ref: null
         rows: 9
     filtered: 100.00
        extra: using index
1 row in set, 1 warning (0.00 sec)

• using index“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
• using temporary查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

23.表最多可创建多少列，多少索引？

前言: 网上关于一张mysql表最多可以创建多少个索引?基本就是两种答案,一种是不限制,一种是16列, 我认为16的由来应该网上传着传着给扭曲, 因为mysql的单个索引最多能包括16列,但是这个是一个索引包含几列的答案而不是一张可以建多少个索引.真的是误导人啊!

因为mysql的一些限制跟具体存储引擎有关,为此以下只列出我们最常用的innodb和myisam,其他存储引擎的具体限制可以去官网找.

• innodb: 最多创建1017列, 最多64个二级索引, 单个索引最多包含16列, 索引最大长度767字节(其实行格式为redundant,compact最高为767字节,但行格式为dynamic,compressed最高可达为3072字节), 行大小最大65536字节
• mysiam: 最多4096列, 最多64个二级索引, 单个索引最多包含16列, 索引最大长度1000字节, 行大小最大65536字节

mysql官方文档地址

• innodb限制的官方文档: https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html
• myisam限制的官方文档: https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

这个问题要根据mysql具体版本，具体引擎来回答.

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

主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的id列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全.

25.字段为什么要求建议为not null?

mysql官网这样介绍:

null columns require additional space in the rowto record whether their values are null. for myisam tables, each null columntakes one bit extra, rounded up to the nearest byte.

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况.

• 很多表都包含可为null（空值）的列， 即使应用程序并不需要保存null 也是如此， 这是因为可为null 是列的默认属性。通常情况下最好指定列为not null, 除非真的需要存储null 值。
• 如果查询中包含可为null 的列， 对mysql来说更难优化， 因为可为null 的列 使得索引、 索引统计和值比较都更复杂。可为 n ull的列会使用更多的存储空间， 在mysql里也需要特殊处理。当可为null的列被索引时， 每个索引记录需要一个额 外的字节， 在myisam 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
• 通常把可为null 的列改为not null 带来的性能提升比较小， 所以（调优时）没有 必要首先在现有schema中查找井修改掉这种情况，除非确定这会导致问题。但是， 如果计划在列上建索引， 就应该尽扯避免设计成可为 null 的列。
• 当然也有例外， 例如值得一提的是， lnnodb 使用单独的位 (bit) 存储null 值， 所以对于稀疏数据 有很好的空间效率。但这一点不适用千myisam。

26.varchar(10)和int(10)代表什么含义

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示。

27.视图是什么？对比普通表优势?

视图(view)是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并 不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时 动态生成的。

视图相对于普通的表的优势主要包括以下几项。

• 简单:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件， 对用户来说已经是过滤好的复合条件的结果集。
• 安全:使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能 限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立:一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加 列对视图没有影响;源表修改列名，则可以通过修改视图来解决，不会造成对访问 者的影响。

28.coun()在不同引擎的实现方式?

count()的实现方式:在不同的mysql引擎中，count()实现的方式不同。· myisam引擎中把一个表的总行数直接存在了磁盘上，执行count() 的时候直接返回这个数，效率很高;(不支持事务)· 而innodb引擎，执行count()的时候，需要把数据一行一行的从引擎读出来，然后累计计数;（因为mvcc的实现，应该返回多少行是不确定的(自己能读到自己事务的未提交记录，而不能读到别人事务的未提交记录)）

但是innodb在执行count()是做了优化的。innodb是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count()这个操作，遍历哪个索引树得到的结果逻辑上是一样的，所以mysql优化器会选择最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

1. count(* )返回的结果集，一行行的判断，如果count函数的参数不是null，累计值就加1，最后返回累加值。server层要什么，innodb就返回什么。(就是sql语句要什么字段，innodb就返回什么字段)
2. count(字段) 则表示返回满足条件的数据行里面，字段值不为null的总个数。1.如果字段定义为not null的话，一行行的从记录里面读出这个字段，判断不能为null，按行累加;
   2.如果字段定义允许为null，执行的时候判断到有可能是null，还要把值取出来再判断一下，不是null才累加。
3. count(主键id): innodb引擎会遍历整张表，把每一行的id取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
4. count(1): innodb引擎会遍历整张表，但不取值。server层对于返回的每一行，放一个数字”1″进去，判断是不可能为空的，按行累加。

结果: count(*)≈count(1)>count(主键id)>count(字段)。

29.mysql大表优化方案

当mysql单表记录数过⼤时，数据库的crud性能会明显下降，⼀些常⻅的优化措施如下：

限定数据的范围

务必禁⽌不带任何限制数据范围条件的查询语句。⽐如：我们当⽤户在查询订单历史的时候，我们可以 控制在⼀个⽉的范围内；

读写分离

经典的数据库拆分⽅案，主库负责写，从库负责读；

垂直分区

根据数据库⾥⾯数据表的相关性进⾏拆分。 例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将⽤户表拆分成两个单独的表，甚⾄放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把⼀张列⽐较多的表拆分为多张表。 如下图所示，这样来说⼤家应该就更容易理解了。

垂直拆分的优点： 可以使得列数据变⼩，在查询时减少读取的block数，减少i/o次数。此外， 垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起join操作，可以通过在应⽤层进⾏join来解决。此外，垂直分区会让事务变得更加复杂；

⽔平分区

保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式的⽬的。⽔平拆分可以⽀撑⾮常⼤的数据量。

⽔平拆分是指数据表⾏的拆分，表的⾏数超过200万⾏时，就会变慢，这时可以把⼀张的表的数据拆成 多张表来存放。举个例⼦：我们可以将⽤户信息表拆分成多个⽤户信息表，这样就可以避免单⼀表数据 量过⼤对性能造成影响。

⽔平拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是：分表仅仅是解决了单⼀表数据过⼤的问题，但 由于表的数据还是在同⼀台机器上，其实对于提升mysql并发能⼒没有什么意义，所以 ⽔平拆分最好分库 。

⽔平拆分能够 ⽀持⾮常⼤的数据量存储，应⽤端改造也少，但 分⽚事务难以解决 ，跨节点join性能较差，逻辑复杂。《java⼯程师修炼之道》的作者推荐 **尽量不要对数据进⾏分⽚，因为拆分会带来逻辑、部署、运维的各种复杂度 ，⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚，尽量选择客户端分⽚架构，这样可以减少⼀次和中间件的⽹络i/o。

下⾯补充⼀下数据库分⽚的两种常⻅⽅案：

• 客户端代理： 分⽚逻辑在应⽤端，封装在jar包中，通过修改或者封装jdbc层来实现。当当⽹的sharding-jdbc、阿⾥的tddl是两种⽐较常⽤的实现。
• 中间件代理：应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。我们现在谈的 mycat 、360的atlas、⽹易的ddb等等都是这种架构的实现。

30.解释⼀下什么是池化设计思想.什么是数据库连接池?为什么需要数据库连接池?

池化设计应该不是⼀个新名词。我们常⻅的如java线程池、jdbc连接池、redis连接池等就是这类设计 的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好⽐你去⻝堂打饭，打饭的⼤妈会先把饭盛好⼏份放那⾥，你来了就直 接拿着饭盒加菜即可，不⽤再临时⼜盛饭⼜打菜，效率就⾼了。除了初始化资源，池化设计还包括如下 这些特征：池⼦的初始值、池⼦的活跃值、池⼦的最⼤值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。

数据库连接本质就是⼀个 socket 的连接。数据库服务端还要维护⼀些缓存和⽤户权限信息之类的 所以占⽤了⼀些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据 库的请求时可以重⽤这些连接。为每个⽤户打开和维护数据库连接，尤其是对动态数据库驱动的⽹站应

⽤程序的请求，既昂贵⼜浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使⽤它，因此 不必建⽴新的连接。如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。 连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

31.分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要⼀个全局唯⼀的 id来⽀持。

⽣成全局 id 有下⾯这⼏种⽅式：

uuid：不适合作为主键，因为太⻓了，并且⽆序不可读，查询效率低。⽐较适合⽤于⽣成唯⼀的 名字的标示⽐如⽂件的名字。

数据库⾃增 id : 两台数据库分别设置不同步⻓，⽣成不重复id的策略来实现⾼可⽤。这种⽅式⽣成的 id 有序，但是需要独⽴部署数据库实例，成本⾼，还会有性能瓶颈。

利⽤ redis ⽣成 id : 性能⽐较好，灵活⽅便，不依赖于数据库。但是，引⼊了新的组件造成系统更加复杂，可⽤性降低，编码更加复杂，增加了系统成本。

twitter**的snowflake算法** ：github 地址：
https://github.com/twitter-archive/snowflake。

美团的leaf分布式**id⽣成系统** ：leaf 是美团开源的分布式id⽣成器，能保证全局唯⼀性、趋势递增、单调递增、信息安全，⾥⾯也提到了⼏种分布式⽅案的对⽐，但也需要依赖关系数据库、zookeeper等中间件。感觉还不错。美团技术团队的⼀篇⽂章：
https://tech.meituan.com/2017/04/21/mt-leaf.html 。

32.⼀条sql语句执⾏得很慢的原因有哪些？

1. 大多数情况下很正常，偶尔很慢，则有如下原因(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。(2)、执行的时候，遇到锁，如表锁、行锁。
2. 这条 sql 语句一直执行的很慢，则有如下原因。(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。(2)、数据库选错了索引。

33.聊聊存储过程

一组为了完成特定功能的 sql 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过 程是数据库中的一个重要对象。

存储过程优化思路：

1. 尽量利用一些sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. 中间结果存放于临时表，加索引。
3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
4. 事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
5. 使用try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。

34.mysql里记录货币用什么字段类型好?

numeric和decimal类型被mysql实现为同样的类型，这在sql92标准允许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数据。

当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。例如：salary decimal(9,2)在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。因此，在这种情况下，能被存储在salary列中的值的范围是从-9999999.99到9999999.99。在ansi/iso sql92中，句法decimal(p)等价于decimal(p,0)。同样，句法decimal等价于decimal(p,0)，这里实现被允许决定值p。

mysql当前不支持decimal/numeric数据类型的这些变种形式的任一种。这一般说来不是一个严重的问题，因为这些类型的主要益处得自于明显地控制精度和规模的能力。decimal和numeric值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。一个字符用于值的每一位、小数点(如果scale>0)和“-”符号(对于负值)。如果scale是0，decimal和numeric值不包含小数点或小数部分。decimal和numeric值得最大的范围与double一样，但是对于一个给定的decimal或numeric列，实际的范围可由制由给定列的precision或scale限制。当这样的列赋给了小数点后面的位超过指定scale所允许的位的值，该值根据scale四舍五入。

当一个decimal或numeric列被赋给了其大小超过指定(或缺省的）precision和scale隐含的范围的值，mysql存储表示那个范围的相应的端点值。

35.char 和varchar 的区别？

1. 区别一，定长和变长char 表示定长，长度固定，varchar表示变长，即长度可变。char如果插入的长度小于定义长度时，则用空格填充；varchar小于定义长度时，还是按实际长度存储，插入多长就存多长。因为其长度固定，char的存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。varchar则刚好相反，以时间换空间。
2. 区别之二，存储的容量不同对 char 来说，最多能存放的字符个数 255，和编码无关。而 varchar 呢，最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。

36.havng 子句 和 where的异同点?

一、相同点：

二者的相同点是在功能上来说的，having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。

二、区别如下：

1. where是先分组再筛选记录，where在聚合前先筛选记录．也就是说作用在group by 子句和having子句前；而 having子句在聚合后对组记录进行筛选。
2. 在 sql 中增加 having 子句原因是，where 关键字无法与合计函数一起使用。而having子句中可以。having 子句是聚组函数唯一出现的地方。
3. 在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行。而where子句在查询过程中执行优先级高于聚合语句。
4. having 子句中的每一个元素必须出现在select列表中。

37.drop,delete与truncate的区别？

drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

1. delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。truncate table 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
2. 表和索引所占空间。当表被truncate后，这个表和索引所占用的空间会恢复到初始大小，而delete操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
3. 应用范围。truncate只能对table；delete可以是table和view
4. truncate和delete只删除数据，而drop则删除整个表（结构和数据）。
5. truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。
6. delete语句为dml（data maintain language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 trigger,执行的时候将被触发。truncate、drop是dll（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚
7. 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
8. truncate table 表名 速度快,而且效率高,因为: truncate table 在功能上与不带 where 子句的 delete语句相同：二者均删除表中的全部行。但 truncate table 比 delete速度快，且使用的系统和事务日志资源少。delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table
9. 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
10. truncate table 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 delete。如果要删除表定义及其数据，请使用 drop table 语句。
11. 对于由 foreign key 约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete语句。由于 truncate table 不记录在日志中，所以它不能激活触发器。

38.myisam和innodb区别

myisam是mysql的默认数据库引擎（5.5版之前）。虽然性能极佳，⽽且提供了⼤量的特性，包括全⽂索引、压缩、空间函数等，但myisam不⽀持事务和⾏级锁，⽽且最⼤的缺陷就是崩溃后⽆法安全恢复。不过，5.5版本之后，mysql引⼊了innodb（事务性数据库引擎），mysql 5.5版本后默认的存储引擎为innodb。⼤多数时候我们使⽤的都是 innodb 存储引擎，但是在某些情况下使⽤ myisam 也是合适的⽐如读密集的情况下。（如果你不介意 myisam 崩溃恢复问题的话）。

两者的对⽐

是否⽀持⾏级锁

• myisam 只有表级锁(table-level locking)，⽽innodb ⽀持⾏级锁(rowlevel locking)和表级锁,默认为⾏级锁。

是否⽀持事务和崩溃后的安全恢复

• myisam 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐innodb类型更快，但是不提供事务⽀持。但是innodb 提供事务⽀持事务，外部键等⾼级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities)的事务安全(transaction-safe (acid compliant))型表。

是否⽀持外键

• myisam不⽀持，⽽innodb⽀持。

是否⽀持mvcc

• 仅 innodb ⽀持。应对⾼并发事务, mvcc⽐单纯的加锁更⾼效;mvcc只在read committed 和 repeatable read 两个隔离级别下⼯作;mvcc可以使⽤ 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中mvcc实现并不统⼀。

一般情况下我们选择 innodb 都是没有问题的，但是某些情况下你并不在乎可扩展能⼒和并发能⼒，也不需要事务⽀持，也不在乎崩溃后的安全恢复问题的话，选择myisam也是⼀个不错的选择。但是⼀般情况下，我们都是需要考虑到这些问题的。

不要轻易相信“myisam⽐innodb快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，innodb的速度都可以让myisam望尘莫及，尤其是⽤到了聚簇索引，或者需要访问的数据都可以放⼊内存的应⽤。

39.数据库三范式是什么?

第一范式（1nf）

1. 数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。所有关系型数据库系统都满足第一范式数据库表中的字段都是单一属性的，不可再分。
2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。

第二范式（2nf）

1. 是在第一范式（1nf）的基础上建立起来的，即满足第二范式（2nf）必须先满足第一范式（1nf）。
2. 要求数据库表中的每行必须可以被惟一地区分，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
3. 通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。

第三范式（3n）

1. 满足第三范式（3nf）必须先满足第二范式（2nf）。
2. 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息,即数据不能存在传递关系，。像：a–>b–>c 列之间含有这样的关系，是不符合第三范式的。
3. 特征：每一列只有一个值 ，每一行都能区分，每一个表都不包含其他表已经包含的非主关键字信息。

设计思考

• 三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库表。

40.hash索引和b+tree索引底层是怎么实现的（实现原理），有什么不一样？

hash索引

实现

• 把索引的值做hash运算，并存放到hash表中，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。使用较少，一般是memory引擎使用。。

hash索引的优点

• 因为使用hash表存储，按照常理，hash的性能比b+tree效率高很多。

hash索引的缺点。

1. hash索引只能适用于精确的值比较，=，in，或者<>；无法使用范围查询。
2. 无法使用索引排序；
3. 组合hash索引无法使用部分索引；
4. 如果大量索引hash值相同，性能较低；

b+tree索引

实现

• 底层是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可获的所查键值，然后根据查询判断是否需要回表查询数据。innodb使用b+tree索引。
• innodb中，存在两种索引类型

- 主键索引（primary key），在索引内容中直接保存数据的地址。
- 其他索引，在索引内容中保存的是指向主键索引的引用。

所以在使用innodb的时候，要尽量的使用主键索引，速度非常快。

b+tree索引优点

• b+tree所有的节点都遵循（左节点小于父节点，右节点大于父节点，多叉树也类似）天然支持范围查询。查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

hash索引与b+tree索引不同

• hash索引进行等值查询更快（一般情况)，但是无法进行范围查询。
• hash索引不支持使用索引排序。
• hash索引不支持模糊查询以及多列索引的最左前缀匹配，原因是hash函数的不可预测。
• hash索引任何时候都避免不了回表查询数据，而b+tree在符合某些条件（聚集索引、覆盖索引）的时候可以只通过索引完成查询。
• hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而b+tree查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择b+tree树索引可以获得稳定且较好的查询速度，而不需要使用hash索引。

41.mysql有哪些索引？

数据结构角度

b+tree

• 是b-tree的改进版本，同时也是数据库索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比b-tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而b-tree需要获取所有节点，相比之下b+tree效率更高

hash

• 基于哈希表实现，只有精确匹配索引所有列的查询才有效。

fulltext

• 一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引更类似于搜索引擎做的事情，实际生产中我们一般不会使用mysql来做类似搜索引擎的工作。

r-tree

• myisam支持空间索引，可以用作地理数据存储，r-tree无须前缀索引。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。

物理存储角度

聚集索引(clustered index)

1. 指索引项的排序方式和表中数据记录排序方式一致的索引。
2. 也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。
3. 聚集索引的使用场合为：　a.查询命令的回传结果是以该字段为排序依据的；　b.查询的结果返回一个区间的值；　c.查询的结果返回某值相同的大量结果集。
4. 聚集索引会降低 insert，和update操作的性能，所以，是否使用聚集索引要全面衡量。

非聚集索引（non-clusterd index）

1. 索引顺序与物理存储顺序不同。
2. 非聚集索引的使用场合为：　a.查询所获数据量较少时；　b.某字段中的数据的唯一性比较高时；

逻辑角度

1. 普通索引:仅加速查询。
2. 唯一索引:加速查询+列值唯一(可以有null)。
3. 主键索引:加速查询+列值唯一(不可以有null)+表中只有一个。
4. 组合索引:多列值组成一个索引，专门拥有组合搜索，其效率大于索引合并。
5. 全文索引:对文本的内容进行分词，进行搜索。

42.说说索引有哪些设计原则?

什么是索引

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

索引设计原则

1. 搜索的索引列，不一定是所要选择的列。最适合索引的列是在出现在where子句、连接子句、排序 order by 、聚合group by 指定的列，而不是出现在select关键字后的选择列表中的列。
2. 使用唯一索引。考虑某列中值的分布。对唯一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。
3. 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如：有一个char(200)列，如果在前10个或者20个字符内，多数值是惟一的，那么就不要对整个列进行索引。
4. 利用最左前缀。在创建一个n列的索引时，实际是创建了mysql可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同，索引一个列的前缀是利用该列的前你个字符作为索引值)
5. 不要过度索引，删除不再使用或者很少使用的索引。每个额外的索引都要占额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能进行重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或者从不使用，那么会不必要地减缓表的修改速度。此外，mysql在生产一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使mysql选择不到所要使用的最好索引。只保持所需要的索引有利于查询优化。如果想给已有索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。
6. 考虑在列上进行的比较类型。索引可用于”<“、”<=”、”=”、”>=”、”>”和between运算。在模式具有一个直接量前缀时，索引也用于like运算。
7. 使用数据量少的索引，如果索引的值很长，那么查询的速度会受到影响。
8. 选择区分度高的列作为索引，区分度的公式是表示字段不重复的比例。
9. 索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
10. 更新非常频繁的字段不适合创建索引；原因,索引有维护成本。

43.mysql并发事务带来哪些问题?

在典型的应⽤程序中，多个事务并发运⾏，经常会操作相同的数据来完成各⾃的任务（多个⽤户对同⼀数据进⾏操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（dirty read）

• 当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

丢失修改（lost to modify）

• 指在⼀个事务读取⼀个数据时，另外⼀个事务也访问了该数据，那么在第⼀个事务中修改了这个数据后，第⼆个事务也修改了这个数据。这样第⼀个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据a=20，事务2也读取a=20，事务1修改a=a-1，事务2也修改a=a-1，最终结果a=19，事务1的修改被丢失。

不可重复读（unrepeatableread）

• 指在⼀个事务内多次读同⼀数据。在这个事务还没有结束时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。

幻读（phantom read）

• 幻读与不可重复读类似。它发⽣在⼀个事务（t1）读取了⼏⾏数据，接着另⼀个并发事务（t2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（t1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

不可重复读和幻读区别

• 不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

44.数据库事务的四大特性（acid）

事务(transaction)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行 。事务是一个不可分割的工作逻辑单元 事务必须具备以下四个属性，简称acid 属性：

原子性（atomicity）

• 事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。

一致性（consistency）

• 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就*中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

隔离性（isolation）

• 事物的隔离性，基于原子性和一致性，因为事物是原子化，量子化的，所以，事物可以有多个原子包的形式并发执行，但是，每个事物互不干扰。-多个用户并发访问操作数据库时，一个用户的事务操作不能被其它的用户事务所干扰，也就是多个并发事务之间操作是隔离的。

持久性（durability）

• 是指一个事务一旦提交成功，那么对数据库的数据的修改是永久性的。就算数据库出现故障或关机，数据也是永久保存下来的。

45.innodb支持些事务隔离级别，他们有什么区别？

read uncommitted（读取未提交内容）

• 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（dirty read）。

read committed（读取提交内容）

• 这是大多数数据库系统的默认隔离级别（但不是mysql默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（nonrepeatable read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

repeatable read（可重读）

• 这是mysql的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（phantom read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。innodb和falcon存储引擎通过多版本并发控制（mvcc，multiversion concurrency control 间隙锁）机制解决了该问题。注：其实多版本只是解决不可重复读问题，而加上间隙锁（也就是它这里所谓的并发控制）才解决了幻读问题

serializable（可串行化）

• 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

汇总