MySQL背诵版

1.数据库的三范式是什么？什么是反范式？

第一范式：数据具有原子性不可再分。

第二范式：满足第二范式必须满足第一范式，数据库中的每一行都可以被唯一的区分也就是主键。要求属性完全依赖主关键字，不可以部分依赖。

第三范式：每一列只能有一个值，每一行都能区分，每一个表都不包含其他表已经包含的非主关键字信息。

反范式：通过增加冗余或者重复的数据来提高数据库的性能，

2.mysql有哪些数据类型？
数值类型：TINYINT,SMALLINT,MEDIUMINT,INT,INTEGER,BIGINT,FLOAT,DOUBLE,DECIMAL.

日期和时间类型：DATE,TIME,YEAR,DATETIME,TIMESTAMP.

字符串类型：CAHR(定长)，VARCHAR(变长)，TINYBLOB(不超255个字符的二进制字符串)，TINYTEXT(短文本字符串)，BLOB(二进制形成的长文本数据)，TEXT(长文本数据)，MEDIUMBLOB(二进制形式的中等长度的文本数据)，MEDIUMTEXT(中等程度的文本数据)，LONGBLOB(二进制形式的极大文本数据)，LONGTEXT(极大文本数据)。

3.MySQL 中 varchar 与 char 的区别？varchar(50) 中的 50 代表的涵义？

varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型 ，

varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样，

但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)，所以要选择合适的长度。

4. int(11) 中的 11 代表什么涵义？

int(11) 中的 11 ，不影响字段存储的范围，只影响展示效果 。

5. 金额(金钱)相关的数据，选择什么数据类型？  

方式一，使用 int 或者 bigint 类型。如果需要存储到分的维度，需要 *100 进行放大

方式二，使用 decimal 类型，避免精度丢失。如果使用 Java 语言时，需要使用 BigDecimal 进行对应

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

一般情况下，我们创建的表的类型是 InnoDB ，如果新增一条记录（不重启 MySQL 的情况下），这条记录的 ID 是18 ；但是如果重启 MySQL 的话，这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 ID 丢失。

但是，如果我们使用表的类型是 MyISAM ，那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MYSQL 后，自增主键的最大 ID 也不会丢失。

7. 表中有大字段 X(例如：text 类型)，且字段 X 不会经常更新，以读为为主，请问您是选择拆成子表，还是继续放一起?写出您这样选择的理由

拆带来的问题：连接消耗 + 存储拆分空间 ，如果能容忍拆分带来的空间问题，拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ，减少连接消耗，最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。

不拆可能带来的问题：查询性能

实际场景下，例如说商品表数据量比较大的情况下，会将商品描述单独存储到一个表中。即，使用拆的方案。

8. 如何选择合适的存储引擎？

目前，MySQL 默认的存储引擎是 InnoDB ，并且也是最主流的选择 ，支持事务，支持行级锁和表级锁，能支持更多的并发量，查询不加锁，完全不影响查询，支持崩溃后恢复。

在 MySQL5.1 以及之前的版本，默认的存储引擎是 MyISAM ，但是目前已经不再更新，且它有几个比较关键的缺点 ，不支持事务，使用表级锁，如果数据量大，一个插入操作锁定表后，其他请求都将阻塞。

9. 请说明 InnoDB 和 MyISAM 的区别 ？

innodb支持事务，支持行锁，支持崩溃后恢复支持外键，myisam不支持事务，表级锁，不支持崩溃后恢复，不支持外键。

10.为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

对于 SELECT COUNT(*) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。

innodb优化方案：count主键，如果主键过大，增加一个二级索引。

11.什么是索引？ 

索引就是目录，MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

12.索引的好处与坏处？

好处：提高检索速度，降低io，降低排序成本，降低cpu消耗，因为索引先将数据排好序

坏处：占用存储空间，因为索引也是一张表，降低表的更新速度，因为表更新，索引也要进行更新

13.索引的使用场景？

对于小的表，大部分用于全表扫描。对于中大型表。

对于特大型表，建立索引的代价随着增长，使用分库分表方案（sharding sphere，mycat）

14.索引的类型？

1、普通索引：最基本的索引，没有任何约束。

2、唯一索引：与普通索引类似，但具有唯一性约束

3、主键索引：特殊的唯一索引，不允许有空值。

4、复合索引：将多个列组合在一起创建索引，可以覆盖多个列。

5、外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作

6、全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。 （solar，elasticserach）

15.MySQL 索引的“创建”原则？

1、最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。

2、索引列的基数越大，索引效果越好。

3、根据情况创建复合索引，复合索引可以提高查询效率。

4、避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。

5、主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率

6、对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

16.MySQL 索引的“使用”注意事项？  

1、应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

注意，column IS NULL 也是不可以使用索引的

2、应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。

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

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

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

6、复合索引遵循前缀原则。

7、如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引

8、列类型是字符串类型，查询时一定要给值加引号，否则索引失效。

9、LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引

17. 想知道一个查询用到了哪个索引，如何查看?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。 详解百度mysql explain执行计划。

18.事务的特性指的是？

原子性：要么全部完成，要么全部失败。

一致性：事务开始和结束，数据库的完整性没有被破坏，写入的数据必须满足数据的规则

隔离性：防止事务交叉执行导致数据不一致

持久性：事务结束后，对数据的修改是永久性的

19. 事务的并发问题？

脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

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

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

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

20.事务隔离级别？

1.READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的 会导致脏读

2.READ COMMITTED（提交读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的 （不可重复读）

3.REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行

4.SERIALIZABLE（可串行化）：强制事务串行执行。

21.MySQL的innodb的事务隔离级别？

可重复读。

22.mysql的锁机制？

共享锁（读锁）：不堵塞，多个用户可以同时读一个资源，互不干扰

排他锁（写锁）：一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他的用户读取正在写入的资源

23.锁的粒度？

表锁：系统开销最小，会锁定整张表，MyIsam 使用表锁

行锁：最大程度的支持并发处理，但是也带来了最大的锁开销，InnoDB 使用行锁

24.有哪些手段可以降低死锁？

设置锁的超时时间。

按同一顺序访问

避免事务中的用户交互

保持事务的简短并在一个批处理中

使用低隔离级别

使用绑定连接

25.MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？

InnoDB 是基于索引来完成行锁。例如：SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE  FOR UPDATE 可以根据条件来完成行锁锁定，并且 id 是有索引键的列,如果 id 不是索引键那么 InnoDB 将完成表锁

26.innodb的行锁怎么实现的？

。。。。。

27.MySQL的执行顺序？
sql语句的执行过程中，每一步都会产生一个虚拟表，来保存sql语句的执行结果

1.执行from语句，确定哪个表开始，对关联的表取笛卡尔积，表一

2.执行on过滤，根据on的条件去掉不符合条件的数据，表二

3.添加外部行，添加被表er过滤出的数据，并且赋值为null，表三(在表连接时)

4.执行where过滤，表四

5.group by 主要是对表四进行分组，表五

6.执行having过滤，主要配合group by 使用，得到表六

7.select表，表七

8.执行distinct字句，对distinct操作的列增加了一个唯一索引，以此来去除重复数据

9.执行order by 对指定的列进行排序，然后返回一个新的虚拟表，表八

10.执行limit语句

28.MySQL优化？

1.分析查询速度

记录慢查询日志：使用pt-query-digest进行分析

2.使用show profile

set peofiling=1; # 服务器上执行的所有语句会检测消耗的时间、存到临时表中

show profiles # 所有的SQL语句执行记录

show profile for query 临时表ID # 查执行ID的SQL语句

3.show status

show status会返回一些计数器，show global status查看服务器级别的所有计数

4.show processlist

观察是否有大量的线程处于不正常的状态或特征。

5.explain：分析单条sql语句

29.优化查询过程中的数据访问？

数据太多会导致查询性能下降

确定应用程序是否在检索大量超过需要的数据，可能太多行或列

确认mysql服务器是否在分析大量不必要的数据行

1.查询不需要的记录，使用limit解决

2.多表管理返回全部列，需要定制列如a.id,b.name

3.总是取出全部列，select * 会让优化器无法完成索引覆盖扫描优化

4.重复查询相同的数据，可以使用缓存，下次直接读取缓存

5.切分查询，把大查询分为多个小的查询

6.分解关联查询，将一条关联语句分解成多个sql语句执行

30.优化特定类型的查询

1.count（*）占用*会忽略所有的列，直接统计所有的列数，因此不要使用count（列名）

2.确定on或者using字句的列上有索引

3.确保group by 和order by中只有一个表中的列，这样MySQL才有可能使用索引

4.优化子查询尽量使用关联查询替代

5.group by和distinct 关联查询中使用主键列进行分组效率会更高，如果不需要order by 进行group by 时使用order by MySQL不会进行 文件排序。

6.优化union unionall的小路高于union