mysql

mysql

mysql笔记

陈叔叔 发表了文章 • 0 个评论 • 60 次浏览 • 2022-05-24 10:51 • 来自相关话题

DBMS,SQL,DB三者的关系 DBMS:数据库管理系统 SQL:用于访问和处理数据库的标准的计算机语言 DB:数据库 三者的关系:DBMS数据库管 ...查看全部

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(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)上面仅仅是个玩笑,但是多学习一点总归是好的(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)

约束

  1. 非空约束 not null :约束的字段不能为NULL
  2. 唯一数据 unique : 约束的字段不能重复
  3. 主键约束 primary key : 约束的字段既不能为NULL,也不能重复
  4. 外键约束 foreign key : oreign key(classno) references t_class(cno)
    classno的外键引用t_class表中的cno字段
  5. 检查约束 check : Oracle有,mysql没有

存储引擎 engines

查看当前mysql支持的存储引擎  show engines G
  1. MyISAM: 读 咪塞姆
    不支持事务,mysql最常用的存储引擎,但不是默认的
    使用3个文件表示一张表,表结构.frm,表数据.MYD,索引.MYI
    灵活的AUTO_INCREMENT字段处理
    可被转换为压缩、只读表来节省空间

  2. InnoDB:
    支持事务、外键、行级锁
    mysql默认存储引擎
    每个InnoDB表在数据库目录中以.frm格式文件表示
    InnoDB表空间tablespace被用于存储表的内容(逻辑概念),无法被压缩,无法转换成只读
    提供一组用来记录事务性活动的日志文件
    在mysql服务器崩溃后提供自动恢复

  3. 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

  1. 首先开启事务机制
  2. 执行insert语句-->这个执行成功之后,把执行记录到数据库的操作历史中,并不会向文件中保存一条数据,不会真正的修改硬盘上的数据
  3. 执行update语句-->这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据
  4. 执行delete语句-->这个执行也是记录一下历史操作,记录到缓存,不会真正的修改硬盘上的数据
  5. 提交事务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. 什么是索引,有什么作用
    索引就相当于一本书的目录,通过目录可以快速的找到对应的资源
    在数据库方面,查询一张表的时候有两种检索方式:
    1)全表扫描
    2)根据索引检索(效率很高)
  2. 索引为什么可以提高检索效率?
    原因是缩小了扫描的范围
    索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护,维护是有成本的。
    比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护
    添加索引是给某一个字段,或者说某些字段添加索引  select ename,sal from emp where ename = 'SMITH';
    当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值
    当ename字段上添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值
  3. 怎么创建索引对象?怎么删除索引对象?
创建索引对象create index 索引名称 on 表名(字段名);删除索引对象drop index 索引名称 on 表名;
  1. 什么时候考虑给字段添加索引?
    * 数据量庞大 (根据客户的需求,根据线上的环境)
    * 该字段很少的DML操作 (因为字段进行修改操作,索引也需要维护)
    * 该字段经常出现在where子句中 (经常根据哪个字段查询)

主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高,
explain 查看该语句的解释

索引底层采用的数据结构是:B + Tree

  1. 索引的实现原理
    通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率最高
	select ename from emp where ename = 'SMITH';	通过索引转换为:	select ename from emp where 物理地址 = '0x001';
  1. 索引的分类
    单一索引:给单个字段添加索引
    复合索引:给多个字段联合起来添加1个索引
    主键索引:主键上自动添加索引
    唯一索引:有unique约束的字段上会自动添加索引
  2. 索引什么时候失效?
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) ...查看全部

一、故事背景
有一张 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数据库管 ...查看全部

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(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)上面仅仅是个玩笑,但是多学习一点总归是好的(* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄) (* ̄︶ ̄)

约束

  1. 非空约束 not null :约束的字段不能为NULL
  2. 唯一数据 unique : 约束的字段不能重复
  3. 主键约束 primary key : 约束的字段既不能为NULL,也不能重复
  4. 外键约束 foreign key : oreign key(classno) references t_class(cno)
    classno的外键引用t_class表中的cno字段
  5. 检查约束 check : Oracle有,mysql没有

存储引擎 engines

查看当前mysql支持的存储引擎  show engines G
  1. MyISAM: 读 咪塞姆
    不支持事务,mysql最常用的存储引擎,但不是默认的
    使用3个文件表示一张表,表结构.frm,表数据.MYD,索引.MYI
    灵活的AUTO_INCREMENT字段处理
    可被转换为压缩、只读表来节省空间

  2. InnoDB:
    支持事务、外键、行级锁
    mysql默认存储引擎
    每个InnoDB表在数据库目录中以.frm格式文件表示
    InnoDB表空间tablespace被用于存储表的内容(逻辑概念),无法被压缩,无法转换成只读
    提供一组用来记录事务性活动的日志文件
    在mysql服务器崩溃后提供自动恢复

  3. 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

  1. 首先开启事务机制
  2. 执行insert语句-->这个执行成功之后,把执行记录到数据库的操作历史中,并不会向文件中保存一条数据,不会真正的修改硬盘上的数据
  3. 执行update语句-->这个执行也是记录一下历史操作,不会真正的修改硬盘上的数据
  4. 执行delete语句-->这个执行也是记录一下历史操作,记录到缓存,不会真正的修改硬盘上的数据
  5. 提交事务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. 什么是索引,有什么作用
    索引就相当于一本书的目录,通过目录可以快速的找到对应的资源
    在数据库方面,查询一张表的时候有两种检索方式:
    1)全表扫描
    2)根据索引检索(效率很高)
  2. 索引为什么可以提高检索效率?
    原因是缩小了扫描的范围
    索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护,维护是有成本的。
    比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护
    添加索引是给某一个字段,或者说某些字段添加索引  select ename,sal from emp where ename = 'SMITH';
    当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值
    当ename字段上添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值
  3. 怎么创建索引对象?怎么删除索引对象?
创建索引对象create index 索引名称 on 表名(字段名);删除索引对象drop index 索引名称 on 表名;
  1. 什么时候考虑给字段添加索引?
    * 数据量庞大 (根据客户的需求,根据线上的环境)
    * 该字段很少的DML操作 (因为字段进行修改操作,索引也需要维护)
    * 该字段经常出现在where子句中 (经常根据哪个字段查询)

主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高,
explain 查看该语句的解释

索引底层采用的数据结构是:B + Tree

  1. 索引的实现原理
    通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率最高
	select ename from emp where ename = 'SMITH';	通过索引转换为:	select ename from emp where 物理地址 = '0x001';
  1. 索引的分类
    单一索引:给单个字段添加索引
    复合索引:给多个字段联合起来添加1个索引
    主键索引:主键上自动添加索引
    唯一索引:有unique约束的字段上会自动添加索引
  2. 索引什么时候失效?
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) ...查看全部

一、故事背景
有一张 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