Mysql基础知识

最新推荐文章于 2024-10-21 17:07:08 发布

原创最新推荐文章于 2024-10-21 17:07:08 发布 · 752 阅读

1 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

38 篇文章

订阅专栏

1. 索引的数据结构

B+ Tree

2. 查找、删除、插入如何操作

查找：首先在根节点进行二分查找，找到一个key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的data。

插入删除会破坏平衡树的平衡性，因此插入删除操作，需要对树进行分裂、合并、旋转等操作。

3. 为什么不用红黑树？

更少的查找次数
平衡树查找的时间复杂度与树高h相关，O(h) = O(logdN),d为每个节点的出度。而红黑树的是二叉树，那么会导致树的高度过高，而在数据库设计中，将一个节点定为一个页面置页换的大小，所以过多的节点查询会极大的消耗资源
利用磁盘预读特性
为了减少磁盘I/O操作，磁盘往往不会按需查取，根据局部性访问原理，每次访问都会预读，预读过程中按照顺序读取，顺序读取不需要进行磁盘寻道，只需要很短的旋转时间，速度非常快。
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

4. Mysql有哪些索引？

B+Tree 索引：大多数MYSQL存储引擎的默认索引类型。
适用于：全键值、键值范围和键前缀查找。键前缀查找只适用于最左前缀查找。
Innodb的B+Tree索引分为主索引和辅助索引。主索引的叶子节点data域记录完整的数据记录，这种索引方式称为聚簇索引，因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引记录叶子节点记录主键的值，通过主键的值，去主索引表中查找所需数据。
哈希索引：能够以O（1）的时间进行查找，失去了有序性
缺点：无法进行排序和分组；只支持精确查找，无法用于范围查找和部分查找。
注意：InnoDB 存储索引有一个特殊的功能：自适应哈希索引，当某个索引值被使用的非常频繁时，会在B+Tree索引上再创建一个hash索引，这样B+Tree索引具有哈希索引的一些优点，方便快速哈希查找。
全文索引：用于查找文本中的关键词，而不是直接比较是否相等。原理：倒排索引实现,记录关键词到所在文档的映射。
查找条件：MATCH AGAINST，不是普通的Where
Innodb引擎在5.6.4版本后开始支持全文索引
空间数据索引:MyiSAM引擎支持空间数据索引(R-Tree),用于存储地理数据

5. 索引的优点有哪些？（3）

加快查找速度，减少服务器需要扫描的数据行数
帮助服务器避免进行排序和分组,以及避免创建临时表(B+Tree索引是有序的，可以用于Order By和Group By操作)，临时表主要是在排序和分组的过程中创建，由于不需要排序和分子，也就不需要创建临时表
将随机I/O变为顺序I/O

6. 列举创建索引但是无法命中索引的8种情况

7. 什么时候去使用索引？（3）

对于非常小的表，大部分情况下简单的全表扫描比简历索引更高效
对于中到大型的表，索引就非常有效
对于特大型的表，建立和维护索引的代价会随之增长。此情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是用一条记录去匹配，例如分区技术

8. 如何进行查询优化？(3):分析+数据访问+查询方式

使用Explain进行分析，EXPLAIN用来分析SELECT查询语句，通过分析结果来优化查询语句。常见字段：select_type:查询类型，有简单查询、联合查询、子查询等。key:使用的索引；rows:扫描的行数
优化数据访问
2.1 减少请求的数据量：只返回必要的列（尽量不用select *），只返回必要的行(limit限制数据数目)；缓存重复查询的数据；
2.2 减少服务端扫描的行数（使用索引）
重构查询方式
3.1 切分大查询：一个大查询如果一次性执行的话，可能会锁住很多数据，占满整个事务日志、耗尽资源、阻塞很多小的但重要的查询。所以，可以通过for/while循环一次性查询一定的量。

DELETE FROM message WHERE create < DATA_SUB(NOW(), INTERVAL 3 MONTH);
↓
row_affected = 0
do{
    rows_affected = do_query("DELETE FROM message WHERE create < DATA_SUB(NOW(),INTERVALE 3 MONTH) LIMIT 10000")
}while rows_affected > 0

3.2 分解大连接查询：将连接查询分解成对每个单表的查询，在服务器后端进行关联。
优点：

缓存更加高效，对于连接查询，如果其中一个表发生变化，则整个查询缓存无法使用，分解后的多个查询，即使其中一个表发生改变，对于其他表的查询缓存依旧可以使用。
分解成单表后，这些单表查询的缓存，有可能被其他查询语句适用到。
减少锁竞争
在应用层进行连接，更容易对数据进行拆分，做到高性能和可伸缩

8. Mysql存储引擎有哪些？隔离级别分别是什么？

Innodb(默认)：支持事务，行级锁，外键约束，表加密
实现四个标准的隔离级别：默认级别可重复读（REPEATABLE READ)，在可重复读隔离级别下，通过多版本控制（MVCC)+间隙锁(Next-key locking)防止幻影读
主索引是聚簇索引，索引中保存了数据，从而避免直接读取磁盘。
支持真正的在线热备份。
MyISAM：不支持事务，不支持行级锁，只有表级锁，支持压缩表，空间数据索引等。
由于是表级锁，当读取时对需要读到的所有表加共享锁，写入时对所有表加排它锁，但在表有读取操作的同时，也可以向表中插入新的记录，被称为并发插入（CONCURRENT INSERT)
如果指定了DELAY_KEY_WRITE选项，每次修改执行完成后不会立即将修改的索引数据写入磁盘，而是写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作
Memory
Merge
ARCHIVE

9.如何进行索引优化？

独立的列：索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用。

SELECT actor_id FROM db.actor where actor_id + 1 = 5
//无法使用索引

多列索引(联合索引)：当需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。

SELECT film_id, actor_id FROM film_actor WHERE actor_id = 1 AND film_id = 1
//建议使用多列索引

索引列的顺序：让选择性最强的索引列放在前面

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_select,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_select,
COUNT(*)
FROM payment

//结果：
  staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

所用选择customer_id列放在多列索引的前面

前缀索引:对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。对于前缀长度的选取需要根据索引选择性来确定。

索引包含所有需要查询的字段的值。

具有以下优点：

索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

10. 数据库的切分有哪些？

数据库的切分包括：水平切分和垂直切分

水平切分：sharding,将同一个表中的记录拆分到多个结构相同的表中。

当一个表中数据不断增多时，水平分表是必然的选择，可以将数据分布到集群的不同节点上，从而缓解单个数据库的压力

垂直切分：将一张表列分成多个表，通常按照列的关系密集程度进行切分，也可以利用垂直切分将经常使用的列和不经常使用的列切分到不同的表中

在数据库的层面使用垂直切分将数据库中表的紧密程度部署到不同的库中，例如将电商数据垂直切分数据库、用户数据库等。

11.水平切分Shard策略以及存在的问题

11.1 策略

哈希取模：hash(key)%N
范围：ID范围或者是时间范围
映射表：使用单独的一个数据库来存储映射关系

11.2 存在的问题

事务问题：使用分布式事务来解决
连接：将原来的表连接分解成多个单表查询，在用户程序中进行连接
ID 唯一性：

使用全局唯一ID（GUID)
为每一个分片指定一个ID范围
分布式ID生成器

12. 数据库的主从复制和读写分离？

大多数的业务都是读多写少，为了缓解单个数据库压力，减少数据库锁的使用消耗，提出了读写分离的业务实现方法，实现技术为主从复制。也就是写一个主库，主库挂多个从库，从库用来读。

12.1 主从复制的原理是什么？

要想实现主从复制，主库一定要开启binlog二进制日志;
主库I/O线程将变更写入binlog日志
然后从库(slave)I/O线程连接到主库之后，将主库的binlog日志拷贝到自己的本地，同时更新master_info信息（也就是用于版本同步，保证数据一致。），接着写入relay中继日志，紧接着从库SQL线程从中继日志读取binlog，执行binlog日志中的内容，在自己本地执行一次sql语句，实现主从复制。

这里有一个非常重要的一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行的操作，在从库上会串行执行。所以这就是一个非常重要的点了，由于从库从主库拷贝日志以及串行执行 SQL 的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。

而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。

所以 MySQL 实际上在这一块有两个机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。

这个所谓半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
所谓并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

12.2 读写分离：主服务器处理写操作以及实时性要求比较高的读操作，从服务器处理读操作。

提高性能的原因：

主从服务器负责各自的读和写，极大程度缓解了锁的争用
从服务器可以使用MyISAM，提升查询性能以及节约系统开销。
增加冗余，提高可用性

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

11. 简述数据库三大范式

第一范式（1NF）：属性不可分
第二范式（2NF）：每个非主属性完全函数依赖与键码
第三范式（3NF）：非主属性不传递函数依赖于键码

什么是事务？Mysql如何支持事务
简述触发器、函数、视图、存储过程
Mysql常见函数
如何开启慢日志查询
数据库导出命令
数据库优化方案
char和varchar的区别，以及varchar(50)50代表的涵义
Mysql的复制原理以及流程

20. MySQL中myisam与innodb的区别

事务：Innodb事务型，可以使用Commit和Rollback语句
并发：MyISAM只支持表级锁，而Innodb支持行级锁
外键：Innodb支持外键
备份：InnoDB支持在线热备份
崩溃恢复：MyISAM 崩溃后发生损坏的几率比Innodb高很多，恢复速度也慢
select count(*) Myisam比Innodb快，因为它有计数器。

其他特性：MyISAM 支持压缩表和空间数据索引

21. 表中有大字段X(例如：text类型)，且字段X不会经常更新，以读为为主，请问

(1)、您是选择拆成子表，还是继续放一起；
(2)、写出您这样选择的理由。

拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；
如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗
如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

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

InnoDB引擎是基于索引来完成行锁。

select * from table where id = 1 for update;  
//这条语句实现了行级锁的排它锁，事务中。锁定此条数据。

for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,
如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起

一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

主键超键候选键外键
视图的作用，视图可以更改么？
drop,delete与truncate的区别
DELETE命令从一个表中删除某一行，或多行，TRUNCATE命令永久地从表中删除每一行。
连接的种类
什么是触发器，MySQL中都有哪些触发器？
触发器是指一段代码，当触发某个事件时，自动执行这些代码。在MySQL数据库中有如下六种触发器：

1、Before Insert

2、After Insert

3、Before Update

4、After Update

5、Before Delete

6、After Delete
29. https://juejin.im/entry/5b57ec015188251aa8292a69
30. https://blog.youkuaiyun.com/miaoqinian/article/details/80787592
31. https://www.cnblogs.com/frankielf0921/p/5930743.html
32. https://www.cnblogs.com/Curry-Coder/p/6810757.html
33. https://blog.youkuaiyun.com/waveclouds/article/details/79535685