MySQL基础

一、为什么要学习数据库

持久化数据
方便检索
存储大量数据
共享、安全
通过组合分析，获取新的数据

二、数据库的相关概念

分类：DBMS、DB、SQL
1. DB:database数据库，存储一系列有组织数据的容器
1. DBMS：Database Management System数据库管理系统，使用DBMS管理和维护DB
1. SQL：StructureQueryLanguage结构化查询语言，程序员用于和DBMS通信的语言

三、数据库存储数据的特点

1. 数据先放在表中，表再放在库中
1. 一个库可以有多张表，每张表都有自己的唯一标识名
1. 一张表的设计，类似于java中”类"的设计
- 表中的字段的设计，类似于属性的设计
- 表中的单条记录，类似于一个对象
- 表中的所有的记录，类似于对象的集合
orm ：object relation mapping 对象关系映射

四、初始MySQL

MySQL产品的介绍

MySQL 前身属于瑞典的一家公司AB，2008年被sun公司收购，2009年sun被oracle公司收购。
特点：
- ①体积小、安装较方便
- ②开源、免费
- ③性能高、稳定性好
- ④兼容性好

MySQL产品的安装 ★

基于C/S架构的DBMS，需要安装服务端和客户端
www.oracle.com
MySQL5.5

MySQL服务的启动和停止 ★

方式一：图形化
- 右击——计算机管理——服务——MySQL服务
方式二：通过管理员身份运行dos
- net start 服务名
- net stop 服务名

MySQL服务的登录和退出 ★

方式一：通过dos命令
mysql -h主机名 -P端口号 -u用户名 -p密码
注意:
- 如果是本机，则-h主机名可以省略
- 如果端口号是3306，则-P端口号可以省略
方式二：通过图形化界面客户端
- 通过SQLyog/Navicat，直接输入用户名、密码等连接进去即可

###　MySQL的常见命令和语法规范

①常见命令
- show databases 显示当前连接下所有数据库
- show tables 显示当前库中所有表
- show tables from 库名显示指定库中所有表
- show columns from 表名显示指定表中所有列
- use 库名打开/使用指定库
②语法规范
- 不区分大小写
- 每条命令结尾建议用分号
- 注释：
  - #单行注释
  - -- 单行注释
  - /多行注释/

五、DQL语言的学习```````````★````````````

概念：DQL:Data Query Language 数据查询语言

5.1 基础查询 ★

语法：
sql
```
select 查询列表 from 表名;
```
1
特点：
- ①查询结果集是一个虚拟表
- ②查询列表可以是单个字段、多个字段、常量、表达式、函数，可以是以上的组合

引申1：起别名

sql

select 字段名  as  "别名" from 表名;
select 字段名 "别名" from 表名;

引申2：+的作用
- 作用：加法运算
  - 如果两个操作数都是数值型，则直接做加法运算
  - 如果其中一个为非数值型，则将强值转换成数值型，如果转换失败，则当做0
```
'123'+4 => 127
'abc'+4 => 4
null+4 =>4
```
    1
    2
    3
  - 如果其中一个为null，则结果直接为null

引申3：去重

sql

select distinct department_id from employees;

引申4：补充函数

sql

select version();
select database();
select user();
select ifnull(字段名,表达式);
select concat(字符1，字符2,字符3);
select length(字符/字段);获取字节长度

5.2 条件查询 ★

语法：

sql

select 查询列表 
from 表名
where 筛选条件;

筛选条件的分类：：
- ①按条件表达式筛选
  - 关系运算符：>= < <= > <> =
- ②按逻辑表达式筛选
  - 逻辑运算符：and or not
- ③模糊查询
  - like:一般和通配符搭配使用
  - _:任意单个字符
  - %:任意多个字符
  - between and:一般用于判断某字段是否在指定的区间
    - a between 10 and 100
  - in：一般用于判断某字段是否在指定的列表
    - a in(10,30,50)
  - is null:判断null值

5.3 排序查询 ★

语法：

sql

select  查询列表
from 表名
where 筛选条件
order by 排序列表;

特点：
- ①排序列表可以是单个字段、多个字段、函数、表达式、别名、列的索引，以及以上的组合
- ②升序:asc 降序:desc

5.4 常见函数 ★

概述
- 说明：sql中的函数分为单行函数和分组函数
- 调用语法：select 函数名(实参列表);

字符函数

sql

concat(str1,str2,..)    # 拼接字符
substr(str,pos)   # 截取从pos开始的所有字符，   起始索引从1开始
substr(str,pos,len)   # 截取len个从pos开始的字符，起始索引从1开始
length(str)   # 获取字节个数
char_length(str)    # 获取字符个数
upper(str)    # 变大写
lower(str)    # 变小写
trim(【substr from】str)    # 去前后指定字符，默认是去空格
left(str,len)   # 从左边截取指定len个数的 字符
right(str,len)    # 从右边截取指定len个数的 字符
lpad(str,substr,len)    # 左填充
rpad(str,substr,len)：右填充
strcmp(str1,str2)   # 比较两个字符的大小
instr(str,substr)   # 获取substr在str中第一次出现的索引

数学函数

sql

ceil(x)   # 向上取整
floor(x)    # 向下取整
round(x,d)    # 四舍五入
mod(x,y)    # 取模/取余
truncate(x,d)   # 截断，保留小数点后d位
abs(x)    # 求绝对值

日期函数

sql

now():获取当前日期——时间
curtime():只有时间
curdate():只有日期
date_format(date,格式):格式日期为字符
str_to_date(str,格式):将字符转换成日期
datediff(date1,date2):获取两个日期之间的天数差
year(date)
month(date)

5.4 流程控制函数

sql

①if(条件，表达式1，表达式2)：如果条件成立，返回表达式1，否则返回表达式2

case 表达式

sql

②case 表达式
when 值1 then 结果1
when 值2 then 结果2
...
else 结果n
end

case

sql

③case
when 条件1 then 结果1
when 条件2 then 结果2
...
else 结果n
end

5.5 分组函数 ★

说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
sql
```
sum:求和
avg:平均
max:最大
min:最小
count:个数
```
1
2
3
4
5
接收参数特点：
- 实参的字段的类型，sum和avg只支持数值型，其他三个可以支持任意类型
- 这五个函数都忽略null值
- count可以支持以下参数
  - count(字段)：查询该字段非空值的个数
  - count(*) ：查询结果集的行数
  - count(1)：查询结果集的行数
分组函数可以和distinct搭配使用，实现去重的统计
sql
```
select count(distinct 字段) from 表;
```
1

示例：

sql

## 5.5 分组函数        ★    
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM   employees;

SELECT COUNT(employee_id) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT COUNT(salary) FROM employees WHERE salary > 2500;

SELECT COUNT(manager_id) FROM employees;

# 5.1 统计结果集的行数，推荐使用count(*)
SELECT COUNT(*) FROM employees;

SELECT COUNT(*) FROM employees WHERE department_id=30;

SELECT COUNT(1) FROM employees;

SELECT COUNT(1) FROM employees WHERE department_id=30;

SELECT * FROM employees;

#5.2 搭配distinct实现去重统计
# 需求，查询部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;

#5.3 思考：每个部门的总工资、平均工资？需要用到分组查询，只用分组函数无法实现
SELECT SUM(salary) FROM employees WHERE department_id = 30;
SELECT SUM(salary) FROM employees WHERE department_id = 35;

5.6 分组查询 ★

语法：

sql

select 分组函数,分组的字段
from 表名
where 分组前的筛选条件
group by 分组列表
having 分组后的筛选条件
order by 排序列表;

执行顺序： ① from子句 ② where子句 ③ group by子句 ④ having 子句 ⑤ select子句 ⑥ order by子句
特点：
- 分组列表可以是单个字段、多个字段
- 筛选条件分为两类

筛选方式	筛选的基表	使用的关键字	位置
分组前筛选	原始表	where	group by前面
分组后筛选	分组后的结果集	having	group by后面

示例：

sql

## 6、分组查询		   ★	
SELECT SUM(salary),AVG(salary),department_id FROM employees GROUP BY  department_id;	

# 查询每个工种的平均工资
SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

# 查询每个领导的手下人数（有的部门没有领导，需要过滤下）
SELECT COUNT(*),manager_id FROM employees WHERE manager_id IS NOT NULL GROUP BY   manager_id;

# 查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary) 最高工资 ,department_id FROM employees WHERE email LIKE '%a%'  GROUP BY department_id;

# 查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资,manager_id FROM employees WHERE commission_pct IS NOT   NULL GROUP BY manager_id;

# 查询哪个部门的员工个数>5
# 需要使用分组后筛选having
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*) >5;

# 分组函数做条件只可能放在having后面

# 求每个工种没有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NULL GROUP BY  job_id HAVING MAX(salary)>12000;

# 领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT MIN(salary) 最低工资,manager_id FROM employees WHERE manager_id > 102 GROUP  BY manager_id HAVING MIN(salary)>5000;

# 可以实现排序
# 查询每个工种没有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序
SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NULL GROUP BY  job_id HAVING MAX(salary)>6000 ORDER BY MAX(salary) ASC;

# 按多个字段分组
# 查询每个工种每个部门的最低工资，并按先按工种升序再最低工资降序
SELECT MIN(salary),job_id,department_id FROM employees GROUP BY job_id, department_id ORDER BY job_id ASC,MIN(salary) DESC;

5.7 连接查询 ★

说明：当查询中涉及到了多个字段，则需要通过多表连接
笛卡尔乘积：
- 出现原因：没有有效的连接条件
- 解决办法：添加有效的连接条件
分类（MySql）：
- 按年代分类：
  - SQL92标准：仅仅支持内连接
  - SQL99标准【推荐】支持内连接+外连接+交叉连接
  - SQL92和SQL99的区别
    - SQL99使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！
- 按功能分类：
  - 内连接：
    - 等值连接
    - 非等值连接
    - 自连接
  - 外连接：
    - 左外连接
    - 右外连接
    - 全外连接
  - 交叉连接

SQL92语法

语法：

sql

select 查询列表
from 表1 别名,表2 别名,...
where 连接条件
and 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

执行顺序： ① from子句 ② where子句 ③ group by子句 ④ having 子句 ⑤ select子句 ⑥ order by子句

SQL92语法内连接示例：

sql

## 7、连接查询	 	★
USE girls;

# 笛卡尔乘积（错误实例）
# beauty 12行，boys4行，最终输出了12*4=48行。
SELECT `name`,`boyname` FROM beauty,boys;

# 正确查询
SELECT `name`,`boyname` FROM beauty,boys WHERE beauty.boyfriend_id = boys.id;


# 简单的两表连接
USE myemployees;
# 查询员工名和部门名
SELECT * FROM employees;
SELECT * FROM employees e,departments d WHERE e.department_id = d.department_id;
SELECT last_name,department_name FROM employees e,departments d WHERE e.department_id = d.department_id;

# 查询部门编号>100的部门名称和所在的城市名
SELECT department_id,department_name,city FROM departments d,locations l WHERE d.location_id = l.`location_id` AND d.`department_id`>100;

# 查询有奖金的员工名、部门名
SELECT `first_name`,`last_name`,`department_name` FROM `employees` e,`departments` d WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL;

# 查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`  FROM `departments` d,`locations` l WHERE d.`location_id`=l.`location_id` AND l.`city` LIKE '_o%';

# 查询每个城市的部门个数
SELECT COUNT(*),l.city FROM `departments` d,`locations` l  WHERE d.`location_id` = l.`location_id` GROUP BY l.`city`;

# 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.`department_name`,d.`manager_id`,MIN(salary) FROM `employees` e,`departments` d WHERE e.`department_id`=d.`department_id` GROUP BY d.`department_id`;

# 查询哪个部门的员工个数>5，并按员工个数进行降序
SELECT COUNT(*),d.`department_name` FROM `employees` e,`departments` d WHERE e.`department_id`=d.`department_id` GROUP BY d.`department_name` HAVING COUNT(*) >5 ORDER BY COUNT(*) DESC;

SQL99语法

内连接

语法：

sql

# [inner] 可省略
select 查询列表
from 表1 别名
[inner] join 表2 别名 on 连接条件
[inner] join 表3 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表

执行顺序： ① from子句 ② where子句 ③ group by子句 ④ having 子句 ⑤ select子句 ⑥ order by子句

SQL99语法示例：

内连接 -> 等值连接（与SQL92语法对比）

sql

# 内连接 -> 等值连接：SQL99 与 SQL92语法 查询对比
# 查询有奖金的员工名、部门名（SQL92语法）
SELECT `first_name`,`last_name`,`department_name` FROM `employees` e,`departments` d WHERE e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL;

# 查询有奖金的员工名、部门名（SQL99语法）
SELECT `first_name`,`last_name`,`department_name` FROM `employees` e JOIN `departments` d ON e.`department_id` = d.`department_id` WHERE e.`commission_pct` IS NOT NULL;

# 查询哪个部门的员工个数>5，并按员工个数进行降序（SQL92语法）
SELECT COUNT(*),d.`department_name` FROM `employees` e,`departments` d WHERE e.`department_id`=d.`department_id` GROUP BY d.`department_name` HAVING COUNT(*) >5 ORDER BY COUNT(*) DESC;

# 查询哪个部门的员工个数>5，并按员工个数进行降序（SQL99语法）
SELECT COUNT(*),d.`department_name` FROM `employees` e JOIN `departments` d ON e.`department_id`=d.`department_id` GROUP BY d.`department_name` HAVING COUNT(*) >5 ORDER BY COUNT(*) DESC;

内连接 -> 非等值连接

sql

# 内连接 -> 非等值连接（SQL99语法）
# 查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT COUNT(*),`grade` FROM `employees` e JOIN `sal_grade` g ON e.`salary` WHERE e.`salary` BETWEEN g.`min_salary` AND g.`max_salary` GROUP BY g.`grade`;

内连接 -> 自连接

sql

# 内连接 -> 自连接（SQL99语法）
# 查询员工名和对应的领导名
SELECT e.`last_name`,m.`last_name` FROM `employees` e JOIN `employees` m ON e.`manager_id` = m.`employee_id`;

外连接

mysql仅支持左连接、右连接
说明：
- 查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项，如果从表没有匹配项，则显示null
应用场景：
- 一般用于查询主标中有但从表中没有的记录
特点：
- 外连接分主从表，两表的顺序不能任意调换。
- 左连接的话，左边为主表
- 右连接的话，右边为主表

语法

sql

# outer 可省略
select 查询列表
from 表1 别名
left|right [outer] join 表2 别名
on 连接条件
where 筛选条件;

示例：

sql

USE girls;
# 查询所有女神的记录，以及对应的男神名，如果没有对应的男神，则显示为null
# 左连接
SELECT * FROM beauty b LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

# 查询所有男神的记录，以及对应的女神名
# 左连接
SELECT * FROM  boys bo LEFT JOIN beauty b ON b.`boyfriend_id` = bo.`id`;

# 右连接
SELECT * FROM beauty b RIGHT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

# 查询哪个女神没有男朋友，则显示为null
# 左连接
SELECT b.`name` FROM beauty b LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id` WHERE bo.`id` IS NULL;

# 右连接
SELECT b.`name` FROM boys bo RIGHT JOIN beauty b ON b.`boyfriend_id` = bo.`id` WHERE bo.`id` IS NULL;

总结join连接

5.8 子查询 √

含义：一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询, 在外面的查询语句，称为主查询或外查询.
分类:
- select后面（标量子查询）
  - 要求：子查询的结果为单行单列
- from后面（表子查询）
  - 要求：子查询的结构可以为多行多列
- where或having后面 ☆【最常用】
  - 要求：子查询的结构必须为单列
    - 单行子查询
    - 多行子查询
- exists后面（相关子查询）
  - 要求:子查询结构必须为单列

特点：

1. 子查询都放在小括号内
1. 子查询放在条件中，要求必须放在条件的右侧
1. 子查询优先于主查询执行，主查询使用了子查询的执行结果
1. 子查询根据查询结果的行数不同分为以下两类：
- ① 单行子查询
  - 结果集只有一行
  - 一般搭配单行操作符使用：> < = <> >= <=
  - 非法使用子查询的情况：
  - a、子查询的结果为一组值
  - b、子查询的结果为空
- ② 多行子查询
  - 结果集有多行
  - 一般搭配多行操作符使用：any、all、in、not in、min、max
  - in：属于子查询结果中的任意一个就行
  - any和all往往可以用其他查询代替
  sql
```
x > any(10,30,50)
x > min(10,30,50)

x = any(10,30,50)
x in (10,30,50)

x > all(10,30,50)
x > max(10,30,50)
```
  1
  2
  3
  4
  5
  6
  7
  8

示例（放到where后面）：

单行子查询

sql

# 单行子查询
# 查询和 Zlotkey 相同部门的员工姓名和工资
SELECT `last_name`,`salary` FROM `employees` WHERE `department_id` = (
 SELECT `department_id` FROM `employees` WHERE `last_name` = 'Zlotkey'
);

# 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT `employee_id`,`last_name`,`salary` FROM `employees` WHERE `salary`>(
 SELECT AVG(`salary`) FROM `employees`
);

多行子查询

sql

# 多行子查询
# 返回location_id 是1400 或 1700 的部门中的所有员工姓名
SELECT `last_name` FROM `employees` WHERE `department_id` IN (
 SELECT `department_id` FROM `departments` WHERE `location_id` IN (1400,1700)
);

# 返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id 以及 salary
SELECT `employee_id`,`job_id`,`salary` FROM `employees` WHERE `salary` < ANY(
 SELECT DISTINCT `salary` FROM `employees` WHERE `job_id` = 'IT_PROG'
);

示例（放到select后面）：

sql

# 放在select后面
SELECT (
 SELECT COUNT(*) FROM `employees` WHERE `department_id` = 50
) 个数;

示例（放到from后面）：

sql

# 放在from后面
# 查询每个部门的平均工资的工资级别
SELECT dep_ag.`department_id`,dep_ag.ag,`grade` FROM `sal_grade` g JOIN(
 SELECT AVG(`salary`) ag,`department_id` FROM `employees` GROUP BY `department_id`
) dep_ag ON dep_ag.ag BETWEEN g.`min_salary` AND g.`max_salary`;

示例（放在exists后面）：

sql

# 查询有无名字叫"张三丰"的员工信息
SELECT EXISTS(
 SELECT * FROM `employees` WHERE `last_name` = 'Abel'
) 有无Abel;

# 查询没有女朋友的男神信息
USE girls;
SELECT bo.* FROM boys bo WHERE bo.id NOT IN(
 SELECT `boyfriend_id` FROM `beauty` b
);

5.9 分页查询 ★

应用场景：
- 实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

语法：

sql

select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组字段
having 分组后筛选
order by 排序的字段
limit 起始的条目索引，显示的条目数;

执行顺序：

sql

1. from 子句
2. join 子句
3. on 子句
4. where 子句
5. group by 子句
6. having 子句
7. select 子句
8. order by 子句
9. limit 子句

特点：
1. 起始条目索引从0开始，如果不写，则就是从0开始显示
2. limit子句放在查询语句的最后
3. 公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
- 每页显示条目数sizePerPage
- 要显示的页数 page

示例：

sql

USE myemployees; 
# 查询员工信息表的前5条
SELECT  * FROM employees LIMIT 5;
SELECT  * FROM employees LIMIT 0,5;
SELECT  * FROM employees LIMIT 5,5;

# 查询有奖金的，且工资较高的第11名到第20名
SELECT * FROM `employees` WHERE  `commission_pct` IS NOT NULL ORDER BY `salary` DESC;
SELECT * FROM `employees` WHERE  `commission_pct` IS NOT NULL ORDER BY `salary` DESC LIMIT 10,20;

5.10 union联合查询 √

说明当查询结果来自多张表，但多张表之间没有关联，这个时候往往使用联查查询，也称为union查询
引入：
- union 联合、合并

语法：

sql

select 查询列表 from 表1 where 筛选条件 union [all]
select 查询列表 from 表2 where 筛选条件 union [all]
select 查询列表 from 表3 where 筛选条件 union [all]
  ...
select 查询列表 from 表n where 筛选条件;

特点：
1. 多条查询语句的查询的列数必须是一致的
2. 多条查询语句的查询类型、字段意义最好一致
3. union代表去重，union all代表不去重

示例：

sql

SELECT * FROM `jobs` UNION
SELECT * FROM `china` UNION
SELECT * FROM `departments`;

SELECT 1,'test' UNION
SELECT 1,'test' UNION
SELECT 2,'test' UNION
SELECT 2,'test';

SELECT 1,'test' UNION ALL
SELECT 1,'test' UNION ALL
SELECT 2,'test' UNION ALL
SELECT 2,'test';

六、DDL语言的学习```````````★````````````

Data Define Language

6.1 创建数据库

sql

CREATE DATABASE stuDB2;
CREATE DATABASE IF NOT EXISTS stuDB2;

6.2 删除数据库

sql

DROP DATABASE stuDB2;
DROP DATABASE IF EXISTS stuDB2;

6.3 创建表

列类型：
- 1、整型：tinyint/smallint/int/bigint
- 2、浮点型：float(m，n)/double(m，n)/decimal(m，n);m,n可选
- 3、字符型：char(n)/varchar(n)/text n表示最多字符数
- 4、日期型：datet/time/datetime/timestamp
- 5、二进制型
  - blob存储图片数据
sql
```
USE studb;
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT ,
    stuname VARCHAR(20),
    stugender CHAR,
    email VARCHAR(20),
    borndate DATETIME
);
DESC stuinfo;
```
1
2
3
4
5
6
7
8
9
六大约束：
- NOT NULL 非空：用于限制该字段为必填项
- DEFAULT 默认：用于限制该字段没有显式插入值，直接显示默认值
- PRIMARY KEY 主键：用于限制该字段的值不能重复，设置我诶主键列的字段默认不能为空，一个表只能有一个主键
- UNIQUE 唯一：用于限制该字段值不能重复，可为空，一个表可以有n个唯一
- CHECK 检查：用于限制该字段值必须满足指定条件。CHECK(age BETWEEN 1 AND 100)（mysql不支持）
- FOREIGN KEY 外键：用于限制两个表的关系，要求外键列的值必须来自于主表的关联列。
  - ①主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
  - ②主表的关联列要求必须是主键（mysql中也可以是唯一）。
sql
```
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT PRIMARY KEY,	#添加了主键
    stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一约束+非空	#
    stugender CHAR DEFAULT '男',#添加了默认约束
    email VARCHAR(20) NOT NULL,
    age INT CHECK (age BETWEEN 0 AND 100),	#添加了检查越是，mysql不支持，但不报错
    majorid INT,
    borndate DATETIME,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (major_id) REFERENCES major(major_id)#外键要放到最后	
);
```
1
2
3
4
5
6
7
8
9
10

6.4 修改表

修改表名
sql
```
ALTER TABLE stuinfo RENAME TO students;
```
1

添加字段

sql

ALTER TABLE students ADD COLUMN borndate2 TIMESTAMP NOT NULL;

修改字段名

sql

ALTER TABLE students CHANGE COLUMN borndate birthday TIMESTAMP;

修改字段类型

sql

ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP;

删除字段

sql

ALTER TABLE students DROP COLUMN borndate2;

6.5 删除表

sql

DROP TABLE IF EXISTS students;

6.6 复制表

仅复制表的结构：

sql

CREATE TABLE newtable LIKE stuinfo;
CREATE TABLE newtable2 LIKE major;

复制表的结构+数据：

sql

CREATE TABLE newtable3 SELECT * FROM girls.beauty;
CREATE TABLE studb.newtable3 SELECT * FROM girls.beauty; 
CREATE TABLE emp SELECT last_name,department_id,salary FROM myemployees.employees; #复制指定列+数据
CREATE TABLE emp SELECT last_name,department_id,salary FROM myemployees.employees WHERE 1 = 2; #复制指定列（无数据）

七、DML语言的学习

Data Manipulation Language:insert update delete

7.1 数据的插入：

特点： ①字段和值列表一一对应，包含类型、约束等必须匹配 ②数值型的值，不用单引号号；非数值型的值，必须是用单引号。 ③字段顺序无要求
sql
```
insert into 表名（字段名1，字段名2，...）values (值1，值 2，...)
```
1

插入单行：

sql

insert into 表名（字段名1，字段名2，...）values (值1，值 2，...)    

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,major_id)
VALUES(1,'huangweijie','男','1091088708@qq.com',30,1);

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,major_id)
VALUES(2,'zhangwenli','女','1091088708@qq.com',30,2);

INSERT INTO stuinfo(stuid,stuname,email,age)
VALUES(3,'choudan','599499789@qq.com',2);

INSERT INTO stuinfo(stuid,stuname,email,age)
VALUES(4,'dandan','599499789@qq.com',100);

插入多行：

sql

insert into 表名（字段名1，字段名2，...）values (值1，值 2，...),(值1，值 2，...) ,(值1，值 2，...);

自增长列特点：
- 1、自增长列要求必须设置在一个键上，比如主键或者唯一键
- 2、自增长列要求数据类型为数值型
- 3、一个表至多有一个

7.2 数据的删除

delete语句：删除指定数据。

sql

DELETE FROM stuinfo WHERE stuname LIKE 'xiao%';

truncate语句：删除所有数据，但不影响表的结构
sql
```
TRUNCATE TABLE stuinfo;
```
1
delete和truncate区别：
- 1）delete可以添加where条件
- 2）truncate不能添加where条件，一次性清除所有数据
- 3）如果删除带自增长列的表，使用delete删除后，重新插入数据，记录从断点处开始使用truncate删除后，重新插入数据，记录从1开始。
- 4）delete删除数据，可以支持事务回滚 truncate删除数据，不支持事务回滚。

八、TCL语言的学习

8.1 什么事事务？为什么要用事务？

一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败！
默认情况下，每条单独的sql语句就是一个单独的事务。

8.2 事务的四大特性（ACID）

原子性：要么都执行，要么都回滚
一致性：保证数据的状态操作前和操作后保持一致
隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

8.3 分类：

隐式事务：没有明细的开启和结束标记
- 比如dml语句的insert、update、delete语句本身就是一条事务
  sql
```
INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'二年级'),(NULL,'三年级')
```
  1
显示事务：具有明显的开启和结束标记
- 一般由多条sql语句组成，必须具有明显的开启和结束标记
- 步骤：
- 0、取消隐式事务自动开启的功能
- 1、开启事务
- 2、编写事务需要的sql语句（1条或多条）
- 3、结束事务
- 演示：
  sql
```
SET autocommit = 0;
START TRANSACTION;
UPDATE stuinfo SET age = age + 5 WHERE stuname = 'huangweijie';
UPDATE stuinfo SET age = age - 5 WHERE stuname = 'zhangwenli';
COMMIT; 	# 提交
ROLLBACK;   # 回滚
```
  1
  2
  3
  4
  5
  6

8.4 事务和事务处理

MySQL 高级

二、索引优化分析

2.3 索引简介

2.3.1 索引是什么
- MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
  - 索引的目的在于提高查询效率，可以类比字典，
  - 如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
  - 如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
  - 是不是觉得如果没有索引，这个事情根本无法完成？
  - 你可以简单理解为“排好序的快速查找数据结构”。
- 详解(重要)
  - 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
  - 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
    - 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
    - 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
  - 结论
    - 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
2.3.2 优势
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
2.3.3 劣势
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2.3.4 mysql索引结构
- BTree索引
  - 原理图
    - 指针（黄色所示），
    - 如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
    - P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
    - 真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
    - 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
  - 【查找过程】
    - 如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
    - 真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
  - 关于时间复杂度
    - 同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。
- B+Tree索引
  - 原理图
- B树和B+树的区别（B+Tree与B-Tree 的区别）
  - 1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
  - 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。
  - 思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？
    - 1. B+树的磁盘读写代价更低
      - B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
    - 1. B+树的查询效率更加稳定
      - 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
- 聚簇索引与非聚簇索引
  - 概念
    - 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
    - 术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
    - 如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。
  - 聚簇索引的好处：
    - 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的io操作。
  - 聚簇索引的限制：
    - 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
    - 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
    - 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

2.3.5 mysql索引分类

单值索引

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

语法

随表一起建索引：

sql

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)
);

单独建单值索引：

sql

CREATE  INDEX idx_customer_name ON customer(customer_name);

删除索引：

sql

DROP INDEX idx_customer_name  on customer;

唯一索引

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

语法

随表一起建索引：

sql

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);

单独建唯一索引：

sql

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

删除索引：
sql
```
DROP INDEX idx_customer_no on customer;
```
1

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

语法

随表一起建索引：

sql

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
  
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

单独建主键索引：

sql

ALTER TABLE customer 
add PRIMARY KEY customer(customer_no);

删除建主键索引：
sql
```
ALTER TABLE customer 
drop PRIMARY KEY;
```
1
2
修改建主键索引：
- 必须先删除掉(drop)原索引，再新建(add)索引

复合索引

即一个索引包含多个列

语法

随表一起建索引：

sql

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);

单独建索引：

sql

CREATE  INDEX idx_no_name ON customer(customer_no,customer_name);

删除索引：
sql
```
DROP INDEX idx_no_name  on customer ;
```
1

基本语法

创建

sql

CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column))

删除
sql
```
DROP INDEX [indexName] ON mytable;
```
1
查看
sql
```
SHOW INDEX FROM table_name\G
```
1

使用ALTER命令

有四种方式来添加数据表的索引：

sql

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

2.3.6 哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题，组合索引性价比更高
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
2.3.7 哪些情况不要创建索引
- 表记录太少
- 经常增删改的表或者字段
- Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
- Where条件里用不到的字段不创建索引
- 过滤性不好的不适合建索引

2.4 性能分析

2.4.1 Explain
- 是什么(查看执行计划)
  - 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
- 官网介绍
  - http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
- 能干嘛
  - 表的读取顺序
  - 哪些索引可以使用
  - 数据读取操作的操作类型
  - 哪些索引被实际使用
  - 表之间的引用
  - 每张表有多少行被物理查询
- 怎么玩
  - Explain + SQL语句
  - 执行计划包含的信息
- 建表脚本
  sql
```
CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content  VARCHAR(100) NULL ,  PRIMARY KEY (id));

INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));
```
  1
  2
  3
  4
  5
  6
  7
  8
  9
- 各字段解释
  - id
    - select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
    - 三种情况
      - id相同，执行顺序由上至下
      - id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
      - id相同和不同，同时存在
    - 执行顺序
      - id如果相同，可以认为是一组，从上往下顺序执行；
      - 在所有组中，id值越大，优先级越高，越先执行
    - 衍生 = DERIVED
    - 关注点
      - id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。
  - select_type
    - 有哪些
    - 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
      - SIMPLE
        简单的 select 查询,查询中不包含子查询或者UNION
      - PRIMARY
        查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
      - DERIVED
        在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
      - SUBQUERY
        在SELECT或WHERE列表中包含了子查询
      - DEPENDENT SUBQUERY
        在SELECT或WHERE列表中包含了子查询,子查询基于外层
      - UNCACHEABLE SUBQUREY
        sql
        SHOW VARIABLES LIKE '%lower_case_table_names%'; SELECT @@lower_case_table_names FROM DUAL;
      - UNION
        若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
      - UNION RESULT
        从UNION表获取结果的SELECT
  - table
    - 显示这一行的数据是关于哪张表的
  - partitions
    - 代表分区表中的命中情况，非分区表，该项为null
  - type
    - 访问类型排列
      - type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
      - 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
    - system
      - 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
    - const
      - 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量
    - eq_ref
      - 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    - ref
      - 非唯一性索引扫描，返回匹配某个单独值的所有行. 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
    - range
      - 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
    - index
      - 出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
    - all
      - Full Table Scan，将遍历全表以找到匹配的行
    - index_merge
      - 在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中
    - ref_or_null
      - 对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
    - index_subquery
      - 用索引来关联子查询，不再全表扫描。
    - unique_subquery
      - 该联接类型类似于index_subquery。子查询中的唯一索引
    - 备注：一般来说，得保证查询至少达到range级别，最好能达到ref。
  - possible_keys
    - 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
  - key
    - 实际使用的索引。如果为NULL，则没有使用索引
    - 查询中若使用了覆盖索引，则该索引和查询的select字段重叠
  - key_len
    - 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
      sql
      EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name LIKE 'ab%';
    - key_len字段能够帮你检查是否充分的利用上了索引
    - 如何计算
      - 1、先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
      - 2、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘 3,GBK要乘2，
      - 3、varchar这种动态字符串要加2个字节
      - 4、允许为空的字段要加1个字节
        第一组
        key_len=age的字节长度+name的字节长度=4+1 + ( 20*3+2)=5+62=67
        第二组
        key_len=age的字节长度=4+1=5
  - ref
    - 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
  - rows
    - rows列显示MySQL认为它执行查询时必须检查的行数。
    - 越少越好
  - filtered
    - 这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
  - Extra
    - 包含不适合在其他列中显示但十分重要的额外信息
    - Using filesort
      - 出现filesort的情况：
      - 优化后，不再出现filesort的情况：
      - 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
        说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
    - Using temporary
      - 优化前存在 using temporary 和 using filesort
      - 优化前存在 using temporary 和 using filesort 不在，性能发生明显变化：
      - 使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
    - USING index
      - 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
      - 利用索引进行了排序或分组
    - Using where
      - 表明使用了where过滤
    - using join buffer
      - 使用了连接缓存：
    - impossible where
      - where子句的值总是false，不能用来获取任何元组（说明语句可能存在语法错误，这是一个错误的语句）
    - select tables optimized away
      - 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
      - 在innodb中：
      - 在Myisam中：

2.5 查询优化

2.5.2 单表使用索引及常见索引失效

全值匹配我最爱

系统中经常出现的sql语句如下：

sql

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

索引应该如何建立？

建立索引

sql

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)

建立索引前
索引后

最佳左前缀法则
- 如果系统经常出现的sql如下：
  sql
```
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd';
# 或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
```
  1
  2
  3
- 那原来的idx_age_deptid_name 还能否正常使用？
  - 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
    sql
```
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd';
```
    1
  - 虽然可以正常使用，但是只有部分被使用到了。
  - 完全没有使用上索引。
- 结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
- 这两条sql哪种写法更好
  sql
```
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%' 
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc'
```
  1
  2
- 第一种
- 第二种
存储引擎不能使用索引中范围条件右边的列
- 如果系统经常出现的sql如下：
  sql
```
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc';
```
  1
  - 那么索引 idx_age_deptid_name这个索引还能正常使用么？
  - 如果这种 sql 出现较多应该建立：
    sql
```
create index idx_age_name_deptid on emp(age,name,deptid)
# drop index idx_age_name_deptid on emp
```
    1
    2
    - 效果

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

sql

CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc';

is not null 也无法使用索引,但是is null是可以使用索引的
sql
```
UPDATE emp SET age =NULL WHERE id=123456;
```
1

下列哪个sql语句可以用到索引

sql

EXPLAIN SELECT * FROM emp WHERE age IS NULL;
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL;

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
小结：
- 假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b，c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like 'kk%' and c = 4	Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4	Y,只用到a
where a = 3 and b like '%kk%' and c = 4	Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y,使用到a,b,c

一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
- 书写sql语句时，尽量避免造成索引失效的情况
2.5.5 排序分组优化
- 创建了索引
sql
```
create index idx_age_deptid_name on emp (age,deptid,name)
```
1
- 排序的条件
  - 无过滤不索引
  - 顺序错必排序
  - 方向反必排序
    - 以下是否能使用到索引，能否去掉using filesort
      sql
      explain select SQL_NO_CACHE * from emp order by age,deptid; explain select SQL_NO_CACHE * from emp order by age,deptid limit 10; explain select * from emp where age=45 order by deptid; explain select * from emp where age=45 order by deptid,name; explain select * from emp where age=45 order by deptid,empno; explain select * from emp where age=45 order by name,deptid; explain select * from emp where deptid=45 order by age; explain select * from emp where age=45 order by deptid desc, name desc; explain select * from emp where age=45 order by deptid asc, name desc;
    - ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
- 索引的选择
  - 执行案例前先清除emp上的索引，只留主键
  - 查询年龄为30岁的，且员工编号小于101000的用户，按用户名称排序
    sql
```
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME;
```
    1
  - 结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
  - 开始优化：
    - 思路：尽量让where的过滤条件和排序使用上索引
    - 但是一共两个字段(deptno,empno)上有过滤条件，一个字段(ename)有索引
    - 1、我们建一个三个字段的组合索引可否？
      sql
      CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
      - 我们发现using filesort 依然存在，所以name 并没有用到索引。
      - 原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。
      - 所以我们建一个3值索引是没有意义的
    - 2、那么我们先删掉这个索引，DROP INDEX idx_age_empno_name ON emp
      - 为了去掉filesort我们可以把索引建成
        sql
        CREATE INDEX idx_age_name ON emp(age,NAME);
      - 也就是说empno 和name这个两个字段我只能二选其一。
      - 这样我们优化掉了 using filesort。
      - 执行一下sql
        速度果然提高了4倍。 .......
    - 3、但是
      - 如果我们选择那个范围过滤，而放弃排序上的索引呢
      - 建立
        sql
        DROP INDEX idx_age_name ON emp create index idx_age_eno on emp(age,empno);
      - 果然出现了filesort，而且type还是range光看字面其实并不美好。
        我们来执行以下sql
        结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。何故？
        原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。
    - 结论：当范围条件和group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
- 如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序
  - 双路排序
    - MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
    - 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
    - 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
  - 单路排序
    - 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
    - 结论及引申出的问题
    - 由于单路是后出的，总体而言好过双路
    - 但是用单路有问题
  - 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。
  - 本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。
  - 优化策略
    - 增大sort_buffer_size参数的设置
    - 增大max_length_for_sort_data参数的设置
    - 减少select 后面的查询的字段。
    - Why
      - 提高Order By的速度
      - Order by时select * 是一个大忌。只Query需要的字段，这点非常重要。在这里的影响是：
        1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
        1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
      - 尝试提高 sort_buffer_size
        不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
      - 尝试提高 max_length_for_sort_data
        提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整
- GROUP BY关键字优化
  - group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。
2.5.6 最后使用索引的手段：覆盖索引
- 什么是覆盖索引？
  - 简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键
    sql
```
explain select * from emp where name like '%abc';
```
    1
- 使用覆盖索引前
- 使用覆盖索引后

三、查询截取分析

3.1 慢查询日志

3.1.1 是什么
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。
3.1.2 怎么玩
- 说明
  - 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  - 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
- 查看是否开启及如何开启
  - 默认
    - 默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启
  sql
```
SHOW VARIABLES LIKE '%slow_query_log%';
```
  1
  - 开启
    sql
```
set global slow_query_log=1;
```
    1
    - 使用set global slow_query_log=1;开启了慢查询日志只对当前数据库生效，
    - 如果MySQL重启后则会失效。
    - 全局变量设置，对当前连接不影响
    - 对当前连接立刻生效
    - 如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
      - 修改my.cnf文件，[mysqld]下增加或修改参数
        slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件
        slow_query_log =1
        slow_query_log_file=/var/lib/mysql/atguigu-slow.log
  - 关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文host_name-slow.log（如果没有指定参数slow_query_log_file的话）
- 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？
  - 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
    sql
```
SHOW VARIABLES LIKE 'long_query_time%';
```
    1
  - 可以使用命令修改，也可以在my.cnf参数里面修改。
  - 假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，
    - 在mysql源码里是判断大于long_query_time，而非大于等于。
  - 查看当前多少秒算慢
    sql
```
SHOW VARIABLES LIKE 'long_query_time%';
```
    1
  - 设置慢的阙值时间
    sql
```
set long_query_time=1
```
    1
    - 修改为阙值到1秒钟的就是慢sql
- 记录慢SQL并后续分析
  - 实验一条慢sql
  - 跟踪日志信息
  - 查询当前系统中有多少条慢查询记录
    sql
```
show global status like '%Slow_queries%';
```
    1
  - 配置版 my.cnf
    ini
```
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3
log_output=FILE
```
    1
    2
    3
    4
    5

3.1.3 日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
查看mysqldumpslow的帮助信息

mysqldumpslow --help

-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序；
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t:即为返回前面多少条的数据；
-g:后边搭配一个正则匹配模式，大小写不敏感的；

工作常用参考
- 得到返回记录集最多的10个SQL
```
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
```
  1
- 得到访问次数最多的10个SQL
```
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
```
  1
- 得到按照时间排序的前10条里面含有左连接的查询语句
```
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
```
  1
- 另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现爆屏情况
```
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
```
  1

3.2 SHOW PROCESSLIST

3.2.1 能干什么：查询所有用户正在干什么
- 如果出现不顺眼的
- kill [id]

四、工具和技巧拾遗

4.1 视图 VIEW

4.1.1 是什么
- 将一段查询sql封装为一个虚拟的表。
- 这个虚拟表只保存了sql逻辑，不会保存任何查询结果。
4.1.2 作用
- 1、封装复杂sql语句，提高复用性
- 2、逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活
4.1.3 适用场景
- 很多地方可以共用的一组查询结果
- 报表

4.1.4 语法

创建

sql

CREATE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

使用 -> 查询：
sql
```
select * from view_name
```
1

使用 -> 更新：

sql

CREATE OR REPLACE VIEW view_name 
AS
SELECT column_name(s)
FROM table_name
WHERE condition

4.1.5 注意事项(适用5.5)
- mysql的视图中不允许有from 后面的子查询，但oracle可以

五、主从复制

5.1 复制的基本原理

5.1.1 slave会从master读取binlog来进行数据同步
5.1.2 三步骤+原理图
- MySQL复制过程分成三步：
  - 1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
  - 2 slave将master的binary log events拷贝到它的中继日志（relay log）；
  - 3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的
  - 4 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
  - 5 slave将master的binary log events拷贝到它的中继日志（relay log）
  - 6 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

5.2 复制的基本原则

5.2.1 每个slave只有一个master
5.2.2 每个slave只能有一个唯一的服务器ID
5.2.3 每个master可以有多个salve

5.3 复制的最大问题

5.3.1 延时

5.4 一主一从常见配置

5.4.1 mysql版本一致且后台以服务运行
5.4.2 主从都配置在[mysqld]结点下，都是小写

5.4.3 主机修改my.ini配置文件

ini

# 主服务器唯一ID
server-id=1
# 启用二进制日志
# log-bin=自己本地的路径/data/mysqlbin
log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
# 设置不要复制的数据库
binlog-ignore-db=mysql
# 设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
# 设置logbin格式
binlog_format=STATEMENT（默认）
binlog_format=STATEMENT（默认）
binlog_format=ROW

5.4.4 mysql主从复制起始时，从机不继承主机数据

5.4.5 从机配置文件修改my.cnf的[mysqld]栏位下

ini

server-id = 2
# 注意 my.cnf 中有server-id = 1
relay-log=mysql-relay

5.4.6 因修改过配置文件，请主机+从机都重启后台mysql服务
5.4.7 主机从机都关闭防火墙
- windows手动关闭
- 安全工具关上：腾讯管家
- 360不影响
- 关闭虚拟机linux防火墙 systemctl stop firewalld
5.4.8 在Windows主机上建立帐户并授权slave
sql
```
GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456';
```
1
- 查询master的状态
```
show master status;
```
  1
- 记录下File和Position的值
  - 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

5.4.9 在Linux从机上配置需要复制的主机

sql

CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='zhangsan',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;

sql

CHANGE MASTER TO MASTER_HOST='192.168.124.3',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;

启动从服务器复制功能
sql
```
start slave;
show slave status\G;
```
1
2
下面两个参数都是Yes，则说明主从配置成功！
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```
1
2

5.4.10 主机新建库、新建表、insert记录，从机复制
5.4.11 如何停止从服务复制功能
sql
```
stop slave;
```
1
5.4.12 如何重新配置主从
sql
```
stop slave;
reset master;
```
1
2

六、mycat

6.1 Mycat介绍

6.1.1 是什么
- 数据库中间件
- 前身是阿里的cobar
6.1.2 干什么的
- 1、读写分离
- 2、数据分片
  - 垂直拆分
  - 水平拆分
  - 垂直+水平拆分
- 3、多数据源整合
6.1.3 原理
- “拦截”
- Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户
- 这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用mycat还是mysql。

6.2 安装启动

6.2.1 解压缩文件拷贝到linux下 /usr/local/
6.2.2 三个文件
- schema.xml
  - 定义逻辑库，表、分片节点等内容
- rule.xml
  - 定义分片规则
- server.xml
  - 定义用户以及系统相关变量，如端口等.

6.2.3 启动前先修改schema.xml

xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <!--逻辑库    name名称,   checkSQLschema         sqlMaxLimit 末尾是否要加 limit xxx-->
  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
  <!--逻辑库    name名称,   dataHost 引用的哪个dataHost      database:对应mysql的database-->
  <dataNode name="dn1" dataHost="localhost1" database="db1" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"></writeHost>
  </dataHost>
</mycat:schema>

6.2.4 再修改server.xml

xml

<user name="root">
  <property name="password">654321</property>
  <property name="schemas">TESTDB</property>
</user>

6.2.5 验证数据库访问情况

sql

mysql -uroot -p123123 -h 192.168.154.1 -P 3306
mysql -uroot -p123123 -h 192.168.154.154 -P 3306

如本机远程访问报错，请建对应用户

sql

grant all privileges on *.* to root@'缺少的host'  identified by '123123';

6.2.6 启动程序
- 控制台启动：去mycat/bin 目录下 mycat console
- 后台启动：去mycat/bin 目录下 mycat start
6.2.7 启动时可能出现报错
- 域名解析失败
  - 1、用vim 修改 /etc/hosts 文件
    - 在 127.0.0.1 后面增加你的机器名
  - 2、修改后重新启动网络服务

6.2.8 登录

后台管理窗口

mysql -uroot -p654321 -P9066 -h192.168.67.131

命令
sql
```
show database
```
1
- show @@help

数据窗口

mysql -uroot -p654321 -P8066 -h192.168.67.131

6.3 读写分离

6.3.1 schema.xml

xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
  <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
  <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm1" url="192.168.67.1:3306" user="root" password="123123">
      <!--读库（从库）的配置 -->
      <readHost host="hosts1" url="192.168.67.131:3306" user="root" password="123123"></readHost>
    </writeHost>
  </dataHost>
</mycat:schema>

balance
- 负载均衡类型，目前的取值有4 种：
  - 1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
  - 1. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
  - 1. balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
  - 1. balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

6.3.2 读写分离

创建表

sql

create table t_replica 
(id int auto_increment , 
  name varchar(200) 
);

分别在两个库下插入：

sql

insert into t_replica(name) values (@@hostname)

然后再mycat下执行select * from t_replica能够

6.4 分库

6.4.1 如何选择分库表

sql

#客户表  rows:20万 
CREATE TABLE customer(
    id INT AUTO_INCREMENT,
    NAME VARCHAR(200),
    PRIMARY KEY(id)
);

sql

#订单表   rows:600万
CREATE TABLE orders(
    id INT AUTO_INCREMENT,
    order_type INT,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY(id)  
);

sql

#订单详细表     rows:600万
CREATE TABLE orders_detail(
    id INT AUTO_INCREMENT,
    detail VARCHAR(2000),
    order_id INT,
    PRIMARY KEY(id)
);

sql

#订单状态字典表   rows:20
CREATE TABLE dict_order_type(
    id INT AUTO_INCREMENT,
    order_type VARCHAR(200),
    PRIMARY KEY(id)
);

sql

select o.*,od.detail,d.order_type 
from orders  o 
inner join orders_detail  od on o.id =od.order_id  
inner join dict_order_type d on o.order_type=d.id  
where o.customer_id=xxxx

6.4.2 schema.xml

xml

<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
  </schema>
  <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
  <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
  <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native"
    switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm1" url="192.168.67.1:3306" user="root" password="123123">
      <readHost host="hosts1" url="192.168.67.131:3306" user="root" password="123123">
      </readHost>
    </writeHost>
  </dataHost>
  <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"
    switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm2" url="192.168.67.1:3306" user="root" password="123123">
    </writeHost>
  </dataHost>
</mycat:schema>

6.5 水平分表

6.5.1 schema.xml

xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
  </schema>
  <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
  <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
  <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native"
    switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm1" url="192.168.67.1:3306" user="root" password="123123">
      <!-- <readHost host="hosts1" url="192.168.67.131:3306" user="root" password="123123">
      </readHost> -->
    </writeHost>
  </dataHost>
  <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"
    switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm2" url="192.168.67.1:3306" user="root" password="123123">
    </writeHost>
  </dataHost>
</mycat:schema>

6.5.2 rule.xml

xml

<tableRule name="mod_rule">
  <rule>
    <columns>customer_id</columns>
    <algorithm>mod-long</algorithm>
  </rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
  <!-- how many data nodes -->
  <property name="count">2</property>
</function>

6.5.3 跨库join

ER表

为了相关联的表的行尽量分在一个库下

schema.xml

xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schma name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule">
      <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
    </table>
    <table name="dict_status" dataNode="dn1,dn2" type="global"></table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
    <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native"
      switchType="1" slaveThreshold="100">
      <heartbeat>select user()</heartbeat>
      <writeHost host="hostm1" url="192.168.67.1:3306" user="root" password="123123">
        <!-- <readHost host="hosts1" url="192.168.67.131:3306" user="root" password="123123">
        </readHost> -->
      </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"
      switchType="1" slaveThreshold="100">
      <heartbeat>select user()</heartbeat>
      <writeHost host="hostm2" url="192.168.67.1:3306" user="root" password="123123">
      </writeHost>
    </dataHost>
</mycat:schema>

建相关表

全局表
- 设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。
- 所以全局表一般不能是大数据表或者更新频繁的表
- 一般是字典表或者系统表为宜。

schema.xml

xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="customer" dataNode="dn2"></table>
    <table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
    <table name="dict_order_type" dataNode="dn1,dn2" type="global"></table>
  </schema>
  <dataNode name="dn1" dataHost="host1" database="atguigu_mc" />
  <dataNode name="dn2" dataHost="host2" database="atguigu_sm" />
  <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native"
    switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm1" url="192.168.67.1:3306" user="root" password="123123">
      <!-- <readHost host="hosts1" url="192.168.67.131:3306" user="root" password="123123">
      </readHost> -->
    </writeHost>
  </dataHost>
  <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"
    switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostm2" url="192.168.67.1:3306" user="root" password="123123">
    </writeHost>
  </dataHost>

6.5.4 全局序列

三种方式

1.本地文件【不推荐】

2.数据库方式

数据库序列方式原理
- 利用数据库一个表来进行计数累加。
- 但是并不是每次生成序列都读写数据库，这样效率太低
- mycat会预加载一部分号段到mycat的内存中，这样大部分读写序列都是在内存中完成的。
- 如果内存中的号段用完了 mycat会再向数据库要一次。
问：那如果mycat崩溃了，那内存中的序列岂不是都没了？
- 是的。如果是这样，那么mycat启动后会向数据库申请新的号段，原有号段会弃用。
- 也就是说如果mycat重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。

建库序列脚本

win10

sql

CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT NULL,  increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;

sql

DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC  
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER;

DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) 
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER;

SELECT * FROM MYCAT_SEQUENCE

TRUNCATE TABLE MYCAT_SEQUENCE

##增加要用的序列
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);

3.修改mycat配置
```
sequence_db_conf.properties 
vim sequence_db_conf.properties
```
1
2
- 意思是 ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml
- server.xml
  - vim server.xml
- 然后重启
- 插入语句
  sql
```
insert into `orders`(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
```
  1
- 时间戳方式
  - 18位
  - 比较长
  - 自主生成
  - 根据业务逻辑组合
  - 可以利用 redis的单线程原子性 incr来生成序列

sql 语句

<<< @/program/learnMySql.sql

MySQL基础 ​

一、为什么要学习数据库 ​

二、数据库的相关概念 ​

三、数据库存储数据的特点 ​

四、初始MySQL ​

MySQL产品的介绍 ​

MySQL产品的安装 ★ ​

MySQL服务的启动和停止 ★ ​

MySQL服务的登录和退出 ★ ​

五、DQL语言的学习```````````★```````````` ​

5.1 基础查询 ★ ​

5.2 条件查询 ★ ​

5.3 排序查询 ★ ​

5.4 常见函数 ★ ​

5.5 分组函数 ★ ​

5.6 分组查询 ★ ​

5.7 连接查询 ★ ​

5.8 子查询 √ ​

5.9 分页查询 ★ ​

5.10 union联合查询 √ ​

六、DDL语言的学习```````````★```````````` ​

6.1 创建数据库 ​

6.2 删除数据库 ​

6.3 创建表 ​

6.4 修改表 ​

6.5 删除表 ​

6.6 复制表 ​

七、DML语言的学习 ​

7.1 数据的插入： ​

7.2 数据的删除 ​

八、TCL语言的学习 ​

8.1 什么事事务？为什么要用事务？ ​

8.2 事务的四大特性（ACID） ​

8.3 分类： ​

8.4 事务和事务处理 ​

MySQL 高级 ​

二、索引优化分析 ​

2.3 索引简介 ​

2.4 性能分析 ​

2.5 查询优化 ​

三、查询截取分析 ​

3.1 慢查询日志 ​

3.2 SHOW PROCESSLIST ​

四、工具和技巧拾遗 ​

4.1 视图 VIEW ​

五、主从复制 ​

5.1 复制的基本原理 ​

5.2 复制的基本原则 ​

5.3 复制的最大问题 ​

5.4 一主一从常见配置 ​

六、mycat ​

6.1 Mycat介绍 ​

6.2 安装启动 ​

6.3 读写分离 ​

6.4 分库 ​

6.5 水平分表 ​

sql 语句 ​

MySQL基础

一、为什么要学习数据库

二、数据库的相关概念

三、数据库存储数据的特点

四、初始MySQL

MySQL产品的介绍

MySQL产品的安装 ★

MySQL服务的启动和停止 ★

MySQL服务的登录和退出 ★

五、DQL语言的学习```````````★````````````

5.1 基础查询 ★

5.2 条件查询 ★

5.3 排序查询 ★

5.4 常见函数 ★

5.5 分组函数 ★

5.6 分组查询 ★

5.7 连接查询 ★

5.8 子查询 √

5.9 分页查询 ★

5.10 union联合查询 √

六、DDL语言的学习```````````★````````````

6.1 创建数据库

6.2 删除数据库

6.3 创建表

6.4 修改表

6.5 删除表

6.6 复制表

七、DML语言的学习

7.1 数据的插入：

7.2 数据的删除

八、TCL语言的学习

8.1 什么事事务？为什么要用事务？

8.2 事务的四大特性（ACID）

8.3 分类：

8.4 事务和事务处理

MySQL 高级

二、索引优化分析

2.3 索引简介

2.4 性能分析

2.5 查询优化

三、查询截取分析

3.1 慢查询日志

3.2 SHOW PROCESSLIST

四、工具和技巧拾遗

4.1 视图 VIEW

五、主从复制

5.1 复制的基本原理

5.2 复制的基本原则

5.3 复制的最大问题

5.4 一主一从常见配置

六、mycat

6.1 Mycat介绍

6.2 安装启动

6.3 读写分离

6.4 分库

6.5 水平分表

sql 语句