mysql

事务

事务的基本特性和事务的隔离级别

四大特性分别是

ACID

• 原子性
• 一致性
• 隔离性
• 持久性

隔离级别分别是

• 未提交读：就是一个事务可以读取另一个未提交事务的数据(会引发脏读)
• 提交读:一个事务要等另一个事务提交之后才能读到数据
• 重复读:启事务的时候不允许其他人做修改操作
• 串行读:开启事务的时候不允许其他人做查询和修改修改操作(表锁)

事务的并发问题有哪些

• 脏读:线程中的事务读取到了另一个线程中未提交的数据
• 不可重复读:一个线程中的事务读取到了另一个线程中提交的update的数据
• 幻读:一个线程中的事务读取到了另一个线程中insert的数据

优化

常见的sql语句优化有哪些

使用全值匹配索引,对索引进行覆盖,保证命中索引.

用最左匹配原则,不适用函数,不使用范围查询,

尽量减少对or关键字的使用,用union或来进行代替

多使用join连表查询,少使用分步查询

分页查询时,当遇到可能出现很大的页数的时候,使用子查询查询出所需记录对应的主键,根据主键在进行需要数据的查询

对于慢查询有什么优化经验

慢查询主要是针对三个方向来进行的,是查询没有命中索引?还是加载了不需要的数据列?还是数据量太大

• 首先分析语句,看看是否加载了额外的数据,可能是查询了多余的行或者列,对语句进行分析以及重写
• 分析语句的执行计划,我然后获得使用索引的情况,之后修改语句或者修改索引,让sql语句尽可能的命中索引
• 如果对于语句的优化已经无法进行,可以考虑是不是表中的数据量太大,是的话可以进行横向分表或纵向分表

为什么不要使用select *

select 指定查询字段除了减少网路的IO，磁盘的cpu开销等，使用select指定查询字段sql要先去统计所有的字段才能进行下一步，最主要的是，如果需要查询的内容为索引的话可以达到覆盖索引的效果，mysql不用再去回表查询，大大替身了性能

count(1)和count(*)的区别

在innodb引擎下,这俩个其实没有性能上的差异,myisam引擎是由差异的,myisam引擎对于count(*)进行了优化,可以快速得到结果.

在mysql8中对于count(*)做了额外的优化,如果没有额外的查询字句(where,group by)那么yinnodb表的查询count(*)将针对于单线程负载进行优化.innodb会通过遍历最小的辅助索引来选择count(*),如果不存在二级索引就会通过聚集索引来选择count(*)语句

超大分页如何优化

可以使用子查询优化,先查询出索引的字段,虽然要查询的数据量不变,但是由于覆盖了索引,要查询的字段都在索引中,所以速度回很快.同时如果id的连续性较好,可以传入上一页的最大页的id,以id为基准向后查询每页的数据.

从需求的角度要减少这种请求...比如百度查询的总页数最多只有76页

索引

索引的了解

索引是一种特殊的查询表,数据库的搜索引擎可以利用它加速对数据库的检索;索引对于查询比较快,对于增删会比较慢.

索引的建立的注意

• 为经常需要排序,分组,联合操作以及查询的字段建立索引
• 索引要选择数据量比较少的索引,如果数据很长查询速度会受到影响
• 数据量比较小的表可以不使用索引,查询花费的时间可能比遍历索引的时间还要短,索引不会产生优化的效果
• 索引并不是越多越好.每个索引都会额外占用磁盘空间,索引越多磁盘空间就越大

对索引数据结构的理解

索引的数据结构和使用的存储引擎有关,mysql有hash索引和b+树索引

b+树底层实现是多路平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且统计的节点间有指针相互连接

哈希索引就是采用哈希算法,把键值转换成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需要一次哈希算法就可以立即定位到响应的位置,速度非常快.远快于B+树索引,但只有memory支持哈希索引

hash索引和b+树索引的区别于优劣

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以取到响应的键值,之后1进行回表查询获得书籍数据.b+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可获得所查询的值

他们有以下的不同

• hash索引进行等值查询的更快,但是琺进行范围查询

因为hash索引中结果hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而b+树的所有节点遵循做节点小于父节点天然支持范围查询

• hash索引不支持使用索引进行排序,原理同上
• hash索引1不支持模糊查询以及多列索引的最左前缀匹配原则.原理也是因为hash函数的不可预测
• hash索引任何时候都避免不了回表查询数据,而B+树在一些情况下可以只通过索引完成查询
• memory才支持hash索引,但是大部分引擎支持b+树索引
• hash索引虽然在只查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,会发生hash碰撞效率可能很差

什么是聚集索引

在b+树的所有中,叶子节点可能存储了当前的key值,也可能存储了当前key值以及整行的数据,这就是聚集索引和非聚集索引.在innodb中1只有主键索引是非局促索引,如果没有主键,则挑选一个唯一键建立聚集索引,如果没有则自耦东生成一个键来建立聚集索引.

当查询时使用局促索引时,在对叶子节点,可以获取到整行的数据,因此不再进行回表查询

非聚集索引一定会回表查询吗

不一定,这涉及到了查询语句要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必回表查询

联合索引是什么,为什么需要注意联合索引的顺序

mysql可以使用多个字段同事建立一个索引,就叫做联合索引.在联合索引中,如果想要去命中索引,就必须按照建立索引时的字段挨个使用,否则无法命中索引

假设家里了name,age,school的联合索引,那么索引的排序顺序为,先按照name排序,如果name相同,则按照age排序,如果age值相同,则按照school排序

如何查看索引有没有被使用到,或者说怎么才可以知道语句运行很慢的原因

mysql提供了explain命令来查看语句的执行计划,mysql在执行某个语句之前会将该语句过一遍查询优化器,之后会对语句进行分析,可以通过 possible_key,key,key_len等字段查看所有的使用情况

哪些情况下会导致索引失效

• 使用不等于查询
• 列参与了数学运算或者函数
• 在字符串like左边是通配符类似于%aaa
• mysql分析全表扫描比使用索引快的时候不使用索引
• 没有依次使用索引导致索引失效(最左匹配原则)
• 使用范围查询

索引走不上如何排查

首先排查是不是sql写的有问题,比如对索引字段进行了一些函数操作,是否使用到了最左匹配原则,还有范围查询也会导致索引失效,可以在explain中查看key,key_len,ref,判断索引的命中情况.因为一条sql只会使用到一个索引,mysql的索引优化器会计算出合适的索引,但是这个索引并不是最好的.可以尝试用force index强制走索引,但这不应该成为一个常用的操作,可能迁移到其他的数据库就不支持了.同事可以使用analyze tables刷新索引的信息。

表的设计

myisam和innodb的区别

• innodb支持事务,myisam不支持
• innodb支持行锁,而myisam支持表锁
• innodb支持mvcc,myisam不支持
• innodb支持外键,myisam不支持
• innodb不支持全文索引,myisam支持

vachar和char有什么区别

char是一个定长字符串,加入申请了char(10)那么无论市级存储多少内容该字段都占用10个字符,而vachar是变长的

也就是说生气的长度只是最大长度占用的空间为实际字符长度+1

从效率上来讲char>varchar因此在使用中如果确定摸个字段的值的长度,可以用char,否则应该尽量使用varchar

比如存储用户身份证应该使用varchar

varchar(10)和int(10)代表什么含义

varchar的10代表了申请的空间长度,也是存储的数据的最大长度,而int的10代表了展示的长度,不足10位以0填重,int(1)和int(10)所能存储的数字大小以及占用空间都是相同的,只是展示的时候按照长度展示

存储过程是什么,有哪些优缺点

存储过程是一组可编程的函数,是一些预编译的语句.执行效率很高

存储过程可以将重复性很高的操作封装起来,简化了对sql的调用,提高了网络通信的速度

但存储过程一般使用较少,因为互联网项目迭代比较快,管理存储过程没有那么方便,复用性也没有写在服务层好

数据库设计的三大范式

• 每个列都不可再分
• 要有进行唯一标识的字段(要有主键)
• 相关联的表中,不应该存在重复的冗余的字段(比如在用户表中存部门名称)

数据库日志有几种

有错误日志（errorlog），查询日志（gennerallog），二进制日志（binlog），慢查询日志（showquerylog），innodb还有,重做日志（redolog）,回滚日志（undolog）六种

超键、候选键、主键、外键分别是什么？

超键:在关系中能唯一标识元组属性集称之位超键,一个属性可以作为一个超键,多个属性组合在一起也可以作为一个超键.超键包含候选键和主键.

候选键:是最小超键,没有多余属性的超键

主键:数据库表中存储数据对象予以唯一和完整标识的数据列或者属性的组合,一个数据列只能有一个主键,且主键取值不能为null

外键:用于1建立和加强俩个数据表之间数据连接的一个列或者多个列

drop,delete,truncate区别和使用场景

delete和truncate值删除表的数据不删除表结构

速度drop>truncate>delete

不需要一张表的全部数据的时候用drop(清空)

想删除部分数据航的时候用delete

想删除表数据与表结构用truncate

唯一索引,普通索引,主键索引的性能区别

唯一索引相对于普通索引,为了确保唯一会多一次判断的过程,但这个过程开销很小,真正的开销是在buffer区

在innodb之下查询普通索引和唯一索引的时候,会先查询到主键,之后进行一次回表根据主键查询导数据

在修改辅助索引的时候,辅助索引页不在缓冲池中的时候,changebuffer会chche对辅助索引的修改,等到相关的索引页被读入buffer pool中后,才会使用change buffer中的内容对辅助索引页进行修改

mysql id用完了

innodb如果在没有指定主键的是华内部会维护一个row_id作为主键,row_id达到上限之后,下一个值就是0.

数据库自增id达到上线之后,再申请1它的值就不会再改变了,继续插入数据时会导致报主键冲突错误.

在数据库建立的时候,预计到id会被用完应该选择bigint类型