Mysql进阶学习

目录

一.Mysql服务器内部架构(了解)

二.Mysql引擎

2.1 innodb引擎

2.2 myisam引擎

三.索引

3.1索引分类

3.2mysql索引数据结构

3.3聚簇索引和非聚簇索引

3.4回表查询 

3.5索引下推

四.事务

数据库事务特征

事务隔离性，隔离级别

事务实现原理

 五.锁

①全局锁

②表级锁

③行级锁

六.sql优化

一.Mysql服务器内部架构(了解)

二.Mysql引擎

mysql中的引擎就是实际对数据操作的一种实施者，不同的引擎的所使用的技术不同。

查询引擎种类sql语句：SHOW ENGINES;

主要讲innodb引擎和myisam引擎

2.1 innodb引擎

        是一个综合能力比较强的引擎，支持事务，行级锁，外键约束，全文索引，支持数据缓存等功能。

        支持主键自增，不存储表的总行数(统计表的总行数，innodb中默认不存储，需要自己查询)。

适合增删改较多，且数据重要的场景。

2.2 myisam引擎

        不支持事务，只支持表锁，增，删，改操作时会锁定整个表效率低，适合查询较多的情况，支持全文索引，存储表的总行数。

三.索引

什么是索引?

数据库索引是为了实现高效数据查询的一种排好序的数据结构。

索引类似于书的目录，通过目录可以快速的定位到想要找到的数据。

因为一张表中的数据会有很多，如果直接去表中检索数据会效率低(逐行查找)，所以需要为表中的数据建立索引(一般主键默认会创建索引)，这样就会提高查询效率。

索引优势

        通过索引可以快速定位到数据，降低IO次数，提高效率。排序列添加索引，也可以提高排序的效率(因为索引是有序的)

索引劣势

        索引保存也是需要占用空间的。增删改数据时，数据发生变化，索引也需要随之变动，也是需要开销的。

索引创建原则

哪些场景适合

①主键自动创建索引

②查询条件列

③多使用组合索引(多个列用一个索引)，减少单值索引

④建议排序和分组使用到的列

⑤对数据量大的表

哪些场景不适合

①表中数据比较少(类型表，菜单表，友情链接，系统信息表)

②查询条件中用不到的列

③增删改频率高的表

④重复率高的列(性别：男，女)

3.1索引分类

①主键索引

创建表时，设置哪个列为primary key 就是主键列，主键列默认会自动创建索引。

create table test1(
   id int  primary key auto_increment,  -- 创建表时直接设置主键
   account varchar(20) unique    -- 设置唯一约束,  会添加唯一索引
)

create table test2(
   id int
)

ALTER TABLE test2 add PRIMARY KEY test2(id);    -- 后来修改表结构,设置主键
ALTER TABLE test2 drop PRIMARY KEY ;     -- 删除主键

②唯一索引

设置某个列数据唯一性，会创建唯一索引。

create table test1(
   id int primary key auto_increment,  -- 创建表时直接设置主键
   account varchar(20) unique    -- 设置唯一约束,  会添加唯一索引
)


--test2表
CREATE UNIQUE INDEX index_unique_account ON test2(account);  -- 创建表时未添加索引，后面修改表添加唯一索引

③单值索引

一个索引中，只包含一个列。

--对单列添加单一索引
--index_test1_name：索引名
create index index_test1_name on test1(name)  


​
drop index index_test1_name on test1  -- 删除索引sql

④组合索引(复合索引)

一个索引中包含多个列，节省了索引开支。

--给两个列添加组合索引
create index index_test1_name_age on test1(name,age)

！注意： 满足组合索引最左前缀原则

在使用组合索引时, 条件中必须要用到最左侧的列，否则索引失效

例如 a，b，c 3个列，a 和 b 创建组合索引

create index index_test1_a_b on test1(a,b)

由于a在b的左侧，故只有使用a作为查询条件时，索引才会生效。

where a = 1 and b = 2 索引生效

where b = 2 and a= 1 索引生效

where a = 1 and c = 2 索引生效

where b = 1 and c = 2 索引不生效

在查询sql语句前使用explain可查看sql执行计划

explain select * from test3 where b=2 and c=3   --查看sql执行计划

⑤前缀索引

有些列长度比较大，需要只给前面指定的长度区间添加索引即可。

create index 索引名 on 表名(列名(长度))


--给test表的title列的前5个字符创建索引名为index_test_title的索引
例：create index index_test_title on test(title(5))

⑥全文索引

模糊查询时，即使列有索引，也会导致索引失效。

可以为列添加全文索引

-- 在test 表的 title 列上创建一个全文索引

-- ngram：解析器
CREATE FULLTEXT INDEX index_test_title ON test(title) WITH PARSER ngram;


explain SELECT * FROM test WHERE MATCH(title) AGAINST('中国')

3.2mysql索引数据结构

        由于二叉树和平衡二叉树一个节点只能存储一个元素，再加上mysql使用自增主键，导致不适合使用二叉树或平衡二叉树。所以mysql底层使用的是B+树。

原因：

①一个节点中可以存储多个索引数据。

②表数据都存储在叶子节点，非叶子节点不存储表数据，只存索引，这样一个节点就可以存储更多的索引。

③叶子节点之间还有指针指向，所以非常适合范围查询。

3.3聚簇索引和非聚簇索引

        聚簇索引：数据表的数据会按照聚簇索引的顺序存储。每个表只能有一个聚簇索引，因为数据只能按一种顺序存储。

innodb引擎中主键索引，就是聚簇索引，主键和数据在一个树上。

        非聚簇索引：数据表的数据顺序和索引顺序无关，索引是单独存储的，里面存的是索引值和指向数据的地址。

        myisam引擎中，由于索引和数据分别在两个不同的文件中存储，找到了索引，还需要重新查找一次才能找到数据，这种称为非聚簇索引。

innodb引擎中，像普通的索引也称为二级索引，他们也是非聚簇索引。

        例如姓名，通过名字查找人的所有信息时，在姓名索引树中找到后，还需要在主键索引树中再次进行查找，最终在主键索引树中找到数据，这种称为非聚簇索引。

3.4回表查询

回表查询指的是查询时的次数。

例如：学生有id，学号，姓名三个信息，设定id是主键，学号添加唯一索引。

select * from student where id = 1

        ① 通过id(主键)查询学生所有的信息, 这时只需要查询一次即可。

select * from student where 学号 = 12

        ②通过学号查询学生所有信息，由于学号是普通索引，先通过学号，在学号索引树上找学号，然后再通过id去回表二次查询主键索引树，查询两次, 称为回表查询了。

select 学号 from student where 学号 = 12 

        ③通过学号查询学号自己(判断学号是否存在)，由于使用学号只查询学号本身，并不查询其他数据，这种情况下，可以在学号索引树上直接找到学号数据，这种情况不需要回表查询了。

3.5索引下推

        mysql5.6版本引入的一项查询优化技术，将条件筛选过程下推到索引树上。

        以前没有索引下推，先找具体的数据，然后再对数据进行条件过滤，查询的数据范围就比较大。

        使用索引下推，直接在索引树上进行条件筛选，筛选出符合条件的记录，然后只将满足条件的记录进行回表查询，减少了回表查询的次数。适用的是非主键索引。

索引扫描（找索引） - - > 索引条件过滤（过滤索引） - - > 回表操作（查询数据） - - > 剩余条件过滤（查其他数据）

--建立索引
create index index_name on employee(name)

-- 使用索引下推
select * from employee where name like "张%" and salary >3000

四.事务

什么是数据库事务?

首先数据库事务是数据库对执行操作的一种管理机制。

数据库事务特征

BEGIN：开始一个事务

ROLLBACK：事务回滚

COMMIT：事务确认

SET GLOBAL （全局）/ SESSION（本次会话） autocommit=0;    --禁止自动提交
SET GLOBAL / SESSION autocommit=1;     --开启自动提交

SHOW GLOBAL / SESSION VARIABLES LIKE 'autocommit';

 五.锁

        mysql中读写不互斥(前提是没有使用串行化隔离级别)，但是写写操作是互斥的，mysql中使用锁机制来实现写写互斥。

按照锁的粒度分为：

①全局锁

 锁定整个数据库，只允许读操作

一般在备份数据库时使用

FLUSH TABLES WITH READ LOCK  --添加全局锁

UNLOCK TABLES;  -- 释放全局锁

备份数据库语句 ：（再cmd命令窗口里执行）

mysqldump --single-transaction -uroot -proot 库名> E:/文件名.sql

②表级锁

给整个表加锁

myisam引擎只支持表锁，innodb默认支持行锁。

③行级锁

加锁的粒度以行为单位。

行级锁又可以分为:

行锁: 只锁定操作的那一行数据，使用的主键作为条件（防止同一行数据被多个事务修改或删除）。

间隙锁: 锁定的是一个范围 。如： id>2 and id <6，表里的id有（1,2,4,5,10），那么当开启事务时，id范围（2,4）和id范围（5,10）的数据是不能插入的。因为你的数据库表里根本就没有6这个id数据，只能通过下一个索引的顺序（10）进行加锁。索引的顺序 加锁，而不是按查询条件加锁。     （防止幻读）。

临键锁: 是行锁和间隙锁的组合（为了防止幻读并防止数据冲突）。

行锁又分为：

①共享锁:

        主要是为查询语句添加的，为查询语句如果添加了共享锁，那么其他事务可以读，但是其他事务不能写的(指定的是同一条记录)。(其他事务可读不可写)

select * from employees where id = 1 lock in share mode; --为查询语句添加共享锁

读锁，允许多个事务读，不允许读写同时进行。

②排他锁: 就是互斥锁，当一个事务操作时，其他事务就不能进行加共享锁和排他锁操作。

👉其他事务无论是共享锁还是排他锁，排他锁都会阻止任何其他类型的锁。

        insert ,update,delete操作时，自动会添加排他锁。

👉查询操作如果需要添加排他锁，可以在查询语句后面添加 for update 语句

select * from employees where id = 1 for update  --为查询语句添加排他锁

六.sql优化