MySQL的体系架构 & InnoDB&数据库优化 & 不合适的索引适得其反

一览MySQL的体系架构

引自《MySQL技术内幕InnoDB存储引擎》p4

由体系机构图来看，MySQL主要由以下几部分组成：

1. 连接池组件 （管理连接，权限校验等）

2. 管理服务和工具组件

3. SQL接口组件

4. 查询分析器组件 （语法树分析）

5. 优化器组件

6. 缓冲组件 (存储访问时的缓存表和索引数据。在专用服务器上，通常会为缓冲池分配50% - 75%的物理内存)

7. 插件式存储引擎 （区别于其他数据库的重要特点之一）

8. 物理文件

注意： 存储引擎基于表而非数据库！

存储引擎的底层物理结构的实现！

从连接到操作文件系统大致过程是：

权限校验---> 查询缓存---> 分析器---> 优化器---> 权限校验---> 执行器---> 引擎

一览 InnoDB 结构

InnoDB简介

InnoDB是一个平衡了高可用和高性能的通用存储引擎。完整支撑ACID事务的MySQL存储引擎。具有行锁设计、支持MVCC、外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU。

InnoDB存储引擎的特点

行锁设计，支持外键、一致性非锁定读、MVCC、事务

InnoDB存储引擎功能

不同存储引擎之间的比较

InnoDB存储引擎体系架构

InnoDB存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责 如下工作:

• 维护所有进程/线程需要访问的多个内部数据结构。

• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。

• 重做日志（redo log）缓冲。

• ...

后台线程的主要作用：是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行状态。

1.Master Thread Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插人缓冲（INSERT BUFFER)、UNDO页的回收等。 2.IO Thread 在InnoDB存储引擎中大量使用了AIO(Async IO）来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。

3.Purge Thread 事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎

4.Page Cleaner Thread Page Cleaner Thread是在InnoDB 1.2.x版本中引人的。其作用是将之前版本中脏页的刷新操作都放人到单独的线程中来完成。而其目的是为了减轻原Master Thread 的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

缓冲池

先来看一眼MYSQL InnoDB内存数据对象，如下图：

简介： 存储访问时的缓存表和索引数据。在专用服务器上，通常会为缓冲池分配50%~75%的物理内存

作用： 提高数据库整体性能，加快处理速度。

要点：具体来看，缓冲池中缓存的数据页类型有:索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希(adaptive hash index)、InnoDB存储的锁信息(lockinfo）、数据字典信息（data dictionary）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。

Page：为了high-volume的读取效率，缓冲池进一步被分为页的结构。

LRU：为了缓存的管理效率，缓冲池实现page间的链表，使用LRU算法。缓冲池使用调整后的LRU（最近最少使用）算法，当需求添加新的page时，最近最少使用的page被清除，同时新页面被添加到链表的中间部分

这种中间点插入的策略，把链表分为两个子链表

• 头部：最近被访问过的“年轻”页

• 尾部：最近被访问的old page

这样使新子列表中保存更重要的page，旧子列表包含较少使用的page，这部分page是被清除的候选page

默认情况下，算法配置如下：

• 旧子列表：缓冲池的3/8

• midpoint（中间点）是新子列表尾部和旧子列表头部的交界

• 当旧页被访问，会被移动到缓冲池的头部，随着数据库的运行，一直没有被访问的页会一直后移，直至最后被移除。

数据库页的操作特点：

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。注意 ——> 页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能。

ChangeBuffer

当某些页面不在缓冲池中，缓存会改变二级索引page，这可能会造成insert，update，delete（DML）操作会与其他从缓冲池中的读操作加载的page合并。

不同于聚簇索引，二级索引通常不唯一，同时二级索引的插入相对随机。

同时，为了避免频繁的IO随机读写，当更新和删除操作时，并不会立即写入磁盘，而是会选择系统空闲时定期进行写入磁盘的操作。Change Buffer在内存中，是缓冲池中的一部分。

减少随机访问，提升性能；

写多读少的场景效果更佳；

何时页合并？

1) 访问数据页 2) 后台线程定期merge 3) 数据库关闭merge

什么是索引？

《高性能MySQL（第3版）.pdf》如是说：

当前数据库版本：

存储引擎

存储引擎：基于表而非数据库

MySQL数据库不同于其他数据的一个重要特点： 插件式的表存储引擎

存储引擎表：

• InnoDB： 支持事务、行锁、外键、非锁定读、聚簇索引、

• MyISAM：不支持事务、表锁、支持全文检索

查看mysql支持的引擎：

-- 查看支持的存储引擎
SHOW ENGINES
​
-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'
​
--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename
​
--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

索引是谁实现的？

它帮助MySQL快速查询数据，由存储引擎实现！

命中索引，为何查询快？

不断缩小想要获取数据的范围来筛选数据、避免全表扫描

 索引原理

采用InnoDB存储引擎时，有哪些索引经常被提及？

索引类别	描述	特点	语句(ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）)
主键索引PRIMARY KEY	一个表只能有一个，未显示声明时，InnoDB会为每一行生成一个6字节的ROWID作为主键	唯一、主键索引是一种特殊的唯一索引，必须指定为 primary key、可被其他表引用、逻辑键、不可空、一种约束
唯一索引UNIQUE	可以有多个	列值唯一、可以有NULL值、物理键、不可被引用、可多个、一种索引	需判断唯一性
单列索引
组合索引	由多个字段组成的索引		//普通索引 alter table table_name add index index_name (column_list) ; //唯一索引 alter table table_name add unique (column_list) ; //主键索引 alter table table_name add primary key (column_list) ;
普通索引INDEX	无唯一性之类的限制	允许出现相同的索引的内容重复出现、仅加速查询	创建索引1: CREATE index <索引名> on table_name column_name; 增加索引2: ALTER table table_name add index <索引名> column_name;
自适应哈希索引
前缀索引	取索引列的前N个字段	对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。

组合索引和前缀索引是对建立索引技巧的一种称呼，并不是索引的类型。

索引的类型：

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值

• INDEX(普通索引)：允许出现相同的索引内容

• PRIMARY KEY(主键索引)：不允许出现相同的值

• fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维

• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

索引类别(聚簇索引&非聚簇索引)

• 聚簇索引： 索引与数据存在一起；非聚簇索引(也叫辅助索引)反之！

索引页+数据页组成的组成的B+树就是聚簇索引

B 树

根节点至少包括两个孩子 树中每个节点最多含有m个孩子(m>=2 ) 所有叶子节点都位于同一层 除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子

假设每个非终端结点中包含有n个关键字信息，其中 a)Ki (i=1...n)为关键字，且关键字按顺序升序排序K(i-1)< Ki b)关键字的个数n必须满足:[ceil(m/ 2)-1]<= n <= m-1 c)非叶子结点的指针:P[1],P[2],，...P[M];其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1]，K[i])的子树

B+ 树

 

B+树（引用原文）

MySQL 中最常用的索引的数据结构是 B+ 树，他有以下特点：

1. 在 B+ 树中，所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子结点只存储key的信息，这样可以大大减少每个节点的存储的key的数量，降低B+ 树的高度
2. B+ 树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
3. B+ 树的层级更少：相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
4. B+ 树查询速度更稳定：B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
5. B+ 树天然具备排序功能：B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
6. B+ 树全节点遍历更快：B+ 树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

B+树

 

 

 索引分层

索引页

 

主键目录

 

 

 

 

B+树是B树的变体，其定义基本与B树相同，除了:

非叶子节点的子树指针与关键字个数相同 非叶子节点仅用来索引，数据都保存在叶子节点中 所有叶子节点均有一个链指针指向下一个叶子结点 非叶子节点的子树指针P[i]，指向关键字值[K[i],K[i+1])的子树

为什么B+Tree更适合用来做存储索引

>B+树的磁盘读写代价更低 >B+树的查询效率更加稳定 >B+树更有利于对数据库的扫描

回表

使用非主键索引查询记录时，通过普通索引查询到主键，然后依据主键索引查询对应的记录

索引覆盖

使用非主键索引查询数据时就能获取到需要的字段，不需要查询聚簇索引！

如： SELECT age,name from user where name = '张三' and age = 18 ;

其中，age,name 创建组合索引，不需要回表查询记录（查询列要被所建的索引覆盖。）

如果仅name建了索引，虽然命中索引，但未覆盖，需要回表！！！

最左匹配

索引是复合索引，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个过程就是最左匹配特性。

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<. between、like)就停止匹配，比如a = 3 and b = 4 and c > 5 and d = 6如果建立(a,b.c.d)顺序的 索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 2.=和in可以乱序，比如a = 1 and b = 2 and c= 3建立(a,b,c)索引可以任意顺序，mysql的查询 优化器会帮你优化成索引可以识别的形式

索引下推（Index Condition Pushdown） 优化

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的数据，减少回表次数！

如：MySQL数据库会取出索引的同时，判断是否可以进行 where 条件的优化，也就是将WHERE部分的过滤操作在存储层进行而非服务层！

何时合适创建索引

原则：索引的设计要根据 WHERE 条件和 ORDER BY 还有 GROUP BY 后面的字段进行设计

• 主键自动创建唯一索引

• 查询频繁

• 与其他表关联的字段，外键关系建立索引

• 排序字段，排序字段通过索引访问大幅提高排序速度

• 统计、分组字段

• 单键/组合索引选择查询，高并发下倾向创建组合索引

注意： 组合索引的列不宜超过5个 单表索引不宜过多 单表字段数不宜超过20 列值较长的应该分表存储

索引与临时表

1. 如果GROUP BY 的列没有索引,产生临时表.
2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表. 
3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表. 
4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表. 
5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表. 
6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
7. GROUP BY 和 ORDER BY 的列一样且是主键，但SELECT 列含有除GROUP BY列之外的列，也会产生临时表 
 

不适合创建索引

• 修改频繁字段

• 区分度不高（唯一性太差的字段不适合单独创建索引、数据分布比较均匀） 性别（要么男、要么女），但是某些状态的记录很少时且会被经常查询，建索引可以加速查询

• 数据量较少

• WHERE条件用不到的字段

• 建立索引

create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name (length) , …..);

alter table table_name ADD INDEX [index_name] (index_col_name,...)

添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

• 删除索引

DROP INDEX index_name ON tbl_name; alter table table_name drop index index_name;

删除主键(索引)比较特别: alter table t_b drop primary key;

• 查询索引(均可)

show index from table_name;

show keys from table_name;

desc table_Name;

索引的优缺点

优势

• 提高数据检索效率，降低数据库IO成本

• 降低数据排序的成本，降低CPU的消耗

劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息(页分裂、合并)

• 

索引失效:

• 字段做函数计算

• 类型隐式转换

• 隐式字符编码转换

• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

• like查询是以%开头

常用的SQL优化

大批量插入数据
对于MyISAM：
alter table table_name disable keys;
loading data;
alter table table_name enable keys;
对于Innodb：
1，将要导入的数据按照主键排序
2，set unique_checks=0,关闭唯一性校验。
3，set autocommit=0,关闭自动提交。
4，set foreign_key_checks = 0; 禁用外键
5，删除主外键和索引
6，对于插入操作尽量使用insert into table select或insert into table values(),(),()提高插入性能
7，控制小批量的范围不要太多行才提交，控制好缓存
8，优化group by 语句
    默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但      用户想要避免排序结果的消耗，则可以使用order by null禁止排序
9，有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
10，如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引
    select * from 表名 where 条件1=‘’ or 条件2=‘tt’

SQL语句的执行过程

SQL解析

MySQL执行查询过程:

MySQL优化:

优化顺序及层次：

• （1）SQL 语句及索引的优化

• （2）数据库表结构的优化

• （3）系统配置的优化

• （4）硬件的优化

优化方法：

• （1）选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，例如’省份’、’性别’最好适用 ENUM

• （2）使用连接(JOIN)来代替子查询

• （3）适用联合(UNION)来代替手动创建的临时表

• （4）事务处理

• （5）锁定表、优化事务处理

• （6）适用外键，优化锁定表

• （7）建立索引

• （8）优化查询语句

语句优化:

• （1）Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。

• （2）用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。

• （3） 避免在索引列上使用计算

• （4）避免在索引列上使用 IS NULL 和 IS NOT NULL

• （5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• （6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

• （7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

慢查询设置：

开启 ——> 设置慢查询日志文件路径 & 慢查询时间

show variables like '%quer%';
show status like '%slow_queries%';
set global slow_query_log = on;
set global long_query_time = 1; // 需要重新连接生效或者直接修改配置文件里的值

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log_file='/var/lib/mysql/logs/slow-query.log';
Query OK, 0 rows affected (0.00 sec)
# mysql必须对该目录有读写权限

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

或者直接修改配置文件：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/logs/slow-query.log
long_query_time = 1

重启mysql服务！！！

定位慢查询:

explain + sql语句

如:

Index Key 和 Table Filter

在 RR隔离级别下：

若SQL 语句的 Where 条件使用了两个索引，分别是唯一索引和非唯一索引。MySQL 会根据索引选择性等指标选择其中一个索引来使用，而另外一个没有被使用的 Where 条件就被当做普通的过滤条件，一般称被用到的索引称为 Index Key，而作为普通过滤的条件则被称为 Table Filter。多数情况下，唯一索引性能更高，通常也比主键索引高！

所以，该 SQL 执行的过程就是依次将 Index Key 范围内的索引记录读取，然后回表读取完整数据记录，然后返回给MySQL的服务层按照 Table Filter 进行过滤。

ICP (索引下推)技术

MySQL 5.6 推出的 ICP 技术其实就是 Index Filter 技术，只不过是因为 MySQL 分为服务层和存储引擎层，而 Index Filter 将原本服务层做的过滤操作“下推”到存储引擎层处理。将原来的在服务层进行的Table Filter中可以进行Index Filter的部分，在引擎层面使用 Index Filter 进行处理，不再需要回表进行 Table Filter。

这样做的好处就是减少了加锁的记录数，减少了回表查询的数量，提高了 SQL 的执行效率。

优化器选择不使用索引的情况:

在范围查找、JOIN连接操作等情况下，优化器通常会直接进行全表扫描来得到数据。

如果用户访问的数据量很小，则优化器还是会选择辅助索引；但当访问的数据占整个表的蛮大一部分时（一般20%左右），优化器会选择通过聚集索引来查找数据。因为顺序读远远快于离散读。

对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是 ——> 通过辅助索引查到的数据是少量的。这是当前传统的机械硬盘特性决定的，利用顺序读来替换随机读。如果用的是固态硬盘（随机读操作很快），同时有足够的信心确认辅助索引可以带来更好的性能，那么可以使用关键字 FORCE INDEX来强制使用某个索引！

SELECT * FROM tb FORCE INDEX(索引列) WHERE 索引列 > 1000 and 索引列 < 1100

CREATE TABLE index_test (a INT, b INT, KEY(a), KEY(b)) ENGINE = INNODB;
​
INSERT INTO index_test SELECT 1,1;
INSERT INTO index_test SELECT 1,2;
INSERT INTO index_test SELECT 2,3;
INSERT INTO index_test SELECT 2,4;
INSERT INTO index_test SELECT 1,2;
​
DESC index_test
​
SELECT * FROM index_test
​
EXPLAIN SELECT * FROM index_test WHERE a = 1 and b = 2;
​
EXPLAIN SELECT * FROM index_test USE INDEX(a) WHERE a = 1 and b = 2;
​
EXPLAIN SELECT * FROM index_test FORCE INDEX(a) WHERE a = 1 and b = 2;

常见SQL语句练习

先来看一下表之间的结构和联系

创建表结构

CREATE TABLE course (cid INT, cname VARCHAR(20), tid INT, PRIMARY KEY(cid)) ENGINE = INNODB;
​
CREATE TABLE teacher (tid INT, tname VARCHAR(20), tcid INT,PRIMARY KEY(tid)) ENGINE = INNODB;
​
CREATE TABLE student (sid INT, sname VARCHAR(20), sbirth VARCHAR(20), ssex VARCHAR(10),PRIMARY KEY(sid)) ENGINE = INNODB;
​
CREATE TABLE score (sid INT, cid INT, sscore INT(3),PRIMARY KEY(sid,cid)) ENGINE = INNODB;

向表中添加数据：

• student 学生表

insert into  student select 1 , '赵雷' , '1990-01-01' , '男' ;
insert into  student select 2 , '钱电' , '1990-12-21' , '男' ;
insert into  student select 3 , '孙风' , '1990-05-20' , '男' ;
insert into  student select 4 , '李云' , '1990-08-06' , '男' ;
insert into  student select 5 , '周梅' , '1991-12-01' , '女' ;
insert into  student select 6 , '吴兰' , '1992-03-01' , '女' ;
insert into  student select 7 , '郑竹' , '1989-07-01' , '女' ;
insert into  student select 8 , '王菊' , '1990-01-20' , '女' ;

• course 课程表

insert into course select 1 , '语文' , '02';
insert into course select 2 , '数学' , '01';
insert into course select 3 , '英语' , '03';

• teacher 教师表 插入数据values

insert into teacher values(1 , '张三' , 1);
insert into teacher values(3 , '李四' , 2);
insert into teacher values(2 , '王五' , 3);

• score 分数表

insert into score values(1 , 1 , 80);
insert into score values(1 , 2 , 90);
insert into score values(1 , 3 , 99);
insert into score values(2 , 1 , 70);
insert into score values(2 , 2 , 60);
insert into score values(2 , 3 , 80);
insert into score values(3 , 1 , 80);
insert into score values(3 , 2 , 80);
insert into score values(3 , 3 , 80);
insert into score values(4 , 1 , 50);
insert into score values(4 , 2 , 30);
insert into score values(4 , 3 , 20);
insert into score values(5 , 1 , 76);
insert into score values(5 , 2 , 87);
insert into score values(6 , 1 , 31);
insert into score values(6 , 3 , 34);
insert into score values(7 , 2 , 89);
insert into score values(7 , 3 , 98);

插入十八条记录

1、查询1课程比2课程成绩低的学生的信息及课程分数

思路解析: 需要查询的字段：学生信息 课程1分数 课程2分数

SELECT s.* FROM student s; SELECT sc.sscore FROM score sc; SELECT s.* , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid; SELECT s.* , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid AND sc.cid = 1; SELECT s.* , sc2.sscore as score2 ,sc.sscore as score1 FROM student s JOIN score sc ON s.sid = sc.sid AND sc.cid = 1 LEFT JOIN score sc2 ON s.sid = sc2.sid AND sc2.cid = 2 where sc2.sscore > sc.sscore

2、查询平均成绩大于等于60分的学生编号和学生姓名和平均成绩

-- 查询student表的 sid、sname、avg(score.sscore)
SELECT s.sid , s.sname FROM student s;
​
SELECT s.sid , s.sname , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid;
​
SELECT s.sid , s.sname , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid , s.sname HAVING ROUND(AVG(sc.sscore),2)>=60; 
​
SELECT s.sid , s.sname , ROUND(AVG(sc.sscore),2) as avgScore FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid , s.sname HAVING ROUND(AVG(sc.sscore),2)>=60; 

3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 -- (包括有成绩的和无成绩的) 根据成绩表来看，4、6、8学生的成绩不完整

SELECT
    s.sid,
    s.sname,
    ROUND( AVG( sc.sscore ), 2 ) AS avgScore 
FROM
    student s
    LEFT JOIN score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname 
HAVING
    ROUND( AVG( sc.sscore ), 2 ) < 60 UNION
SELECT
    a.sid,
    a.sname,
    0 AS avgScore 
FROM
    student a 
WHERE
    a.sid NOT IN ( SELECT DISTINCT sid FROM score );

联合查询前面的是正常的三门课平均成绩不足60的 联合后面是无成绩的学生信息

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.sid , s.sname , COUNT(sc.cid) AS '总课程数', SUM(sc.sscore) AS '总分数' FROM student s LEFT JOIN score sc ON s.sid = sc.sid GROUP BY s.sid , s.sname;

SELECT
    s.sid,
    s.sname,
    COUNT( sc.cid ) AS '总课程数',
    SUM( sc.sscore ) AS '总分数' 
FROM
    student s
    LEFT JOIN score sc ON s.sid = sc.sid 
GROUP BY
    s.sid,
    s.sname;

5、查询学过"张三"老师授课的同学的信息

根据表结构关系来看，需要通过中间表 score 建立起学生和教师之间的关系

SELECT
    s.* 
FROM
    student s
    LEFT JOIN score sc ON s.sid = sc.sid 
WHERE
    sc.cid IN (
SELECT
    c.cid 
FROM
    course c 
WHERE
    c.tid = ( SELECT t.tid FROM teacher t WHERE t.tname = '张三' ));

事务的实现

Undo Log 和 Redo Log

Undo Log

Undo Log是与事务相关，主要作用在事务回滚和多版本并发控制(MVCC)中。

Undo Log在回滚段中存储，回滚段在Undo表空间和全局临时表空间中。Undo log被分为insert undo log 和update undo log。Insert undo log 只在事务回滚时需要，一旦事务提交就被丢弃。Update undo log 也被用在一致性读，在一致性读中可能需要update undo log的信息来生成该行数据早期的版本。

关于undo log的建议

定期地提交事务，包括哪些只包含一致性读的事务，否则，InnoDB不会丢弃update undo log中的数据，回滚段会变得越来越大，占满空间。undo log中回滚段的物理空间，通常小于相应插入或更新的行，可以利用这个信息计算回滚段需要的空间

Redo Log

也就是ib_logfile0和ib_logfile1两个文件

这里结合的是MySQL的WAL（Write-Ahead Logging）也就是先写日志，再写磁盘，具体过程是下面这样：当有一条记录要更新，先将记录写到redo log，并更新内存，InnoDB会在空闲的时候，把操作记录更新到磁盘。

不合适的索引，适得其反

 表结构

 索引失效与优化 （操作符左边的运算挪到右边）

 

 索引选择性