MySql高级视频笔记

索引

索引 : 是帮助MySql高效查询数据的数据结构

优势&劣势

• 优势:
  1. 提高数据检索的效率, 降低数据库的IO成本
  2. 通过索引列队数据进行排序, 降低数据的排序成本, 降低CPU的消耗
• 劣势:
  1. 索引维护了主键信息, 并指向表中数据记录, 也是占用磁盘空间的
  2. 索引提高了查询效率, 但索引也不是越多越好, 增删改的时候会对索引进行维护, 会降低更新表速度

索引结构

索引是MySql的存储引擎层实现的, 而不是在服务器层实现的

MySql 目前提供了以下4种规则引擎:

• BTREE(btree)索引: 常见的索引类型, 大部分索引都支持B树索引
• HASH(hash)索引 : 只有Memory引擎支持, 使用场景简单
• R-tree索引(空间索引) : 空间索引是MyISAM引擎的一个特殊索引类型, 主要用于地理空间数据类型, 通常使用较少
• Full-text(全文索引) : 全文索引也是MyISAM引擎的一个特殊索引类型, 主要用于全文索引, InnoDB从MySql5.6版本开始也支持全文索引

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text索引	5.6版本之后支持	支持	不支持

平常说的索引, 如果没有特别指明, 都是指B+树(多路搜索树, 并不一定是二叉)结构组织的索引. 其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引, 统称为索引.

BTREE结构

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

• 树中每个节点最多包含m个孩子
• 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)]个孩子
• 若根节点不是叶子节点, 至少有俩个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点由n个key与n+1个指针组成, 其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree树为例, key的数量 : 公式推导[ceil(m/2)-1] <= n <= m-1. 所以 2 <= n <=4. 当n>4时,中间节点分裂到父节点, 俩边的节点分裂.

B+TREE结构

B+Tree为BTree的变种, B+Tree与BTree的区别为:

1. n叉B+Tree最多含有n个key. 而BTree最多含有n-1个key.
2. B+Tree的叶子节点保存所有key信息, 依key大小顺序排列.
3. 所有的非叶子节点多可以看作是key的索引部分.
   
   由于B+Tree只有叶子节点保存key信息, 查询任何key都要从root走到叶子节点. 所以B+Tree的查询效率更加稳定.

MySql中的B+Tree

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

索引分类

1. 单值索引 : 即一个索引只包含单个列, 一个表可以有多个单列索引
2. 唯一索引 : 索引列的值必须唯一, 但允许有空值(NULL可以出现多次)
3. 复合索引 : 即一个索引包含多个列

创建索引

-- 字段名为多个时,创建为复合索引
create [UNIQUE|FULLTEXT|SPATIAL] index 索引名称 [USING index_type] on 表名(字段名...);

示例: create index inx_name on student(name);

查看索引

show index from 表名;

删除索引

drop index 索引名称 on 表名;

ALTER(alter)命令

-- 添加主键
alter table 表名 add primary key(字段名);
-- 添加唯一主键(NULL可以出现多次)
alter table 表名 add unique 索引名称(字段名);
-- 添加普通索引
alter table 表名 add index 索引名称(字段名);
-- 指定索引为FULLTEXT,用于全文索引
alter table 表名 add fulltext 索引名称(字段名);

索引设计原则

索引的设计可以遵循一些已有的规则, 创建索引的时候请尽量考虑符合这些原则, 便于提升索引的使用效率, 更高效的使用索引.

• 对查询频次较高, 且数据量比较大的表建立索引.
• 索引字段的选择, 最佳候选列应当从where子句(查询条件)中提取, 如果where子句的组合比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
• 使用唯一索引, 区分度越高, 使用索引的效率越高.
• 索引可以有效的提升查询数据的效率, 但索引的数量不是越多越好, 索引越多, 增删时维护索引难度也水涨船高
• 使用短索引, 索引创建之后也是使用硬盘来存储的, 因此提升索引访问的I/O效率, 也可以提升总体的访问效率. 假如构成索引的字段总长度比较短, name在给定大小的存储块内可以存储更多的索引值, 相应的可以有效的提升MySql访问索引的I/O效率.
• 利用最左前缀, N个列组合而成的组合索引, name相当于创建了N个索引, 如果查询时where子句中使用了组合索引的前几个字段, name这条查询SQL可以利用组合索引来提升查询效率.

视图

视图(View) 是一种虚拟存在的表. 视图在数据库中并不是真实存在的, 行和列的数据来源自定义查询中的表, 并在使用视图时动态生成. 通俗的讲, 视图就是一条SELECT语句执行返回的结果集. 所以创建视图时, 重点是创建这条SQL的查询语句上.

• 简单 : 不需要关注视图背后对应的表结构、关联条件和筛查条件, 对使用视图来说视图已经是过滤好的复合条件的结果集.
• 安全 : 使用视图只允许访问视图返回的结果集, 对表的权限管理可以限制到某行某列.
• 数据独立 : 视图一旦查询语句确定, 源表结构变化对视图没有影响, 源表修改列名, 修改视图便好, 不会对源表有影响.

创建视图

create [or replace] [algorithm = {undefined | merge | temptable}]
view 视图名称[(column_list)]
as 查询语句
[with [cascaded | local] check option]

修改视图

alter [algorithm = {undefined | merge | temptable}]
view 视图名称[(column_list)]
as 查询语句
[with [cascaded | local] check option]

选项说明

with [cascaded | local] check option 决定了是否允许更新数据使记录不再满足视图的条件
local : 只要满足视图条件就可以更新
cascaded(默认值) : 必须满足所有针对该视图的所有视图条件才可以更新.

删除视图

drop view [if exists] 视图名称...[restrict | cascade]

存储过程和存储函数

概述

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句集合, 调用存储过程和函数可以简化开发, 减少数据库和应用之间的传输, 对于提高数据处理效率有一定的好处.
存储过程和函数的区别在于函数必须哟返回值,而存储过程没有.
函数 : 是一个有返回值得过程
过程 : 是一个没有返回值的函数

创建存储过程

create procedure 存储过程名称([proc_parameter(...)])
begin
	-- SQL语句
end;

创建存储过程中使用SQL语句分隔符,可能会报错,所以使用delimiter关键字修改SQL分隔符

调用存储过程

call 存储过程名称();

查看存储过程

-- 查询数据库中的所有存储过程
select name from mysql.proc where db='数据库名称'
-- 查询存储过程的状态信息
show procedure status;
-- 查询某个存储过程的定义
show create procedure 数据库名.存储过程名称;

删除存储过程

drop procedure [if exists] 存储过程名称;

语法

存储过程是可以编程的, 意味着可以使用变量、表达式、控制结构来完成比较复杂的功能.

变量DECLARE

• DELARE
  通过declare可以定义一个局部变量, 该变量作用范围只能在begin…end块中.

declare 变量名称[...] 变量类型 [default 默认值]

示例 :

create 存储名称()
begin
	declare a int default 3;
	select a + 10;
end;

• SET

直接赋值用SET, 可以赋常量活表达式

set 变量名 = 值[...]

示例 :

create procedure 存储过程名称()
begin
	declare a int;
	set a = 10;
	select a;
end;

• select … into 赋值

create procedure 存储过程名称()
begin
	declare a int;
	select count(id) into a from 数据库表;
	select a;
end;

if条件判断

if 条件语句 then 业务逻辑
[elseif 判断语句 then 业务逻辑] ...
[else 业务逻辑]
end if;

示例 :

create procedure 存储过程名称()
begin
	declare a int default 175;
	declare b varchar(32) default '';
	if a >= 180 then
		set b = '高';
	elseif a >= 170 and a < 180 then
		set b = '中'
	else
		set b = '低'
	end if;
end; 

传递参数

create procedure 存储过程名称([in/out/inout] 参数名 参数类型)
...
in : 该参数作为输入参数,默认
out: 该参数作为输出参数
inout: 既可以作为输入参数,也可以作为输出参数

in-输入参数

示例 :

create procedure 存储过程名称(in a int)
begin
	declare b varchar(10) default '';
	if a >= 180 then
		set b = '高';
	elseif a >= 170 and a < 180 then
		set b = '中';
	else
		set b = '低';
	end if;
end;

out-输出参数

示例 :

create procedure 存储过程名称(in a int,out b varchar(8))
begin
	if a >= 180 then
		set b = '高';
	elseif a >=170 and a< 180 then
		set b = '中';
	else
		set b = '低';
	end if;
end;

调用:

call 存储过程名称(168,@b);
select @b;

@变量名称 : 这种变量前面加上"@" 符,叫用户会话变量, 代表整个会话过程都有作用,类似全局变量
@@变量名称 : 这种变量前加"@@"符, 叫系统变量

case结构

-- 方式一:
case 变量
	when 判断值的判断语句 then 为true执行业务逻辑
	[when 判断值的判断语句 then 为true执行业务逻辑] 
	...
	[else 上面条件全部不满足执行的业务逻辑]
end case;
-- 方式二:
case
	when 条件判断语句 then 为true的业务逻辑
	[when 条件判断语句 then 为true的业务逻辑]
	...
	[else 上面条件不满足所执行的业务逻辑]
end case;

示例 :

create procedure 存储过程名称(mon int)
begin
	declare result varchar(8);
	case
		when mon > 0 and mon <= 3 then
			set result = '一';
		when mon > 3 and mon <= 6 then
			set result = '二';
		when mon > 6 and mon <= 9 then
			set reuslt = '三';
		else 
			set result = '四';
	end case;
end;

while循环

while 判断条件 do
	循环体
end while;

示例:

create procedure 存储过程名称(n int)
begin
	declare total int default 0;
	declare num int default 1;
	while num <= n do
		set total = total + num;
		set num = num + 1;
	end while;
end;

repeat循环

有条件的循环控制语句, 当满足条件时退出循环. while满足条件才执行, repeat是满足条件才退出

repeat
	循环体
unitl 退出循环条件(这里不加sql语句结束符)
end repeat;

示例 :

create procedure 存储过程名称(n int)
begin
	declare total int default 0;
	repeat
		set total = total + n;
		set n = n - 1;
		until n = 0
	end repeat;
end;

loop循环

loop实现简单的循环, 退出循环的条件需要其他的语句定义, 通常使用leave语句实现

[loop别名:]loop
	循环体
end loop [loop别名]

leave语句

示例:

create procedure 存储过程名称(n int)
begin
	declare total int default 0;
	c:loop
		set total = total + n;
		set n = n - 1;
		if n <= 0 then
			leave c;
		end if;
	end loop c;
end;

游标/光标

游标是用来存储查询结果集的数据类型, 再存储过程和函数可以使用光标对结果集进行循环处理.
声明光标:

declare 光标名称 cursor for 查询语句;

open(打开)光标:

open 光标名称;

fetch(遍历)光标:

fetch 光标名称 into var_name [,var_name]...

close(关闭)光标:

close 光标名称;

示例:

create procedure 存储过程名称()
begin
	declare e_id int(11);
	declare e_name varchar(32);
	declare e_age int(11);
	declare e_salary int(11);
	
	declare has_data int default 1;
	
	declare 光标名称 cursor for select * from a;
	declare exit handler for not found set has_data = 0;
	
	open 光标名称;
	
	repeat
		fetch 光标名称 into e_id,e_name,e_age,e_salary;
		select concat(e_id,e_name,e_age,e_salary);
		until has_data = 0
	end repeat;

	close 光标名称;
end;

存储函数

create function 存储函数名称([变量名 变量类型...])
returns 返回值类型
begin
	...
end;

示例:

create function 函数名称(a int)
returns int
begin
	declare num int;
	select count(id) into num from city where country_id = a;
	return num;
end;

触发器

触发器是与表有关的数据库对象, 指在insert/update/delete之前或之后, 触发并执行触发器中定义的sql语句集合.

使用别名OLD和NEW来引用触发器中发生变化的记录内容.

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

创建触发器

create trigger 触发器名称
before/after insert/update/delete
on 表名
[for each row]
begin
	... -- 获取新数据 new.id 获取旧数据lod.id
end;

查看触发器

show triggers;

删除触发器

drop trigger [数据库名.]触发器名称;

MySQL体系结构

整个MySql Server由以下组成:

• Connection Pool : 连接池组件(认证、连接最大数、缓存的一些设置)
• Management Services & Utilities: 管理服务和工具组件(数据备份恢复、数据安全、系统配置等)
• SQL Interface : Sql接口组件(sql语句CRUD、视图、存储过程、函数等)
• Parser : 查询分析器组件(分析过滤Sql语句)
• Optimizer : 优化器组件(MySql内部对解析后的sql进行优化)
• Caches & Buffers : 缓冲池组件(看当前sql语句是否有缓存数据,有直接返回缓存数据,没有继续往下走)
• Pluggable Storage Engines : 存储引擎(根据不同业务设置不同存储引擎)
• File System : 文件系统(具体数据存放位置)

1. 连接层
   最上层是一些客户端和链接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似与连接处理、授权认证及相关的安全方案。在该层上引入线程池的概念，为通过认证安全接入的客户端提供线程。同样再该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2. 服务层
   第二层架构主要完成大多数的核心服务功能，如Sql接口，完成缓存的查询，Sql分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这层实现（如过程、函数等）。服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化（如确定表的查询顺序、是否利用索引等）。最后生成相应的执行操作
3. 引擎层
   存储引擎负责了MySql中数据的存储和提取，服务器通过API和存储引擎进行通信。
4. 存储层
   主要将数据存储到文件系统上，并完成与存储引擎的交互

和其他数据库相比,MySql有点与众不同,MySql插件式存储引擎架构可以在多种不同场景中应用并发挥良好作用. 将查询处理和其他的系统任务及数据的存储提取分离. 这种架构可以根据业务的需求和实际需要选择合适的存储引擎

存储引擎

概述

存储引擎就是存储数据, 建立索引, 更新查询数据等技术的实现方式. 存储引擎是基于 表 的,而不是基于库的.所以存储引擎也可被称为表类型

Oracle，SqlServer等数据库只有一种存储引擎。MySql提供了插件式的存储引擎架构。

MySql5.0支持的存储引擎包含 : InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表
可以通过指定show engines,查询当前数据库支持的存储引擎:

通过指令show variables like '%storage_engine%', 查看当前服务器默认存储引擎

存储引擎特性

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁(支持高并发)	表锁	表锁	表锁	表锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

InnoDB

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

InnoDB存储引擎不同于其他存储引擎的特点 ：
事务控制

外键约束
MySql支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，指标在创建外键的时候，也会自动创建对应的索引。
存储方式
InnoDB 存储表和索引有俩种方式：

1. 使用共享表空间存储，这种方式创建的表的表结构保存再.frm文件中，数据和索引保存再innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件
2. 使用多表空间存储，这种创建的表的表结构存在.frm文件中，但表的数据和索引单独保存再.ibd文件中。

MyISAM

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

不支持事务
文件存储方式
每个MyISAM再磁盘上存储成3个文件，其文件名和表名相同，扩展名分别为：

• .frm（表结构）
• .MYD（MYData，存储数据）
• .MYI（MYIndex，存储索引）

MEMOEY（Memory）

 Memory存储引擎将表的数据存放到内存中。每个Memory表实际对应一个磁盘文件（.frm），该文件只存储表结构，数据文件存储到内存中，有利于数据的快速处理，提高整个表的效率。
 Memory类型的表默认使用HASH索引，因为数据存放在内存中，所以服务器一旦关闭，数据就会丢失。

MERGE（Merge）

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，Merge表本身并没有存储数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际都是对内部的MyISAM表进行的。
对于Merge类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使插入操作被相应的作用在第一或最后一个表上，不定义这个子句或者定义为NO，表示不能对这个Merge表执行插入操作。
可以对Merge表进行drop操作，但这个操作只是删除Merge表的定义，对内部的表是没有任何影响的。

存储引擎的选择

在选择存储引擎时，根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，根据实际情况选择多种存储引擎进行组合。

• InnoDB：是MySql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用多事务的完整性有较高要求，再并发调价下要求数据一致性，数据操作出来插入和查询以外，还有很多更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并对事务的完整性，并发要求不是很高，选择这个存储引擎是非常合适的。
• MEMORY（Memory）：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供快速的访问。Memory的缺陷是对表的大小有限制，太大的表无法缓存到内存中，其次要确保表的数据可以恢复，数据库异常终止后表中数据是可以恢复的。Memory表通常用于更新不太频繁的小表，用以快速得到访问结果。
• MERGE（Merge）：用于将一系列等同的MyISAM表已逻辑方式组合在一起，并作为一个对象引用他们。Merge表的优点在于突破对单个MyISAM表的大小限制，将不同的表分布在多个磁盘上，有效改善Merge表的访问效率。对于存储如数据仓储等VLDB环境十分合适。

SQL优化

查看SQL执行频率

MySql客户端连接成功后，通过show [session|global] status 命令提供服务器状态信息。show [session|global] status 可以根据需要加上参数session或global来显示session级(当前连接)的统计结果和global级(自数据库上次启动至今)的统计结果。默认使用session。
显示当前session中所有统计参数的值：

show status like 'Com_______'; -- 下划线7个

show status like 'Innodb_rows_%';

定位低效率执行SQL

• 慢查询日志：通过慢查询日志定位那些执行效率低的SQL语句，用--log-slow-queries[=file_name]选项启动时，mySqld写一个包含所有执行时间超过long_query_time秒的Sql语句的日志文件
• show processlist ：慢查询日志在查询结束之后才记录，所以在应用反映执行效率出现问题的时候查询日志不能定位问题，可以使用show processlist命令查看当前MySql在进行的线程，包括线程状态、是否锁表等，可以实时查看Sql的执行情况，同时对锁表操作进行优化。
  

列	说明
ID	用户登录MySql时，系统分配的‘connection_id’，可以使用函数connecton_id()查看
User	显示当前用户，如果不是root，这个命令只显示用户权限范围的sql语句
Host	显示ip地址和端口，可以用来跟踪出现问题语句的客户
db	显示连接的那个数据库
Command	显示当前连接执行的命令，一般值为休眠(sleep)、查询(query)、连接(connect)等
Time	状态持续时间(单位秒)
State	显示当前连接的Sql语句状态,已查询为例(可能需要copyiing to tpm table、sorting result、sending data等才能完成)
info	显示Sql语句，判断问题的重要依据

EXPLAIN(explain)分析执行计划

通过EXPLAIN或者DESC命令获取MySql如何执行的select语句信息，包括select语句执行过程中表如何连接及连接顺序。

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的顺序
select_type	表示select类型，常见的取值有SLMPLE(简单表，即不使用表连接或子查询)、PRIMARY(主查询，即外层查询)、UNION(UNION中第二个或者后面的查询语句)、SUBUERY(子查询中第一个select)等
table	输出结果集的表
type	表的连接类型，性能由好到差的连接类型为(system -> const -> eq_ref ->ref_or_null -> index_merge -> index_subquery -> range -> index -> all)
possible_keys	查询时, 可能使用的索引
key	实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

ID列

id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者操作表的顺序。

• id相同表示加载表的顺序是从上到下
• id不同，值越大，优先级越高，越优先被执行
• id有相同，也有不同，同时存在。id相同的可以看成一组，从上到下顺序执行；所有组中，值越大，优先级越高，越先执行

select_type列

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在select或where列表中包含子查询
EDRIVED	在from列表中包含的子查询，被标记为DERIVED(衍生)MySql会递归执行子查询，吧结果放到临时表中
UNION	若第二个select出现在UNION之后，则标记为UNION；若UNION包含from子句的子查询中，外层select将标记为：DERIVED
UNION RESILT	从UNION表获取结果的select

table列

展示这行的数据关于哪张表的

type列

type显示的是访问类型，是最重要的一个指标

type	含义
NULL	MySql不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就能找到，const用于比较primary key或unique索引。因为只匹配一行数据，所以很快。如：将主键置于where列表中，MySql就能将该查询转换为一个常量。const将“主键”或“唯一”索引的所有部分与常量进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独的所有行。本质上也是一种索引访问，返回所有匹配某个单独值得所有行
range（MySql查询的最低标准）	只检索给定返回的行，使用一个索引来选择行。where之后出现的between、<、>、in等操作
index	index与all的区别为 index类型只遍历索引，比all快，all是遍历所有数据
all	将遍历全表以找到匹配的行

结果值从最好到最坏依次是：

NUll > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > All

system > const > eq_ref > ref > range > index > all

多个key列

possible_keys：显示可能应用这张表的索引，一个或多个
key：实际使用的索引，如果为null，则没用使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

rows列

扫描行的数量

extra列

其他的额外执行计划信息

extra	含义
using filesort	mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序读取，称为“文件排序”；效率低
using temporary	使用了临时表保存中间结果，mysql对查询结果排序时使用临时表。常见order by和group by；效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行；效率一般
using where	在查找是哦用索引的情况下, 需要回表去查询所需数据
using index condition	查找使用了索引, 需要回表查询数据
using index; using where	查询使用了索引,需要的数据再索引列中, 无需回表查询数据

show profile 分析sql

MySql 从5.0.37版本开始增加了对show profiles和show profile语句支持。

通过have_profiling参数,看到当前MySql是否支持profile
select @@have_profiling

默认profiling是关闭的，可以通过set语句在session级别开启profling
select @@profiling

set profiling = 1; -- 开启profiling开关

执行show profiles命令,查看sql语句执行耗时

通过show profile for query query_id语句查看该sql执行过程中每个线程的状态和消耗时间

TIP : 
	Sending data 状态表示MySql线程开始访问数据并把数据返回客户端，而不是仅仅是返回到客户端。由于在Sending data状态下，MySql线程往往需要大量的磁盘读取操作，所以经常是整个查询中最耗时的状态。

在获取到最消耗时间的线程状态后，MySql支持进一步选择all、cpu、block io、context weitch、page faults等明细类型查看MySql在使用什么资源上耗费了过高的时间。

trace分析优化器执行计划

MySql5.6提供了对sql的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划,而不是B计划.
打卡trace, 设置格式为JSON,并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能够完整展示.

 set optimizer_trace = 'enabled=on',end_markers_in_json = on;
 set optimizer_trace_max_mem_size = 1000;

执行Sql语句
检查information_schema.optimizer_trace就可以知道MySql是如何执行sql的

select * from information_schema.optimizer_trace\G;

索引的使用

索引是数据库最常用也是最重要的手段之一, 通过索引可以解决大多数MySql的性能优化问题.

• 最左前缀法则
  多列建索引,要遵守最左前缀法则(查询从索引的最左前列开始,并不跳过索引中的列).
• 范围查询右边的列, 不能使用索引.
• 不要唉索引列上进行运算操作, 索引将失效.
• varchar类型字符串不加单引号, 索引将失效.
• 尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)), 避免select *
  explain工具 sql语句 extra 列具体说明
• 用or分隔开的条件,如果or前的条件中的列有索引, 后面的列没索引, 那么涉及的索引列中的索引不会被用到(有一列没建索引,那么用or连接是不走索引的)
• 以%开头的like模糊查询,索引失效(以%结尾的模糊查询,索引不会失效)
  通过覆盖索引来解决(返回列为索引列)
• 如果MySql评估全表比使用索引更快,则不使用索引(当某一列创建了索引,且某值占表的比例很大,走全表扫描比走索引查询更快)
• is null 、is not null 有时索引失效(看表中数据某列null值占比例偏大,is null时不走索引,is not null 走索引。反之亦然)
• in走索引，not in索引失效
• 单列索引(当查询条件多个时,每列都创建一个索引,数据库会根据辨识度最高的索引来使用,而不是使用全部索引)和复合索引(遵守最左前缀法则,多个列同时在索引范围内会使用复合索引查询效率更高)

查看索引的使用情况

show status like 'Handler_read%';

show global status like 'Handler_read%';

列名	说明
Handler_read_first(这个值越低越好)	索引中第一条被读的次数, 如果较高,表示服务器正执行大量全索引扫描
Handler_read_key(这个值越高越好)	如果这个索引正在工作,这个值代表一个行被索引值读的次数. 如果值越低,表示索引得到的性能改善不高, 因为索引不经常使用
Handler_read_last
Handler_read_next	按照键顺序读下一行的请求数. 如果你用范围约束或如果执行索引扫描查询索引列, 该值增加.
Handler_read_prev	按照顺序读前一行的请求数. 该方法主要用于优化order by … desc
Handler_read_rnd	按照键顺序读前一行的请求数. 如果你正执行大量查询并需要对结果进行排序该值较高. 你可能使用了大量需要MySql扫描整个表的查询或你的连接没有正确使用键. 这个值较高,意味着运行效率低, 应该建立索引补救
Handler_read_rnd_next	在数据文件中读下一行的请求数. 如果你正在进行大量的表扫描…该值越高, 说明表索引不正确或写入的查询

SQL优化

大批量插入数据

使用load命令导入数据时,适当的设置可以提高导入效率

load data local infile '文件路径/文件名.文件格式' into table `表名` fields terminated by ',' lines terminated by '\n';
-- local infile 本地文件系统
-- terminated by ','  字段分隔符
-- terminated by '\n' 行分隔符

对于InnoDB类型的表, 有以下几种方式可以提高导入效率

1. 主键顺序插入
   因为InnoDB表是按照主键的顺序保存的, 所以将导入的数据按照主键的顺序排列, 可以有效的提高导入数据的效率. 如果InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主键, 所以如果可以给表创建一个主键,将可以利用这点, 来提高导入数据的效率.
2. 关闭唯一性校验
   在导入数据前执行set unique_checks = 0, 关闭唯一性校验, 在导入结束后执行set unique_checks = 1, 恢复唯一性校验, 可提高导入效率
3. 手动提交事务
   如果应用使用自动提交的方式, 建议在导入前执行set autocommit = 0,关闭自动提交, 导入结束后在执行set autocommit = 1,打开自动提交,也可以提高导入效率

优化insert语句

当进行数据insert操作时, 可以考虑采用以下几种优化方案

1. 同时对一张表插入多行数据时, 尽量使用多个值表的insert语句, 可以减少客户端与数据库之间的连接/关闭等消耗. 效率比分开单个执行insert语句快
   例:

insert into 表名 values(1,'tom');
insert into 表名 values(2,'cat');
-- 优化为:
insert into 表名 values(1,'tom'),(2,'cat');

2. 在事务中进行数据插入

start transaction;
insert into 表名 values(1,'tom');
insert into 表名 values(2,'cat');
commit;

3. 数据主键有序插入

order by优化

1. 通过对返回数据进行排序, 也就是通常说的filesort排序, 所有不是通过索引直接返回排序结果的排序都叫fileSort排序(返回不含索引字段)
2. 通过有序索引顺序扫描直接返回有序数据, 这种情况即为using index(explain工具中extra列), 不需要额外排序, 操作效率高.(反回字段全部为索引字段)

FileSort的优化

通过创建合适的索引, 能够减少fileSort的出现, 但在某些情况下, 条件限制不能让fileSort消失, 那就需要加快fileSort的排序操作.

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

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

尽量减少额外的排序, 通过索引直接返回有序数据, where条件和order by 使用相同的索引, 且order by的顺序和索引顺序相同, 且order by的字段都是升序,或降序,否则可能需要额外的操作, 这样就会出现fileSort.

group by语句优化

由于group by实际上也同样会进行排序操作, 且与order by相比, group by主要多了排序后的分组操作.
查询分组且要避免排序结果的消耗, 可以执行order by null禁止排序

select id,count(id) from 表名 group by age order by null;

也可以对分组的字段添加索引,提高效率

优化OR条件

对于包含OR的查询语句, 如果要利用索引, 则OR之间的每个条件列都必须用到索引, 而且不能使用到复合索引, 如果没有索引, 则考虑增加索引
建议使用union替换or

优化分页查询

一般分页查询时, 通过创建覆盖索引能够提高性能. 当查询几百万页后的数据时, 分页查询limit查询的效率就会下降

优化思路一

在索引上完成排序、分页操作，最后根据索引列关联回原表查询所需要的其他列内容。

优化思路二

该方案只适用于主键自增的表，可以吧limit查询转换成某个位置的查询(对数据要求比较高, 主键自增且连续, 避免出现断数据的情况)
例：

select * from 表名 where id > 200000 limit 10;

使用SQL提示

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

use index

在查询语句中表名的后面, 添加user index来提供希望MySql去参考的索引列表, 就可以让MySql不再考虑其他可用的索引.

select * from 表名 use index(索引名) where 查询条件;

ignore index

在查询语句中表名后面, 添加ignore idnex来让MySql忽略一个或多个索引

select * from 表名 ignore index(索引名) where 查询条件;

force index

在查询语句中表名后面, 添加force index ,强制使MySql使用一个特定的索引

select * from 表名 force index(索引名) where 查询条件;

缓存

概述及流程

开启MySql的查询缓存, 当执行完全相同的Sql语句时, 服务器会直接从缓存中读取结果, 当数据被修改, 之前的缓存会失效, 修改频繁的表不适合做查询缓存.

1. 客户端发送一条查询给Sql服务器
2. 服务器会检查查询缓存, 如果命中了缓存, 立即返回存储在缓存中的结果, 否则进入下一阶段
3. 服务器进行sql解析, 预处理, 再由路由器生成对应的执行计划
4. 查询对应的数据文件,查询对应的结果
5. 将结果返回客户端,并将查询出的结果缓存到查询缓存中

查询缓存配置

• 查看当前MySql数据库是否支持查询缓存

  show variables like 'have_query_cache';
  

• 查看当前MySql是否开启了查询缓存

  show variables like 'query_cache_type';
  

• 查看查询缓存的占用大小(单位字节)

  show variables like 'query_cache_size';
  

• 查看查询缓存的状态变量

show status like 'Qcache%';

参数	含义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
Qcache_hits	查询缓存命中数
Qcache_inserts	添加到查询缓存的查询数
Qcache_lowmem_prunes	由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached	非缓存查询的数量(由于query_chche_type设置而无法缓存或未缓存)
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

开启查询缓存

MySql的查询缓存默认是关闭的, 需要手动配置参数query cache type, 来查询缓存.

值	含义
off/0	查询缓存功能关闭
on/1	查询缓存功能打开, select的结果符合缓存条件即会缓存, 否则不予缓存, 显示指定sql_no_cache, 不予缓存
demand/2	查询缓存功能按需进行, 显示指定sql_cache的select语句才会缓存, 其它不予缓存

在/usr/my.cnf配置中, 增加一些配置

# 开启MySql的查询缓存
query_cache_type=1

配置完毕后,重启服务即可生效
然后在命令执行sql语句进行验证, 执行一条比较耗时的sql语句, 然后再执行几次, 查看后面几次的执行时间, 获取通过查看缓存的缓存命中数, 来判定是否走查询缓存.

查询缓存select选项

可以再select语句中指定俩个与查询缓存相关的选项
sql_cache : 如果查询结果是可缓存的, 且query_cache_type系统变量的值为on或demand, 则缓存查询结果.
sql_no_cache : 服务器不使用查询缓存. 既不检查查询缓存, 也不检查是否已缓存, 也不缓存查询结果.
例:

select sql_cache * from 表名 where 条件;

select sql_no_cache * from 表名 where 条件;

查询缓存失效的情况

1. sql语句不一致时(要想命中缓存,sql要完全一致)

select count(*) from 表名;

Select count(*) from 表名;

2. 当查询条件不确定时, 不会缓存.例: now()、current_date()、curdate()、curttime()、rand()、uuid()、user()、database()。

select * from 表名 where update_time < now();

3. 不查任何表

select 'A';

4. 查询系统库表时(mysql、information_schema或performance_schema)

select * from information_schema.engines;

5. 在存储的函数, 触发器或事件的主体内执行的查询
6. 缓存的表有更改, 该表的所有高速缓存查询都将变为无效并从高速缓存中删除(包括使用MERGE映射到已更改的表的查询). 一个表可以被许多类型的语句更改(insert、update、delete、truncate table、alter table、drop table、drop database)。

MySql内存管理及优化

内存优化原则

1. 尽量将多的内存分配给MySql做缓存, 但要给操作系统和其他程序预留足够的内存
2. MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存, 所以有MyISAM表, 就要预留更多的内存给操作系统做IO缓存
3. 排序区, 连接区等缓存是分配给每个数据库回话(session)专用的, 其默认值的设置要根据最大连接数合理分配, 如果设置太大, 不仅浪费资源, 还有可能再并发高时导致物理内存耗尽.

MyISAM内存优化

MyISAM存储引擎使用key_buffer存储索引块, 加速MyISAM索引的读写速度. 对于MyISAM表的数据块, MySql没有特别的缓存机制, 完全依赖于操作系统的IO缓存.

key_buffer_size

key_buffer_size决定MyISAM索引块缓存区的大小, 直接影响到MyISAM表的存取效率. 可以在MySql参数文件中设置key_buffer_size的值, 对于一般MyISAM数据库, 建议至少将1/4可用内存分配给key_buffer_size.
在/usr/my.cnf中做以下配置

key_buffer_size=512M

read_buffer_size

如果需要经常顺序扫描MyISAM表, 可以通过增大read_buffer_size的值来改善性能, 但需要注意的是read_buffer_size是每个session独占的, 如果默认值设置太大, 就会造成内存浪费.

read_rnd_buffer_size

对于需要做排序的MySIAM表的查询, 如带有order by子句的sql. 适当增加read_rnd_buffer_size的值, 可以改善此类的sql性能, 但需要注意的是read_rnd_buffer_size是每个session独占的, 如果默认值设置太大, 就会造成内存浪费.

InnoDB内存优化

InnoDB用一块内存区做IO缓存池, 不仅用来缓存InnoDB的索引块, 也用来缓存InnoDB的数据块

innodb_buffer_pool_size

该变量决定了innodb存储引擎表数据的索引数据的最大缓存区大小. 在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size的值越大, 缓存命中率越高, 访问InnoDB表需要的磁盘I/O就越少, 性能也就越高

innodb_buffer_pool_size=512M

innodb_logbuffer_zise

决定了innodb重做日志缓存的大小, 对于可能产生最大更新记录的大事务, 增加innodb_log_buffer_size的大小, 可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作

innodb_log_buffer_size=10M

MySql并发参数调整

MySql Server是多线程结构, 包括后台线程和客户服务线程. 多线程可以有效的利用服务资源, 提高数据库的并发性能. 控制并发连接和线程的主要参数包括 max_connectons、back_log、thread_cache_size、table_open_cahce.

max_connections

采用max_connections控制允许连接到MySql数据库的最大数量, 默认值151. 如果状态变量connection_errors_max_connections不为零, 并且一直增长, 说明不断有连接请求因数据库连接数已达到最大值而失败, 可以考虑增大max_connections的值
MySql最大可支持的连接数, 取决于很多因素(操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度、期望的响应时间等),在Linux平台下,性能好的服务器, 支持500-1000个连接,具体根据服务器性能进行设定

back_log

back_log参数控制MySql监听TCP端口时设置的积压请求栈大小.
当MySql的连接数达到max_connectons时, 新来的请求将会被存到堆栈中, 以等待某一连接释放资源, 该堆栈的数量即back_log, 如果等待的数量超过back_log, 将不被授予连接资源, 将会报错.
5.6.6版本之前默认值为50, 之后的版本默认为50+(max_connections/5), 但最大不会超过900
如果需要数据库在较短时间内处理大量连接请求, 可以考虑适当增大back_log的值

table_open_cache

该参数用来控制所有Sql语句执行线程可以打开表缓存的数量, 而在执行Sql语句时, 每个sql执行线程至少打开1个表缓存. 该参数的值应该根据设置最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定.
max_connections * N

thread_cache_size

为了加快连接数据库的速度, MySql会缓存一定数量的客户服务线程以备重用, 通过参数tread_cache_size可控制MySql缓存客户服务线程的数量

innodb_lock_wait_timeout

该参数是用来设置InnoDB事务等待行锁时间,默认50ms.
根据需要进行动态设置, 对于需要快速反馈的业务系统来说, 可以将行锁的等待时间调小, 以避免事务长时间挂起, 对于后台运行的批量处理程序来说, 可以将行锁的等待时间调大, 已避免发生大的回滚操作.

MySql锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制
在数据库中, 除传统的计算资源(CPU、RAM、I/O等)的争用外, 数据也是一种供许多用户共享的资源. 如何保证数据并发访问的一致性、有效性、是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要因素。锁对数据库而言显得尤为重要，也更加复杂。

锁分类

• 数据操作的颗粒度：
  1. 表锁：操作时，会锁定整个表
  2. 行锁：操作时，会锁定当前操作行
• 数据操作的类型：
  1. 读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响
  2. 写锁(排它锁)：当前操作没有完成之前，会阻断其他写锁和读锁

MySql锁

针对其他数据库而言，MySql的锁机制比较简单，特点就是不同的存储引擎支持不同的锁机制

存储引擎	表级锁	行级锁	页面锁
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

锁的特点

锁类型	特点
表级锁	偏向MyISAM存储引擎, 开销小, 加锁快, 不会出现死锁, 锁定粒度大,发生锁冲突的概率最高, 并发最低
行级锁	偏向InnoDB存储引擎, 开销大, 加锁慢, 会出现死锁, 锁定粒度最小, 发生锁冲突的概率最低, 并发度最高
页面锁	开始和加锁时间介于表锁和行锁之间, 会出现死锁, 锁定粒度介于表锁和行锁之间,并发度一般

表级锁更适合查询为主,只有少量按索引条件更新数据的应用 如Web应用
行级锁更适合有大量按索引条件并发更新少量不同数据, 同时又有查询 如一些在线事务处理(OLTP)系统

MyISAM表锁

MyISAM存储引擎只支持表锁, 也是MySql开始版本中唯一支持的锁类型

加表锁

MyISAM在执行查询语句(select)前, 会自动给涉及的所有表加读锁, 在执行更新操作(update、delete、insert)前，会自动给涉及的表加写锁，这个过程不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显示加锁

   lock table 表名 read; -- 加读锁
   lock table 表名 write; -- 加写锁
   unlock tables; -- 解锁

锁类型	当前操作界面	其他操作界面
读锁	可读, 不可写	可读, 不可写
写锁	可读, 可写	不可读, 不可写

由上表可见:

1. 对MyISAM表的读操作, 不会阻塞其他用户对用一张表的读请求, 但会阻塞对同一张表的写请求
2. 对MyISAM表的写操作, 则会阻塞其他用户对同一张表的读和写操作

简而言之, 就是读锁会阻塞写, 但不会阻塞读, 而写锁, 即会阻塞读, 又会阻塞写
此外, MyISAM的读写锁调度是写优先, 这也是MyISAM不适合做写为主的表的存储引擎的原因, 因为写锁后, 其他线程不能做任何操作, 大量的更新会使查询很难得到锁, 从而造成永久阻塞

查看锁争用情况

show open tables;

• In_user: 当前表被查询使用的次数, 如果该数为0, 则表是打开的, 但当前没有被使用.
• Name_locked: 表名称是否被锁定. 名称锁定用于取消表或对表进行重命名等操作

show status like 'Table_locks%';

• Table_locks_immediate: 指的是能够立即获的表级锁的次数, 每立即获取表, 值+1
• Table_locks_waited: 指的是不能立即获取表级锁而需要等待的次数, 每等待一次, 值+1, 此值高说明存在较为严重的表级锁争用情况

InnoDB行锁

行锁介绍

行锁特点: 偏向InnoDB存储引擎, 开销大, 加锁慢; 会出现死锁; 锁定粒度小, 发生锁冲突的概率最低, 并发度也最高
InnoDB与MyISAM的最大不同有俩点: 一是支持事务, 二是采用行级锁

show variables like 'tx_isloation';-- 查看数据库默认隔离级别

InnoDB的行锁模式

InnoDB实现了一下俩种类型的行锁:

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

对于udate、delete、和insert语句，InnoDB会自动给涉及的数据集加排他锁（X）
对于普通的select语句，InnoDB不会加任何锁

通过以下语句显示给记录集加共享锁或排他锁

select * from 表名 where ... lock in share mode; -- 共享锁
select * from 表名 where ... for update; -- 排他锁

无索引行锁升级为表锁

如果不通过索引条件检索数据, 那么InnoDB将对表中所有记录加锁, 实际效果跟表锁一样.

查看当前表的索引 : show index from 表名;

间隙锁危害

当我们用范围条件, 而不是使用相等条件检索数据, 并请求共享或排它锁时, InnoDB会给符合条件的已有数据进行加锁. 对于键值在条件范围内但并不存在的记录, 叫做间隙(GAP).
InnoDB也会对这个"间隙"加锁, 这种锁机制就是所谓的间隙锁(Next-Key锁).

InnoDB行锁争用情况

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits : 当前正在等待锁定的数量
Innodb_row_lock_time : 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg : 每次等待所花平均时长
Innodb_row_lock_time_max : 从系统启动到现在等待最长一次所花的时间
Innodb_row_lock_waits : 系统启动到现在总共等待的次数

当等待次数很高, 而且每次等待时间也不小时, 就需要分析系统为什么会有如此多的等待, 然后根据分析结果着手制定优化计划.

SQL技巧

SQL执行顺序

SQL编写顺序

select distinct
	查询字段...
from
	表名
[left|right] join 
	表名 on 关联条件
where 
	查询条件
group by 
	分组字段...
having
	分组过滤条件
order by
	排序字段
limit
	分页数量

SQL执行顺序

from 
	表名
on 
	关联条件
[left|right] join
	表名
where 
	查询条件
group by 
	分组字段...
having 
	分组过滤条件
select distinct
	查询字段...
 order by 
 	排序字段
 limit 
 	分页数量

正则表达式使用

正则表达式(Regular Expression) 是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串

符号	含义
^	在字符串开始处进行匹配
$	在字符串末尾处进行匹配
.	匹配单个字符, 包括换行符
[…]	匹配出括号内的任意字符
[^…]	匹配不出括号内的任意字符
a*	匹配零个或多个a(包括空串)
a+	匹配一个或多个a(不包括空串)
a?	匹配零个或一个a
a1|a2	匹配a1或a2
a(m)	匹配m个a
a(m,)	只是匹配m个a
a(m,n)	匹配m-n个a
a(,n)	匹配0-n个a
(…)	将模式元素组成单一元素

示例

select * from 表名 where 条件字段 regexp '^T'
select * from 表名 where 条件字段 regexp 'T$'
select * from 表名 where 条件字段 regexp '[awb]'

常用函数

数字函数

函数名称	作用
abs	求绝对值
sqrt	求二次方根
mod	求余数
ceil 和 ceiling	俩个函数功能相同, 都是返回不小于参数的最小整数, 即向上取整
floor	向下取整,返回值转化为一个bigint
rand	生成一个0-1之间的随机数,传入整数参数是,用来产生重复序列
round	对所传参数进行四舍五入
sign	返回参数的符号
pow 和 power	俩个函数功能相同, 都是所传参数的次方的结果值
sin	求正弦值
asin	求反弦值, 与函数sin互为反函数
cos	求余弦值
acos	求反余弦值, 与函数cos互为反函数
tan	求正切值
atan	求反正切值, 与函数tan互为反函数
cot	求余切值

字符串函数

函数名称	作用
length	计算字符串长度的函数, 返回字符串的字节长度
concat	合并字符串, 返回结果为连接参数产生的字符串, 参数可以是一个或多个
insert	替换字符串函数
lower	将字符串中的字母转换为小写
upper	将字符串中的字母转换为大写
left	从左侧截取字符串,返回字符串左边的若干个字符
right	从右侧截取字符串, 返回字符串右边的若干个字符
trim	删除字符串左右俩边的空格
replace	字符串替换函数, 返回替换后的新字符串
substring	截取字符串, 返回从指定位置开始的指定长度的字符串
reverse	字符串反转(逆序)函数, 返回与原字符串顺序相反的字符串

日期函数

函数名称	作用
curdate 和 current_date	俩个函数的作用相同, 返回当前系统的日期值
curtime 和 current_time	俩个函数的作用相同, 返回当前系统的时间值
now 和 sysdate	俩个函数作用相同, 返回当前系统的日期和时间值
month	获取指定日期中月份
monthname	获取指定日期中的月份的英文名称
dayname	获取指定日期中对应星期几的英文名称
dayofweek	获取指定日期对应一周的索引位置值
week	获取指定日期是一年中第几周, 返回值范围0~52或1-53
dayofyear	获取指定日期是一年中第几天, 返回值为1-366
dayofmonth	获取一个日期是一个月中的第几天, 返回值范围是1~31
year	获取年份, 返回值为1970~2069
time_to_sec	将时间参数转换为秒数
sec_to_time	将秒数转为时间, 与time_to_sec互为反函数
date_add 和 adddate	俩个函数功能相同, 都是向日期添加指定时间间隔
date_sub 和 subdate	俩个函数功能相同, 都是向日期减去指定的时间间隔
addtime	时间加法运算, 在原始时间上添加指定时间
subtime	时间减法运算, 在原始时间上减去指定的时间
datediff	获取俩个日期之间的间隔, 返回参数1-参数2的值
date_format	格式化指定的日期, 根据参数返回指定格式的值
weekday	后去指定日期在一周内对应的工作日索引

MySql常用工具

MySql客户端工具

mysql [options] [database]
参数:
	-u , --user=name		指定用户名
	-p , --password[=name]	指定密码
	-h , --host=name		指定服务器ip或域名
	-P , --post=#			指定连接端口
	-e , --execute-name		执行sql语句并退出
示例 :
mysql -h127.0.0.1 -P3306 -uroot -p1234
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -uroot -p1234 数据库名称 -e "执行的sql语句";

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存, 所以如果想要检查这些文本格式的文本, 就会使用到mysqlbinlog日志管理工具

mysqlbinlog [options] log-files ....
选项:
	-d, --database=name : 指定数据库名称, 只列出指定的数据库相关操作
	-o, --offset=# : 忽略调日志中的前N行命令
	-r, -- result-file=name : 将输出文本格式日志输出到指定文件
	-s, --short-form : 显示简单格式, 省略掉一些信息
	--start-datatime=date --stop-datetime=date : 指定日期间隔内的所有日志
	-- start-position=pos --stop-position=pos : 指定位置间隔内的所有日志

mysqldump

mysqldump 客户端工具用来备份数据库或不同数据库之间进行数据迁移, 备份内容包含创建表, 及插入表的sql语句

mysqldump [options] 数据库名称 [表名称]
mysqldump [options] --database/-B 数据库名称..
mysqldump [options] -all-databases/-A
参数:
	-u , --user=name		指定用户名
	-p , --password[=name]	指定密码
	-h , --host=name		指定服务器ip或域名
	-P , --post=#			指定连接端口
输出内容参数选项:
	--add-drop-database		在每个数据库创建语句前加上drop database语句
	--add-drop-table		在每个表创建语句前加上drop table语句,默认开启;不开启(--skip-add-drop-table)
	-n, --no-create-db		不包含数据表的创建语句
	-t, --no-create-info	不包含数据表的创建语句
	-d, --no-data			不包含数据
	-T, --tab=name			自动生成俩个文件: 一个.sql文件,创建表结构的语句;
							一个.txt文件,数据文件, 相当于select info outfile
示例:
	mysqldump -u登用户名 -p登录密码 数据库名 表名 [--add-drop-database --add-drop-table] > 文件名
	myslqdump -u登录用户名 -p登录密码 -T 目录地址 数据库名 表名

mysqlimport/source

mysqlimport是客户端数据导入工具, 用来导入mysqldump加-T参数后导出的文本文件

mysqlimport [options] 表名 文件名...
示例:
mysqlimport -uroot -p1234 test /tmp/city.txt

如果需要导入sql文件, 可以使用mysql中的source指令:

source /root/tb_test.sql

mysqlshow

mysqlshow客户端对象查找工具, 用来很快的查找存在哪些数据库, 数据库中的表, 表中的列或索引

mysqlshow [options] [数据库名称 [数据表名 [数据列名称]]]
参数: 
	--count 显示数据库及表的统计信息(数据库,表均可不指定)
	-i		显示指定数据库或指定表的状态信息
示例:
mysqlshow -uroot -p134 库名 表名 --count

MySql日志

任何一种数据库中, 都会有各种各样的日志, 记录着数据库工作的方方面面, 以记录数据库发生的各种事件,
MySql中,有4种不同的日志(错误日志、二进制日志、查询日志、慢查询日志)

错误日志

错误日志是MySql中最重要的日志之一, 记录了当mysqld启动和停止时, 以及服务器在运行过程中发生任何严重错误时的相关信息, 当数据库出现任何故障导致无法正常是使用时, 可以先查看此日志.
该日志是默认开启的, 默认存放目录是数据目录(var/lib/mysql), 模式日志文件名为hostname.err(hostname是主机名)

show variables like 'log_error%';

查看日志内容

tail -f .\Z-HR-Z.err;

二进制日志

二进制日志(binlog)记录了所有的DDL(数据定义)语句和DML(数据超纵)语句, 但不包括数据查询语句. 此日志对于灾难时的数据恢复起极其重要的作用.MySql的主从复制,就是通过binlog实现的
二进制日志, 默认是没有开启的. 需要到MySql的配置文件中开启, 并配置MySql日志的格式
配置文件名称: my.cnf
日志存放位置: 配置时, 给定了文件名但没有指定路径, 日志默认写入mysql的数据目录

# 配置开启binlog日志, 日志的文件前缀为 mysqlbin --> 生成的文件名如: mysqlbing.000001
log_bin=mysqlbin

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

mysql’bin.index : 该文件是日志索引文件, 记录日志的文件名
mysqlbing.000001: 日志文件
查看日志内容:

## STATEMENT
mysqlbinlog mysqlbing.000001;
## ROW
mysqlbinlog -vv mysqlbing.000002;

日志格式

STATEMENT

该日志格式在日志文件中记录的是sql语句,每一条对数据进行修改的sql都会记录在日志中, 通过mysql提供的mysqlbinlog工具, 可以清晰的查看到每条语句的文本, 主从复制时, 从库(slave) 会将日志解析为原文本, 并在从库中执行一次

ROW

该日志在日志文件中记录的是每一行的数据变更, 而不是记录sql语句,
比如:
update table set a = 1
如果是STATEMENT日志格式, 在日志中会记录一行sql语句, 如果是ROW, 由于是全表进行更新, 也就是每一行记录都会变更, ROW格式的日志中会记录每行的数据变更

MIXED

这是目前mysql默认的日志格式, 即混合了 STATEMENT和ROW俩种格式. 默认情况下采用STATEMENT, 但在一些特殊情况下采用ROW来进行记录, MIXED格式能尽量利用俩种模式的优点, 而避开他们的缺点

日志删除

对于比较繁忙的系统, 由于每天生成日志量大, 日志文件就会占用大量的磁盘空间, 所以要对日志文件进行清除

方式一

通过Reset Master 指令删除全部的binlog日志, 删除之后, 日志编号将从****.000001重新开始
执行删除日志指令

Reset Master;

方式二

执行指令purge master logs to 'mysqlbin.****'; , 该命令将删除*****编号之前的所有日志.

方式三

执行指令purge master logs before 'yyyy-mm-dd hh24:mi:ss';,该命令将删除日志为yyyy-mm-dd hh24:mi:ss 之前产生的所有日志

方式四

设置参数 --expire_logs_days=#, 此参数的含义是设置日志的过期天数, 过了指定的天数后日志将自动删除, 这样有利于减少DBA管理日志的工作量.
在mysql的配置文件中配置

log_bin=mysqlbin
binlog_format=ROW
--expire_logs_days=3

查询日志

查询日志中记录了客户端的所有操作语句, 而二进制日志不包含查询数据的sql语句
默认情况下, 查询日志是未开启的, 开启查询日志, 设置如下配置:
在mysql的配置文件中

# 开启查询日志 0-关闭 1-开启
general_log=1
# 配置查询日志的文件名 如果没有指定 默认文件名为host_name.log
general_log_file=file_name.log

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值,并扫描记录数不小于min_examined_row_limit的所有sql语句的日志
log_query_time默认为10秒, 最小为0, 可以精确到微秒

文件位置和格式

慢查询日志默认是关闭的, 可以通过俩个参数开启慢查询日志

# 该参数用来控制慢查询日志是否开启, 1-开启 0-关闭
slow_query_log=1
# 该参数用来指定慢查询日志的文件名
slow_query_log_file=日志文件名.log
# 该选项用来配置查询的时间限制, 超过这个时间将认为是慢查询, 将进行日志记录, 默认10S
long_query_time=3

查看日志

查询慢查询日志时间

show varialbes like 'long_query_time';

查看慢查询日志

查看日志最后几行记录

tail -f 日志文件名

如果慢查询日志内容很多, 可以借助mysqldumpslow工具

mysqldumpslow 日志文件名

Msql复制

复制概述

复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中然后在从库上对这些日志重新执行(重做). 从而使从库和主库数据保持同步.

Mysql支持一台主库同时向多台从库复制, 从库同时也可以作为其它从服务器的主库, 实现链状复制

复制原理

从上图来看, 复制分为三步:

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

复制优势

• 主库出现问题,可以快速切换到从库提供服务
• 可以在从库上执行查询操作, 在主库中更新, 实现读写分离, 降低主库的访问压力
• 可以在从库中执行备份, 以避免备份期间影响主库的服务

搭建步骤

master

1. 在master的配置文件my.cnf(一般位于/usr/my.cnf)中, 配置如下内容

   # mysql服务ID, 保证整个集群环境中唯一
   server-id=1
   # mysql binlog日志的存储路径和文件名
   lob-bin=/var/lib/mysql/mysqlbin
   # 错误日志, 默认开启
   # log-err
   # mysql的安装目录
   # basedir
   # mysql的临时目录
   # tmpdir
   # mysql的数据存放目录
   # datadir
   # 是否只读 1-只读 0-读写
   read-only=0
   #忽略的数据, 指定不需要同步的数据库
   binlog-ignore-db=mysql
   # 指定同步的数据库
   # binlog-do-db=数据库名
   

2. 执行完毕后, 重启mysql

   service mysql restart
   

3. 创建同步数据的账户, 并且进行授权操作(Mysql执行)

   grant replication slave on *.* to '账户名'@'目标ip' identified by '账户密码';
   

   刷新权限列表

   flush privileges;
   

4. 查看master状态

   show master status;
   

   
   字段含义:

   File: 从那个日志文件开始推送日志文件
   Positon: 从那个位置开始推送日志
   Binlong_Ignore_DB: 指定不需要同步的数据库
   

slave

1. 在slave端配置文件中, 配置如下内容

   # mysql服务端ID, 唯一
   server-id=2
   # 指定binlog日志
   lob-bin=/var/lib/mysql/mysqlbin
   

2. 执行完毕后, 重启mysql

   service mysql restart
   

3. 执行如下指令(mysql执行)–指定当前从库对应的主库的ip地址,用户名,密码, 从那个日志文件开始的那个位置开始同步推送日志

   change msater to master_host='master服务器IP地址',master_user='master设置的用户名',master_password='master设置的密码',master_log_file='日志开始文件名',master_log_pos=日志开始行;
   

4. 开启同步操作

   start slave;
   

   查看是否启动成功

   show slave status\G;
   

   
5. 停止同步操作

   stop slave;