钓鱼网站的主要危害,手机如何制作图片,大型旅游网站,wordpress插件如何应用文章目录 MySQL面试题(66道)基础1.什么是内连接、外连接、交叉连接、笛卡尔积呢#xff1f;2.那 MySQL 的内连接、左连接、右连接有有什么区别#xff1f;3.说一下数据库的三大范式#xff1f;4.varchar 与 char 的区别#xff1f;5.blob 和 text 有什么区别#xff1f;6.… 文章目录 MySQL面试题(66道)基础1.什么是内连接、外连接、交叉连接、笛卡尔积呢2.那 MySQL 的内连接、左连接、右连接有有什么区别3.说一下数据库的三大范式4.varchar 与 char 的区别5.blob 和 text 有什么区别6.DATETIME和TIMESTAMP的异同7.MySQL 中 in 和 exists 的区别8.MySQL 里记录货币用什么字段类型比较好9.MySQL 怎么存储 emoji表情10.dropdelete与truncate的区别11.UNION 与 UNION ALL 的区别12.count(1)、count(*)与count(列名)的区别?13.一条SQL 查询语句的执行顺序 数据库架构14.说说 MySQL 的基础架构15.一条SQL查询语句在MySQL中如何执行的 存储引擎16.MySQL 有哪些常见存储引擎17.那存储引擎应该怎么选择18.InnoDB和MylSAM主要有什么区别 日志19.MySQL日志文件有哪些分别介绍下作用20.binlog和redo log有什么区别21.一条更新语句怎么执行的了解吗22.那为什么要两阶段提交呢23.redo log 怎么刷入磁盘的知道吗 SQL 优化24.慢 SQL 如何定位呢25.有哪些方式优化慢 SQL26.怎么看执行计划explain如何理解其中各个字段的含义 索引27.能简单说一下索引的分类吗28.为什么使用索引会加快查询29.创建索引有哪些注意点30.索引哪些情况下会失效呢31.索引不适合哪些场景呢32.索引是不是建的越多越好呢33.MySQL 索引用的什么数据结构了解吗34.那一棵 B树能存储多少条数据呢35.为什么要用B树而不用普通二叉树36.为什么用B树而不用B树呢37.Hash 索引和B树索引区别是什么38.聚簇索引与非聚簇索引的区别39.回表了解吗40.覆盖索引了解吗41.什么是最左前缀原则/最左匹配原则42.什么是索引下推优化 锁43.MySQL 中有哪几种锁列举一下44.说说 InnoDB 里的行锁实现45.意向锁是什么知道吗46.MySQL 的乐观锁和悲观锁了解吗47.MySQL遇到过死锁问题吗你是如何解决的 事务48.MySQL 事务的四大特性说一下49.那 ACID 靠什么保证的呢50.事务的隔离级别有哪些MySQL的默认隔离级别是什么51.什么是幻读脏读不可重复读呢52.事务的各个隔离级别都是如何实现的53.MVCC了解吗怎么实现的 高可用/高性能54.数据库读写分离了解吗55.那读写分离的分配怎么实现呢56.主从复制原理了解吗57.主从同步延迟怎么处理58.你们一般是怎么分库的呢59.那你们是怎么分表的60.水平分表有哪几种路由方式61.不停机扩容怎么实现62.常用的分库分表中间件有哪些63.那你觉得分库分表会带来什么问题呢 运维64.百万级别以上的数据如何删除65.百万千万级大表如何添加字段66.MySQL数据库cpu飙升的话要怎么处理呢 MySQL面试题(66道) 基础
作为SQLBoy基础部分不会有人不会吧面试也不怎么问基础掌握不错的小伙伴可以跳过这一部分。当然可能会现场写一些SQL语句SQ语句可以通过牛客、LeetCode、LintCode之类的网站来练习。 DDL全称是Data Definition Language即数据定义语言 DML全称是Data Manipulation Language即数据操纵语言 DQL全称是Data Query Language即数据查询语言 DCL全称是Data Control Language即数据控制语言。 1.什么是内连接、外连接、交叉连接、笛卡尔积呢
内连接inner join取得两张表中满足存在连接匹配关系的记录。外连接outer join不只取得两张表中满足存在连接匹配关系的记录还包括某张表或两张表中不满足匹配关系的记录。交叉连接cross join显示两张表所有记录–对应没有匹配关系进行筛选它是笛卡尔积在SQL中的实现如果 A 表有 m 行B 表有 n 行那么 A 和 B 交叉连接的结果就有 m*n 行。笛卡尔积是数学中的一个概念例如集合 A{a, b}集合 B{0, 1, 2}那么 AXB{a, 0, a, 1, a, 2, b, 0, b, 1, b, 2}。
2.那 MySQL 的内连接、左连接、右连接有有什么区别
MySQL 的连接主要分为内连接和外连接外连接常用的有左连接、右连接。 MySQL-joins-来源菜鸟教程
inner join内连接在两张表进行连接查询时只保留两张表中完全匹配的结果集left join在两张表进行连接查询时会返回左表所有的行即使在右表中没有匹配的记录。right join 在两张表进行连接查询时会返回右表所有的行即使在左表中没有匹配的记录。
3.说一下数据库的三大范式
第一范式数据表中的每一列每个字段都不可以再拆分。例如用户表用户地址还可以拆分成国家、省份、市这样才是符合第一范式的。第二范式在第一范式的基础上非主键列完全依赖于主键而不能是依赖于主键的一部分。例如订单表里存储了商品信息商品价格、商品类型那就需要把商品 ID 和订单 ID 作为联合主键才满足第二范式。第三范式在满足第二范式的基础上表中的非主键只依赖于主键而不依赖于其他非主键。例如订单表就不能存储用户信息姓名、地址。 三大范式的作用是为了控制数据库的冗余是对空间的节省实际上一般互联网公司的设计都是反范式的通过冗余一些数据避免跨表跨库利用空间换时间提高性能。
4.varchar 与 char 的区别 char
char 表示定长字符串长度是固定的如果插入数据的长度小于 char 的固定长度时则用空格填充因为长度固定所以存取速度要比varchar快很多甚至能快50%但正因为其长度固定所以会占据多余的空间是空间换时间的做法对于 char 来说最多能存放的字符个数为 255和编码无关
varchar
varchar 表示可变长字符串长度是可变的插入的数据是多长就按照多长来存储varchar 在存取方面与 char 相反它存取慢因为长度不固定但正因如此不占据多余的空间是时间换空间的做法对于varchar来说最多能存放的字符个数为65532
日常的设计对于长度相对固定的字符串可以使用char对于长度不确定的使用varchar更合适一些。
5.blob 和 text 有什么区别
blob 用于存储二进制数据而 text 用于存储大字符串。blob 没有字符集text 有一个字符集并且根据字符集的校对规则对值进行排序和比较
6.DATETIME和TIMESTAMP的异同
相同点
两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS两个数据类型都包含「日期」和「时间」部分。两个数据类型都可以存储微秒的小数秒秒后6位小数秒
区别
日期范围DATETIME 的日期范围是1000-01-01 00:00:00.000000到 9999-12-31 23:59:59.999999TIMESTAMP 的时间范围是 1970-01-01 0000:01.000000 UTC 到2038-01-09 03:14:07.999999 UTC存储空间DATETIME 的存储空间为 8 字节TIMESTAMP 的存储空间为 4 字节时区相关DATETIME 存储时间与时区无关TIMESTAMP 存储时间与时区有关显示的值也依赖于时区默认值DATETIME的默认值为nullTIMESTAMP的字段默认不为空not null默认值为当前时间CURRENT_TIMESTAMP
7.MySQL 中 in 和 exists 的区别
MySQL中的 in语句是把外表和内表作hash连接而exists语句是对外表作loop循环每次loop循环再对内表进行查询。我们可能认为 exists 比 in 语句的效率要高这种说法其实是不准确的要区分情景
如果查询的两个表大小相当那么用in和 exists 差别不大。如果两个表中一个较小一个是大表则子查询表大的用 exists子查询表小的用 in。not in 和 not exists如果查询语句使用了 not in那么内外表都进行全表扫描没有用到索引而 not extsts 的子查询依然能用到表上的索引。所以无论那个表大用 not exists 都比 not in 要快。
8.MySQL 里记录货币用什么字段类型比较好
货币在数据库中MySQL常用Decimal和Numric类型表示这两种类型被MySQL实现为同样的类型。他们被用于保存与货币有关的数据。
例如salary DECIMAL(9, 2)9(precision)代表将被用于存储值的总的小数位数而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从-9999999.99到9999999.99
DECIMAL和NUMERIC值作为字符串存储而不是作为二进制浮点数以便保存那些值的小数精度。
之所以不使用 float 或者 double 的原因因为 float 和 double 是以二进制存储的所以有一定的误差。
9.MySQL 怎么存储 emoji表情
MySQL 可以直接使用字符串存储 emoji.
但是需要注意的utf8编码是不行的MySQL中的utf8是阉割版的utf8它最多只用3个字节存储字符所以存储不了表情。那该怎么办需要使用utf8mb4编码。
alter table blogs modify content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci not null;10.dropdelete与truncate的区别
三者都表示删除但是三者有一些差别 因此在不再需要一张表的时候用drop在想删除部分数据行时候用delete在保留表而删除所有数据的时候用 truncate。
11.UNION 与 UNION ALL 的区别
如果使用UNION会在表链接后筛选掉重复的记录行如果使用UNION ALL不会合并重复的记录行从效率上说UNION ALL要比UNION快很多如果合并没有刻意要删除重复行那么就使用UNION All
12.count(1)、count(*)与count(列名)的区别?
执行效果
count(*)包括了所有的列相当于行数在统计结果的时候不会忽略列值为NULLcount(1)包括了忽略所有列用1代表代码行在统计结果的时候不会忽略列值为NULLcount(列名)只包括列名那一列在统计结果的时候会忽略列值为空这里的空不是只空字符串或者 0而是表示 null的计数即某个字段值为 NULL 时不统计。
执行速度
列名为主键count(列名)会比 count(1)快列名不为主键count(1)会比 count(列名)快如果表多个列并且没有主键则 count(1)的执行效率优于 count(*)如果有主键则 select count(主键)的执行效率是最优的如果表只有一个字段则 select count(*)最优。
13.一条SQL 查询语句的执行顺序 FROM对FROM子句中的左表left_table和右表right_table执行笛卡儿积Cartesianproduct产生虚拟表 VT1ON对虚拟表 VT1 应用 ON 筛选只有那些符合join_condition的行才被插入虚拟表 VT2 中JOIN如果指定了OUTER JOIN如LEFT OUTER JOINRIGHT OUTER JOIN那么保留表中未匹配的行作为外部行添加到虚拟表VT2中产生虚拟表VT3。如果FROM子句包含两个以上表则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3直到处理完所有的表为止WHERE对虚拟表VT3应用WHERE过滤条件只有符合where_condition的记录才被插入虚拟表VT4中GROUP BY根据 GROUP BY 子句中的列对 VT4 中的记录进行分组操作产生 VT5CUBE|ROLLUP对表VT5进行CUBE或ROLLUP操作产生表VT6HAVING对虚拟表VT6应用HAVING过滤器只有符合having-condition的记录才被插入虚拟表VT7中。SELECT第二次执行SELECT操作选择指定的列插入到虚拟表VT8中DISTINCT去除重复数据产生虚拟表VT9ORDER BY将虚拟表VT9中的记录按照order_by_list进行排序操作产生虚拟表VT10.11LIMIT取出指定行的记录产生虚拟表VT11并返回给查询用户
数据库架构
14.说说 MySQL 的基础架构 MySQL 逻辑架构图主要分三层
客户端最上层的服务并不是MySQL所独有的大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。Server层大多数MySQL的核心服务功能都在这一层包括查询解析、分析、优化、缓存以及所有的内置函数例如日期、时间、数学和加密函数所有跨存储引擎的功能都在这一层实现存储过程、触发器、视图等。存储引擎层第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。Server 层通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异使得这些差异对上层的查询过程透明。 15.一条SQL查询语句在MySQL中如何执行的
先检查该语句 是否有权限如果没有权限直接返回错误信息如果有权限会先查询缓存MySQL8.0 版本以前。如果没有缓存分析器进行 语法分析提取 sql 语句中 select 等关键元素然后判断 sql 语句是否有语法错误比如关键词是否正确等等。语法解析之后MySQL 的服务器会对查询的语句进行优化确定执行的方案。完成查询优化后按照生成的执行计划 调用数据库引擎接口返回执行结果。 一条SQL更新语句在MySQL中的执行过程 存储引擎
16.MySQL 有哪些常见存储引擎
InnoDB、MyISAM、MEMORY
主要存储引擎以及功能如下 MySQL5.5之前默认存储引擎是MyISAM5.5之后变成了InnoDB。 InnoDB支持的哈希索引是自适应的InnoDB会根据表的使用情况自动为表生成哈希索引不能人为干预是否在一张表中生成哈希索引。 MySQL 5.6 开始 InnoDB 支持全文索引。 17.那存储引擎应该怎么选择
大致上可以这么选择
大多数情况下使用默认的 InnoDB 就够了。如果要提供提交、回滚和恢复的事务安全ACID 兼容能力并要求实现并发控制InnoDB 就是比较靠前的选择了。如果数据表主要用来插入和查询记录则 MyISAM 引擎提供较高的处理效率。如果只是临时存放数据数据量不大并且不需要较高的数据安全性可以选择将数据保存在内存的MEMORY 引擎中MySQL 中使用该引擎作为临时表存放查询的中间结果。
使用哪一种引擎可以根据需要灵活选择因为存储引擎是基于表的所以一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎将会提高整个数据库的性能。
18.InnoDB和MylSAM主要有什么区别
PS:MySQL8.0 都开始慢慢流行了如果不是面试MyISAM 其实可以不用怎么了解。 存储结构每个MyISAM在磁盘上存储成三个文件InnoDB所有的表都保存在同一个数据文件中也可能是多个文件或者是独立的表空间文件InnoDB表的大小只受限于操作系统文件的大小一般为2GB.事务支持MyISAM不提供事务支持InnoDB提供事务支持事务具有事务commit、回滚rollback和崩溃修复能力crash recovery capabilities的事务安全特性。最小锁粒度MyISAM只支持表级锁更新时会锁住整张表导致其它查询和更新都会被阻塞InnoDB支持行级锁。索引类型MyISAM的索引为非聚簇索引数据结构是B树InnoDB的索引是聚簇索引数据结构是B树。主键必需MyISAM 允许没有任何索引和主键的表存在InnoDB 如果没有设定主键或者非空唯一索引就会自动生成一个6字节的主键用户不可见数据是主索引的一部分附加索引保存的是主索引的值。表的具体行数MyISAM保存了表的总行数如果select count(*) from table会直接取出该值InnoDB没有保存表的总行数如果使用 select count(*) from table就会遍历整个表但是在加了wehre 条件后MylSAM 和InnoDB 处理的方式都一样。外键支持MyISAM不支持外键InnoDB支持外键。
日志
19.MySQL日志文件有哪些分别介绍下作用
MySQL 日志文件有很多包括
错误日志error log错误日志文件对MySQL的启动、运行、关闭过程进行了记录能帮助定位MySQL问题。慢查询日志slow query log慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志可以查找出哪些查询语句的执行效率很低以便进行优化。一般查询日志general log一般查询日志记录了所有对MySQL数据库请求的信息无论请求是否正确执行。二进制日志bin log关于二进制日志它记录了数据库所有执行的 DDL和 DML 语句除了数据查询语句 select、show 等以事件形式记录并保存在二进制文件中。
还有两个 InnoDB 存储引擎特有的日志文件
重做日志redo log重做日志至关重要因为它们记录了对于 InnoDB 存储引擎的事务日志。回滚日志undo log回滚日志同样也是InnoDB引擎提供的日志顾名思义回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改InnoDB引擎不仅会记录redo log还会生成对应的undo log日志如果事务执行失败或调用了rollback导致事务需要回滚就可以利用undo log中的信息将数据回滚到修改之前的样子。
20.binlog和redo log有什么区别
bin log 会记录所有与数据库有关的日志记录包括 InnoDB、MyISAM 等存储引擎的日志而 redo log 只记InnoDB 存储引擎的日志。记录的内容不同bin log 记录的是关于一个事务的具体操作内容即该日志是逻辑日志。而 redo log 记录的是关于每个页Page的更改的物理情况。写入的时间不同bin log 仅在事务提交前进行提交也就是只写磁盘一次。而在事务进行的过程中却不断有 redo ertry 被写入 redo log 中。写入的方式也不相同redo log 是循环写入和擦除bin log 是追加写入不会覆盖已经写的文件。
21.一条更新语句怎么执行的了解吗 更新语句的执行是 Server 层和引擎层配合完成数据除了要写入表中还要记录相应的日志。
执行器先找引擎获取ID2 这一行。ID 是主键存储引擎检索数据找到这一行。如果ID2 这一行所在的数据页本来就在内存中就直接返回给执行器否则需要先从磁盘读入内存然后再返回。执行器拿到引擎给的行数据把这个值加上1比如原来是N现在就是N1得到新的一行数据再调用引擎接口写入这行新数据。引擎将这行新数据更新到内存中同时将这个更新操作记录到redo log里面此时redo log处于prepare状态。然后告知执行器执行完成了随时可以提交事务。执行器生成这个操作的 binlog并把 binlog 写入磁盘。执行器调用引擎的提交事务接口引擎把刚刚写入的redo log改成提交commit状态更新完成。
从上图可以看出MySQL 在执行更新语句的时候在服务层进行语句的解析和执行在引擎层进行数据的提取和存储同时在服务层对binlog进行写入在InnoDB内进行redo log的写入。
不仅如此在对 redo log 写入时有两个阶段的提交一是 binlog 写入之前 **prepare**状态的写入二是 binlog 写入之后 commit 状态的写入。
22.那为什么要两阶段提交呢
为什么要两阶段提交呢直接提交不行吗
我们可以假设不采用两阶段提交的方式而是采用单阶段进行提交即要么先写入redo log后写入binlog要么先写入binlog后写入redo log。这两种方式的提交都会导致原先数据库的状态和被恢复后的数据库的状态不一致。
先写入 redo log后写入 binlog
在写完redo log之后数据此时具有crash-safe能力因此系统崩溃数据会恢复成事务开始之前的状态。但是若在redo log写完时候binlog写入之前系统发生了宕机。此时binlog没有对上面的更新语句进行保存导致当使用binlog进行数据库的备份或者恢复时就少了上述的更新语句。从而使得id2这一行的数据没有被更新。 先写入 binlog后写入 redo log
写完 binlog 之后所有的语句都被保存所以通过 binlog 复制或恢复出来的数据库中 id2 这一行的数据会被更新为a1。但是如果在redo log写入之前系统崩溃那么redo log中记录的这个事务会无效导致实际数据库中 id2 这一行的数据并没有更新。 简单说redo log和binlog都可以用于表示事务的提交状态而两阶段提交就是让这两个状态保持逻辑上的一致。
23.redo log 怎么刷入磁盘的知道吗
redo log的写入不是直接落到磁盘而是在内存中设置了一片称之为 redo log buffer的连续内存空间也就是redo日志缓冲区。 什么时候会刷入磁盘 在如下的一些情况中log buffer 的数据会刷入磁盘
log buffer 空间不足时
log buffer的大小是有限的如果不停的往这个有限大小的log buffer里塞入日志很快它就会被填满。如果当前写入 log buffer 的 redo 日志量已经占满了 log buffer 总容量的大约一半左右就需要把这些日志刷新到磁盘上。
事务提交时
在事务提交时为了保证持久性会把log buffer中的日志全部刷到磁盘。注意这时候除了本事务的可能还会刷入其它事务的日志。
后台线程输入
有一个后台线程大约每秒都会刷新一次 log buffer 中的 redo log 到磁盘。 正常关闭服务器时 触发 checkpoint 规则
重做日志缓存、重做日志文件都是以块block的方式进行保存的称之为重做日志块redo log block块的大小是固定的512字节。我们的redo log它是固定大小的可以看作是一个逻辑上的log group由一定数量的log block组成。 它的写入方式是从头到尾开始写写到末尾又回到开头循环写。
其中有两个标记位置
write pos是当前记录的位置一边写一边后移写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到磁盘。 当write_pos追上 checkpoint 时表示 redo log 日志已经写满。这时候就不能接着往里写数据了需要执行checkpoint规则腾出可写空间。
所谓的checkpoint 规则就是 checkpoint 触发后将 buffer 中日志页都刷到磁盘。
SQL 优化
24.慢 SQL 如何定位呢
慢 SQL 的监控主要通过两个途径
慢查询日志开启MySQL 的慢查询日志再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志当然现在一般的云厂商都提供了可视化的平台。服务监控可以在业务的基建中加入对慢SQL的监控常见的方案有字节码插桩、连接池扩展、ORM框架过程对服务运行中的慢SQL进行监控和告警。
25.有哪些方式优化慢 SQL
慢 SQL 的优化主要从两个方面考虑SQL 语句本身的优化以及数据库设计的优化。 避免不必要的列
这个是老生常谈但还是经常会出的情况SQL 查询的时候应该只查询需要的列而不要包含额外的列像slect *这种写法应该尽量避免。
分页优化
在数据量比较大分页比较深的情况下需要考虑分页的优化。 例如
select * from table where type 2 and level 9 order by id asc limit 190289,10;优化方案
延迟关联 先通过where条件提取出主键在将该表与原数据表关联通过主键id提取数据行而不是通过原来的二级索引提取数据行 例如
select a.* from table a,(select id from table where type 2 and level 9 order by id asc limit 190289,10 ) bwhere a.id b.id书签方式 书签方式就是找到limit第一个参数对应的主键值根据这个主键值再去过滤并limit例如
select * from table where id (select * from table where type 2 and level 9 order by id asc limit 190190289,10 )索引优化
合理地设计和使用索引是优化慢 SQL 的利器。
利用覆盖索引 InnoDB使用非主键索引查询数据时会回表但是如果索引的叶节点中已经包含要查询的字段那它没有必要再回表查询了这就叫覆盖索引 例如对于如下查询
select name from test where city上海我们将被查询的字段建立到联合索引中这样查询结果就可以直接从索引中获取
alter table test add index idx_city_name (city, name);低版本避免使用 or 查询 在MySQL 5.0之前的版本要尽量避免使用or查询可以使用union或者子查询来替代因为早期的MySQL版本使用 or 查询可能会导致索引失效高版本引入了索引合并解决了这个问题。避免使用! 或者 操作符 SQL 中不等于操作符会导致查询引擎放弃查询索引引起全表扫描即使比较的字段上有索引解决方法通过把不等于操作符改成 or可以使用索引避免全表扫描
例如把 columnaaa改成columnaaa or columnaaa就可以使用索引了 适当使用前缀索引 适当地使用前缀索引可以降低索引的空间占用提高索引的查询效率。 比如邮箱的后缀都是固定的xxx.com那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
alter table test add index index2(email(6));PS需要注意的是前缀索引也存在缺点MySQL 无法利用前缀索引做 order by 和 group by 操作也无法作为覆盖索引
避免列上函数运算 要避免在列字段上进行算术运算或其他表达式运算否则可能会导致存储引擎无法正确使用索引从而影响了查询的效率
select * from test where id 1 50;
select * from test where month(updateTime) 7;正确使用联合索引 使用联合索引的时候注意最左匹配原则。
JOIN 优化
优化子查询 尽量使用 Join 语句来替代子查询因为子查询是嵌套查询而嵌套查询会新创建一张临时表而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间同时对于返回结果集比较大的子查询其对查询性能的影响更大小表驱动大表 关联查询的时候要拿小表去驱动大表因为关联的时候MySQL 内部会遍历驱动表再去连接被驱动表。 比如 left join左表就是驱动表A 表小于 B 表建立连接的次数就少查询速度就被加快了。
select name from A left join B ;适当增加冗余字段 增加冗余字段可以减少大量的连表查询因为多张表的连表查询性能很低所有可以适当的增加冗余字段以减少多张表的关联查询这是以空间换时间的优化策略避免使用JOIN关联太多的表 《阿里巴巴 Java 开发手册》规定不要 join 超过三张表第一 join 太多降低查询的速度第二 join 的 buffer 会占用更多的内存。 如果不可避免要join多张表可以考虑使用数据异构的方式异构到ES中查询。
排序优化 利用索引扫描做排序 MySQL有两种方式生成有序结果其一是对结果集进行排序的操作其二是按照索引顺序扫描得出的结果自然是有序的 但是如果索引不能覆盖查询所需列就不得不每扫描一条记录回表查询一次这个读操作是随机IO通常会比顺序全表扫描还慢 因此在设计索引时尽可能使用同一个索引既满足排序又用于查找行 例如
--建立索引date,staff_id,customer_id
select staff_id, customer_id from test where date 2010-01-01 order by staff_id,customer_id;只有当索引的列顺序和ORDERBY子句的顺序完全一致并且所有列的排序方向都一样时才能够使用索引来对结果做排序
UNION优化 条件下推 MySQL处理union的策略是先创建临时表然后将各个查询结果填充到临时表中最后再来做查询很多优化策略在union 查询中都会失效因为它无法利用索引 最好手工将where、limit等子句下推到union的各个子查询中以便优化器可以充分利用这些条件进行优化 此外除非确实需要服务器去重一定要使用union all如果不加all 关键字MySQL 会给临时表加上distinct选项这会导致对整个临时表做唯一性检查代价很高。
26.怎么看执行计划explain如何理解其中各个字段的含义
explain是sql优化的利器除了优化慢sql平时的sql编写也应该先explain查看一下执行计划看看是否还有优化的空间。
直接在select 语句之前增加explain关键字就会返回执行计划的信息。 索引
索引可以说是MySQL面试中的重中之重一定要彻底拿下。
27.能简单说一下索引的分类吗
从三个不同维度对索引分类 例如从基本使用使用的角度来讲
主键索引InnoDB主键是默认的索引数据列不允许重复不允许为NULL一个表只能有一个主键。唯一索引数据列不允许重复允许为NULL值一个表允许多个列创建唯一索引。普通索引基本的索引类型没有唯一性的限制允许为NULL值。组合索引多列值组成一个索引用于组合搜索效率大于索引合并
28.为什么使用索引会加快查询
传统的查询方法是按照表的顺序遍历的不论查询几条数据MySQL 需要将表的数据从头到尾遍历一遍。
在我们添加完索引之后MySQL一般通过BTREE 算法生成一个索引文件在查询数据库时找到索引文件进行遍历在比较小的索引数据里查找然后映射到对应的数据能大幅提升查找的效率。
和我们通过书的目录去查找对应的内容一样的道理。 29.创建索引有哪些注意点
索引虽然是sql性能优化的利器但是索引的维护也是需要成本的所以创建索引也要注意 索引应该建在查询应用频繁的字段。在用于where判断、order排序和join的on字段上创建索引。 索引的个数应该适量。索引需要占用空间更新时候也需要维护。 区分度低的字段例如性别不要建索引。离散度太低的字段扫描的行数降低的有限。 频繁更新的值不要作为主键或者索引维护索引文件需要成本还会导致页分裂I0次数增多。 组合索引把散列性高区分度高的值放在前面为了满足最左前缀匹配原则 创建组合索引而不是修改单列索引。组合索引代替多个单列索引对于单列索引MySQL 基本只能使用一个索引所以经常使用多个条件查询时更适合使用组合索引 过长的字段使用前缀索引。当字段值比较长的时候建立索引会消耗很多的空间搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引这个就叫前缀索引。 不建议用无序的值例如身份证、UUID作为索引。
当主键具有不确定性会造成叶子节点频繁分裂出现磁盘存储的碎片化
30.索引哪些情况下会失效呢
查询条件包含or可能导致索引失效如果字段类型是字符串where 时一定用引号括起来否则会因为隐式类型转换索引失效like通配符可能导致索引失效。联合索引查询时的条件列不是联合索引中的第一个列索引失效。在索引列上使用mysql的内置函数索引失效。对索引列运算如、-、*、/索引失效。索引字段上使用! 或者not in时可能会导致索引失效。索引字段上使用is nullis not nul可能导致索引失效。左连接查询或者右连接查询查询关联的字段编码格式不一样可能导致索引失效。MySQL优化器估计使用全表扫描要比使用索引快则不使用索引。
31.索引不适合哪些场景呢 数据量比较少的表不适合加索引 更新比较频繁的字段也不适合加索引 离散低的字段不适合加索引如性别
32.索引是不是建的越多越好呢
当然不是。
索引会占据磁盘空间**索引虽然会提高查询效率但是会降低更新表的效率。**比如每次对表进行增删改操作MySQL不仅要保存数据还有保存或者更新对应的索引文件。
33.MySQL 索引用的什么数据结构了解吗
MySQL的默认存储引擎是InnoDB它采用的是B树结构的索引。
B树只有叶子节点才会存储数据非叶子节点只存储键值。叶子节点之间使用双向指针连接最底层的叶子节点形成了一个双向有序链表。 在这张图里有几个重点
图中的每个方块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项粉色所示和指针蓝色所示如根节点磁盘包含数据项 17 和 35包含指针 P1、P2、P3P1 表示小于17 的磁盘块P2表示在 17 和 35 之间的磁盘块P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、4、5…65。非叶子节点只不存储真实的数据只存储指引搜索方向的数据项如 17、35 并不真实存在于数据表中。叶子节点之间使用双向指针连接最底层的叶子节点形成了一个双向有序链表可以进行范围查询。
34.那一棵 B树能存储多少条数据呢 假设索引字段是 bigint 类型长度为 8 字节。指针大小在 InnoDB 源码中设置为6 字节这样一共 14 字节。非叶子节点一页可以存储16384/141170个这样的 单元键值指针代表有1170个指针。树深度为 2 的时候有 1170^2 个叶子节点可以存储的数据为 11701170162190 2400。在查找数据时一次页的查找代表一次10也就是说一张 2000 万左右的表查询数据最多需要访问 3 次磁盘。 所以在 InnoDB 中 B 树深度一般为 1-3 层它就能满足千万级的数据存储。
35.为什么要用B树而不用普通二叉树
可以从几个维度去看这个问题查询是否够快效率是否稳定存储数据多少以及查找磁盘次数。 为什么不用普通二叉树 普通二叉树存在退化的情况如果它退化成链表相当于全表扫描。平衡二叉树相比于二叉查找树来说查找效率更稳定总体的查找速度也更快。 为什么不用平衡二叉树呢 读取数据的时候是从磁盘读到内存。如果树这种数据结构作为索引那每查找一次数据就需要从磁盘中读取一个节点也就是一个磁盘块但是平衡二叉树可是每个节点只存储一个键值和数据的如果是B树可以存储更多的节点数据树的高度也会降低因此读取磁盘的次数就降下来啦查询效率就快。
36.为什么用B树而不用B树呢
B相比较 B 树有这些优势
它是 B Tree 的变种B Tree 能解决的问题它都能解决。
B Tree 解决的两大问题每个节点存储更多关键字路数更多
扫库、扫表能力更强
如果我们要对表进行全表扫描只需要遍历叶子节点就可以 了不需要遍历整棵 BTree 拿到所有的数据。
B Tree的磁盘读写能力相对于B Tree来说更强IO次数更少
根节点和枝节点不保存数据区所以一个节点可以保存更多的关键字一次磁盘加载的关键字更多IO次数更少。
排序能力更强
因为叶子节点上有下一个数据区的指针数据形成了链表。
效率更加稳定 BTree 永远是在叶子节点拿到数据所以IO次数是稳定的。
37.Hash 索引和B树索引区别是什么
B树可以进行范围查询Hash索引不能。B树支持联合索引的最左侧原则Hash索引不支持。B 树支持 order by 排序Hash 索引不支持。Hash索引在等值查询上比B 树效率更高。B树使用like进行模糊查询的时候like后面比如%开头的话可以起到优化的作用Hash索引根本无法进行模糊查询。
38.聚簇索引与非聚簇索引的区别
首先理解聚簇索引不是一种新的索引而是而是一种数据存储方式。
聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎–MyISAM 采用的是非聚簇索引InnoDB采用的是聚簇索引。
可以这么说
索引的数据结构是树聚簇索引的索引和数据存储在一棵树上树的叶子节点就是数据非聚簇索引索和数据不在一棵树上。 一个表中只能拥有一个聚簇索引而非聚簇索引一个表可以存在多个。聚簇索引索引中键值的逻辑顺序决定了表中相应行的物理顺序非聚簇索引索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。聚簇索引物理存储按照索引排序非聚集索引物理存储不按照索引排序
39.回表了解吗
在InnoDB存储引擎里利用辅助索引查询先通过辅助索引找到主键索引的键值再通过主键值查出主键索引里面没有符合要求的数据它比基于主键索引的查询多扫描了一棵索引树这个过程就叫回表。
例如select \* from user where name 张三; 40.覆盖索引了解吗
在辅助索引里面不管是单列索引还是联合索引如果 select 的数据列只用辅助索引中就能够取得不用去查主键索引这时候使用的索引就叫做覆盖索引避免了回表。
如select name from user where name 张三; 41.什么是最左前缀原则/最左匹配原则
注意最左前缀原则、最左匹配原则、最左前缀匹配原则这三个都是一个概念。
最左匹配原则在InnoDB的联合索引中查询的时候只有匹配了前一个/左边的值之后才能匹配下一个。
根据最左匹配原则我们创建了一个组合索引如a1a2a3相当于创建了a1、a1a2和a1a2a3三个索引。
为什么不从最左开始查就无法匹配呢
比如有一个 user 表我们给 name 和 age 建立了一个组合索引。
ALTER TABLE user add INDEX comidx_name_phone (name,age);组合索引在 BTree 中是复合的数据结构它是按照从左到右的顺序来建立搜索树的name 在左边age 在右边。 从这张图可以看出来name是有序的age是无序的。当name相等的时候age才是有序的。
这个时候我们使用where name 张三 and age 20去查询数据的时候BTree会优先比较name来确定下一步应该搜索的方向往左还是往右。如果name相同的时候再比较age。但是如果查询条件没有name就不知道下一步应该查哪个节点因为建立搜索树的时候 name是第一个比较因子所以就没用上索引。
42.什么是索引下推优化
索引条件下推优化**Index Condition PushdownICP**是MySQL5.6 添加的用于优化数据查询。
不使用索引条件下推优化时存储引擎通过索引检索到数据然后返回给 MySQL ServerMySQL Server 进行过滤条件的判断。当使用索引条件下推优化时如果存在某些被索引的列的判断条件时MySQL Server 将这一部分判断条件下推给存储引擎然后由存储引擎通过判断索引是否符合MySQL Server传递的条件只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
例如一张表建了一个联合索引nameage查询语句select * from t_user where name like 张% and age10;由于 name 使用了范围查询根据最左匹配原则
不使用ICP引擎层查找到name like 张%的数据再由Server层去过滤age10这个条件这样一来就回表了两次浪费了联合索引的另外一个字段 age。 但是使用了索引下推优化把where的条件放到了引擎层执行直接根据name like 张% and age10的条件进行过滤减少了回表的次数。 索引条件下推优化可以减少存储引擎查询基础表的次数也可以减少 MySQL 服务器从存储引擎接收数据的次数。
锁
43.MySQL 中有哪几种锁列举一下 如果按锁粒度划分有以下 3 种
表锁开销小加锁快锁定力度大发生锁冲突概率高并发度最低不会出现死锁。行锁开销大加锁慢会出现死锁锁定粒度小发生锁冲突的概率低并发度高。页锁开销和加锁速度介于表锁和行锁之间会出现死锁锁定粒度介于表锁和行锁之间并发度一般
如果按照兼容性有两种
共享锁S Lock也叫读锁read lock相互不阻塞。排他锁XLock也叫写锁write lock排它锁是阻塞的在一定时间内只有一个请求能执行写入并阻止其它锁读取正在写入的数据。
44.说说 InnoDB 里的行锁实现
我们拿这么一个用户表来表示行级锁其中插入了4行数据主键值分别是16812现在简化它的聚簇索引结构只保留数据记录。 InnoDB的行锁的主要实现如下
Record Lock 记录锁 记录锁就是直接锁定某行记录。当我们使用唯一性的索引包括唯一索引和聚簇索引进行等值查询且精准匹配到一条记录时此时就会直接将这条记录锁定。例如select * from t where id 6 for update就会将id6的记录锁定。 Gap Lock 间隙锁 间隙锁Gap Locks的间隙指的是两个记录之间逻辑上尚未填入数据的部分是一个左开右开空间。 间隙锁就是锁定某些间隙区间的。当我们使用用等值查询或者范围查询并且没有命中任何一个record此时就会将对应的间隙区间锁定。例如select * from t where id 3 for update;或者select * from t where id 1 and id 6 for update;就会将16区间锁定。
Next-key Lock 临键锁 临键指的是间隙加上它右边的记录组成的左开右闭区间。比如上述的16]、68]等。 临键锁就是记录锁Record Locks和间隙锁Gap Locks的结合即除了锁住记录本身还要再锁住索引之间的间隙。当我们使用范围查询并且命中了部分record记录此时锁住的就是临键区间。注意临键锁锁住的区间会包含最后一个record 的右边的临键区间。例如 select * from t where id 5 and id 7 for update;会锁住16]、68]。mysql 默认行锁类型就是 临键锁Next-KeyLocks。当使用唯一性索引等值查询匹配到一条记录的时候临键锁Next-Key Locks会退化成记录锁没有匹配到任何记录的时候退化成间隙锁。 间隙锁Gap Locks和临键锁Next-Key Locks都是用来解决幻读问题的 在已提交读READ COMMITTED隔离级别下间隙锁Gap Locks和 临键锁Next-Key Locks都会失效 上面是行锁的三种实现算法除此之外在行上还存在插入意向锁。
Insert Intention Lock 插入意向锁
一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了意向锁如果有的话插入操作需要等待直到拥有gap锁的那个事务提交。但是事务在等待的时候也需要在内存中生成一个锁结构表明有事务想在某个间隙中插入新记录但是现在在等待。这种类型的锁命名为 Insert Intention Locks也就是插入意向锁。
假如我们有个T1事务给16区间加上了意向锁现在有个T2事务要插入一个数据id为4它会获取一个16区间的插入意向锁又有有个T3 事务想要插入一个数据id为 3它也会获取一个16区间的插入意向锁但是这两个插入意向锁锁不会互斥。 45.意向锁是什么知道吗
意向锁是一个表级锁不要和插入意向锁搞混。意向锁的出现是为了支持 InnoDB 的多粒度锁它解决的是表锁和行锁共存的问题。当我们需要给一个表加表锁的时候我们需要根据去判断表中有没有数据行被锁定以确定是否能加成功。假如没有意向锁那么我们就得遍历表中所有数据行来判断有没有行锁有了意向锁这个表级锁之后则我们直接判断一次就知道表中是否有数据行被锁定了。有了意向锁之后要执行的事务A在申请行锁写锁之前数据库会自动先给事务A申请表的意向排他锁。当事务 B 去申请表的互斥锁时就会失败因为表上有意向排他锁之后事务 B 申请表的互斥锁时会被阻塞。 46.MySQL 的乐观锁和悲观锁了解吗
悲观锁Pessimistic Concurrency Control
悲观锁认为被它保护的数据是极其不安全的每时每刻都有可能被改动一个事务拿到悲观锁后其他任何事务都不能对该数据进行修改只能等待锁被释放才可以执行。
数据库中的行锁表锁读锁写锁均为悲观锁。
乐观锁Optimistic Concurrency Control
乐观锁认为数据的变动不会太频繁。
乐观锁通常是通过在表中增加一个版本version或时间戳timestamp来实现其中版本最为常用。
事务在从数据库中取数据时会将该数据的版本也取出来v1当事务对数据变动完毕想要将其更新到表中时会将之前取出的版本v1与数据中最新的版本v2相对比如果v1v2那么说明在数据变动期间没有其他事务对数据进行修改此时就允许事务对表中的数据进行修改并且修改时version会加1以此来表明数据已被变动。
如果v1不等于v2那么说明数据变动期间数据被其他事务改动了此时不允许数据更新到表中一般的处理办法是通知用户让其重新操作。不同于悲观锁乐观锁通常是由开发者实现的。
47.MySQL遇到过死锁问题吗你是如何解决的
排查死锁的一般步骤是这样的
1查看死锁日志 show engine innodb status
2找出死锁 sql
3分析 sql 加锁情况
4模拟死锁案发
5分析死锁日志
6分析死锁结果
当然这只是一个简单的流程说明实际上生产中的死锁千奇百怪排查和解决起来没那么简单。
事务
48.MySQL 事务的四大特性说一下
ACIDAtomicity、Consistency、Isolation、Durability 原子性事务作为一个整体被执行包含在其中的对数据库的操作要么全部被执行要么都不执行。一致性指在事务开始之前和事务结束以后数据不会被破坏假如A账户给B账户转10块钱不管成功与否A和B的总金额是不变的。隔离性多个事务并发访问时事务之间是相互隔离的即一个事务不影响其它事务运行效果。简言之就是事务之间是进水不犯河水的。持久性表示事务完成以后该事务对数据库所作的操作更改将持久地保存在数据库之中。
49.那 ACID 靠什么保证的呢
事务的隔离性是通过数据库锁的机制实现的。事务的一致性由 undo log 来保证undo log 是逻辑日志记录了事务的 insert、updatedeltete 操作回滚的时候做相反的 delete、update、insert 操作来恢复数据。事务的原子性和持久性由redo log来保证redolog被称作重做日志是物理日志事务提交的时候必须先将事务的所有日志写入 redo log 持久化到事务的提交操作才算完成。 50.事务的隔离级别有哪些MySQL的默认隔离级别是什么 事务的四个隔离级别
读未提交Read Uncommitted读已提交Read Committed可重复读Repeatable Read串行化Serializable
MySQL 默认的事务隔离级别是可重复读Repeatable Read。
51.什么是幻读脏读不可重复读呢
事务 A、B 交替执行事务 A 读取到事务 B 未提交的数据这就是脏读。在一个事务范围内两个相同的查询读取同一条记录却返回了不同的数据这就是不可重复读。事务 A 查询一个范围的结果集另一个并发事务 B 往这个范围中插入/删除了数据并静悄悄地提交然后事务 A 再次查询相同的范围两次读取得到的结果集不一样了这就是幻读。
不同的隔离级别在并发事务下可能会发生的问题 详细内容讲解可参考一文详解幻读、脏读和不可重复读_weixin_45483322的博客-CSDN博客
52.事务的各个隔离级别都是如何实现的
读未提交
读未提交就不用多说了采取的是读不加锁原理。
事务读不加锁不阻塞其他事务的读和写事务写阻塞其他事务写但不阻塞其他事务读
读取已提交可重复读
读取已提交和可重复读级别利用了ReadView和MVCC也就是每个事务只能读取它能看到的版本ReadView。
READ COMMITTED每次读取数据前都生成一个 ReadViewREPEATABLE READ在第一次读取数据时生成一个 ReadView
串行化
串行化的实现采用的是读写都加锁的原理。
串行化的情况下对于同一行事务写会加写锁读会加读锁。当出现读写锁冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行。
53.MVCC了解吗怎么实现的
MVCCMulti Version Concurrency Control中文名是多版本并发控制简单来说就是通过维护数据历史版本从而解决并发访问情况下的读一致性问题。
关于它的实现要抓住几个关键点隐式字段、undo日志、版本链、快照读当前读、Read View。
版本链
对于 InnoDB 存储引擎每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR
DB_TRX_ID事务ID每次修改时都会把该事务ID复制给DB_TRX_IDDB_ROLL_PTR回滚指针指向回滚段的undo日志。 假如有一张 user 表表中只有一行记录当时插入的事务id为80。此时该条记录的示例图如下 接下来有两个 DB_TRX_ID 分别为 100、200 的事务对这条记录进行 update 操作整个过程如下 由于每次变动都会先把undo日志记录下来并用DB_ROLL_PTR指向undo日志地址。因此可以认为对该条记录的修改日志串联起来就形成了一个版本链版本链的头节点就是当前记录最新的值。如下 ReadView 对于Read Committed和Repeatable Read隔离级别来说都需要读取已经提交的事务所修改的记录也就是说如果版本链中某个版本的修改没有提交那么该版本的记录时不能被读取的。所以需要确定在Read Committed和Repeatable Read隔离级别下版本链中哪个版本是能被当前事务读取的。于是就引入了ReadView 这个概念来解决这个问题。 Read View就是事务执行快照读时产生的读视图相当于某时刻表记录的一个快照通过这个快照我们可以获取 m_ids表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。min_trx_id表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id也就是 m_ids中的最小值。max_trx_id表示生成ReadView时系统中应该分配给下一个事务的id值。creator_trx_id表示生成该 ReadView 的事务的 事务 id
有了这个 ReadView这样在访问某条记录时只需要按照下边的步骤判断记录的某个版本是否可见
如果被访问版本的DB_TRX_ID 属性值与 ReadView中的 creator_trx_id 值相同意味着当前事务在访问它自己修改过的记录所以该版本可以被当前事务访问。如果被访问版本的 DB_TRX_ID 属性值小于 ReadView 中的 min_trx_id 值表明生成该版本的事务在当前事务生成 ReadView 前已经提交所以该版本可以被当前事务访问。如果被访问版本的 DB_TRX_ID 属性值大于 ReadView 中的 max_trx_id 值表明生成该版本的事务在当前事务生成 ReadView 后才开启所以该版本不可以被当前事务访问。如果被访问版本的 DB_TRX_ID 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间那就需要判断一下trx_id属性值是不是在m_ids列表中如果在说明创建ReadView时生成该版本的事务还是活跃的该版本不可以被访问如果不在说明创建 ReadView 时生成该版本的事务已经被提交该版本可以被访问。
如果某个版本的数据对当前事务不可见的话那就顺着版本链找到下一个版本的数据继续按照上边的步骤判断可见性依此类推直到版本链中的最后一个版本。如果最后一个版本也不可见的话那么就意味着该条记录对该事务完全不可见查询结果就不包含该记录。
在MySQL中READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView 的时机不同。 READ COMMITTED是每次读取数据前都生成一个ReadView这样就能保证自己每次都能读到其它事务提交的数据 REPEATABLE READ是在第一次读取数据时生成一个ReadView这样就能保证后续读取的结果完全一致。
高可用/高性能
54.数据库读写分离了解吗
读写分离的基本原理是将数据库读写操作分散到不同的节点上下面是基本架构图 读写分离的基本实现是
数据库服务器搭建主从集群一主一从、一主多从都可以。数据库主机负责读写操作从机只负责读操作。数据库主机通过复制将数据同步到从机每台数据库服务器都存储了所有的业务数据。业务服务器将写操作发给数据库主机将读操作发给数据库从机。
55.那读写分离的分配怎么实现呢
将读写操作区分开来然后访问不同的数据库服务器一般有两种方式程序代码封装和中间件封装。
程序代码封装
程序代码封装指在代码中抽象一个数据访问层有的文章也称这种方式为中间层封装实现读写操作分离和数据库服务器连接的管理。例如基于 Hibernate 进行简单封装就可以实现读写分离 目前开源的实现方案中淘宝的TDDLTaobao Distributed Data Layer外号头都大了是比较有名的。
中间件封装
中间件封装指的是独立一套系统出来实现读写操作分离和数据库服务器连接的管理。中间件对业务服务器提供SQL 兼容的协议业务服务器无须自己进行读写分离。
对于业务服务器来说访问中间件和访问数据库没有区别事实上在业务服务器看来中间件就是一个数据库服务器。
其基本架构是 56.主从复制原理了解吗
master 数据写入更新 binlogmaster 创建一个 dump 线程转存线程向 slave 推送 binlogslave连接到master的时候会创建一个IO线程接收binlog并记录到relay log中继日志中slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行完成同步slave 记录自己的 binglog 57.主从同步延迟怎么处理
主从同步延迟的原因
一个服务器开放N个链接给客户端来连接的这样有会有大并发的更新操作但是从服务器的里面读取binlog的线程仅有一个当某个SQL在从服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致主服务器的SQL大量积压未被同步到从服务器里。这就导致了主从不一致也就是主从延迟。
主从同步延迟的解决办法
解决主从复制延迟有几种常见的方法
写操作后的读操作指定发给数据库主服务器
例如注册账号完成后登录时读取账号的读操作也发给数据库主服务器。这种方式和业务强绑定对业务的侵入和影响较大如果哪个新来的程序员不知道这样写代码就会导致一个bug。
读从机失败后再读一次主机
这就是通常所说的二次读取二次读取和业务无绑定只需要对底层数据库访问的API进行封装即可实现代价较小不足之处在于如果有很多二次读取将大大增加主机的读操作压力。例如黑客暴力破解账号会导致大量的二次读取操作主机可能顶不住读操作的压力从而崩溃。
关键业务读写操作全部指向主机非关键业务采用读写分离
例如对于一个用户管理系统来说注册登录的业务读写操作全部访问主机用户的介绍、爰好、等级等业务可以采用读写分离因为即使用户改了自己的自我介绍在查询时却看到了自我介绍还是旧的业务影响与不能登录相比就小很多还可以忍受。
58.你们一般是怎么分库的呢
垂直分库以表为依据按照业务归属不同将不同的表拆分到不同的库中。 水平分库以字段为依据按照一定策略hashrange等将一个库中的数据拆分到多个库中。 59.那你们是怎么分表的
水平分表以字段为依据按照一定策略hash、range 等将一个表中的数据拆分到多个表中。垂直分表以字段为依据按照字段的活跃性将表中字段拆到不同的表主表和扩展表中。 60.水平分表有哪几种路由方式
什么是路由呢就是数据应该分到哪一张表。
水平分表主要有三种路由方式
范围路由选取有序的数据列例如整形、时间戳等作为路由的条件不同分段分散到不同的数据库表中。
我们可以观察一些支付系统发现只能查一年范围内的支付记录这个可能就是支付公司按照时间进行了分表。 范围路由设计的复杂点主要体现在分段大小的选取上分段太小会导致切分后子表数量过多增加维护复杂度分段太大可能会导致单表依然存在性能问题一般建议分段大小在100万至2000万之间具体需要根据业务选取合适的分段大小。
范围路由的优点是可以随着数据的增加平滑地扩充新的表。例如现在的用户是100万如果增加到1000万只需要增加新的表就可以了原有的数据不需要动。范围路由的一个比较隐含的缺点是分布不均匀假如按照1000万来进行分表有可能某个分段实际存储的数据量只有1000条而另外一个分段实际存储的数据量有900万条。
Hash 路由选取某个列或者某几个列组合也可以的值进行Hash运算然后根据Hash结果分散到不同的数据库表中。
同样以订单id为例假如我们一开始就规划了4个数据库表路由算法可以简单地用id%4的值来表示数据所属的数据库表编号id为12的订单放到编号为50的子表中id为13的订单放到编号为61的字表中。 Hash路由设计的复杂点主要体现在初始表数量的选取上表数量太多维护比较麻烦表数量太少又可能导致单表性能存在问题。而用了Hash路由后增加子表数量是非常麻烦的所有数据都要重分布。Hash路由的优缺点和范围路由基本相反Hash 路由的优点是表分布比较均匀缺点是扩充新的表很麻烦所有数据都要重分布。
配置路由配置路由就是路由表用一张独立的表来记录路由信息。同样以订单 id 为例我们新增一张order_router表这个表包含 orderjd和tablejd两列根据orderjd就可以查询对应的table_id。
配置路由设计简单使用起来非常灵活尤其是在扩充表的时候只需要迁移指定的数据然后修改路由表就可以了。 配置路由的缺点就是必须多查询一次会影响整体性能而且路由表本身如果太大例如几亿条数据性能同样可能成为瓶颈如果我们再次将路由表分库分表则又面临一个死循环式的路由算法选择问题。
61.不停机扩容怎么实现
实际上不停机扩容实操起来是个非常麻烦而且很有风险的操作当然面试回答起来就简单很多。 第一阶段在线双写查询走老库 建立好新的库表结构数据写入久库的同时也写入拆分的新库数据迁移使用数据迁移程序将旧库中的历史数据迁移到新库使用定时任务新旧库的数据对比把差异补齐 第二阶段在线双写查询走新库
完成了历史数据的同步和校验把对数据的读切换到新库 第三阶段旧库下线 旧库不再写入新的数据经过一段时间确定旧库没有请求之后就可以下线老库 62.常用的分库分表中间件有哪些
sharding-jdbcMycat
63.那你觉得分库分表会带来什么问题呢
从分库的角度来讲 事务的问题 使用关系型数据库有很大一点在于它保证事务完整性。而分库之后单机事务就用不上了必须使用分布式事务来解决。 跨库 JOIN 问题 在一个库中的时候我们还可以利用JOIN 来连表查询而跨库了之后就无法使用JOIN 了。此时的解决方案就是在业务代码中进行关联也就是先把一个表的数据查出来然后通过得到的结果再去查另一张表然后利用代码来关联得到最终的结果。这种方式实现起来稍微比较复杂不过也是可以接受的。还有可以适当的冗余一些字段。比如以前的表就存储一个关联ID但是业务时常要求返回对应的Name或者其他字段。这时候就可以把这些字段冗余到当前表中来去除需要关联的操作。还有一种方式就是数据异构通过 binlog 同步等方式把需要跨库 join 的数据异构到 ES 等存储结构中通过 ES进行查询。
从分表的角度来看 跨节点的 countorder bygroup by 以及聚合函数问题 只能由业务代码来实现或者用中间件将各表中的数据汇总、排序、分页然后返回。 数据迁移容量规划扩容等问题 数据的迁移容量如何规划未来是否可能再次需要扩容等等都是需要考虑的问题。 ID 问题 数据库表被切分后不能再依赖数据库自身的主键生成机制所以需要一些手段来保证全局主键唯一。 还是自增只不过自增步长设置一下。比如现在有三张表步长设置为3三张表ID初始值分别是1、2、3这样第一张表的ID增长是1、4、7。第二张表是2、5、8第三张表是3、6、9这样就不会重复了。UUID这种最简单但是不连续的主键插入会导致严重的页分裂性能比较差。分布式 ID比较出名的就是 Twitter 开源的 sonwflake 雪花算法
运维
64.百万级别以上的数据如何删除
关于索引由于索引需要额外的维护成本因为索引文件是单独存在的文件所以当我们对数据的增加修改删除都会产生额外的对索引文件的操作这些操作需要消耗额外的IO会降低增/改/删的执行效率。
所以在我们删除数据库百万级别数据的时候查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
所以我们想要删除百万数据的时候可以先删除索引然后删除其中无用数据删除完成后重新创建索引创建索引也非常快
65.百万千万级大表如何添加字段
当线上的数据库数据量到达几百万、上千万的时候加一个字段就没那么简单因为可能会长时间锁表。
大表添加字段通常有这些做法
通过中间表转换过去
创建一个临时的新表把旧表的结构完全复制过去添加字段再把旧表数据复制过去删除旧表新表命名为旧表的名称这种方式可能回丢掉一些数据。
用pt-online-schema-change
pt-online-schema-change 是 percona 公司开发的一个工具它可以在线修改表结构它的原理也是通过中间表。
先在从库添加 再进行主从切换
如果一张表数据量大且是热表读写特别频繁则可以考虑先在从库添加再进行主从切换切换后再将其他几个节点上添加字段。
66.MySQL数据库cpu飙升的话要怎么处理呢
排查过程
1使用 top 命令观察确定是 mysqld 导致还是其他原因。
2如果是 mysqld 导致的show processlist查看 session 情况确定是不是有消耗资源的 sql 在运行。
3找出消耗高的 sql看看执行计划是否准确索引是否缺失数据量是否太大。
处理
1kill 掉这些线程同时观察 cpu 使用率是否下降
2进行相应的调整比如说加索引、改 sql、改内存参数
3重新跑这些 SQL。
其他情况
也有可能是每个sql消耗资源并不多但是突然之间有大量的session连进来导致cpu飙升这种情况就需要跟应用一起来分析为何连接数会激增再做出相应的调整比如说限制连接数等。 资料来源面渣逆袭MySQL六十六问两万字五十图详解有点六
文章转载自： http://www.morning.lphtm.cn.gov.cn.lphtm.cn http://www.morning.jfbrt.cn.gov.cn.jfbrt.cn http://www.morning.bqwrn.cn.gov.cn.bqwrn.cn http://www.morning.hjjhjhj.com.gov.cn.hjjhjhj.com http://www.morning.plhyc.cn.gov.cn.plhyc.cn http://www.morning.ldzxf.cn.gov.cn.ldzxf.cn http://www.morning.hnhkz.cn.gov.cn.hnhkz.cn http://www.morning.ktrh.cn.gov.cn.ktrh.cn http://www.morning.ykmg.cn.gov.cn.ykmg.cn http://www.morning.kgcss.cn.gov.cn.kgcss.cn http://www.morning.ypklb.cn.gov.cn.ypklb.cn http://www.morning.jmbgl.cn.gov.cn.jmbgl.cn http://www.morning.jydky.cn.gov.cn.jydky.cn http://www.morning.tkyry.cn.gov.cn.tkyry.cn http://www.morning.bmtyn.cn.gov.cn.bmtyn.cn http://www.morning.crsqs.cn.gov.cn.crsqs.cn http://www.morning.sfphz.cn.gov.cn.sfphz.cn http://www.morning.nngq.cn.gov.cn.nngq.cn http://www.morning.dpqwq.cn.gov.cn.dpqwq.cn http://www.morning.zzhqs.cn.gov.cn.zzhqs.cn http://www.morning.hsgxj.cn.gov.cn.hsgxj.cn http://www.morning.tgnr.cn.gov.cn.tgnr.cn http://www.morning.mtmph.cn.gov.cn.mtmph.cn http://www.morning.hrpmt.cn.gov.cn.hrpmt.cn http://www.morning.lbxhy.cn.gov.cn.lbxhy.cn http://www.morning.jfqqs.cn.gov.cn.jfqqs.cn http://www.morning.zyndj.cn.gov.cn.zyndj.cn http://www.morning.xnqwk.cn.gov.cn.xnqwk.cn http://www.morning.ywpwq.cn.gov.cn.ywpwq.cn http://www.morning.gmnmh.cn.gov.cn.gmnmh.cn http://www.morning.rlqqy.cn.gov.cn.rlqqy.cn http://www.morning.twwzk.cn.gov.cn.twwzk.cn http://www.morning.dnqlba.cn.gov.cn.dnqlba.cn http://www.morning.jyzqn.cn.gov.cn.jyzqn.cn http://www.morning.thzwj.cn.gov.cn.thzwj.cn http://www.morning.zsleyuan.cn.gov.cn.zsleyuan.cn http://www.morning.nmfml.cn.gov.cn.nmfml.cn http://www.morning.znmwb.cn.gov.cn.znmwb.cn http://www.morning.zdfrg.cn.gov.cn.zdfrg.cn http://www.morning.2d1bl5.cn.gov.cn.2d1bl5.cn http://www.morning.zhoer.com.gov.cn.zhoer.com http://www.morning.ctlbf.cn.gov.cn.ctlbf.cn http://www.morning.gbsfs.com.gov.cn.gbsfs.com http://www.morning.hrdx.cn.gov.cn.hrdx.cn http://www.morning.brsgw.cn.gov.cn.brsgw.cn http://www.morning.qfdyt.cn.gov.cn.qfdyt.cn http://www.morning.zxfr.cn.gov.cn.zxfr.cn http://www.morning.jiuyungps.com.gov.cn.jiuyungps.com http://www.morning.hmsong.com.gov.cn.hmsong.com http://www.morning.lqljj.cn.gov.cn.lqljj.cn http://www.morning.ydrn.cn.gov.cn.ydrn.cn http://www.morning.qptbn.cn.gov.cn.qptbn.cn http://www.morning.mcjrf.cn.gov.cn.mcjrf.cn http://www.morning.kdnrp.cn.gov.cn.kdnrp.cn http://www.morning.bwmm.cn.gov.cn.bwmm.cn http://www.morning.hsrpc.cn.gov.cn.hsrpc.cn http://www.morning.qsmmq.cn.gov.cn.qsmmq.cn http://www.morning.rbnj.cn.gov.cn.rbnj.cn http://www.morning.pycpt.cn.gov.cn.pycpt.cn http://www.morning.lkfsk.cn.gov.cn.lkfsk.cn http://www.morning.xfyjn.cn.gov.cn.xfyjn.cn http://www.morning.cwyrp.cn.gov.cn.cwyrp.cn http://www.morning.mrttc.cn.gov.cn.mrttc.cn http://www.morning.hhpbj.cn.gov.cn.hhpbj.cn http://www.morning.kwwkm.cn.gov.cn.kwwkm.cn http://www.morning.pgcmz.cn.gov.cn.pgcmz.cn http://www.morning.nkjnr.cn.gov.cn.nkjnr.cn http://www.morning.zwfgh.cn.gov.cn.zwfgh.cn http://www.morning.rdxp.cn.gov.cn.rdxp.cn http://www.morning.rgsgk.cn.gov.cn.rgsgk.cn http://www.morning.lmpfk.cn.gov.cn.lmpfk.cn http://www.morning.xmhpq.cn.gov.cn.xmhpq.cn http://www.morning.jsrnf.cn.gov.cn.jsrnf.cn http://www.morning.mrfbp.cn.gov.cn.mrfbp.cn http://www.morning.lxfqc.cn.gov.cn.lxfqc.cn http://www.morning.nchsz.cn.gov.cn.nchsz.cn http://www.morning.tgfjm.cn.gov.cn.tgfjm.cn http://www.morning.btlsb.cn.gov.cn.btlsb.cn http://www.morning.gtmgl.cn.gov.cn.gtmgl.cn http://www.morning.dbxss.cn.gov.cn.dbxss.cn
