mysql高级知识点

一、mysql架构

1. 连接层：负责接收客户端的连接请求，可以进行授权、认证(验证账号密码)。
2. 服务层：负责调用sql接口，对sql语法进行解析，对查询进行优化，缓存。
3. 引擎层：是真正进行执行sql的地方，不同的引擎特点不同。
4. 物理文件存储层：使用各种文件以及各种日志文件来存储数据。

与数据库交互一次：首先通过语言向数据库发送SQL，SQL经过服务层经过一系列处理，然后到达引擎层，选择不同的引擎进行处理，最后将数据存储在文件或者日志中。

二、mysql引擎

引擎：对数据处理的不同的方式(锁的方式、事物方式)。mysql中提供了多种不同的引擎，在实际的开发中，可以根据需要选择不同的引擎，这样会提高灵活度。

1、查看数据库支持的所有引擎 show engines;

2、查看表的引擎 show table status like 'user';

3、修改引擎

方式1：将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.

方式2：创建表时指定引擎

create table user(
	id int,
	account int
)engine=myisam;

 方式3：修改表的引擎

alter table 表名 engine = INNODB; 

存储引擎主要有：MyIsam，InnoDB，Memory，Blackhole，CSV，Performance_Schema，Archive，Federated，Mrg_Myisam（innodb是唯一一个支持事物的引擎）

主要分析InnoDB和MyIsam：

对比项	Innodb（默认）	MyISAM
事务	支持	不支持
强外键约束	支持	不支持
缓存	缓存索引，还缓存真实数据	只缓存索引，不缓存真实数据
锁	行级锁(对某行数据操作时，只会锁定某一行数据，效率高)	表锁(进行增删改操作时，会锁定整张表，效率低)
主要适用于	增删改多的场景	查询多的场景
存储表的总行数	不支持	支持
表空间	大	小
关注点	事务	性能

三、索引

为什么要用到索引？数据量大的时候，需要查询一个数据，效率非常低。因为要从第一个开始进行查找，直到找到数据为止。

索引是一种有序的数据结构，利用索引可以提高mysql的查询效率。类似于书的目录，不需要翻遍每一页去查询数据，可以通过目录快速定位获取。创建数据库表的时候，会为一些列创建索引，这样查询效率高。

本质都是：通过不断地缩小范围，来获取数据，同时把随机的事件变成顺序的事件。

为了加快col2的查找，给col2创建索引，就会给col2列创建一个二叉树。

优势：提高查询效率，体现在与硬盘IO次数减少了；由于是排好序的，降低了查询次数，cpu消耗低。

劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。所以索引需要存储空间；新增、修改、删除操作时也需要维护索引需要的时间开销。

1、索引创建原则

哪些情况需要创建索引：

• 主键会自动创建唯一的索引
• 查询条件的列（where后面的语句)，加快条件的判断速度。
• 外键的列
• 排序的列
• 分组中的字段

哪些列不适合建立索引：

• 表记录太少
• 经常增删改的表（因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件）
• Where条件里用不到的字段
• 数据重复率高的列（例如性别：男、女）

2、索引分类

查看索引：show index from 表名;

1.主键索引：设定为主键后数据库会自动建立索引

alter table 表名 drop primary key;#删除主键(索引)
ALTER TABLE test add PRIMARY KEY test(id);#添加主键

2.单值索引：一个索引中只包含一个列，一个表可以有多个单列索引

创建单值索引：create index 索引名 on 表名(列名);

删除索引：alter table 表名 drop index 索引名;

create index index_student_name on student(name);#创建单值索引
explain select * from student where name="张三" #测试
alter table student drop index index_student_name;#删除索引

3.唯一索引：索引列的值必须唯一，允许为null

create unique index 索引名 on 表名(列名);

4.组合索引(复合索引)：一个索引中可以包含多个列。组合索引比单值索引的开销小

创建复合索引：create index 索引名 on 表名(列 1,列 2...);

删除索引：alter table 表名 drop index 索引名;

create index index_student_name_gender on student(name,gender);#创建

组合索引要满足最左前缀原则：

例如表中有a、b、c 3列，a、b两列创建组合索引。当查询条件中用到组合索引时，必须使用到最左边列，否则索引失效。

create index index_test_a_b on test(a,b); #a列就是最左边列
explain select * from test where a = 'aa' and b='bb'
explain select * from test where b = 'aa' and a='bb'
explain select * from test where a = 'aa' and c='bb'

explain select * from test where b = 'aa' and c='bb' -- 组合索引失效 因为没有用到最左边列

 5.全文索引：可以解决模糊查询索引失效的问题。（模糊查询时，索引会失效。）

explain select * from test where name like '%j%'#模糊查询时，索引失效

create fulltext index 索引名 on 表名(字段名) with parser ngram;

select 结果 from 表名 where match(列名) against('搜索词')

create fulltext index index_content ON test(content) with parser ngram;#test表中,content列创建了全文索引
 
explain select * from  test where MATCH(content) against('中国')#content列中有没有出现“中国”

3、索引的数据结构

二叉树、红黑树在自增主键中使用，效率不高。

使用B+树：

• 排好序的，一个节点可以存储多个数据，使得B+树的高度不会太高。
• 数据存储在叶子节点，叶子节点之间还有连接
• 非叶子节点只存储索引，可以放更多的索引。

4、聚簇索引和非聚簇索引

聚簇索引：找到了索引就找到了数据。innodb中的主键索引就是聚簇索引，主键索引的B+树的叶子节点存储的就是数据。

一个表生成的数据、索引、表结构都存储在.ibd文件中，因为数据和主键的索引树绑在一起的。

 innodb中除了主键索引是聚簇索引，其他索引是非聚簇索引。例如name列添加了索引，但是不能直接通过name找到数据，先通过name索引树找到主键索引，再通过主键找到数据，需要两次查找，所以是非聚簇索引。

非聚簇索引：找到了索引，并没有找到数据，需要二次查询。myisam引擎中的索引都是非聚簇索引，因为表结构、索引、数据都在不同的表中存储。  

5、回表查询

回表查询也就是二次查询。例如通过学号/姓名等信息作为条件查询，首先通过学号/姓名找到主键索引，再通过主键索引找到数据，称为回表查询。

例如：学生表

id 主键索引

no 唯一索引

name 单值索引

如果我们通过主键查询学生信息，因为主键索引是聚簇索引，找到主键索引就找到了数据，所以不需要回表查询。

select * FROM student WHERE id = 1

 如果通过学号去查询学号和姓名，因为学号是非聚簇索引，通过学号找到主键索引，再通过主键索引找到学号和姓名，需要二次回表查询。

SELECT NO,NAME FROM student WHERE NO = 123

如果通过学号只查询学号是否存在，找到学号索引，就找到了数据，此种场景不需要再回表查询了，也属于聚簇索引。

SELECT NO FROM student WHERE NO = 123

四、锁机制

多个事物(线程)对同一个表中的同一行数据进行操作，需要通过锁进行保护。按照粒度，锁可以分为表锁、行锁、间隙锁。innodb引擎支持行级锁，可以将锁的粒度到行级。

表锁：操作时会锁住整张表，效率低。myisam就只支持表锁。

行锁：操作时会锁住操作的行，并发量高。innodb支持支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁

间隙锁：当条件为一个范围区间时，只对某个区间进行加锁。innodb支持间隙锁。

共享锁(S)：又称读锁。一个事物对数据A进行读操作时，如果添加了共享锁，其他事物可以对数据A进行读操作，但是其他事物就不能对数据A进行修改，不能加排他锁。添加共享锁语法：select … lock in share mode;

排他锁(Ｘ)：又称写锁。事物A对数据进行增删改操作时，会自动添加锁，其他事物不能对数据加任何锁，直到事物A释放锁才可以。增删改会自动加锁；查询时如果需要添加排他锁，可以使用select … for update;语句。

五、事务

mybatis中提交事物，sqlsession.commit();

spring对事物进行管理：编程式事物、声明式事物(xml配置, 注解)

事物：数据库操作的过程中可能包含多条sql执行，多条sql执行应该是一个整体，保多条sql语句要么全部执行， 要么全部不执行。 用来管理insert、update、delete语句。

mysql中只有innodb引擎支持事物

1、事物特征(ACID)

原子性(Atomicity)：一个事物中有多条sql，要么都成功执行，要么都不执行，执行过程中如果发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

持久性(Durability)：在事物提交后，要保证数据的持久化。

隔离性(Isolation)：在多个事物并发访问下，对多个事物操作要进行隔离，各个事务之间不能互相干扰。

一致性(Consistency)：最终目标就是保证数据操作的一致性。例如经过多次转账操作后，最终的结果和预期的结果应该一致。

2、事务设置

默认情况下，MySQL启用自动提交模式（变量autocommit为on）。只要执行DML操作的语句，MySQL会立即隐式提交事务。

用SET来改变MySQL的自动提交模式

SET GLOBAL autocommit=0; #禁止自动提交 
SET GLOBAL autocommit=1;#开启自动提交 

查看autocommit模式

SHOW GLOBAL VARIABLES LIKE 'autocommit';

例如：

begin;/START TRANSACTION;#开始一个事务
	insert into test(id,name) value(2,'aaa');
commit#事务确认

rollback#事务回滚

3、事务隔离级别

只有InnoDB支持事务，所以这里说的事务隔离级别是指InnoDB下的事务隔离级别。

理论上在某个事务对数据进行操作时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续操作这个数据。但是这样的话对性能影响太大，所以设计数据库的大叔提出了各种隔离级别，来最大限度的提升系统并发处理事务的能力。

查看隔离级别：SELECT @@global.transaction_isolation,@@transaction_isolation;

设置隔离级别：SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

多个事物对同一个数据操作时，采用哪种隔离级别。mysql中默认的隔离级别是可重复读。

4种隔离级别：

1.读 未提交(read uncommitted)：A事物正在对数据操作，事物没有提交，此时B事物可以读到A事物修改后未提交的数据。可能出现的问题：脏读、不可重复读、幻读。

2.读 已提交(read committed)：A事物对数据正在操作，B事物只能读到A事物已经提交的数据，如果未提交，是读不到的。解决了脏读问题，但是仍然有 不可重复读、幻读问题。

3.可重复 读(repeatable read)：事物B在事物A中连续读取同一个数据，两次读到结果是一致的，哪怕在此期间有别的事物修改数据。解决了 不可重读问题。

mysql8中的可重读的正常查询解决了幻读问题，如果在查询语句后面添加for update语句，会出现幻读问题。

4.串行化(serializable)：当一个事物对一行进行操作时(即使是读操作)，还未提交，其他事物不能进行增删改操作。

脏读：B事务读取A事务中的数据，但A事物有可能回滚，B事物读到的就是脏数据。

不可重复读：在同一个事物中，两次读同一个id的数据不相同的。（事务A将数据改了，事务B读不到，只能读到和之前一样的？？？？）

幻读：在一个事物，两次读到数量不一致。（事务A新增了一条数据，事务B读不到，只能读到和之前一样的？？？？）

4、事物实现原理（和增删改相关的）

1.原子性原理

在底层使用undolog(回滚日志)日志，保存一个相反的操作。例如执行insert操作，那么undolog日志中会记录一个相反的delete操作；执行delete操作，记录一个insert，当事物回滚时，执行反向操作。

2.持久性实现原理

使用redolog(重做日志)保证持久性。执行sql时，先将sql写入到redolog中，然后再执行保存到数据库；万一这个过程中，数据还没有写入到硬盘，突然断电，那么下次mysql启动时，会重新执行redolog日志，保证持久性。

3.隔离性

多版本并发控制(MVCC Multi-Version Concurrent Control)

为了实现可重复读，底层每次事物操作时，都会生成一个版本，对于数据的多个操作，每个版本之间都存在关系，形成一个版本链。

如果是读已提交，每次会生成一个最新的版本快照，读到最新提交的数据。

如果是可重复读，会在第一次读的时候生成一个版本快照，之后再次读取时，仍然从第一次生成的快照中读。

4.一致性

通过满足原子性、持久性、隔离性，最终达到一致性。

SQL优化

1. 使用varchar代替char

• varchar：字符串长度不固定时使用，按数据的实际长度存储，可以节省存储空间； 如：姓名，地址，QQ号
• char：字符串长度固定时使用，不足补空格； 如：性别，手机号

1）char定长字符串, 不足的会以空格补齐，最多可以存储255个字符

2）varchar变长字符串，有几个字符就存储几个字符，最多可以存储65535个字符

2. 清空表时优先使用truncate

（一张表里有100万条数据，需要清空，怎么做？）

truncate table比delete速度快，且使用的系统和事务日志资源少。

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据

delete是一行一行删的

truncate是DDL语句，对表结构操作的

3.查询SQL尽量不要使用select *，而是具体字段

节省资源、减少网络开销。

可能用到覆盖索引，减少回表，提高查询效率。

4.表连接不宜太多，索引不宜太多，一般5个以内

联的表个数越多，编译的时间和开销也就越大

每次关联内存中都生成一个临时表

应该把连接表拆开成较小的几个执行，可读性更高

5. 尽量使用数值替代字符串类型

主键（id）：primary key优先使用数值类型int

性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；而对于数字型而言只需要比较一次就够了；

字符会降低查询和连接的性能，并会增加存储开销；

6. 提高 group by 语句的效率

反例：先分组，再过滤

正例：先过滤，后分组

7. inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用 inner join

inner join 内连接，只保留两张表中完全匹配的结果集；

left join 会返回左表所有的行，即使在右表中没有匹配的记录；

right join 会返回右表所有的行，即使在左表中没有匹配的记录；

8.首先考虑在where及 order by涉及的列上建立索引

9.避免在where子句中使用or来连接条件

反例：SELECT * FROM user WHERE id=1 OR salary=5000

正例：使用union all把两个两个SQL结果合并

对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；

10. 避免在索引列上使用内置函数，索引失效

11. 避免索引失效

1. 模糊查询将导致全表扫描（两边都有%）

select id from t where name like '%abc%'

2. 避免在where子句中使用or来连接条件，否则导致索引失效，而进行全表扫描，如:select id from t where num=10 or num=20

3. in和not in要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)，对于连续的数值，能用between就不用in，因为B+树中，叶子节点之间有联系，范围查找快一些。select id from t where num between 1 and 3

4. 避免where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)='abc'