MySQL专题

最新推荐文章于 2024-07-25 16:06:56 发布

木子小猿

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量38

点赞数

分类专栏： JAVA面试专栏文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/u010800359/article/details/132542280

版权

JAVA面试专栏专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL、SQL Server、Oracle的区别
- SQL Server只支持windows系统，MySQL和Oracle支持不同系统之间的移植
- MySQL开源免费，SQL Server和Oracle收费
- MySQL很小，SQL Server居中，Oracle最大
- Oracle支持大并发量，大访问量，SQL Server还行，而MySQL支持并发没那么高，因此现在的MySQL的话最好是要使用集群或者缓存来搭配使用，单机MySQL的QPS一般在4K左右
- Oracle没有自动增长类型，MySQL和SQL Server一般使用自动增长类型
MySQL区分大小写吗
- MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写的
- 阿里规范中，库名、表名、字段名都小写
- Linux系统下：show variables like ‘lower%’;
  - lower_case_file_system = OFF，代表当前系统文件是否大小写敏感，只读参数，无法修改。ON 大小写不敏感，OFF 大小写敏感
  - lower_case_table_names，代表库表名是否大小写敏感，可以修改，参数有0、1、2三种；0 大小写敏感，1和2是不敏感
数据库三大范式
- 第一范式：每个列都不可再分
- 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分（course_id、uid、uname）
- 第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键（uid、memberId、member）
MySQL的底层架构
- 客户端
- server层：连接器、缓存、分析器、优化器、执行器
- 存储引擎：可插拔式，MyISAM、InnoDB、Memory
MySQL的binlog
- binlog以事件形式记录了所有的DDL和DML语句(除了select)，包括语句执行时间；一般来说开启binlog日志大概会有1%的性能损耗
- binlog日志有两个最重要的使用场景
  - mysql主从复制
  - 数据恢复(binlog日志包括两类文件：日志索引文件.index和日志文件.00000*)
- 如何查看binlog日志
  - 只能使用mysqlbinlog命令查看
- MySQL的binlog有几种录入格式
  - statement模式：每一条会修改数据的sql都会记录在binlog中；不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能；由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制
  - row模式：不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大
  - mixed模式：一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row
MySQL的MyISAM和InnoDB引擎的区别
- 事务支持：InnoDB 支持事务，MyISAM 不支持事务
- 存储结构：InnoDB存储一个文件，MyISAM存储三个文件
- 锁：InnoDB支持表锁、行锁，MyISAM只支持表锁(并发写性能差)
  - InnoDB行锁是针对索引字段加锁；MyISAM是针对非索引字段加锁
- 主键、外键：InnoDB必须有主键，MyISAM允许没有主键；InnoDB支持外键，MyISAM不支持外键
- 表的行数：InnoDB没有存储表总行数，只能实时遍历，MyISAM缓存了表总行数
- 应用场景： InnoDB支持事务、行锁、外键，适合高并发和数据安全性比较高的场景；MyISAM提供高效存储和查询，适合读多和数据安全性较低的场景
索引的使用场景
- 当数据多且字段值有相同的值得时候用普通索引
- 当字段多且字段值没有重复的时候用唯一索引
- 当有多个字段名都经常被查询的话用复合索引；区分度高的字段放前面，可以减少查询次数，更快的匹配到所需数据
- 索引下推(复合索引)：可以减少回表查询次数，提高查询效率
- 表增删改多而查询较少的话，就不要创建索引了
- 更新太频繁地字段不适合创建索引
- 不会出现在where条件中的字段不该建立索引
主键索引和唯一索引的区别
- 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的
- 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键
- 唯一性索引列允许空值，而主键列不允许为空值
- 一个表最多只能创建一个主键，但可以创建多个唯一索引
- 主键可以被其他表引用为外键，而唯一索引不能
索引的数据结构
- 索引的数据结构和具体存储引擎的实现有关：InnoDB存储引擎的默认索引实现为B+树索引
- Hash索引：hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据；hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询
- B+树索引：B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据；B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围
- InnoDB选用B+树而不选用B树的原因：
  - B树只适合随机检索，而B+树同时支持随机检索和顺序检索
  - B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低：B树的每个节点中不仅包含数据的key值，还有data值；而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率；B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+树的高度
  - B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当
索引的基本原理
- 索引的原理很简单，就是把无序的数据变成有序的查询
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
百万级别或以上的数据如何删除
- 索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加、修改、删除，都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执行效率
- 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
- 然后删除其中无用数据（此过程需要不到两分钟）
- 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右
聚簇索引和非聚簇索引
- 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
  - 在InnoDB中，只有主键索引是聚簇索引，也就是说，如果通过主键进行查询数据，则不需要回表查询数据，叶子结点上就包含行数据所有信息
  - 如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引
- 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行
- 非聚簇索引一定会回表查询吗？不一定，查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询
MySQL事务四大特性
- A：原子性，undo log实现
  - undo log：记录的是逻辑日志，也就是SQL语句；比如：当我们执行一条insert语句时，Undo Log就记录一条相反的delete语句
  - 作用：回滚事务时，恢复到修改前的数据；实现MVCC多版本并发控制
- C：一致性，一致性是由其他三大特征保证，程序代码要保证业务上的一致性
- I：隔离性，由MVCC实现，MVCC如何实现的???
- D：持久性，由redo log实现
  - redo log：记录的是物理日志，也就是磁盘数据页的修改
  - 作用：用来保证服务崩溃后，仍能把事务中变更的数据持久化到磁盘上
什么是脏读？不可重复读？幻读？
- 脏读：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的；【根本原因就是读到了未提交的数据】
- 不可重复读：在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据【根本原因就是一个事务结束前被别的事务篡改了数据】
- 幻读：在一个事务的两次查询中数据条数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的
什么是事务隔离级别？MySQL的默认隔离级别？
- 事务的四种隔离级别：用于保证MySQL在并发环境下保证ACID的策略
  - 读未提交：最低的隔离级别，允许读取尚未提交的数据变更；解决不了任何问题
  - 读已提交：允许读取并发事务已经提交的数据；解决脏读
  - 可重复读（MySQL默认）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改；解决脏读、不可重复读
  - 串行化：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行；解决脏读、不可重复读、幻读
- 注意：
  - 隔离级别越低，事务请求的锁越少
MySQL隔离级别和锁的关系
- 在读未提交级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
- 在读已提交级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁
- 在可重复读级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁
- SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成
MySQL中InnoDB引擎的行锁是怎么实现的？
- InnoDB是基于索引来完成行锁
- select * from tab_with_index where id = 1 for update;
- for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起
什么是死锁？怎么解决？
- 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象
- 常见的解决死锁的方法
  - 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会
  - 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
  - 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率
  - 如果业务不好处理,可以用分布式事务锁或者使用乐观锁
MySQL的并发控制
- 并发控制的目的，是确保多个事务同时增删改查同一数据时，不破坏事务的ACID特性
- 没有做好并发控制，就可能导致脏读、不可重读和幻读等问题
- 实现并发控制的主要手段分为乐观并发控制和悲观并发控制两种
  - 悲观锁：当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，直接对该数据进行加锁以防止并发；悲观锁分为共享锁和排他锁
    - 共享锁：当用户要进行数据的读取时，对数据加上共享锁；共享锁就是让多个线程同时获取一个锁；select * from tb_user lock in share mode;作用：防止其他事务修改当前数据；可以实现多个事务并发读取相同的数据，提高并发性能，并确保数据的一致性
    - 排他锁：当用户要进行数据的写入时，对数据加上排他锁；排它锁也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁;select * from tb_user for update;作用：防止其他事务读取或者更新当前数据
  - 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性；在修改数据的时候把事务锁起来，通过version的方式来进行锁定；实现方式：一般会使用版本号机制或CAS算法实现
    - 版本号机制：//核心SQL update table set x=x+1, version=version+1 where id=#{id} and version=#{version};
  - 使用场景：悲观锁用于多写的场景，一般经常会产生冲突；乐观锁用于少写的场景，即冲突真的很少发生的时候，可以省去锁的开销
SQL语句的语法顺序
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- UNION
- ORDER BY
- LIMIT
如何定位SQL语句性能问题
- 使用执行计划explain，显示数据库引擎对于SQL语句的执行详细情况，包含是否使用索引、使用什么索引、使用的索引的相关信息等
- 分析执行计划：
  - id、select_type：id表示子查询的执行顺序，值越大优先级越高，越先被执行；select_type每个子查询的查询类型，一般包含SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION/UNION RESULT
  - type：访问类型，ALL 扫描全表数据；index 遍历索引；range 索引范围查找；index_subquery 在子查询中使用 ref；unique_subquery 在子查询中使用 eq_ref；ref_or_null 对Null进行索引的优化的 ref；fulltext 使用全文索引；ref 使用非唯一索引查找数据；eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联
  - possible_keys：可能使用的索引，不一定会使用，如果这一列为NULL，就要考虑SQL是否需要优化了
  - key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
  - key_length：索引长度
  - rows：返回估算的结果集数目，并不是一个准确的值
  - extra：Using index 使用覆盖索引；Using where 使用了用where子句来过滤结果集；Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化；Using temporary 使用了临时表
- 【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。
  - consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据
  - ref 指的是使用普通的索引（normal index）
  - range 对索引进行范围检索
  - 反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫
超大分页怎么处理
- 数据库层面：LIMIT偏移量大的时候，查询效率较低，可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
  - select * from table where age > 20 limit 1000000,10修改为
  - 方案一：使用子查询查出符合条件的主键，再用主键ID做条件查出所有字段：select * from table where id in (select id from table where age > 20 limit 1000000,10)
  - 方案二：使用分页游标：如果ID连续的话，那可以直接select * from table where id > 1000000 limit 10
  - 核心思想就是减少load的数据和尽量避免回表查询
- 缓存层面：预先缓存
慢查询日志
- 用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考
- 开启慢查询日志：配置项slow_query_log
  - set GLOBAL slow_query_log = on
  - 会在datadir下产生一个xxx-slow.log的文件
- 查看：show VARIABLES like 'long_query_time'，单位秒
  - 设置：set long_query_time=0.5
关注过业务系统里面的sql耗时吗？，统计过慢查询吗？对慢查询怎么优化？
- 在业务系统中，一般都是在性能环境上测试SQL耗时
- 慢查询的统计一般都是运维在做，会定期将业务中的慢查询反馈给我们
- 慢查询优化
  - 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写
  - 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引
  - 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表
SQL语句优化
- 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
- 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
- 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
- 应尽量避免在 where 子句中使用or 来连接条件（改用union all），否则将导致引擎放弃使用索引而进行全表扫描
- in 和 not in 也要慎用，否则会导致全表扫描
- 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
- 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
如何确定mysql数据库的最大连接数max_connections
- 通常，mysql的最大连接数默认是100，最大可以达到16384
- 如果我们设置的连接数太多，由于MySQL会为每一个连接提供缓冲区，会造成过多的内存开销
- 一般可以通过查询服务器响应的最大连接数：show global status like ‘Max_used_connections’;
  - 比较理想的设置是：Max_used_connections/max_connections = 85%
- MySQL为每一个连接分配线程来处理，所以也可以通过查询线程连接数：SHOW STATUS LIKE ‘Threads_connected’;
mysql的主备复制原理
- 是一个异步的复制过程，底层是基于Mysql数据库自带的二进制日志binlog功能
- 二进制日志包括数据定义语言DDL和数据操纵语言DML
- 复制过程分三步
  - MySQL主库：将数据变更写入二进制日志（binary log）
  - 从库的IO线程：将master的binary log拷贝到它的中继日志（relay log）
  - 从库的SQL线程：重做中继日志中的事件，将数据更新到它的数据上
mysql主从结构，主挂了，从库没法从主库同步数据怎么办？
- 半同步复制mysql semi-sync：解决数据丢失的问题
- 备份主库数据，并在从库上恢复
cannal
- Canal是基于MySQL二进制日志的高性能数据同步系统
- 基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费
- 原理
  - canal 模拟 MySQL slave 的交互协议，伪装自己为 MySQL slave ，向 MySQL master 发送dump 协议
  - MySQL master 收到 dump 请求，开始推送 binary log 给 slave (即 canal )；
  - canal 解析 binary log 对象(原始为 byte 流）
数据库优化
- 表结构优化：尽量使得每条记录占用空间量最小
  - 数字类型：不要使用DOUBLE和DECIMAL，建议乘以相应倍数，转换成整数存储，可以节省存储空间
  - 字符类型：定长字段，建议使用 CHAR 类型（char查询快，但是耗存储空间，可用于用户名、密码等长度变化不大的字段），不定长字段尽量使用 VARCHAR（varchar查询相对慢一些但是节省存储空间，可用于评论等长度变化大的字段），且仅仅设定适当的最大长度
  - 时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要DATETIME 类型的一半
- SQL语句和索引优化
  - load数据尽量是按需select
  - 索引的合理使用以及索引失效问题排查
  - 连表、子查询合理使用
  - 小表驱动大表
- 数据分区、分表、分库：垂直分表，水平分库
- 数据库架构优化：负载均衡、读写分离
给你一个SQL，如何做优化
- 先看一下涉及到的几张表的数据量
- 再看原SQL的执行时间
- 再从执行计划入手，看type(ALL全表扫)、key、rows
- 观看执行计划和SQL，确定优化方案
  - 分析导致索引失效、走全表的原因：存在隐式转换、关联字段建索引

触发器

触发器可以在数据库表进行Update(增、删、改)操作的之前或之后，触发sql语句的执行

创建触发器语法

DELIMITER $$

    CREATE TRIGGER trigger_name 
    	BEFORE/AFTER INSERT/UPDATE/DELETE
        ON table_name
        FOR EACH ROW 
		BEGIN
    		/*触发SQL语句*/
        END$$

    DELIMITER ;

查询触发器：SHOW TRIGGERS;

存储过程
- 存储过程是事先编译并存储于数据库的sql语句集合
  - 创建存储过程语法
```
DELIMITER $$

CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
	BEGIN
		/*存储过程逻辑*/
	END$$

DELIMITER ;
```
- 调用存储过程：call procedure_name;
函数
- 函数是事先编译并存储于数据库的sql语句集合；函数分为自定义函数和内置函数
- 创建函数语法：
```
DELIMITER $$

CREATE
    FUNCTION function_name()
    RETURNS TYPE
    BEGIN
	/*函数体*/
    END$$

DELIMITER ;
```
- 调用函数：select function_name;
存储过程和函数的区别
- 存储过程功能更强大，可以实现一些复杂的业务逻辑，函数的话就有很多限制，比如不能在函数中使用insert/update/delete/create等语句
- 函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强
- 存储过程可以调用函数，但是函数不能调用存储过程
- 存储过程一般是作为一个独立的部分来执行(call调用)；而函数可以作为查询语句的一个部分来调用

木子小猿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL专题

B+树索引：B+树底层实现是多路平衡查找树。Hash索引：hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据；使用执行计划explain，显示数据库引擎对于SQL语句的执行详细情况，包含是否使用索引、使用什么索引、使用的索引的相关信息等。不一定，查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。数据库层面：LIMIT偏移量大的时候，查询效率较低，可以记录上次查询的最大ID，下次查询时直接根据该ID来查询。
复制链接

扫一扫

专栏目录