mysql 笔记

MySQL主要的版本

MySQL 5.7

1)、安全性

在MySQL 5.7中，有不少安全性相关的改进。包括：

• MySQL数据库初始化完成以后，会产生一个 root@localhost 用户，从MySQL 5.7开始，root用户的密码不再是空，而是随机产生一个密码，这也导致了用户安装5.7时发现的与5.6版本比较大的一个不同点。
• MySQL官方已经删除了test数据库，默认安装完后是没有test数据库的，就算用户创建了test库，也可以对test库进行权限控制了
• MySQL 5.7版本提供了更为简单SSL安全访问配置，并且默认连接就采用SSL的加密方式。
• 可以为用户设置密码过期策略，一定时间以后，强制用户修改密码。

2)、灵活性

MySQL 5.7的两个全新的功能，即JSON和generate column

CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

3)、可用性

• 在线设置复制的过滤规则，不再需要重启MySQL，只需要停止SQL thread，修改完成以后，启动SQL thread

MySQL 8.0

数据库三大范式是什么

• 第一范式：每个列都不可以再拆分。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
• 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
• 在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

MySQL有关权限的表都有哪几个

• MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：
• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

MySQL的binlog有有几种录入格式？分别有什么区别？

MySQL的binlog有三种格式: statement，row和mixed。

• 1)、statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• 2)、row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• 3)、mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
• 此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

平时用到哪些关系型数据库和非关系数据库, 可以谈谈你对它们的理解吗?

关系型数据库比如MySQL, 非关系数据库(NoSql数据库)比如Redis, MongoDB等等。

• 1)、事务方面
• 关系型数据库的最大特点就是事务的一致性，所以对于订单模型，对一致性要求比较高的还是建议用MySQL。
• 2)、关系数据库的另一个特点就是其具有固定的表结构
• 其实在业务模型中, 表结构固定反而是一件好事, 没有约束的模型，更容易出问题。
• 3)、复杂SQL，特别是多表关联查询
• NoSql是不支持JOIN 这种查询的。
• 4)、索引方式
• 关系型数据库：B树、哈希等
• NoSql：键值索引
• 5)、并发支持
• 关系型数据库：通过事务和锁来支持并发，高并发情况下，执行效率较低。
• NoSql：打破了传统关系型数据库范式的约束和事务一致性，因此并发性能高。

可以简单说说你对MySQL的逻辑架构了解吗？

• 第一层是服务器层，主要提供连接处理、授权认证、安全等功能。
• 第二层实现了 MySQL 核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器、视图等。
• 第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

了解MySQL中的MVCC是什么?

• MVCC 是多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。
• InnoDB 的MVCC 通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间。不过存储的不是实际的时间值而是系统版本号，每开始一个新的事务系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
• MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

PostgreSQL相对于MySQL的优势

• 在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；
• 存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；
• 对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；
• PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
• PG的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。

PostgreSQL和MySQL的一些区别

• MySQL不支持地理数据类型。
• 从9.2开始，PG支持json数据类型。相对于MySQL来说，PG对json的支持比较先进。他有一些json指定的操作符和函数，是的搜索json文本非常高效。
• 9.4开始，可以以二进制的格式存储json数据，支持在该列上进行全文索引（GIN索引），从而在json文档中进行快速搜索。
• 从5.7开始，MySQL支持json数据类型，比PG晚。也可以在json列上建立索引。然而对json相关的函数的支持比较有限。不支持在json列上全文索引。由于MySQL对SQL支持的限制，在存储和处理json数据方面，MySQL不是一个很好的选择。

索引有哪些使用场景

1)、应该创建索引的场景

• 主键应该创建主键索引。
• 频繁作为查询条件的字段应该创建索引。
• 查询中需要与其他表进行关联的字段应该创建索引。
• 需要排序的字段应该创建索引。
• 需要统计或分组的字段应该创建索引。
• 优先考虑创建复合索引。

2)、不应创建索引的场景

• 数据记录较少的表。
• 经常需要增删改操作的字段。
• 数据记录重复较多且分布平均的字段（如性别、状态等）。
• 索引的选择性是指索引列中不同值的数目与表中记录总数的比。索引的选择性越接近于1，创建索引的价值就越高。反之就越低。

创建索引的原则是什么?

• 1)、选择唯一性索引
• 2)、为经常需要排序、分组和联合操作的字段建立索引
• 3)、为常作为查询条件的字段建立索引
• 4)、限制索引的数目
• 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
• 5)、尽量使用数据量少的索引
• 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
• 6)、尽量使用前缀来索引
• 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
• 7)、最左前缀匹配原则
• 8)、查询时使用计算，会导致索引失效

不是所有的查询使用索引查询都能提高性能

• 像 like % xxx% 、不满足最左匹配原则的情况下并不能使用到建好的索引
• MySQL 在可以使用多个索引的情况下，查询优化器会根据查询范围的数据量估算索引代价，最坏的是估算完毕后，发现这些索引的字段区分度不高，还不如扫全表，于是 Mysql 扫全表了
• 如果索引的列比需要查询的列少，Mysql 会通过聚簇索引回表查询其他字段
• 如果索引的字段很大，每个页能存的条目就很少，读取时 IO 会消耗更多，页 Buffer 轮替的更快

索引有哪些优缺点？

1)、索引的优点

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

2)、索引的缺点

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
• 空间方面：索引需要占物理空间。

讲一讲聚簇索引与非聚簇索引？

• 在 InnoDB 里，索引B+Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。
• 而索引B+Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

• 非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
• 对于InnoDB来说，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。第一次索引一般是顺序IO，回表的操作属于随机IO。需要回表的次数越多，即随机IO次数越多，我们就越倾向于使用全表扫描 。
• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是覆盖索引的话，查一次即可

注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

百万级别或以上的数据如何删除

方法一:

1. 索引是单独的文件，增删改时，当存在索引，会消耗额外io。删除速度和索引数量成正比
2. 删除索引
3. 删除需要删除的数据
4. 重新建立索引

这个方法有一个很明显的缺点, 就是在正式环境这个表如果访问频率比较高的话， 删除索引后有大量的SQL查询会导致数据库IO和CPU特别高

方法二:

• 之前我在正式环境的做法是 将删除任务拆分为一次删除1w条， 然后把删除任务重新压入的异步任务队列里面。

什么是最左前缀原则？什么是最左匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：

对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

数据库为什么使用B+树而不是B树

B树和B+树的区别主要有两点：

• 在B树中，你可以将键和值存放在内部节点和叶子节点，但在B+树中，内部节点都是键，没有值。叶子节点同时存放键和值
• B+树的叶子节点有一条链相连，而B+树的叶子节点各自独立。

使用B+树的好处

• 由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。
• B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间。
• 数据库的数据读取都是需要进行代价巨大的磁盘IO操作，因此，更快地缩小范围和更少的读取次数是数据库需要关注的重点。而B+树在这些点上比B树做的更好。这就是为什么数据库要选用B+树作为底层实现。