MySQL

1. 对数据库的常用操作

1. 查看所有的数据库
   • show databases;
2. 创建数据
   • create database [if not exists] mydatabase [charset=utf8];
3. 却换（选择要操作的数据库）
   • use mydatabase;
4. 删除数据库
   • drop database [if exists] mydatabase;
5. 修改数据库编码
   • alter database mydatabase character set utf8’

2. 对表结构的常用操作

2.1 创建表

create table [if not exists]表名(
	字段名1 类型 [(宽度)] [约束条件] [comment '字段说明'],
    字段名2 类型 [(宽度)] [约束条件] [comment '字段说明'],
    字段名3 类型 [(宽度)] [约束条件] [comment '字段说明'],
)[表的一些设置];

创建表是构建一张块表，指定这个表的名字，这个表有几列，每一列叫什么名字，以及每一列存储的数据类型。

2.2 查看表

1. 查看当前数据库的所有表的名称
   • show tables；
2. 查看指定某个表的创建语句
   • show create table 表名；
3. 查看表结构
   • desc 表名；
4. 删除表
   • drop table 表名；

2.3 修改表结构格式

修改表添加列

语法格式

alter table 表名 add 列名 类似(长度) [约束];

例子：

为student表添加一个新的字段为：系别 dept 类型为 varchar(20)

alter table student add 'dept' varchar(20);

修改列名和类型

语法格式

alter table 表名 change 旧列名 新列名 类型(长度) 约束;

例子：为student表的dept字段更换为department varchar(30)

alter table student change 'dept' department varchar(30);

修改表删除列

语法格式：

alter table 表名 drop 列名;

例如：删除student表中的department这列

alter table student drop department;

修改表名

语法格式：

rename table 表名 to 新表名;

例子： 将表student改名为stu

rename table 'student' to stu

3. 数据库基本操作-DML

DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。

3.1 数据插入

语法格式：

// 向表中插入某些列
insert into 表名(列名1, 列名2, 列名3...) values (值1, 值2, 值3); 

// 向表中插入所有列
insert into 表名(值1, 值2, 值3);

例子：

insert into student(sid,name,gender,age,birth,adderss) values(1001,'man',18,'1996-12-23','beijing');
insert into student values(1001,'男', 18,'1996-12-23','北京');

3.2 数据修改

语法格式：

update 表名 set 字段名=值, 字段名=值...;
update 表名 set 字段名=值, 字段名=值... where 条件;

例子：

# 将所有学生的地址修改为曹县
update student set address = '曹县';

# 将id为1004的学生的地址修改为青岛
update student set address = '青岛' where id = 1004;

# 将id为1001的学生的地址修改为北京，成绩修改为100
update student set address = '北京',score = 100 where id = 1001;

3.3 数据删除

语法格式：

delete from 表名 [where 条件];
truncate table 表名 或者 truncate 表名;

例子：

# 删除sid为1004的学生数据
delete from stduent where sid = 1004;
# 删除表所有数据
delete from student;
# 清空表数据
truncate table student;
truncate student;

注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为是将整个表删除，然后再创建该表。

SQL的执行顺序

from - on - join -where -group by -having -select - distinct order by - limit

MySQL约束

概念

约束英文：constraint

约束实际上就是表中数据的限制条件

作用

表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户列表的值（手机号）不能为空，有些列的值（身份证号）不能重复。

分类

• 主键约束（primary key）PK
• 自增长约束（auto_increment）
• 非空约束（not null）
• 唯一性约束（unique）
• 默认约束（default）
• 零填充约束（zerofill）
• 外键约束（foreign key）FK

1. MySQL约束-主键约束

概念

• MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，方便在RDBMS中尽快的找到某一行。
• 主键约束相当于 唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。
• 每个表最多只允许一个主键
• 主键约束的关键字是：primary key
• 当创建主键的约束时，系统默认会在所在列和列组合上建立对应的唯一索引。

操作-添加单列主键

创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键

方法1 - 语法：

# 在 create table 语句中，通过 primary key 关键字来指定主键
# 在定义字段的同时指定主键，语法格式如下：
create table 表名(
	...
	<字段名><数据类型> primary key
	...
);

方法1-实现：

create table emp1(
	eid int primary key,
	name varchar(20),
	salary double
);

方法2-语法：

#在定义字段之后再指定主键，语法格式如下：
create table 表名 (
	...
	[constraint<约束名>] primary key [字段名]
);

方法2-实现：

mysql> create table emp2(
    -> eid int,
    -> name varchar(20),
    -> salary double,
    -> constraint pk1 primary key(eid)
    -> );

主键重复报错：

ERROR 1062 (23000): Duplicate entry '1' for key 'emp1.PRIMARY'

主键不能为NULL报错：

ERROR 1048 (23000): Column 'eid' cannot be null

操作-添加多列主键（联合主键）

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

注意：

1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
2. 一张表只能有一个主键，联合主键也是一个主键。

语法：

create table 表名(
	...
	primary key(字段1, 字段2, ... ,字段n)
);

实现：

create table emp3(
	name varchar(20),
	deptId int,
	salary double,
	primary key(name,deptId)
);

联合主键中联合的列，只要有一列不相同，其他列相同旧认为是不同的。

通过修改表结构添加主键

语法：

create table 表名(
	...
);
alter table 表名 add primary key (字段列表);

实现：

create table if not exists emp1(
	eid int,
	name varchar(20),
	depId int,
	salary double,
);
alter table emp1 add primary key(eid);

如果一个表已经有primary key，则不能再则不能在使用

ERROR 1068 (42000): Multiple primary key defined

删除主键约束

一个表中不需要主键约束，就需要从表中将其删除。删除主键约束的方法要比创建主键约束容易的多。

语法：

alter table 表名 drop primary key

实现：

# 删除主键（单列、多列）
alter table emp1 drop primary key;

2. MySQL约束—自增长约束(auto_increment)

概念

在MySQL中，当主键定义为自增长后，这个主键的值就不需要用户输入数据了，而由数据库根据定义自动赋值，每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加auto-increment属性来实现主键自增长。

语法：

字段名 数据类型 auto_increment

实现：

create table emp1(
	id int primary key auto_increment,
	name varchar(20)
);

自增到int的最大范围时，无法继续添加数据：

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'emp1.PRIMARY'

特点

• 默认情况下，auto_increment的初始值是1，每新增一条记录，字段值自动加1

• 一个表中只能有一个字段使用auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键的一部分）。

• auto_increment约束的字段必须具备NOT NULL属性。

• auto_increment约束的字段只能是整数类型（tinyint，samllint，int，bigint）。

• auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，就无法继续添加数据。

指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条件记录的id值设置为5，那么再插入记录时，id值就会从5开始往上增加。

# 方式1 创建表时指定
create table emp1(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment=100;

# 方式2 创建表之后指定
create table emp1(
	id int primary key auto_incremetn,
	name varchar(20)
);
alter table 表名 auto_increment=100;

delete和truncate在删除后自增列的变化

• delete数据之后自增字段从断点开始
• truncate数据之后自动增长从默认起始值开始

3. 非空约束（not null）

概念

MySQL非空约束（not null）指字段的值不能为空。对于使用非空约束的字段，如果用户在添加数据时没有指定值，数据库就会报错。

语法

# 方式1
<字段名><数据类型> not null
# 方式2
alter table 表名 modify 字段 类型 not null;

实现

# 创建表时添加
create table emp1(
	id int,
	name varchar(20) not null,
	address varchar(20) not null
);
alter table emp1 modify id int not null;

删除非空约束

alter table 表名 modify 字段 类型

4. 唯一约束（unique）

概念

唯一约束（Unique key）是指所有记录中字段的值不能重复出现。例如，为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能出现重复的情况。

语法

# 方式1
<字段名><数据类型> unique;
# 方式2
alter table 表名 add constraint 约束名 unique (列);

实现

create table emp1(
	id int,
	name varchar(20),
	phone_number varchar(20) unique --指定唯一约束
);

在MySQL中 NULL和任何值都不相同 甚至和自己都不相同

删除唯一约束

语法

alter table 表名 drop index <唯一约束名>;

没有指定约束名，默认就为列名。

5. 默认约束（default）

概念

MySQL默认值约束用来指定某列的默认值。

语法

# 方式1
<字段名><数据类型> default <默认值>
# 方法2
alter table 表名 modify 列名 类型 default 默认值;

实现

create table emp1(
	id int,
	name varchar(20),
	address varchar(20) default 'beijing' -- 指定默认约束
);

6. 零填充约束（zerofill）

概念

1. 插入数据时，当该字段的值的长度小于定义的长度时，会在该值前面补上想要的0
2. zerofill默认为int（10）
3. 当使用zerofill时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍。例如有符号为-128_{127，无符号为0}256。

操作

create table person(
	id int zerofill, -- 零填充约束
	name varchar(20)
);

删除

alter table person modify id int;

MySQL数据库基本操作-DQL-基本查询

概念

• 数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。
• MySQL提供了功能强大、灵活的语句来实现这些操作。
• MySQL数据库使用select语句来查询数据。

语法

select
	[all|distinct]
	<目标列的表达式1>[别名],
	<目标列的表达式2>[别名]...
from <表名或视图名>[别名],<表名或视图名>[别名]...
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

简化版语法

select *| 列名 from 表 where 条件

1. 运算符

简介

数据库中的表结构确立后，表中的数据代表的意义就已经确定。通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据。

例如，学生表中存在一个birth字段，这个字段表示学生的出生年份。而运用MySQL的算数运算符用当年的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

MySQL支持4中运算符

算数运算符

比较运算符

# %用来匹配任意字符
select * from gooder where pname like '%ku%';

# _用来匹配一个字符
select * from gooder where pname like '_kou';

# 如果求最小值时，有个值为null，则不会进行比较，结果直接为null, 
# greatest同理
select least(10, null, 20);

位运算符

位运算是二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。

然后再将计算结果从二进制数变回十进制数。

select 3&5;	-- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移

2. 排序查询

介绍

如果我们需要对读取的数据进行排序，我们就可以使用MySQL的 order by子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

select
	字段名1, 字段名2, ...
from 表名
order by 字段名1 [asc|desc], 字段名2[asc|desc];

特点

• asc代表升序，desc代表降序，如果不写默认升序
• order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名
• order by子句，放在查询语句的最后面。LIMIT子句除外。

操作

# 使用价格排序（降序）
select * from gooder order by price desc;
# 在价格排序（降序）的基础上，以分类排序（降序）
# 价格相同 才会按分类进行排序
select * from gooder order by price desc, category_id asc;
# 显示商品的价格（去重复），并排序（降序）
select distinct price from product order by price desc;

3. 聚合查询

简介

之前我们做的查询都是横向查询，它们都是根据条件一行一列的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。

聚合函数对-NULL值的处理

• count函数对null值的处理
  • 如果count函数的参数为星号(*)，则统计所有记录的个数。而如果参数为某字段，不统计含null值得记录个数。
• sum和avg函数对null值的处理
  • 这两个函数忽略null值得存在，就像该条记录不存在一样，
• max和min函数对null值的处理
  • max和min两个函数同样忽略null值得存在。

4. 分组查询

简介

分组查询是指使用group by子句对查询信息进行分组

格式

select 字段1, 字段2... from 表名 group by 分组字段 having 分组条件

操作

# 统计各个分类商品得个数
select category_id, count(*) from product group by category_id;

如果要进行分组的话，则SELECT子句之后，只能出现分组的字段和统计函数，其他字段不能出现。

分组之后的条件筛选-having

• 分组之后对统计结果进行筛选的话必须使用having，不能使用where
• where子句用来筛选 from 子句中指定的操作所产生的行
• group by 子句用来分组 where 子句的输出
• having子句用来从分组的结果中筛选行

操作

# 统计各个分类商品的个数，且只显示个数大于4的信息
# SQL执行顺序 from -> group by -> count(pid) -> select -> having -> order by
select category_id, count(*) from product group by category_id having count(*) > 1;

5. 分页查询-limit

简介

分页查询在项目开发中很常见，由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。例如数据共有30条，每页显示5条，第一页显示和1-5条，第二页显示6-10条。

格式

# 方法1 显示前n条
select 字段1，字段2... from 表名 limit n;
# 方法2 分页显示
select 字段1，字段2... from 表名 limit m,n;
# m: 整数，表示从第几条索引开始，计算方式（当前页 -1）*每页显示条数
# n: 整数，表示查询多少条数据

操作

# 查询product表的前5条记录
select * from product limit 5;

# 从第4条开始显示，显示5条
select * from product limit 3,5

6. insert into select语句

简介

将一张表的数据导入到另一张表中，可以使用insert into select语句。

格式

# 1.
insert into table2(field1,field2,...) select value1,value2,...from Table1;
# 2.
insert into table2 select * from table1;

要求目标表Table2必须存在。

# ifnull(salary, 0)
# 如果salary的值为null,则当做0；不为null，则为salary

正则表达式

介绍

​ 正则表达式（regular expression）描述了一种字符串匹配的规则，正则表达式本省就是一个字符串，使用这个字符串来描述、用于定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常用来检索、替换那些符合某个规则的文本。

​ MySQL通过REGEXP关键之支持正则表达式进行字符串匹配。

格式

MySQL的多表查询—多表关系

多表关系

MySQL多表之间的关系可以概况为：一对一、一对多、多对多关系。

一对一关系

• 一个学生只有一张身份证；一张身份证只能对应一个学生。
• 在任一表中添加唯一外键，指向另一方主键，确保一对一关系。
• 一般一对一关系很少见，遇到一对一关系的表最好是合并表。

一对多/多对一关系

部门和员工

分析：一个部门有多个员工，一个员工只能对应一个部门

实现原则：在多的一方建立外键，指向一的一方的主键。

多对多关系

学生和课程

分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

原则：多对多关系实现需要借助第三方中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键。

外键约束

简介

MySQL外键约束（foreign key）是表的一个特殊字段，经常与主键约束以一起使用。对于两个具有关联关系的表而言，相关字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果、桃子、梨子、西瓜4种水果，那么你来到水果摊要买水果就只能选择苹果、桃子、梨子和西瓜，其他水果是不能购买的。

特点

定义一个外键时，需要遵守下列规则：

• 主表必须已经存在于数据库，或者是当前正在创建的表。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

操作–创建外键约束

方式1：在创建表时设置外键约束

在create table语句中，通过foreign key关键字来指定外键

语法：

[constraint <外键名>] foreign key 字段名 [, 字段名2, ...] references <主表名> 主键列1 [, 主键2，...]

实现：

# 主表
create table if not exists dept(
	detpno varchar(20) primary key, --部门号
	name varchar(20) -- 部门名字
);

# 从表
create table if not exists employee(
	eid varchar(20) primary key, -- 员工编号
	ename varchar(20),	-- 员工名字
	age int,
	dept_id varchar(20),
	constraint emp_fk foreign key (dept_id) references department (detpno)
);

方式2 在创建表时设置外键约束

外键约束也可以在修改表时添加，但是添加外键外外键约束的前提是：从表中中外键列中的数据必须与主键列中的数据一致或者没有数据。

alter table <表名> add constraint <外键名> foreign key(列名) references <主表名>(<列名>);

删除外键约束

当一个表不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系

格式

alter table <表名> drop foreign key <外键约束名>;

实现

alter table employee drop foreign key dept_fk;

多对多关系

在多对多关系中，A表中的一行对于B的多行，B表的一行对应A表的多行，我们要新增加一个中间表，来建立多对多关系。

多表联合查询

交叉连接查询

• 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积。
• 笛卡尔积可以理解为一张表的每一行去和另外一战表的任意一行进行匹配
• 假如A表有m行数据，B表有n行数据，则返回m*n行数据
• 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在改基础上进行条件筛选

格式

select * from 表1, 表2, 表3...;

实现

select * from department,employee;

内连接查询

内连接查询求多张表的交集

格式

# 隐式内连接（SQL92标准）
select * from A,B where 条件;
# 显示内连接（SQL99标准）
selecr * from A inner join B on 条件;

操作

—— 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

外连接查询

外连接分为左外连接（left outer join）、右外连接（right outer join）、满外连接（full outer join）。

注意：oralce里面有full join，可是1在MySQL对full join支持的不好。我们可以使用union来达到目的。

格式

# 左外连接
select * from A left outer join B on 条件;
# 右外连接
select * from A right outer join B on 条件;
# 满外连接
select * from A full outer join B on 条件;

子查询

介绍

子查询就是指的一个完整的查询语句中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是select嵌套的查询。

特点

子查询可以返回的数据类型一共分为四种：

1. 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
2. 单行多列：返回一行数据中多个列的内容；
3. 多行多列：返回多行建立之中同一列的内容，相当于给出了一个操作范围；
4. 多行多列：查询返回的结果是一张临时表

子查询关键字

在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能。

ALL

格式

select * from 表名 where c > all(查询语句);
# 等价于
select ... from ... where c > result1 and c > result2 and c > result 3 ...

特点

• ALL：与子查询返回的所有值比较为true则返回true
• AL可以与=、>、>=、<、<=、<>结合来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。
• ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于等于子查询集中的最大值；如果是小于号即小于等于子查询集的最小值。

操作

—— 查询年龄大于'1003'部门所有年龄的员工信息
select * from employee where age > all (select age from employee where dept_id = '1003')；
—— 查询不属于任何一个部门的员工信息
select * from employee where dept_id != all(select deptno from department);

ANY

格式

select ... from ... where > any(查询语句);
-- 等价于
select ... from ... where c > result1 or c > result2...

特点

• ANY：与子查询返回值得任何值比较为true，则返回true
• ANY可以与=、>、>= 、< 、<= 、<> 结合起来使用。
• 表示指定列中得值要（大于）子查询中的任意一个值，即必须要大于子查询集中的最小值。
• SOME和ANY的作用一样，SOME可以理解为ANY的别名。

操作

select * from employee where age > any(select age from dept_id = '1003');

IN

格式

select ... from where c in(查询语句);
-- 等价于:
select ... from ... where c = result1 or c = result or c=result2;

特点

• IN关键字，用于判断某各记录的值，是否在指定的集合中
• 在IN关键字前面加上not可以将条件反过来。

操作

—— 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from employee where dept_id in(select deptno from department name = '研发部' or name = '小说部');

EXISTS

格式

select .. from ... where exists(查询语句);

特点:

• 该子查询如果“有数据结果”（至少返回一行数据），则该EXISTS（）的结果为“true”，外层查询执行
• 该子查询如果"没有数据结果"（没有任何数据返回），则该EXISTS的结果为“false”，外层查询不执行
• EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时，where条件成立。
• 注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大量数据时，推荐使用EXISTS关键字

操作

select * from employee a where exists(select * from employee b where a.age > 60);

select * from department a where exists (select * from employee b where a.deptno = b.dept_id)；

自关联查询

概念

MySQL有时在信息查询需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。

注意自关联时表必须起别名。

格式

select 字段列表 from 表1 a, 表1 b where 条件;

select 字段列表 from 表1 a join 表1 b on 条件;

触发器

• 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时，自动触发这个SQL片段的执行，无需手动调用。
• 在MySQL中，只有执行insert、delete、update操作时才能触发触发器的执行
• 触发器的这种特性可以协助应有在数据库端保证数据的完整性，日志记录，数据效验等操作
• 使用别名OLD和NEW来引用触发器中发送变化的记录内容，这与其他的数据库是类似的。现在触发器还只支持行级触发，不支持语级触发。

格式

create trigger 触发器名 before | after 触发事件
on 表名 for each row
执行语句;

create trigger 触发器 before|after 触发事件
on 表名 for each row
begin
	执行语句列表
end；

NEW与OLD

格式

MySQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的那行一行数据，来引用触发器中发生变化的记录内容

MySQL索引

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式分类，主要有Hash索引和B+Tree索引

索引分类

单列索引

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

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯碎为了查询数据更快一点。

create index  index_card_id on student(card_id);

alter table student add index index_age(age);

删除索引

-- 删除索引
drop index index_name on student;

alter table student drop index index_age;

查看索引

select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydatabase';

show index from student;

select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydatabase' and a.table_name like '%student%';

唯一索引

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

-- 创建表的时候直接指定
create table if not exists student(
	sid int primary key,
	card_id varchar(20),
	name varchar(20),
	gender varchar(20),
	age int,
	birth date,
	phone_num varchar(11),
	score double,
	unique index_card_id(card_id)	-- 给card_id列创建索引
);
-- 直接创建
create unique index index_phone_num on student(phone_num);
-- 修改表结构
alter table student add unique index_sid(sid);

主键索引

每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。主键索引具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。

组合索引

• 组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的也可以建立为普通索引或者是唯一索引。
• 复合索引的使用复合最左原则

create index indexname on student(phone_num,name);

-- 组合索引
-- 普通索引
create index indexname on student(phone_num,name);

drop index indexname on student;

-- 组合索引 唯一索引
create unique index index_phone_name on student(phone_num,name);

最左匹配原则

select * from student where name = '张三';
select * from student where phone_num = '15100046637';
select * from student where phone_num = '15100046637' and name = '张三';
select * from student where name = '张三' and phone_num = '15100046637';

/*
四条sql只有2、3、4能使用的到索引index_phone_num，因为条件里面必须包含索引前面的字段才能进行匹配
而3和4相比where条件的实现不一样，为什么4可以用到索引呢？是因为MySQL本身就有一层sql优化，会根据sql来识别出来该用哪个索引，我们可以理解为3和4在MySQL眼中是等价的。
*/

全文索引

• 全文索引的关键字是fulltext
• 全文索引主要用来查找文本中的关键字，而不是直接与索引中的值想比较，它更像是一个搜索引擎，基于相似度查找，而不是简单的where语句的参数匹配
• 用like + % 就可以实现模糊匹配了，为什么还要全文索引？like+%在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的，全文索引在大量的数据面前，能比like+%快N倍，速度不是一个数量级，但是全文索引可能存在精度问题。

全文索引的版本、存储引擎、数据类型的支持情况：

• MySQL5.6以前的版本，只有myisam存储引擎支持全文索引；
• MySQL5.6及以后的版本，MyISAM和InnoDB存储引擎均支持全文索引；
• 只有字段的数据类型为char、varchar、text及其系列才可以建全文索引；
• 在数据量比较大时，先建表，后建索引，效率会比较高一些。

MySQL中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不被索引。通俗点就是说，想对一个词语全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

这两个的默认值可以使用以下命令查看：

show variables like '%ft%';

-- 修改表结构添加全文索引
alter table t_article add fulltext index_content(content);

drop index index_content on t_article;

-- 添加全文索引
create fulltext index index_content on t_article(content);

使用全文索引

和常用的模糊匹配使用 like+% 不同，全文索引有自己的语法格式，使用match和against关键字。

-- 使用全文索引
select * from t_article where match(content) against ('yo'); -- 没有结果，最小搜索长度为3

select * from t_article where match(content) against ('yoo'); -- 有结果

索引的原理

• 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
• 这样的话，索引查找过程中就要产生磁盘I/O操作，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
• 换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

Hash算法

优点：通过字段的值计算的hash值，定位数据非常快。

缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较。

二叉树搜索树

特性：分为左子树、右子树和根节点，左子树比根结点要小，右子树比根节点值要大。

缺点：有可能产生不平衡，产生类似于链表的结构。

平衡二叉树

特点：

1. 它的左子树和右子树都是平衡二叉树
2. 左子树比中间小，右子树比中间值大
3. 左子树和右子树的深度只差的绝对值不超过1

缺点：

1. 插入操作需要旋转
2. 支持范围查询，但回旋查询效率低，比如要查找大于8的，会回旋到父节点7、10
3. 如果存放几百条数据的情况下，数高度越高，查询效率越慢

BTREE树

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，BTree结构可以有效的解决目前的相关算法遇到的问题。

B-Tree

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sMpqVR8g-1641373580479)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20220104103640579.png)]

B+Tree

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xO6aa39v-1641373580480)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20220104104307975.png)]

MyISAM引擎使用B+Tree

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

InnoDB引擎使用B+Tree

InnoDB的叶子节点的data域存放的是数据，相比于MyISAM效率要高一些，但是比较占硬盘内存大小。

索引的特点

优点

• 大大加快数据的查询速度
• 使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
• 创建唯一索引，能够保证数据库表中每一行数据的唯一性
• 在实现数据的参考完整性方面，可以加速表和表之间的连接

缺点

• 创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
• 索引需要占据磁盘空间
• 对数据表中的数据进行增加，修改、删除时，索引也要动态的维护，降低了维护的速度

创建索引的原则

• 更新频繁的列不应设置索引
• 数据量小的表不要使用索引
• 重复数据量多的字段不应设为索引（比如性别，只有男和女，一般来说，重复的数据超过百分之15就不该建索引）
• 首先应该考虑对where和order by 涉及的列上建立索引

MySQL的存储引擎

概念

• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
• 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
• 用户可以根据不同的需求为数据选择不同的存储引擎
• 可以使用SHOW ENGINES命令 可以查看MySQL的所有执行引擎。

分类

MyISAM：MySQL5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务。

InnoDB：事务性速记的首选引擎，支持ACID事务，支持行级锁，MySQL5.5成为默认数据库引擎

memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数量成正比的内存空间。并且其内容会在MySQL重启启动后丢失。

Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差。

Federated：将不同的MySQL服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用。

-- 查询当前数据库支持的存储引擎
show engines;

-- 查看当前的默认存储引擎
show variables like '%storage_engine%';

-- 查看某个表使用了什么存储引擎
show create table student;

drop table if exists student;

-- 创建表时，指定存储引擎
create table if not exists student(id int, name varchar(20), gender varchar(10)) engine = MyISAM;

-- 通过alter命令来修改表的引擎
alter table student engine = INNODB;

MySQL的事务

什么是事务

• 在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
• 事务用来管理DDL、DML、DCL操作，比如insert、update、delete语句，默认是自动提交的。

MySQL的事务操作主要有以下三种：

1. 开启事务：start transaction
   • 任何一条DML语句（insert、update、delete）执行，标志事务的开启
   • 命令：begin 或 start transaction
2. 提交事务：commit transaction
   • 成功的结束，将所有的DML语句操作历史记录和底层硬盘来一次同步
   • 命令：commit
3. 回滚事务 Rollback transaction
   • 失败的结束，将所有的DML语句的操作历史记录全部清空
   • 命令：ROLLBACK

之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。

在MySQL中直接用set来改变MySQL的自动提交模式

set autocommit = 0; -- 禁止自动提交
set autocommit - 1; -- 开启自动提交

事务的特性

• 原子性：事务是一个不可分割的整体，事务开始后的所有操作，要么全部完成，要么全部不做
• 一致性：系统从一个正确的状态迁移到另一个正确的状态
• 隔离性：每个事务的对象对其他事务的操作对象互相分离，事务提交前对其他事务不可见
• 持久性：事务一旦提交，则其结果是永久性的

事务的隔离级别

lsolate，顾名思义就是将事务与另一个事务隔离开，为什么要隔离呢？如果一个事务正在操作的数据被另一个事务修改或删除了，最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他的问题。

读未提交 read uncomitted

一个事务可以读取另一个未提交的数据，最低级别，任何情况下都无法保证，会造成脏读。

读提交 read commited

一个事务要等另一个事务提交后才能读取数据，可避免脏读，会造成不可重复读。

repeateable read 可重复度（MySQL默认）

就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生。

serializale串行化

是最高的事务隔离级别，会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

脏读

如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一样的情况，就意味着发生了不可重复度现象。

脏读

在一个事务内多次查询某个符合查询条件的记录数量，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了幻读现象。

MySQL的锁机制

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争夺）。

在数据库中，除传统的计算机资源（如CPU、RAM、I/O等）的争抢以外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

从对数据操作的粒度分：

• 表锁：操作时，会锁定整个表
• 行锁：操作时，会锁定当前操作行

从对数据操作的类型分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排他锁）：当前操作没有完成之前，他会阻断其他写锁和读锁。

表级锁

偏向MyISAM存储引擎，开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。

行级锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁粒度最小哦啊，发生锁冲突的概率最低并发度也最高。

MyISAM表锁

MyISAM在执行查询语句（select）前，会自动给涉及的所有表叫读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户的干预，因此，用户一般不需要直接用lock table命令给MyISAM表显式加锁。

-- 加读锁:
lock table table_name read;
-- 加写锁:
lock table table_name write;
-- 释放锁
unlock tables;

InnoDB行锁

行锁特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁，锁粒度最小哦啊，发生锁冲突的概率最低并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁。

行锁模式

InnoDB实现了以下两种类型的行锁。

共享锁（S）：又称读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

排他锁（X）：又称为写锁，简称X锁，排他锁就是不能和其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获得改行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务可以对数据行读取和修改。

对于UPDATE、delete和insert语句，InnoDB会自动给涉及数据加排他锁（X）

对于普通的SELECT语句，InnoDB不会加如何锁。

MySQL日志

错误日志

错误日志是MySQL中最重要的日志之一，它记录了当前MySQL启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

该日志是默认开启的，默认存放目录为MySQL的数据目录，默认的日志文件名为hostname.err（hostname是主机名）。

查看日志位置指令：

show variables like 'log_error%';

二进制日志-binlog

二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操作语言）语句。但是不包括数据查询语句。此日志对于灾难性时的数据恢复起着极其重要的作用，MySQL的主从放置，就是通过binlog实现的。

二进制日志，MySQL默认已经开启，低版本的MySQL需要通过配置文件开启，并配置MySQL日志的格式。

日志格式

STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过MySQL提供的MySQLbinlog工具，可以清楚的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句，比如执行SQL语句；update tb_book set status = ‘1’，如果是statement日志格式，在日志中会记录一行SQL文件；如果是row，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中记录每一行的数据变更。

MIXED

混合了STATEMENT和ROW两种格式。

-- 查看所有日志
show binlog events;

-- 查看最新的日志
show master status;

-- 查询指定的binlog日志
show binlog events in 'binlog.000003' ;

-- 清空所有的binlog日志文件
reset master;

查询日志

• 查询日志中记录了客户端得所有操作语句，而二进制日志不包含查询数据得SQL语句。
• 默认情况下，查询日志是未开启得。如果需要开启查询日志，可以设置以下配置：

-- 该选项用来开启查询日志，可选项：0代表开启，1代表关闭
general_log = 1;
-- 设置日志的文件名，如果没有指定，默认的文件名为host_name.log
general_log_file=file_name;

慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志。long_query_time默认为110秒，最小为0，精度可以到微秒。

-- 慢查询日志查询

-- 查看慢查询日志是否开启
show variables like 'slow_query_log%';

-- 开启慢查询日志
SET global slow_query_log = 1;

-- 查看慢查询的超时时间
show variables like 'long_query_time%';

MySQL优化

MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL：

• 从设计上优化
• 从查询上优化
• 从索引上优化
• 从存储上优化

查看SQL执行的频率

-- 查看当前会话SQL执行类型的统计信息
show session status like 'Com_______';

-- 查看全局（自从上次MySQL服务器启动至今）执行类型的统计信息
show global status like 'Com_______';

-- 查看针对innoDB引擎的统计信息
show status like 'InnoDB_rows_%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3VyjKD9O-1641373580481)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20220104223231458.png)]

定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的SQL语句

• 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句。
• show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态】是否锁表，可以实时得查看SQL的执行情况，同时对一些锁表操作进行优化。

-- 查看慢日志配置信息
show variables like '%slow_query_log%';

-- 开启慢日志查询
set global show_query_log = 1;

-- 查看慢日志记录SQL的最低阈值时间
show variables like '%long_query_time%';

-- 修改慢查询日志记录SQL的最低阈值时间
set global long_query_time = 5;

-- 动态定位查询效率低的SQL
show processlist;

explain分析执行计划

通过以上步骤查询到效率低的SQL语句后，可以通过EXPLAIN命令获取MySQL如何执行select语句的信息，包括在select语句在执行过程中表如何连接和连接的顺序。

explain select * from user where uid = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LHPzcqVH-1641373580481)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20220105121918335.png)]

id

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

select_type

type

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

show profile分析SQL

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

-- 查看当前的MySQL是否支持profile
select @@have_profiling;

-- 如果不支持，则需要设置打开
set profiling = 1;

-- 查看大致耗时
show profiles;
-- 查看详细耗时
show profile for query 61;
-- 查看CPU详细耗时
show profile cpu for query 80;

trace分析优化器执行计划

MySQL5.6提供了SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是B计划。

使用索引优化

避免索引失效----全值匹配

-- 全值匹配和字段匹配成功即可，和字段的先后顺序无关。
explain select * from ta_seller where name = 'Yokoyama Mai' and status = '1' and address = '270 Diplomacy Drive';

explain select * from ta_seller where status = '1' and address = '270 Diplomacy Drive' AND name = 'Yokoyama Mai';

避免索引失效----最左前缀法则

create index idx_seller_name_sta_addr on ta_seller(name,status,address);

-- 最左前缀法则
-- 索引失效
explain select * from ta_seller where status = '1' and address = '270 Diplomacy Drive' ;
-- 用到了索引
explain select * from ta_seller where name = 'Yokoyama Mai';
-- 用到了索引，只有name生效，address没有生效
explain select * from ta_seller where address = '270 Diplomacy Drive' AND name = 'Yokoyama Mai';

避免索引失效应用—其他匹配原则

-- 范围查询右边的列，不能使用索引
-- 根据前面的两个字段name，status，查询时走索引的，但是最后一个条件address没有用到索引。

explain select * from ta_seller where name = 'Yokoyama Mai' and status > '1' and address = '270 Diplomacy Drive';

-- 不要在索引列上进行运算操作，索引将失效
explain select * from ta_seller where SUBSTRING(name,3,2)='Mai';

-- 字符串不加单引号，造成索引失效
explain select * from ta_seller where name = 'Yokoyama Mai' and status = 1;

-- 尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from ta_seller where name = 'Yokoyama Mai' and address= '270 Diplomacy Drive';

-- 从索引树上就可以查询到所有的数据
explain select name from ta_seller where name = 'Yokoyama Mai' and address= '270 Diplomacy Drive';
/*
Extral
	using index : 使用覆盖索引的时候就会出现
	using where : 在查找使用索引的情况下，需要回表去查询所需的数据
	using index condition : 查找使用了索引，但是需要会表查询数据
	using index;using where : 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
	

-- 用or分割开的条件，那么涉及的索引都不会被用到
explain select * from ta_seller where sellerid = 200 or status = '1';

-- 以%开头的like模糊查询，索引失效
-- 索引有效
explain select * from ta_seller where name like 'Yo%';
-- 索引失效
explain select * from ta_seller where name like '%Yo';
-- 弥补不足，不用*，使用索引列
explain select name from ta_seller where name like '%Yo%';

-- 如果MySQL评估使用索引比全表慢，则不使用索引
-- 这种情况是由数据本身的特点来决定的
create index index_address on ta_seller(address);

explain select * from ta_seller where address = '186 Narborough Rd';

-- is NULL, is not null 有时有效，有时索引失效
-- 这种情况是由数据本身的特点来决定的
explain select * from ta_seller where address is null;
explain select * from ta_seller where address is not null;

-- in走索引， not in 索引失效
explain select * from ta_seller where address in('186 Narborough Rd','V');
explain select * from ta_seller where address not in('186 Narborough Rd','V');


-- 单列索引和符合索引，尽量使用符合索引
create index idx_seller_name_sta_addr on ta_seller(name,status,address);
/*
	等价于：
	NAME
	name + STATUS
	name + status + address
*/
-- 如果一张表有多个单列索引，即使where中都使用了这些索引列，则只有一个最优索引生效

SQL优化

大批量插入数据

-- 加载数据
-- 结论通关load向表中加载数据时，尽量保证文件中的主键有序，这样可以提高运行执行效率
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';

-- 关闭唯一性效验
show global variables like 'unique_checks';

set unique_checks = 0;

优化insert语句

-- 减少客户端与服务器连接的次数
-- 原始方式
insert into user values(1,'lisi','123456');
insert into user values(2,'zhangsan','123456');
insert into user values(3,'wangwu','123456');

-- 优化后的方案
insert into user values(1,'lisi','123456'),(2,'zhangsan','123456'),(3,'wangwu','123456');

-- 在事务中进行数据插入
begin
insert into user values(1,'lisi','123456');
insert into user values(2,'zhangsan','123456');
insert into user values(3,'wangwu','123456');
commit;

-- 数据插入有序
-- 原始方式
insert into user values(4,'laowa','123456');
insert into user values(1,'lisi','123456');
insert into user values(2,'zhangsan','123456');
insert into user values(3,'wangwu','123456');

-- 优化后
insert into user values(1,'lisi','123456');
insert into user values(2,'zhangsan','123456');
insert into user values(3,'wangwu','123456');
insert into user values(4,'laowa','123456');

优化order by语句

两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

-- 排序 oreder by 
explain select * from employee order by age; -- using filesort 
explain select * from employee order by age,salary; -- using filesort 

explain select id from employee order by age; -- using INDEX
explain select id, salary FROM employee order by age; -- using index 

explain select id,age from employee order by age desc,salary desc; -- Backward index scan; Using index

-- order by 后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from employee order by salary ,age; -- Using index; Using filesort

FileSort的优化

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

MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出字段总大小，来判定是否选择那种排序方式，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。

优化子查询

使用子查询可以一次性的完成很大逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表死锁，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（join）替代。

连接（join）查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

优化limit查询

一把分页查询时，通过创建索引能够比较好地提高性能。一个常见又头疼地问题就是limit 90000，10，此时需要MySQL排序前90010条记录，其他记录丢弃，查询排序地代价非常大。

1. 优化思路一

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

   • select * from tab_user limit 0,10;
     
     explain select * from ta_user limit 900000,10;
     
     explain select * from ta_user as a,(select id from tb_user order by id limit 90000,10) as b where a.id = id;
     

2. 优化思路二

   • 该方案适用于主键自增地表，可以把limit查询转换成某个位置的查询。

   explain select * from tb_user where id > 900000 limit 10;
   

insert into user values(3,‘wangwu’,‘123456’);

– 优化后
insert into user values(1,‘lisi’,‘123456’);
insert into user values(2,‘zhangsan’,‘123456’);
insert into user values(3,‘wangwu’,‘123456’);
insert into user values(4,‘laowa’,‘123456’);

### 优化order by语句

两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的filesort排序，所有不是通过索引直接返回排序结果的排序都叫filesort排序。

第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

```sql
-- 排序 oreder by 
explain select * from employee order by age; -- using filesort 
explain select * from employee order by age,salary; -- using filesort 

explain select id from employee order by age; -- using INDEX
explain select id, salary FROM employee order by age; -- using index 

explain select id,age from employee order by age desc,salary desc; -- Backward index scan; Using index

-- order by 后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from employee order by salary ,age; -- Using index; Using filesort

FileSort的优化

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

MySQL通过比较系统变量max_length_for_sort_data的大小和query语句取出字段总大小，来判定是否选择那种排序方式，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高sort_buffer_size和max_length_for_sort_data系统变量，来增大排序区的大小，提高排序的效率。

优化子查询

使用子查询可以一次性的完成很大逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表死锁，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（join）替代。

连接（join）查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

优化limit查询

一把分页查询时，通过创建索引能够比较好地提高性能。一个常见又头疼地问题就是limit 90000，10，此时需要MySQL排序前90010条记录，其他记录丢弃，查询排序地代价非常大。

1. 优化思路一

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

   • select * from tab_user limit 0,10;
     
     explain select * from ta_user limit 900000,10;
     
     explain select * from ta_user as a,(select id from tb_user order by id limit 90000,10) as b where a.id = id;
     

2. 优化思路二

   • 该方案适用于主键自增地表，可以把limit查询转换成某个位置的查询。

   explain select * from tb_user where id > 900000 limit 10;