Tools

首页

```
# 命令
mysql -h localhost -u root -p;
show databases; 
use test;
show tables;
create table user(
	id int(10) not null auto_increment,
	name varchar(50) not null default '',
	primary key (id)
) engine=innodb charset=utf8 comment='user表';

alter table user add age int(3) not null default 0;
alter table user add sex tinyint(1) not null default 1 comment '0-未知，1男，2女';
alter table user change age age int(10) not null default 18;

show variable like 'slow%'; 		#可以查看很多配置变量
show create table user; 			#展示创建语句
show index from user; 				#展示索引
show processlist; 					#展示进程
desc user; 							#展示表结构
explain sql; 						#分析SQL语句
mysqldump -uroot -p test > /backup/test.sql 	#导出sql
select name from person where name regexp '^z'; #正则查询

# 查询
1.常用函数：if replace sum count max unix_timestamp from_unixtime find_in_set

2.联表
常用 left join，join，基本做法：查询主表，left join关联其他表，其他表数据与主表有重复时，group by

user_log表，对某个操作(act_id)，用户(user_id)多次操作，要求找出最后一次操作的时间(add_time)
user_id act_id add_time
1 		1 		2023-06-01
1 		2 		2023-06-02
1 		1 		2023-06-10

select a.id,a.name,max(from_unixtime(b.add_time)) from user a left join user_log b on a.id=b.user_id where b.act_id=1 group by a.id

同一张表可以连接多次
from user as ua left join user as ub

3.嵌套
select xxx from user where id in(select user_id from user_log where xxx)

# value与values有什么区别？
insert into user(name,age) value('张三', 18);
insert into user(name,age) values('张三', 18);

官方解释：
VALUE是在这种情况下的同义词 VALUES。既不暗示任何有关值列表的数量，也不暗示有关每个列表的值的数量。无论是单个值列表还是多个列表，也无论每个列表的值数量如何，都可以使用其中之一。

这种情况，即插入多个值时（INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9)）是同义词，等价的，也可以用value！

我的做法：插入单个值：value，插入多个值：values

# key与index有什么区别？

# 索引

## 创建索引
alter table user add index sex(`sex`); #普通索引
alter table user add primary key (`id`); #主键索引
alter table user add unique index phone(`phone`); #唯一索引
alter table user add fulltext index intro(`intro`); #全文索引，必须是MyISAM，InnoDB不支持
alter table user add age_sex (`age`,`sex`); #联合索引

## 删除索引
alter table user drop index index_name;

## 全文索引

alter table article add fulltext index fulltext_article (title, content);

select * from article where match(title,content) against('2012');

### 如何用好索引
* 依据where查询条件建立索引；
* 使用联合索引，而不是多个单列索引；
例如：select * from tab_a where b=? and c=?这个SQL，对b c字段建立联合索引的效率比单列的索引效率更高。

* 联合索引中索引的顺序根据区分度排，区分度大的放在前面。区分度是指字段值的种类，字段值种类越多的字段要放在前面，例如：idx_smp(name,gender)的效率要比idx_smp(gender,name)的效率高

* 联合索引能为前缀单列、复列查询提供帮助；例如：有idx_smp(a,b,c)这样的索引，where a=?或者where a=? and b=?都可以使用该索引，但是where c=?就无法使用该索引。
* 同样的，要合理创建联合索引，避免冗余
例如建立了idx_smp（a,b,c）就不需要建立idx_smp(a)、idx_smp(a,b)索引了。
* order by group by distinct等需要排序的操作，在没有索引的大数据量情况下需要排序，对IO和CPU性能消耗很大。如果有类似排序需求，则需要对相关字段建立索引，这样利用索引的有序特性不需要排序，直接按着索引顺序扫描即可。
* select …where .. like ‘%xx’;这种%放在头部的，是无法走索引的。
* select ＊ 不建议使用，因为会读取大量数据，也不利于使用索引覆盖技术。索引字段能够完全在索引中获取， 就不要使用select ＊（因为会导致回表），无法完整在索引中获取，也是建议select具体字段。

---------

什么情况会用到索引，什么情况不会，待整理

name like 'aaa%'//会用到索引

为什么field2或者field2,field3字段上没有索引。这是由于BTree索引因要遵守最左前缀原则

id与name都建立单列索引
order by name desc 不会使用索引
(id,name)建立联合索引
最后需要注意mysql对排序记录的大小有限制：max_length_for_sort_data 默认为1024；也就意味着如果需要排序的数据量大于1024，则order by不会使用索引，而是使用using filesort

mysql对于排序,使用了两个变量来控制sort_buffer_size和  max_length_for_sort_data

-------

## 存储引擎
MyIsam
表锁，一般情况下如果查询多建议使用myIsam 。

Innodb
延迟写入，一次写入
行锁，支持事务
InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如：`update table set num=1 where name like "zhang%"`

InnoDB不支持FULLTEXT类型的索引。

对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
待验证

InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

archive
1.只支持select and insert，不支持update and delete

csv
csv可编辑

memory
HASH索引 等值查询 地区 城市
btree

federated

创建权限
grant select,update,delete,insert on database.tablename to username@'127.0.0.1' identified by '123456';

connetion='mysql://user:123456@127.0.0.1:3306/database/tablename'

32位
mysql进程使用内存最大3G

为每个线程分配
排序缓存内存
sort_buffer_size
连接
join_buffer_size

内存溢出，超过总内存大小
数据库专用服务器

基准测试
工具生成数据

压力测试
真实数据及逻辑

## 二进制日志

statement（基于段落的日志），row（基于行的日志）

1. 配置my.ini

[mysqld]
log_bin=mysql-bin
binlog_format=statement

2. 打开mysql命令行

// 查询记录方式是否statement（基于段落的日志）
show variables like 'binlog_format';

// 刷新日志，mysql的data目录就会多一个mysql-bin开头的日志文件
// 然后做一些增删改查操作，mysql就会记录到日志文件中
flush logs;

// 重写打开一个cmd，进入bin目录
mysqlbinlog ../data/mysql-bin.000002 //查看
mysqlbinlog ../data/mysql-bin.000002 > test.sql //导出

3. 行格式

set session binlog_format=row;
show variables like 'binlog_row_image'
//row的三个参数，full,minimal,noblob

索引

btree-------听的模棱两可
order_sn='123456789';//全值匹配
order_sn like '123%'
> < 
order by
限制
内容占用大，mysql--》全局
not in <>

hash------
等值查询
每一行有一个hash码
限制
读取两次，1查询行，2，查询记录

## 慢查询日志
配置

slow_query_log=ON
slow_query_log_file="D:/phpStudy/MySQL/logs/slow.txt"
long_query_time=0.001//超过1毫秒的查询做日志记录
log_queries_not_using_indexes=1//是否记录不使用索引的查询

分析工具
1.自带mysqldumpslow
2.pt-query-digest

系统表实时查看

select id,user,host,db,command,time,state,info from information_schema.processlist;

## MySQL执行顺序
1.发送请求
2.是否有缓存--------》有则返回
3.解析，预处理，优化器生成执行计划
4.存储引擎API查询数据
5.返回客户端

开启查询缓存
query_cache_type=ON 
query_cache_size=0（1024倍数）

查询优化器
表的关联顺序
min()函数，btree左端记录
count() myisam常数
提前终止结果

explain sql语句;//生成sql的优化器处理结果（即执行计划）

mysql查询各个阶段所消耗的时间

文章：https://www.inbeijing.org/archives/1334

set profiling=1
show profiles;
show profile for query query_id;

## mysql事务隔离级别(这个稍显复杂，了解就好)
未提交读
已提交读
可重复读
可串行化

参考知乎文章：https://zhuanlan.zhihu.com/p/117476959/

## mysql优化

优化MYSQL数据库的方法:

1,选取最适用的字段属性,尽可能减少定义字段长度,尽量把字段设置NOT NULL,例如'省份,性别',最好设置为ENUM

2，使用join代替子查询

3，使用联合(UNION)来代替手动创建的临时表

4，事务处理（保证数据完整性,例如添加和修改同时,两者成立则都执行,一者失败都失败）

5，适当建立索引（如何建立索引？索引的利与弊？）

6，优化sql语句

7，explain可以看到mysql执行计划

8，分表（垂直分表，水平分表？）

执行时间，长的

开启慢查询日志，分析具体sql

```