MySQL
基本概念
数据库范式是什么？
范式是数据库设计规范，范式越高则数据库冗余越小，但查询也更复杂，一般只需满足第三范式。
第一范式，每列都是不可再分的数据单元。
第二范式，在第一范式的基础上消除部分依赖，非主键列完全依赖于主键列。
第三范式，在第二范式的基础上消除传递依赖，非主键列只依赖于主键列。
视图是什么？
视图是一个虚拟表，是存储在数据库中的查询 SQL 语句，视图只是一个逻辑，具体结果在引用视图时动态生成。
优点：① 具有安全性，可以进行权限控制，创建只读视图并公开给特定用户。② 可以简化复杂的查询，只保存其逻辑。
游标是什么？
游标是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。
存储引擎
InnoDB 有什么特点？
① MySQL5.1 开始的默认引擎，最大的优点是支持事务和外键，InnoDB 的性能和自动崩溃恢复特性使它在非事务型需求中也很流行，一般应该优先考虑使用 InnoDB。
② 底层存储结构是 B+ 树，每个节点都对应 InnoDB 的一个页。非叶子节点只有 key 值，叶子节点包含完整的数据。
③ 支持行锁，采用 MVCC 支持高并发，实现了四个标准的隔离级别，默认级别是可重复读，通过间隙锁防止幻读。
④ 基于聚簇索引，对主键查询有很高的性能。
⑤ 内部做了很多优化，例如加速读操作的自适应哈希索引、加速插入操作的缓冲区等。
MyISAM 有什么特点？
① MySQL5.1及之前的默认引擎，提供的特性包括全文索引、空间索引等，不支持事务、行锁和外键。
② 最大的缺陷是崩溃后无法恢复，在插入和更新数据时需要锁定整张表，效率低。
③ 对于只读的数据或者表比较小、可以忍受修复操作的情况可以使用 MyISAM。
Memory 有什么特点？
① 如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，可以使用 Memory 表。
② 数据保存在内存，不需要磁盘 IO，表的结构在重启后会保留，数据会丢失。
③ 支持哈希索引，查找速度快。
④ 使用表锁，并发性能低。
索引
有哪些索引？
B-Tree：大多数引擎都支持这种索引，但底层使用不同结构，例如 NDB 使用 T-Tree，InnoDB 使用 B+ Tree。所有的值都是顺序存储的，并且每个叶子页到根的距离相同。B-Tree 索引能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取数据，而是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点的指针指向的是被索引的数据，而不是其他节点页。
Hash： 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。
空间索引：MyISAM 的一个特殊索引类型，用作地理数据存储。
全文索引：MyISAM 的一个特殊的 B-Tree 索引，一共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的文档指针。用于通过关键字匹配进行查询。
聚簇索引是什么？
聚簇索引不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B 树索引和数据行。当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，由于无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
优点：可以把相关数据保存在一起；将索引和数据保存在同一个 B 树中，获取数据比非聚簇索引要更快。
缺点：如果数据全部在内存中会失去优势；更新代价高，强制每个被更新的行移动到新位置；插入行或主键更新时，可能导致页分裂，占用更多磁盘空间。
索引建立的规范？
① 控制数量：索引越多代价越高，对于 DML 频繁的表，索引过多会导致很高的维护代价。
② 使用短索引：假如构成索引的字段长度比较短，那么在储块内就可以存储更多的索引，提升访问索引的 IO 效率。
③ 建立索引：对查询频次较高且数据量比较大的表建立索引。如果 WHERE 子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
④ 使用前缀索引：对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。
⑤ 合适的索引顺序：当不需要考虑排序和分组时，将选择性最高的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高。
⑥ 删除重复索引：MySQL 允许在相同列上创建多个索引，重复索引需要单独维护。
索引失效的情况有哪些？
① 隐式类型转换，常见情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。
② 如果条件中 OR 只有部分列使用了索引，索引会失效。
③ 执行 LIKE 操作时，最左匹配会被转换为比较操作，但如果以通配符开头，存储引擎就无法做比较，。索引失效
④ 如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
⑤ 对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。
⑥ 如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。
事务和锁
事务是什么？
事务是一组原子性的 SQL 语句，具有ACID四个特性。
原子性：一个事务在逻辑上是必须不可分割的最小单元，整个事务中的所有操作要么全部成功，要么全部失败。
一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性：针对并发事务而言，要隔离并发运行的多个事务之间的影响，数据库提供了多种隔离级别。
持久性：一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。
事务的隔离级别有哪些？
未提交读：事务中的修改即使没有提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还存在不可重复读和幻读，很少使用。
提交读：多数数据库的默认隔离级别，事务只能看见已提交事务的修改。存在不可重复读，两次执行同样的查询可能会得到不同结果。
可重复读：是 MySQL 的默认隔离级别，解决了不可重复读，保证同一个事务中多次读取同样的记录结果一致，InnoDB 通过 MVCC 解决。但无法解决幻读，幻读指当某个事务在读取某个范围内的记录时，会产生幻行。
可串行化：最高隔离级别，通过强制事务串行执行避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少使用，只有非常需要确保数据一致性时考虑。
乐观锁和悲观锁有什么区别？
乐观锁：每次读数据时都认为别人不会修改，所以不会加锁，但在更新时会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。适用于多读场景，可以提高吞吐量。
悲观锁：每次读数据时都认为别人会修改，所以每次读数据时都会加锁，这样别人想拿数据就会阻塞直到拿到锁。比如 MySQL 的行锁、表锁、读锁、写锁等，都是在做操作前先加锁。
什么是读写锁？
读锁即共享锁（S 锁），写锁即排它锁（X 锁），读锁是共享的，相互不阻塞，多个客户在同一时刻可以读取同一资源。写锁是排它的，会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入。
写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但读锁不能插入到写锁前面。
表锁和行锁有什么区别？
表锁会锁定整张表，加锁快，开销小，不会出现死锁，但并发度低、锁冲突概率高。适合以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如 Web 应用。
行锁可以最大程度地支持并发，锁冲突概率低，但开销大，会出现死锁。行锁只在存储引擎层实现，而服务器层没有实现，适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如在线事务处理系统。
死锁是什么，产生的原因？
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。
锁的行为和顺序和存储引擎相关，以同样的顺序执行语句，有些存储引擎会产生死锁有些则不会。
如何解决和避免死锁？
发生死锁后，InnoDB 一般都能自动检测到并使一个事务释放锁并回滚，另一个事务获得锁继续完成事务。但在涉及外部锁或表锁时，InnoDB 并不能完全自动检测到死锁，需要设置锁等待超时参数 innodb_lock_wait_timeout 解决。
MyISAM：在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，不会出现死锁。
InnoDB：① 如果要更新记录，应该直接申请足够级别的锁，即排它锁。② 多个事务应尽量约定以相同的顺序来访问表。
MVCC 是什么？
多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞读，写也只锁定必要行。
InnoDB 通过在每行记录保存两个隐藏列来实现，这两个列分别保存了行的创建和过期时间，但不是实际时间而是系统版本号，每开始一个新的事务系统版本号会自动递增，事务开始时的系统版本号会作为事务版本号，用来和查询到的每行记录的版本号比较。
MVCC 只能在提交读和可重复读两个级别工作，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行，而可串行化则会对所有读取的行都加锁。
SQL
内连接、左连接和外连接的区别？
左连接：关键字 left join on，以左表为主表，可以查询左表存在而右表为 null 的记录。
右连接：关键字 right join on，以右表为主表，可以查询右表存在而左表为 null 的记录。
内连接：查询左右表同时满足条件的记录，两边都不可为 null。
如何定位低效 SQL？
可以通过慢查询日志定位到已经执行完毕的 SQL 语句。还可以使用 SHOW PROCESSLIST 命令查看正在执行的线程，包括线程状态、是否锁表等，可以实时查看 SQL 的执行情况，同时对一些锁表操作进行优化。
定位到低效语句后，就可以通过 EXPLAIN 或 trace 进一步处理。
EXPLAIN 有哪些字段？
执行计划是 SQL 调优的重要依据，可以通过 EXPLAIN 命令查看 SQL 的执行计划，如果作用在表上，该命令相当于 DESC。
| 字段 | 含义 |
|---|---|
| id | SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。 |
| select_type | 查询中每个 SELECT 子句的类型，例如 SIMPLE 表示简单查询，PRIMARY 表示复杂查询的最外层查询。 |
| type | 访问类型，性能由差到好：ALL、index、range（至少达到）、ref（要求）、const、system、NULL。 |
| possible_keys | 查询时可能用到的索引，列出大量可能索引时意味着备选索引太多。 |
| key | 查询时实际使用的索引，没有则为 NULL。 |
| key_len | 所用索引字段的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。 |
| ref | 表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。 |
| rows | 估算找到所需记录所需要读取的行数。 |
| Extra | 额外信息，例如 Using temporary 表示需要使用临时表存储结果集；Using index 表示只需使用索引就可满足查询要求，说明表正在使用覆盖索引。 |
优化 SQL 的方法有哪些？
① 避免全表扫描：考虑在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，尽量用 BETWEEN 取代。
② 优化 COUNT：某些业务不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。
③ 避免子查询：在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。
④ 禁止排序：当查询使用 GROUP BY 时，结果集默认会按照分组字段排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。
⑤ 优化分页：从上一次取数据的位置开始扫描，避免使用 OFFSET。
⑥ 优化 UNION：MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。
⑦ 使用用户自定义变量：用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。
delete、drop、truncate的区别？
delete 可以删除部分数据也可以删除全部数据，和 truncate 一样只删除数据而不删除表的结构，drop 会删除表的结构。
delete 是 DML 操作，可以进行回滚；drop 和 truncate 是 DDL，不能进行回滚。
速度来说，一般 drop > truncate > delete。
MySQL 有哪些聚合函数？
① max 求最大值。② min 求最小值。③ count 统计数量。④ avg 求平均值。⑤ sum 求和。
数据类型
VARCHAR 和 CHAR 的区别？
VARCHAR 存储可变字符串，比 CHAR 更节省空间，需要 1 或 2 个额外字节记录字符串长度，不删除末尾空格。
场景：字符串列的最大长度比平均长度大很多、列的更新很少、使用了 UTF-8 这种复杂字符集。
CHAR 是定长的，根据定义的字符串长度分配空间， 删除末尾空格。
场景：很短的字符串、所有值都接近同一个长度。
DATETIME 和 TIMESTAMP 的区别？
DATETIME 能保存大范围的值，从 1001~9999 年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用 8 字节存储空间。
TIMESTAMP 和 UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。
数据类型的优化策略有哪些？
① 尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常也更快，因为它们占用更少的磁盘、内存和 CPU 缓存。
② 尽可能简单，简单数据类型的操作需要更少的 CPU 周期，例如应该使用 date、time 或 datetime 而不是字符串来存储日期和时间。
③ 通常情况下最好指定列为 NOT NULL，除非需要存储 NULL值。查询中包含可为 NULL 的列更难优化，可为 NULL 的列使索引、索引统计和值比较都更复杂，并且会使用更多存储空间。
4996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
