MYSQL数据库（进阶篇）

存储引擎

---

存储引擎可以理解为一种表结构类型

-- 创建表 my_myisam, 并指定MyISAM存储引擎
create table my_myisam(
    id int,
    name varchar(10)
) engine = MyISAM;

show create table my_myisam;

-- 创建表my_memory, 指定Memory存储引擎
create table my_memory
(
    id   int,
    name varchar(10)
) engine = Memory;

show create table my_memory;

面试题：

InnoDB和MyISAM的区别：

1.前者是支持事务的，后者是不支持事务的，单从这一点，感觉肯定要选择前者啊

2.前者是支持行锁的，后者是支持表锁的

3.前者是支持外键的，后者是不支持外键的，感觉现在外键没啥用，阿里都不让用外键

 索引

---

概念

---

概念：索引是一种有序的数据结构，用来帮助mysql高效的获取数据。通过索引指向原始数据，这样能够用一些算法高效的获取数据

优缺点 

---

优点：

1.提高数据的检索效率，降低数据的IO成本

2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点

1.索引也是要占用空间的

2.索引大大提高的数据的查询效率，但是对于增删改的操作来说，是降低了效率，这个很好理解，每次新买的东西，收拾好，找的时候就方便了么。

索引结构

---

B+Tree索引：最常见的索引类型，大部分引擎都支持B+Tree索引

Hash索引：底层数据结构使用hash表实现的，只有精确匹配索引列的查询才有效，不支持范围查询，可以支持精确查询

R-Tree索引：主要用于地理位置数据类型

Full-Text（全文索引）：快速匹配文档的索引类型

索引分类

---

 

1. 如果存在主键索引，主键索引就是聚集索引
2. 如果不存在主键索引，将使用第一个唯一索引作为聚集索引
3. 如果表中既没有主键索引，又没有合适的唯一索引，则InnoDB会自动生成一个rowId作为隐藏的聚集索引

-- 查询指定表的索引
show index from user;

-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on user(name);

-- phone手机号字段的值是非空，且唯一的，为该字段创建唯一索引
create unique index idx_user_phone on user(phone);

-- 为profession、age、status创建联合索引
create index idx_user_pro_age_sta on user(profession, age, status);

-- 删除索引
drop index idx_user_phone on user;

SQL性能分析工具

---

-- 查询数据库sql的执行频次
show global status like 'Com_______';

-- 查看慢查询日志是否开启
show variables like 'slow_query_log';

# 是否支持profile操作
select @@have_profiling;

-- 查看profile开启情况， 0为关闭，1为开启
select @@profiling;

-- 设置profile为开启状态
set profiling = 1;

# 查询当前数据库所有sql语句的耗时情况
show profiles ;

# 指定SQL的耗时情况
show profile for query 168;

# 查看指定query_id的sql语句CPU使用情况
show profile cpu for query 168;

# 查询sql语句执行计划
explain select * from user where id = 1;
desc select * from user where id = 1;

如果MYSQL的慢查询日志默认没有开启，需要在MYSQL的配置文件(etc/my.cnf)中配置如下信息

# 开启MYSQL慢日志查询开关

slow_query_log = 1

# 设置慢日志的时间为2s，SQL语句执行时间超过2s,就会视为慢查询，记录慢查询日志

long_query_time = 2

配置完成以后，需要对数据库服务器进行重启

explain执行计划查询结果分析：

id：select查询的序列号，表示查询中执行select子句或者操作表的顺序（id值如果是相同的，那么是从上到下执行；id值不同的话，id值越大，越先执行）

select type：代表的是查询的类型

type：访问的类型，从NULL、system、const、eq_ref、ref、range、index、all，上述字段，依次访问效率降低

索引的使用原则 

---

 最左前缀法则

如果是联合索引，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）

这句话是什么意思呢？直接上截图

该表的联合索引为id_workno_name_age

按照顺序作为查询条件进行查询的时候，索引是不会失效的

 

如果跳过第一列，或者跳过中间列，那么后面的索引就会失效，看截图

 范围查询

联合索引中，出现范围查询（> <），范围查询右侧的列索引会失效

当添加条件age>18的时候，索引值的长度从91变成了48，说明后面的索引失效了 

解决方案：如果业务允许的话，>最好更改为>=就可以了

 

 索引运算操作

不要在索引列上进行运算操作，否则索引会失效，这个很简单，就是查询条件不要进行运算，直接查

字符串不加引号

这个也很好理解，就是用字符串查询的时候，就要用加引号的查询，不然就会索引失效

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部进行模糊匹配，索引就会失效。如果大数量查询的，尽量规避头部模糊匹配

• LIKE '张三%'，索引不会失效；
• LIKE '%张三'，索引会失效；
• LIKE '%张三%'，索引会失效；

OR连接的条件

如果是用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会用到，很好理解，就是or连接的条件中，只要没有列没有索引，那么用or连接起来的条件就不会用到索引

数据分布影响

如果Mysql评估使用索引比全表更慢，则不使用索引（不常用，知道就行了，感觉更像是mysql的一种内部计算机制）

SQL提示

SQL提示，是优化数据库的一个重要手段，简单的说，就是SQL语句中加入一些认为的提示来达到优化操作的目的。

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少使用SELECT *

前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变的很大，查询时，浪费大量的磁盘IO，影响查询效率。此时，可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

create index idx_xxxx on table_name(column(n));

唯一索引的选择性是1，这个是最好的索引选择性，性能也是最好的

计算公式：

select count(distinct email) from tb_user/select count(*) from tb_user

select count(distinct substring(email, 1, 5)) from tb_user/select count(*) from tb_user

单列索引和联合索引

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

1. 针对数据量较大，而且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where），排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的频率就越高
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引。联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就会越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL进行约束。当优化器知道每列是否包含NULL值的时，它可以更好的确定哪个索引最有效的用于查询

SQL优化

插入数据优化

1. 批量插入，一次性插入的数据应该在500-1000条之内
2. 手动事务提交
3. 主键顺序插入
4. 如果是大批量插入数据，使用insert性能较低，此时可以使用Mysql数据库提供的load指令进行插入

# 客户端连接服务器，加上参数--local-infile
mysql --local-infile -u root -p

# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

# 执行load指令将准备好的数据加载到表结构中
load data local infile '/root/sqlq.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

主键优化

1. 主键的设计原则
2. 在满足业务需求的情况下，尽量降低主键的长度
3. 插入数据时，尽量选择顺序插入，选择自增主键
4. 尽量不要使用uuid做主键或者其他自然主键，如身份证号
5. 业务操作的时候，尽量避免对主键进行操作

Order By优化

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
2. 尽量使用覆盖索引
3. 多字段进行排序，一个升序，一个降序，那么建立联合索引的时候，应该对升序的字段建立升序索引，对于降序的字段建立降序索引
4. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认是256k）

Group By优化

1. 在分组操作时，可以通过索引来提高效率
2. 在分组操作时，索引的使用也满足最左前缀法则

Limit优化

• 问题：一个常见的问题就是Limit 2000000 10， 此时需要Mysql排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的时间耗时代价比较大
• 解决方案：
  • 一般是先对主键id进行分页，id必须是有索引的，查出来的数据作为一张表，然后和主表进行连接查询完成优化
  • SELECT * FROM user u, (SELECT id FROM user ORDER BY id LIMIT 2000000 10) t WHERE u.id = t.id;

Count优化

• 借助其他表进行记录数据，也可以使用redis记录数据，感觉这个优化只针对于少量的业务场景
• COUNT(*)>COUNT(1)>COUNT(主键id)>COUNT(字段)，尽量使用COUNT(*)

UPDATE优化

• InnoDB的行锁是针对索引加的锁，不是针对于记录加的锁，并且该索引不能失效，否则会从行级锁升级为表级锁，这个优化其实就是更新的时候尽量使用主键id去更新数据，就是先用别的条件查询出需要更新的行数据，更新的时候用update user set name = '张三' where id = 1；进行更新

视图

-- 创建视图
create or replace view stu_v_1 as select id, name from student where id <=10;

-- 查询创建视图的雨具
show create view stu_v_1;

-- 查询视图的数据
select * from stu_v_1;
select * from stu_v_1 where id = 4;

-- 修改视图
create or replace view stu_v_1 as select id, name, age from student where id <=10;
alter view stu_v_1 as select id, name from student where id <=10;

-- 删除视图
drop view if exists stu_v_1;

-- 创建视图增加检查选项
create or replace view stu_v_1 as select id, name from student where id <= 20 with cascaded/local check option;

-- 把数据添加到视图中(数据添加失败)
insert into stu_v_1 value (32, '张三2');
-- 把数据添加到视图中(数据添加成功)
insert into stu_v_1 value (3, '张三3');

存储过程

-- 创建存储过程
create procedure p1()
begin
    select count(*) from student;
end;

-- 调用存储过程
call p1();

-- 查看存储过程
-- 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'food-dev';
-- 查询某个存储过程的定义
show create procedure p1;

-- 删除存储过程
drop procedure if exists p1;

-- 将SQL语句的结束符更改为$$
delimiter $$

-- 将SQL语句的结束符更改为;, 默认就是;
delimiter ;

系统变量

-- 查看系统变量
show variables ;

-- 查看当前会话和事务提交的开关
show session variables like 'auto%';

-- 查看全局会话和事务提交的开关
show global variables like 'auto%';

-- 查看具体某个系统变量的值（当前session会话）
select @@session.autocommit;

-- 查看具体某个系统变量的值（全局）
select @@global.autocommit;

-- 设置变量的值(当前会话)
set session autocommit  = 0;

用户自定义变量

-- 变量：用户自定义变量
-- 赋值
set @myname = 'itcast';
set @myage = 10;
set @mygender = '男', @myhobby = 'Java';

set @mycolor:='red';

# 查询自定义变量
select @myname, @myage, @mygender, @myhobby, @mycolor;

# 将某个查询出来的值赋值给某个变量
select count(*) into @mycount from student;

# 查询自定义变量
select @myname, @myage, @mygender, @myhobby, @mycolor, @mycount;

局部变量

-- 变量：局部变量
-- 声明局部变量的关键字：declare
-- 赋值
create procedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;

-- 查询存储过程
call p2();

IF

-- IF存储过程示例
create procedure p3()
begin
    declare score int default 90;
    declare result varchar(10);

    if score>=85 then
        set result = '优秀';
    elseif score >= 60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;
    select result;
end;

call p3();

参数

-- 传参的存储过程
create procedure p4(in score int, out result varchar(10))
begin
    if score>=85 then
        set result = '优秀';
    elseif score >= 60 then
        set result = '及格';
    else
        set result = '不及格';
    end if;
    select result;
end;

call p4(90, @result);

-- 传参的存储过程(将传入的200分制的分数，进行换算，换成百分制，然后返回分数)
create procedure p5(inout score double)
begin
    set score:= score * 0.5;
end;

set @score = 198;
call p5(@score);
select @score;

case

-- case
-- 根据传入的月份，判定月份所属的季节
-- 1-3月份为第一季度
-- 4-6月份为第二季度
-- 7-9月份为第三季度
-- 10-12月份为第四季度
create procedure p6(in month int)
begin
    declare result varchar(10);
    case
        when month>=1 and month<=3 then
            set result:='第一季度';
        when month>=4 and month<=6 then
            set result:='第二季度';
        when month>=7 and month<=9 then
            set result:='第三季度';
        when month>=10 and month<=12 then
            set result:='第四季度';
        else
            set result:='非法参数';
    end case;

    select concat('您输入的月份为：', month,',所属的季度为：', result);
end;

call p6(10);

while

-- while 计算从1累加n，n为传入的参数值
-- A 定义局部变量，记录累加之后的值
-- B 每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p7(in n int)
begin
    declare total int default 0;
    while n > 0 do
        set total = total + n;
        set n = n - 1;
        end while;
    select total;
end;

call p7(10);

repeat

-- repeat 计算从1累加n，n为传入的参数值
-- A 定义局部变量，记录累加之后的值
-- B 每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
        set total = total + n;
        set n = n - 1;
    until
        n <= 0
        end repeat;
    select total;
end;

call p8(100);

loop

-- loop 计算从1累加n，n为传入的参数值
-- A 定义局部变量，记录累加之后的值
-- B 每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p9(in n int)
begin
    declare total int default 0;
    sum:loop
        -- 如果n小于等于0，则退出循环
        if n <= 0 then
            leave sum;
        end if;
        set total = total + n;
        set n = n - 1;
    end loop sum;

    select total;
end;

call p9(10);

-- loop 计算从1累加n，n为传入的参数值
-- A 定义局部变量，记录累加之后的值
-- B 每循环一次，就会对n进行减1，如果n减到0，则退出循环
-- C 如果档次累加的数据是奇数，则直接进入下一次循环
create procedure p10(in n int)
begin
    declare total int default 0;
    sum:loop
        -- 如果n小于等于0，则退出循环
        if n <= 0 then
            leave sum;
        end if;
        if n % 2 = 1 then
            set n = n - 1;
            iterate sum;
        end if;
        set total = total + n;
        set n = n - 1;
    end loop sum;

    select total;
end;

call p10(10);

锁

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的增删改语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获得一致性视图，保证数据的完整性

-- 对数据库进行全局加锁
flush tables with read lock;

-- 执行SELECT语句
select * from account where name='张三';

-- 执行update语句是不可以的
update account set money = 1000 where name = '张三';

-- 解除全局锁
unlock tables;

表级锁

表共享读锁

当前客户端

-- 对于account增加表共享读锁
lock tables account read;

-- 当前客户端可以正常对数据进行查询
select * from account;

-- 当前客户端是不可以对数据进行更新的
update account set money = money + 1000 where name ='张三';

-- 解除表级锁
unlock tables;

其他客户端 

-- 其他客户端是可以整正常执行查询操作的
select * from account;

-- 其他客户端在执行更新操作的时候会被阻塞
update account set money = money + 1000 where name ='张三';

总结：对于增加了表共享读锁的表，当前客户端可以查询该表的数据，其他客户端也可以查询该表的数据。当前客户端对该表进行增删改操作的时候，会直接报错，其他客户端对当前表进行增删改操作的时候，会进入阻塞状态，当当前表的锁解除的时候，其他客户端的更新操作可以正常执行。

表独占写锁

当前客户端

-- 对当前表格增加表共享读锁
lock tables account write;

-- 当前客户端可以对表正常查询
select * from account;

-- 当前客户端可以执行增删改操作
update account set money = money + 1000 where name = '张三';

-- 解除表锁
unlock tables;

其他客户端

-- 增加锁表之后，其他的客户端对表的查询会进入阻塞状态
select * from account;

-- 增加锁表之后，其他的客户端对表的增删改会进入阻塞状态
update account set money = money + 1000 where name = '张三';

总结：对于增加了表独占写锁的表，当前客户端可以对表进行正常的增删改查操作，其他客户端在对当前表执行增删改查操作的时候，会进入阻塞状态

元数据锁

元数据锁：

当执行select语句的时候，会增加共享读锁，当执行增删改语句的时候，会增加共享写锁，共享读锁和共享写锁是相互兼容的，所以，执行查询操作的时候，增删改操作也可以正常执行。

但是当执行alter语句的时候，会增加EXCLUSIVE锁，这个锁和共享读锁和共享写锁是互斥的，这个时候，如果别的客户端有正在开启的事务的话，alter语句会处于阻塞状态，直到别的事务提交以后，才能够正常执行

-- 查看元数据锁的SQL
select OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION from performance_schema.metadata_locks;

意向锁

概念：大白话解释一下，就是线程A对某个表的某条数据进行增删改的操作的时候，会自动对该数据增加行锁，当线程B要对该表进行DML操作的时候，肯定是不能对该表增加表锁的，因为行锁和表锁会冲突的，这个时候，如果一行一行的进行检查的话，那么，太浪费性能了，这个时候A线程在执行的时候除了增加某一行的行锁之外，还对该表增加意向锁，意向锁并不是真实的锁，而是一个标识，当别的线程也要进行对表操作的话，直接检查意向锁即可，如果意向锁的情况和要B线程要加的锁是兼容的，那么可以直接加表锁，如果不兼容的话，那就不能加表锁，B线程就会进入阻塞状态

意向共享锁

查询语句会生成意向共享锁，与表锁共享锁兼容，与表锁排他锁互斥

意向排他锁

增删改语句会生成意向排他锁，与表锁共享锁和排他锁都互斥

-- 查看意向锁及行锁的加锁情况
select OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_DATA from performance_schema.data_locks;

行级锁

行级锁是针对于数据的索引进行加锁，并不是针对于记录加锁的

行锁：锁定某个单行记录，防止其他事务对改行数据进行修改和删除的操作

间隙锁：锁定索引间隙，确保索引记录间隙不变，防止其他事务进行insert操作，造成幻读

注意事项：

如果增删操作的条件值，没有索引的话，行锁会升级为表锁

InnoDB引擎

所有的数据都是在表空间，一个表空间分为多个段，一个段分为多个区，一个区分为多个页，一个分为多行，一行分为字段，索引等信息

InnoDB的内存结构

Buffer Pool：缓冲池是主内存的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据，然后再以一定的频率刷到磁盘中，从而减少磁盘IO，加快处理速度