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

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

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

8. 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 表名;

4. 什么时候考虑给字段添加索引？
   * 数据量庞大 （根据客户的需求，根据线上的环境）
   * 该字段很少的DML操作 （因为字段进行修改操作，索引也需要维护）
   * 该字段经常出现在where子句中 （经常根据哪个字段查询）

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

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

5. 索引的实现原理
   通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率最高

	select ename from emp where ename = 'SMITH';	通过索引转换为：	select ename from emp where 物理地址 = '0x001';

6. 索引的分类
   单一索引：给单个字段添加索引
   复合索引：给多个字段联合起来添加1个索引
   主键索引：主键上自动添加索引
   唯一索引：有unique约束的字段上会自动添加索引
7. 索引什么时候失效？

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;

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