索引

索引是在MySQL的存储引擎层中实现的。 索引：B+树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引，统称为索引。

索引的数据结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下:

• 树中每个节点最多包含m个孩子;
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子;
• 若根节点不是叶子节点，则至少有两个孩子;
• 所有的叶子节点都在同一层;
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1。(key是节点最大容量，超过m-1中间节点分裂到父节点，两边节点分裂)

举例5叉BTree：CNGAHEKQMFWLTZDPRXYS。2<=n<=4

B+Tree为BTree的变种，B+Tree与BTree的区别为：【查询效率更加稳定】

• n叉B+Tree最多含有n个key，而BTree最多含有n-1个key；
• B+Tree的叶子节点保存所有的key信息，依key大小顺序排列；
• 所有的非叶子节点都可以看作是key的索引部分

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

索引分类与语法

• 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引；
• 唯一索引：索引列的值必须唯一，但允许有空值；
• 复合索引：即一个索引包含多个列

# 创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[USING index_type]
ON tb1_name(index_col_name, ...)

index_col_name:column_name[(length)][ASC/DESC]

# 查看索引
show index from table_name\G;

# 删除索引
DROP INDEX index_name ON tb1_name;

# alter命令
-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table tb_name add primary key(column_list);

-- 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)
alter table tb_name add unique index_name(column_list);

-- 添加普通索引，索引值可以出现多次
alter table tb_name add index index_name(column_list);

-- 该语句指定了索引为FULLTEXT，用于全文索引
alter table tb_name add fulltext index_name(column_list);

索引设计原则

• 对查询频次较高，且数据量比较大的表建立索引；
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合；
• 使用唯一索引，区分度越高，使用索引的效率越高；
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价高(DML操作效率降低)；
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率；
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

索引的使用

避免使用select * 查询，会导致走索引后进行回表查询。
如果MySQL评估使用索引比全表更慢，不使用索引。
某列相同的数据过多，就不会走索引，直接全表查询。
单列索引，数据库会找到最优的(辨识度最高的)某个单列索引，这个选择方式的算法是？

索引失效：

• 复合索引非最左匹配原则
• 使用函数(substring...)
• 表达式计算(>...)
• 类型转换(varchar->int...)
• 模糊匹配(like)-覆盖索引？解决
• where子句中出现or/is not null/not in...

查看索引的使用情况： show [global] status like 'Handler_read%';

视图

视图(View)是一种虚拟存在的表。视图就是一条SELECT语句执行后返回的结果集。

# 创建视图的语法
CREATE [OR REPLACE][ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
As select_statement
[WITH [CASCADED / LOCAL ] CHECK OPTION]

# 修改视图的语法
ALTER [ALGORITHM = {UNDEF工NED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
As select_statement
[wITH [CASCADED / LOCAL] CHECK OPTION]

# 选项说明
WITH[ [CASCADED / LOCAL] CHECK OPTION ] 决定了是否允许更新数据使记录不再满足视图的条件。
LOCAL：只要满足本视图的条件就可以更新。
CASCADED：必须满足所有针对该视图的所有视图的条件才可以更新。

# 查看视图
show create view view_name

# 删除视图
DROP VIEW [IF EXIST5] view_name [，view_name] ... [RESTRICT | CASCADE]

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，减少数据在数据库和应用服务器之间的传输，存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

# 创建存储过程
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin
    -- SQL语句
end;

# 将SQL语句以$为结束符
delimiter $

# 调用存储过程
call procedure_name();

# 查看存储过程
-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db= 'db_name';
-- 查询存储过程的状态信息
show procedure status;
-- 查询存储过程的定义
show create procedure procedure_name \G;

# 删除存储过程
DROP PROCEDURE [IF EXISTS] procedure_name;

-- 申明变量
DECLARE var_name[,...] type [DEFAULT value];

-- 赋值
SET var_name = expr [, var_name = expr] ... ;
select ... into var_name from ... ;

-- if语句
if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_list]
end if; 

-- 参数传递
create procedure procedure_name([in/out/inout] 参数名 参数类型)
IN：该参数可以作为输入，也就是需要调用方传入值，默认
OUT：该参数作为输出，也就是该参数可以作为返回值
INOUT：既可以作为输入参数，也可以作为输出参数

call procedure_name(@[out参数名]);
select @[out参数名];
小知识
@[out参数名]：这种变量要在变量名称前面加上"@"符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@XXX：这种在变量前加上"@@”符号,叫做系统变量。

-- case
-- 方式一:
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] 
    ...
    [ELSE statement_list]
END CASE
-- 方式二:
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] 
    ...
    [ELSE statement_list]
END CASE;

-- while循环
while search_condition do
    statement_list
end while;
-- repeat循环
REPEAT
    statement_1ist
    UNTIL search_condition
END REPEAT;

-- loop ... leave

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE。

- 声明光标:
DECLARE cursor_name CURSOR FOR select_statement ;
- OPEN光标:
OPEN cursor_name ;
- FETCH光标:
FETCH cursor_name INTO var_name [, var_name] ...
- CLOSE光标:
CLOSE cursor_name ;

-- 句柄机制，假如NOT FOUND 设置变量has_data值为1 并且退出EXIT
DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0 ;

存储函数

CREATE FUNCTTON function_name([param type ... ]
RETURNS type
BEGIN
    ...
END;

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW和OLD的使用
INSERT型触发器	NEW表示将要或者已经新增的数据
UPDATE 型触发器	OLD表示修改之前的数据，NEW表示将要或已经修改后的数据
DELETE 型触发器	OLD表示将要或者已经删除的数据

create trigger trigger_name
before/after insert/update/ delete
on tbl_name
[for each row] --行级触发器
begin
  trigger _stmt;
end

删除触发器语法结构：

drop trigger [schema_name.] trigger_name
-- 如果没有指定schema_name，默认为当前数据库。

查看触发器：

-- 可以通过执行SHOW TRIGGERS命令查看触发器的状态、语法等信息。语法结构﹔
show triggers;

存储引擎

InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

show engines;
InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys

-- 事务
start transaction;
commit;

-- 外键foreign key
ON DELETE RESTRICT # 删除主表数据时，如果有关联记录，不删除﹔
ON UPDATE CASCADE  # 更新主表时，如果子表有关联记录，更新字表记录;

表结构仍然存在.frm文件中，每个表的数据和索引单独保存在.ibd中

MyISAM

MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。

.frm (存储表定义)、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)

SQL优化

# 1.SQL查询次数
-- 查询当前连接的命令次数
show status like 'Com_______';
-- 查询所有的命令次数
show global status like 'Com_______';
-- Innodb相关查询次数
show global status like 'Innodb_rows_%';

# 2.SQL执行效率低下
-- 慢查询日志
-- 查看所有连接当前语句执行信息
show processlist;

# 3.explain分析执行计划

• id：id相同表示加载表的顺序是从上到下；id不同id值越大，优先级越高，越先被执行。
• select_type：从上到下效率越来越低，查看表的嵌套结构。
• type：一般来说，我们需要保证查询至少达到range级别，最好达到ref。
• exrea：using filesort、using temporary、using index(性能最好)。

# 4.时间耗费地方
-- 是否支持这个工具
select @@have_profiling;
-- 是否开启
select @@profiling;
-- 开启
set profiling=1;

show profiles; -- Query_ID Duration Query 
show profiles for query query_id; -- 某query_id各个阶段耗时情况 Status Duration

# 5.trace 优化器执行计划
SET optimizer__trace="enabled=on" ,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace\G;

大量数据导入
当使用load命令导入数据的时候，适当的设置可以提高导入的效率。(最好主键有序，以逗号分割每字段)

load data local infile "/root/sql1.log' into table 'tb_user_1' fields terminated by ',' lines terminated by '\n';
-- 关闭唯一性校验
SET UNIQUE_CHECKS = 0;
-- 手动提交事务
SET AUTOCOMMIT = O;

优化insert语句
使用多条插入语句

-- 在事务中进行数据插入
start transaction;
...
commit;

order by排序

• 第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
• 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index排序，不需要额外排序，操作效率高。

了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where条件和Order by使用相同的索引，并且Order by的顺序和索引顺序相同，并且Order by的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

MySQL有两种排序算法：

• 两次扫描算法：MySQL4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buffer中排序，如果sort buffer不够，则在临时表temporary table中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机/O操作。
• 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定是否那种排序算法。如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高sort_buffer_size和max_length_for_sort_dlata系统变量，来增大排序区的大小，提高排序的效率。

group by分组
分组查询语句后面加order by null

• 子查询用连接查询(多表联查)替换
• or不能用复合索引，or连接各字段都用单列索引，union替换or
• ①在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。②主键自增的表，可以把limit查询转换成某个位置的查询。
• sql提示(USE INDEX提供参考/IGNORE INDEX忽略某个/FORCE INDEX强制使用)

应用优化

• 数据库连接池
• 减少对MySQL的访问(避免对数据进行重复检索/增加cache层)
• 负载均衡

查询缓存(mysql8.0取消)

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

-- 查看当前的MySQL数据库是否支持查询缓存
SHOW VARIABLES LIKE 'have_query_cache';
-- 查看当前MySQL是否开启了查询缓存
SHOW VARIABLES LIKE 'query_cache_type';
-- 查看查询缓存的占用大小
SHOW VARIABLES LIKE 'query_cache_size';
-- 查看查询缓存的状态变量
SHOW STATUS LIKE 'Qcache%';

-- SQL_CACHE/SQL_NO_CACHE
SELECT SQL_CACHE id,name FROM customer ;

如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变INSERT，UPDATE，DELETE，TRUNCATE TABLE，ALTER TABLE，DROP TABLE，或DROP DATABASE。

MyISAM内存优化
myisam存储引擎使用key_buffer缓存索引块，加速myisam索引的读写速度.赖于操作系统的IO缓存。

SHOW VARIABLES LIKE 'key_buffer_size';
-- read_buffer_size
-- read_rnd_buffer_size

InnoDB内存优化
innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

-- innodb_buffer_pool_size
-- innodb_log_buffer_size

锁

从对数据操作的粒度分：
1）表锁：操作时，会锁定整个表。
2）行锁：操作时，会锁定当前操作行。

从对数据操作的类型分∶
1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
2）写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

# MyISAM (读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写)
加读锁：lock table table_name read; --阻塞其他线程的写操作
加写锁：1ock table table_name write ; --阻塞其他线程的读写操作
unlock tables;  --关锁

-- 查看锁的征用情况
show open tables;

-- Table_locks_immediate：指的是能够立即获得表级锁的次数，每立即获取锁，值加1。
-- Teable_locks_waited：指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。
show status like 'Table_locks%';

事务是由一组SQL语句组成的逻辑处理单元。事务具有以下4个特性,简称为事务ACID属性。

出现问题

隔离级别 备注∶√代表可能出现，×代表不会出现。
查看隔离级别：show variables like 'tx_isolation';

InnoDB实现了以下两种类型的行锁。

• 共享锁(S)：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁(X)：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句，InnoDB不会加任何锁。

# Innodb
共享锁(S)：SELECT * FROM tab1e_name WHERE ... LOCK IN SHARE MODE;
排他锁(X)：SELECT * FROM table_name WHERE ... FOR UPDATE;

set autocommit=0;  --关闭自动提交

-- InnoDB行锁争用情况
show status like 'innodb_row_lock%';

索引失效行锁升级为表锁。

间隙锁

复制

常用工具：mysqladmin/mysqlbinlog/mysqldump/mysqlimport/source/mysqlshow

错误日志、二进制日志、查询日志、慢查询日志

-- 配置文件位置:/usr/my.cnf
-- 日志存放位置:配置时，给定了文件名但是没有指定路径，日志默认写入Mysql的数据目录。
# 配置开启binlog日志，日志的文件前缀为 mysqlbin.000001 ->生成的文件名如: mysq1bin.000001,mysq1bin.000002
log_bin=mysqlbin

# 配置二进制日志的格式 STATEMENT/ROW/MIXED
binlog_format=STATEMENT

STATEMENT
该日志格式在日志文件中记录的都是SQL语句( statement )，每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库( slave )会将日志解析为原文本，并在从库重新执行一次。
Row
该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句: update tb_book set status=1',如果是STATEMENT日志格式，在日志中会记录一行SQL文件;如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更。
MIXED
这是目前MySQL默认的日志格式，即混合了STATEMENT和ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED格式能尽量利用两种模式的优点，而避开他们的缺点。

# STATEMENT
mysqlbin.index:该文件是日志索引文件，记录日志的文件名;
mysqlbing.000001:日志文件

# Row
mysqlbinlog -vv mysqlbin.000002

# 删除二进制日志(四种方式)
mysql> Reset Master;
mysql> purge master logs to 'mysabin.xxxxxx";
mysql> purge master logs before 'yyyy-mm-dd hh:mm:ss';
设置参数(此参数的含义是设置日志的过期天数)：--expire_logs_days=# 

复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行(也叫重做），从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。

从上层来看，复制分成三步：

• Master主库在事务提交时，会把数据变更作为时间Events记录在二进制日志文件 Binlog中；
• 主库推送二进制日志文件Binlog 中的日志事件到从库的中继日志Relay Log；
• Slave重做中继日志中的事件，将改变反映它自己的数据。

参考资料

[1] MySQL高级进阶课程
[2] MySQL体系结构