MySQL 功能与特性

索引

索引是帮助MySQL高效获取数据的数据结构

通过维护一个树结构，通过检索索引来完成快速的数据区分，

索引的优势

1 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO 成本

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

索引的劣势

1 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是需要占用空间的

2 虽然索引大大提高了查询效率，同时也降低更新表的速度，如对表进行INSERT，UPDATE，DELETE，因为更新表时，MySQL不仅要存储数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引结构

索引是在MySQL的数据引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引起用都支持所有的索引类型。MySQL提供了以下4种索引

BTREE索引：最常见的是索引类型，大部分索引都支持B树索引
HASH索引：只有Memeory引擎支持，使用场景简单
R-tree索引：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text 全文索引：全文索引页是MyISAM的一个特殊索引类型，用于全文索引

BTREE

多路平衡搜索树

树种每个节点最多包含m个孩子
除根节点与叶子节点外，每个节点至少有[cell(m.2)]个孩子
若根节点不是叶子节点，则至少有两个孩子
所有的叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成，其中[cell(m/2)-1] ≤ n ≤ m-1

B树插入过程

插入 A B G O P M W T Z D K L Q N I 这组数据
树阶为5 
B 树阶为 5 的定义：
每个节点最多存储 m - 1 = 4 个关键字
最少存储 ceil(m/2) - 1 = 2 个关键字（非根）
每个节点最多 5 个孩子
插入导致关键字超过 4 个时，取中间值上提，左右分裂为两个新节点
初始插入 A ~ G（A B G O P）
[A B G O P] ← 插入 P 时触发裂变
裂变为
        [G]
      /     \\
 [A B]     [O P]

插入 M → 放入右子树 [O P M]
插入 W → [O P M W] → 无需裂变
插入 T → [O P M W T] → 裂变
        [G]
      /     \\
 [A B]     [M]
          /     \\
     [O P]     [T W]
   
插入 Z → 插入到右子树 [T W Z] → 不触发裂变
插入 D → 插入 [A B D] → 不裂变
插入 K → 插入 [A B D K] → 不裂变
插入 L → [A B D K L] → 裂变

              [D G]
            /   |    \\
			   [A B] [K L]  [M]
                     /   \\
                  [O P]  [T W Z]
插入 Q → 插入 [O P Q]
插入 N → 插入 [K L N]
插入 I → 插入 [A B I]
到目前还未再触发裂变
插入导致 [K L N] → [K L N] 满
插入 Q → [O P Q] 满 → 裂变
          [D G M]
      /    |     |    \\
 [A B I] [K L N] [O P] [T W Z]

B+ Tree

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

叶子节点中保存了所有key的信息，即所有的值都需要遍历到叶子节点才可以被访问到，保证了数据查询的统一性

MySQL 扩展

在叶子节点间添加了链表指针，便于区间访问查询

索引分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

创建索引

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name;

[USING index_type]

ON tbl_name(index_col_name , …)

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

查看索引

show index from tbl_name ; 查看索引

show index from city\G ; 查看详细的索引

drop index index_name on tbl_name ; 删除索引

alter table tbl_name add primary key (column_list);

alter table tb_name add unique index_name (column_list);

alter table tb_name add index index_name (column_list);

alter table tb_name add fulltext index_name (column_list);

索引设计原则

查询频次较高，且数据量比较大的表

索引字段的选择

使用唯一索引，区分度越高，使用索引的效率越高

索引条目不是越多越好会影响整体的查询性能

使用短索引，提升整体的查询效率

利用最左前缀

视图

视图是一条select语句执行后返回的结果集

简单使用视图的用户完全不需要关心后面对应的表的结构，关联条件和筛选条件。
安全使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列
数据独立一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，

创建视图

CREATE view view_name as select_statement ;

修改视图

ALTER view view_name as select_statement ;

查看视图

SHOW CREATE VIEW view_name ;

删除视图

DROP VIEW view_name ;

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合

存储过程：是一个没有返回值的函数

函数：是一个有返回值的过程

创建存储过程

CREATE PROCEDURE procedure_name

begin

end ;

使用 delimiter $ 来定义分隔符来便于创建存储过程

调用存储过程

call procedure_name();

查询存储过程

select name from mysql.proc where db=”” 4；

show procedure status ;

show create procedure procedure_name ;

删除存储过程

drop procedure procedure_name ;

语法

声明变量

create procedure pro_test1()
begin 
declare num int default 10 ;
select concat('num的值为: ', num);
end;

set 赋值

create procedure pro_test2()
begin
	declare name varchar(20);
	set name = 'mysql';
	select name ;
end ;
delimiter ;

select into 赋值

create procedure pro_test3()
begin 
	declare num int ;
	select count(*) into num from city ;
	select concat('记录数',num);
end ;
delimiter ;

条件判断

if search_condition then statment_list 
	[elseif search_condition then statment_list]
	[else statement_list]
end if ;

传递参数

1
2
3

create procedure procedure_name([in/out/inout] 参数名 参数类型)
...

create procedure pro_test(in height int , out description varchar(100))
begin 
	if height >= 180 then 
		set description = '身材高挑';
	elseif height >=170 and height < 180 then 
		set description = '标准身材' ;
	else 
		set description = '一般身材' ;
	end if ;
end

call pro_test(168 , @description)$
select @description$

case

create procedure pro_test(mon int)
begin 
	declare result varchar(10);
	case 
		when mon >= 1 and mon <= 3 ; then 
	set result ='第一季度';
		when mon >= 4 and mon <= 6 ; then 
	set result = '第二季度';
		when mon >= 7 and mon <= 9 ; then 
	set result = '第三季度';
		else 
			set result = '第四季度';
	end case ;
	select concat("结果为", result) as content ;
end ;

循环

WHILE （满足条件继续循环）

1
2
3

while sarch_condition do
	statement_list 
end while ;

REPEAT（满足条件退出循环）

REPEAT
	statment_list

	UNTIL serach_condition
END REPEAT;

LOOP

LOOP
	statment_list
	if statment then 
		leave 
	end if;
	end loop statment;

游标/光标

存储查询结果相当于存储结果集变量

DECLARE cursor_name CURSOR FOR select_statment ;

OPEN cursor_name ;   
 
FETCH curosr_name INTO var_name [, var_name] ...    获取游标

CLOSE ;                关闭游标

create procedure protest()
begin 
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11);
	declare has_data int default 1 ;

	declare emp_result cursor for select * from emp ;
	DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0 ;

	open emp_result ;
	repeat 
		fetch emp_result into e_id , e_name , e_age , e_salary ;
		select concat('id=',e_id , ', name=',e_name ,', age= ',e.age,',薪资为: ',e_salary);
		until has_data = 0 
	end repeat ;
	close emp_result

end ;

存储函数

create function fun1(countryId int)
RETURNS int
begin 
	declare cnum int 

	select count(*) into cnum from city where country_id = countryId;

	return cnum ;
end 

select fun1();  调用函数

触发器

触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端保证数据的完整性，

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 ;

# 创建表结构
 create table emp_logs (
	 id int(11)  not null auto_increment,
	 operation varchar(20)  not null comment '操作类型，insert/update/delete',
	 operation_time datetime not null comment '操作时间',
	 operate_id int(11) not null comment '操作表的ID',
	 operate_parames varchar(500) comment '操作参数',
	 primary key('id')
	 ) engine=innodb default charset=utf8;
 }
 
 # 创建触发器
 create trigger emp_insert_trigger 
 after insert
 on emp 
 for each row 
 begin
	 insert into emp_logs(id,operation,operation_time,operation_params) values(null,'insert', now(),
	 new.id, concat('插入后('id:', new.id ,', name:' , new.name,",salary:",new salary,')'));
 end

MySQL架构

连接器与MySQL 服务器建立了连接
服务层，定义命令接口，命令解析器，优化和缓存
引擎层，接收命令并对命令进行响应的操作处理
文件系统，对写入的数据进行数据存储

存储引擎

show engines ;

InnoDB

事务安全，锁机制-行锁，支持外键

MyISAM

不支持事务，表锁，不支持外键

InnoDB

事务

1
2
3

start transaction 
insert into ;
commit ;

外键约束

1	CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country innodb(country_id) ON DELETE RESTRICT ON UPDATE CASADE

ON DELETE RESTRICT 删除主表时，如果有关联记录，则不删除

ON CASCADE 跟新主表，如果有关联，则更信子表记录

SQL 优化

查看SQL 执行频率

show status like ‘Com___________’; 整体的查询次数

show global status like ‘Innodb_rows_%’; 查看innodb 影响的行数

定位低效SQL语句

慢查询日志

show processlist ;

+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  289 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
1 id 系统分配的 connection_id
2 user列 显示当前用户
3 host列 在哪个IP/端口上工作
4 db列 目前进程连接的是哪个数据库
5 command 列 显示当前连接执行的命令
6 time列 显示这个状态的持续时间
7 state列 使用当前连接的sql语句状态
8 info列 显示这个sql语句，是判断问题语句的一个重要依据

explain

mysql> explain select student_name from students ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   15 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1 id  
- 当所有信息的id 是一样的，则表是顺序加载的
- id不同，id值越大，加载优先级越高
- id有相同也有不同，则先执行优先级高的，相同id视为同一组

2 select_type
- SIMPLE  简单子查询
- PRIMARY 包含复杂子查询
- SUBQUERY 在SELECT或WHERE列表中包含了子查询
- DERIVED 在FROM列表中包含的子查询
- UNION 若第二个SELECT出现在UNION之后，则标记为UNION
- UNION RESULT 从UNION表获取结果的SELECT
3 table输出表信息
4 type
- NULL 不访问任何表，索引
- system 表只有一行记录
- const 通过索引一次查询到
- eq_ref 使用主键关联查询 结果
- ref 非唯一性索引扫描
- range 只检索给定返回的行，
- index 只遍历了索引数
- 将遍历全表以找到匹配的行
5 key
- possible_keys  显示可能应用在这张表的索引，一个或多个
- key 实际使用的索引，如果为NULL，则没有使用索引。
- key_len 表示索引中使用的字节数，该值为索引字段最大可能长度，在不损失精度的前提下，越短越好
6 rows
- 扫描的行数
7 extra 
- using filesort  对数据使用了一个外部的索引排序，而不是按照表内的索引进行读取 （效率低
- using temporary 使用了临时表保存中间结果 （效率低
- using index     使用了覆盖索引，避免访问表的数据行

show profiles

查询当前版本是否支持profile 功能

select @@have_profiling ;

set profiling = 1;

select @@profiling

会对后续的查询命令进行性能统计

trace

分析优化器执行计划

SET optimizer_trace= “enabled=on”, end_markers_in_json=on;

set optimizer_trace_max_mem_size = 100000;

select * from tb_item where id < 4;

select * from information_schema.optimizer_trace\G; 查询优化器的执行过程

索引查询

避免缓存失效

使用全值匹配
最左前缀法则当表中具有复合索引时查询时包含索引的最左列
范围查询右边的匹配，不能使用索引。
不要在索引列上进行运算操作，索引将失效
字符串不加单引号，索引将失效
尽量使用覆盖索引，避免select * 覆盖索引，查询被添加到索引中的字段
用or 分割的条件，or 前后没有都使用索引，则索引失效
以%开头的Like 模糊查询索引失效
全表比索引快则不走索引
is NULL / is not NULL 有时索引失效会根据性能决定
in 走索引 not in 不走索引
单列索引和复合索引，尽量使用复合索引，而少使用单列索引。
- 当遇到单列索引时，数据库会选择使用最优的索引

SQL优化

大批量加载数据

load data local infile ‘/root/sql2.log’ into table ‘tb_user_2’ fields terminated by ‘,’ lines terminated by ‘\n’;

关闭唯一性校验

SET UNIQUE_CHECKS= 0 关闭唯一性校验

手动提交事务

SET AUTOCOMMIT=0 关闭自动提交事务

优化 insert 语句

insert into tb_test values(1,'tom');
insert into tb_test values(2,'jerry');
优化为
insert into tb_test valuses(1,'tom'),(2,'jerry');

在事务中进行数据插入
start transaction ;
insert into tb_test values(1,'tom');
insert into tb_test values(2,'jerry');
commit ;

数据有序插入
insert into tb_test values(2,'jerry');
insert into tb_test values(1,'tom');
优化为

insert into tb_test values(1,'tom');
insert into tb_test values(2,'jerry');

优化order by 语句

尽量减少额外的排序，通过索引直接返回有序数据，where条件和Order by 使用相同的索引，并且Order By的顺序和索引顺序相同，并且Order by的字段都是升序或者都是降序。

filesort 查询

1 两次扫描算法 4.1之前，根据条件取出排序字段和行指针信息，在sort buffer中排序，如果sort buffer不够，则在临时表中存储排序结果。完成后再根据行指针取回读取记录

2 一次扫描算法一次性取出满足条件的所有字段，然后在排序区中排序后直接取出结果集

优化group by 语句

设置不排序（order by null）

设置索引

优化子查询语句

使用多表连接查询替换子查询

优化OR 条件

保证查询条件都具备索引

1
2
3

select * from emp where id = 1 or id = 10 ;
替换为
select * from emp where id = 1 union select * emp where from id = 10 ;

优化limit 限制

直接使用分页查询，会对表中的数据进行排序操作，且约往后代价越高

select * from tb_item limit 2000000, 10 ;

1 通过使用索引先查询到对应的信息，再根据索引找到原表中的其他列信息
select * from tb_item t ; (select id from tb_item order by id limit 2000000,10) a where t.id = a.id;

2 将分页转换为对某个位置的查询
select * from tb_item t where t.id > 2000000 limit 10 ;

优化索引查询

1 . 指定索引名称

指定数据库查询所使用的索引

1 2	`explain select * from tb_seller use index(idx_seller_name) where name = "";`

2 . 忽略索引名称

忽略某个索引对数据库的查询

1	`explain select * from tb_seller ignore index(idx_seller_name) where name = "";`

3 . 强制使用索引

数据库认为全表扫描比索引速度更快

1	`explain select * from tb_seller force index(idx_seller_name) where name = "";`

应用侧优化

连接池

减少查询频率

缓存层

缓存查询过程

1 . 客户端发送一条查询给服务器

2 . 服务器先会检查查询语句，命中缓存则立即返回缓存中的结果

3 . 服务端进行SQL解析，预处理，再由优化器生成对应的执行计划

4 . MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询

5 . 将结果返回给客户端

缓存配置

show variables like 'have_query_cache';
// 是否支持缓存
show variables like 'query_cache_type' ;
query_cache_type = 1
// 缓存是否开启 
show variables like 'query_cache_size' ;
// 缓存大小
show status like 'Qcache%' ;
// 查询缓存碰撞信息

SQL_CACHE  如果结果可缓存，则查询缓存结果
// DEMAND 按序执行查询缓存功能  ， 需指定SQL_CACHE
SQL_NO_CACHE  不检查查询缓存，也不检查结果是否已缓存，也不缓存结果

查询缓存失效

当查询缓存中的条目发生变化，则会使所有的查询缓存失效，在下一次查询时，需要重新从数据库中获取条目

负载均衡

读写分离 / 分布式完成负载均衡

内存优化

1 尽可能多的个MySQL分配缓存，但不能影响操作系统和其他应用软件

2 MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存

3 排序区，连接区等缓存是分配给每个数据库会话专用的，需要进行适当的内存大小分配

MyISAM内存优化

key_buffer_size ;
决定了MyISAM索引块缓存区的大小，影响存取效率
read_buffer_size ; 
如需要经常顺序扫描表，可以增大这一项来改善性能，但是每个session独占
read_rnd_buffer_size ;
对于需要做排序的myisam表的查询。

InnoDB 内存优化

innodb_bufffer_pool_size 
设置innodb存储引擎表数据和索引数据的最大缓存区大小，在保证其他程序正常运行的情况下
innodb_log_buffer_size 
决定innodb重做日志缓存的大小

并发配置

max_connections

max_connections 控制允许连接的最大数量，根据服务器性能进行评估

back_log

mysql连接数达到max_connections 存入back_log ，等待释放资源后进行连接

table_open_cache

控制所有执行线程可打开表缓存的数量

thread_cache_size

线程池大小

innodb_lock_wait_timeout

设置innodb事务等待行锁的时间

锁

协调多个进程或线程并发访问某一资源的机制

1
2
3

表级锁       偏向MyISAM存储引擎，开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率高，并发度小
行级锁       偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突概率低，并发度大      
页面锁

读写锁

1
2
3

lock table tb_book read ;

lock table tb_book write ;

锁状态

show open tables ;
In_user  表当前被查询使用的次数，如果该数为零，则表是打开的
Name_locked  表名称是否被锁定，名称锁定用于取消表或对表进行重命名等操作
show status like 'Table_locks%' ;
Table_locks_immediate  指能够立即获得表级锁的次数，每立即获得锁1;
Table_locks_waited     获取锁的等待次数

并发事务的问题

丢失更新             当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖
脏读                 当一个事务正则访问数据，并且对数据进行了修改，而这个修改还没提交到数据库中，这时另一个事务也访问这个数据，然后使用了这个数据
不可重读             一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致
幻读                 一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据

事务的隔离级别

隔离级别             丢失更新        脏读       不可重复读         幻读

Read uncommitted        - 
Read committed          -             -
Repeatable read         -             -             -
Serializable            -             -             -              -

设置事务隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Innodb 行锁

innodb的行锁依赖于事务，

当进入事务后，未commit前，由事务操作的行，无法被其他事务进行操作。

当索引失效后进行查询时，行锁会升级为表锁

间隙锁，当进行范围查询时，不存在的中间条目同样也会被进行加锁

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 ;      系统启动后到现在总共等待的次数

优化

尽可能让所有数据检索都能通过索引完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少索引条件，及索引范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度

尽可能使用低级别事务隔离

技巧

编写顺序

SELECT DISTINCT
	<select list>
FROM 
	<left_table> <join_type>
JOIN
	<right_table> on <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING 
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

执行顺序

FROM  <left_table>
ON    <join_condition>
<join_type>  JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select list>
ORDER BY <order_by_condition>
LIMIT <limit_params>

正则表达式

where regexp [regexp format]

常用函数

+---------------------+---------------------------------------------+
|       函数名        |                  功能描述                   |
+---------------------+---------------------------------------------+
| 字符串函数                                                      |
+---------------------+---------------------------------------------+
| LENGTH(str)         | 返回字符串的字节长度（中文算3，utf8下）     |
| CHAR_LENGTH(str)    | 返回字符串的字符长度（中文算1）             |
| CONCAT(str1,str2)   | 字符串拼接                                   |
| SUBSTRING(str, m, n)| 截取子字符串，从第 m 个字符开始 n 个长度     |
| LEFT(str, n)        | 返回字符串左边的 n 个字符                    |
| RIGHT(str, n)       | 返回字符串右边的 n 个字符                    |
| LOWER(str)          | 转小写                                       |
| UPPER(str)          | 转大写                                       |
| TRIM(str)           | 去除首尾空格                                 |
| REPLACE(str,a,b)    | 将字符串中的 a 替换为 b                      |
| INSTR(str,substr)   | 返回 substr 在 str 中的位置                  |
| LPAD(str,len,pad)   | 左填充到指定长度                             |
| RPAD(str,len,pad)   | 右填充到指定长度                             |

+---------------------+---------------------------------------------+
| 数学函数                                                          |  
+---------------------+---------------------------------------------+
| ABS(x)              | 返回绝对值                                   |
| CEIL(x) / CEILING(x)| 向上取整                                     |
| FLOOR(x)            | 向下取整                                     |
| ROUND(x,y)          | 四舍五入保留 y 位小数                        |
| MOD(x,y)            | 取模（x % y）                                |
| RAND()              | 返回0~1之间的随机数                          |
| POW(x,y) / POWER(x,y)| x 的 y 次幂                                 |
| SQRT(x)             | 开平方                                      |

+---------------------+---------------------------------------------+
| 日期时间函数                                                    |
+---------------------+---------------------------------------------+
| NOW()               | 当前日期时间                                 |
| CURDATE()           | 当前日期（仅日期）                           |
| CURTIME()           | 当前时间（仅时间）                           |
| DATE_FORMAT(d,f)    | 格式化日期时间                               |
| DATEDIFF(d1,d2)     | 返回两个日期之间相差天数                     |
| TIMESTAMPDIFF(unit,d1,d2)| 按单位返回日期差（年/月/日/小时等）     |
| DATE_ADD(d, INTERVAL x unit)| 日期加法                            |
| DATE_SUB(d, INTERVAL x unit)| 日期减法                            |
| YEAR(d)             | 提取年份                                     |
| MONTH(d)            | 提取月份                                     |
| DAY(d)              | 提取日期中的“日”                             |
| HOUR(d)             | 提取小时                                     |
| MINUTE(d)           | 提取分钟                                     |
| SECOND(d)           | 提取秒                                       |

+---------------------+---------------------------------------------+
| 聚合函数                                                        |
+---------------------+---------------------------------------------+
| COUNT(col)          | 计数                                         |
| SUM(col)            | 求和                                         |
| AVG(col)            | 平均值                                       |
| MAX(col)            | 最大值                                       |
| MIN(col)            | 最小值                                       |
| GROUP_CONCAT(col)   | 将同组内字段拼接成字符串                     |

+---------------------+---------------------------------------------+
| 控制流函数                                                      |
+---------------------+---------------------------------------------+
| IF(expr, t, f)      | 条件判断，expr 为真返回 t 否则返回 f         |
| IFNULL(expr1, expr2)| 如果 expr1 为 NULL 则返回 expr2             |
| NULLIF(a, b)        | 如果 a=b 则返回 NULL 否则返回 a             |
| CASE ... WHEN ... THEN ... ELSE ... END | 多条件判断语句          |
+---------------------+---------------------------------------------+
| 加密/杂项函数                                                      |
+---------------------+---------------------------------------------+
| MD5(str)            | 返回字符串的MD5值                            |
| SHA1(str)           | 返回字符串的SHA-1哈希                        |
| UUID()              | 生成全局唯一标识符（36位）                   |
| SLEEP(n)            | 延迟 n 秒（常用于测试）                      |
+---------------------+---------------------------------------------+

工具

mysql 客户端工具

-u   用户名
-p   密码
-h   IP
-P   端口

-e   命令执行

mysqladmin

mysqladmin -uroot -pitcast create "dbname";

mysqladmin drop "dbname";

mysqladmin version

mysqlbinlog

mysqlbinlog 
	-d   指定数据库名
	-o   忽略前n行命令
	-r   输出
	-s   简单信息
	--start-datetime=   --stop-datetime   时间区间查看日志
	--start-position=   --stop-position   位置间隔内的所有日志

mysqldump

mysqldump  
	-u  用户名
	-p  密码
	-h  IP
	-P  端口

mysqlimport

mysqlimport  [option]  db_name  textfile1 

mysqlimport -uroot -p test /tmp/city.txt 

source /root/tb_book.sql

mysqlshow

mysqlshow [options] [db_name [table_name[col_name]]]

--count 显示数据库及表的统计信息
-i      显示指定数据库或者指定表的状态信息

日志

1 2	`show variables list 'log_error'; // 查看错误日志位置`

慢查询日志

慢查询日志默认关闭

slow_query_log =1  开启慢查询日志

slow_query_log_file = slow_query.log

long_query_time = 10  超过这个值则认为本次查询是慢查询 需要日志记录

tail -f slow_query.log 查询日志

mysqldumpslow slow_query.log

复制

将主数据库中的DDL和DML 操作记录到二进制日志中，传送到从服务器中

1 master 主库事务提交时，会把数据变更作为时间events记录在二进制日志文件binlog中

2 主库推送二进制文件 binlog中的日志事件到从库的中继日志relay log

3 slave 重做中继日志中的事件，将改变反映它自己的数据

优势

1 主库出现问题，可以快速切换到从库

2 可以从从库中进行查询减少主库压力

3 可以在从库进行备份，不影响主库进行服务

MySQL

#存储 #数据库 #中间件 #MySQL

MySQL 功能与特性

http://gadoid.io/2025/06/04/MySQL-功能与特性/

作者

Codfish

发布于

2025年6月4日

许可协议

RocketMQ 单机部署下一篇

MySQL 功能与特性

索引

索引结构

BTREE

B树 插入过程

B+ Tree

索引分类

创建索引

查看索引

索引设计原则

视图

创建视图

修改视图

查看视图

删除视图

存储过程

创建存储过程

调用存储过程

查询存储过程

删除存储过程

语法

声明变量

set 赋值

select into 赋值

条件判断

传递参数

case

循环

游标/光标

存储函数

触发器

MySQL架构

存储引擎

InnoDB

事务

外键约束

SQL 优化

查看SQL 执行频率

定位低效SQL语句

explain

show profiles

trace

索引查询

SQL优化

优化 insert 语句

优化order by 语句

优化group by 语句

优化子查询语句

优化OR 条件

优化limit 限制

优化索引查询

应用侧优化

连接池

减少查询频率

缓存层

缓存查询过程

缓存配置

查询缓存失效

负载均衡

内存优化

MyISAM内存优化

InnoDB 内存优化

并发配置

max_connections

back_log

table_open_cache

thread_cache_size

innodb_lock_wait_timeout

锁

读写锁

锁状态

并发事务的问题

事务的隔离级别

Innodb 行锁

优化

技巧

编写顺序

正则表达式

常用函数

工具

B树插入过程