目录
事务
ACID
- 原子性:一个事务要么全部成功,全部失败,不会停在某个阶段
- 一致性 :事务的前后处于一致状态
- 隔离性:多个事务同时操作相同数据库的同一个数据时,一个事务的执行不受另外一个事务的干扰
- 持久性:一个事务一旦提交,则数据将持久化到本地,除非其他事务对其进行修改
脏读
表示一个事务能够读取另一个事务中还未提交的数据。比如,某个事务尝试插入记录 A,此时该事务还未提交,然后另一个事务尝试读取到了记录 A。
幻读
指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录,但是第二次同等条件下查询却有 n+1 条记录,这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据,同一个记录的数据内容被修改了,所有数据行的记录就变多或者变少了。
不可重复读
是指在一个事务内,多次读同一数据。
隔离级别
- 读未提交,最低隔离级别、事务未提交前,就可被其他事务读取(会出现幻读、脏读、不可重复读);
- 提交读,一个事务提交后才能被其他事务读取到(会造成幻读、不可重复读),SQL server 的默认级别;
- 可重复读,保证多次读取同一个数据时,其值都和事务开始时候的内容是一致,禁止读取到别的事务未提交的数据(会造成幻读),MySQL 的默认级别;
- 序列化,代价最高最可靠的隔离级别,该隔离级别能防止脏读、不可重复读、幻读。
造成隔离失效的场景
1.抛出检查异常导致事务不能正确回滚
原因:Spring 默认只会回滚非检查异常
解决方案:配置 rollbackFor 属性 (@Transactional(rollbackFor = Exception.class )
2.业务方法内自己 try-catch 异常导致事务不能正确回滚
原因:事务通知只有捉到了目标抛出的异常,才能进行后续的回滚处理,如果目标自己处理掉异常,事务通知无法知悉
解决方案: 1)异常原样抛出,即在 catch 块添加 throw new RuntimeException(e);2) 手动设置 TransactionStatus.setRollbackOnly() ,在 catch 块添加 TransactionInterceptor.currentTransactionStatus().setRollbackOnly();
3.aop 切面顺序导致导致事务不能正确回滚
原因:事务切面优先级最低,但如果自定义的切面优先级和他一样,则还是自定义切面在内层,这时若自定义切面没有正确抛出异常
解决方案: 1) 和失效原因二一样
2) 调整切面顺序,在 MyAspect 上添加 @Order(Ordered.LOWEST_PRECEDENCE - 1) (不推荐)
4.非 public 方法导致的事务失效
原因:Spring 为方法创建代理、添加事务通知、前提条件都是该方法是 public 的
解决方案:1)使用public方法
5.父子容器导致的事务失效
原因:子容器扫描范围过大,把未加事务配置的 service 扫描进来
解决方案:1)各扫描各的,不要图简便
2)不要用父子容器,所有 bean 放在同一容器
锁
在进行数据库操作时,多个线程同时修改同一条数据库数据可能会引发并发冲突和数据不一致的问题。为了解决这个问题,可以采用乐观锁机制或悲观锁机制。
1. 乐观锁机制:
在数据库表中添加一个版本号(或时间戳)字段,用于标识数据的版本。
当多个线程同时修改数据时,每个线程在读取数据时会获取当前的版本号。
在更新数据时,检查当前数据的版本号是否与读取时获取的版本号一致,如果一致则进行更新,否则抛出并发冲突异常。
在MyBatis-Plus中,可以使用`@Version`注解来标识版本号字段。
import com.baomidou.mybatisplus.annotation.Version;
public class User {
// 其他字段...
@Version
private Integer version;
// Getters and Setters...
}
2. 悲观锁机制:
在数据库事务中,使用SELECT...FOR UPDATE语句来锁定要修改的数据,防止其他线程同时修改。
在MyBatis-Plus中,可以使用`@Select("SELECT * FROM table_name WHERE id = #{id} FOR UPDATE")`注解来实现悲观锁。
@Mapper
public interface UserMapper {
@Select("SELECT * FROM user WHERE id = #{id} FOR UPDATE")
User selectUserForUpdate(Long id);
}
示例
这里使用MyBatis-Plus进行实现
1.设置MyBatis-Plus的配置类
package com.bsh.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
@Configuration
public class MybatisPlusConfig {
/**
* 新的分页插件,一级缓存和二级缓存遵循遵循mybatis的规则,需要设置mybatisConfiguration #useDeprecatedExecutor = false 避免缓存出现问题
* @return
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
//向Mybatis过滤器链中添加分页拦截器
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MARIADB));
return mybatisPlusInterceptor;
}
@Bean
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
// 配置其他属性...
return sessionFactory.getObject();
}
@Bean
public ExecutorService executorService() {
return Executors.newFixedThreadPool(10); // 创建线程池
}
}
2.创建多线程修改同一数据
package com.bsh.service.impl;
import com.bsh.entity.Bo.User;
import com.bsh.mapper.UserMapper;
import com.bsh.service.UserService;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.Random;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
@Service
public class UserServiceImpl implements UserService {
private final ExecutorService executorService;
private final SqlSessionFactory sqlSessionFactory;
@Autowired
public UserServiceImpl(ExecutorService executorService, SqlSessionFactory sqlSessionFactory) {
this.executorService = executorService;
this.sqlSessionFactory = sqlSessionFactory;
}
@Resource
private UserMapper userMapper;
@Transactional(rollbackFor = Exception.class)
@Override
public void addUserData() {
for (int i = 0; i < 10000000; i++) {
String uuid = UUID.randomUUID().toString().replaceAll("-", "");
// 截取前11位作为随机数
String randomNum = uuid.substring(0, 11);
// 创建随机数生成器
Random random = new Random();
// 生成1或2的随机数
int randomNumber = random.nextInt(2) + 1;
User user = new User();
user.setUserName("cdd" + i);
user.setPhone(randomNum);
user.setSex(randomNumber);
user.setPassword(uuid);
user.setBalance((double) i);
user.setNote("这是第" + i + "条");
userMapper.insert(user);
}
}
@Override
public void updateData(String userId, String password) {
executorService.submit(() -> {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 同时操作同一条数据库数据
User user = userMapper.selectById(userId);
user.setPassword(password);
userMapper.updateById(user);
sqlSession.commit();
}
});
}
}
类型
类型 | 数据类型 | 数据大小 | 数据范围 |
整数类型 | TINYINT | 1字节 | 范围为-128到127(有符号)或0到255(无符号) |
SMALLINT | 2字节 | 范围为-32,768到32,767(有符号)或0到65,535(无符号) | |
INT | 4字节 | 范围为-2,147,483,648到2,147,483,647(有符号)或0到4,294,967,295(无符号) | |
BIGINT | 8字节 | ,范围为-9,223,372,036,854,775,808到9,223,372,036,854,775,807(有符号)或0到18,446,744,073,709,551,615(无符号) | |
浮点数类型 | FLOAT | 4字节 | 单精度浮点数 |
DOUBLE | 8字节 | 双精度浮点数 | |
字符串类型 | CHAR(N) | 固定长度的字符 | 最多可存储N个字符 |
VARCHAR(N) | 可变长度的字符 | 最多可存储N个字符 | |
TEXT | 可变长度的文本 | 最大长度为65,535个字符 | |
BLOB | 二进制大对象 | 可存储大量的二进制数据 | |
日期和时间类型 | DATE | 日期,格式为'YYYY-MM-DD | |
TIME | 时间,格式为'HH:MM:SS’ | ||
DATETIME | 8字节 | 时间,格式为'HH:MM:SS | |
TIMESTAMP | 自动记录时间戳,格式为'YYYY-MM-DD HH:MM:SS' | ||
定点数类型 | DECIMAL(M, D) | 用于存储精确小数,M表示总位数,D表示小数点后的位数 | |
枚举类型 | ENUM | ||
集合类型 | SET | ||
JSON类型 |
char与varchar的区别
在MySQL中,CHAR和VARCHAR是两种常见的字符串数据类型,它们之间存在一些区别。
1. 存储方式:CHAR是固定长度的字符串类型,而VARCHAR是可变长度的字符串类型。当使用CHAR类型定义一个列时,MySQL会分配固定长度的存储空间,不论实际存储的数据是否达到了指定的长度。而对于VARCHAR类型,MySQL只会分配实际存储的数据所需的存储空间。
2. 空间占用:由于CHAR是固定长度的,它在存储每个值时都会使用指定长度的存储空间,无论实际存储的数据是否达到了指定长度。相比之下`VARCHA类型的存储空间取决于实际存储的数据长度。因此,当存储的数据长度较短时,VARCHAR比CHAR更节省存储空间。
3. 填充空格:当存储一个短于指定长度的字符串时,CHAR类型会使用空格进行填充,以达到指定长度。而VARCHAR类型不会填充空格。
4. 查询性能:由于CHAR是固定长度的,对于固定长度的查询,CHAR类型的查询性能可能会略优于VARCHAR类型。因为对于CHAR类型,MySQL可以直接计算偏移量来获取数据。而对于VARCHAR类型,MySQL需要先读取存储在表中的额外长度信息,然后再根据偏移量来获取数据。
基于上述区别,一般建议在以下情况下使用:
- 使用`CHAR`类型当存储的字符串长度是固定的,例如存储国家代码、固定长度的标识符等。
- 使用`VARCHAR`类型当存储的字符串长度可变,例如存储用户的姓名、评论内容等。
需要注意的是,`CHAR`和`VARCHAR`类型都有最大长度限制,对于`CHAR`类型是255字节,对于`VARCHAR`类型是65,535字节。在选择合适的数据类型和长度时,需要根据实际需求和存储空间的限制进行权衡。
索引
索引分为
- 主键
- 单值索引
- 索引
- 唯一索引
- 复合索引
字段类型 | 数据类型 | 索引类型 | 索引方法 |
字符串类型 | CHAR 、VARCHAR 、TEXT | INDEX、UNIQUE、PRIMARY KEY、FULLTEXT | BTREE/HASH |
整数类型 | INT 、BIGINT 、TINYINT | INDEX、UNIQUE、PRIMARY KEY、FULLTEXT | BTREE/HASH |
浮点数类型 | FLOAT 、DOUBLE | INDEX(普通索引) | BTREE/HASH |
日期和时间类型 | DATE 、TIME、 DATETIME | INDEX | BTREE/HASH |
枚举类型、集合类型 | INDEX | BTREE/HASH |
索引失效的情况
- 模糊查询like以%开头,
- 数据类型错误
- 对索引字段使用内部函数
- 索引列是null
- 索引列运行四则运算
- 复合索引不按索引列最左特性开始查找
约束
1.主键约束
保证数据的唯一性,并且主键列数据不能为空(唯一性,非空性)。
1.在创建表时添加约束
CREATE TABLE table_name (id INT PRIMARY KEY, ...)
2.表创建完成后添加约束
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
2.唯一约束
保证数据的唯一性,唯一约束的列可以为空(唯一性,可以空,但只能有一个)。
1.在创建表时添加约束
CREATE TABLE table_name (email VARCHAR(50) UNIQUE, ...)
2.表创建完成后添加约束
ALTER TABLE tb_class ADD UNIQUE (class_name);
3.外键约束
需要建立两表间的关系并引用主表的列,保证数据的完整性,互相依赖的数据不能缺失。
1.在创建表时添加约束
CREATE TABLE orders (order_id INT, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id));
2.表创建完成后添加约束
ALTER TABLE 外键表ADD CONSTRAINT 外键约束名称FOREIGN KEY (外键字段) REFERENCES 主键表(主键字段);
4.非空约束
数据列不能为空
1.在创建表时添加约束
CREATE TABLE table_name (name VARCHAR(50) NOT NULL, ...)
2.表创建完成后添加约束
ALTER TABLE 表名 MODIFY 字段 字段类型 NOT NULL;
5.检查约束
保证数据的有效性,让值在有效范围内取值,对该列数据的范围、格式的限制(如:年龄、性别等)
1.在创建表时添加约束
CREATE TABLE table_name (age INT CHECK (age >= 18), ...)
2.表创建完成后添加约束
alter table 表名
add constraint 检查约束名 check(字段 约束条件);
视图
MySQL视图是一种虚拟表,它基于一个或多个表的查询结果构建而成,并且与实际表具有相似的行为。视图可以被视为存储在数据库中的预定义查询。
要创建一个MySQL视图,可以使用CREATE VIEW语句。以下是创建MySQL视图的基本语法:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
其中,`view_name`是视图的名称,`column1, column2, ...`是要选择的列,`table_name`是要查询的表,`condition`是可选的过滤条件。
以下是一个示例,演示如何创建一个简单的MySQL视图:
CREATE VIEW customer_view AS
SELECT customer_id, first_name, last_name
FROM customers
WHERE country = 'USA';
上述代码将创建一个名为`customer_view`的视图,该视图从名为`customers`的表中选择`customer_id`、`first_name`和`last_name`列,并且只包括`country`为'USA'的行。
创建视图后,可以像查询实际表一样使用它们。例如,可以使用SELECT语句从视图中检索数据:
SELECT * FROM customer_view;
视图还可以与其他表进行连接、过滤和排序,就像操作实际表一样。
如果需要更新视图中的数据,可以使用UPDATE、INSERT和DELETE语句,就像对实际表进行操作一样。然而,需要注意的是,有些视图是不可更新的,具体取决于视图的定义和查询中使用的功能。
要删除一个视图,可以使用DROP VIEW语句:
DROP VIEW view_name;
引擎
MySQL支持多种存储引擎,每个存储引擎具有不同的特点和适用场景。以下是一些常见的MySQL存储引擎:
1. InnoDB:InnoDB是MySQL的默认存储引擎。它支持事务处理和行级锁定,并具有高度可靠性和高性能的特点。InnoDB还提供了外键约束和崩溃恢复能力。它适用于需要事务支持和高并发读写的应用。
2. MyISAM:MyISAM是MySQL的另一个常用存储引擎。它不支持事务处理,但具有较快的读取速度和较小的存储空间占用。MyISAM适用于读密集型的应用,例如数据仓库、报表生成等。
3. Memory:Memory存储引擎将数据存储在内存中,因此读写速度非常快。但是,它具有易失性,即在服务器关闭或重启时数据会丢失。Memory存储引擎适用于需要快速读写临时数据的场景,例如缓存表、临时计算等。
4. Archive:Archive存储引擎用于存储和检索大量的归档数据。它以高压缩比存储数据,并且对于插入和查询操作具有较快的速度。但是,Archive存储引擎不支持索引和更新操作。
5. CSV:CSV存储引擎以纯文本的CSV格式存储数据。它适用于需要与其他应用程序或工具交换数据的场景。
除了以上列举的存储引擎,MySQL还支持其他存储引擎,如Blackhole、Federated等。每个存储引擎具有不同的功能和适用性,因此在选择存储引擎时需要考虑应用程序的需求和性能要求。
可以使用以下SQL语句查看MySQL中可用的存储引擎:
SHOW ENGINES;
该语句将列出MySQL服务器上可用的存储引擎以及它们的状态。
引擎 | 不同点 | 适用的场景 |
MyISAM | 1.不支持事务和行级锁, 2.提供表级锁 | 适合读操作频繁、写操作较少的场景。 适用于静态数据和只读数据的存储。 |
InnoDB(默认的MySQL存储引擎) | 1.支持事务和行级锁, 2.提供崩溃恢复机制和数据完整性保护。 3.支持外键约束和事务的ACID特性。 | 适合高并发、写操作频繁的场景。大多数应用场景。 |
MEMORY | 将数据存储在内存中,读写速度非常快, 不支持持久化 | 适用于对临时数据进行高速缓存、计算和处理的场景。 |
NDB Cluster | 提供分布式数据库的支持,数据存储在多个节点上。 支持高可用性和自动分片。 | 适用于需要高可用性和可伸缩性的大规模应用。 |
ARCHIVE | 数据压缩率高 | 专门用于存储和查询归档数据。适用于数据存档和历史数据查询。 |
CSV | 将数据以逗号分隔值的形式存储在文本文件中。 不支持索引 | 适合用于数据导入和导出 |
B+树
主从
大数据
不同数据量的插入/查询/更新/删除
环境
硬件 | 1C(8核) / 16G / 500G |
系统 / 工具 | win11 / Idea2021.2.3 |
操作方式 | MybatisPlus 对象方式 |
数据库版本 | Mariadb 数据库(5.5.68-MariaDB) |
引擎 | InnoDB |
字符集/排序规则 | utf-8 / utf8-bin |
索引/约束/外键 | 无 |
网络延迟 | win系统(idea) vmware(centos7) |
插入
数据量 | 时间 | 方式 | 代码 |
200万 | 25分钟 | 新增 | StopWatch stopWatch = new StopWatch(); stopWatch.start("入库"); for (int i = 0; i < 2000000; i++) { User user = new User(); user.setUserName("hj"+i); user.setNote("测试备注"+i); user.setPassword("123123"); user.setPhone("13121952195"); user.setSex(1); user.setBalance(1.58); myTestMapper.insert(user); } stopWatch.stop(); System.out.println(stopWatch.prettyPrint()); |
1000万 | 53分钟39秒 | 新增 (10个线程,每个线程100万) | |
1000万 | 新增 (一个事务) | ||
200万 | 1分钟不到 | 删除 | StopWatch stopWatch = new StopWatch(); stopWatch.start("删除"); myTestMapper.delete(new QueryWrapper<User>().isNotNull("id")); stopWatch.stop(); System.out.println(stopWatch.prettyPrint()); |
700万 | 23秒 | 查询总数 |
查询
查询总数
数据量 | 耗时(秒) | 方式 |
700w(再插入的途中查询的) | 23 | select count(*) from my_user |
1000w | 19 | select count(*) from my_user |
1000w | 21 | select count(*) from my_user |
1000w | 15 | select count(*) from my_user |
1000w | 10.9 | select count(*) from my_user |
1000w | 10.18 | select count(*) from my_user |
1000w | 10.3 | select count(*) from my_user |
查询的耗时越来越小。可以有以下原因。
1. 操作系统将磁盘的数据加载到内存中所以速度快了
2. InnoDB缓冲池,缓存了数据库的数据页,减少磁盘I/O操作,减少了耗时
3. MariadbDB提供了查询缓存的功能,可以缓存查询语句和响应的结果集。但是查询缓存对于频繁更新的表/大型结果集不太适合,所以10.4版本被废弃了。这里mariadb版本是5.5.68还有缓存功能。可能用到了缓存。
单个等于条件查询
数据量1000w,无索引
耗时(秒)/(次) | sql | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
1314.0473906 53.4798253 | select * from `my_user` where user_name = 'm0819MTU' | 无 | 无 | 无 | 大数据量查询会很慢 |
0.0511096 0.0084089 | select * from `my_user` where user_name = 'm0819MTU' | NORMAL(基本的索引) | BTREE | 102.3685951 101.1714666 | user_name添加了基本索引,查询明显块了 |
两个等于条件查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
51.1781119 51.4915773 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 | 无 | 无 | 无 | 无 | 为什么时间越来越长 |
0.3020055 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 | user_name | NORMAL(基本的索引) | BTREE | 993.2805428 | |
1990.8425113 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 | sex | NORMAL(基本的索引) | BTREE | 1927.0790536 | |
0.1483153 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 | user_name,sex | NORMAL(基本的索引) | BTREE | 641.7624486 | |
46.9917692 48.651719 | select * from `my_user` where sex = 0 and user_name = 'm0819MTU' | 无 | 无 | 无 | 无 | |
0.0006254 | select * from `my_user` where sex = 0 and user_name = 'm0819MTU' | user_name | NORMAL(基本的索引) | BTREE | 993.2805428 | |
600.037661 | select * from `my_user` where sex = 0 and user_name = 'm0819MTU' | sex | NORMAL(基本的索引) | BTREE | 1927.0790536 | |
0.0007004 | select * from `my_user` where sex = 0 and user_name = 'm0819MTU' | user_name,sex | (复合索引) | BTREE | 641.7624486 | |
78.2026062 | select * from `my_user` where user_name = 'm0819MTU ' or sex = 0 | 无 | 无 | 无 | 无 | |
2003.3290889 | select * from `my_user` where user_name = 'm0819MTU' or sex = 0 | user_name | NORMAL(基本的索引) | BTREE | 993.2805428 | |
1383.786355 | select * from `my_user` where user_name = 'm0819MTU' or sex = 0 | sex | NORMAL(基本的索引) | BTREE | 1927.0790536 | |
2043.4194128 | select * from `my_user` where user_name = 'm0819MTU' or sex = 0 | sex,user_name | NORMAL(基本的索引) | BTREE | 641.7624486 | |
288.1063669 | select * from `my_user` where sex = 0 or user_name = 'm0819MTU ' | 无 | 无 | 无 | 无 | |
1932.8533332 | select * from `my_user` where sex = 0 or user_name = 'm0819MTU ' | user_name | NORMAL(基本的索引) | BTREE | 993.2805428 | |
77.1472803 | select * from `my_user` where sex = 0 or user_name = 'm0819MTU ' | sex | NORMAL(基本的索引) | BTREE | 1927.0790536 | |
138.4565387 | select * from `my_user` where sex = 0 or user_name = 'm0819MTU ' | sex,user_name | NORMAL(基本的索引) | BTREE | 641.7624486 |
三个等于条件查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
296.344 1216.878 79.496 95.570 107.789 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ | 无 | 无 | 无 | 无 | |
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ | phone | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ | user_name, sex,phone | 复合索引 | ||||
77.8880232 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 or phone = ‘15610056939’ | 无 | 无 | 无 | 无 | |
select * from `my_user` where user_name = 'hj2584567' and sex = 0 or phone = ‘15610056939’ | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 or phone = ‘15610056939’ | phone | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 or phone = ‘15610056939’ | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' and sex = 0 or phone = ‘15610056939’ | user_name, sex,phone | 复合索引 | BTREE | |||
49.518618 | select * from `my_user` where user_name = 'm0819MTU' or sex = 0 and phone = '15610056939'; | 无 | 无 | 无 | 无 | |
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 and phone = '15610056939'; | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 and phone = '15610056939'; | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 and phone = '15610056939'; | phone | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 and phone = '15610056939'; | user_name, sex,phone | 复合索引 | BTREE | |||
66.5336563 | select * from `my_user` where user_name = 'm0819MTU' or sex = 0 or phone = '15610056939'; | 无 | 无 | 无 | 无 | |
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 or phone = '15610056939'; | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 or phone = '15610056939'; | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 or phone = '15610056939'; | phone | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name = 'm0819MTU' or sex = 0 or phone = '15610056939'; | user_name, sex,phone | 复合索引 | BTREE |
多个等于条件查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
47.7848422 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ and balance = '390.51' | 无 | 无 | 无 | 无 | |
0.121413 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ and balance = '390.51' | user_name | NORMAL(基本的索引) | BTREE | 121.9631695 | |
1970.2271485 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ and balance = '390.51' | sex | NORMAL(基本的索引) | BTREE | 1958.7972847 | |
0.1766105 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ and balance = '390.51' | phone | NORMAL(基本的索引) | BTREE | 149.4697939 | |
1.5557255 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ and balance = '390.51' | balance | NORMAL(基本的索引) | BTREE | 1977.7742727 | |
0.2082178 | select * from `my_user` where user_name = 'm0819MTU' and sex = 0 and phone = ‘15610056939’ and balance = '390.51' | user_name, sex,phone,balance | 复合索引 | 2059.3011775 |
Like 条件查询
耗时 (秒)/(次) | sql | 索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
2058.0407574 | select * from `my_user` where user_name like '%0819%' | 无 | 无 | 无 | 无 | |
2025.637244 | select * from `my_user` where user_name like '%0819%' | user_name | NORMAL(基本的索引) | BTREE | 775.2723692 | 模糊查询以%开头索引失效了 |
691.9975826 | select * from `my_user` where user_name like '%0819%' and sex = 0 | 无 | 无 | 无 | 无 | |
346.9835451 | select * from `my_user` where user_name like '%0819%' and sex = 0 | user_name | NORMAL(基本的索引) | BTREE | 775.2723692 | |
2023.6479041 | select * from `my_user` where user_name like '%0819%' and sex = 0 | sex | NORMAL(基本的索引) | BTREE | 521.8166577 | |
select * from `my_user` where user_name like '%0819%' and sex = 0 | user_name,sex | NORMAL(基本的索引) | BTREE | 954.9402814 | ||
3726.042 | select * from `my_user` where user_name like '%0819%' or sex = 0 | 无 | 无 | 无 | 无 | |
75.6335019 | select * from `my_user` where user_name like '%0819%' or sex = 0 | user_name | NORMAL(基本的索引) | BTREE | 775.2723692 | |
1281.2973505 | select * from `my_user` where user_name like '%0819%' or sex = 0 | sex | NORMAL(基本的索引) | BTREE | 521.8166577 | |
select * from `my_user` where user_name like '%0819%' or sex = 0 | user_name,sex | NORMAL(基本的索引) | BTREE | 954.9402814 | ||
51.066749 | SELECT * FROM my_user WHERE sex = 0 and user_name LIKE '%0819%' | 无 | 无 | 无 | 无 | |
47.4951965 | SELECT * FROM my_user WHERE sex = 0 and user_name LIKE '%0819%' | user_name | NORMAL(基本的索引) | BTREE | 775.2723692 | |
101.5677375 | SELECT * FROM my_user WHERE sex = 0 and user_name LIKE '%0819%' | sex | NORMAL(基本的索引) | BTREE | 521.8166577 | |
SELECT * FROM my_user WHERE sex = 0 and user_name LIKE '%0819%' | user_name,sex | NORMAL(基本的索引) | BTREE | 954.9402814 | ||
67.2604578 | SELECT * FROM my_user WHERE sex = 0 or user_name LIKE '%0819%' | 无 | 无 | 无 | 无 | |
67.8061277 | SELECT * FROM my_user WHERE sex = 0 or user_name LIKE '%0819%' | user_name | NORMAL(基本的索引) | BTREE | 775.2723692 | |
152.65516 | SELECT * FROM my_user WHERE sex = 0 or user_name LIKE '%0819%' | sex | NORMAL(基本的索引) | BTREE | 521.8166577 | |
SELECT * FROM my_user WHERE sex = 0 or user_name LIKE '%0819%' | user_name,sex | NORMAL(基本的索引) | BTREE | 954.9402814 |
right Like 条件查询
耗时 (秒)/(次) | sql | 索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
93.305 113.803 139.981 572.866 1271.222 | select * from `my_user` where user_name like 'MTU%' | 无 | 无 | 无 | 无 | 无索引查询慢 |
select * from `my_user` where user_name like 'MTU%' | user_name | NORMAL(基本的索引) | BTREE | 53.757 | 模糊查询以%结尾的索引有效 | |
4447.774 3719.307 | select * from `my_user` where user_name like 'MTU%' and sex = 0 | 无 | 无 | 无 | 无 | |
select * from `my_user` where user_name like 'MTU%' and sex = 0 | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name like 'MTU%' and sex = 0 | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where sex = 0 and user_name like 'MTU%' | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where sex = 0 and user_name like 'MTU%' | 无 | 无 | 无 | 无 | ||
select * from `my_user` where user_name like 'MTU%' or sex = 0 | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name like 'MTU%' or sex = 0 | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where sex = 0 or user_name like 'MTU%' | sex | NORMAL(基本的索引) | BTREE |
Left Like 条件查询
耗时 (秒)/(次) | sql | 索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
291.196 122.449 556.373 1259.092 4445.564 | select * from `my_user` where user_name like '%m089' | 无 | 无 | 无 | 无 | 无索引查询慢 |
select * from `my_user` where user_name like '%m089' | user_name | NORMAL(基本的索引) | BTREE | 53.757 | 模糊查询以%开头索引失效了 | |
select * from `my_user` where user_name like '%m089' and sex = 0 | 无 | 无 | 无 | |||
select * from `my_user` where user_name like '%m089' and sex = 0 | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name like '%m089' and sex = 0 | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where sex = 0 and user_name like '%m089' | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where sex = 0 and user_name like '%m089' | 无 | 无 | 无 | |||
select * from `my_user` where user_name like '%m089' or sex = 0 | user_name | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where user_name like '%m089' or sex = 0 | sex | NORMAL(基本的索引) | BTREE | |||
select * from `my_user` where sex = 0 or user_name like '%m089' | user_name | NORMAL(基本的索引) | BTREE |
分组查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
69.909 118.269 97.688 149.610 177.061 | select count(*) from `my_user` group by sex | 无 | 无 | 无 | 无 | 无索引查询慢 |
select count(*) from `my_user` group by sex | sex | NORMAL(基本的索引) | BTREE | 分组查询的时对分组得数据添加索引,查询速度快 | ||
593.610 1230.767 4440.038 3696.245 | select max(balance) from `my_user` group by sex | 无 | 无 | 无 | 无 | |
select max(balance) from `my_user` group by sex | sex | NORMAL(基本的索引) | BTREE | |||
select min(balance) from `my_user` group by sex | 无 | 无 | 无 | 无 | ||
select min(balance) from `my_user` group by sex | sex | NORMAL(基本的索引) | BTREE | |||
select sum(balance) from `my_user` group by sex | 无 | 无 | 无 | 无 | ||
select sum(balance) from `my_user` group by sex | sex | NORMAL(基本的索引) | BTREE | |||
select AVG(balance) from `my_user` group by sex | 无 | 无 | 无 | 无 | ||
select AVG(balance) from `my_user` group by sex | sex | NORMAL(基本的索引) | BTREE |
排序查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
SELECT * FROM my_user order by create_date | 无 | 无 | 无 | 无 | ||
SELECT * FROM my_user order by create_date | create_date | NORMAL(基本的索引) | BTREE | |||
SELECT * FROM emp order by create_date desc | 无 | 无 | 无 | 无 | ||
SELECT * FROM emp order by create_date desc | create_date | NORMAL(基本的索引) | BTREE |
分页查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
0.666 0.040 0.039 0.036 0.033 | SELECT * FROM my_userLIMIT 0,100 | 无 | 无 | 无 | 无 | 分页查询,一页查询100条就很快,因此可以使用分页查询对大数据进行查询操作 |
0.193 0.048 0.053 0.048 0.065 | SELECT * FROM my_userLIMIT 0,1000 | 无 | 无 | 无 | 无 | |
SELECT * FROM my_user where sex = 1 LIMIT 0,100 | sex | NORMAL(基本的索引) | BTREE | |||
SELECT * FROM my_user where balance > 500 LIMIT 0,100 | balance | NORMAL(基本的索引) | BTREE |
添加函数查询
耗时 (秒)/(次) | sql | 添加索引字段 | 索引类型 | 索引方法 | 添加索引时间 | 总结 |
204.656 122.638 88.122 101.746 133.873 | select length(user_name) from my_user -- 数据的长度 | 无 | 无 | 无 | 无 | |
select length(user_name) from my_user -- 数据的长度 | user_name | NORMAL(基本的索引) | BTREE | |||
690.009 1207.624 4414.638 3668.100 | SELECT dname,SUBSTR(user_name,1,3) new_name FROM my_user ; -- 截取[1,3] | 无 | 无 | 无 | 无 | |
SELECT dname,SUBSTR(user_name,1,3) new_name FROM my_user ; -- 截取[1,3] | user_name | NORMAL(基本的索引) | BTREE | |||
SELECT user_name,LOWER(user_name) new_name from my_user ; -- 数据转小写 | 无 | 无 | 无 | 无 | ||
SELECT user_name,LOWER(user_name) new_name from my_user ; -- 数据转小写 | user_name | NORMAL(基本的索引) | BTREE | |||
SELECT user_name,LOWER(user_name) from my_user ; -- 数据转小写 | 无 | 无 | 无 | 无 | ||
SELECT user_name,LOWER(user_name) from my_user ; -- 数据转小写 | user_name | NORMAL(基本的索引) | BTREE | |||
select user_name,concat(user_name,'123') from my_user -- 拼接数据 | 无 | 无 | 无 | 无 | ||
select user_name,concat(user_name,'123') from my_user -- 拼接数据 | user_name | NORMAL(基本的索引) | BTREE | |||
select user_name,replace(user_name,'a','666') new_name from my_user --把a字符替换成666 | 无 | 无 | 无 | 无 | ||
select user_name,replace(user_name,'a','666') new_name from my_user --把a字符替换成666 | user_name | NORMAL(基本的索引) | BTREE |
计算方式
数据大小 = 行数 × 列大小
MVCC
慢查询
慢查询是指在数据库中执行时间较长的查询操作。它可能导致性能下降,影响系统的响应时间和用户体验。为了解决慢查询问题,可以采取以下步骤:
1. 识别慢查询:通过监控数据库性能或使用性能分析工具,确定执行时间超过预期阈值的查询。在MariaDB中,可以启用慢查询日志来记录执行时间较长的查询语句。
2. 分析查询执行计划:使用EXPLAIN命令分析慢查询的执行计划。执行计划可以帮助你了解查询是如何执行的,是否存在索引失效、全表扫描等性能问题。
3. 优化查询语句:根据查询执行计划和业务需求,对慢查询语句进行优化。可以考虑重写查询、添加适当的索引、优化查询条件等手段来改善查询性能。
4. 调整数据库配置:检查数据库的配置参数,如缓冲区大小、连接数限制等,根据系统负载和资源配置合理调整。
5. 监控和调优:持续监控慢查询情况,确保优化措施的有效性。定期进行数据库性能调优,以保持系统的良好性能。
BinLog
其他
触发器
MySQL触发器(Trigger)是一种数据库对象,它在指定的表上自动执行与触发事件相关联的操作。触发器通常与INSERT、UPDATE或DELETE操作相关联,当这些操作在表上执行时,触发器会自动触发并执行定义的操作。
以下是MySQL触发器的基本语法:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
-- 触发器操作语句
END;
其中:
- `trigger_name`是触发器的名称,可以自定义。
- `{BEFORE | AFTER}`指定触发器是在触发事件之前还是之后执行。
- `{INSERT | UPDATE | DELETE}`指定触发器与哪种操作相关联。
- `table_name`是要在其上创建触发器的表的名称。
- `FOR EACH ROW`指定触发器为每一行数据执行操作。
- `BEGIN`和`END`之间是触发器的操作语句,可以是单个语句或包含多个语句的代码块。
以下是一个创建触发器的示例,该触发器在每次向`orders`表中插入新行时自动更新相关的`order_count`列:
CREATE TRIGGER update_order_count
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE order_summary
SET order_count = order_count + 1
WHERE customer_id = NEW.customer_id;
END;
上述代码中,`update_order_count`是触发器的名称,`AFTER INSERT ON orders`表示触发器在`orders`表的插入操作之后执行。`NEW`是一个特殊的关键字,表示正在插入的新行。
触发器可以用于执行各种操作,如更新其他表、插入记录、验证数据等,以满足特定的业务需求。
要查看数据库中已定义的触发器,可以使用以下SQL语句:
SHOW TRIGGERS;
Q&A
sql语句
这里主要记录mariadb。对一些sql也会记录Mysql的差别
查询数据库版本
SELECT VERSION();
sql函数
函数类型 | 函数式 | 作用 | 实例 |
字符串函数 | CONCAT(str1, str2, ...) | 连接多个字符串。 | |
LENGTH(str) | 返回字符串的长度 | ||
SUBSTRING(字段, 截取开始位置, 截取长度) | 提取字符串的子串 | ||
UPPER(str) | 将字符串转换为大写 | ||
LOWER(str) | 将字符串转换为小写 | ||
REPLACE(字段, 被替换的字符串,替换成的字符串) | 替换字符串中的部分内容 | ||
TRIM(str) | 去除字符串两端的空格 | ||
数值函数 | ABS(x) | 返回x的绝对值 | |
ROUND(x, d) | 对x进行四舍五入,保留d位小数 | ||
CEILING(x) | 向上取整。 | ||
FLOOR(x) | 向下取整 | ||
RAND() | 返回一个随机数 | ||
时间和日期函数 | DATE_FORMAT(date, format): | 将日期格式化为指定的格式。 | |
NOW() | 返回当前日期和时间。 | ||
YEAR(date) | 提取日期的年份 | ||
MONTH(date) | 提取日期的月份 | ||
DAY(date) | 提取日期的天数 | ||
DATEDIFF(date1, date2) | 计算两个日期之间的天数差。 | ||
聚合函数 | COUNT(expr) | 计算匹配条件的行数 | |
SUM(expr) | 计算匹配条件的列值之和 | ||
AVG(expr) | 计算匹配条件的列值的平均值。 | ||
MAX(expr) | 返回匹配条件的列的最大值。 | ||
MIN(expr) | 返回匹配条件的列的最小值 |