MySQL基本知识及常见问题总结

DCDDDDcccc

于 2024-08-13 10:00:00 发布

阅读量655

点赞数 12

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/mx132465/article/details/141124220

版权

==1、数据库引擎有哪些==

如何查看mysql提供的所有存储引擎
mysql> show engines;
通过使用命令来查看数据库默认使用的引擎：
SHOW VARIABLES LIKE '%storage_engine%';
mysql常用引擎包括：MYISAM、Innodb、Memory、MERGE

\1. MYISAM：

全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎

\2. Innodb:

行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些

\3. Memory:

全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表

\4. MERGE：

是一组MYISAM表的组合

==2、InnoDB与MyISAM的区别==

\1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
\2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
\3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
\4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
\5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高

==3、索引==

**索引（Index）是帮助 MySQL 高效获取数据的数据结构**。常见的查询算法,顺序查找,二分查找,二叉排序树查找,哈希散列法,分块查找,平衡多路搜索树 B 树（B-tree），索引是对数据库表中一个或多个列的值进行排序的结构，建立索引有助于快速获取信息。
你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库
mysql 有4种不同的索引：
主键索引（PRIMARY）
唯一索引（UNIQUE）
普通索引（INDEX）
全文索引（FULLTEXT）
索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引
索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度唯一索引可以确保每一行数据的唯一性
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能索引需要占物理和数据空间

==3、常见索引原则==

\1. 选择唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
\2. 为经常需要排序、分组和联合操作的字段建立索引。
\3. 为常用作为查询条件的字段建立索引。
\4. 限制索引的数目：越多的索引，会使更新表变得很浪费时间。尽量使用数据量少的索引
\5. 如果索引的值很长，那么查询的速度会受到影响。尽量使用前缀来索引
\6. 如果索引字段的值很长，最好使用值的前缀来索引。
\7. 删除不再使用或者很少使用的索引
\8. 最左前缀匹配原则，非常重要的原则。
\9. 尽量选择区分度高的列作为索引区分度的公式是表示字段不重复的比例
\10. 索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
\11. 尽量的扩展索引，不要新建索引

==4、数据库的三范式是什么==

第一范式：1NF是对属性的原子性，要求属性具有原子性，不可再分解；
第二范式：2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖；主键约束
第三范式：3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；外键约束
且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

==5、事务==

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。
事务是一个不可分割的工作逻辑单元事务必须具备以下四个属性，简称 ACID 属性：
原子性（Atomicity）
事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
一致性（Consistency）
当事务完成时，数据必须处于一致状态。
隔离性（Isolation）
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
持久性（Durability）
事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性

==6、并发事务带来哪些问题?==

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。
脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatable read）:指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）:幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了

==7、SQL优化==

1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union
all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null 值，然后这样查询： select id from t where num=0

==8、简单说一说drop、delete与truncate的区别==

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别
delete和truncate只删除表的数据不删除表的结构速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到
rollbacksegment中,不能回滚. 操作不触发trigger
面试题：delete 和 truncate 、drop 三者的区别delete 删除1.可以删除所有，也可以根据条件进行删除2.删除后，下次主键自增会从下一个自增值开始3.DML操作数据操作语言4.支持事务回滚5.delete 删除数据，会返回受影响的行数（JDBC操作）
truncate 删除1.只能删除所有，不可以根据条件删除数据2.删除数据后，下次主键自增会从起始值恢复使用3.不支持事务回滚4.不删除表结构5.truncate的效率比delete高6.TRUNCATE删除数据，不返回受影响的行数
drop 删除1.只能删除所有，不可以根据条件删除数据2.DDL操作数据定义语言3.不支持事务回滚4.删除表结构，释放表创建时所有资源

==9、简述在MySQL数据库中MyISAM和InnoDB的区别==

MyISAM：

不支持事务，但是每次查询都是原子的；支持表级锁，即每次操作是对整个表加锁；存储表的总行数；
一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb：

支持 ACID 的事务，支持事务的四种隔离级别；支持行级锁及外键约束：因此可以支持写并发；
不存储总行数：
一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+树结构，文件的大调整。

==10、MySQL中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？==

SQL 标准定义的四个隔离级别为：
1、read uncommited ：读到未提交数据
2、read committed：脏读，不可重复读
3、repeatable read：可重读
4、serializable ：串行事物

==11、你怎么看到为表格定义的所有索引？==

索引是通过以下方式为表格定义的：
SHOW INDEX FROM <tablename>;

==12、如何显示前50行？==

在 MySQL 中，使用以下代码查询显示前 50 行：
SELECT * FROM 表名 LIMIT 0,50;

==13、索引的底层实现原理和优化==

B+树，经过优化的 B+树，主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

==14、实践中如何优化MySQL==

最好是按照以下顺序优化：
1、SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化
详细可以查看阿里 P8 架构师谈：MySQL 慢查询优化、索引优化、以及表等优化总结

==15、数据库中的事务是什么?==

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。
事务特性：
1、原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
2、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态
3、隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事物
4、持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。
或者这样理解：
事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。

==16、对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题==

1)、索引的目的是什么？

快速访问数据表中的特定信息，提高检索速度。创建唯一性索引，保证数据库表中每一行数据的唯一性。加速表和表之间的连接。使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

2)、索引对数据库系统的负面影响是什么？

负面影响：创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

3)、为数据表建立索引的原则有哪些？

在最频繁使用的、用以缩小查询范围的字段上建立索引。
在频繁使用的、需要排序的字段上建立索引

4)、什么情况下不宜建立索引？

对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等==74、主键、外键和索引的区别？==
主键、外键和索引的区别
定义：
主键–唯一标识一条记录，不能有重复的，不允许为空
外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值索引–该字段没有重复值，但可以有一个空值
作用：
主键–用来保证数据完整性
外键–用来和其他表建立联系用的索引–是提高查询排序的速度
个数：
主键–主键只能有一个
外键–一个表可以有多个外键
索引–一个表可以有多个唯一索引

==17、说说对SQL语句优化有哪些方法？==

1、Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。
2、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。
3、避免在索引列上使用计算
4、避免在索引列上使用 IS NULL 和 IS NOT NULL
5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

==18、mysql有哪些常见的锁==

MySQL 常见的锁有以下几种：

1. 共享锁（Shared Lock，S 锁）：允许其他事务读取锁定的数据，但不能修改。例如，多个事务可以同时对一张表加共享锁来读取数据。
2. 排他锁（Exclusive Lock，X 锁）：阻止其他事务获取相同的锁，既不能读也不能写。常用于数据修改操作，保证数据的一致性和完整性。

在表级锁中：

1. 表共享读锁（Table Read Lock）：对整个表加共享锁，多个事务可以同时持有该锁来读取表中的数据。
2. 表独占写锁（Table Write Lock）：对整个表加排他锁，阻止其他事务对该表进行读和写操作。

在行级锁中：

1. 记录锁（Record Lock）：锁定表中的一行记录。
2. 间隙锁（Gap Lock）：用于锁定一个范围，但不包含记录本身，主要用于防止幻读。
3. 临键锁（Next-Key Lock）：它是记录锁和间隙锁的组合，既能锁住记录，又能锁住记录前面的间隙。

==19、mysql主从分布复制原理==

MySQL 主从复制的原理主要包含以下几个关键步骤：
1. 主库的二进制日志记录
o 主库在执行数据修改操作（如插入、更新、删除等）时，会将这些操作以事件的形式记录到二进制日志（Binary Log）中。这些事件包含了足够的信息，以便从库能够重现相同的操作。

2.从库与主库建立连接
o 从库通过一个专门的 I/O 线程与主库建立连接，并向主库发送请求，表明希望获取二进制日志的内容。
3. 主库发送二进制日志
o 主库接收到从库的请求后，会启动一个单独的线程将二进制日志中的事件发送给从库。
4. 从库接收并存储中继日志
o 从库的 I/O 线程接收到主库发送的二进制日志事件，并将其存储在本地的中继日志（Relay Log）中。
5. 从库的 SQL 线程应用中继日志
o 从库的 SQL 线程读取中继日志中的事件，并在从库上按照相同的顺序执行这些操作，从而实现数据的同步更新。

举例来说，假设主库中有一个表 students，执行了一条 INSERT INTO students VALUES (1, 'John') 的语句。这个操作会被记录到主库的二进制日志中。从库接收到这个二进制日志事件后，其 SQL 线程会在从库的 students 表中执行相同的插入操作，以确保从库的数据与主库保持一致。