
mysql
mysql笔记
陈叔叔 发表了文章 • 0 个评论 • 60 次浏览 • 2022-05-24 10:51
DBMS,SQL,DB三者的关系
DBMS:数据库管理系统
SQL:用于访问和处理数据库的标准的计算机语言
DB:数据库
三者的关系:DBMS数据库管理系统执行SQL语句,来操作DB数据库当中的数据的
关于in与or的建议
条件查询 in(a,b,c,...)
可以替代or,提高检索效率
例:
select id,name,age from student where name = '张三' or name = '李四'替换为:select id,name,age from student where name in ('张三' , '李四')
模糊查询like
%代表任意多个字符
_代表任意1个字符
例1:查询姓张的同学select id,name,age from student where name like '张%'
例2:查询姓李且只有两个字的同学select id,name,age from student where name like '李_'
ps:CSDN遵循Markdown语法,不能打出下划线,所以需要转义字符,如_
排序 order by
asc 升序(默认)
desc 降序
排序 order by sal 薪资 asc/desc ;
例:按薪水为公司的员工排序降序select name,age,sal from emp order by sal desc
SQL完整的语句系统的执行顺序(重要)
select *5from tableName 1 where 条件(join) 2group by ...3having ...4order by ...6limit ...7
分组函数
分组函数:多行处理函数
函数 | 说明 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
注意: |
- 分组函数一共5个
- 输入多行,输出结果总是1行
- 分组函数自动忽略NULL
- NULL参与运算时,结果为NULL,解决办法是:ifnull(comm,0)
- 分组函数不可直接使用在where子句中,因为分组函数是在group by执行之后才会执行
- count(*) 统计总记录的条数,count(字段名) 统计某个字段不为NULL的数量
聚合函数 group by
group by :按照某个字段或者某些字段进行分组
having :对分组之后的数据进行再次过滤
例1:按公司员工的工作岗位进行分组select name,job from emp group by job;
例2:找出每个部门的最高薪资,要求显示薪资大于2900的数据select max(sal),deptno from emp group by deptno having max(sal)>2900; 效率低select max(sal),deptno from emp where sal>2900 group by deptno; 效率高,建议能够使用where过滤的尽量使用能够在分组之前过滤的,就先进行where过滤
例3:找出每个部门的平均薪资,要求显示薪资>2000的数据步骤1.找出每个部门的平均薪资 select deptno,avg(sal) from emp group by deptno;步骤2.要求显示薪资>2000的数据 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
去重 distinct
select distinct job from emp;distinct 只能出现在所有字段的最前边,表示字段的联合去重
连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的数据
一般一个业务都会对应多张表,比如:学生和班级,起码两张表
连接查询的分类
根据语法出现的年代来划分
- SQL92(一些老的DBA可能还在使用这种语法。DBA:数据库管理员)
- SQL99(比较新的语法)
根据表的连接方式来划分
内连接:
- 等值连接
- 非等值连接 between and
- 自连接
外连接
- 左外连接(左连接)
- 右外连接(右连接)
全连接(很少用)
注:在表的连接查询方面有一种现象被称为:笛卡尔积现象
在数据库中,两张表连接查询默认显示的结果条数是两张表记录条数的乘积
案例:找出每一个员工的部门名称,要求显示员工名和部门名 select ename,dname from emp,dept;
如何避免笛卡尔积现象?
避免了笛卡尔积现象,不会减少记录的匹配次数,只不过显示的是有效记录
等值连接
select //SQL92 旧语法 e.ename,d.dnamefrom emp e,dept dwhere e.deptno = d.deptno; **内连接之等值连接:**特点:条件是等量关系select //SQL99 新语法,常用的 e.ename,d.dnamefrom emp ejoin dept don e.deptno = d.deptno; 语法: ... A (inner) join //inner 可读性更强,但可以省略 B on 连接条件 where ...SQL99语法结构更清晰,表的连接条件和后来的where条件分离了
内连接之非等值连接:
特点:连接条件中的关系是非等量的关系
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级 select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
内连接之自连接
特点:一张表看做两张表,自己连接自己
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名 select a.ename,b.ename from emp a inner join emp b on a.mgr = b.empno;
外连接
内连接:
假设A表和B表进行连接,使用内连接,凡是A表和B表能够匹配上的记录查询出来
AB两张表没有主副之分,两张表是平等的
外连接:
假设A表和B表进行连接,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的
数据,捎带着查询副表,当副表的数据没有和主表数据匹配上,副表自动模拟出NULL与之匹配
外连接的分类
左外连接(左连接):表示左边的这张表是主表
右外连接(右连接):表示右边的这张表是主表
案例:找出每个员工的上级领导 select a.ename,b.ename from emp a left (outer) join //left 左边是主表 outer可以省略 emp b on a.mgr = b.empno;
在实际开发中,大部分是外连接
外连接最重要的特点:主表的数据无条件的全部查询出来
案例:找出哪个部门没有员工? select d.deptno,d.dname from emp e right outer join dept d on e.deptno = d.deptno where e.empno is null;
三张表连接查询
案例:找出每一个员工的部门名称以及工资等级 select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and hisal;
案例:找出每一个员工的部门名称以及工资等级以及上级领导 select e.ename '员工',d.dname '部门',s.grade '工资等级',em.ename '领导' from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and hisal left join emp em on e.mgr = em.empno;
子查询
select语句当中嵌套select语句,被嵌套的select语句是子查询
select ..(select) 在select中嵌套 from ..(select) 在from中嵌套 where ..(select) 在where中嵌套
- 在where子句中使用子查询
案例:找出高于平均薪资的员工信息select *from empwhere sal > (select avg(sal) from emp);
- from后面嵌套子查询
案例:找出每个部门平均薪水的薪资等级 select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and hisal;
案例:找出每个部门平均的薪水等级 select e.ename,e.sal,e.deptno,s.grade,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno
- 在select后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示员工名和部门名 select emp.ename,dept.dname from emp join dept on dept.deptno = emp.deptno;
将查询结果集相加 union
案例:找出工作岗位是SALESMAN和MANAGER的员工? select ename,job from emp where// job = 'salesman' or job = 'manager'; // or改成in job in ('salesman','manager');方法二: select ename,job from emp where job = 'salesman'; union select ename,job from emp where job = 'manager';
limit (重点,分页查询)
特点:mysql特有的,其他数据库没有。
作用:limit取结果集中的部分数据
语法:limit startIndex,length
startIndex 表示起始位置,length 表示取几个
案例:取出工资前5名的员工 select ename,sal from emp order by sal desc limit 0,5;
案例:找出工资排名在第4到第9名的员工 select ename,sal from emp order by sal desc limit 3,6;
** 通用的标准分页sql ** 每页显示3条记录 第1页:0,3 第2页:3,3 第3页:6,3 每页显示pageSize条记录(如上述的pageSize=3) pageNo表示第几页,如上述的第1页、第2页、第3页,pageNO=1,2,3,.... 公式:第pageNo页:(pageNo-1)*pageSize,pageSize
创建表 create
create table 表名( 字段名1 数据类型 约束, 字段名2 数据类型 约束, 字段名3 数据类型 约束, ... ); create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) ); 主键值自增: auto_increment 从1开始递增
插入表 insert
insert into 表名(字段名1,字段名2,字段名3,....) values (值1,值2,值3,...) insert into t_student(no,name,sex,classno,birth) values (1,'zhangsan','1','gaosan1ban','1999-07-06');
复制一张表
create table emp1 as select * from emp; 创建一张表emp1,将emp表的查询结果放到emp1中
修改数据 update
update 表名 set 字段名1=值1,字段名2=值2,... where 条件;
删除数据 delete
delete from 表名 where 条件; 删除大表 truncate table emp; //表被截断,不可回滚,永久丢失
关于MySQL从删除到跑路的方法 : )
1.删除数据库删除后可能会遗留日志,一些数据还是可以通过日志恢复的,所以索性把日志也一起删了吧drop database databasenamepurge binary logs to '日志名字';2.直接删除mysql的服务和数据find / -name mysql3.删除找到的关于mysql的一切rm -rf /var/lib/mysqlrm -rf /var/lib/mysqlrm -rf /usr/lib64/mysqlrm -rf /etc/my.cnf(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)上面仅仅是个玩笑,但是多学习一点总归是好的(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)
约束
- 非空约束 not null :约束的字段不能为NULL
- 唯一数据 unique : 约束的字段不能重复
- 主键约束 primary key : 约束的字段既不能为NULL,也不能重复
- 外键约束 foreign key : oreign key(classno) references t_class(cno)
classno的外键引用t_class表中的cno字段 - 检查约束 check : Oracle有,mysql没有
存储引擎 engines
查看当前mysql支持的存储引擎 show engines G
-
MyISAM: 读 咪塞姆
不支持事务,mysql最常用的存储引擎,但不是默认的
使用3个文件表示一张表,表结构.frm,表数据.MYD,索引.MYI
灵活的AUTO_INCREMENT字段处理
可被转换为压缩、只读表来节省空间 -
InnoDB:
支持事务、外键、行级锁
mysql默认存储引擎
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容(逻辑概念),无法被压缩,无法转换成只读
提供一组用来记录事务性活动的日志文件
在mysql服务器崩溃后提供自动恢复 -
MEMORY:
不支持事务,数据容易丢失,因为所有数据和索引都是存储在内存当中
查询速度最快,以前被称为HEAP引擎
在数据库目录中,每个表以.frm格式文件表示
表数据及索引被存储在内存中
表级锁机制
不能包含TEXT(CLOB)或BLOB字段
事务 transaction
一个事务是一个完整的业务逻辑单元,不可再分
比如:银行账户转账,从A账户向B账户转账1000,需要执行两条update语句 update t_act set balance = balance -1000 where actno='act-001'; update t_act set balance = balance +1000 where actno='act-002';
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败
事务的存在是为了保证数据的完整性、安全性(insert delete update)
假设一个业务,需要先执行一条Insert,再执行update,最后执行delete
- 首先开启事务机制
- 执行insert语句-->这个执行成功之后,把执行记录到数据库的操作历史中,并不会向文件中保存一条数据,不会真正的修改硬盘上的数据
- 执行update语句-->这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据
- 执行delete语句-->这个执行也是记录一下历史操作,记录到缓存,不会真正的修改硬盘上的数据
- 提交事务commit或者回滚事务rollback(结束)
事务的特性:
ACID
A 原子性:事务是最小的工作单元,不可再分
C 一致性:事务必须保证多条DML语句同时成功或者同时失败
I 隔离性:事务A与事务B之间具有隔离
D 持久性:最终数据必须持久化到硬盘文件,事务才算成功的结束
事务之间的隔离性:事务隔离性存在隔离级别
-
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据
读未提交存在脏读(Dirty Read)现象,表示读到了脏的数据
未提交的事务,另一个线程用户也能够读取到未提交的数据 -
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到
解决了脏读现象
读已提交存在的问题是:不可重复读(做不到从头到尾读取到的数据一样的)
只有提交了事务,另一个线程的用户才能读取到提交后的数据 -
第三级别:可重复读(repeatable read)
解决了不可重复读的问题
但存在的问题是:读取到的数据是幻象
数据可以一直读,读取到的是备份数据,即使数据已经被另一个线程的用户删除了 -
第四级别:序列化读/串行化读(serializable)
解决了所有问题
效率低,需要事务排队
两个线程的用户不可同时进行事务操作,用户A进行操作事务,用户A必须提交事务,用户B才能进行事务操作,排队oracle数据库默认的隔离级别是:第二级别
mysql数据库默认的隔离级别是:第三级别
mysql事务默认情况下是自动提交的(只要执行任意一条DML语句则提交一次)
关闭自动提交 start transaction;(即开启事务) set global transaction isolation level read uncommitted; 设置全局的事务隔离级别 读未提交 select @@global.tx isolation; 查看全局事务隔离级别
索引 index
- 什么是索引,有什么作用
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源
在数据库方面,查询一张表的时候有两种检索方式:
1)全表扫描
2)根据索引检索(效率很高) - 索引为什么可以提高检索效率?
原因是缩小了扫描的范围
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护,维护是有成本的。
比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值添加索引是给某一个字段,或者说某些字段添加索引 select ename,sal from emp where ename = 'SMITH';
当ename字段上添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值 - 怎么创建索引对象?怎么删除索引对象?
创建索引对象create index 索引名称 on 表名(字段名);删除索引对象drop index 索引名称 on 表名;
- 什么时候考虑给字段添加索引?
* 数据量庞大 (根据客户的需求,根据线上的环境)
* 该字段很少的DML操作 (因为字段进行修改操作,索引也需要维护)
* 该字段经常出现在where子句中 (经常根据哪个字段查询)
主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高,
explain 查看该语句的解释
索引底层采用的数据结构是:B + Tree
- 索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率最高
select ename from emp where ename = 'SMITH'; 通过索引转换为: select ename from emp where 物理地址 = '0x001';
- 索引的分类
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加1个索引
主键索引:主键上自动添加索引
唯一索引:有unique约束的字段上会自动添加索引 - 索引什么时候失效?
select ename from emp where ename like '%A%';模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的
视图 view
站在不同的角度去看待数据(同一张表,通过不同的角度去看待)
创建视图 create view myview as select empno,ename from emp;删除视图 drop view myview;
对视图进行增删改查,会影响到原表数据。(通过视图影响原表的数据的,不是直接操作的原表)
只有DQL语句才能以视图对象的方式创建出来
视图的作用
- 视图可以隐藏表的实现细节
- 保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD
DBA命令
将数据库中的数据导出 mysqldump 数据库名>E:test.sql -uroot -proot (DOS命令)导出数据库中的指定表 mysqldump 数据库名 emp>E:test.sql -uroot -proot导入数据 create database 数据库名; user 数据库名; source E:test.sql (source,文件拖进来)
数据库设计三范式
设计范式:设计表的依据,按照三范式设计的表不会出现数据冗余
三范式:
- 第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分
- 第二范式:所有非主键字段完全依赖主键,不能产生部分依赖
- 第三范式:所有非主键字段直接依赖主键,不能产生传递依赖
口诀:
多对多,三张表,关系表两个外键
一对多,两张表,多的表加外键
在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度
rank函数的三种用法
- rank() over(业务逻辑)
select name,score, rank() over(order by score desc) 'rank' from student作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。说明:例如学生排名,使用这个函数,成绩相同的两名是并列, 下一位同学空出所占的名次。即:1 1 3 4 5 5 7
- dense_rank() over(业务逻辑)
select name,score, dense_rank() over(order by score desc) 'rank' 作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。说明:和rank() over 的作用相同,区别在于dense_rank() over 排名是密集连续的。 例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学接着下一个名次。即:1 1 2 3 4 5 5 6
- row_number() over(业务逻辑)
select name,score, row_number() over(order by score desc) 'rank' 作用:查出指定条件后的进行排名,条件相同排名也不相同,排名间断不连续。说明:这个函数不需要考虑是否并列,即使根据条件查询出来的数值相同也会进行连续排序。 即:1 2 3 4 5 6
关于面试中遇到的问题
- 面试官说一下你的项目干啥了
答:我的这个项目有某模块..我负责哪些模块,这个模块用到了哪些技术,当时我在做这个的时候遇到了哪些困难,这个困难是如何解决的 - 你项目的哪一块用到索引了
答:当时的业务是怎么回事,为什么要考虑用索引,用了索引项目的效率有什么提升
MySQL试题
1.取得每个部门最高薪水的人员名称select e.ename,t.deptno,t.maxsalfrom (select deptno,max(sal) maxsal from emp group by deptno) tjoin emp eon t.maxsal=e.sal and t.deptno=e.deptno; 2.哪些人的薪水在部门的平均薪水之上 select e.ename,e.sal,t.* from (select deptno,avg(sal) avgsal from emp e group by deptno) t join emp e on e.sal>avgsal and e.deptno = t.deptno; 3.取得部门中所有人的平均的薪水等级 select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal betweeen s.losal and s.hisal group by deptno; 4.不用max函数,取得最高薪水 select sal from emp order by sal desc limit 0,1 5.取得平均薪水最高的部门的部门编号 select deptno,avg(sal) avgsal, from emp group by deptno order by avgsal desc limit 1; 6.取得平均薪水最高的部门的部门名称 select d.dname,avg(e.sal) avgsal from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsal desc limit 1;
百万数据 mysql count(*)优化
陈叔叔 发表了文章 • 0 个评论 • 62 次浏览 • 2022-05-15 13:28
一、故事背景
有一张 500w 左右的表做 select count(*) 速度特别慢。
二、原 SQL 分析
Server version: 5.7.24-log MySQL Community Server (GPL)
SQL 如下,仅仅就是统计 api_runtime_log 这张表的行数,一条简单的不能再简单的 SQL:
select count(*) from api_runtime_log;
我们先去运行一下这条 SQL,可以看到确实运行很慢,要 40 多秒左右,确实很不正常~
mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
| 5718952 |
+----------+
1 row in set (42.95 sec)
我们再去看下表结构,看上去貌似也挺正常的~存在主键,表引擎也是 InnoDB,字符集也没问题。
CREATE TABLE `api_runtime_log` (
`BelongXiaQuCode` varchar(50) DEFAULT NULL,
`OperateUserName` varchar(50) DEFAULT NULL,
`OperateDate` datetime DEFAULT NULL,
`Row_ID` int(11) DEFAULT NULL,
`YearFlag` varchar(4) DEFAULT NULL,
`RowGuid` varchar(50) NOT NULL,
......
`apiid` varchar(50) DEFAULT NULL,
`apiname` varchar(50) DEFAULT NULL,
`apiguid` varchar(50) DEFAULT NULL,
PRIMARY KEY (`RowGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
三、执行计划
通过执行计划,我们看下是否可以找到什么问题点。
mysql> explain select count(*) from api_runtime_log G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: api_runtime_log
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 152
ref: NULL
rows: 5718952
filtered: 100.00
Extra: Using index
可以看到,查询走的是 PRIMARY,也就是主键索引。貌似也没有什么问题,走索引了呀!那么是不是真的就没问题呢?
四、原理
为了找到答案,通过 Google 查找 MySQL 下 select count(*) 的原理,找到了答案。这边省略过程,直接上结果。
简单介绍下原理:
聚簇索引:每一个 InnoDB 存储引擎下的表都有一个特殊的索引用来保存每一行的数据,称为聚簇索引(通常都为主键),聚簇索引实际保存了 B-Tree 索引和行数据,所以大小实际上约等于为表数据量
二级索引:除了聚集索引,表上其他的索引都是二级索引,索引中仅仅存储了对应索引列及主键列
在 InnoDB 存储引擎中,count(*) 函数是先从内存中读取数据到内存缓冲区,然后进行扫描获得行记录数。这里 InnoDB 会优先走二级索引;如果同时存在多个二级索引,会选择key_len 最小的二级索引;如果不存在二级索引,那么会走主键索引;如果连主键都不存在,那么就走全表扫描!
这里我们由于走的是主键索引,所以 MySQL 需要先把整个主键索引读取到内存缓冲区,这是个从磁盘读写到内存的过程,而且主键索引基本等于整个表数据量(10GB+),所以非常耗时!
那么如何解决呢?
答案就是:建二级索引。
因为二级索引只包含对应的索引列及主键列,所以体积非常小。在 select count(*) 的查询过程中,只需要将二级索引读取到内存缓冲区,只有几十 MB 的数据量,所以速度会非常快。
举个形象的比喻,我们想知道一本书的页数:
走聚集索引:从第一页翻到最后一页,知道总页数;
走二级索引:通过目录直接知道总页数。
五、验证
创建二级索引后,再次执行 SQL 及查看执行计划。
mysql> create index idx_rowguid on api_runtime_log(rowguid);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
| 5718952 |
+----------+
1 row in set (0.89 sec)
mysql> explain select count(*) from api_runtime_log G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: api_runtime_log
partitions: NULL
type: index
possible_keys: NULL
key: idx_rowguid
key_len: 152
ref: NULL
rows: 5718952
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
可以看到添加二级索引后,确实速度明显变快,而且执行计划也变成了走二级索引。至此这个问题其实已经解决了,就是由于表上缺少二级索引导致。
六、深入测试
为了进一步验证上述的推论,所以就做了如下的测试。
测试过程如下:
通过 sysbench 创建了一张 500W 的测试表 sbtest1,表上仅仅包含一个主键索引,表大小为 1125MB;
调整部分 MySQL 参数,重启 MySQL,保证目前 innodb buffer pool (内存缓冲区) 中为空,不缓存任何数据;
执行 select count(*),理论上走主键索引,查看当前内存缓冲区中缓存的数据量(理论上会缓存整个聚簇索引);
在测试表 sbtest1 上添加二级索引,索引大小为 55MB;
再次重启 MySQL,保证内存缓冲区为空;
再次执行 select count(*),理论上走二级索引;
再次查看内存缓冲区中缓存的数据量(理论上只会缓存二级索引)。
测试结果如下:
1. 聚簇索引
查询当前内存缓冲区状态,结果为空证明不缓存测试表数据。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.92 sec)
mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 5188434 |
+----------+
1 row in set (5.52 sec)
再次查看内存缓冲区,发现缓存了 sbtest1 表上 1G 多的数据,基本等于整个表数据量。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' G;
*************************** 1. row ***************************
object_schema: test
object_name: sbtest1
allocated: 1.08 GiB
data: 1.01 GiB
pages: 71081
pages_hashed: 0
pages_old: 28119
rows_cached: 5189798
最后我们再来看下执行计划,确实走的是主键索引,放在最后执行是为了避免影响缓冲区。
mysql> explain select count(*) from test.sbtest1 G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 5117616
filtered: 100.00
Extra: Using index
2. 二级索引
创建二级索引 idx_id,查看 sbtest1 表上主键索引与二级索引的数据量。
mysql> create index idx_id on sbtest1(id);
Query OK, 0 rows affected (12.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT sum(stat_value) pages ,index_name ,
(round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB
FROM mysql.innodb_index_stats
WHERE table_name = 'sbtest1'
AND database_name = 'test'
AND stat_description = 'Number of pages in the index'
GROUP BY index_name;
+-------+------------+------+
| pages | index_name | MB |
+-------+------------+------+
| 72000 | PRIMARY | 1125 |
| 3492 | idx_id | 55 |
+-------+------------+------+
重启 MySQL,再次查看缓冲区同样为空,证明没有缓存测试表上的数据。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.49 sec)
mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 5188434 |
+----------+
1 row in set (2.92 sec)
再次查看内存缓冲区,发现仅仅缓存了 sbtest1 表上的 50M 数据,约等于二级索引的数据量。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' G;
*************************** 1. row ***************************
object_schema: test
object_name: sbtest1
allocated: 49.48 MiB
data: 46.41 MiB
pages: 3167
pages_hashed: 0
pages_old: 1575
rows_cached: 2599872
最后确认下执行计划,确实走的是二级索引。
mysql> explain select count(*) from test.sbtest1 G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 5117616
filtered: 100.00
Extra: Using index
七、案例总结
从上述这个测试结果可以看出,和之前的推论基本吻合。
如果 select count(*) 走的是主键索引,那么会缓存整个表数据,大量查询时间会花费在读取表数据到缓冲区。
如果存在二级索引,那么只需要读取索引页到缓冲区即可,速度自然快。
另:项目上由于磁盘性能层次不齐,所以当遇上这种情况时,性能较差的磁盘更会放大这个问题;一张超级大表,统计行数时如果走了主键索引,后果可想而知
八、优化建议
此次测试过程中我们仅仅模拟是百万数据量,此时我们通过二级索引统计表行数,只需要读取几十 M 的数据量,就可以得到结果。
那么当我们的表数据量是上千万,甚至上亿时呢。此时即便是最小的二级索引也是 几百 M、过 G 的数据量,如果继续通过二级索引来统计行数,那么速度就不会如此迅速了。
这个时候可以通过避免直接 select count(*) from table 来解决,方法较多,例如:
使用 MySQL 触发器 + 统计表实时计算表数据量;
使用 MyISAM 替换 InnoDB,因为 MyISAM 自带计数器,坏处就不多说了;
通过 ETL 导入表数据到其他更高效的异构环境中进行计算;
升级到 MySQL 8 中,使用并行查询,加快检索速度。
当然,什么时候 InnoDB 存储引擎可以直接实现计数器的功能就好了!
————————————————
版权声明:本文为CSDN博主「MariaOzawa」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/MariaOzawa/article/details/115603713
mysql笔记
陈叔叔 发表了文章 • 0 个评论 • 60 次浏览 • 2022-05-24 10:51
DBMS,SQL,DB三者的关系
DBMS:数据库管理系统
SQL:用于访问和处理数据库的标准的计算机语言
DB:数据库
三者的关系:DBMS数据库管理系统执行SQL语句,来操作DB数据库当中的数据的
关于in与or的建议
条件查询 in(a,b,c,...)
可以替代or,提高检索效率
例:
select id,name,age from student where name = '张三' or name = '李四'替换为:select id,name,age from student where name in ('张三' , '李四')
模糊查询like
%代表任意多个字符
_代表任意1个字符
例1:查询姓张的同学select id,name,age from student where name like '张%'
例2:查询姓李且只有两个字的同学select id,name,age from student where name like '李_'
ps:CSDN遵循Markdown语法,不能打出下划线,所以需要转义字符,如_
排序 order by
asc 升序(默认)
desc 降序
排序 order by sal 薪资 asc/desc ;
例:按薪水为公司的员工排序降序select name,age,sal from emp order by sal desc
SQL完整的语句系统的执行顺序(重要)
select *5from tableName 1 where 条件(join) 2group by ...3having ...4order by ...6limit ...7
分组函数
分组函数:多行处理函数
函数 | 说明 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
注意: |
- 分组函数一共5个
- 输入多行,输出结果总是1行
- 分组函数自动忽略NULL
- NULL参与运算时,结果为NULL,解决办法是:ifnull(comm,0)
- 分组函数不可直接使用在where子句中,因为分组函数是在group by执行之后才会执行
- count(*) 统计总记录的条数,count(字段名) 统计某个字段不为NULL的数量
聚合函数 group by
group by :按照某个字段或者某些字段进行分组
having :对分组之后的数据进行再次过滤
例1:按公司员工的工作岗位进行分组select name,job from emp group by job;
例2:找出每个部门的最高薪资,要求显示薪资大于2900的数据select max(sal),deptno from emp group by deptno having max(sal)>2900; 效率低select max(sal),deptno from emp where sal>2900 group by deptno; 效率高,建议能够使用where过滤的尽量使用能够在分组之前过滤的,就先进行where过滤
例3:找出每个部门的平均薪资,要求显示薪资>2000的数据步骤1.找出每个部门的平均薪资 select deptno,avg(sal) from emp group by deptno;步骤2.要求显示薪资>2000的数据 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
去重 distinct
select distinct job from emp;distinct 只能出现在所有字段的最前边,表示字段的联合去重
连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的数据
一般一个业务都会对应多张表,比如:学生和班级,起码两张表
连接查询的分类
根据语法出现的年代来划分
- SQL92(一些老的DBA可能还在使用这种语法。DBA:数据库管理员)
- SQL99(比较新的语法)
根据表的连接方式来划分
内连接:
- 等值连接
- 非等值连接 between and
- 自连接
外连接
- 左外连接(左连接)
- 右外连接(右连接)
全连接(很少用)
注:在表的连接查询方面有一种现象被称为:笛卡尔积现象
在数据库中,两张表连接查询默认显示的结果条数是两张表记录条数的乘积
案例:找出每一个员工的部门名称,要求显示员工名和部门名 select ename,dname from emp,dept;
如何避免笛卡尔积现象?
避免了笛卡尔积现象,不会减少记录的匹配次数,只不过显示的是有效记录
等值连接
select //SQL92 旧语法 e.ename,d.dnamefrom emp e,dept dwhere e.deptno = d.deptno; **内连接之等值连接:**特点:条件是等量关系select //SQL99 新语法,常用的 e.ename,d.dnamefrom emp ejoin dept don e.deptno = d.deptno; 语法: ... A (inner) join //inner 可读性更强,但可以省略 B on 连接条件 where ...SQL99语法结构更清晰,表的连接条件和后来的where条件分离了
内连接之非等值连接:
特点:连接条件中的关系是非等量的关系
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级 select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and hisal;
内连接之自连接
特点:一张表看做两张表,自己连接自己
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名 select a.ename,b.ename from emp a inner join emp b on a.mgr = b.empno;
外连接
内连接:
假设A表和B表进行连接,使用内连接,凡是A表和B表能够匹配上的记录查询出来
AB两张表没有主副之分,两张表是平等的
外连接:
假设A表和B表进行连接,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的
数据,捎带着查询副表,当副表的数据没有和主表数据匹配上,副表自动模拟出NULL与之匹配
外连接的分类
左外连接(左连接):表示左边的这张表是主表
右外连接(右连接):表示右边的这张表是主表
案例:找出每个员工的上级领导 select a.ename,b.ename from emp a left (outer) join //left 左边是主表 outer可以省略 emp b on a.mgr = b.empno;
在实际开发中,大部分是外连接
外连接最重要的特点:主表的数据无条件的全部查询出来
案例:找出哪个部门没有员工? select d.deptno,d.dname from emp e right outer join dept d on e.deptno = d.deptno where e.empno is null;
三张表连接查询
案例:找出每一个员工的部门名称以及工资等级 select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and hisal;
案例:找出每一个员工的部门名称以及工资等级以及上级领导 select e.ename '员工',d.dname '部门',s.grade '工资等级',em.ename '领导' from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and hisal left join emp em on e.mgr = em.empno;
子查询
select语句当中嵌套select语句,被嵌套的select语句是子查询
select ..(select) 在select中嵌套 from ..(select) 在from中嵌套 where ..(select) 在where中嵌套
- 在where子句中使用子查询
案例:找出高于平均薪资的员工信息select *from empwhere sal > (select avg(sal) from emp);
- from后面嵌套子查询
案例:找出每个部门平均薪水的薪资等级 select t.*,s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and hisal;
案例:找出每个部门平均的薪水等级 select e.ename,e.sal,e.deptno,s.grade,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno
- 在select后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示员工名和部门名 select emp.ename,dept.dname from emp join dept on dept.deptno = emp.deptno;
将查询结果集相加 union
案例:找出工作岗位是SALESMAN和MANAGER的员工? select ename,job from emp where// job = 'salesman' or job = 'manager'; // or改成in job in ('salesman','manager');方法二: select ename,job from emp where job = 'salesman'; union select ename,job from emp where job = 'manager';
limit (重点,分页查询)
特点:mysql特有的,其他数据库没有。
作用:limit取结果集中的部分数据
语法:limit startIndex,length
startIndex 表示起始位置,length 表示取几个
案例:取出工资前5名的员工 select ename,sal from emp order by sal desc limit 0,5;
案例:找出工资排名在第4到第9名的员工 select ename,sal from emp order by sal desc limit 3,6;
** 通用的标准分页sql ** 每页显示3条记录 第1页:0,3 第2页:3,3 第3页:6,3 每页显示pageSize条记录(如上述的pageSize=3) pageNo表示第几页,如上述的第1页、第2页、第3页,pageNO=1,2,3,.... 公式:第pageNo页:(pageNo-1)*pageSize,pageSize
创建表 create
create table 表名( 字段名1 数据类型 约束, 字段名2 数据类型 约束, 字段名3 数据类型 约束, ... ); create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) ); 主键值自增: auto_increment 从1开始递增
插入表 insert
insert into 表名(字段名1,字段名2,字段名3,....) values (值1,值2,值3,...) insert into t_student(no,name,sex,classno,birth) values (1,'zhangsan','1','gaosan1ban','1999-07-06');
复制一张表
create table emp1 as select * from emp; 创建一张表emp1,将emp表的查询结果放到emp1中
修改数据 update
update 表名 set 字段名1=值1,字段名2=值2,... where 条件;
删除数据 delete
delete from 表名 where 条件; 删除大表 truncate table emp; //表被截断,不可回滚,永久丢失
关于MySQL从删除到跑路的方法 : )
1.删除数据库删除后可能会遗留日志,一些数据还是可以通过日志恢复的,所以索性把日志也一起删了吧drop database databasenamepurge binary logs to '日志名字';2.直接删除mysql的服务和数据find / -name mysql3.删除找到的关于mysql的一切rm -rf /var/lib/mysqlrm -rf /var/lib/mysqlrm -rf /usr/lib64/mysqlrm -rf /etc/my.cnf(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)上面仅仅是个玩笑,但是多学习一点总归是好的(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)
约束
- 非空约束 not null :约束的字段不能为NULL
- 唯一数据 unique : 约束的字段不能重复
- 主键约束 primary key : 约束的字段既不能为NULL,也不能重复
- 外键约束 foreign key : oreign key(classno) references t_class(cno)
classno的外键引用t_class表中的cno字段 - 检查约束 check : Oracle有,mysql没有
存储引擎 engines
查看当前mysql支持的存储引擎 show engines G
-
MyISAM: 读 咪塞姆
不支持事务,mysql最常用的存储引擎,但不是默认的
使用3个文件表示一张表,表结构.frm,表数据.MYD,索引.MYI
灵活的AUTO_INCREMENT字段处理
可被转换为压缩、只读表来节省空间 -
InnoDB:
支持事务、外键、行级锁
mysql默认存储引擎
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容(逻辑概念),无法被压缩,无法转换成只读
提供一组用来记录事务性活动的日志文件
在mysql服务器崩溃后提供自动恢复 -
MEMORY:
不支持事务,数据容易丢失,因为所有数据和索引都是存储在内存当中
查询速度最快,以前被称为HEAP引擎
在数据库目录中,每个表以.frm格式文件表示
表数据及索引被存储在内存中
表级锁机制
不能包含TEXT(CLOB)或BLOB字段
事务 transaction
一个事务是一个完整的业务逻辑单元,不可再分
比如:银行账户转账,从A账户向B账户转账1000,需要执行两条update语句 update t_act set balance = balance -1000 where actno='act-001'; update t_act set balance = balance +1000 where actno='act-002';
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败
事务的存在是为了保证数据的完整性、安全性(insert delete update)
假设一个业务,需要先执行一条Insert,再执行update,最后执行delete
- 首先开启事务机制
- 执行insert语句-->这个执行成功之后,把执行记录到数据库的操作历史中,并不会向文件中保存一条数据,不会真正的修改硬盘上的数据
- 执行update语句-->这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据
- 执行delete语句-->这个执行也是记录一下历史操作,记录到缓存,不会真正的修改硬盘上的数据
- 提交事务commit或者回滚事务rollback(结束)
事务的特性:
ACID
A 原子性:事务是最小的工作单元,不可再分
C 一致性:事务必须保证多条DML语句同时成功或者同时失败
I 隔离性:事务A与事务B之间具有隔离
D 持久性:最终数据必须持久化到硬盘文件,事务才算成功的结束
事务之间的隔离性:事务隔离性存在隔离级别
-
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据
读未提交存在脏读(Dirty Read)现象,表示读到了脏的数据
未提交的事务,另一个线程用户也能够读取到未提交的数据 -
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到
解决了脏读现象
读已提交存在的问题是:不可重复读(做不到从头到尾读取到的数据一样的)
只有提交了事务,另一个线程的用户才能读取到提交后的数据 -
第三级别:可重复读(repeatable read)
解决了不可重复读的问题
但存在的问题是:读取到的数据是幻象
数据可以一直读,读取到的是备份数据,即使数据已经被另一个线程的用户删除了 -
第四级别:序列化读/串行化读(serializable)
解决了所有问题
效率低,需要事务排队
两个线程的用户不可同时进行事务操作,用户A进行操作事务,用户A必须提交事务,用户B才能进行事务操作,排队oracle数据库默认的隔离级别是:第二级别
mysql数据库默认的隔离级别是:第三级别
mysql事务默认情况下是自动提交的(只要执行任意一条DML语句则提交一次)
关闭自动提交 start transaction;(即开启事务) set global transaction isolation level read uncommitted; 设置全局的事务隔离级别 读未提交 select @@global.tx isolation; 查看全局事务隔离级别
索引 index
- 什么是索引,有什么作用
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源
在数据库方面,查询一张表的时候有两种检索方式:
1)全表扫描
2)根据索引检索(效率很高) - 索引为什么可以提高检索效率?
原因是缩小了扫描的范围
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护,维护是有成本的。
比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值添加索引是给某一个字段,或者说某些字段添加索引 select ename,sal from emp where ename = 'SMITH';
当ename字段上添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值 - 怎么创建索引对象?怎么删除索引对象?
创建索引对象create index 索引名称 on 表名(字段名);删除索引对象drop index 索引名称 on 表名;
- 什么时候考虑给字段添加索引?
* 数据量庞大 (根据客户的需求,根据线上的环境)
* 该字段很少的DML操作 (因为字段进行修改操作,索引也需要维护)
* 该字段经常出现在where子句中 (经常根据哪个字段查询)
主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高,
explain 查看该语句的解释
索引底层采用的数据结构是:B + Tree
- 索引的实现原理
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率最高
select ename from emp where ename = 'SMITH'; 通过索引转换为: select ename from emp where 物理地址 = '0x001';
- 索引的分类
单一索引:给单个字段添加索引
复合索引:给多个字段联合起来添加1个索引
主键索引:主键上自动添加索引
唯一索引:有unique约束的字段上会自动添加索引 - 索引什么时候失效?
select ename from emp where ename like '%A%';模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的
视图 view
站在不同的角度去看待数据(同一张表,通过不同的角度去看待)
创建视图 create view myview as select empno,ename from emp;删除视图 drop view myview;
对视图进行增删改查,会影响到原表数据。(通过视图影响原表的数据的,不是直接操作的原表)
只有DQL语句才能以视图对象的方式创建出来
视图的作用
- 视图可以隐藏表的实现细节
- 保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD
DBA命令
将数据库中的数据导出 mysqldump 数据库名>E:test.sql -uroot -proot (DOS命令)导出数据库中的指定表 mysqldump 数据库名 emp>E:test.sql -uroot -proot导入数据 create database 数据库名; user 数据库名; source E:test.sql (source,文件拖进来)
数据库设计三范式
设计范式:设计表的依据,按照三范式设计的表不会出现数据冗余
三范式:
- 第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分
- 第二范式:所有非主键字段完全依赖主键,不能产生部分依赖
- 第三范式:所有非主键字段直接依赖主键,不能产生传递依赖
口诀:
多对多,三张表,关系表两个外键
一对多,两张表,多的表加外键
在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度
rank函数的三种用法
- rank() over(业务逻辑)
select name,score, rank() over(order by score desc) 'rank' from student作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。说明:例如学生排名,使用这个函数,成绩相同的两名是并列, 下一位同学空出所占的名次。即:1 1 3 4 5 5 7
- dense_rank() over(业务逻辑)
select name,score, dense_rank() over(order by score desc) 'rank' 作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。说明:和rank() over 的作用相同,区别在于dense_rank() over 排名是密集连续的。 例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学接着下一个名次。即:1 1 2 3 4 5 5 6
- row_number() over(业务逻辑)
select name,score, row_number() over(order by score desc) 'rank' 作用:查出指定条件后的进行排名,条件相同排名也不相同,排名间断不连续。说明:这个函数不需要考虑是否并列,即使根据条件查询出来的数值相同也会进行连续排序。 即:1 2 3 4 5 6
关于面试中遇到的问题
- 面试官说一下你的项目干啥了
答:我的这个项目有某模块..我负责哪些模块,这个模块用到了哪些技术,当时我在做这个的时候遇到了哪些困难,这个困难是如何解决的 - 你项目的哪一块用到索引了
答:当时的业务是怎么回事,为什么要考虑用索引,用了索引项目的效率有什么提升
MySQL试题
1.取得每个部门最高薪水的人员名称select e.ename,t.deptno,t.maxsalfrom (select deptno,max(sal) maxsal from emp group by deptno) tjoin emp eon t.maxsal=e.sal and t.deptno=e.deptno; 2.哪些人的薪水在部门的平均薪水之上 select e.ename,e.sal,t.* from (select deptno,avg(sal) avgsal from emp e group by deptno) t join emp e on e.sal>avgsal and e.deptno = t.deptno; 3.取得部门中所有人的平均的薪水等级 select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal betweeen s.losal and s.hisal group by deptno; 4.不用max函数,取得最高薪水 select sal from emp order by sal desc limit 0,1 5.取得平均薪水最高的部门的部门编号 select deptno,avg(sal) avgsal, from emp group by deptno order by avgsal desc limit 1; 6.取得平均薪水最高的部门的部门名称 select d.dname,avg(e.sal) avgsal from emp e join dept d on d.deptno = e.deptno group by d.dname order by avgsal desc limit 1;
百万数据 mysql count(*)优化
陈叔叔 发表了文章 • 0 个评论 • 62 次浏览 • 2022-05-15 13:28
一、故事背景
有一张 500w 左右的表做 select count(*) 速度特别慢。
二、原 SQL 分析
Server version: 5.7.24-log MySQL Community Server (GPL)
SQL 如下,仅仅就是统计 api_runtime_log 这张表的行数,一条简单的不能再简单的 SQL:
select count(*) from api_runtime_log;
我们先去运行一下这条 SQL,可以看到确实运行很慢,要 40 多秒左右,确实很不正常~
mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
| 5718952 |
+----------+
1 row in set (42.95 sec)
我们再去看下表结构,看上去貌似也挺正常的~存在主键,表引擎也是 InnoDB,字符集也没问题。
CREATE TABLE `api_runtime_log` (
`BelongXiaQuCode` varchar(50) DEFAULT NULL,
`OperateUserName` varchar(50) DEFAULT NULL,
`OperateDate` datetime DEFAULT NULL,
`Row_ID` int(11) DEFAULT NULL,
`YearFlag` varchar(4) DEFAULT NULL,
`RowGuid` varchar(50) NOT NULL,
......
`apiid` varchar(50) DEFAULT NULL,
`apiname` varchar(50) DEFAULT NULL,
`apiguid` varchar(50) DEFAULT NULL,
PRIMARY KEY (`RowGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
三、执行计划
通过执行计划,我们看下是否可以找到什么问题点。
mysql> explain select count(*) from api_runtime_log G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: api_runtime_log
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 152
ref: NULL
rows: 5718952
filtered: 100.00
Extra: Using index
可以看到,查询走的是 PRIMARY,也就是主键索引。貌似也没有什么问题,走索引了呀!那么是不是真的就没问题呢?
四、原理
为了找到答案,通过 Google 查找 MySQL 下 select count(*) 的原理,找到了答案。这边省略过程,直接上结果。
简单介绍下原理:
聚簇索引:每一个 InnoDB 存储引擎下的表都有一个特殊的索引用来保存每一行的数据,称为聚簇索引(通常都为主键),聚簇索引实际保存了 B-Tree 索引和行数据,所以大小实际上约等于为表数据量
二级索引:除了聚集索引,表上其他的索引都是二级索引,索引中仅仅存储了对应索引列及主键列
在 InnoDB 存储引擎中,count(*) 函数是先从内存中读取数据到内存缓冲区,然后进行扫描获得行记录数。这里 InnoDB 会优先走二级索引;如果同时存在多个二级索引,会选择key_len 最小的二级索引;如果不存在二级索引,那么会走主键索引;如果连主键都不存在,那么就走全表扫描!
这里我们由于走的是主键索引,所以 MySQL 需要先把整个主键索引读取到内存缓冲区,这是个从磁盘读写到内存的过程,而且主键索引基本等于整个表数据量(10GB+),所以非常耗时!
那么如何解决呢?
答案就是:建二级索引。
因为二级索引只包含对应的索引列及主键列,所以体积非常小。在 select count(*) 的查询过程中,只需要将二级索引读取到内存缓冲区,只有几十 MB 的数据量,所以速度会非常快。
举个形象的比喻,我们想知道一本书的页数:
走聚集索引:从第一页翻到最后一页,知道总页数;
走二级索引:通过目录直接知道总页数。
五、验证
创建二级索引后,再次执行 SQL 及查看执行计划。
mysql> create index idx_rowguid on api_runtime_log(rowguid);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from api_runtime_log;
+----------+
| count(*) |
+----------+
| 5718952 |
+----------+
1 row in set (0.89 sec)
mysql> explain select count(*) from api_runtime_log G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: api_runtime_log
partitions: NULL
type: index
possible_keys: NULL
key: idx_rowguid
key_len: 152
ref: NULL
rows: 5718952
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
可以看到添加二级索引后,确实速度明显变快,而且执行计划也变成了走二级索引。至此这个问题其实已经解决了,就是由于表上缺少二级索引导致。
六、深入测试
为了进一步验证上述的推论,所以就做了如下的测试。
测试过程如下:
通过 sysbench 创建了一张 500W 的测试表 sbtest1,表上仅仅包含一个主键索引,表大小为 1125MB;
调整部分 MySQL 参数,重启 MySQL,保证目前 innodb buffer pool (内存缓冲区) 中为空,不缓存任何数据;
执行 select count(*),理论上走主键索引,查看当前内存缓冲区中缓存的数据量(理论上会缓存整个聚簇索引);
在测试表 sbtest1 上添加二级索引,索引大小为 55MB;
再次重启 MySQL,保证内存缓冲区为空;
再次执行 select count(*),理论上走二级索引;
再次查看内存缓冲区中缓存的数据量(理论上只会缓存二级索引)。
测试结果如下:
1. 聚簇索引
查询当前内存缓冲区状态,结果为空证明不缓存测试表数据。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.92 sec)
mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 5188434 |
+----------+
1 row in set (5.52 sec)
再次查看内存缓冲区,发现缓存了 sbtest1 表上 1G 多的数据,基本等于整个表数据量。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' G;
*************************** 1. row ***************************
object_schema: test
object_name: sbtest1
allocated: 1.08 GiB
data: 1.01 GiB
pages: 71081
pages_hashed: 0
pages_old: 28119
rows_cached: 5189798
最后我们再来看下执行计划,确实走的是主键索引,放在最后执行是为了避免影响缓冲区。
mysql> explain select count(*) from test.sbtest1 G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 5117616
filtered: 100.00
Extra: Using index
2. 二级索引
创建二级索引 idx_id,查看 sbtest1 表上主键索引与二级索引的数据量。
mysql> create index idx_id on sbtest1(id);
Query OK, 0 rows affected (12.97 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT sum(stat_value) pages ,index_name ,
(round((sum(stat_value) * @@innodb_page_size)/1024/1024)) as MB
FROM mysql.innodb_index_stats
WHERE table_name = 'sbtest1'
AND database_name = 'test'
AND stat_description = 'Number of pages in the index'
GROUP BY index_name;
+-------+------------+------+
| pages | index_name | MB |
+-------+------------+------+
| 72000 | PRIMARY | 1125 |
| 3492 | idx_id | 55 |
+-------+------------+------+
重启 MySQL,再次查看缓冲区同样为空,证明没有缓存测试表上的数据。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test';
Empty set (1.49 sec)
mysql> select count(*) from test.sbtest1;
+----------+
| count(*) |
+----------+
| 5188434 |
+----------+
1 row in set (2.92 sec)
再次查看内存缓冲区,发现仅仅缓存了 sbtest1 表上的 50M 数据,约等于二级索引的数据量。
mysql> select * from sys.innodb_buffer_stats_by_table where object_schema = 'test' G;
*************************** 1. row ***************************
object_schema: test
object_name: sbtest1
allocated: 49.48 MiB
data: 46.41 MiB
pages: 3167
pages_hashed: 0
pages_old: 1575
rows_cached: 2599872
最后确认下执行计划,确实走的是二级索引。
mysql> explain select count(*) from test.sbtest1 G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sbtest1
partitions: NULL
type: index
possible_keys: NULL
key: idx_id
key_len: 4
ref: NULL
rows: 5117616
filtered: 100.00
Extra: Using index
七、案例总结
从上述这个测试结果可以看出,和之前的推论基本吻合。
如果 select count(*) 走的是主键索引,那么会缓存整个表数据,大量查询时间会花费在读取表数据到缓冲区。
如果存在二级索引,那么只需要读取索引页到缓冲区即可,速度自然快。
另:项目上由于磁盘性能层次不齐,所以当遇上这种情况时,性能较差的磁盘更会放大这个问题;一张超级大表,统计行数时如果走了主键索引,后果可想而知
八、优化建议
此次测试过程中我们仅仅模拟是百万数据量,此时我们通过二级索引统计表行数,只需要读取几十 M 的数据量,就可以得到结果。
那么当我们的表数据量是上千万,甚至上亿时呢。此时即便是最小的二级索引也是 几百 M、过 G 的数据量,如果继续通过二级索引来统计行数,那么速度就不会如此迅速了。
这个时候可以通过避免直接 select count(*) from table 来解决,方法较多,例如:
使用 MySQL 触发器 + 统计表实时计算表数据量;
使用 MyISAM 替换 InnoDB,因为 MyISAM 自带计数器,坏处就不多说了;
通过 ETL 导入表数据到其他更高效的异构环境中进行计算;
升级到 MySQL 8 中,使用并行查询,加快检索速度。
当然,什么时候 InnoDB 存储引擎可以直接实现计数器的功能就好了!
————————————————
版权声明:本文为CSDN博主「MariaOzawa」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/MariaOzawa/article/details/115603713