数据库常见面试
8道面试题目录
目录
7道面试题
1.怎样进行sql优化
4、group by优化
5、limit优化
6、count优化
7、update优化
2.。怎样查看sql实行情况呢(哪个关键字)，说说你对这个关键字的认识
4) possible_key：
5) key
3.说说你对innodb和 myisam的理解
4.char 和varchar的区别
5.innodb引擎底层结构是什么，为什么不用B树呢
B+树
6.事物你相识吗，具体说说
事物四大特性
并发事物题目
事物隔离级别
演示题目：
读未提交-脏读题目
读已提交-不可重复读
可重复读-幻读
7.主键使用自增ID好照旧uuid（比较大的随机数）好呢
自增ID (Auto-Incrementing ID)
UUID (通用唯一辨认码)
总结
8、什么情况下索引会失效
1.怎样进行sql优化
1、怎样进行sql优化
1)查询对where条件后的字段加索引，用于提升查询服从
2)explain查看实行情况，须要插入数据时批量插入
3)sql语句上，查询数目时尽可能用count(*)而不用count(字段)
4)在查询时，尽量不要进行回表查询，这就要求满意需求的情况下，不要使用select *
5)多条件时，思量是不是可以使用最左前缀原则针对条件列进行索引
6)插入数据时，假如有主键，那么主键最好是次序插入的且尽量不要太长，可以避免页分裂
7)模糊查询时，尽量不要开头加%
8)尽量不要使用or/in/not in，因为可能会造成索引失效
9)where条件后的字段(加索引)假如是varchar类型，那么一定要加’’
10)尽量不要将索引列进行运算
[*]插入数据
[*]批量插入：因为一条条插入时，每一条数据的插入都要与数据库建立毗连，而且关闭毗连
https://i-blog.csdnimg.cn/direct/05ee46a1feac44c4a3bfffb4dbe6018d.png
[*]手动提交事物：
默认是自动提交，每提交一次insert语句就会提交一次事物，造成事物的频繁提交和关闭
https://i-blog.csdnimg.cn/direct/efdfb7244bcb47b8938266d107ce0c9e.png
[*]主键次序插入
https://i-blog.csdnimg.cn/direct/cebe6c881cfe47d882cad13dab0cbe80.png
次序插入的性能要高于乱序插入，主键优化中讲解
[*]大批量数据插入
假如一次性须要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下·
https://i-blog.csdnimg.cn/direct/80a2610a149642e6a9e3f037ff4f3631.png
[*]客户端毗连服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
[*]设置全局参数 local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
[*]实行local指令，将准备好的数据加载到表结构中
local data local infile ‘/xx/yy/sql.log’ into table 表名 fields terminated by ‘,’ lines terminated by ‘\n’;
https://i-blog.csdnimg.cn/direct/9af3b5775507467f9f41a08aaed2cb51.png
[*]主键优化
https://i-blog.csdnimg.cn/direct/98c664eebf9043859ed7e11af81029b8.png
页可以为空，也可以填充一半，也可以填充100%。每个页包罗了2-N行数据(假如一行数据多大，会行溢出)，根据主键排列。
主键次序插入时，当第一个page写满后，再去申请第二个page
https://i-blog.csdnimg.cn/direct/bd23b5b08f7845ec9d9494cc6b245e8b.png
页和页之间须要维护一个双向指针
https://i-blog.csdnimg.cn/direct/f7d273c2fd4b46b7976418a1c6a3f83f.png
乱序插入时：可能发生页分裂
https://i-blog.csdnimg.cn/direct/2b6637e8e6224fa396a73cab142d0125.png
最后变成：
https://i-blog.csdnimg.cn/direct/c2694c253ecd464a8a0f2310fbbaa224.png
页归并：
当删除一行纪录时，实际上纪录并没有被物理删除，只是纪录被标记(flaged)为删除而且它的空间允许被其他纪录声明使用，当页中删除的纪录达到 MERGE_THRESHOLD(默以为页的50%)，InnoDB会开始探求最靠近的页(前或后)看看是否可以将两个页归并以优化空间使用。
https://i-blog.csdnimg.cn/direct/8bbedcd6a3864333b98692a67742e82f.png
归并后：
https://i-blog.csdnimg.cn/direct/aab75a9eebdd4e63bf0874cd85148381.png
主键计划原则：
[*]满意业务需求的情况下，尽量降低主键的长度
二级索引下，二级索引的叶子节点中挂的是主键，假如主键较长，二级索引比较多，
将会占用大量的磁盘空间，在搜索时将会淹灭大量的磁盘IO
[*]插入数据时，尽量选择次序插入，选择使用AUTO_INCREMENT自增主键
次序插入时，当一个page写完后才会写另一个page，而乱序插入可能会引起页分裂征象
[*]尽量不要使用UUID作为做主键大概其他自然主键，如身份证号
主键较长，二级索引比较多，将会占用大量的磁盘空间，在搜索时将会淹灭大量的 磁盘IO
[*]业务操作时，避免对主键的修改
[*]order by优化
[*]Using filesort;通过表的索引或全表扫猫，读取满意条件的教据行，然后在排序缓冲区sort bufter中完成排序操作，全部不是通过索引直接返回排序结果的排序都叫 FileSort排序。
[*]Using index:通过有序索引次序扫描直接返回有序数据，这种情况即为using index，不须要额外排序，操作服从高
排序字段加上索引
https://i-blog.csdnimg.cn/direct/fc4964327feb4ecfaf2bb1090f8d099e.png
两个排序字段，一正一倒呢？
https://i-blog.csdnimg.cn/direct/5ca16d6bc8de4da7ad0c1de1b7568f27.png
https://i-blog.csdnimg.cn/direct/ba316f2c664a4ff7ad7b3356b25b595b.png
以是我们优化 时就是尽可能将using filesort给优化点
可以通过创建索引来办理
https://i-blog.csdnimg.cn/direct/9f97b3e283fd4158bff3740349d1633d.png
优化后的结果为：
https://i-blog.csdnimg.cn/direct/ce0a035c3759420d88e4b703b0f96bb3.png
排序字段不加索引：
https://i-blog.csdnimg.cn/direct/79c2673e45a6405a91ad2394e9fb7762.png
总结：
[*]根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
[*]尽量使用覆盖索引
[*]多字段排序,一个升序一个降序，此时须要注意联合索引在创建时的规则 (ASC/DESC)
[*]假如不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小,sort buffer_size(默认256k)。
https://i-blog.csdnimg.cn/direct/df95efe5b7cd427cbbe3ec3163da9303.png
4、group by优化
主要是针对索引进行优化。验证下加索引与不加索引时 分组的服从
https://i-blog.csdnimg.cn/direct/d32fff8deced42fca94f8b76e2a25497.png
加上索引后，服从会得到提升
https://i-blog.csdnimg.cn/direct/983dbc3c2cf14151afca65802cab92e1.png
关于索引的使用
https://i-blog.csdnimg.cn/direct/846b27ec3dc84483b9035194bf2c63ff.png
5、limit优化
须要时间较久，假如有1000万条数据，时间大概会在10s级别
https://i-blog.csdnimg.cn/direct/9ce40f8962bf42148f15411031adb1fc.png
可以通过覆盖索引+子查询的方式优化
https://i-blog.csdnimg.cn/direct/a2cca1cf7f8f47e2a799a9695c2ba8d2.png
6、count优化
https://i-blog.csdnimg.cn/direct/f63072f588d44a7383058f5f780e0e28.png
count的用法：
https://i-blog.csdnimg.cn/direct/3aedfa22aeae48b1bb61cbdf8b267603.png
用法截图如下：
https://i-blog.csdnimg.cn/direct/210c879e29c447809ae9ae56e043e2ce.png
服从：
https://i-blog.csdnimg.cn/direct/63866b1e7bd848a197359c8f5566f17c.png
7、update优化
行锁照旧表锁
两个事物，当都修改id（有索引）对应的信息时
update course set name = ‘kafka’ where id=4
update course set name = ‘java’ where id=1
两个事物的修改操作都能乐成，因为此时锁的是行级锁
两个事物，当都修改name（无索引）对应的信息时
update course set name = ‘kafka’ where name=’java’
update course set name = ‘java’ where name=’mysql’
第一个事物会立刻修改乐成，但是第二个事物的修改会品级一个事物提交后才能修改乐成，因为name没有索引，此时锁的是表级锁
为name加上索引后，两个事物都修改name对应的信息时
两个事物的修改操作都能乐成，因为此时锁的是行级锁
2.。怎样查看sql实行情况呢(哪个关键字)，说说你对这个关键字的认识
explain实行操持 通过explain我们可以模拟一个优化器对sql语句进行优化，进而提升查询服从，以下是explain查询结果中的几个重要字段
[*] id:
select查询的序列号，表示查询中实行select子句大概是操作表的次序(id相同，实行次序从上到下，ID不同，值越大，越先实行)
多表查询展示id值相同:
https://i-blog.csdnimg.cn/direct/9c9afcbd6f1a4300bdfb854478c6d5e2.png
多表查询展示id值不同:此时先实行id大的
https://i-blog.csdnimg.cn/direct/a963f31ba09b4ee6aec99f46c30bfebf.png
[*] select_type
表示select的类型，常见的取值有simple(简单表、即不使用表链接或子查询)、primary（主查询，即外层的查询）、union(union中的第二个大概背面查询语句)、subquery(包罗了子查询)等
[*]type
表示毗连类型，性能由好到差的毗连类型为null、system、const、eq_ref、ref、range、index、all
null：一样寻常的业务开发不会优化到null，因为不访问任何表的时间才是null
https://i-blog.csdnimg.cn/direct/5b2c23545c4549028fa5ac5f0fdf9367.png
system:一样寻常是访问系统表时才可能会是system
const：根据主键大概唯一性索引进行访问一样寻常会是const
https://i-blog.csdnimg.cn/direct/e3d5fd8da658486ea31d4735de09cd52.png
ref:假如我们使用非唯一性索引查询时会出现
https://i-blog.csdnimg.cn/direct/05f5315fc1c345ffabb1b964520e9871.png
range: 对普通索引字段范围查找
https://i-blog.csdnimg.cn/direct/9c048ec86607452fb4891a99bbae5bc9.png
index:
当查询可以大概仅通过扫描索引来满意而无需访问实际的数据行时，毗连类型可能会是index
https://i-blog.csdnimg.cn/direct/80edbce0eb0b44c09699a2de0821106b.png
all:性能最低，须要全表扫描，查询的字段一样寻常黑白索引字段
https://i-blog.csdnimg.cn/direct/bc63d7f45b654ef8b5d597b4861d4300.png
4) possible_key：
在这张表中可能用到的索引,一个或多个
5) key
实际使用的索引，没有则为null
[*]key_len
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
[*]rows
MySOL以为必须要实行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
[*]filtered
表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
[*]extra
额外信息展示
3.说说你对innodb和 myisam的理解
介绍
InnoDB是一种分身高可靠性和高性能的通用存储引擎，在MYSOL 5.5之后，lnnoDB是默认的 MySOL存储引擎。
特点：
DML操作遵循ACID模型，支持事务，
行级锁，提高并发访问性能;
支持外键 FOREIGN KEY约束，保证数据的完整性和精确性
myisam是mysql早期默认的存储引擎
特点：
不支持事物、不支持外键
支持表锁，不支持行锁
访问速度快、
1)innodb支持事物，myisam不支持
2)inndb存储引擎对应的表会有一个ibd文件，用于存储索引、数据、结构，而myisam对用三个文件，分别存储索引、数据、结构
3)innodb支持行级锁，而myisam支持表级锁，以是innodb存储引擎在高并发下冲突小
4)myisam是在mysql5.5版本前的默认存储引擎，之后变成了innodb
5)innodb支持外键约束，myisam不支持
4.char 和varchar的区别
1、char性能高，varchar性能较低，缘故原由是须要计算数据的长度进而确定须要使用的空间
2、char是定长的，varchar是变长的，更节省空间，使空间得倒充实使用
5.innodb引擎底层结构是什么，为什么不用B树呢
B+树
B+树
https://i-blog.csdnimg.cn/direct/17cf10aae29e4477be629f753f6669d2.png
和B树相似，但是全部的数据都会出现在叶子节点，而不是每个节点都会挂载真实数据，且最后的叶子节点形成了一个单向链表，方便范围查找，叶子节点是用来存放数据的，非也字节点起到索引的作用
为什么不接纳B树？（面试题）
数据保存：对于B tree，无论是叶子节点照旧非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增长树的高度，导致性能降低
查询服从稳定：B+树每次查询数据都是遍历到叶子节点，很稳定，而B树则不一定遍历到哪一层
范围查找上：B+树找到最小值后，根据叶子节点形成的链表结构就可以找到最大值，B树还得须要二分查找才可以找到最大值
6.事物你相识吗，具体说说
事物四大特性
原子性：事物是不可分割的最小操作单位，要么全部乐成，要么全部失败
一致性：事物完成时，必须使全部的数据都保持一致状态
隔离性：数据库系统提供的隔离机制，保证事物在不受外部并发操作影响的独立环境下运行
长期性：事物一旦提交或回滚，他对数据库中数据的改变就是永世的
并发事物题目
题目
描述
脏读
一个事物读到了另一个事物还没提交的数据
不可重复读
一个事物先后读取同一条纪录，但是读到的数据不同，称之为不可重复读
幻读
一个事物按照条件读取数据，并没有对应的数据，但是在插入时又发现了对应的数据，就像出现了幻影一样
事物隔离级别
https://i-blog.csdnimg.cn/direct/087039b3bfcc4f46815357668a156c56.png
查看事物的隔离级别
select @@transaction_isolation;
https://i-blog.csdnimg.cn/direct/153ae94b312947b897b4a3632d273d55.png
设置事物的隔离级别
set transaction isolation level 级别
演示题目：
读未提交-脏读题目
首先将隔离级别定为 读未提交
一个事物：
set session transaction isolation level read UNCOMMITTED;
start TRANSACTION;
select *
from account
另一个事物
start TRANSACTION;
update account set money = money-1000 where name = '张三';
//未提交但是上一个事物已经读到数据了
读已提交-不可重复读
set session transaction isolation level read COMMITTED;
办理脏读题目，会有不可重复读题目
事物1
https://i-blog.csdnimg.cn/direct/b9b5db54051f435c8a8227a5d6057917.png
事物2
https://i-blog.csdnimg.cn/direct/f5e638ce0b1c40fa80452bf1536fc3e9.png
事物1先开始查询一遍数据，
事物2插入了一条数据并提交
事物1在查询一边数据，发现此时读的数据和之前读的不一样了，就验证了不可重复读题目
可重复读-幻读
办理了不可重复读的题目，但是办理不了幻读题目
一个事物A里面读到的东西是一样的，不管中心其他事物有没有修改数据并提交，当事物A提交后在查询才能查到最新的数据
幻读-演示：
事物1；
https://i-blog.csdnimg.cn/direct/0b54a43f6b9146a2af5b32055a295747.png
查询时没有某条数据，但是插入时就报错，说主键冲突，像tm幻觉一样
事物二：插入一条数据
https://i-blog.csdnimg.cn/direct/5363ef7c14d14785b3e7b1a5f8d6c308.png
不可重复读侧重于修改
幻读侧重于插入
串行化办理幻读的题目，因为串行化同一时间只支持一个事物在操作，其他事物处于阻塞状态， 最安全，但是服从最低
7.主键使用自增ID好照旧uuid（比较大的随机数）好呢
自增ID (Auto-Incrementing ID)
优点：
[*]简单易用，不须要额外的存储或计算资源来生成。
[*]插入性能好，因为只须要简单地递增一个计数器。
[*]次序的，这可以使得范围查询更快。
[*]占用空间小，通常是整型数据。
缺点：
[*]假如表有多个并发写入者，则须要锁定机制来确保唯一性。
[*]可能会袒露一些关于数据库内部结构的信息，好比插入次序等。
[*]在分布式系统中实现起来可能更复杂，因为须要跨多个节点调和ID的生成。
UUID (通用唯一辨认码)
优点：
[*]全局唯一性，理论上不会重复。
[*]不连续的值，可以增长数据的安全性，因为它们不透露插入次序。
[*]在分布式环境中更容易管理，因为每个节点都可以独立生成UUID。
[*]支持多种生成方式，包括基于时间、随机数以及名字空间等方式。
缺点：
[*]占用更多存储空间，通常是16字节。
[*]随机生成的UUID可能会导致B树索引中的页面分裂，影响写入性能。
[*]查询性能可能受到影响，尤其是当涉及到范围查询时。
总结
选择哪种方案取决于具体的应用场景：
uuid不是次序插入的，会导致页分裂，服从底下
uuid比较大，在大数据两下占用空间比较多，造成索引扫描时消耗大
[*]假如你的应用程序对插入次序不敏感，而且对读取性能要求较高，大概是在一个高度分布式的环境中运行，那么UUID可能是更好的选择。
[*]假如你的应用须要快速的插入性能，而且对数据的物理存储次序有一定的需求，大概是在一个不太关心全局唯一性的单一服务器上运行，那么自增ID可能更恰当。
8、什么情况下索引会失效
1)模糊查询时，开头加%会导致索引失效
2)使用or/in/not in,会导致索引失效
3) 一个联合索引，假如使用过程中跳过了联合列中的某一列，那么该列背面的列索引失效
4)索引列通过表达式运算会导致索引失效
5)某个varchar类型的字段使用时不加引号导致索引失效
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]