Skip to content

Files

Latest commit

 

History

History
287 lines (266 loc) · 18.2 KB

MySQL.md

File metadata and controls

287 lines (266 loc) · 18.2 KB

MySQL可能会问的问题

1.如何优化慢查询?

LICSLAN回答:

 A.定位慢查询日志,在建库的时候开启慢查询时间规定,在配置文件中开启时间限制,
   日志文件存储设置,统计慢查询条数设置。
 B.explian select xxxx  语句  可以看到该语句内容  type是什么 当type为index or all 时
   extra 字段为 Using filesort Using temporary  就注意是否要优化SQL判断是否是慢查询了
 C.如果时慢查询,那么就要根据业务场景来修改SQL了或者给相应的字段加索引了

2.最左匹配原则是怎么样的?

LICSLAN回答:

 当建立了联合索引时 比如顺序时(abcd)时
 如果是 select  * from tablename where a ='' and b='' and ='' and =''  会走索引 
 如果是 select * from tablename where c ='' and b='' and d='' and a='' 也会走索引 
 如果是 select  * from tablename where b='' and c='' and d='' 那么就不会走索引了 注意喔
 如果是 select  * from tablename where ... 当后面的where语句遇到 in < > bettewn 等字段也不会走索引      

3.密集索引和稀疏索引的区别?

LICSLAN回答:

 密集索引:密集索引文件中的每个搜索码值都对应一个索引值
 稀疏索引:稀疏索引文件只为索引码的某些值建立索引项,不是每个都建立

4.为什么要使用索引?

LICSLAN回答:

 使用索引后就不会走全表查询了,提高了查询效率

5.什么样的信息能成为索引?

LICSLAN回答:

 主键 唯一键 就是能明显区别信息的字段  当然字段内容也不宜过大的信息     

6.索引的数据结构?

LICSLAN回答:

 主流:B+tree  较少:hash bitMap MySQL不支持hash Myisam,innodb不支持hash 
 数据库面试有哪些要总结的呢?
 关系型数据库
 	1.架构
 	2.索引
 	3.锁
 	4.语法
 	5.理论范式  123
 		(1)取原始的1NF关系投影,消去非主属性对键的部分函数依赖,从而产生一组2NF关 
 		(2)取2NF关系的投影,消去非主属性对键的传递函数依赖,产生一组3NF关系。 
 		(3)取这些3NF的投影,消去决定因素不是键的函数依赖。产生一组BCNF关系。 
 		(4)取这些BCNF关系的投影,消去其中不是函数依赖的非平多值依赖,产生一组4NF关系。
 		 数据库范式也分为1NF,2NF,3NF,BCNF,4NF,5NF。一般在我们设计关系型数据库的时候,
 		 最多考虑到BCNF就够。符合高一级范式的设计,必定符合低一级范式,例如符合2NF的关系模式,
 		 必定符合1NF。
 		 规范化程度越高,分解就越细,所得关系的数据冗杂就越小,异常情况也就越少,但同时也增大
 		 了系统对数据检索的开销,降低了数据检索的效率。系统只有对这些细分的关系进行自然连接,
 		 才能获取所需的信息,而连接操作所需的系统资源和开销是比较大的,因此,规范化程度越高的
 		 关系模式并非是最好的。 
 
 
 	如何设计一个关系型数据库?
 	设计一个数据库就和设计一个软件一样  要知道划分多少个模块
 	RDBMS
 		1.存储模块  机械硬盘/ssd  文件系统
 		2.程序实例 
 			1.存储管理模块  如何优化存储效能  加载内存中去做  io读取 是性能瓶颈   
 			  和内存相比 不是一个量级的  尽量减小io  用块或者页存储数据 一次读取多条数据  
 			  多个块 多个页		
 			2.缓存机制		
 			3。SQL解析模块  SQL缓存到缓存里   算法里面又淘汰机制  
 			4.日志管理 binlog
 			5.权限划分(BDA管理)
 			6.容灾机制  异常机制 数据库挂了 该如何恢复 恢复到什么程度
 			7.索引管理**** 重点   提高查询速度  最能突出数据库的模块
 			8.锁管理  **** 重点   提高并发      最能突出数据库的模块
 
 	索引常见问题
 		1.为什么要使用索引
 		2.什么样的信息能成为索引
 		3.索引的数据结构
 		4.密集索引和稀疏索引的区别		
 
 			对于1:首先不使用索引 全表扫描 如果数据量较小几十行数据加载到内存里面 性能要优于
 			      使用索引的情况  如果是个大表含有大量数据
 				  这个时候就要用到索引了  这个时候要尽量避免全表的操作的SQL  
 				  索引的灵感来自于字典
 			对于2:自然是能把要查找的数据限定在一定的范围内的关键信息 如主键id	
 			      唯一键已经普通键
 			对于3:自然是能提高查询速度的数据结构啦 
 						生成索引建立二叉查找树进行二分查找  它复杂一点的变种的 
 						平衡二叉树(任意左右节点的高度均不超过1  O(logn)) 红黑树
 						生成索引建立B-Tree结构进行查找 (平衡多路查找树)   
 						O(logn)减小时间复杂度 和 减少io  尽可能存储更多数据  
 						和 尽可能减少io次数(树的高度会减小)
 							1.根节点至少包括2个孩子
 							2.树中每个节点最多含有m个孩子(m>=2)
 							3.除根节点和叶子节点,其他每个节点至少有 ceil(m/2)孩子
 							4.所有叶子节点都位于同一层
 							5.假设每个非终端节点中包含有N 个关键字信息,其中
 								a)Ki (i=1...n)为关键字,且关键字按顺序排序K(i-1)<Ki
 								b)关键字的个数N必须满足:[ceil(m/2)-1]<=N<=m-1
 								c)非叶子节点的执政:P[1],p[2],...,p[M];其中p[1]指向关键字小于K[1]
 								  的子树,p[M]指向关键字大于K[M-1]的子树,其他p[i]指向关键字属于
 								  (K[i-1],K[i])的子树
 						生成索引建立B+-Tree结构进行查找
 							比B-Tree更好的索引替代品
 							B+树是B树的变体,其定义基本与B树相同,除了:
 								非叶子节点的子树指针与关键子个数相同
 								非叶子节点的子树指针P[i],指向关键字值[K[i],K[i+])的子树
 								非叶子节点仅用来索引,数据都保存在;叶子节点中
 								所有叶子节点均有一个链指针指向下一个叶子结点  支持范围统计
 							B+Tree更适合用来做存储索引原因:
 								A.B+Tree 的磁盘读写代价更低
 								B.B+Tree 的查询效率更加稳定
 								C.B+Tree 更有利于对数据库的扫描
 						生成索引建立Hsah结构进行查找
 							优点:查询效率高 高于B+Tree
 							缺点: 
 							     仅仅能满足“=” “IN”,不能使用范围查询
 							     无法被用来避免数据的排序操作
 							     不能利用部分索引键查询
 							     不能避免表扫描
 							     遇到大量Hash值相等的情况后性能并一定就会比B-Tree索引高
 						BitMap 索引 位图索引(男女性别之类的简单统计)	不适合高并发的索引  
 			对于4:
 				密集索引文件中的每个搜索码值都对应一个索引值
 				稀疏索引文件只为索引码的某些值建立索引项
 				MySQL 存储引擎:
 						MyISAM 不管是主键索引或是唯一索引或普通索引均属于稀疏索引
 						Innodb 有且仅有一个密集索引	
 								若一个主键被定义,该主键则作为密集索引
 								若没有主键被定义,该表的第一个唯一非空索引则作为密集索引
 								若不满足以上条件,innodb内部会生产一个隐藏主键(密集索引)	
 								非主键索引存储相关键位和其对应的主键值包含2次查找	   
 			而MySQL的索引最终是		B+-Tree	 来实现的
 
 			对于索引学习后衍生出来的问题,以MySQL为例子:
 				1.如何定位并优化慢查询SQL
 				2.联合索引的最左匹配原则的成因
 				3.索引是建立得越多越好吗
 				对于1:
 					主要考察是否做过SQL优化
 					具体场景具体分析,只提出大致思路
 					 根据慢日志定位慢查询SQL
 					 	show variables like '%quer%'
 					 	show status like '%slow_queries%'
 					 	set global slow_query_log = on;
 					 	set global long_query_time =1;
 					 	建议改MySQL配置文件永久生效
 					 使用explain等工具分析SQL
 					 	explain select * from xxx .....
 					 	explain 关键字含义
 					 		type: systme>const>eq_ref>ref>.....>index>all(index & all 表明走的是全表扫描,可以优化)
 					 			从前到后越来越慢
 					 		extra: 中出现一下2项意味着MySQL根本不能使用索引,效率会受到重大的影响,需要优化SQL
 					 		Using filesort Using temporary 表明需要优化SQL	
 					 修改SQL或者尽量让SQL走走索引
 					 		给关键信息加上索引
 					 			alter talbe xx add index idx_name(字段名称);
 					 			也可以用 force index 找到合适的索引  主键索引不一定比其他索引块 由索引优化器决定
 				对于2:
 					1.最左前缀匹配原则,非常重要的原则MySQL会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配,比如a=3 and b =4 and c>5 and d =6 ,如果建立(abcd)顺序的索引,d是用不到索引的,如果建立(abdc)的索引 则都可以用到,abd顺序可以任意调整
 					2.=和in可以乱序,比如a=1 and b=2 and c = 3 建立(abc)索引可以任意顺序,MySQL的优化器会帮你优化成索引可以识别的形式 	
 					MySQL建立联合索引的时候 会对最左的第一个索引进行排序的,进而对后面的字段排序的,有点类似order by xx order by yy ...	 所以第一个字段是绝对有序的 而后后面的是无序,再用后面的字段就用不到索引了 这也是最左匹配原则成因了	
 				对于3:
 					不是的 
 					对于数据量小的表不需要建立索引,建立索引会增加额外的索引开销
 					数据变更需要维护索引,因此更多的索引意味着更多的维护成本
 					更多的索引意味着也需要更多的空间  比如100页的书  居然有50页的目录 。。。哈哈
 
 
 	锁模块常见问题
 		1.MyISAM 与 InnoDB关于锁方面的区别是什么
 		2.数数据库的事务的四大特性
 		3.事务隔离级别以及各级别下的并发访问问题
 		4.Innodb可重复读隔离级别下如何避免幻读
 		5.RC,RR级别下的InnoDB的非阻塞读如何实现			
 
 			对于1:
 				MyISAM默认用的表级锁,不支持行级锁
 				InnoDB默认用的是行级锁,也支持表级锁
 				对于MyISAM 而言 在查询的时候会加上一个表级别的读锁,此时另一个人去执行增删
 				改的操作时,会给表加上一个写锁,直到读锁释放 查询结束,写锁才可以操作
 				手动加读锁 lock tables xx read;
 				释放锁 unlock tables;
 				此时的读锁也是;一个共享锁
 				读读 不会阻塞住
 				读写会阻塞 写读也阻塞 
 				写锁也叫排他锁(增删改会上排他锁)
 				select 也可以上排他锁 + for update 即在语句 select * form xx for update;
 				上了共享锁  依然支持上共享锁 不支持上排他锁 先上排他锁 后面的读写都不支持
 				共享锁和排他锁也支持innodb的搜索引擎 支持行级锁  支持事务
 				MySQL默认自动提交事务 show variables like 'autocommit';
 				set autocommit = 0;#关闭事务自动提交 仅对当前session有效 
 				innodb使用了二段锁 枷锁解锁分为2步的
 				加共享锁select * from xxx lock in share mode
 
 				表级别的锁 与索引无关  那么行级锁呢?
 				当不走索引的时候 整张表会被锁住 此时查询用的是表级别锁
 				innodb在没有用索引的时候用到的是表级别的锁,用到索引的时候就是行级别锁和gap锁(走非普通唯一索引时候用到)
 				innondb还支持表级别的逆向锁 共享读锁IS  排他写锁IX和MyISAM的表锁作用差不多 为的是表级别操作的时候不用去轮询每一行有没有上行级别的锁
 						X(排他写锁)     S(共享读锁)
 				X             冲突             冲突
 				S             冲突             兼容
 
       			MyISAM适合的场景
       				频繁执行全表count语句
       				对数据进行增上该的频率不高,查询非常频繁适用
       				适合没有事务的场景
       			InnoDB适合的场景
       				数据增删改都相当频繁
       				可靠性要求比较高,要支持事务的场景
       			数据库锁的分类
       				按锁的粒度划分:可分为表级锁,行级锁,页级锁(bdb引擎支持,介入表级和页级的锁)		
       				按锁级别划分:共享锁,排他锁
       				按枷锁方式:自动锁,显式锁
       				按操作划分:分为DML (增删改查)DDL锁(该表操作)
       				按使用方式划分:
       				乐观锁(一般认为操作不会发生冲突,在数据正式提交的时候才会
       				检查冲突与否,一旦冲突返回用户错误的信息给用户,让用户决定如何去做,一般实现
       				方式记录数据的版本,有2种方式:1使用版本号2,时间戳)
       				悲观锁(往往依靠数据库提供的锁机制)=== 排他锁
 
       	数据库事务的四大特性:
       		其实程序的事务特性没有太大区别 ACID
       		A 原子性  一系列操作要么全部成功或失败
       		B 一致性  转账不论几次最终一致
       		I 隔离性  多个事务并发执行 一个事务并不会其他事务
       		D 持久性   一个事务一旦提交就会永久保存数据库中 当系统挂掉 已提交内容不会丢失	
       	事务隔离级别 以及各级别下的并发访问的问题
       		事务并发访问引起的问题以及如何避免
       		MySQL会利用锁机制创建不同的事务级别
       		read uncommitted  --  read uncommitted --  repeatable read --
       		1.更新丢失 (一个事务更新覆盖了另一个事务更新的操作) 
       		  MySQL所有事务隔离级别在数据库层面上均可避免
       		2.脏读(一个事务读到另一个事务未提交的更新数据) READ-COMMITTED(已提交读)
       		  事务级别以上可以避免
       		查询数据库隔离级别 select @@tx_isolation; 默认REPEATABLE-READ
       		设置隔离级别语句
       		set session transaction isolation level read uncommitted; 读未提交
       		取消自动提交手动写
       		start transaction;
       		select * from xx where id = 'xx';
       		commit/rollback;
 
       		set session transaction isolation level read committed; 读提交
       		3.不可重复读 (事务A多次读取同一数据,事务B在事务A多次读取过程中对数据做了更新并提交导致
       		  事务A多次读取结果不一致)REPEATABLE-READ事务级别以上可以避免 innodb 默认隔离级别 
       		  REPEATABLE-READ 可重复读
       		  set session transaction isolation level repeatable read; 可重复读
 
       		4.幻读(事务A在读取与搜索条件匹配的若干行,事务B在以插入行或
       		  修改行的操作来修改事务A的结果集,
       		  事务A看起来向出现幻觉一样) SERIALIZABLE事务隔离级别可避免
 
       		事务隔离级别  更新丢失  脏读  不可重复读  幻读
       		未提交读       避免     发生   发生       发生
       		已提交读       避免     避免   发生       发生
       		可重复读       避免     避免   避免       发生(理论实验也避免了)
       		串行化         避免     避免   避免       避免
 
       		不可重复读侧重于对同一数据进行修改  而幻读侧重对于数据新增和删除
       		MySQL  默认事务隔离级别REPEATABLE-READ
       		ORACLE 默认事务隔离级别READ-COMMITTED
 
       		Innodb可重读隔离级别下如何避免幻读的呢?
       		表象:快照读(非阻塞读)--伪MVCC
       		内在:next-key锁(行锁+gap锁)
 
       		什么快照读和当前读呢?
       		当前读:select ...lock in share mode, select ... for update
       		当前读:update,delete,insert
       		当前读就是加了锁的CRUD
       		快照读:不加锁的非阻塞读,select
 
       		rc 隔离级别下  当前读和快照读去读结果一直
       		rr 隔离级别下  结果可能一样 和快照查询时机有关 得到结果可能不一样
 
       		rc rr 级别下的innodb的非阻塞读如何实现?
       			数据行里的DB_TRX_ID,DB_ROLL_PTR,DB-ROW_ID字段
       			undo日志  insert undo log   update undo log 
       			read view  可见性算法  DB_TRX_ID 比较
 
       		对主键索引或唯一索引会用Gap锁吗
       			如果where 条件全部命中,则不会用Gap锁,只会加记录锁	
       			如果where 条件部分命中或者全不命中,则会加Gap 锁
       		Gap锁会用在非唯一索引或者不走索引的当前读中	
       			非唯一索引
       			不走索引
 
       		SQL语法简单讲解  group by   having
       			group by 
       				满足 select 子句中的列名必须为分组列或列函数 
       				列函数对于group by 子句定义的每个组各返回 一个结果  同一张表
       			where要在group by 前面		
       			having
       				通常与group by子句一起使用
       				where 过滤行 having 过滤组
       				出现在同一SQL的顺序 where > group by >having