【精华压缩】Mysql精点大全

阳光穿透心脏的1/2处 2024-04-17 22:26 13阅读 0赞

# 一、环境准备 #

## 1.1 软件安装 ##

root@0bf60ac1eb90:/$ rpm -e  --nodeps mariadb-server 
    root@0bf60ac1eb90:/$ rpm -e  --nodeps mariadb
    root@0bf60ac1eb90:/$ tar -xvf  mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar 
    root@0bf60ac1eb90:/$ rm -rf mysql-community-server-minimal-5.7.17-1.el7.x86_64.rpm
    root@0bf60ac1eb90:/$ yum -y  install  perl-Data-Dumper  perl-JSON
    root@0bf60ac1eb90:/$ rpm -Uvh mysql-community-*.rpm
    root@0bf60ac1eb90:/$ rpm -qa  | grep -i mysql

## 1.2 启动服务 ##

root@0bf60ac1eb90:/$ systemctl start mysqld
    root@0bf60ac1eb90:/$ netstat -anultp | grep :3306

## 1.3 获取初始密码并修改 ##

root@0bf60ac1eb90:/$ grep password /var/log/mysqld.log
    root@0bf60ac1eb90:/$ mysql   -uroot   -p"初始密码"
    mysql>set global validate_password_policy=0;
    mysql>set global validate_password_length=6;
    mysql>alter user root@"localhost" identified by "123456";
    mysql>quit

## 1.4 配置文件设置密码策略 ##

root@0bf60ac1eb90:/$ vim /etc/my.cnf
    [mysqld]
    validate_password_policy=0
    validate_password_length=6
    root@0bf60ac1eb90:/$ systemctl restart mysqld

## 1.5 重置密码 ##

mysqladmin -h127.0.0.1 -uroot -p password “123456”

## 1.6 忘记密码 ##

1.配置文件跳过授权表
    root@0bf60ac1eb90:/$ vim /etc/my.cnf
    [mysqld]
    skip-grant-tables
    2.重启服务
    3.执行sql命令更新密码
    mysql> update mysql.user set authentication_string=password("123456") where user="root" and host="localhost";
    mysql> flush  privileges;
    4.将配置文件中"skip-grant-tables"去掉、并重启服务

# 二、基本使用 #

## 2.1 常用命令 ##

<table> 
 <thead> 
  <tr> 
   <th>desc</th> 
   <th>example</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>连接服务器</td> 
   <td>mysql -hlocalhost -uroot -p密码 [库名]</td> 
  </tr> 
  <tr> 
   <td>创建库</td> 
   <td>create database xxx;</td> 
  </tr> 
  <tr> 
   <td>创建表</td> 
   <td>create table xxx.info(name char(10), age int);</td> 
  </tr> 
  <tr> 
   <td>表结构</td> 
   <td>desc xxx.info;</td> 
  </tr> 
  <tr> 
   <td>插记录</td> 
   <td>insert into xxx.info(name, age) values(“jstang”,21),(“alice”, 18);</td> 
  </tr> 
  <tr> 
   <td>改记录</td> 
   <td>update xxx.info set name=jstang,age=18 where id=1;</td> 
  </tr> 
  <tr> 
   <td>查记录</td> 
   <td>select * from xxx.info;</td> 
  </tr> 
  <tr> 
   <td>删记录</td> 
   <td>delete from xxx.info where id=x; (没有where删所有)</td> 
  </tr> 
  <tr> 
   <td>删除表</td> 
   <td>drop table xxx.info;</td> 
  </tr> 
  <tr> 
   <td>删除库</td> 
   <td>drop database xxx;</td> 
  </tr> 
  <tr> 
   <td>查寻库</td> 
   <td>show databases;</td> 
  </tr> 
  <tr> 
   <td>选择库</td> 
   <td>use xxx</td> 
  </tr> 
  <tr> 
   <td>查参数</td> 
   <td>show variables like ‘%char%’;</td> 
  </tr> 
  <tr> 
   <td>建表命令</td> 
   <td>show create table xxx;</td> 
  </tr> 
 </tbody> 
</table>

# 三、MySQL数据类型 #

## 3.1 字符类型 ##

char 255 定长  
varchar 65532 变长  
text 大文本（文章）  
blob 可存二进制资源（音频 视频 图片）

## 3.2 数值类型 ##

create table xxx(month int(2) zerofill)  
长度表示显示宽度(默认11，最大255), 实际长度取决于整型类型  
如上，插入2月份则为02

### 3.2.1 整型(有符号/无符号unsigned) ###

[参考文章][Link 1]

### 3.2.2 浮点类型 ###

整数.小数 总位数  
2088.66 6  
float(n,m) 、double(n,m) n 总位数，m 小数位数  
float(7,2) 99999.99 、 -99999.99

## 3.3 日期时间类型 ##

年 year YYYY 2019(插入值为01-69视为20??、插入值为70-99视为19??)  
日期date YYYYMMDD 20190413  
时间 time hhmmss 123000

日期时间  
timestamp yyyymmddhhmmss 20190413123000  
datetime yyyymmddhhmmss 20190413123000  
区别：timestamp默认是当前系统时间，并且修改其他字段会自动更新timestamp类型的字段时间。

时间函数  
year() date() month() day() time() now()  
年 日期 月 日 时间 当前日期时间

create table info(name char(5), s_year year, s_date date, s_time time, s_datetime datetime);
    insert into info values("test1", "2019", "20190413","123000","20190413123000");
    insert into info values("test2", year(now()),date(now()),time(now()),now());

## 3.4 枚举类型 ##

作用： 字段的值在列举的范围内选择  
enum(值1，值2，值n) 单选  
set(值1，值2，值n) 多选

create table info(
    	sex   enum("man","woman",),
    	hobby  set("game","film","music","book"),
    );

# 四、 字段约束条件 #

作用：限制如何给字段赋值  
Null 是否允许为null 默认null  
Key 索引  
Default 默认值 default 值  
Extra 额外设置

create table info(
    	name char(10) not null,
    	age tinyint unsigned default 25,
    	primary key(name)
    );

# 五、修改表结构： #

## 5.1 添加新字段 ##

alter table  xxx add  addr varchar(255) not null default '' after name;

## 5.2 修改字段类型 ##

alter table xxx modify name varchar(100) not null default '' first;

## 5.3 删除字段 ##

alter table xxx drop addr, drop age;

## 5.4 修改字段名 ##

alter table  xxx change name nickname char(255) not null;

## 5.5 修改表名 ##

alter table xxx rename yyy;

# 六、 Mysql索引 #

## 6.1 介绍 ##

### 6.1.1 索引优点 ###

*  加快数据检索速度
 *  加速表与表的连接
 *  加速分组和排序

### 6.1.2 索引缺点 ###

*  创建、维护索引需耗费时间
 *  索引占用物理空间
 *  表的修改联动索引的修改

### 6.1.3 应用场景 ###

*  在经常需要搜索的列上(经常把做查询条件的字段)，可以加快检索速度
 *  在作为主键的列上，强制该列的唯一行和组织表中数据和排列结构
 *  在经常用在连接的列上，这些列主要为外键，可加快连接速度
 *  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
 *  在经常需要排序的列上，因为索引已排序，查询时利用索引排序加快排序查询时间
 *  在经常使用where子句中的列上面创建索引，加快条件的判断速度

### 6.1.4 索引红线 ###

*  列很少被查询
 *  表数据量小
 *  大数据类型(text，blob)
 *  经常被修改的列

> 更多参考  
> https://www.cnblogs.com/guojin705/archive/2011/06/21/2086429.html  
> https://blog.csdn.net/weixin\_44535476/article/details/89256755

### 6.1.5 索引类型 ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>KEY标志</th> 
   <th>一表多个</th> 
   <th>允许重复</th> 
   <th>可为NULL</th> 
   <th>备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>普通索引*</td> 
   <td>MUL</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>主键*</td> 
   <td>PRI</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>唯一索引</td> 
   <td>UNI</td> 
   <td>是</td> 
   <td>否</td> 
   <td>是</td> 
   <td>限制条件为not null时，则该字段限制与主键相同（KEY标志UNI变为PRI）</td> 
  </tr> 
  <tr> 
   <td>外键*</td> 
   <td>FOR</td> 
   <td>是</td> 
   <td>是</td> 
   <td>否</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

## 6.2 普通索引 ##

创建表时创建索引：

create table (
    	name varchar(255),
    	class varchar(255),
    	index(name),
    	index(class)
    )

在已有表上创建索引

create index tt on info(name);  //create index 索引名 on 表名(字段);

删除索引

drop index tt on info; //drop index 索引名 on 表;

显示索引详情

show index from info;  //show index from 表名;

## 6.3 主键 ##

*  通常与AUTO\_INCREMENT(自增长)连用
 *  经常把能够唯一标识记录的字段作为主键

create table xxx(id int(20) primary key);
    //或者
    create table xxx(
    	id int(20),
    	primary key(id)
    );

复合主键：多个字段都作为PRIMARY KEY,则称为复合主键，必须一起创建。  
应用场景：ip+端口一起做主键，ip+端口不重复即可

create table xxx(
    	cip varchar(15),
    	port smallint(5) unsigned,
    	primary key(cip,port)
    );

删除主键：

alter table xxx drop primary key;

## 6.4 唯一索引 ##

应用场景：身份证

create table xxx(
    	sid varchar(255),
    	unique(sid)
    );

创建、删除unique索引

create unique index 索引名 on 表名(字段名);
    drop index 索引名 on 表名;

## 6.5 外键 ##

作用：给当前表的字段赋值时，只能在另外表的字段值范围内选择  
使用规则：

*  表的存储引擎必须为innodb
 *  字段类型必须匹配
 *  被参考字段为key的一种(primary key)

【example】  
员工表(id, nickname,sex), 工资表(gid, month, pay)  
没有员工就没有工资，所以外键约定于工资表

*  1、建表

create table workers(
    	id int(20) primary key auto_increment,
    	nickname varchar(255) not null,
    	sex enum("man","woman")
    )engine=innodb;
    
    create table salary(
    	id int(20) primary key auto_increment,
    	sid int(20) not null,
    	pay float(7,2),
    	foreign key(sid) references workers(id) on update cascade on delete cascade
    )engine=innodb;

*  2、添加人员到员工表

insert into workers(nickname, sex) values("jstang","man");
    insert into workers(nickname, sex) values("jerry","woman");
    insert into workers(nickname, sex) values("tom","man");

*  3、插入工资记录(在员工表没有员工id记录是没办法插入工资记录的)

insert into salary values(3, 3000);
    insert into salary values(2, 2000);
    insert into salary values(1, 1000);

*  4、员工表id发生变更，工资表对应的sid也发生变更(ON UPDATE CASCADE)

update workers set id=10 where id=1;
    select * from salary where sid=10;
    +-----+---------+
    | sid | pay     |
    +-----+---------+
    |  10 | 1000.00 |
    +-----+---------+

*  5、员工表id记录被删除，工资表对应的sid也发生删除(ON DELETE CASCADE)

delete from workers where id=10;
    select * from salary where sid=10;
    Empty set (0.00 sec)

*  6、显示建表命令(后面删除外键需要用到CONSTRAINT字段)  
    mysql>show create table salary

CREATE TABLE `salary` (
      `sid` int(20) NOT NULL,
      `pay` float(7,2) DEFAULT NULL,
      KEY `sid` (`sid`),
      CONSTRAINT `salary_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `workers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

*  7、删除外键

alter table salary drop foreign key salary_ibfk_1;
    // 上面执行后变成普通索引
    drop index sid on salary;

*  8、添加外键

alter table salary add foreign key(sid) references workers(id) on update cascade on delete cascade;

# 七、存储引擎 #

## 7.1 基本介绍 ##

是mysql自带的功能程序，不同存储引擎有不同功能和数据存储方式;  
引擎选择：读操作多MyISAM，写操作多InnoDB;  
查看当前服务器可用的存储引擎：show engines;  
修改现有表的存储引擎：alter table xxx engine=MyISAM;  
修改数据库服务默认使用的存储引擎:

[root@localhost ~]$ echo ' [mysqld] default-storage-engine=myisam' >> /etc/my.cnf
    [root@localhost ~]$ systemctl restart mysqld

## 7.2 引擎特点 ##

### 7.2.1 MyISAM ###

不支持外键、行级锁、事务  
支持表级锁、独享表空间  
表名.frm 表结构 DESC 表名  
表名.MYD 表记录 select \* from 表名;  
表名.MYI 表索引

### 7.2.2 InnoDB ###

支持外键、行级锁、事务  
共享表空间  
表名.frm 表结构  
表名.idb 表记录+表索引

## 7.3 锁和事务 ##

锁用来解决并发冲突的问题  
类型：

*  读锁(select)共享锁
 *  写锁(insert,update delete)互斥锁

粒度

*  表级锁
 *  行级锁

事务：一次sql从开始到结束的过程  
事务回滚：任意一步操作失败，恢复之前所有的操作  
事务文件：ib\_logfile0、ib\_logfile1、ibdata1

# 8、数据处理 #

## 8.1 数据导入 ##

将/etc/password文件内容导入到数据库

root@0bf60ac1eb90:/var/lib/mysql$ cat /etc/passwd
    root:x:0:0:root:/root:/bin/bash
    daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin

*  建表

mysql> CREATE TABLE `sysuser` (
      `name` char(30) DEFAULT NULL COMMENT '用户名',
      `passwd` char(1) DEFAULT NULL COMMENT '密码占位符',
      `uid` smallint(5) unsigned DEFAULT NULL COMMENT '用户id',
      `gid` smallint(5) unsigned DEFAULT NULL COMMENT '组id',
      `description` varchar(255) DEFAULT NULL COMMENT '描述',
      `homedir` varchar(255) DEFAULT NULL COMMENT '家目录',
      `shell` char(30) DEFAULT NULL COMMENT 'shell',
      index(name)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='系统用户表'

*  查看文件导入导出限制([了解更多][Link 2])

*  导入数据库

root@0bf60ac1eb90:~$ cp /etc/passwd /var/lib/mysql-files/
    load data infile "/var/lib/mysql-files/passwd" into table test.sysuser fields terminated by ":" lines terminated by "\n";

## 8.2 数据导出 ##

select * from sysuser into outfile "/var/lib/mysql-files/xx.txt";  
    root@0bf60ac1eb90:~$ cat /var/lib/mysql-files/xx.txt
    root x 0 0 root /root /bin/bash
    daemon x 1 1 daemon /usr/sbin /usr/sbin/nologin

## 8.3 条件查询 ##

> 数字也是字符  
> NULL字段携带where查询不参与匹配  
> (续)例如表记录(“jstang”, 18),(NULL, 20); select \* from xxx where name !=jstang; 的结果为empty

*  数字比较 >、>=、=、<=、<
 *  字符比较 =、!=
 *  范围比较 between 数字1 and 数字2、in (字符1，字符2)、not in (字符1，字符2)
 *  空值比较 is null、is not null
 *  逻辑匹配 and、or、not(逻辑非 `select * from xxx where not name="jstang";` )；等同于 !=
 *  忽略重复字段值 distinct (`select distinct age from xxx;`)
 *  正则匹配 `select * from sysuser where name regexp '^...$';` 筛选name为三个任意字符的记录
 *  模糊匹配 %多个、\_单个 `select * from sysuser where name like '%bod_';` 可匹配nobody
 *  聚集函数 max、min、avg、sum、count，`select max(id) from sysuser where id<10;` 先where再函数
 *  查询排序 asc(升序)、desc(降序) `select * from sysuser order by id desc;`
 *  查询分组 `select shell from sysuser where id<10 group by shell;` 查询id小于10的所有shell路径
 *  四则运算 `select (2021-id)%2,(2021+id)/2 as "asname" from sysuser where name="root";`

+-------------+-----------+
    | (2021-id)%2 | asname    |
    +-------------+-----------+
    | 	0 	      | 1011.0000 |
    +-------------+-----------+

*  记录限制

select * from sysuser limit 10;     // 从第1条开始，取10条
    select * from sysuser limit 20,10;  // 从第20条开始，取10条

*  复制表(备份表、快速建表)

create table 库.表 sql查询;
    create table test.newtable select name,shell from sysuser where id<10;

*  where嵌套查询

select * from salary where pay<(select avg(pay) from salary); // 查询低于平均工资的记录

*  多表查询

select worker.name,salary.pay,salary.month from worker,salary where worker.id=salary.uid;  // 查询员工名及其各个月工资

*  连接查询  
    ![在这里插入图片描述][20210609195636454.PNG_pic_center]

select * from worker left join salary on worker.id=salary.uid;           #左连接
    +------+--------+------+------+---------+-------+
    | id   | name   | id   | uid  | pay     | month |
    +------+--------+------+------+---------+-------+
    | 1000 | jstang |    1 | 1000 | 6500.00 |    11 |
    | 1001 | alice  |    2 | 1001 | 8500.00 |    11 |
    | 1000 | jstang |    3 | 1000 | 9000.00 |    12 |
    | 1001 | alice  |    4 | 1001 | 9000.00 |    12 |
    | 1002 | tom    | NULL | NULL |    NULL |  NULL |
    +------+--------+------+------+---------+-------+
    
    select * from worker right join salary on worker.id=salary.uid;          #右连接
    +------+--------+----+------+---------+-------+
    | id   | name   | id | uid  | pay     | month |
    +------+--------+----+------+---------+-------+
    | 1000 | jstang |  1 | 1000 | 6500.00 |    11 |
    | 1000 | jstang |  3 | 1000 | 9000.00 |    12 |
    | 1001 | alice  |  2 | 1001 | 8500.00 |    11 |
    | 1001 | alice  |  4 | 1001 | 9000.00 |    12 |
    +------+--------+----+------+---------+-------+

# 九、用户授权及撤销 #

创建用户: create user xxx identified by ‘123456’;

## 9.1 用户授权 ##

grant 权限列表 on 数据库表 to 用户名@"IP" identified by "密码" with grant option; grant all on *.* to root@"20.18.03.26" identified by "123456" with grant option; grant select, update(name,age) on test.worker to root@"127.0.0.1" identified by "123456"

*  权限列表：all, usage, select, insert, update(字段名列表)
 *  数据库表： *.*，库名.\* 库名.表名
 *  with grant option：有授权权限

mysql> select user();  // 显示当前登录用户及地址
    mysql> show grants [for 用户@“IP”];    // 显示当前用户的授权信息

myslq默认库表

*  mysql 授权库
 *  mysql.user 存储已有的授权用户
 *  mysql.db 存储用户对库的访问权限
 *  mysql.tables\_priv 存储授权用户对表的访问权限
 *  mysql.columns\_pri 存储授权用户对字段的访问权限

## 9.2 权限撤销 ##

mysql> revoke 权限列表 on 数据库 from 用户@"IP" mysql> revoke all on *.* from hhx@"%"; mysql> revoke grant option on test.* from hhx@"%"; mysql> show grants for hhx@"%";

删除授权用户

mysql> delete from mysql.user where user="hhx" and host="%";
    mysql> flush privileges;
    mysql> drop user hhx@"%";

授权用户登录后，修改本用户密码:

mysql> set password=password("123456");

管理员账户重置授权用户密码

mysql> set password for hhx@"%"=password("123456");

# 十、Mysql性能调优 #

## 10.1 Mysql工作原理 ##

*  功能模块

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>desc</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>连接池</td> 
   <td>监听客户端连接请求，并判断是否有空闲线程和资源响应</td> 
  </tr> 
  <tr> 
   <td>SQL接口</td> 
   <td>客户端输入的sql命令传递给mysql服务处理</td> 
  </tr> 
  <tr> 
   <td>分析器</td> 
   <td>检测sql语法的正确性</td> 
  </tr> 
  <tr> 
   <td>优化器</td> 
   <td>对执行的sql命令进行优化处理，以最优的算法和节省系统资源的方式执行</td> 
  </tr> 
  <tr> 
   <td>查询缓存</td> 
   <td>在物理内存划分空间，存储查询完毕后的结果</td> 
  </tr> 
  <tr> 
   <td>存储引擎</td> 
   <td>表处理器(myisam、innodb)</td> 
  </tr> 
  <tr> 
   <td>文件系统</td> 
   <td>硬盘</td> 
  </tr> 
  <tr> 
   <td>管理工具</td> 
   <td>mysql 提供的命令, 如mysqladmin、mysqldump</td> 
  </tr> 
 </tbody> 
</table>

*  查询过程  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTAwNTY1_size_16_color_FFFFFF_t_70_pic_center]

## 10.2 Mysql性能调优 ##

数据库响应客户端请求慢的原因：

*  带宽
 *  服务器硬件(CPU、内存、硬盘IO)
 *  软件调优( `show variables like "%timeout%"; set global connect_timeout=9;`)
 *  sql查询命令

### 10.2.1 软件调优 ###

*  并发及连接控制

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>max_connections</td> 
   <td>最大并发连接数, 默认151, 最优设置Max_used_connections/0.85</td> 
  </tr> 
  <tr> 
   <td>connect_timeout</td> 
   <td>连接超时, 默认10s</td> 
  </tr> 
  <tr> 
   <td>wait_timeout</td> 
   <td>等待关闭连接的不活动时间, 默认28800s(8小时)</td> 
  </tr> 
 </tbody> 
</table>

> 查看曾经最大连接数： show global status like “Max\_used\_connections”;  
> 清空status：flush status;

*  缓存参数控制  
    缓冲区、线程数量、开表数量

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>key_buffer_size</td> 
   <td>索引缓存大小，默认(8388608byte)8M</td> 
  </tr> 
  <tr> 
   <td>sort_buffer_size</td> 
   <td>排序缓存大小，默认(262144byte)256k</td> 
  </tr> 
  <tr> 
   <td>read_buffer_size</td> 
   <td>为顺序读取表记录保留的缓存大小</td> 
  </tr> 
  <tr> 
   <td>thread_cache_size</td> 
   <td>缓存中可被重用的线程数量</td> 
  </tr> 
  <tr> 
   <td>table_open_cache</td> 
   <td>缓存中打开的表的数量</td> 
  </tr> 
 </tbody> 
</table>

*  查询缓存  
    默认关闭，通常由redis、mongodb等内存型数据库服务器取代。  
    相关参数：show variables like “%query\_cache%”;

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>query_cache_limit</td> 
   <td>查询缓存大小上限，默认1M</td> 
  </tr> 
  <tr> 
   <td>query_cache_min_res_unit</td> 
   <td>缓存最小单元块大小，例如4k一块，现数据为7k则分在两块存储，块越小，查询越慢、但节省空间</td> 
  </tr> 
  <tr> 
   <td>query_cache_size</td> 
   <td>查询缓存大小, 默认1M</td> 
  </tr> 
  <tr> 
   <td>query_cache_type</td> 
   <td>类型， 默认OFF; 可选值有0,1,2,，0为OFF，1为ON，2需要sql查询加入sql_query_cache字段</td> 
  </tr> 
  <tr> 
   <td>query_cache_wlock_invalidate</td> 
   <td>写锁，主针对myisam，如数据发生变更，则停止从查询缓存读数据，待变更完再把数据重写入查询缓存</td> 
  </tr> 
 </tbody> 
</table>

*  查询缓存状态信息  
    查询缓存命中率： Qcache\_hits / Qcache\_inserts  
    相关参数：show global status like “%qcache%”;

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Qcache_free_blocks</td> 
   <td>查询缓存空闲块</td> 
  </tr> 
  <tr> 
   <td>Qcache_free_memory</td> 
   <td>查询缓存空闲内存</td> 
  </tr> 
  <tr> 
   <td>Qcache_hits</td> 
   <td>客户端从查询缓存中找到记录并返回</td> 
  </tr> 
  <tr> 
   <td>Qcache_inserts</td> 
   <td>客户端查询请求次数</td> 
  </tr> 
  <tr> 
   <td>Qcache_lowmem_prunes</td> 
   <td>查询缓存满后，记录被删条数</td> 
  </tr> 
  <tr> 
   <td>Qcache_not_cached</td> 
   <td>sql查询没放入查询缓存次数</td> 
  </tr> 
  <tr> 
   <td>Qcache_total_blocks</td> 
   <td>查询缓存一共有几个块</td> 
  </tr> 
 </tbody> 
</table>

### 10.2.2 SQL查询优化 ###

*  Mysql日志类型  
    错误日志(默认开启)、binlog日志、查询日志(记录所有sql命令)、慢查询日志(记录耗时或不使用索引的查询操作)
 *  启动查询日志

set global general_log=on;

*  慢查询日志

set global slow_query_log=on;  // 启动慢查询日志
    set global log_queries_not_using_indexes=on; // 开启"没有使用索引的查询记录"写入慢查询日志 
    set global long_query_time=5;  // 查询"耗时上限"会写入慢查询日志，默认10s
    root@0bf60ac1eb90:/var/lib/mysql# mysqldumpslow 0bf60ac1eb90-slow.log // 统计慢查询日志

# 十一、视图 #

视图的作用：当需要提供表数据，但是仅允许提供部分数据，此时需要用到视图  
什么是视图：是一张虚拟表，本身没有实际的数据存储形式(无ibd记录数据，仅有frm表结构数据)，由后端表(基表)动态生成。基表或者视图数据发生变更，其对应的基表和视图也会进行更新。

## 11.1 视图的优点 ##

*  简单：视图的数据是已经过滤好的结果集；
 *  安全：用户只能看到视图中的数据；
 *  数据独立：一旦视图结构确定了，可以屏蔽表结构变化对用户的影响；

## 11.2 使用限制 ##

不能在视图上创建索引；  
在视图的FROM子句中不能使用子查询；  
下列情况中，视图无法更新：

*  包含以下关键字的SQL语句：聚合函数(sum\\min\\max\\count)、distinct、group by、having、union、union all
 *  常量视图
 *  join
 *  from一个不能更新的视图
 *  where子句中的子查询引用了from子句中的表
 *  使用了临时表、视图是不可更新

## 11.3 基本使用 ##

**ALGORITHM**：定义出来视图的方式(undefined,merge,teamptable)

*  undefined（未定义）：表示使用merge替换方法（默认值）
 *  merge（替换方式）：视图名直接使用视图的公式替换掉，把视图公式合并到了select中
 *  temptable（具体化方式）：先得到视图的执行结果，该结果形成一个中间结果暂时存在内存中，之后，外面的select语句就调用了这些中间结果。

**With Check Option**: 定义对视图表里的数据作操作时的限制方式。意思是改数据不能随便改得受限制；

*  “”: 空使用默认值
 *  Local：仅检查当前视图的限制
 *  Cascade：同时要满足基表的限制(默认值)  
    创建视图

create [ALGORITHM=?] view 视图名称 as SQL查询 [with ? check option];
    create view 视图名称(字段名列表1) as select (字段名列表2) from xxx;
    create [or replace] view t1 as select * from sysuser;  // 若视图已存在则替换(覆盖)

查看视图

mysql> show table status where comment="view"\G;  //当前库下哪种表是视图表
    mysql> show create view t1; // 或者show create table t1; 当前视图是基于哪张表(视图创建命令)

使用视图  
视图的使用跟表的使用一样。

# 十二、存储过程 #

## 12.1 基本使用 ##

什么是存储过程：

*  数据库中保存的一系列sql命令的集合
 *  编写存储过程时，可以使用变量、条件判断、流程控制等
 *  存储过程就是mysql中的脚本

存储过程的优点：

*  避免重复的SQL操作
 *  防止对表的直接访问
 *  可以减轻网络负担，无需输入sql数据，直接运行脚本即可

创建存储过程：

mysql> delimiter //
    mysql> create procedure pro1()
        -> begin
        -> select * from test.worker;
        -> select * from test.salary;
        -> end
        -> //
    Query OK, 0 rows affected (0.04 sec)
    mysql> delimiter ;   //注意分号前要空格

查看存储过程

mysql> show procedure status\G; // 查所有
    mysql> select db,name,type from mysql.proc; // 查所有
    mysql> select db,name,type,body from mysql.proc where name="say1"\G;  // 查看具体的函数内容

调用\\删除存储过程

mysql> call pro1();
    mysql> drop procedure pro1();

## 12.2 变量 ##

系统/全局、会话、自定义变量

mysql> show global variables; // 全局、系统变量
    mysql> show session variables; //会话变量
    mysql> set session sort_buffer_size = 40000; // 修改会话变量
    mysql> set @val=10; select @val;   // 自定义变量赋值和查询
    mysql> select uid into @val from salary where id=1;  // sql查询结果赋值给自定义变量
    mysql> select @@hostname;  // 获取系统变量

局部变量

mysql> delimiter //
    mysql> create procedure pro2()
    	-> begin
    	-> declare pid int default 10;
    	-> declare part char(10);
    	-> set part="局部变量";
    	-> select pid;
    	-> select part;
    	-> end
    	->//
    mysql> delimiter ;

参数变量

*  in(输入类型参数，默认)， 可传值、变量。过程中无法变更值
 *  out(输出类型参数)，只能传变量。过程中可变更值。变量即使有值也仅传入空的接收载体
 *  inout(输入输出参数)，只能传变量。过程中可变更值。变量不会传入空的载体而是本身值的载体

mysql> delimiter //
    mysql> create procedure pro2(in param char(20))
    	-> begin
    	-> select name from test.sysuser=param;
    	-> end
    	->//
    mysql> delimiter ;

加减乘除、整除DIV、求余%

mysql> select @val1,@val2;
    +-------+-------+
    | @val1 | @val2 |
    +-------+-------+
    |    10 |     3 |
    +-------+-------+
    mysql> select @val1/@val2;      // 得 3.3333
    mysql> select @val1 DIV @val2;  // 取整数3
    mysql> select @val1 % @val2;    // 得1

## 12.2 条件、控制语句 ##

if、if else

mysql> delimiter //
    mysql> create procedure pro1(in param char(20))
    	-> begin
    	-> if param is null then
    	-> select name from test.sysuser;
    	-> else
    	-> select name from test.sysuser where id=param;
    	-> end if;
    	-> end
    	->//
    mysql> delimiter ;

while循环

mysql> delimiter //
    mysql> create procedure pro2()
        -> begin
        -> declare i int;
        -> set i=1;
        -> while i<=5 do
        -> select i;
        -> set i=i+1;
        -> end while;
        -> end
        -> //
    mysql> delimiter ;

loop无条件循环

mysql> delimiter //
    mysql> create procedure pro3()
        -> begin
        -> declare i int;
        -> set i=1;
        -> loop
        -> select i;
        -> set i=i+1;
        -> end loop;
        -> end
        -> //
    mysql> delimiter ;

repeat until循环

mysql> delimiter //
    mysql> create procedure pro4()
        -> begin
        -> declare i int;
        -> set i=1;
        -> repeat
        -> select i;
        -> set i=i+1;
        -> until i=6	
        -> end repeat;
        -> end
        -> //
    mysql> delimiter ;

leave 标签名(break)退出循环  
iterate 标签名(continue)结束本次循环，进入下循环

mysql> delimiter //
    mysql> create procedure pro5()
        -> begin
        -> declare i int;
        -> set i=1;
        -> lab:while i<=5 do
        -> select i;
        -> leave lab;
        -> end while;
        -> end
        -> //
    mysql> delimiter ;

# 十三、分库分表 #

## 13.1 概述 ##

解决单服务器访问压力和存储压力  
解决单表负载过大的问题  
数据切片模式：

*  垂直：专库专用，不同的库（主机）分担不同的业务，由一库x个业务=>（一库一个业务）\*x
 *  水平：数据行切分(分片)，由一个表的x条数据=>分散到多个库中同名表中。客户端操作网关(非数据库)，由网关决定往哪个数据库存取

## 14.2 [Mycat数据库中间件][Mycat] ##

Mycat是基于Java的分布式数据库系统中间层，为高并发下的分布式提供解决方案

*  支持jdbc形式连接
 *  支持mysql、oracle、sqlserver、mongodb
 *  提供数据读写分离服务
 *  可以实现数据库服务器的高可用
 *  提供数据分片服务
 *  基于阿里巴巴cobar进行研发的开源软件
 *  适合大量写入数据的存储需求(利写不利读)

缺点：

*  降低读速度，因为网关要从多个实例中收集

Mycat 常用的分片规则:

*  分片枚举【sharding-by-intfile】
 *  固定分片hash 算法【rule1】
 *  范围约定【auto-sharding-long】
 *  取 模【mod-long】
 *  按日期（天）分片【sharding-by-date】
 *  取模范围约束【sharding-by-pattern】
 *  截取数字做 hash 求模范围约束【sharding-by-prefixpattern】
 *  应用指定【sharding-by-substring】
 *  截取数字 hash 解析【sharding-by-stringhash】
 *  一致性 hash【sharding-by-murmur】
 *  按单月小时拆分【sharding-by-hour】
 *  范围求模分片【auto-sharding-rang-mod】
 *  日期范围 hash 分片【rangeDateHash】
 *  冷热数据分片【sharding-by-hotdate】
 *  【sharding-by-month】

配置文件

*  rule.xml：定义分片规则
 *  server.xml：设置连接mycat服务的账户、密码等
 *  schema.xml：对哪个数据库下的哪个表进行分片

## 13.3 分片部署案例 ##

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>ip</th> 
   <th>db</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>mycat</td> 
   <td>192.168.122.10</td> 
   <td>数据库中间件</td> 
  </tr> 
  <tr> 
   <td>mysql-0</td> 
   <td>192.168.122.11</td> 
   <td>db1</td> 
  </tr> 
  <tr> 
   <td>mysql-1</td> 
   <td>192.168.122.12</td> 
   <td>db2</td> 
  </tr> 
 </tbody> 
</table>

\#步骤：

*  0、下载mycat包并安装jdk

[root@mycat ~]$ wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
    [root@mycat ~]$ yum install java -y
    [root@mycat ~]$ tar -xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

*  1、定义连接myca服务的用户和密码及虚拟数据库名称

[root@mycat mycat]$ cat conf/server.xml
    ...
    <user name="root">  
    	<property name="password">123456</property> 
    	<property name="schemas">TESTDB</property> 
    </user>
    ...

*  2、对哪些表进行数据分片及使用的分片规则

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
    		<table name="travelrecord" dataNode="dn1,dn2" rule="auto-sharding-long" />
    		<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2" />
    		<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
    		<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2" rule="mod-long" />
    		<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
    		<table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile">
    			<childTable name="orders" primaryKey="ID" joinKey="customer_id" parentKey="id">
    				<childTable name="order_items" joinKey="order_id" parentKey="id" />
    			</childTable>
    			<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id" parentKey="id" />
    		</table>
    	</schema>
    	<dataNode name="dn1" dataHost="mysql-0" database="db1" />
    	<dataNode name="dn2" dataHost="mysql-1" database="db2" />
    	<dataHost name="mysql-0" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat <writeHost host="hostM1" url="192.168.122.11:3306" user="root" password="123456">
    		</writeHost>
    	</dataHost>
    	<dataHost name="mysql-1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    		<heartbeat>select user()</heartbeat>
    		<writeHost host="hostM1" url="192.168.122.12:3306" user="root" password="123456">
    		</writeHost>
    	</dataHost>
    </mycat:schema>

*  3、启动服务，查看服务信息

[root@mycat ~]$ /usr/local/mycat/bin/mycat start
    Starting Mycat-server...
    [root@mycat ~]$ ps -elf| grep java  && netstat -anultp | grep 8066

*  4、根据文件创建分片表  
    根据schema.xml文件创建表，以employee表举例

由以上得知表主键为id，分片规则为sharding-by-intfile，进入rule.xml文件查看规则

<tableRule name="sharding-by-intfile">
    	<rule>
    		<columns>sharding_id</columns>
    		<algorithm>hash-int</algorithm>
    	</rule>
    </tableRule>
    <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    	<property name="mapFile">partition-hash-int.txt</property>
    </function>

由以上得知分片规则以sharding\_id字段，算法为hash-int，对应函数读取的文件为partition-hash-int.txt，打开该文件

10000=0
    10010=1

意思是sharding\_id字段值为10000的存在db1，10010的存在db2；  
综上所述，employee表中，id为主键，sharding\_id字段类型为int，并只有10000,10010的枚举值，据此在mycat进行建表；  
连接mycat数据库中间件

mysql -h192.168.122.10 -P8066 -uroot -p
    mysql> use TESTDB
    mysql> create table employee(
    	-> id int(11) primary key,
    	-> sharding_id int(11) not null,
    	-> name varchar(100),
    	-> age tinyint(3) unsigned);
    mysql> insert into employee(id,sharding_id,name,age) values(1,10000,"jstang",18),(2,10010,"alice",16);

*  5、客户端测试  
    连接mysql-0的db1库查看employee表（mysql-1同理）

mysql -h192.168.122.11 -p
    mysql> select * from db1.employee;
    +----+-------------+--------+------+
    | id | sharding_id | name   | age  |
    +----+-------------+--------+------+
    |  1 |       10000 | jstang |   18 |
    +----+-------------+--------+------+

发现仅有一条数据。那么第二条则在db2库上。验证完毕  
**Help**

*  Failed to parse byte string: -3715333734B：[《解决方案》][Link 3]
 *  rang-long、mod-long size:3>2: 分别修改autopartition-long.txt里去掉最后一行、rule.xml的function为"mod-long"的值为2。

[Link 1]: https://www.cnblogs.com/wayne173/p/3747477.html
[Link 2]: https://cloud.tencent.com/developer/article/1064439
[20210609195636454.PNG_pic_center]: https://img-blog.csdnimg.cn/20210609195636454.PNG#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTAwNTY1_size_16_color_FFFFFF_t_70_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/00e61b211fee4166abf9894a3c4e00ba.png
[Mycat]: http://mycat.org.cn/document/mycat-definitive-guide.pdf
[Link 3]: https://blog.csdn.net/u013716179/article/details/89886452