目录
MySQL存储引擎InnoDB(默认)
Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
innoDB更适合数据经常修改的情况,MyIsam更适合不经常修改,频繁查询的情况。
索引的实现方式:B+tree
SQL 约束有哪几种?
- NOT NULL: 用于控制字段的内容一定不能为空(NULL)。
- UNIQUE: 控件字段内容不能重复,一个表允许有多个 Unique 约束。
- PRIMARY KEY: 也是用于控件字段内容不能重复,但它在一个表只允许出现一个。
- FOREIGN KEY: 用于预防破坏表之间连接的动作,也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
- CHECK: 用于控制字段的值范围。
SQL的生命周期
- 应用服务器与数据库服务器建立一个连接
- 数据库进程拿到请求sql
- 解析并生成执行计划,执行
- 读取数据到内存并进行逻辑处理
- 通过步骤一的连接,发送结果到客户端
- 关掉连接,释放资源
1对1,多对多配置
Mybatis用注解方式配置,一对一是@one,一对多,多对多是@many
xml方式配置,1对1关键字是association,1对多,多对多的关键字是collection
表设计三大范式
第一范式:每个列都不可以再拆分。
如果实体中的某个属性有多个值时,必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。比如说不能一个字段又存姓名又存地址。
第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。
意思就是一张表只能由一个主键,不能有多主键。
第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。
在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。
char和varchar的区别
varchar用于存储可变长字符串,它比定长类型更节省空间
char表示定长字符串,长度是固定的,如果插入数据的长度小于char的固定长度时,则用空格填充。根据定义的字符串长度分配足够的空间,char适合存储很短的字符串,或者所有值长度都相近的字符串.
因为长度固定,所以存取速度要比varchar快很多,甚至能快50%,但正因为其长度固定,所以会占据多余的空间,是空间换时间的做法;
对于经常变更的数据来说,char比varchar更好,因为char不容易产生碎片。
什么是存储过程?有哪些优缺点?
存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需要创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。
优点
1)存储过程是预编译过的,执行效率高。
2)存储过程的代码直接存放于数据库中,通过存储过程名直接调用,减少网络通讯。
3)安全性高,执行存储过程需要有一定权限的用户。
4)存储过程可以重复使用,减少数据库开发人员的工作量。
缺点
1)调试麻烦,但是用 PL/SQL Developer 调试很方便!弥补这个缺点。
2)移植问题,数据库端代码当然是与数据库相关的。但是如果是做工程型项目,基本不存在移植问题。
3)重新编译问题,因为后端代码是运行前编译的,如果带有引用关系的对象发生改变时,受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。
4)如果在一个程序系统中大量的使用存储过程,到程序交付使用的时候随着用户需求的增加会导致数据结构的变化,接着就是系统的相关问题了,最后如果用户想维护该系统可以说是很难很难、而且代价是空前的,维护起来更麻烦。
创建存储过程:
create procedure xxx()
begin
.........
end;
调用存储过程
基本语法:call xxx();
注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递
删除存储过程
drop procedure xxx;
innoDB锁机制
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
行级锁 表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
表级锁 表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。
特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。
从锁的类别上分MySQL都有哪些锁呢?
从锁的类别上来讲,有共享锁和排他锁。
共享锁: 又叫做读锁。 当用户要进行数据的读取时,对数据加上共享锁。共享锁可以同时加上多个。
排他锁: 又叫做写锁。 当用户要进行数据的写入时,对数据加上排他锁。排他锁只可以加一个,他和其他的排他锁,共享锁都相斥。
MySQL中InnoDB引擎的行锁是怎么实现的?
答:InnoDB是基于索引来完成行锁
例: select * from tab where id = 1 for update;
for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起。
InnoDB存储引擎的锁的算法有三种
- Record lock:单个行记录上的锁
- Gap lock:间隙锁,锁定一个范围,不包括记录本身
- Next-key lock:record+gap 锁定一个范围,包含记录本身
什么是死锁?怎么解决?
死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象。
- 如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。
- 在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率。
- 不要嵌套锁。
数据库的乐观锁和悲观锁是什么?怎么实现的?
悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来,直到提交事务。实现方式:使用数据库中的锁机制
乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来,通过version的方式来进行锁定。实现方式:乐观锁一般会使用版本号机制或CAS算法实现。
两种锁的使用场景
两种锁各有优缺点,像乐观锁适用于写比较少的情况下(多读场景),即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。
但如果是多写的情况,一般会经常产生冲突,这就会导致上层应用会不断的进行retry,这样反倒是降低了性能,所以一般多写的场景下用悲观锁就比较合适。
事务的四大特性
- 原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
- 一致性: 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
- 隔离性: 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
- 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
什么是脏读?幻读?不可重复读?
- 脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。
- 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。
- 幻读(Phantom Read):在一个事务的两次查询中数据条数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。
事务的隔离级别?MySQL的默认隔离级别是什么?
- READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
- SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。
Mysql 默认采用的隔离级别是可重复读;
事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC(多版本并发控制),通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
Mysql是怎么解决幻读问题的?
mvvc版本控制+间隙锁
Mysql常用函数
AVG,COUNT,
GROUP BY,ORDER BY,LIMIT,去重:DISTINCT,HAVING,WHERE
REGEXP(正则匹配),
REPLACE(替换),
DATE_FORMAT(时间空格转换),
CONCAT (拼接字符串,一般用来代替like使用,如果有一个字段为null,则返回的结果为null)
IF(条件判断语句,v1,v2);如果为true,则返回v1,否则返回v2
Year Month 取出日期格式的字段的年,月
Union和Union All的区别
union 用于把来自多个select 语句的结果组合到一个结果集合中
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录。
(select id,name from table1 where age > 10 order by id desc limit 999)
union
(select id,name from table2 where age > 10 order by id desc limit 999);
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
而UNION ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
从效率上说,UNION ALL 要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用UNION ALL。
UNION会导致order by 语句失效
通常我们会用 union 连接多个select语句,select子句的 order by 要和 limit 联合使用才生效,否则order by被优化器干掉。
慢sql查询
explain关键字,放在sql语句的前面来执行,看查询状态。
天兔系统监控慢sql,设置慢sql生成的时间限制,比如如果sql运行时间超过5s,就属于慢sql,生成慢sql日志。然后查看慢sql日志,找到对应的sql,用explain命令去执行,主要看三个字段
select_type,type,key
select_type:select关键字对应的那个查询的类型
type:针对单表的访问方法
key:是否使用了索引,如果没有用到,就是null
子查询,和内连接,外连接查询的效率对比
一般情况下最好不要用子查询,可以改为内外连接来优化效率。
子查询:select * from t1 where id in(select id from t2 where name='xxx');
改成内连接:select * from t1 inner join t2 where t1.id=t2.id and t2.name='xxx'。
改成外连接:select * from t1 left join t2 on t1.id=t2.id and t2.name='xxx'.
索引专栏
索引有哪些优缺点?
索引的优点
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
- 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
索引的缺点
- 时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率。
- 空间方面:索引需要占物理空间。
索引有哪几种类型?
- 主键索引: 数据列不允许重复,不允许为NULL,一个表只能有一个主键。
- 唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
- 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
- 普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。
- 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
- 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
- 全文索引: 是目前搜索引擎使用的一种关键技术。
- 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
索引的数据结构(b树,hash)
B+tree性质:
1.)n棵子tree的节点包含n个关键字,不用来保存数据而是保存数据的索引。
2.)所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
3.)所有的非终端结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字。
4.)B+ 树中,数据对象的插入和删除仅在叶节点上进行。
5.)B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点。
索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引,一般来说执行查询时遍历整张表。
索引的原理很简单,就是把无序的数据变成有序的查询。
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
索引的设计原则
- 适合索引的列是出现在where子句中的列,或者连接子句中指定的列。
- 基数较小的类,索引效果较差,没有必要在此列建立索引。
- 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间。
- 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。
索引的设计原则,创建索引的原则(重中之重)
1) 最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2)较频繁作为查询条件的字段才去创建索引。
3)更新频繁字段不适合创建索引。
4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)。
5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。
6)定义有外键的数据列一定要建立索引。
7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
8)对于定义为text、image和bit的数据类型的列不要建立索引。
创建索引时需要注意什么?
- 非空字段:应该指定列为NOT NULL,除非你想存储NULL。在mysql中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值;
- 取值离散大的字段:(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说明字段的唯一值越多字段的离散程度高;
- 索引字段越小越好:数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。
使用索引查询一定能提高查询的性能吗?
通常,通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。
- 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
- 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
- 基于非唯一性索引的检索
什么是最左前缀原则?什么是最左匹配原则
- 顾名思义,就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
- 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
- =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。
B树和B+树的区别
- 在B树中,你可以将键和值存放在内部节点和叶子节点;但在B+树中,内部节点都是键,没有值,叶子节点同时存放键和值。
- B+树的叶子节点有一条链相连,而B树的叶子节点各自独立。
使用B树的好处
B树可以在内部节点同时存储键和值,因此,把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询87@👉付宝👈邀请好友把团建,一起瓜汾销ffei券DC6R0PW44jS va:/效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。
使用B+树的好处
由于B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键,有利于更快地缩小查找范围。 B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历的时候,B+树只需要使用O(logN)时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间。
索引失效
模型数空运最快
- 模:模糊查询,like以%开头,索引失效
- 型:类型错误,索引列是number类型,where条件却用得是varchar
- 数:对索引的字段使用内部函数
- 空:索引的字段为null,因该限制索引列不能为null
- 运:对索引列进行加减乘除等运算
- 最:如果不是按照索引的最左列开始查找,则无法使用索引
- 快:如果数据库判断全表扫描要比使用索引快,就会放弃使用索引