本文详细介绍了MySQL数据库的三大范式，强调了遵循范式的重要性。讨论了InnoDB和MyISAM引擎的差异，如行锁与表锁、事务隔离级别以及内存管理。此外，还涵盖了SQL查询的执行顺序、ACID特性、索引原理和优化、NoSQL数据库的作用以及如何排查和避免慢查询。通过对数据库设计、查询优化和性能提升的深入理解，有助于提升数据库管理的效率和性能。
MySQL
数据库的三范式是什么？
- 第一范式: 每个列都不可以再拆分.
- 第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分.
- 第 三范式: 非主键列只依赖于主键,不依赖于其他非主键.
在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由.比如性能. 事实上我们经常会 为了性能而妥协数据库的设计.
一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？
表类型如果是 MyISAM ，那 id 就是 8。
表类型如果是 InnoDB，那 id 就是 6。
InnoDB 表只会把自增主键的最大 id 记录在内存中，所以重启之后会导致最大 id 丢失。
MySQL 常用的引擎？
-
InnoDB 引擎：InnoDB 引擎提供了对数据库 acid 事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。
- MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。
- 该引擎是不支持全文搜索，同时启动也比较的慢，它是不会保存表的行数的，所以当进行 select count( * ) from table 指令的时候，需要进行扫描全表。
- 由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。
-
MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。
- 当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。
- 和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。
- 如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。
说一下 MySQL 的行锁和表锁？
MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
- 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
- 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。
如何获取当前数据库版本？
使用 select version() 获取当前 MySQL 数据库版本。
说一下 ACID 是什么？（数据库的四大特性）
数据库的四大特性: ACID ( Atomicity Consistency Isolation Durability )
-
Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
-
Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
-
Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
-
Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
事务：
事务是数据库中的用于保证一系列的写操作(增/删/改)要么全部成功，要么全部失败的机制！
数据库的事务隔离？
- MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：
transaction-isolation = REPEATABLE-READ - 可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
- READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
- READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
- REPEATABLE-READ：可重复读，默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。
- SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。
补充：
- 脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。
- 可重复读 ：是指在一个事务内，多次读同一数据。
- 幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。
char 和 varchar 的区别是什么？
- char是一个定长字段,假如申请了 char(10) 的空间,那么无论实际存储多少内容.该字段都占用10个字符
- 而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用 了多长的空间.
- 在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.
float 和 double 的区别是什么？
- float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
- double 最多可以存储 16 位的十进制数，并在内存中占 8 字节。
MySQL 的内连接、左连接、右连接有什么区别？
- 内连接：inner join。内连接是把匹配的关联数据显示出来
- 左连接：left join。左连接是将匹配的关联数据和左边的表全部显示出来
- 右连接：right join。右连接是将匹配的关联数据和右边的表全部显示出来
MySQL中多表联查需要注意的事项?
- 不能直接让两张表进行左外连接，左外连接是在内连接基础上进行的，首先让两张表先做“有条件”的内连接。条件就是两张表要有共同列，在共同列上做内连接，然后才能进行左外连接。
- 不能直接让两张表进行右外连接，右外连接是在内连接基础上进行的，首先让两张表先做“有条件”的内连接。条件就是两张表要有共同列，在共同列上做内连接，然后才能进行右外连接。
SQL Select 语句完整的执行顺序
查询中用到的关键词主要包含 如下展示，并且他们的顺序依次为
select ...from 表1 as 表名1 left join 表2 as 表名2 on 表名1.字段=表名2.字段 where ...
group by ...having ... order by ...asc/desc limit m,n ;
from: 需要从哪个数据表检索数据
where: 过滤表中数据的条件
group by: 如何将上面过滤出的数据分组算结果
order by : 按照什么样的顺序来查看返回的数据
limit m,n : 从m处开始获取n条
查询语句的执行过程
- 当客户端连接 MySQL 时，会发出连接请求到连接器，连接器此时就会去验证这个连接的账号密码.如果账号或者密码不正确，客户端就会收到一个 Access denied for user 的错误，之后此次连接结束;账号密码正确，连接器会到权限表里面查询出该账号所拥有的权限，之后这个连接里面的权限判断，都是以此时读到的权限为根据
- 在MYSQL8.0之前会先去查询缓存中，以这条SQL语句作为key在内存中查询是否有结果，如果有则先判断是否有权限，有权限则返回客户端，否则报错；如果没有从查询缓存命中则进入下一步分析器
- 通过分析器进行词法分析，提取sql语句的关键元素，比如提取上面这个语句是查询select，提取需要查询的表名，提取需要查询的列，提取查询条件。然后判断这个sql语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步优化器。
- 经过分析器分析之后， MySQL 就知道你要的是什么了，但是就像条条大路通罗马一样，看似是一条简单的 SQL 查询语句，有可能有好多条执行路径可以走，比如说要查询的表里面有多个索引，我使用哪儿个索引效率会比较高呀；多表联查的时候，我先关联哪儿个表效率会比较高呀，像这种就是优化器需要做的事情，优化器做完优化之后，就到了执行器。
- 执行器就是要去执行语句，此时肯定要看看对于要查询的表 T有没有查询权限，如果没有直接就拒绝。如果有的话，那就会这样(在这里以 InnoDB 为例):调用 InnoDB 引擎接口取这个表的第一行，判断 ID 的值是不是 10 ，如果不是就跳过，如果是那就放在结果集中；调用引擎接口取“下一行”；重复相同的判断逻辑，直到这个表的最后一行
- 执行器将上述遍历过程中所有满足条件的行，组成记录集返回给客户端
- 至此，语句执行结束
说一下乐观锁和悲观锁？
- 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
- 数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。
- 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。
MySQL 问题排查都有哪些手段？
- 使用 show processlist 命令查看当前所有连接信息。
- 使用 explain 命令查询 SQL 语句执行计划。
- 开启慢查询日志，查看慢查询的 SQL。
如何做到 MySQL数据库的性能优化？
- 选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL， 例如’ 省份’、’ 性 别’ 最好适用 ENUM
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 使用联合(UNION)来代替手动创建的临时表
- 事务处理、优化事务处理
- 锁定表
- 使用外键
- 使用索引 （为搜索字段创建索引）
- 优化的查询语句 （避免使用 select *，列出需要查询的字段）
数据库结构优化
1）范式优化： 比如消除冗余（节省空间。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表： 垂直拆分和水平拆分
数据表损坏的修复方式有哪些？
使用 myisamchk 来修复，具体步骤：
1）修复前将mysql服务停止。
2）打开命令行方式，找到mysql的安装目录的bin/myisamchk工具，在命令行中输入：
myisamchk -c -r ../data/tablename/posts.MYI
然后myisamchk 工具会帮助你恢复数据表的索引。也不用重新启动mysql，问题就解决了
数据库触发器
- DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生DML事件时启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。
- DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生(DDL事件时启用。DDL事件即指在表或索引中的create、alter、drop语句也。
- 登陆触发器：是指当用户登录SQL SERVER实例建立会话时触发。
mysql-获取当前系统时间
1.获取当前日期+时间
这两个函数都是获取日期+时间，不同之处在于：now()在执行开始时值就得到了，sysdate()在函数执行时动态得到值
2.获取当前日期：
3.获取当前时间：
4.获取UTC时间:
如何查看MySQL的执行计划
在查询语句前添加上explain
索引
索引的概念和优点
- 概念：
索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能。 - 优点：
- 创建唯一性索引，保证数据库表中每一行数据的唯一性
- 大大加快数据的检索速度，这也是创建索引的最主要的原因
- 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
索引的分类
- 普通索引：最基本的索引，它没有任何限制。
- 唯一索引：与普通索引类似，不同的就是索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 主键索引：它是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。
- 联合索引（又叫复合索引）：多个字段上建立的索引，能够加速复合查询条件的检索。
- 全文索引：老版本 MySQL 自带的全文索引只能用于数据库引擎为 MyISAM的数据表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索，可以通过扩展 MySQL，添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。
如何避免索引失效
- 范围查询时, 右边的列不能使用索引, 否则右边的索引也会失效.
- 不要在索引上使用运算, 否则索引也会失效.
- 字符串不加引号, 造成索引失效.
- 尽量使用覆盖索引, 避免 select *, 这样能提高查询效率. 如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用 select *
- or 关键字连接,用 or 分割开的条件, 如果 or 前面的列有索引, or 后面的列没有索引, 那么查询的时候前后索引都会失效！如果一定要用 or 查询, 可以考虑下 or 连接的条件列都加索引, 这样就不会失效了.
举例:
- 范围查询时, 右边的列不能使用索引, 否则右边的索引也会失效.
索引生效案例
select * from tb_seller where name = "小米科技" and status = "1" and address = "北京市";
select * from tb_seller where name = "小米科技" and status >= "1" and address = "北京市";
索引失效案例
select * from tb_seller where name = "小米科技" and status > "1" and address = "北京市";
address 索引失效, 因为 status 是大于号, 范围查询.
- 不要在索引上使用运算, 否则索引也会失效.
比如在索引上使用切割函数, 就会使索引失效.
select * from tb_seller where substring(name, 3, 2) = "科技";
- 字符串不加引号, 造成索引失效.
如果索引列是字符串类型的整数, 条件查询的时候不加引号会造成索引失效. Mysql 内置的优化会有隐式转换.
索引失效案例
select * from tb_seller where name = "小米科技" and status = 1
- 尽量使用覆盖索引, 避免 select *, 这样能提高查询效率. 如果索引列完全包含查询列, 那么查询的时候把要查的列写出来, 不使用 select *
- or 关键字连接
用 or 分割开的条件, 如果 or 前面的列有索引, or 后面的列没有索引, 那么查询的时候前后索引都会失效！如果一定要用 or 查询, 可以考虑下 or 连接的条件列都加索引, 这样就不会失效了.
索引失效案例:
select * from tb_seller where name = "小米科技 " or createTiem = "2018-01-01 00:00:00";
MySQL 索引是怎么实现的？
- 索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。
- MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的。B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。
补充:
B+树是B树的一种变体，也属于平衡多路查找树，大体结构与B树相同，包含根节点、内部节点和叶子节点。多用于数据库和操作系统的文件系统中，由于B+树内部节点不保存数据，所以能在内存中存放更多索引，增加缓存命中率。另外因为叶子节点相连遍历操作很方便，而且数据也具有顺序性，便于区间查找。
B+树和B树最大的不同
- B+树内部有两种结点，一种是索引结点，一种是叶子结点。
- B+树的索引结点并不会保存记录，只用于索引，所有的数据都保存在B+树的叶子结点中。而B树则是所有结点都会保存数据。
- B+树的叶子结点都会被连成一条链表。叶子本身按索引值的大小从小到大进行排序。即这条链表是 从小到大的。多了条链表方便范围查找数据。
- B树的所有索引值是不会重复的，而B+树 非叶子结点的索引值 最终一定会全部出现在 叶子结点中。
索引的优缺点
-
优点
- 提高数据检索的效率，降低数据库的 IO 成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。
-
缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列 的字段，都会调整因为 更新所带来的键值变化后的索引信息。
怎么验证 MySQL 的索引是否满足需求？
使用 explain 查看 SQL 是如何执行查询语句的，从而分析你的索引是否满足需求。
explain 语法：explain select * from table where type=1。
NoSQL
NoSQL，全名为Not Only SQL，指的是非关系型的数据库
为什么用NoSQL
传统的关系型数据库如MySQL、SQL Server、Oracle等都是将复杂的数据结构归结为简单的二元关系（即二维表形式），能够使用SQL语句进行复杂的查询并且支持事务。但是随着发展，在巨大数据量下，单一的关系型数据模型难以应对数据种类复杂等情况。这时就需要用到NoSQL数据库了。
NoSQL 对 SQL 做出了很好的补充。在实际开发中，有很多业务需求，并不需要完整的关系型数据库功能，非关系型数据库的功能就足够使用了。这种情况下，使用性能更高、成本更低的非关系型数据库当然是更明智的选择。比如：日志收集、排行榜、定时器等。
NoSQL的优势及特点
-
易扩展：NoSQL数据库种类繁多，但是一个共同的特点都是去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。
-
大数据量，高性能：NoSQL数据库都具有非常高的读写性能，尤其在大数据量下，同样表现优秀。这得益于它的无关系性，数据库的结构简单。
-
灵活的数据模型：NoSQL无须事先为要存储的数据建立字段，随时可以存储自定义的数据格式。而在关系数据库里，增删字段是一件非常麻烦的事情。如果是非常大数据量的表，增加字段将会非常复杂且消耗时间。
-
高可用：NoSQL在不太影响性能的情况，就可以方便地实现高可用的架构。比如Cassandra、HBase模型，通过复制模型也能实现高可用。
NoSQL的分类
1.键值存储数据库（Redis）
键值型数据库通过 Key-Value 键值的方式来存储数据，Key 作为唯一的标识符
- 优点是查找速度快
- 缺点是无法像关系型数据库一样使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。
键值型数据库典型的使用场景是作为内存缓存
2.文档型数据库（MongoDB）
文档型数据库可存放并获取文档，可以是XML、JSON等格式。在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。
3.搜索引擎数据库（Elasticsearch）
关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”。
4.列存储数据库（HBase）
列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储，而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限。
5.图形数据库（Neo4J）
图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。图形数据库最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。如社交网络中人物之间的关系，如果用关系型数据库则非常复杂，用图形数据库将非常简单。
慢SQL
超过指定时间的SQL语句
慢查询：
超过指定时间的SQL语句的查询称为“慢查询”。
慢查询日志就是记录这些sql的日志。
为什么会出现慢SQL
- 写操作
-
刷脏页
在磁盘中，redo log 顺序存储在两个文件中，大小是一定的，且是循环写入的。在高并发场景下，redo log 很快被写满了，但是数据来不及同步到磁盘里，这时候就会产生脏页，并且还会阻塞后续的写入操作。SQL 执行自然会变慢 -
锁
写操作时 SQL 慢的另一种情况是可能遇到了锁，当某一条 SQL 所要更改的行刚好被加了锁，那么此时只有等锁释放了后才能进行后续操作。
-
补充：
脏页的定义是这样的：内存数据页和磁盘数据页不一致时，那么称这个内存数据页为脏页。
如何排查慢SQL
- 索引命中
通常用的方法是查看执行计划，方法也很简单，就是在所执行的 SQL 前加上 explain 就可以来分析当前 SQL 的执行计划，重要的几个字段包括type查询的方式，possible_keys可能的索引，key实际的索引，rows查找的行数，Extra其他重要信息，主要看有没有用覆盖索引Using index
- 刷脏页
对于刷脏页的情况，我们需要控制脏页的比例，不要让它经常接近 75%。同时还要控制 redo log 的写盘速度，并且通过设置 innodb_io_capacity 参数告诉 InnoDB 你的磁盘能力，从而避免脏页落盘阻塞产生的慢SQL问题
数据库多表联查
多表联合查询可以通过连接运算实现。
select
ams_admin.id,
ams_admin.username,
ams_admin.password,
ams_admin.nickname,
ams_role.id AS role_id,
ams_role.name AS role_name,
ams_role.description AS role_description,
ams_role.sort AS role_sort,
ams_role.gmt_create AS role_gmt_create,
ams_role.gmt_modified AS role_gmt_modified
from ams_admin
left join ams_admin_role on ams_admin.id=ams_admin_role.admin_id
left join ams_role on ams_admin_role.role_id=ams_role.id
where ams_admin.id=#{id}
存储引擎
1.MyISAM 存储引擎
主要特点：
- MySQL5.5 版本之前的默认存储引擎
- 支持表级锁（表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁）；
- 不支持事务，外键。
- 适用场景：对事务的完整性没有要求，或以 select、insert 为主的应用基本都可以选用 MYISAM。在 Web、数据仓库中应用广泛。
- 每个 myisam 在磁盘上存储为 3 个文件，文件名和表名相同，扩展名分别是
-
.frm -------存储表定义
-
.MYD --------MYData，存储数据
-
.MYI --------MYIndex，存储索引
-
2.InnoDB 存储引擎
主要特点：
- MySQL5.5 版本之后的默认存储引擎；
- 支持事务；
- 支持行级锁（行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁）；
- 支持聚集索引方式存储数据。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
