文章目录1.基础1.SQL分类2.关系型数据库和非关系型数据库的区别数据库的三范式是什么MySQL 支持哪些存储引擎?MySQL 的正则语法 (8.0)使用子查询和Join哪个效率更高CHAR 和 VARCHAR 区别CHAR 和 VARCHAR 如何选择CHARVARCHAR 和 Text 的区别MySQL中DATETIME 和 TIMESTAMP有什么区别分点对比实际场景选择建议高级面试问题准备面试中常用的对比表格常见误区澄清加分项源码层面的理解MySQL中union all和union的区别COUNT(*)、COUNT(1)、COUNT(列名) 有什么区别limit后面可以跟几个参数代表什么含义什么是内连接、左外链接和右外链接drop、delete与truncate的区别Mysql中in和exists的区别获取当前日期及格式化输出binlog、redolog、undolog 分别有什么作用和区别binlog二进制日志Redo Log重做日志Undo Log撤销日志2.事务什么是事务事务的四大特性(隔离级别)ACID并发事务带来的问题(脏读、不可重复读以及幻读)怎么解决3.索引3.1 什么是索引MySQL 索引类型有哪些主键与索引有什么区别什么时候不要使用索引索引的优缺点索引在什么情况下会失效3.2 Btree索引跟Hash索引的区别为何使用 B 树而非二叉查找树做索引为何使用 B 树而非 B 树做索引4.存储存储引擎有哪些InnoDB是如何解决幻读的sql优化以及慢查询排查MySQL查看SQL语句执行效率的SQL语句项目中遇到慢SQL查询你是怎么排查和解决的慢sql如何优化1.基础1.SQL分类1DDL(Data Definition Language)数据库定义语言create drop alter对表结构的增删改2DML(Data Manipulation Language)数据库操作语言用来对数据库表中的数据进行增删改。关键字:insert,delete,update等3DQL(Data Query Language)数据查询语言用来查询数据表中的记录(数据) ,关键字selectwhere等4DCL(Data Control Language)数据控制语言grant授权、revoke撤销权限等。5TCL(事务控制语言commit提交事务rollback回滚事务(TCL中的T是Transaction)2.关系型数据库和非关系型数据库的区别1关系型数据库都是用表来进行维护所以格式一致可以统一用SQL语言来进行操作2关系型数据库都是表结构所以灵活度不够操作复杂的海量数据性能比较差3虽然性能可能会比较慢但是能做复杂的关联查询操作。比如一对一、一对多等。数据库的三范式是什么1第一范式1NF①原子性每个字段都应该是不可再分的最小数据单元。例如不能将“电话号码”字段设计为存储多个电话如“13800138000,13900139000”而应该拆分为多行或单独字段。②每列数据类型一致同一列中的所有值必须是相同类型。③消除重复组不允许出现多值字段或数组。2第二范式2NF是建立在第一范式之上的它要求表里的非主键列必须完全依赖于整个主键而不是主键的一部分。①不符合2NF选课表(学号, 课程号, 课程名称, 成绩)主键是(学号, 课程号)。课程名称 仅依赖于 课程号主键的一部分而不是整个主键。②解决方案拆分为选课表(学号, 课程号, 成绩) 和 课程表(课程号, 课程名称)。3第三范式3NF在满足2NF的基础上消除非主键字段之间的传递依赖。直接依赖非主键字段必须直接依赖于主键而不是通过其他非主键字段间接依赖。即不能存在 A → B → C 的传递关系其中A是主键B和C是非主键。①不符合3NF学生表(学号, 姓名, 学院, 学院地址)。学号 → 学院 → 学院地址这里“学院地址”传递依赖于学号。②解决方案拆分为学生表(学号, 姓名, 学院) 和 学院表(学院, 学院地址)。MySQL 支持哪些存储引擎?一、MySQL支持的主要存储引擎有1InnoDBMySQL 5.5后的默认引擎2MyISAM传统默认引擎现逐渐淘汰3Memory/HEAP内存引擎4Archive归档引擎5CSVCSV文件引擎6Blackhole、Federated等特殊用途引擎二、核心区别对比从五个关键维度来对比最常用的InnoDB和MyISAM1事务支持①InnoDB完整支持ACID事务有提交、回滚机制②MyISAM不支持事务只有表级锁定2并发控制①InnoDB行级锁 MVCC多版本并发控制示例用户A更新id1的行时用户B可同时更新id2的行②MyISAM表级锁示例任何写操作都会锁定整张表阻塞其他所有操作3数据安全InnoDB支持崩溃后的自动恢复有redo/undo日志保证数据一致性MyISAM崩溃后容易数据损坏恢复需要repair table操作外键约束InnoDB支持外键和级联操作MyISAM不支持外键需要在应用层维护存储结构sql– InnoDB聚簇索引数据在主键索引的叶子节点上CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20)) ENGINEInnoDB;– 物理存储id1的数据和索引存储在一起– MyISAM堆表结构数据与索引分离CREATE TABLE t2 (id INT PRIMARY KEY, name VARCHAR(20)) ENGINEMyISAM;– 物理存储.MYD数据文件和 .MYI索引文件分开三、其他引擎特点Memory引擎数据存于内存重启丢失哈希索引默认查询极快适用会话表、临时计算结果缓存Archive引擎高压缩比比MyISAM小75%只支持插入和查询不支持更新/删除适用日志归档、审计数据CSV引擎数据存为CSV文件可直接用Excel打开不支持索引适用数据导入导出中转四、面试常考的场景题场景1电商订单系统问题应该选择哪个存储引擎回答text必须选择InnoDB原因有三订单需要事务保证如支付扣款和库存减少必须原子性高并发下单需要行级锁支持需要外键保证数据完整性如用户删除时订单处理场景2新闻网站文章表问题读多写少是否可以用MyISAM回答text技术上可以但不推荐。理由MyISAM的表锁在少量写时也会阻塞所有读现代InnoDB的读性能已足够好MyISAM崩溃恢复风险大建议使用InnoDB通过读写分离、缓存优化读性能场景3用户会话存储问题如何选择回答text考虑两个方案方案1Memory引擎优点极快查询缺点重启丢失会话内存有限方案2InnoDB Redis缓存优点持久化 快速读取缺点架构稍复杂根据业务决定对登录态要求不高可选Memory要求可靠则选方案2。五、我的实践经验在实际项目中我遵循以下原则默认使用InnoDB除非有特殊需求迁移MyISAM到InnoDB时注意sql– 1. 先检查外键依赖– 2. 业务低峰期执行ALTER TABLE old_table ENGINEInnoDB;– 3. 调整InnoDB参数SET GLOBAL innodb_buffer_pool_size 8G;混合引擎注意事项跨引擎事务不支持备份策略需要区分复制配置要一致六、面试加分点了解版本演进MySQL 5.1MyISAM为主MySQL 5.5InnoDB成为默认MySQL 8.0InnoDB功能更完善MyISAM逐渐淘汰性能优化差异InnoDB优化innodb_buffer_pool_sizeMyISAM优化key_buffer_size监控指标不同sql– InnoDB监控SHOW ENGINE INNODB STATUS\G– MyISAM监控SHOW TABLE STATUS LIKE ‘table_name’\GMySQL 的正则语法 (8.0)1基本语法 WHERE 列名 REGEXP ‘正则表达式’2常用元字符· . 匹配任意单个字符。· ^ 匹配字符串开头。· $ 匹配字符串结尾。· * 匹配前一个字符 0 次或多次。· 匹配前一个字符 1 次或多次。· ? 匹配前一个字符 0 次或 1 次。· [abc] 匹配 a、b、c 中的任意一个。· [a-z] 匹配小写字母。· [^abc] 匹配除 a、b、c 之外的任意字符。· | 或例如a|b 匹配 a 或 b。· () 分组。使用子查询和Join哪个效率更高取决于数据库管理系统的优化器实现以及数据分布(如数据量 索引表结构)CHAR 和 VARCHAR 区别1char和varchar最主要的区别在于存储方式。char是定长的比如你定义了char(10)那就算你只存了一个字符’a’它在磁盘上也会占用10个字符的空间剩下的会用空格补齐。而varchar是变长的它会根据你实际存储内容的长度来分配空间同时还需要额外1到2个字节来记录内容的实际长度。从性能上讲因为char是定长的处理起来会比varchar稍微快一点。所以在选择时如果一个字段的长度是完全固定的比如像MD5加密后的密码32位、或者手机号这种用char会更合适。但对于大多数长度不确定的字段比如用户名、地址等用varchar会更节省存储空间。2再者在存储方式上CHAR 对英文字符ASCII占用 1 字节对一个汉字使用用 2 字节。而 VARCHAR 对每个字符均使用 2 字节。虽然 VARCHAR 是根据字符串长度分配存储空间的但在内存中依旧使用声明长度进行排序等作业故在使用时仍需综合考量字段长度。CHAR 和 VARCHAR 如何选择1从原则上来讲将来字符串长度不固定的话选择varchar类型字符串长度固定不变则选择char类型2实际上我们生产中在考虑性能问题的方面需要有大量插入insert操作的应用中我们可以考虑使用char去代替varchar。3如果我们业务中大量是查询类操作的应用中数据量级又比较大情况下变长长度数据类型可以考虑采用varchar可以节省空间并有效的减少索引树的高度CHARVARCHAR 和 Text 的区别1长度区别①Char 范围是 0255。②Varchar 最长是 64k注意这里的 64k 是整个 row 的长度要考虑到其它的 column还有如果存在 not null 的时候也会占用一位对不同的字符集有效长度还不一样比如 utf-8 的最多 21845还要除去别的column但 Varchar 在一般情况下存储都够用了。③如果遇到了大文本考虑使用 Text最大能到 4G2效率区别效率来说基本是 Char Varchar Text3默认值区别Char 和 Varchar 支持设置默认值而 Text 不能指定默认值。MySQL中DATETIME 和 TIMESTAMP有什么区别DATETIME存储的是字面时间TIMESTAMP存储的是时间戳UTC秒数分点对比1时间范围不同-- DATETIME时间范围公元1000年到9999年1000-01-01 00:00:00~9999-12-31 23:59:59-- TIMESTAMP时间范围1970年到2038年2038年问题1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC“TIMESTAMP有著名的2038年问题Y2K问题一样存储范围有限DATETIME则没有这个问题。”2时区处理方式不同-- DATETIME存储输入的字面值与时区无关-- 存入 2024-01-01 12:00:00永远显示这个值-- TIMESTAMP存储UTC时间戳显示时转换-- 存入后转换为UTC存储查询时按连接时区显示“这是最关键的区别。TIMESTAMP在存入时会从当前时区转换为UTC存储查询时再转回当前时区显示。DATETIME则直接存储原始值不涉及时区转换。”3存储空间不同-- MySQL 5.6.4之前-- DATETIME: 8字节-- TIMESTAMP: 4字节-- MySQL 5.6.4支持小数秒-- DATETIME(0): 5字节-- TIMESTAMP(0): 4字节-- 每增加2位小数精度增加1字节“TIMESTAMP在存储空间上更有优势特别是对于需要创建索引的列索引会更小。”4自动更新特性-- TIMESTAMP支持自动更新CREATETABLEt1(updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);-- DATETIME在MySQL 5.6.5前不支持自动更新“TIMESTAMP支持ON UPDATE CURRENT_TIMESTAMP自动更新时间常用于记录最后修改时间。DATETIME在旧版本中不支持这个特性。”实际场景选择建议1场景1选择DATETIME的情况-- 生日、历史日期可能早于1970年birthdayDATETIMENOTNULL,-- 预约时间、航班时间固定时间点appointment_timeDATETIME,-- 超过2038年的时间expire_dateDATETIMENOTNULL“当需要存储历史日期如用户生日、未来很远的时间或者时间点本身是业务数据如会议时间时应该用DATETIME。”2场景2选择TIMESTAMP的情况-- 记录创建/修改时间created_atTIMESTAMPDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,-- 国际化应用用户在不同时区查看last_loginTIMESTAMP,-- 日志时间log_timeTIMESTAMP“对于系统自动生成的时间如创建时间、最后登录时间或者需要跨时区显示的应用应该用TIMESTAMP。”高级面试问题准备1关于2038年问题面试官可能问“怎么解决TIMESTAMP的2038年问题”回答要点1.“升级到MySQL 8.0TIMESTAMP支持到’2038-01-19 03:14:07’”2.“对于更远的时间改用DATETIME”3.“或者用BIGINT存储Unix时间戳以秒或毫秒为单位”2时区相关的问题面试官可能问“如果你的应用要国际化部署怎么设计时间字段”回答要点-- 方案1统一使用UTC时区SETtime_zone00:00;-- 所有TIMESTAMP自动以UTC存储-- 方案2存储用户时区时间CREATETABLEevents(event_timeDATETIME,-- 用户本地时间timezoneVARCHAR(50),-- 时区信息created_utcTIMESTAMP-- 系统UTC时间);3性能对比面试官可能问“为什么说TIMESTAMP性能更好”回答要点1.“存储空间小4字节 vs 5-8字节”2.“索引更小B树更浅查询更快”3.“但对于百万级数据差异不会特别明显”面试中常用的对比表格对比维度DATETIMETIMESTAMP存储内容字面时间UTC时间戳时间范围1000-9999年1970-2038年存储空间5-8字节4字节时区影响无自动转换自动更新5.6.5支持支持适用场景业务时间系统时间常见误区澄清1误区1“TIMESTAMP性能一定比DATETIME好”澄清“对于小表或很少按时间查询的表差异可以忽略。只有在大数据量且频繁按时间范围查询时TIMESTAMP的索引优势才明显。”2误区2“DATETIME不能有默认值”澄清“MySQL 5.6.5之后DATETIME也支持DEFAULT CURRENT_TIMESTAMP了。”3误区3“应该全部用TIMESTAMP因为更先进”澄清“这不是先进与否的问题而是适用场景不同。就像不能用螺丝刀敲钉子一样要根据需求选择。”加分项源码层面的理解如果面试官深入问可以提到1.底层存储TIMESTAMP32位整数4字节存储UTC秒数DATETIME打包的二进制格式2.转换开销TIMESTAMP存入和读取都有时区转换DATETIME直接存储无转换3.NULL处理TIMESTAMP列默认NOT NULL插入NULL会变成当前时间DATETIME列默认可为NULLMySQL中union all和union的区别1union和union all关键字都是将两个select语句的结果作为一个整体显示出来2Union对两个结果集进行并集操作不包括重复行同时进行默认规则的排序。3Union All对两个结果集进行并集操作包括重复行不进行排序。COUNT(*)、COUNT(1)、COUNT(列名) 有什么区别limit后面可以跟几个参数代表什么含义1跟两个参数limit 开始的索引,每页查询的条数1跟一个参数limit 5检索前5条数据什么是内连接、左外链接和右外链接1内连接①假设A和B表进行连接使用内连接的话凡是A表和B表能够匹配上的记录就会查询出来这就是内连接。②AB两张表没有主副之分两张表是平等的。2外连接①假设A和B表进行连接使用外连接的话AB两张表中有一张表是主表一张表是副表②主要查询主表中的数据捎带着查询副表当副表中的数据没有和主表中的数据匹配上副表自动模拟出NULL与之匹配。3外连接的分类①左外连接左连接表示左边的这张表是主表。②右外连接右连接表示右边的这张表是主表。drop、delete与truncate的区别1DELETE删除表中的部分或全部数据但表结构仍然存在。特点 可以使用WHERE子句指定删除条件如果不使用WHERE子句则删除表中的所有数据。 删除操作可以回滚ROLLBACK因为DELETE操作会记录删除的每一行数据以便在需要时可以恢复。 不会重置自增字段的计数器。 2TRUNCATE 功能删除表中的所有数据但表结构仍然存在。特点 不能使用WHERE子句只能删除表中的所有数据。 不能回滚ROLLBACK因为TRUNCATE操作不会记录删除的每一行数据而是直接清空表。 会重置自增字段的计数器使其从1开始。3DROP 功能删除表及其所有数据表结构也会被删除。 特点 不能回滚ROLLBACK因为DROP操作会完全删除表及其所有数据。 不会重置自增字段的计数器因为表结构已经被删除。4delete和truncate只删除表的数据不删除表的结构 速度,一般来说: drop truncate delete。drop直接删除整张表(包括数据和数据结构)Mysql中in和exists的区别1核心区别· IN先执行子查询把结果集查出来再和主查询的值做等值比较。类似“看看这个值在不在名单里”。· EXISTS主查询每返回一行就把这行的值代入子查询看子查询是否至少返回一行。它不关心具体值只关心“有没有”。找到匹配会立即返回TRUE不继续扫描。2关键陷阱NOT IN 和 NULL-- 子查询结果(1, 2, NULL)-- 结果永远是空因为 id ! NULL 的结果是 UNKNOWNSELECT*FROMAWHEREidNOTIN(SELECTidFROMB);-- 正确写法SELECT*FROMAWHERENOTEXISTS(SELECT1FROMBWHEREA.idB.id);-- EXISTS 只关心子查询有没有返回行完全不看返回的具体内容。你返回 1、0、NULL 还是 abc 都无所谓只要有行就为真没行就为假。3核心区别对比4执行机制详解①IN的执行机制-- 示例查询SELECT*FROMusersWHEREuser_idIN(SELECTuser_idFROMordersWHEREamount1000);-- 执行步骤-- 1. 先执行子查询SELECT user_id FROM orders WHERE amount 1000-- 2. 得到结果集如(101, 102, 103, 104)-- 3. 再执行SELECT * FROM users WHERE user_id IN (101, 102, 103, 104)-- 4. 相当于SELECT * FROM users WHERE user_id 101 OR user_id 102 OR ...②EXISTS的执行机制-- 示例查询SELECT*FROMusers uWHEREEXISTS(SELECT1FROMorders oWHEREo.user_idu.user_idANDo.amount1000);-- 执行步骤类似嵌套循环-- 1. 从users表取第一行数据-- 2. 带入user_id到子查询中执行-- 3. 如果子查询返回至少一行则users表该行满足条件-- 4. 继续处理users表下一行-- 相当于对users表逐行检查获取当前日期及格式化输出1获取系统日期now()2格式化日期date_format(date,format)binlog、redolog、undolog 分别有什么作用和区别binlog二进制日志1binlog 用于记录数据库执行的写入性操作信息以二进制的形式保存在磁盘中。3binlog 是 mysql的逻辑日志并且由 Server 层进行记录4使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。5补充①逻辑日志可以简单理解为记录的就是sql语句 。②物理日志mysql 数据最终是保存在数据页中的物理日志记录的就是数据页变更 。6binlog使用场景①主从复制 在 Master 端开启 binlog 然后将 binlog发送到各个 Slave 端 Slave 端重放 binlog 从而达到主从数据一致。②数据恢复 通过使用 mysqlbinlog 工具来恢复数据。7binlog刷盘时机对于 InnoDB 存储引擎而言只有在事务提交时才会记录biglog 此时记录还在内存中那么 biglog是什么时候刷到磁盘中的呢mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机取值范围是 0-N①0由系统自行判断何时写入磁盘②1每次 commit 的时候将 binlog 写入磁盘③N每N个事务才会将 binlog 写入磁盘。从上面可以看出 sync_binlog 最安全的是设置是 1 这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能因此实际情况下也可以将值适当调大牺牲一定的一致性来获取更好的性能。8binlog 日志有三种格式分别为 STATMENT 、 ROW 和 MIXED。在 MySQL 5.7.7 之前默认的格式是 STATEMENT MySQL 5.7.7 之后默认值是 ROW。日志格式通过 binlog-format 指定。①STATMENT基于SQL 语句的复制( statement-based replication, SBR )每一条会修改数据的sql语句会记录到binlog 中 。优点不需要记录每一行的变化减少了 binlog 日志量节约了 IO , 从而提高了性能缺点在某些情况下会导致主从数据不一致比如执行sysdate() 、 slepp() 等 。②ROW基于行的复制(row-based replication, RBR )不记录每条sql语句的上下文信息仅需记录哪条数据被修改了 。优点不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 缺点会产生大量的日志尤其是alter table的时候会让日志暴涨③MIXED基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )一般的复制使用STATEMENT 模式保存 binlog 对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlogRedo Log重做日志1为什么需要redolog日志为了保证mysql的持久性最简单的做法是在每次事务提交的时候将该事务涉及到的修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题主要体现在两个方面①Innodb 是以 页 为单位进行磁盘交互的而一个事务很可能只修改一个数据页里面的几个字节这个时候将完整的数据页刷到磁盘的话就太浪费资源了②一个事务如果涉及修改多个数据页并且这些数据页在物理上并不连续使用随机IO写入性能太差2因此 mysql 设计了 redo log 具体来说就是只记录事务对数据页做了哪些修改这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。redo log 包括两部分一个是内存中的日志缓冲( redo log buffer )另一个是磁盘上的日志文件( redo logfile)。mysql 每执行一条 DML 语句先将记录写入 redo log buffer后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。Undo Log撤销日志①原子性 底层就是通过 undo log 实现的。②undo log主要记录了数据的逻辑变化比如一条 INSERT 语句对应一条DELETE 的 undo log 对于每个 UPDATE 语句对应一条相反的 UPDATE 的 undo log 这样在发生错误时就能回滚到事务之前的数据状态。③同时 undo log 也是 MVCC(多版本并发控制)实现的关键。4区别①Redo Log和Undo Log都是在数据库引擎层面实现的而Binlog是在服务器层面实现的。②Redo Log主要用于崩溃恢复记录了对数据库的物理修改操作Undo Log主要用于回滚和事务隔离记录了事务的逻辑修改操作。③Redo Log是顺序写入磁盘的而Undo Log通常是随机写入磁盘的。④Binlog用于主从复制记录了所有的数据库修改操作以便在从服务器上复制主服务器的操作并保持数据一致性。2.事务什么是事务多条sql语句要么全部成功要么全部失败。事务的四大特性(隔离级别)ACID1原子性(Atomicity)指事务是一个不可分割的工作单位事务中的操作要么全部成功要么全部失败。2一致性(Consistency)数据不会被破坏。即事务操作成功后数据库所处的状态和它的业务规则是一致的。如A转账100元给B不管操作是否成功A和B的账户总额是不变的。3隔离性(Isolation)在并发操作时,不同的事务拥有各自的数据空间,它们的操作不会对彼此产生干扰4持久性(Durability)事务被提交后它对数据库中数据的改变就是永久性的接下来即使数据库发生故障也不应该对其有任何影响。并发事务带来的问题(脏读、不可重复读以及幻读)怎么解决1并发事务的问题①脏读一个事务读取到另一个事务尚未提交的改变(update,insert,delete)叫做脏读②不可重复读是指在事务1内读取了一个数据事务1还没有结束时事务2也访问了这个数据修改或删除了这个数据并提交。紧接着事务1又读取这个数据。由于事务2的修改那么事务1两次读到的数据可能是不一样的因此称为是不可重复读。事务1想读取开启事务那一刻的数据结果却读到了修改或删除后的数据③幻读是指在事务1内读取了一个数据事务1还没有结束时事务2也访问了这个数据添加了这个数据并提交。紧接着事务1又读取这个数据。由于事务2的添加那么事务1两次读到的数据可能是不一样的因此称为是幻读。事务1想读取事务开启那一刻的数据结果却读到了添加后的数据2解决并发事务带来的问题的方案是对事务进行隔离SQL标准定义的四个隔离级别为①读未提交 最低的隔离级别允许Transaction1读取Transaction2尚未提交的数据变更可能会导致脏读、幻读以及不可重复读。②读已提交 要求Transaction1只能读取Transaction2已提交的修改。可以解决脏读但是幻读或不可重复读仍有可能发生。③可重复读 确保Transaction1可以多次从一个字段中读取到相同的值即Transaction1执行期间禁止其它事务对这个字段进行更新。可以解决脏读和不可重复读但幻读仍有可能发生。④可串行化 最高的隔离级别完全服从 ACID 的隔离级别。所有的事务依次逐 个执行这样事务之间就完全不可能产生干扰也就是说该级别可以解决脏读、不可重复读以及幻读。注意Oracle默认为读已提交MySQL默认为可重复读数据库的事务3.索引3.1 什么是索引索引是一种能够提高查询效率的提前排好序的数据结构。MySQL 索引类型有哪些1主键索引(Primary key)索引列中的值必须是唯一的不允许有空值。2唯一索引(UNIQUE)索引列中的值必须是唯一的但是允许有空值。3普通索引(INDEX)普通索引是MySQL中的基本索引类型没有什么限制允许在定义索引的列中插入重复值和空值。4全文索引(FULLTEX)只能在文本类型CHAR,VARCHAR,TEXT的字段上创建。当字段长度比较大时如果创建普通索引在进行like模糊查询时效率比较低可以使用全文索引。MyISAM和InnoDB中都可以使用全文索引。主键与索引有什么区别1主键一定会创建一个唯一索引但是有唯一索引的列不一定是主键2主键不允许有空值唯一索引允许有空值3一个表只能有一个主键但是可以有多个唯一索引4主键可以被其他表引用为外键唯一索引列不可以5主键是一种约束而唯一索引是一种索引是表的冗余数据结构什么时候不要使用索引1经常增删改的列不要建立索引2有大量重复的列不要建立索引3表记录太少不要建立索引索引的优缺点1优点①提高数据的检索速度降低数据库IO成本使用索引的意义就是通过缩小表中需要查询的记录数目从而加快搜索的速度②降低数据排序的成本降低CPU消耗索引之所以查的快是因为先将数据排好序若该字段正好需要排序则降低了排序的成本2缺点①占用存储空间索引实际上也是一张表记录了主键与索引字段一般以索引文件的形式存储在磁盘上②降低更新表的速度若表的数据发生了变化对应的索引也要一起变更从而降低更新速度。否则索引指向的物理数据可能不对这也是索引失效的原因之一。索引在什么情况下会失效1条件中有or例如select * from table_name where a 1 or b 32在索引上进行计算会导致索引失效例如select * from table_name where a 1 23 在索引的类型上进行数据类型的隐形转换会导致索引失效例如字符串一定要加引号假设select * from table_name where a 1会使用到索引如果写成select * from table_name where a 1则会导致索引失效。4在索引中使用函数会导致索引失效例如select * from table_name where abs(a) 15在使用like查询时以%开头会导致索引失效6复合索引未使用左列字段7索引字段上使用 is null/is not null判断时会导致索引失效例如select * from table_name where a is null3.2 Btree索引跟Hash索引的区别1Btree 支持范围但是Hash是k-v的形式所以不支持范围查询2查询性能方面如果等值查询hash比tree要快很多因为它是k-v的hash结构。为何使用 B 树而非二叉查找树做索引1我们知道二叉树的查找效率为 O(logn)当树过高时查找效率会下降。另外由于我们的索引文件并不小所以是存储在磁盘上的。2文件系统需要从磁盘读取数据时一般以页为单位进行读取假设一个页内的数据过少那么操作系统就需要读取更多的页涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问是数据库里面成本最高的操作之一。3因而这种树高会随数据量增多急剧增加每次更新数据又需要通过左旋和右旋维护平衡的二叉树不太适合用于存储在磁盘上的索引文件。为何使用 B 树而非 B 树做索引1B 树和 B 树的区别①B 树非叶子结点和叶子结点都存储数据因此查询数据时时间复杂度最好为 O(1)最坏为 O(log n)。②而 B 树只在叶子结点存储数据非叶子结点存储关键字且不同非叶子结点的关键字可能重复因此查询数据时时间复杂度固定为 O(log n)。B 树叶子结点之间用链表相互连接因而只需扫描叶子结点的链表就可以完成一次遍历操作B 树只能通过中序遍历。2为什么 B 树比 B 树更适合应用于数据库索引①B 树减少了 IO 次数。由于索引文件很大因此索引文件存储在磁盘上B 树的非叶子结点只存关键字不存数据因而单个页可以存储更多的关键字即一次性读入内存的需要查找的关键字也就越多磁盘的随机 I/O 读取次数相对就减少了。②B 树查询效率更稳定由于数据只存在在叶子结点上所以查找效率固定为 O(log n)所以 B 树的查询效率相比B树更加稳定。③B 树更加适合范围查找B 树叶子结点之间用链表有序连接所以扫描全部数据只需扫描一遍叶子结点利于扫库和范围查询B 树由于非叶子结点也存数据所以只能通过中序遍历按序来扫。也就是说对于范围查询和有序遍历而言B 树的效率更高。4.存储存储引擎有哪些InnoDB是如何解决幻读的Innodb使用MVCC和next-key locks解决幻读MVCC解决的是普通读快照读的幻读next-key locks解决的是当前读情况下的幻读。sql优化以及慢查询排查MySQL查看SQL语句执行效率的SQL语句1Explain语法explain select … from … [where …]2type这列最重要显示了连接使用了哪种类别,有无使用索引是使用Explain命令分析性能瓶颈的关键项之一。一般来说得保证查询至少达到range级别最好能达到ref否则就可能会出现性能问题。项目中遇到慢SQL查询你是怎么排查和解决的1如果大多数情况都正常偶尔很慢则可能是数据库在刷新脏页例如redo log写满了需要同步到磁盘或者执行的时候遇到锁如表锁、行锁又或许写的SQL有问题由于真实业务数据量大就导致速度极慢。当然这是我们主要考虑的原因。另外也有可能是当时网络不好内存不足I/O吞吐量小形成了瓶颈效应不过一般公司不会出现这种情况用的设施都很好的。如果这条SQL一直执行的很慢可能是没有用上索引或者索引失效2慢查询日志文件可以将查询较慢的DQL语句记录下来便于我们定位需要调优的select语句。通过以下命令查看慢查询日志功能是否开启show variables like slow_query_log;2设置慢查询慢查询日志功能默认是关闭的。修改my.ini文件来开启慢查询日志功能在my.ini的[mysqld]后面添加如下配置注意slow_query_log1表示开启慢查询日志功能long_query_time3表示只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。3分析慢查询日志①可以通过show processlist命令定位低效率执行sql②可以用 explain 分析SQL的执行计划。大多数都是看possible_keys、key、key_len,这三个一般套起来分析还有就是Extra、type看全表扫面还是索引、还是索引范围扫描等等。a:possible_keys:表示查询可能使用的索引b:key 实际使用的索引c.key_len:使用索引字段的长度d.Extra给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。e.type:反映了查询表中数据时的访问类型常见的值NULL效率最高一般不可能优化到这个级别只有查询时没有查询表的时候访问类型是NULL。例如select 1;system通常访问系统表的时候访问类型是system。一般也很难优化到这个程序。const根据主键或者唯一性索引查询索引值是常量值时。explain select * from emp where empno7369;eq_ref根据主键或者唯一性索引查询。索引值不是常量值。ref使用了非唯一的索引进行查询。range使用了索引扫描了索引树的一部分。index表示用了索引但是也需要遍历整个索引树。all全表扫描效率最高的是NULL效率最低的是all从上到下从高到低结合起来可以看出索引使用情况4优化索引sql语句数据库结构优化优化器优化架构优化索引①索引一般建在where和order by基数要大区分度要高不要过度索引①尽量覆盖索引MySQL 5.6引入了索引下推优化允许在索引搜索过程中对部分条件进行下推从而减少回表次数。②组合索引符合最左匹配原则(即在MySQL建立联合索引时会遵守最左前缀匹配原则)③避免索引失效④再写多读少的场景下可以选择普通索引而不要唯一索引。因为更新时普通索引可以使用change buffer进行优化减少磁盘IO,将更新操作记录到change bufer等查询来了将数据读到内存再进行修改.sql语句①分页查询优化该方案适用于主键自增的表可以把Limit查询转换成某个位置的查询。select * from tb_sku where id20000 limit 10;②优化insert语句a.多条插入语句写成一条b.利用主键索引特性让数据有序插入数据库结构优化①将字段多的表分解成多个表有些字段使用频率高有些低数据量大时会由于使用频率低的存在而变慢可以考虑分开。②对于经常联合查询的表可以考虑建立中间表优化器优化优化器使用MRR原理MRR 【Multi-Range Read】将ID或键值读到buffer排序通过把「随机磁盘读」转化为「顺序磁盘读」减少磁盘IO从而提高了索引查询的性能。架构优化读/写分离主库写从库读慢sql如何优化1避免使用select *①查询时需要先将星号解析成表的所有字段然后再查询增加查询解析器的成本②select *查询一般不走覆盖索引会产生大量的回表查询③不需要的字段也查出来浪费CPU、内存资源④文本数据、大字段数据传输增加网络消耗2小表驱动大表小表驱动大表就是指用数据量较小、索引比较完备的表然后使用其索引和条件对大表进行数据筛选从而减少数据计算量提高查询效率。3用连接查询代替子查询①因为子查询需要执行两次数据库查询一次是外部查询一次是嵌套子查询。②连接查询可以更好的利用数据库索引提高查询的性能。子查询通常会使用临时表或内存表而连接查询可以直接利用表上的索引。4提升group by的效率①如果你使用group by的列没有索引那么查询可能会变得很慢。因此可以创建一个或多个适当的索引来加速查询②调整查询查询的写法也会影响group by的效率。可以尝试不使用子查询或临时表或者可以使用join或exists来代替in子查询③限制结果集的数量如果你只需要查看一小部分结果可以在查询中添加limit子句以便只返回一定数量的结果5使用批量操作逐个处理会频繁的与数据库交互损耗性能6使用limit7使用union all代替unionunion去重数据需要遍历、排序和比较它更耗时、更消耗CPU资源8join的表不易过多