Mysql进击篇-存储引擎、索引、sql优化、视图、锁、innoDb、管理
1.存储引擎
（1）连接层
最上层是一些客户端和连接服务，主要完成一些类似于连接处理，授权认证、以及相关的安全方案，服务器也会为安全接入的每个客户端验证它所具有的操作权限
（2）服务层
第二层架构主要完成大多数核心服务功能，如sql接口，并完成缓存的查询，sql的分析和优化，部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等
（3）引擎层
存储引擎真正的负责了mysql中数据的存储和提取，服务器通过api和存储引擎进行通信，不同的存储引擎具有不同的功能，这样我们可以根据自己的需求，来选取合适的存储引擎。
（4）存储层
主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
- 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。默认存储引擎是InnoDB。
相关操作：
-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(
...
) ENGINE=INNODB;
-- 查看当前数据库支持的存储引擎
show engines;
InnoDB
InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。
InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。
Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性
2.索引
(1)性能分析语句的查询频次
查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______';
里面7个下划线
（2）慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time 单位 秒 默认10秒）的多有sql语句的日志
Mysql的慢查询日志默认没有开启，需要在mysql的配置文件(/etc/my.conf)中配置如下信息：
#开启mysql慢日志查询开关
show_query_log=1
#设置慢日志的时间为2秒，sql语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完成通过以下命令重启mysql进行测试，查看日志文件中记录的信息/var/lib/mysql/localhost-slow.log
systemctl restart mysqld
(3)explain
EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法：
# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 HWERE 条件;
EXPLAIN 各字段含义：
id：select 查询的序列号，表示查询中执行 select 子句或者操作表的顺序（id相同，执行顺序从上到下；id不同，值越大越先执行）
select_type：表示 SELECT 的类型，常见取值有 SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type：表示连接类型，性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
possible_key：可能应用在这张表上的索引，一个或多个
Key：实际使用的索引，如果为 NULL，则没有使用索引
Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows：MySQL认为必须要执行的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的
filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
3. 索引
语法：
创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引
查看索引
SHOW INDEX DROM table_name;
删除索引
DROP INDEX index_name ON table_name;
例如：
-- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
-- 删除索引
drop index idx_user_email on tb_user;
3.1 最左前缀原则
如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。
4.视图
mysql在5.1之后推出视图，本身是一个虚拟表，数据来自于表，通过执行时动态生成。视图可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户指定不同的查询视图，比如针对一个公司的销售人员，只想给他看部分数据，而某些特殊的数据，比如采购价格，则不会提供给他，再比如，人员薪酬是敏感字段，只能给某个级别以上人员开放
(暂时没用到以后用到更新)
5、存储过程
存储过程，类似于java中的方法，它是一组预先编译好的sql语句的集合，理解成批处理语句
(暂时没用到以后用到更新)
6、变量、流程控制和游标
6.1 变量
6.1.1 分类
（1）系统变量：全局变量、会话变量
（2）自定义变量：用户变量、局部变量
- 全局变量：
描述：服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效，但不能跨重启
1、查看所有系统变量
SHOW GLOBAL VARIABLES;
2、查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
3、查看指定的系统变量的值
SELECT @@global 系统变量名;
4、为某个系统变量赋值
方式一： SET GLOBAL 系统变量名=值;
方式二： SET @@global 系统变量名=值;
- 会话变量
描述：服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）
1、查看所有系统变量
SHOW 【 SESSION 】 VARIABLES;
2、查看满足条件的部分系统变量
SHOW 【 SESSION 】 VARIABLES LIKE '%char%';
3、查看指定的系统变量的值
SELECT 【 SESSION 】系统变量名;
4、为某个系统变量赋值
SET 【 SESSION 】系统变量名=值;
- 用户变量
描述：针对于当前连接（会话）生效
位置：可以在begin end里面，也可以放在外面
1、声明并赋值
SET @变量名=值; 或
SET @变量名:=值; 或
SELECT @变量名:=值;
2、更新值
方式一：
SET @变量名=值; 或
SET @变量名:=值; 或
SELECT @变量名:=值;
方式二：
SELECT xxx INTO @变量名 FROM 表;
3、查看值
SELECT @变量名;
- 局部变量
描述： 作用域，仅仅在定义它的begin end中有效
位置：只能放在begin end中，而且只能放在第一句
1、声明并赋值
DECLARE 变量名 类型 【 DEFAULT 值 】;
2、更新值
方式一：
SET 变量名=值; 或
SET 变量名:=值; 或
SELECT @变量名:=值;
方式二：
SELECT xxx INTO 变量名 FROM 表;
3、查看值
SELECT 变量名;
例子：
#测试：
#方式1：
SET @m1 = 1;
SET @m2 := 2;
SET @sum := @m1 + @m2;
SELECT @sum;
/*输出
+------+
| @sum |
+------+
| 3 |
+---
例如：声明两个变量，求和并打印（分别使用会话变量和局部变量）
#方式1：使用用户变量
SET @m=1;
SET @n=1;
SET @sum=@m+@n;
SELECT @sum;
#方式2：使用局部变量
DELIMITER //
CREATE PROCEDURE add_value()
BEGIN
#局部变量
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 3;
DECLARE SUM INT;
SET SUM = m+n;
SELECT SUM;
END //
DELIMITER ;
6.2 流程控制
解决复杂问题不可能通过一个sql语句完成，需要执行多个sql操作，流程控制语句的作用就是控制存储过程中sql语句的执行顺序，是我们完成复杂操作必不可少的一部分，只要是执行的程序，流程就分为三大类：
- 顺序结构：
程序从上往下依次执行
条件判断语句：IF语句和CASE语句 - 分支结构：
程序按条件进行选择执行，从两条或多条路径中选择一条进行执行
循环语句：LOOP,WHILE和REPEAT - 循环结构
程序满足一定条件下，重复执行一组语句
跳转语句：ITERATE和LEAVE
（1）IF语法结构
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
根据表达式的结果为TRUE或FALSE执行相应的语句，特点：不同的表达式对应不同的操作，使用在begin end中
举例:声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
DECLARE emp_salary DOUBLE;
DECLARE hire_year DOUBLE;
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id = emp_id;
IF emp_salary < 8000 AND hire_year > 5
THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
(2)CASE语句结构
#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
CASE 语句的语法结构2：
#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
举例：
声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE;
DECLARE bonus DECIMAL(3,2);
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
CASE
WHEN emp_sal<9000
THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id;
WHEN emp_sal<10000 AND bonus IS NULL
THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id;
ELSE
UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
END CASE;
END //
DELIMITER ;
(3)循环结构之loop
loop循环语句用来重复执行某些语句，loop内的语句一致重复执行直到循环被退出（LEAVE），跳出循环过程。
LOOP语句的基本格式如下：
[loop_label:] LOOP
循环执行的语句
END LOOP[loop_label]
其中，loop_label表示LOOP语句的标注名称，改参数可以省略
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
DECLARE avg_salary DOUBLE;
DECLARE loop_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_salary FROM employees;
label_loop:LOOP
IF avg_salary >= 12000 THEN LEAVE label_loop;
END IF;
UPDATE employees SET salary = salary * 1.1;
SET loop_count = loop_count + 1;
SELECT AVG(salary) INTO avg_salary FROM employees;
END LOOP label_loop;
SET num = loop_count;
END //
DELIMITER ;
(4)WHILE循环结构
WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句。
[while_label:] WHILE 循环条件 DO
循环体
END WHILE
例如：“update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE while_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
WHILE avg_sal > 5000 DO
UPDATE employees SET salary = salary * 0.9;
SET while_count = while_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees; END WHILE;
SET num = while_count;
END //
DELIMITER ;
(5)REPEAT循环结构
REPEAT语句创建一个带条件判断的循环过程，与WHILE循环不同的是，REPEAT循环首先会执行一次循环，然后在UNTIL中进行表达式判断，如果满足条件就退出，即END REPEAT，如果条件不满足，则会继续执行循环，直到满足退出条件为止
REPEAT语句的基本格式如下：
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]
例子：update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
DECLARE avg_sal DOUBLE ;
DECLARE repeat_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_sal FROM employees;
REPEAT
UPDATE employees SET salary = salary * 1.15;
SET repeat_count = repeat_count + 1;
SELECT AVG(salary) INTO avg_sal FROM employees;
UNTIL avg_sal >= 13000
END REPEAT;
SET num = repeat_count;
END //
DELIMITER ;
6.3游标
虽然可以通过筛选条件where和having，或者是限定返回记录的关键字LIMIT返回一条数据，但是，却无法在结果集中像指针一样，向前定位一条记录，向后定位一条记录。或者随意定位到某一条记录，并对记录的数据进行处理。
这时候就可以用到游标，能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。
在sql中，游标是一种临时的数据库对象，可以只想存储在数据库中的数据指针，可以通过操作游标对数据进行操作
使用游标的步骤，不同的DBMS语法不同
第一步，声明游标
在mysql中，使用declare关键字来声明游标，其语法的基本形式如下：
DECLARE cursor_name CURSOR FOR select_statement;
如果使用SELECT语句来获取数据结果集，而此时还没有开始遍历数据，这里select_statement代表的是SELECT语句，返回一个用于创建游标的结果集。
比如：
DECLARE cur_emp CURSOR FOR
SELECT employee_id,salary FROM employees;
DECLARE cursor_fruit CURSOR FOR
SELECT f_name, f_price FROM fruits ;
第二步，打开游标
打开游标的语法如下：
OPEN cursor_name
第三步，使用游标（从游标中取得数据）
FETCH cursor_name INTO var_name [, var_name] ...
第四步，关闭游标
LOSE cursor_name
例子：
创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count
DELIMITER //
CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)
BEGIN
DECLARE sum_salary DOUBLE DEFAULT 0;
#记录累加的总工资
DECLARE cursor_salary DOUBLE DEFAULT 0;
#记录某一个工资值
DECLARE emp_count INT DEFAULT 0;
#记录循环个数
#定义游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
#打开游标
OPEN emp_cursor;
REPEAT
#使用游标（从游标中获取数据）
FETCH emp_cursor INTO cursor_salary;
SET sum_salary = sum_salary + cursor_salary;
SET emp_count = emp_count + 1;
UNTIL sum_salary >= limit_total_salary
END REPEAT;
SET total_count = emp_count;
#关闭游标
CLOSE emp_cursor;
END //
DELIMITER ;
6.4触发器
暂时没用到,后续补充
7、索引优化与查询优化
https://blog.csdn.net/IAMLSL/article/details/122853040?spm=1001.2014.3001.5506
8、Mysql主从复制
主从复制允许将来自一个mysql数据库（主服务器）的数据复制到一个或多个mysql数据库服务器（从服务器）
（1）主节点配置
上图中192.168.206.128为主节点
[root@caochenlei ~]# vi /usr/my.cnf
在[mysqld]下一行添加以下信息
#日志名称
log-bin=mysql-bin
#日志格式：row、statement、mixed
binlog-format=row
#服务器ID标识
server-id=1
#刷写事务日志
innodb_flush_log_at_trx_commit=1
#执行写入1次同步1次
sync_binlog=1
#及时更新master_info
sync_master_info=1
重启：
[root@caochenlei ~]# service mysql restart
Shutting down MySQL... [确定]
Starting MySQL..... [确定]
连接：
[root@caochenlei ~]# mysql -uroot -p123456
授权：
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.03 sec)
刷新：
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
查看：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 397
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
(2)从节点
192.168.206.129为从节点
[root@caochenlei ~]# vi /usr/my.cnf
在[mysqld]下一行添加以下信息
#日志名称
log-bin=mysql-bin
#日志格式：row、statement、mixed
binlog-format=row
#服务器ID标识
server-id=2
#是否只读
read_only=1
停止：
[root@caochenlei ~]# service mysql stop
Shutting down MySQL.. [确定]
删除：
这里不能直接重启mysql是因为它们的auto.cnf中的server-uuid是一样的，在进行接下来的配置的时候会失败，所以要删除从库的server-uuid，让它在启动的时候自动生成一个全新的server-uuid
[root@caochenlei ~]# rm -f /var/lib/mysql/auto.cnf
启动：
[root@caochenlei ~]# service mysql start
Starting MySQL... [确定]
连接：
[root@caochenlei ~]# mysql -uroot -p123456
执行：
注意：MASTER_LOG_FILE=show master status的File、MASTER_LOG_POS=show master status的Position
CHANGE MASTER TO
MASTER_HOST='192.168.206.128',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=397;
开启
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
查看
注意：重点检查Master_Log_File、Read_Master_Log_Pos是否和主节点的信息一致，Slave_IO_Running、Slave_SQL_Running是否为YES
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.206.128
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 397
Relay_Log_File: caochenlei-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 397
Relay_Log_Space: 461
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d7200ffb-e698-11ea-87e0-000c29e99cce
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.02 sec)
(3)连接测试
测试连接：192.168.206.128（master）
创建数据库：
CREATE DATABASE `mytest` CHARACTER SET utf8 ;
创建数据表：
CREATE TABLE `mytest`.`myuser` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`username` VARCHAR (20) NOT NULL,
`password` VARCHAR (20) NOT NULL,
PRIMARY KEY (`id`)
) ;
添加数据
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('1', 'zhangsan', '123456');
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('2', 'lisi', '123456');
INSERT INTO `mytest`.`myuser` (`id`, `username`, `password`) VALUES ('3', 'wangwu', '123456');
连接测试：192.168.206.129（slave）
USE mytest;
SELECT * FROM myuser;
我们会发现我们只在192.168.206.128（master）创建数据库、创建表、添加数据，而它的从节点会自动的拉取主节点的内容，这就是所谓的主从复制了。
