#{}和${}的区别
#{}:占位符
${} :字符串拼接
注意 使用字符串拼接有可能造成sql注入
mybatsi是如何进行分页的?分页插件的原理是什么?
物理分页:直接从数据库中拿出我们需要的数据,例如在Mysql中使用limit
逻辑分页:从数据库中拿出所有符合要求的数据,然后再从这些数据中拿到我们需要的分页数据
优缺点
物理分页每次都要访问数据库,逻辑分页只访问一次
物理分页占用内存少,逻辑分页相对较多
物理分页数据每次都是最新的,逻辑分页有可能滞后
1.SQL分页 使用limit关键字
select * from payment limit #{pageNo},#{pageSize}
2.RowBounds实现分页
RowBounds对象有2个属性,offset和limit。
offset:起始行数
limit:需要的数据行数
Mybatis中使用RowBounds实现分页的大体思路:
先取出所有数据,然后游标移动到offset位置,循环取limit条数据,然后把剩下的数据舍弃
3.通过分页插件pagehelper
导入依赖
配置数据源和pagehelper相关配置
mybatis三种批量插入的方法
1.代码中循环插入
2.foreach标签
<insert id="insertBatch">
INSERTINTO tb_student (name, age, phone, address, class_id) VALUES
<foreach collection="list" separator="," item="item">
(#{item.name},#{item.age},#{item.phone},#{item.address},#{item.classId})
</foreach>
</insert>
3.批处理
List<Student> studentList = createData(100);
//使用批处理
long start = System.currentTimeMillis();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
StudentMapper studentMapperNew = sqlSession.getMapper(StudentMapper.class);
studentList.stream().forEach(student -> studentMapperNew.insert(student));
sqlSession.commit();
sqlSession.clearCache();
三种方式中,批处理的方式效率是最高的,尤其是在数据量大的情况下尤为明显。
其次是foreach标签,foreach标签是通过拼接SQL语句的方式完成批量操作的。但是当拼接的SQL过多,导致SQL大小超过了MySQL服务器中max_allowed_packet变量的值时,会导致操作失败,抛出PacketTooBigException异常。
最后是循环插入的方式,这种方式在数据量小的时候可以使用,在数据量大的情况下效率要低很多
参考资料:https://www.cnblogs.com/kelelipeng/p/16803239.html
mybatis标签有哪些
1.定义SQL语句
insert、update、delete、select
2.配置关联关系
collection、association
3.控制动态SQL拼接
foreach、if、choose
4.格式化输出
where、set、trim
MySql有哪些存储引擎
MyISAM、InnoDB
事务和锁机制是什么关系?开启事务就自动加锁了嘛?
1. 事务与锁是不同的。事务具有ACID(原子性、一致性、隔离性和持久性),锁是用于解决隔离性的一种机制。
2. 事务的隔离级别通过锁的机制来实现。另外锁有不同的粒度,同时事务也是有不同的隔离级别的。
3. 开启事务就自动加锁。
事务的基本特性
1. 原子性:事务中的操作要么都执行,要么都回滚
2. 一致性:A和B存款各500,相互转账总额保持1000不变,对开发者有要求,不能写出错误逻辑
3. 隔离性:多事务并发时,保证各事务处理互不干扰,有四个隔离级别,在并发和干扰之间权衡(需要深入学习锁知识)
4. 持久性:事务一旦提交,数据一定持久化到数据库,不可回滚,除非执行相反操作事务,mysql通过日志实现的
MYSQL的事务隔离级别
SHOW VARIABLES LIKE 'tx_isolation'; 查看隔离级别
set transaction level xxx 设置下次事务的隔离级别。
set session transaction level xxx 设置当前会话的事务隔离级别
set global transaction level xxx 设置全局事务隔离级别
READ UNCOMMITED(read uncommited): 允许脏读
READ COMMITED(readcommited): 防止脏读,最常用的隔离级别
REPEATABLEREAD(repeatable read): 防止脏读和不可重复读。MYSQL默认
SERIALIZABLE(serializable):事务串行,可以防止脏读、幻读、不可重复读
隔离级别,级别越高,事务的安全性是更高的,但是事务的并发性能也会越低
什么是脏读、幻读、不可重复读?要怎么处理
脏读:
一个事务读取另外一个事务还没有提交的数据
解决办法:
加锁,在修改的时候加排他锁,直到事务提交才释放。读取的时候增加共享锁,读完释放锁
把数据库的事务隔离级别调整到readcommitted(读提交/不可重复读)
幻读:
也是指当事务不独立执行时,插入或者删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。
例如:系统事务A将数据库中所有数据都删除的时候,但是事务B就在这个时候新插入了一条记录,当事务A删除结束后发现还有一条数据,就好像发生了幻觉一样。这 就叫幻读。
解决办法:
加范围锁 把数据库的事务隔离级别调整到SERIALIZABLE_READ(序列化执行),或者数据库使用者自己进行加锁来保证。
不可重复读:
在同一个事务内,两次相同的查询返回了不同的结果
例如:事务T1会读取两次数据,在第一次读取某一条数据后,事务T2修改了该数据并提交了事务,T1此时再次读取该数据,两次读取便得到了不同的结果。
解决办法:
加锁,读取增加共享锁,写数据增加排他锁 把数据库的事务隔离级别调整到REPEATABLE_READ
MySQL的锁有哪些
从锁的粒度来划分:
行锁(InnoDB支持)
共享锁:读锁。多个事务可以对同一个数据共享同一把锁。持有锁的事务都可以访问数据,但是只能读不能修改。
排他锁:写锁。只有一个事务可以获取排他锁。其他事务都不能获取该行的锁。innodb会对update、delete、insert语句自动添加排他锁
select ….. for update
自增锁:通常针对MySQL中自增的字段。如果有事务回滚情况,数据会回滚,但是自增序列不会回滚。
表锁(MYISAM和innodb都支持)
加锁粒度大,加锁资源开销比较小。
全局锁
加锁之后整个数据库实例都处于只读状态。所有的数据变更操作都会被挂起。一般用于全库备份的时候。
乐观锁和悲观锁
悲观锁
当要对数据库中的一条数据进行修改的时候,为了避免被其他人同时修改,最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制,在修改的时候先锁定,后修改的方式被称为悲观锁
悲观锁主要分为:共享锁和排他锁
![](https://i-blog.csdnimg.cn/blog_migrate/52fef7a8be01061be1b4d778f85fdf45.png)
悲观锁的实现方式:
悲观锁依靠数据库本身的锁机制
1.在记录修改前,先尝试为该记录增加排它锁
2.如加锁失败,说明该记录正在被修改,当前查询可能要等待或者抛出异常
3.如果加锁成功,那么就对记录做修改,事务完成后解锁
4.期间如果有其他对该记录或者加排它锁操作,都会等待解锁或者直接抛出异常
乐观锁
乐观锁是假设数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会对数据是否冲突进行检测,如果冲突,则返回给用户异常信息,让用户决定如何去做。乐观锁适用于读多写少的场景
乐观锁的实现:
1.CAS实现:Java中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式
2.版本号控制:一般是在数据表中加上一个数据版本号 version字段,表示数据被修改的次数。当数据被修改时,version 值会 +1。当线程 A 要更新数据时,在读取数据的同时也会读取 version值,在提交更新时,若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新,否则重试更新操作,直到更新成功
索引是什么
索引是一种用于快速查询和检索数据的数据结构。
索引的底层数据结构有很多种,常见的有索引结构有:B树、B+树、hash、红黑树,无论是MyISAM、InnoDB都使用B+树作为索引结构
主键和唯一索引的区别
主键是一种约束,唯一索引是一种索引,两者在本质上不同
主键创建后会生产主键索引:是一种特殊的唯一索引,唯一索引不一定就是主键
唯一索引列允许空值,而主键列不允许为空
主键可以被其他表引为外键,唯一索引不可以
一个表最多只有一个主键,唯一索引可以有多个
索引的底层数据结构详解
hash表
哈希表是键值对的集合,通过key可以快速的取出value值(通过hash算法,可以快速的找到key对应的index,找到了index,也就找到了value)
hash算法中存在一个问题: hash冲突,多个不同的key最后得到的index相同,解决办法就是 链地址法。将冲突数据存放在链表中
B树、B+树
B树 多路平衡搜索树,B+是B树的一种变体
B树的所有的节点即存放键(key)也存放数据(data),B+树只有叶子节点存放key和data,其他节点只存放key
B树的叶子节点都是相互独立的,B+树的叶子节点有一个引用链指向与他相邻的叶子节点
B树检索可能没有达到叶子节点,检索就结束了;B+树任何查找都是从根节点到叶子节点
MySQL中 无论是MyISAM还是InnoDB引擎, 都是使用B+树作为索引结构,但是两者的实现方式不一样(MyISAM 使用的是非聚簇索引InnoDB使用的是聚簇索引)
索引的类型
主键索引(Primary key)--主键列使用的索引
二级索引(辅助索引)
二级索引又称辅助索引,是因为二级索引的叶子节点存储的数据都是主键,也就是说,通过二级索引,可以定位主键的位置
唯一索引、普通索引、前缀索引等索引属于二级索引
聚簇索引和非聚簇索引
聚簇索引
InnoDB引擎中 其 数据本身就是索引文件,其表数据本身就是按照B+树组织的一个索引结构,树的 叶子节点data域保存了完整的数据记录。这个索引的key是数据表的主键。
非聚簇索引
MyISAM引擎中 B+树的 叶子节点的data域存放的是数据记录的地址,在索引检索时,首先按照B+树搜索算法 搜索索引,如果存在指定key ,则取出data域的值,然后以data域的值为地址读取相应的数据记录。
其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址。在根据辅助索引查找时,则需要先取出主键的值,再走一遍主索引
什么是回表查询
回表查询,实际查询两次,通过辅助索引(普通索引)实现的。因为辅助索引叶子节点不存放数据,只存放普通值和对应记录的主键值
id | name | sex | gred |
1 | shenjian | m | A |
3 | zhangsan | m | A |
5 | lisi | m | A |
9 | wangwu | f | B |
create index idx_t_name on user(name);
主键索引(聚簇索引) | 辅助索引(普通索引) |
![]() |
![]()
|
select * from user where name='lisi';
执行流程
先进行一次B+树查找,通过普通索引查找lisi对应的数据(叶子节点中)
再通过叶子节点的data域中保存的lisi对应的主键值,进行一次B+树查找,找到对应的记录行
将数据查询出来
![](https://i-blog.csdnimg.cn/blog_migrate/d0d63e8ad6c38cc6c84584a674bd09ad.png)
一般情况下,只要使用普通索引,并且select 字段不属于索引,单次普通索引Tree查找,无法获取满足的数据,会进行第二次Tree查找
select name from userwhere name='lisi';
覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。我们知道在InnoDB 存储引擎中,如果不是主键索引,叶子节点存储的是主键+列值。最终还是要“回表”,也就是要通过主键再查找一次。这样就会比较慢 覆盖索引就是把要查询出的列和索引是对应的,不做回表操作!
覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了,而无需回表查询。
如主键索引,如果一条 SQL 需要查询主键,那么正好根据主键索引就可以查到主键。
再如普通索引,如果一条 SQL 需要查询 name,name 字段正好有索引,那么直接根据这个索引就可以查到数据,也无需回表。
SQL 优化的几种方式
1.SQL重构 分解SQL
将一个复杂的SQL分解成多个简单的SQL
优势:
让缓存更高效。很方便的缓存单表查询结果对应的结果对象,便于后续直接从结果对象中获取数据
分解后,执行单个查询可以减少表锁的竞争
单表查询效率高于多表复杂查询
2.查询切分
对于结果集很大的查询,采用“分而治之”思想 -->分页查询
3.执行计划
使用执行计划explain关键字,分析查询语句或者表结构的瓶颈
语法:explain select语句;
通过执行计划结果,将会指导我们进一步来重构SQL语句,如:增加索引、调整索引顺序、避免使用某些函数等等
4.遵守原则
永远为每张表设置一个ID主键。
避免使用select *
避免在索引列上使用计算(如果索引列是计算或者函数的一部分,DBMS 的优化器将不会使用索引而使用全表扫描)
为搜索字段建立索引
操作符的优化 尽量不采用不利于索引的操作符,目的就是为了避免全表扫描。
1)in 和 not in慎用,尽量用 between代替in,用 not exists 代替not in
2)is null和is not null慎用
3)!=或<>操作符能不用就不用,否则将使引擎放弃使用索引而进行全表扫描。
MySQL中3种删除数据命令的区别
drop
drop是DDL(数据定义语言),用于整张表的删除,删除表结构、删除表数据
drop tables 表名
delete
delete是DML(数据操作语言),对表中数据的删除,不会删除表结构,也就是说即使你使用delete from 表名;将表中所有数据都删除了,该表依然存在
truncate
truncate是DDL(数据定义语言),使用该命令可以删除表中所有数据,但不会删除表结构。但是该命令不能添加查询条件
truncate的删除原理是重新创建一个表(不包含数据),然后将原来的表删除。
truncate table 表名
相同点
都可以删除整张表中的数据
不同点
删除的范围:drop(删除表中所有数据及表结构)>truncate(删除表中所有数据)>=delete(删除表中所有数据或部分数据)
查询条件:delete可以使用查询条件进行表中数据删除,drop和truncate不可以
命令类型:delete属于DML,drop和truncate属于DDL
数据能否恢复:delete删除的数据可以恢复,但是drop和truncate删除的数据不能恢复
执行效率:drop>truncate>delete