MySQL高频面试题汇总 1、Select 语句完整的执行顺序 SQL Select 语句完整的执行顺序： （1）from 子句组装来自不同数据源的数据； （2）where 子句基于指定的条件对记录行进行筛选； （3）group by 子句将数据划分为多个分组； （4）使用聚集函数进行计算； （5）使用 having 子句筛选分组； （6）计算所有的表达式； （7）select 的字段； （8）使用order by 对结果集进行排序。 2、MySQL事务 事务的基本要素（ACID）原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。 MySQL事务隔离级别：
事务的并发问题脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。如何解决脏读、幻读、不可重复读脏读： 隔离级别为 读提交、可重复读、串行化可以解决脏读不可重复读：隔离级别为可重复读、串行化可以解决不可重复读幻读：隔离级别为串行化可以解决幻读、通过MVCC + 区间锁可以解决幻读 小结： 不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表 3、MyISAM和InnoDB的区别
4、悲观锁和乐观锁的怎么实现 悲观锁：select…for update是MySQL提供的实现悲观锁的方式。 例如：select price from item where id=100 for update 此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select price from items where id=100 for update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。MySQL有个问题是select…for update语句执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。 乐观锁：乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。 利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。 举例： //1: 查询出商品信息 select (quantity,version) from items where id=100; //2: 根据商品信息生成订单 insert into orders(id,item_id) values(null,100); //3: 修改商品的库存 update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version}; 5、聚簇索引与非聚簇索引区别 都是B+树的数据结构 聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的 非聚簇索引叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本书的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。 优势: 1、查询通过聚簇索引可以直接数据，相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高 2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的 3、聚簇索引适合用在排序的场合，非聚簇索引不适合 劣势; 1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(pagesplit)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZETABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片 2、表因为使用uuId(随机ID)作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键 3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值，过长的主键值，会导致非叶子节点占用占用更多的物理空间 6、什么情况下mysql会索引失效 失效条件：where 后面使用函数使用or条件模糊查询 %放在前边类型转换组合索引 （最佳左前缀匹配原则） SQL #查询条件用到了计算或者函数 explain SELECT * from test_slow_query where age = 20 explain SELECT * from test_slow_query where age +10 = 30 #模糊查询 EXPLAIN SELECT * from test_slow_query where NAME like ‘%吕布’ EXPLAIN SELECT * from test_slow_query where NAME like ‘%吕布%’ EXPLAIN SELECT * from test_slow_query where NAME like ‘吕布&’ #用到了or条件 EXPLAIN SELECT * from test_slow_query where NAME = ‘吕布’ or name = “aaa” #类型不匹配查询 explain SELECT * from test_slow_query where NAME = 11 explain SELECT * from test_slow_query where NAME = ’11’ 7、B+tree 与 B-tree区别 原理:分批次的将磁盘块加载进内存中进行检索,若查到数据,则直接返回,若查不到,则释放内存,并重新加载同等数据量的索引进内存,重新遍历 结构: 数据 向下的指针 指向数据的指针 特点: 1，节点排序 2 .一个节点了可以存多个索，多个索也排序了
结构: 数据 向下的指针 特点: 1.拥有B树的特点 2.叶子节点之间有指针 3.非叶子节点上的素在叶子节点上都冗余了，也就是叶子节点中存储了所有的素，并且排好顺序
从结构上看,B+Tree 相较于 B-Tree 而言 缺少了指向数据的指针 也就红色小方块; Mysq|索引使用的是B+树，因为索引是用来加快查询的，而B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个素，从而可以使得B+树的高度不会太高，在Mysql中一个Innodb页就是一个B+树节点，一个Innodb页默认16kb，所以一般情况下一颗两层的B+树可以存2000万行左右的数据，然后通过利用B+树叶子节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等SQL语句 8、以MySQL为例Linux下如何排查问题 类似提问方式:如果线上环境出现问题比如网站卡顿重则瘫痪 如何是好? —>linux—>mysql/redis/nacos/sentinel/sluth—>可以从以上提到的技术点中选择一个自己熟悉单技术点进行分析 以mysql为例 1,架构层面 是否使用主从 2,表结构层面 是否满足常规的表设计规范(大量冗余字段会导致查询会变得很复杂) 3,sql语句层面() 前提:由于慢查询日志记录默认是关闭的,所以开启数据库mysql的慢查询记录 的功能 从慢查询日志中去哪些sql语句时慢查询 默认10S ,从中到sql语句进行分析 3.1 explain 分析一条sql
Id:执行顺序 如果单表的话,无参考价值 如果是关联查询,会据此判断主表 从表 Select_type:simple Table:表 Type: ALL 未创建索引 、const、 常量ref其他索引 、eq_ref 主键索引、 Possible_keys Key 实际是到到索引到字段 Key_len 索引字段数据结构所使用长度 与是否有默认值null 以及对应字段到数据类型有关，有一个理论值 有一个实际使用值也即key_len的值 Rows 检索的行数 与查询返回的行数无关 Extra 常见的值：usingfilesort 使用磁盘排序算法进行排序，事关排序 分组 的字段是否使用索引的核心参考值 还可能这样去提问：sql语句中哪些位置适合建索引/索引建立在哪个位置 Select id,name,age from user where id=1 and name=”xxx” order by age 总结: 查询字段 查询条件(最常用) 排序/分组字段 补充:如何判断是数据库的问题?可以借助于top命令
9、如何处理慢查询 在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。 慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是加载了不需要的数据列?还是数据量太大? 所以优化也是针对这三个方向来的 首先分析语句，看看是否加载了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。 具体处理流程 （阿里云RDS为例） 1.开启慢查询设置
2.日志管理导出慢查询文件
3.测试环境通过explain执行sql，主要关心以下字段
type：连接类型 key： MYSQL使用的索引 rows：显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引 extra：该列包含MySQL解决查询的详细信息。 10、数据库分表操作 水平分表 步长法：1000万一张表拆分 取模法：举例：根据用户id取模落入不能的表 垂直分表：大表拆小表。商品信息 spu_info spu_image … 可以说使用Mycat或者ShardingSphere等中间件来做，具体怎么做就要结合具体的场景进行分析了。 11、MySQL优化 （1）尽量选择较小的列 （2）将where中用的比较频繁的字段建立索引 （3）select子句中避免使用‘*’ （4）避免在索引列上使用计算、not in 和<>等操作 （5）当只需要一行数据的时候使用limit 1 （6）保证单表数据不超过200W，适时分割表。针对查询较慢的语句，可以使用explain 来分析该语句具体的执行情况。 （7）避免改变索引列的类型。 （8）选择最有效的表名顺序，from字句中写在最后的表是基础表，将被最先处理，在from子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。 （9）避免在索引列上面进行计算。 （10）尽量缩小子查询的结果 12、SQL语句优化案例 例1：where 子句中可以对字段进行 null 值判断吗？ 可以，比如 select id from t where num is null 这样的 sql 也是可以的。但是最好不要给数据库留NULL，尽可能的使用 NOT NULL 填充数据库。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段，null 不占用空间。可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：select id from t where num= 0。 例2：如何优化?下面的语句？ select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10 优化为：select * from (select * from admin where admin_id>10) T1 lef join log on T1.admin_id = log.admin_id。 使用 JOIN 时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小如果有条件应该放到左边先处理， right join 同理反向），同时尽量把牵涉到多表联合的查询拆分多个 query（多个连表查询效率低，容易到之后锁表和阻塞）。 例3：limit 的基数比较大时使用 between 例如：select * from admin order by admin_id limit ,10 优化为：select * from admin where admin_id between and order by admin_id。 例4：尽量避免在列上做运算，这样导致索引失效 例如：select * from admin where year(admin_time)>2014 优化为： select * from admin where admin_time> ‘2014-01-01′ 13、你们公司有哪些数据库设计规范 （一）基础规范 1、表存储引擎必须使用InnoD，表字符集默认使用utf8，必要时候使用utf8mb4 解读： （1）通用，无乱码风险，汉字3字节，英文1字节 （2）utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它 2、禁止使用存储过程，视图，触发器，Event 解读： （1）对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层 （2）调试，排错，迁移都比较困难，扩展性较差 3、禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径 4、禁止在线上环境做数据库压力测试 5、测试，开发，线上数据库环境必须隔离 （二）命名规范 1、库名，表名，列名必须用小写，采用下划线分隔 解读：abc，Abc，ABC都是给自己埋坑 2、库名，表名，列名必须见名知义，长度不要超过32字符 解读：tmp，wushan谁知道这些库是干嘛的 3、库备份必须以bak为前缀，以日期为后缀 4、从库必须以-s为后缀 5、备库必须以-ss为后缀 （三）表设计规范 1、单实例表个数必须控制在2000个以内 2、单表分表个数必须控制在1024个以内 3、表必须有主键，推荐使用UNSIGNED整数为主键 潜在坑：删除无主键的表，如果是row模式的主从架构，从库会挂住 4、禁止使用外键，如果要保证完整性，应由应用程式实现 解读：外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈 5、建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据 （四）列设计规范 1、根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节 2、根据业务区分使用char/varchar 解读： （1）字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高 （2）字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间 3、根据业务区分使用datetime/timestamp 解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime 4、必须把字段定义为NOT NULL并设默认值 解读： （1）NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化 （2）NULL需要更多的存储空间 （3）NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑 5、使用INT UNSIGNED存储IPv4，不要用char(15) 6、使用varchar(20)存储手机号，不要使用整数 解读： （1）牵扯到国家代号，可能出现+/-/()等字符，例如+86 （2）手机号不会用来做数学运算 （3）varchar可以模糊查询，例如like ‘138%’ 7、使用TINYINT来代替ENUM 解读：ENUM增加新值要进行DDL操作 （五）索引规范 1、唯一索引使用uniq_[字段名]来命名 2、非唯一索引使用idx_[字段名]来命名 3、单张表索引数量建议控制在5个以内 解读： （1）互联网高并发业务，太多索引会影响写性能 （2）生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引 （3）异常复杂的查询需求，可以选择ES等更为适合的方式存储 4、组合索引字段数不建议超过5个 解读：如果5个字段还不能极大缩小row范围，八成是设计有问题 5、不建议在频繁更新的字段上建立索引 6、非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引 解读：踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？ 7、理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c) （六）SQL规范 1、禁止使用select *，只必要字段 解读： （1）select *会增加cpu/io/内存/带宽的消耗 （2）指定字段能有效利用索引覆盖 （3）指定字段查询，在表结构变更时，能保证对应用程序无影响 2、insert必须指定字段，禁止使用insert into T values() 解读：指定字段插入，在表结构变更时，能保证对应用程序无影响 3、隐式类型转换会使索引失效，导致全表扫描 4、禁止在where条件列使用函数或者表达式 解读：导致不能命中索引，全表扫描 5、禁止负向查询以及%开头的模糊查询 解读：导致不能命中索引，全表扫描 6、禁止大表JOIN和子查询 7、同一个字段上的OR必须改写问IN，IN的值必须少于50个 8、应用程序必须捕获SQL异常 解读：方便定位线上问题 说明：本规范适用于并发量大，数据量大的典型互联网业务，可直接参考。 14、有没有设计过数据表?你是如何设计的
15、常见面试SQL 例1： 用一条SQL语句查询出每门课都大于80分的学生姓名 name kecheng fenshu 张三 语文 81 张三 数学 75 李四 语文 76 李四 数学 90 王五 语文 81 王五 数学 100 王五 英语 90 答1： select distinct name from table where name not in (select distinct name from table where fenshu<=80) 答2： select name from table group by name having min(fenshu)>80 例2： 学生表 如下: 自动编号 学号 姓名 课程编号 课程名称 分数 1 张三 0001 数学 69 2 李四 0001 数学 89 3 张三 0001 数学 69 删除除了自动编号不同，其他都相同的学生冗余信息 答： delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数) 例3： 一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合. 答： 1.select a.name, b.name 2.from team a, team b 3.where a.name < b.name 例4： 怎么把这样一个表 year month amount 1991 1 1.1 1991 2 1.2 1991 3 1.3 1991 4 1.4 1992 1 2.1 1992 2 2.2 1992 3 2.3 1992 4 2.4 查成这样一个结果 year m1 m2 m3 m4 1991 1.1 1.2 1.3 1.4 1992 2.1 2.2 2.3 2.4 答： 1.select year, 2.(select amount from aaa m where month=1 and m.year=aaa.year) as m1, 3.(select amount from aaa m where month=2 and m.year=aaa.year) as m2, 4.(select amount from aaa m where month=3 and m.year=aaa.year) as m3, 5.(select amount from aaa m where month=4 and m.year=aaa.year) as m4 6.from aaa group by year 例5： 说明：复制表(只复制结构,源表名：a新表名：b) 答： SQL: select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容) ORACLE: 1.create table b 2.As 3.Select * from a where 1=2 [<>（不等于）(SQL Server Compact) 比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。] 例6： 原表： courseid coursename score 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 为了便于阅读,查询此表后的结果显式如下(及格分数为60): courseid coursename score mark 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass 写出此查询语句 答： select courseid, coursename ,score ,if(score>=60, “pass”,”fail”) as mark from course 例7： 表名：购物信息 购物人 商品名称 数量 A 甲 2 B 乙 4 C 丙 1 A 丁 2 B 丙 5 给出所有购入商品为两种或两种以上的购物人记录 答： select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2); 例8： info 表 date result 2005-05-09 win 2005-05-09 lose 2005-05-09 lose 2005-05-09 lose 2005-05-10 win 2005-05-10 lose 2005-05-10 lose 如果要生成下列结果, 该如何写sql语句? date win lose 2005-05-09 2 2 2005-05-10 1 2 答1： select date, sum(case when result = “win” then 1 else 0 end) as “win”, sum(case when result = “lose” then 1 else 0 end) as “lose” from info group by date; 答2： 1.select a.date, a.result as win, b.result as lose 2.from 3.(select date, count(result) as result from info where result = “win” group by date) as a 4.join 5.(select date, count(result) as result from info where result = “lose” group by date) as b 6.on a.date = b.date; 例9 mysql 创建了一个联合索引(a,b,c) 以下 索引生效 的是(1,2,4） 1、where a = 1 and b = 1 and c =1 2、where a = 1 and c = 1 3、where b = 1 and c = 1, 4、where b = 1 and a =1 and c = 1
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/49313.html
