1、SQL 的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):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。
事务的并发问题:
- 脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据
- 不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致
- 幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。
小结:
不可重复读侧重于修改,幻读侧重于新增或删除
3.MySQL事务隔离级别:
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交(read-uncommitted) | 是 | 是 | 是 |
读提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read)(mysql) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
4.简述在MySQL数据库中MyISAM和InnoDB的区别
InnoDB存储引擎-->行锁--->支持事务,外键
MyISAM存储引擎--->支持全文索引--->支持表锁
5.悲观锁和乐观锁:
悲观锁:认为所有的操作都会修改数据库
乐观锁:认为所有的操作都不会修改数据库
悲观锁: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};
6、有没有设计过数据表?你是如何设计的?-->在实际开发中已经不太重视了
第一范式 | 保障每列元素的原子性 |
第二范式 | 保障每行元素的唯一性 |
第三范式 | 保障每列元素除主键外不可依赖性 |
7.索引相关问题
索引的分类:
功能:普通索引,主键索引,唯一索引,全文索引
作用字段:单列索引,联合索引
物理作用:聚簇索引,非聚簇索引
聚簇索引:索引和数据保存在同一个B+树中(叶子节点:所有数据;非叶子节点:索引列+页号)
非聚簇索引:需要进行一次回表操作(叶子节点:索引列 + 主键)
覆盖索引:根据索引就能获取到数据:select name from user where name='张三';
索引下推:将server的工作交给引擎层,可以减少回表操作
聚簇索引和非聚簇索引
聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的
非聚簇索引叶子节点不存储数据、存储的是数据行地址,也就是说根据索引查找到数据行的位置再取磁盘查找数据,这个就有点类似一本书的目录,比如我们要找第三章第一节,那我们先在这个目录里面找,找到对应的页码后再去对应的页码看文章。
优势:
1、查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高
2、聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合,非聚簇索引不适合
劣势;
1、维护索引很昂贵,特别是插入新行或者主键被更新导至要分页(pagesplit)的时候。建议在大量插入新行后,选在负载较低的时间段,通过OPTIMIZETABLE优化表,因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2、表因为使用uuId(随机ID)作为主键,使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面更慢,所以建议使用int的auto_increment作为主键
3、如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值,过长的主键值,会导致非叶子节点占用占用更多的物理空间
索引的底层实现是什么?什么情况下会索引失效?
InnoDB 存储引擎是用 B+Tree 实现其索引结构
失效条件:
1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
2.对于多列索引,不是使用的第一部分,则不会使用索引
3.like查询以%开头
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 类型转行问题
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
6. 组合索引要遵循 最左匹配原则
B+tree 与 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、如何处理慢查询
在业务系统中,除了使用主键进行的查询,其他的都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。
慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是加载了不需要的数据列?还是数据量太大?
所以优化也是针对这三个方向来的
首先分析语句,看看是否加载了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写。
分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引。
如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表。
10、数据库分表操作
可以说使用Mycat或者ShardingSphere等中间件来做,具体怎么做就要结合具体的场景进行分析了。
可以参考:https://database.51cto.com/art/201809/583857.htm
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(多个连表查询效率低,容易到之后锁表和阻塞)。
例如:select * from admin order by admin_id limit 100000,10
优化为:select * from admin where admin_id between 100000 and 100010 order by admin_id。
例如: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异常
解读:方便定位线上问题
说明:本规范适用于并发量大,数据量大的典型互联网业务,可直接参考。