1 Unstar Star 1 Fork 0

huangli1279 / mysql_learningSQL

Create your Gitee Account
Explore and code with more than 5 million developers,Free private repositories !:)
Sign up
This repository doesn't specify license. Without author's permission, this code is only for learning and cannot be used for other purposes.
MySQL知识点复习 spread retract

Clone or download
huangli1279 authored img
Cancel
Notice: Creating folder will generate an empty file .keep, because not support in Git
Loading...
README.md

安装与启动配置

准备

将 Homebrew 设置使用代理

echo export ALL_PROXY=socks5://127.0.0.1:1086 >> ~/.bash_profile

安装MySQL 5.7

命令

使用Homebrew进行安装MySQL 5.7

brew install mysql@5.7

安装成功提示

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

mysql@5.7 is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have mysql@5.7 first in your PATH run:
  echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

For compilers to find mysql@5.7 you may need to set:
  export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
  export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"


To have launchd start mysql@5.7 now and restart at login:
  brew services start mysql@5.7
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mysql@5.7/bin/mysql.server start
==> Summary
🍺  /usr/local/Cellar/mysql@5.7/5.7.29: 318 files, 232.3MB, built in 18 minutes 50 seconds

配置

将MySQL的bin目录加入到PATH路径中

echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile

编辑~/.bash_profile文件,加入

export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

启动

以后台服务形式启动MySQL

brew services start mysql@5.7

以前台形式启动MySQL,关闭终端则停止MySQL服务

/usr/local/opt/mysql@5.7/bin/mysql.server start

登陆

无密码登陆

mysql -uroot

此时可以无密码登陆到MySQL数据库中

设置密码

mysqladmin -uroot password 123456

将root用户的密码设置为123456

密码登陆

mysql -uroot -p

然后输入密码登陆

或者

mysql -uroot -p123456

修改字符集配置

定位 my.cnf 文件配置

find / -name my.*
/etc/my.cnf.d
/etc/my.cnf
/etc/my.cnf.rpmsave

修改 my.cnf 文件内容,添加配置其默认编码格式

vim /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8

重启 mysql 服务

systemctl restart mysqld

查看当前数据库系统变量是否为 utf8 格式

show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

此时新建的数据库会默认采用 utf8 的格式

基本介绍

登陆

命令

mysql -hlocalhost -P3306 -uroot -p

选项

选项 介绍
-h 主机地址
-P 端口号
-u 用户名
-p 密码登陆

不指定主机地址和端口号时,默认使用localhost3306

mysql -uroot -p

注意

如果使用命令行直接输入密码的方式,-p和密码之间不能有空格

退出

exit

或者

quit

简单命令

查询

show databases;

查看所有数据库

use 数据库名称;

选定数据库

show tables;

查看选定的数据库中的数据表

若已选定数据库A想查看数据库B的数据表,可以使用

show tables from B;

查看数据库B中的数据表

select database();

查看当前选定的数据库名称

desc 表名;

查看数据表结构

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查看数据数据库版本

方式一

select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+
1 row in set (0.00 sec)

方式二

退出mysql环境,在终端中

mysql --version
mysql  Ver 14.14 Distrib 5.7.29, for osx10.15 (x86_64) using  EditLine wrapper

也可以将--version替换成-V

新建

create database 数据库名;

新建数据库

mysql> create table 表名(
    -> id int,
    -> name varchar(20));

新建数据表

语法规范

基本

不区分大小写

每条语句用;\g结尾

注释

单行注释

#方式一
-- 方式二

多行注释

/*  */

数据查询

数据查询 - 初级

基础查询

语法
select 查询列表 from 表名;

查询列表

  1. 表中字段
  2. 常量
  3. 表达式
  4. 函数
查询单个字段
select last_name
from employees;
查询表中多个字段
select last_name, salary, email
from employees;

也可以使用折中号(``)包住字段名称,避免与关键字冲突

select `last_name`, `salary`, `email`
from employees;
查询表中所有字段
select *
from employees;
查询常量
select 100;
select 'john';
查询表达式
select 98 * 100;
查询函数
select version();
别名

作用

  1. 便于理解
  2. 区分不同表的重名字段

方式一:使用as连接

select 98 % 100 as 求余;
select last_name as 姓, first_name as 名
from employees;

方式二:直接使用空格连接

select last_name 姓, first_name 名
from employees;

方式三:别名有特殊字符时,可以使用折中号、双引号或单引号包起来

select salary `out#put`
from employees;
去重
select distinct department_id
from employees;
+号运算符
select 100 + 90; # 两个操作数都为数值型则做加法运算
select '123' + 90; # 其中有字符型时,尝试将字符型转为数值型进行加法运算,若转换失败则将字符型置为0
select null + 90; # 有一方为null则结果为null
连接
select concat('a','b','c') 结果;
select concat(first_name,' ', last_name) 姓名 from employees;

条件查询

语法
select 列表 from 表名 where 条件;
分类
  1. 条件表达式>, <, =, !=, <>, >=, <=
  2. 逻辑表达式:(1) &&, ||, !; (2) and, or, not
  3. 模糊查询like, between and, in, is null, is not null
条件表达式

查询工资大于12000的员工信息

select *
from employees
where salary > 12000;

查询部门编号不等于90号的员工名和部门编号

select last_name, department_id
from employees
where department_id <> 90;
逻辑表达式

查询工资在10000到20000之间的员工名、工资以及奖金

select last_name, salary, commission_pct
from employees
where salary >= 10000
  and salary <= 20000;

查询部门编号不是在90到110之间,或者工资高于15000的员工信息

select *
from employees
# where (department_id < 90 or department_id > 110)
#    or (salary > 15000);
where not (department_id >= 90 and department_id <= 110)
   or salary > 15000;
模糊查询

like:通常与通配符搭配

通配符有%_

  1. %:任意多个字符
  2. _:任意单个字符

between and:包含临界值

in:判断字段的值是否属于in列表的一项

注意in列表的值必须互相兼容

is null || is not null:判断null

注意:=等于号或<>不等于号不能判断null

like示例

查询员工名中包含字符a的员工信息

select *
from employees
where last_name like '%a%';

查询员工名中第三个字符为n,第五个字符为l的员工名和工资

select last_name, salary
from employees
where last_name like '__n_l%';

查询员工名中第二个字符为_的员工名,也可以使用escape指定转义符号

select last_name
from employees
# where last_name like '_\_%';
where last_name like '_$_%' escape '$';

between and示例

查询员工编号在100到120之间的员工信息

select *
from employees
# 等价于where employee_id >= 100 and employee_id <= 120;
where employee_id between 100 and 120;

in示例

查询员工的工种编号是IT_PROG、AD_VP、AD_PRES的员工名和工种编号

select last_name, job_id
from employees
# 等价于where job_id = 'IT_PROG' or job_id = 'AD_VP' or job_id = 'AD_PRES';
where job_id in ('IT_PROG', 'AD_VP', 'AD_PRES');

is null示例

查询没有奖金的员工名和奖金率

select last_name, commission_pct
from employees
# 注意:=等于号和<>不等于号不能判断null值
where commission_pct is null;

is not null示例

查询有奖金的员工名和奖金率

select last_name, commission_pct
from employees
where commission_pct is not null;
安全等于

安全等于:<=>

is null对比<=>:前者仅可以判断null值,后者不仅可以判断null值,也可以判断数值

示例

查询没有奖金的员工名和奖金率

select last_name, commission_pct
from employees
where commission_pct <=> null;

查询工资为12000的员工信息

select *
from employees
where salary <=> 12000;

排序查询

语法
select 列表 from 表名 [where 条件] order by 列表 [asc|desc];
关键字
  1. asc:升序,默认
  2. desc:降序
单字段排序

查询员工信息工资从高到低排序

select *
from employees
order by salary desc;
添加筛选语句排序

查询部门编号>=90的员工信息,按入职时间排序

select *
from employees
where department_id >= 90
order by hiredate;
按表达式排序

按照年薪高低显示员工信息和年薪

select *, salary * 12 * (1 + ifnull(commission_pct, 0)) 年薪
from employees
order by salary * 12 * (1 + ifnull(commission_pct, 0)) desc;
按别名排序

按照年薪高低显示员工信息和年薪

select *, salary * 12 * (1 + ifnull(commission_pct, 0)) 年薪
from employees
order by 年薪 desc;
按函数排序

查询员工名,并且按名字的长度降序

select last_name, length(last_name) length
from employees
order by length(last_name) desc;
按多个字段排序

查询员工名字,要求先按工资降序,再按employee_id升序

select last_name, salary, employee_id
from employees
order by salary desc, employee_id asc;

常见函数

分类

  1. 单行函数:如concatlengthifnull
  2. 分组函数:做统计使用

单行函数

分类
  1. 字符函数
  2. 数学函数
  3. 日期函数
  4. 流程控制函数
  5. 其他函数
字符函数

length

获取参数值的字节个数

select length('john');
show variables like '%char%';
select length('你好');

concat

使用空格连接姓和名

select concat(first_name, ' ', last_name) name
from employees;

upperlower

select upper('tom');
select lower('TOM');

将姓变大写,名变小写,然后拼接

select concat(lower(first_name), ' ', upper(last_name)) name
from employees;

substrsubstring

注意:此处的sql索引从1开始数

截取指定索引处至最后的所有字符

select substr('你好世界', 3) sub;

截取指定索引处至后面指定字符长度的字符

select substr('你好世界', 1, 2) sub;

姓名中首字符大写,其他字符小写然后用空格拼接,显示出来

select concat(upper(substr(first_name, 1, 1)), lower(substr(first_name, 2)), ' ', upper(substr(last_name, 1, 1)),
              lower(substr(first_name, 2)))
from employees;

instr

返回子串在原字符串中的第一次出现索引,若找不到则返回0

select instr('你好世界', '世界') output;

trim

去除先后空格

select length(trim('   ninin   ')) output;

去除先后指定字符

select trim('a' from 'aaaaainiininaaaaininiiaaaa') output;

lpad

用指定字符左填充指定长度

select lpad('nihao', 10, '*') output;

rpad

用指定字符右填充指定长度

select rpad('nihao', 10, '=') output;

replace

用新字符替换原句中所有旧字符

select replace('你好世界', '世界', '地球') output;
数学函数

round

四舍五入

select round(1.65);

指定小数位数

select round(1.567, 2);

ceil

向上取整

select ceil(1.2);

floor

向下取整

select floor(1.8) output;

truncate

截断

select truncate(1.4545454, 2) output;

mod

取余

select mod(10, 3) output;
日期函数

now

返回当前日期时间

select now();

curdate

返回当前日期

select curdate();

curtime

返回当前时间

select curtime();

month

返回月份

select month(now()) month;

返回月份英文名

select monthname(now()) month;

示例

获取员工入职年份按倒序排序

select year(hiredate) `year`
from employees
order by `year` desc;

str_to_date

通过指定格式将字符串转换为日期

传入4-3 1992字符串查询入职日期为1992-4-3的员工信息

select *
from employees
where hiredate = str_to_date('4-3 1992', '%c-%d %Y');

date_format

将日期转换为指定格式字符串

将有奖金的员工的入职时间格式转换为yyyy年MM月dd日

select last_name, date_format(hiredate, '%Y年%c月%d日') hiredate
from employees
where commission_pct is not null;

格式符说明

格式符 功能
%Y 4位的年份
%y 2位的年份
%m 月份(01,02…11,12)
%c 月份(1,2,…11,12)
%d 日(01,02,…)
%H 小时(24小时制)
%h 小时(12小时制)
%i 分钟(00,01…59)
%s 秒(00,01,…59)
流程控制函数

if函数

语法

if(判断表达式,结果1,结果2)true则返回结果1false则返回结果2

示例

判断10是否大于5,是则返回true,否则返回false

select if(10 > 5, 'true', 'false') output;

查看员工是否有奖金并备注

select last_name, if(commission_pct is null, '无奖金', '有奖金') 奖金
from employees;

case函数

功能

作用一:switch-case

作用二:if-else if-else

示例

switch-case

查询员工的工资,要求

部门号=30,显示的工资为1.1倍

部门号=40,显示的工资为1.2倍

部门号=50,显示的工资为1.3倍

其他部门,显示的工资为原工资

select salary        原始工资,
       department_id 部门编号,
       case department_id
           when 30 then salary * 1.1
           when 40 then salary * 1.2
           when 50 then salary * 1.3
           else salary
           end       新工资
from employees;

if-else if-else

查询员工的工资的情况

如果工资>20000,显示A级别

如果工资>15000,显示B级别

如果工资>10000,显示C级别

否则,显示D级别

select last_name name,
       salary,
       case
           when salary > 20000 then 'A'
           when salary > 15000 then 'B'
           when salary > 10000 then 'C'
           else 'D'
           end   level
from employees;
其他函数

查询当前版本

select version() version;

查看当前所在数据库

select database() `database`;

查看当前用户

select user() user;

分组函数

简单使用

sum:求和

select sum(salary)
from employees;

avg:平均值

select avg(salary)
from employees;

max:最大值

select max(salary)
from employees;

min:最小值

select min(salary)
from employees;

count:计算非空记录的个数

select count(salary)
from employees;

示例

select sum(salary) 求和, round(avg(salary), 2) 平均, max(salary) 最大值, min(salary) 最小值, count(salary) 计数
from employees;
参数支持类型

日期

select max(hiredate) max, min(hiredate) min
from employees;

字符串

select max(last_name) max, min(last_name) min
from employees;
忽略null

由于null加上任何值都为null

select null + 100 output;

因此无论是累加还是求平均,都必然会忽略null值,证明排除null在外

select avg(commission_pct) avg, sum(commission_pct) / 35, sum(commission_pct) / 107
from employees;

证明最大最小值都不包含null,无论是最大值还是最小值都不存在null,故证明排除null在外

select max(commission_pct) max, min(commission_pct) min
from employees;
配合distinct去重
select count(distinct salary) distinct_count, count(salary) count
from employees;
count函数

统计记录个数

使用count(*)时只有记录字段全为空才不增加,避免由于个别字段为空影响记录计数

select count(*) count
from employees;

使用count(1)时,相当于表中增加了一列1,有多少条记录就增加多少个1,故直接count(常量)同样可以实现记录计数

select count(1) count
from employees;

数据查询 - 高级

分组查询

语法
select 分组列表, 分组函数 from 表名 group by 分组列表;
筛选条件
数据源 位置 关键字
分组前 原始表 group by之前 where
分组后 分组函数 group by之后 having

基于性能考虑优先使用分组前筛选

基础查询

查询每个部门的平均工资

select department_id, avg(salary)
from employees
group by department_id;

查询每个工种的最高工资

select job_id, max(salary)
from employees
group by job_id;

查询每个位置的部门个数

select location_id, count(*)
from departments
group by location_id;
筛选条件 - 分组前

查询每个领导手下有奖金的员工的平均工资

逐步分析

一、得到所有记录的manager_idsalary

select manager_id, salary from employees;

二、得到commission_pct不为空的所有记录的manager_idsalary

select manager_id, salary from employees where commission_pct is not null;

三、再以manager_id进行分组,对不同组的员工工资进行聚合求平均

select manager_id, avg(salary) from employees where commission_pct is not null group by manager_id;

综合

select manager_id, avg(salary)
from employees
where commission_pct is not null
group by manager_id;

查询邮箱中包含a字符的每个部门的最高工资

select department_id, max(salary)
from employees
where email like '%a%'
group by department_id;
筛选条件 - 分组后

查询哪个部门的员工个数>5

逐步分析

一、查询每个部门的员工个数

select department_id, count(*)
from employees
group by department_id;

二、根据一的结果对分组聚合的计数使用having进行判断

select department_id, count(*) count
from employees
group by department_id
# having关键字的位置处于group by之后
having count > 5;

总结

select department_id, count(*) count
from employees
group by department_id
# having关键字的位置处于group by之后
having count > 5;
筛选条件 - 综合

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select job_id, max(salary) max
from employees
where commission_pct is not null
group by job_id
having max > 12000;

领导编号>102的每个领导手下的最低工资大于5000的员工的领导编号和最低工资

select manager_id, min(salary) min
from employees
where manager_id > 102
group by manager_id
having min > 5000;
按表达式或函数分组

按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5 的有哪些

select length(last_name) length, count(*) count
from employees
group by length
having count > 5;
按多个字段分组

查询每个工种每个部门的最低工资

select job_id, department_id, min(salary) min
from employees
group by job_id, department_id;
添加排序

每个工种有部门编号的员工的最高工资>6000的工种编号和最高工资,按最高工资倒序

select job_id, max(salary) max
from employees
where department_id is not null
group by job_id
having max > 6000
order by max desc;

连接查询

概述

连接查询又称多表查询,查询涉及多个数据表的字段

笛卡尔乘积:表1有m行,表2有n行,则联查结果有m*n

分类

按年代

  1. sql92:只有内连接
  2. sql99:有内连接、外连接和交叉连接

按功能

  1. 内连接
    1. 等值连接
    2. 非等值连接
    3. 自连接
  2. 外连接
    1. 左外连接
    2. 右外连接
    3. 全外连接
  3. 交叉连接

连接查询 - sql92

多表等值连接的结果为多表的交集

n个表连接,则至少需要n-1个连接条件

等值连接

查询员工名和对应的部门名

select last_name, department_name
from employees,
     departments
where employees.department_id = departments.department_id;

别名

提高语句简洁度、避免歧义

注意:若起了别名,则不能再用原表名进行限定

查询员工名、工种号、工种名

select e.last_name, d.department_name, e.commission_pct
from employees e,
     departments d
where e.department_id = d.department_id
  and e.commission_pct is not null;

查询城市名中第二个字符为o的部门名和城市名

select d.department_name, l.city
from departments d,
     locations l
where d.location_id = l.location_id
  and l.city like '_o%';

分组

查询每个城市的部门个数

select l.city, count(*) d_count
from locations l,
     departments d
where l.location_id = d.location_id
group by l.city;

查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

select d.department_name, d.manager_id, min(e.salary) min
from departments d,
     employees e
where d.department_id = e.department_id
   and e.commission_pct is not null
group by d.department_name, d.manager_id;

排序

查询每个工种的工种名和员工的个数,并且按员工个数降序

select j.job_title, count(*) count
from jobs j,
     employees e
where j.job_id = e.job_id
group by j.job_title
order by count desc;

三表连接

查询员工名、部门名和所在的城市

select e.last_name, d.department_name, l.city
from employees e,
     departments d,
     locations l
where e.department_id = d.department_id
  and d.location_id = l.location_id;
非等值连接

查询员工的工资和工资级别

select e.salary, j.grade_level
from employees e,
     job_grades j
where e.salary between j.lowest_sal and j.highest_sal;
自连接

查询员工名和上级的名称

select e1.last_name, e2.last_name
from employees e1,
     employees e2
where e1.manager_id = e2.employee_id;

连接查询 - sql99

内连接

语法

select 列表 from 表1 别名1 inner join 表2 别名2 on 连接条件;

其中inner可省略,等价于

select 列表 from 表1 别名1, 表2 别名2 where 连接条件;

分类

  1. 等值连接
  2. 非等值连接
  3. 自连接

等值连接

查询员工名、部门名

select e.last_name, d.department_name
from employees e
         inner join departments d on e.department_id = d.department_id;

筛选

查询名字中包含e的员工名和工种名

select e.last_name, j.job_title
from employees e
         inner join jobs j on e.job_id = j.job_id
where e.last_name like '%e%';

分组

查询部门个数>3的城市名和部门个数

select city, count(*) count
from locations l
         inner join departments d
                    on d.location_id = l.location_id
group by city
having count > 3;

排序

查询哪个部门的员工个数>3的部门名和员工个数,并按个数降序

select department_name, count(*) count
from departments d
         inner join employees e
                    on e.department_id = d.department_id
group by department_name
having count > 3
order by count desc;

三表联查

查询员工名、部门名、工种名,并按部门名降序

select last_name, department_name, job_title
from employees e
         inner join departments d on e.department_id = d.department_id
         inner join jobs j on e.job_id = j.job_id
order by department_name desc;

非等值连接

查询员工的工资级别

select last_name, grade_level
from employees e
         join job_grades j on salary between lowest_sal and highest_sal;

查询工资级别的个数>20的个数,并且按工资级别降序

select grade_level, count(*) count
from employees e
         join job_grades j on e.salary between j.lowest_sal and j.highest_sal
group by grade_level
having count > 20
order by count desc;

自连接

查询员工的名字、上级的名字

select e.last_name, m.last_name
from employees e
         join employees m on e.manager_id = m.employee_id;

查询姓名中包含字符k的员工的名字、上级的名字

select e.last_name, m.last_name
from employees e
         join employees m on e.manager_id = m.employee_id
where e.last_name like '%k%';
外连接

应用场景:查询不同表的差集

特点

  1. 查询结果为主表的所有记录
    1. 若从表中有匹配值,则显示值
    2. 若从表中无匹配之,则显示null
    3. 外连接查询结果 = 内连接结果 + 主表有从表无的差值
  2. 主从表
    1. left join:左边的是主表
    2. right join:右边的是主表

左外连接(右外同理)

查询男朋友不在男神表的的女神名

select be.name
from beauty be
         left outer join boys bo on be.boyfriend_id = bo.id
where bo.id is null;

查询哪个部门没有员工

select department_name
from departments d
         left join employees e on e.department_id = d.department_id
where e.employee_id is null;

全外连接

注意:mysql不支持全外连接

select bo.*, be.*
from boys bo
full join beauty be on be.boyfriend_id = bo.id;
交叉连接

笛卡尔积

select bo.*, be.*
from boys bo
cross join beauty be;

子查询

又称为内查询,外部的查询语句称为主查询或外查询

分类
  1. 按位置
    1. select
    2. from
    3. wherehaving
    4. exists
  2. 按结果集
    1. 标量子查询:一行一列
    2. 列子查询:多行一列
    3. 行子查询:一行多列
    4. 表子查询:任意,一般为多行多列
wherehaving后面

支持标量子查询、列子查询和行子查询

特点

  1. 子查询语句放在小括号内
  2. 子查询一般放在条件右侧
  3. 标量子查询一般搭配单行操作符:> < >= <= <>;列子查询一般搭配多行操作符:in any/some all

标量子查询

谁的工资比 Abel 高

逐步分析

一、查询abel的工资

select salary
from employees
where last_name = 'Abel';

二、查询工资大于1结果的员工

select *
from employees
where salary > (
    select salary
    from employees
    where last_name = 'Abel'
);

返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id 和工资

逐步分析

一、查询141号员工的job_id

select job_id
from employees
where employee_id = 141;

二、查询143号员工的salary

select salary
from employees
where employee_id = 143;

三、结合1和2结果查询

select last_name, job_id, salary
from employees
where job_id = (
    select job_id
    from employees
    where employee_id = 141
)
  and salary > (
    select salary
    from employees
    where employee_id = 143
);

返回公司工资最少的员工的last_name,job_id和salary

逐步分析

一、查询公司最低的工资

select min(salary) min
from employees;

二、结合结果1查询

select last_name, job_id, salary
from employees
where salary = (
    select min(salary) min
    from employees
);

查询最低工资大于50号部门最低工资的部门id和其最低工资

逐步分析

一、查询50号部门的最低工资

select min(salary)
from employees
where department_id = 50;

二、结合结果1查询

select department_id, min(salary) min
from employees
group by department_id
having min > (
    select min(salary)
    from employees
    where department_id = 50
);

列子查询

返回location_id是1400或1700的部门中的所有员工姓名

逐步分析

一、查询location_id是1400或1700的部门id

select distinct department_id
from departments
where location_id in (1400, 1700);

二、查询在这些部门id的员工姓名

select last_name
from employees
where department_id in (
    select distinct department_id
    from departments
    where location_id in (1400, 1700)
);

或者使用any关键字

select last_name
from employees
where department_id = any (
    select distinct department_id
    from departments
    where location_id in (1400, 1700)
);

返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary

# 1
select distinct salary
from employees
where job_id = 'IT_PROG';
# 2
select employee_id, last_name, job_id, salary
from employees
where job_id <> 'IT_PROG'
  and salary < any (
    select distinct salary
    from employees
    where job_id = 'IT_PROG'
);

或者使用max关键字代替

select employee_id, last_name, job_id, salary
from employees
where job_id <> 'IT_PROG'
  and salary < (
    select max(salary)
    from employees
    where job_id = 'IT_PROG'
);

返回其它部门中比job_id为‘IT_PROG’部门所有员工工资都低的员工的员工号、姓名、job_id 以及salary

# 1
select distinct department_id
from employees
where job_id = 'IT_PROG';
# 2
select distinct salary
from employees
where department_id = (
    select distinct department_id
    from employees
    where job_id = 'IT_PROG'
);
# 3
select employee_id, last_name, job_id, salary
from employees
where department_id <> (
    select distinct department_id
    from employees
    where job_id = 'IT_PROG'
)
  and (
        salary < all (select distinct salary
                      from employees
                      where department_id = (
                          select distinct department_id
                          from employees
                          where job_id = 'IT_PROG'
                      ))
    );

行子查询

查询员工编号最小并且工资最高的员工信息

方法一:标量查询方法

# 1
select min(employee_id)
from employees;
# 2
select max(salary)
from employees;
# 3
select *
from employees
where employee_id = (
    select min(employee_id)
    from employees
)
  and salary = (
    select max(salary)
    from employees
);

方法二:行子查询

select *
from employees
where (employee_id, salary) = (
    select min(employee_id), max(salary)
    from employees
);
select后面

仅支持标量子查询

查询每个部门的员工个数

select d.department_id,
       (
           select count(*)
           from employees e
           where e.department_id = d.department_id
       ) count
from departments d;

查询员工号=102的部门名

select (select department_name from departments d where d.department_id = e.department_id) d_name
from employees e
where e.employee_id = 102;
from后面

表子查询,将子查询结果充当一张表,要求必须取别名

查询每个部门的平均工资的工资等级

# 1
select department_id, avg(salary) avg
from employees
group by department_id;
# 2
select d.department_id did, d.avg avg, j.grade_level
from (select department_id, avg(salary) avg
      from employees
      group by department_id) d
         left join job_grades j on d.avg between j.lowest_sal and j.highest_sal;
exists后面

语法

select exists(查询语句);

判断结果集是否存在记录

结果为01

查询有员工的部门名

select department_name
from departments d
where exists(
              select * from employees e where e.department_id = d.department_id
          );

分页查询

语法
select 列表 from 表名 limit offset,size;

注意

  1. 此处的起始索引offset0开始,size条目个数, 当offset0时可省略
  2. limit语句放在查询语句的最后
分页语句
select 列表 from 表名 limit (page-1)*size,size;

查询前五条员工信息

select *
from employees
limit 5;

查询第11条至第25条

# offset=10 size=25-11+1
select *
from employees
limit 10,15;

有奖金的员工信息,并且工资较高的前10名显示出来

select *
from employees
where commission_pct is not null
order by salary desc
limit 10;

联合查询

将多条查询语句的查询结果合并成一个

语法
查询语句1
union
查询语句2;
应用场景

要查询的结果来源于多个表,且各表间没有连接关系,只是查询信息一致

特点
  1. 要求查询语句的字段列数一致
  2. 联合表字段名为第一条查询语句的字段名
  3. 要求查询字段的类型与顺序最好一致
  4. union关键字自动去重,使用union all则不会去重

查询部门编号>90或邮箱包含a的员工信息

常规方式

select *
from employees
where department_id > 90
   or email like '%a%';

联合查询

select *
from employees
where department_id > 90
union
select *
from employees
where email like '%a%';

查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

数据操作

数据插入

方式一

语法

insert into 表名(字段列表) values(值列表);

特点

  1. 列的顺序可以调换
  2. 列的个数和值的个数必须一致
  3. 可以忽略列名,此时默认所有列,且顺序与表中字段顺序一致

示例

插入的值的类型要与列的类型一致或兼容

insert into beauty(id, name, sex, borndate, phone, photo, boyfriend_id)
values (13, '杨超越', '女', '2019-12-12', '18888888888', null, 9);

不可为null的列必须插入值,可为null的值可直接插入null或直接略过

null情况

一、在photo列中直接插入null

insert into beauty(id, name, sex, borndate, phone, photo, boyfriend_id)
values (13, '杨超越', '女', '2019-12-12', '18888888888', null, 9);

二、直接略过photo

insert into beauty(id, name, sex, borndate, phone, boyfriend_id)
values (13, '杨超越', '女', '2019-12-12', '18888888888', 9);

方式二

语法

insert into 表名 set 列1=值1, 列2=值2;

示例

insert into beauty
set id=14,
    name='刘涛',
    phone='19999999999';

对比

方式一 方式二
一次插入多条语句 支持 不支持
子查询 支持 不支持

数据修改

语法

update 表名 set 列1=值1, 列2=值2 where 条件;

单表修改

修改beauty表中姓周的女神的电话为13899888899

update beauty
set phone = 13899888899
where name like '周%';

修改boys表中id号为2的名称为张飞,魅力值10

update boys
set boyName = '张飞',
    userCP  = 10
where id = 2;

多表修改

sql92语法

update 表1 b1, 表2 b2 set 列1=值1, 列1=值1 where 连接 and 筛选;

sql99语法

update 表1 b1 join 表2 b2 on 连接 set 列1=值1, 列2=值2 where 筛选;

修改张无忌的女朋友的手机号为114

update beauty be
    join boys bo on be.boyfriend_id = bo.id
set be.phone = 114
where bo.boyName = '张无忌';

修改没有男朋友的女神的男朋友编号都为2号

update beauty be
    left join boys bo on be.boyfriend_id = bo.id
set be.boyfriend_id = 2
where bo.id is null;

数据删除

关键字 - delete

单表删除

语法

delete from 表名 where 条件;

注意:当没有限制条件时,效果为清空表

删除手机号以9结尾的女神信息

delete
from beauty
where phone like '%9';

多表删除

sql92语法

delete b1, b2 from 表1 b1, 表2 b2 where 连接 and 筛选;

sql99语法

delete b1, b2 from 表1 b1 join 表2 b2 on 连接 where 筛选

删除张无忌的女朋友的信息

delete be
from boys bo
         join beauty be on be.boyfriend_id = bo.id
where bo.boyName = '张无忌';

删除黄晓明的信息及其女朋友的信息

delete bo,be
from beauty be
         join boys bo on be.boyfriend_id = bo.id
where bo.boyName = '黄晓明';

关键字 - truncate

清空表,且清空表时使用truncate效率更高

语法

truncate table 表名;

对比

delete truncate
作用 删除记录 清空表
筛选条件 支持 不支持
自增值 从断点恢复 重新开始
返回值
事务回滚 支持 不支持

库和表的管理

库的管理

创建库

create database if not exists books;

或者

drop database if exists books;
create database books;

修改库

查看库的字符集

show variables like '%character_set%';

修改库的字符集

alter database books character set 'utf8';

删除库

drop database if exists books;

表的管理

创建表

创建book表

create table if not exists book
(
    id           int,
    b_name       varchar(20),
    price        double,
    author_id    int,
    publish_date datetime
);

创建author表

create table if not exists author
(
    id     int,
    a_name varchar(20),
    nation varchar(20)
);

或者

drop table if exists author;
create table author
(
    id     int,
    a_name varchar(20),
    nation varchar(20)
);

修改表

修改列名

注意:最后还要加上新列名的类型,即列改名的同时亦能修改列类型

alter table book
    change column publish_date pub_date datetime;

修改列的类型或约束

alter table book
    modify column pub_date timestamp;

新增列

alter table author
    add column annual double;

删除列

alter table author
    drop column annual;

修改表名

alter table author rename to book_author;

删除表

drop table if exists book_author;

复制表

仅复制表结构

create table author_copy like author;

复制表结构及数据

create table author_copy_data
select *
from author;

仅复制部分结构

create table author_copy_column
select a.id, a.a_name
from author a
where 0;

复制部分数据

create table author_copy_part
select a.id, a.a_name
from author a
where a.nation = '中国';

类型与约束

常见类型

分类

  1. 数值型

    1. 整型
    2. 小数
      1. 浮点型
      2. 定点型
  2. 字符型

    1. 较短的文本
    2. 较长的文本
  3. 日期型

数值型

整型

默认有符号,若想设置无符号需追加unsigned关键字,无符号数值都为非负数

超出数值范围则报Out of range value无法插入

若不设置长度则有默认长度

长度代表数值宽度,可设zerofill关键词用0在左边填充,同时标记为无符号

有符号

create table t_int
(
    t1 int
);

无符号,类型追加unsigned关键字

create table t_int
(
    t1 int,
    t2 int unsigned
);

小数

分类

  1. 浮点型:float(m,d)double(m,d)
  2. 定点型:dec(m,d)/decimal(m,d)

特点

  1. m:整数个数 + 小数个数
  2. d:小数个数
  3. 若数值个数超过m,则插入失败
  4. floatdouble默认无限制,精度决定于插入数值的精度,decimalm默认为10d默认为0

指定md

create table t_float
(
    f1 float(5, 2),
    f2 double(5, 2),
    f3 dec(5, 2)
);

默认md

create table t_float
(
    f1 float,
    f2 double,
    f3 dec
);

字符型

分类

  1. 较短文本
    1. char:固定长度的文本;可以省略默认为1
    2. varchar:可变长度的文本;不可省略
    3. 其他
      1. binary、varbinary:保存二进制数值
      2. enum:保存枚举
      3. set:保存集合
  2. 较长文本
    1. text
    2. blob

测试enum

create table t_char(
    c1 enum('a','b','c')
);
insert into t_char values ('a');
insert into t_char values ('b');
insert into t_char values ('c');
insert into t_char values ('d');

测试set

create table t_set(
    s1 set('a','b','c')
);
insert into t_set values ('a');
insert into t_set values ('a,b');
insert into t_set values ('a,b,d');

日期型

分类

  1. datetime:保存日期和时间;占8个字节;可为null
  2. date:保存日期
  3. time:保存时间
  4. year:保存年
  5. timestamp:保存日期和时间,容易受时区和版本影响;占4个字节;不可为null

示例

create table t_date(
    t1 datetime,
    t2 timestamp
);

insert into t_date values(now(),now());

常见约束

约束表中的数据,保证表中数据的准确性和可靠性

类型

  1. not null:保证该字段不为空
  2. default:默认值
  3. primary key:主键,保证数据的唯一性,并且非空
  4. unique:保证数据的唯一性,可为空
  5. check:检查约束,mysql不支持
  6. foreign key:外键,用于保证该字段的值必须来源于关联列的值,于从表中添加约束引用主表某列的值

位置

  1. 列级约束:除了外键
  2. 表级约束:除了非空和默认

对比 - 主键和唯一键

主键 唯一键
唯一性 唯一 唯一
null 不允许 允许
个数 至多1个 不限
组合 可组合 可组合

对于外键而言,主表的被引用列必须是一个键,如主键或唯一

添加约束 - 创建表

添加列级约束

create table stuinfo
(
    id       int primary key,
    stu_name varchar(20) not null,
    gender   enum ('男','女'),
    seat     int unique,
    age      int default 16,
    major_id int
);
create table major
(
    id         int primary key,
    major_name varchar(20)
);

添加表级约束

create table stuinfo
(
    id       int,
    stu_name varchar(20),
    gender   char,
    seat     int,
    age      int,
    major_id int,
    constraint pk primary key (id),
    constraint uq unique (seat),
    constraint fk_stuinfo_major foreign key (major_id) references major (id)
);

或者省略constraint关键字和约束名,此时约束名为字段名

create table stuinfo
(
    id       int,
    stu_name varchar(20),
    gender   char,
    seat     int,
    age      int,
    major_id int,
    primary key (id),
    unique (seat),
    foreign key (major_id) references major (id)
);

综合

create table stuinfo
(
    id       int primary key,
    stu_name varchar(20) not null,
    gender   enum ('男','女'),
    seat     int unique,
    age      int default 16,
    major_id int,
    constraint fk_stuinfo_major foreign key (major_id) references major (id)
);

添加约束 - 修改表

添加唯一

alter table stuinfo modify column seat int unique;

添加主键

列级约束

alter table stuinfo modify column id int primary key;

表级约束

alter table stuinfo add primary key(id);

添加外键

alter table stuinfo add constraint fk_stuinfo_major foreign key(major_id) references major(id);

删除约束

删除非空约束

alter table stuinfo modify column stu_name varchar(20) null;

删除默认

alter table stuinfo modify column age int;

删除主键

alter table stuinfo drop primary key;

删除唯一,其中seat为键名

alter table stuinfo drop index seat;

删除外键

alter table stuinfo drop foreign key fk_stuinfo_major;

标识列

又称自增长列,可不用手动插入数值,系统提供默认值

当在标识列手动插入值时,该条记录会根据标识列的值在表中自动排序

特点

  1. auto_increment必须与搭配,如主键、唯一等
  2. 一个表至多1个标识列
  3. 只支持数值型

设置标识列 - 创建表

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

此时插入数据可以使用null顶替标识列

insert into t_identity
values (null, 'john');

设置标识列 - 修改表

alter table t_identity
    modify column id int auto_increment;

删除标识列

alter table t_identity modify column id int;

事务

概述

事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的

整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚,所有受到影响的数据将返回到事物开始以前的状态

如果单元中的所有SQL语句均执行成功,则事物被顺利执行

属性

  1. 原子性Atomicity):一个事务不可再分割,要么都执行要么都不执行
  2. 一致性Consistency):一个事务执行会使数据从一个一致状态切换到另外一个一致状态
  3. 隔离性Isolation):一个事务的执行不受其他事务的干扰
  4. 持久性Durability):一个事务一旦提交,则会永久的改变数据库的数据

并发问题

对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题

  1. 脏读:对于两个事务 T1和T2,T1 读取了已经被 T2 更新但还没有被提交的字段,之后若 T2 回滚,T1读取的内容就是临时且无效的
  2. 不可重复读:对于两个事务T1和T2,T1 读取了一个字段,然后 T2 更新了该字段,之后T1再次读取同一个字段时值不同了
  3. 幻读:对于两个事务T1和T2,T1 从一个表中读取了一个字段,然后 T2 在该表中插入了一些新的行,之后若 T1再次读取同一个表就会多出几行

隔离性

数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响,避免各种并发问题

隔离级别

一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱

隔离级别 描述
read uncommitted 可能出现脏读、不可重复读和幻读
read committed 避免脏读,可能出现幻读和不可重复读
repeatable read 避免脏读、不可重复读,可能出现幻读
serializable 可避免脏读、不可重复读和幻读,但并发行最低,性能低下

MySQL中默认repeatable read

查看隔离级别

select @@tx_isolation;

设置隔离级别

set session|global transaction isolation level 隔离级别;

创建

类型

  1. 隐式事务:事务没有明显的开启和结束的标识
  2. 显式事务:事务有明显的开启和结束的标识,前提是将自动提交设为禁用

步骤

  1. 开启事务

    set autocommit = 0;
    start transaction; # 可选的,禁用自动提交后自动启动事务
  2. 编写sql语句,对数据的增删改查

  3. 结束事务

    commit; # 提交
    rollback; # 撤销

实现

一、禁用自动提交,开启事务

set autocommit = 0;

二、编写一组事务的语句

update account set balance = 500 where username = '张无忌';
update account set balance = 1500 where username = '赵敏';

三、结束事务

commit;

保存节点

可以使用savepoint关键字保存事务进度

set autocommit = 0;
delete from account where id = 1;
savepoint a;
delete from account where id = 2;
rollback to a;

此时会回滚到保存点a,舍弃之后的操作

视图

概述

一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果

创建视图

语法

create view 视图名
as
查询语句

示例

查询各部门的平均工资级别

一、创建视图

create view v1 as
select department_id did, avg(salary) avg
from employees e
group by department_id;

二、使用创建的视图

select v.did, j.grade_level
from v1 v
         join job_grades j on v.avg between j.lowest_sal and j.highest_sal;

查询平均工资最低的部门信息

使用之前创建的视图

select d.*
from departments d
         join v1 v on d.department_id = v.did
order by v.avg
limit 1;

查询平均工资最低的部门名和和该部门最低的工资

一、再创建新的视图

create view v2
as
select did
from v1
order by avg
limit 1;

二、使用该视图

select d.department_name, e.salary
from employees e
         join departments d on e.department_id = d.department_id
         join v2 v on e.department_id = v.did
order by e.salary
limit 1;

修改视图

语法

方式一

create or replace view 视图名
as
查询语句

方式二

alter view 视图名
as
查询语句

示例

方式一

create or replace view v1
as
select e.job_id, avg(e.salary) avg_salary
from employees e
group by e.job_id;

方式二

alter view v1
    as
        select *
        from departments;

删除视图

drop view v1;

查看视图

查看视图能查询到的数据

select * from v1;

查看视图的字段结构

desc v1;

查看视图的创建语句

show create view v1;

变量

分类

  1. 系统变量
    1. 全局变量
    2. 会话变量
  2. 自定义变量
    1. 用户变量
    2. 局部变量

系统变量

若为全局级别则加上global,会话级别则为session,不写默认session级别

查看

查看全局系统变量

show global variables;

查看满足条件的全局变量

show global variables like '%char%';

查看指定的全局变量

select @@global.autocommit;

赋值

方式一

set global character_set_server = 'utf8';

方式二

set @@global.character_set_server = 'utf8';

自定义变量

其中用户变量的作用域同于会话变量,而局部变量的作用域仅在beginend

声明并初始化

方式一

set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;

方式二

select count(*) into @count from employees;

查看

select @count;

示例

查询平均工资最低的部门名和和该部门最低的工资

select did into @min_did from v1 order by avg limit 1;

select d.department_name, e.salary
from employees e
         join departments d on e.department_id = d.department_id
where e.department_id = @min_did
order by e.salary
limit 1;

MySQL 逻辑架构

介绍

和其它数据库相比,MySQL 有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离,可以根据业务的需求和实际需要选择合适的存储引擎

图示分析

组件分析

连接层

最上层是一些客户端和连接服务,包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信,主要完成一些类似于连接处理、授权认证、及相关的安全方案

在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程,同样在该层上可以实现基于 SSL 的安全链接,服务器也会为安全接入的每个客户端验证它所具有的操作权限

服务层

Management Serveices & Utilities

系统管理和控制工具

SQL Interface

SQL 接口,接受用户的 SQL 命令,并且返回用户需要查询的结果,比如 select 语句就是调用的 SQL Interface

Parser

解析器,SQL 命令传递到解析器的时候会被解析器验证和解析

Optimizer

查询优化器,SQL 语句在查询之前会使用查询优化器对查询进行优化,例如查询

select uid, name from user where gender=1; 

此时,优化器会决定先投影还是先过滤

Caches & Buffers

查询缓存,如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据,该缓存机制由一系列小缓存组成,比如表缓存,记录缓存,key 缓存,权限缓存等

缓存负责读,缓冲负责写

引擎层

存储引擎层,存储引擎真正的负责了 MySQL 中数据的存储和提取,服务器通过 API 与存储引擎进行通信

不同的存储引擎具有的功能不同,可以根据实际的需要进行选取

存储层

数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互

查询流程分析

图示分析

流程分析

查询

MySQL 客户端通过协议与 MySQL 服务器建立连接,发送查询语句

缓存匹配

先检查查询缓存,如果 SQL 命中,则直接返回结果,否则进行语句解析,也就是说,在解析查询之前服务器会先访问查询缓存 (Query Cache),它存储了 SELECT 语句以及相应的查询结果集

如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行,它仅仅将缓存中的结果返回,提高了系统的性能

解析

经过语法解析器和预处理,首先 MySQL 通过关键字将 SQL 语句进行解析,并生成一颗对应的解析树

MySQL 解析器将使用 MySQL 语法规则验证和解析查询,预处理器则根据一些 MySQL 规则进一步检查解析树是否合法

优化

当查询优化器认为解析树是合法的时候,由优化器将其转化成执行计划,一条查询可以有很多种执行方式,最后都返回相同的结果,优化器的作用就是找到这其中最好的执行计划

索引

介绍

定义

索引 (Index) 是帮助 MySQL 高效获取数据的数据结构,可以得到索引的本质为数据结构,即一种排好序的快速查找数据结构

在数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引

结构分析

下图是一种可能的索引方式示例

其中,左边是数据表,一共有两列七条记录,最左边的是数据记录的是物理地址

为了加快 Col2 的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录

一般索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上

聚集索引、次要索引、覆盖索引、复合索引、前缀索引和唯一索引,默认都是使用的是 B+Tree 索引,都统称为索引

除了 B+Tree 这种类型的索引之外,还有哈稀索引等

优劣势

优势

类似大学图书馆建书目索引,建立索引的目的是为了提高数据检索的效率,降低数据库的 IO 成本

通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗

劣势

索引实际上也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的,当索引过多过大时,同样需要消耗较多的系统资源

虽然索引大大提高了查询速度,同时却会降低更新表的速度,因为更新表时,MySQL 不仅要保存数据,还要保存索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息

分类

主键索引

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

语法示例

随表一起建索引,使用 AUTO_INCREMENT 关键字的列必须有索引

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

单独建主键索引

ALTER TABLE customer add PRIMARY KEY customer(customer_no);   

删除主键索引

ALTER TABLE customer drop PRIMARY KEY;

单值索引

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

语法示例

随表一起建索引

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

单独建单值索引

CREATE INDEX idx_customer_name ON customer(customer_name);
ALTER TABLE customer ADD INDEX idx_customer_name(customer_name);

删除索引

DROP INDEX idx_customer_name on customer;

唯一索引

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

建立唯一索引时,必须保证所有的值是唯一的,除了 null,若有重复数据,则会报错

语法示例

随表一起建索引

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) 
); 

单独建唯一索引

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
ALTER TABLE customer ADD UNIQUE idx_customer_no(customer_no)

删除索引

DROP INDEX idx_customer_no on customer; 

复合索引

即一个索引包含多个列,在数据库操作期间,复合索引比多列单值索引所需要的开销更小

语法示例

随表一起建索引

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) 
);

单独建索引

CREATE INDEX idx_no_name ON customer(customer_no, customer_name);

删除索引

DROP INDEX idx_no_name on customer;

复合索引和单值索引对比

复合索引,no 与 name 拥有同一个索引

create index idx_no_name on emp(no, name);

单值索引,no 和 name 分别拥有一个索引

create index idx_no on emp(no);
create index idx_name on emp(name);

索引使用场景

适合创建索引的场景

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段,外键关系建立索引,例如 A join B on A.bid = B.id,既 A 表查询 B 表的条件,在 join 中,join 左边的表会用每一个字段去遍历 B 表的所有的关联数据,相当于一个查询操作
  • 在高并发的情况下更倾向创建组合索引
  • 查询中进行排序的字段,排序字段若通过索引去访问将大大提高排序速度,例如 group by 和 order by 后面的字段
  • 查询中统计或者分组字段

不适合创建索引的场景

  • 记录太少的表

  • 经常增删改的表和字段,因为虽然提高了查询速度,却会降低更新表的速度,对表进行更新操作时,MySQL 不仅要保存数据,还要保存索引文件

  • Where 条件用不到的字段不创建索引

  • 数据重复且分布平均的表字段,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果,因为索引的选择性是指索引列中不同值的数目与表中记录数的比值

    例如一个表中有 2000 条记录,表索引列有 1980 个不同的值,则该索引的选择性为1980 / 2000 = 0.99

    一个索引的选择性越接近于 1,则该索引的效率就越高

性能分析 Explain

介绍

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句,分析查询语句或是表结构的性能瓶颈

建表脚本准备

create table t1(id int(11) auto_increment, content varchar(100), primary key(id));
create table t2(id int(11) auto_increment, content varchar(100), primary key(id));
create table t3(id int(11) auto_increment, content varchar(100), primary key(id));
create table t4(id int(11) auto_increment, content varchar(100), 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)));

基本使用

explain + SQL 语句,例如:

explain select * from t1;

会得到一个表格,展示该 SQL 语句的执行情况

字段解释

字段 id

介绍

select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序

值情况

  • id 相同

    执行语句

    explain select * from t1, t2, t3 where t1.id = t2.id and t2.id = t3.id;

    在 id 相同的情况下,语句执行顺序由上至下

    在此例中,先执行 where 后的第一条语句 t1.id = t2.id 通过 t1.id 关联 t2.id,再执行 t2.id = t3.id

  • id 不同

    执行语句

    explain select * from t1 where id = (
        select id from t2 where id = (
            select id from t3 where content = 't3_267'
        )
    );

    id 不同,id 值越大优先级越高,越先被执行

    如果是子查询,id 的序号会递增

  • id 有相同有不同

    执行语句

    explain select * from t1, (
        select id from t2 where id = (
            select id from t3 where content = 't3_267'
        )
    ) s2 where t1.id = s2.id;

    id 如果相同,则认为是同一组,从上往下顺序执行

    在所有组中,id 值越大,优先级越高,越先执行

字段 select_type

介绍

SQL 查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询类型

值情况

  • SIMPLE:简单的 select 查询,查询中不包含子查询或者联合查询
  • PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为 Primary
  • DERIVED:在 FROM 列表中包含的子查询被标记为 DERIVED (衍生表),MySQL 会递归执行这些子查询,把结果放在临时表里
  • SUBQUERY:在 SELECT 或 WHERE 列表中包含了子查询
  • DEPENDENT SUBQUERY:在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层
  • UNCACHEABLE SUBQUREY:无法被缓存的子查询
  • UNION:若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION;若 UNION 包含在 FROM 子句的子查询中,外层 SELECT 将被标记为 DERIVED
  • UNION RESULT:从 UNION 结果表中获取结果的 SELECT

字段 table

显示该行的数据是关于哪张表的

字段 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

一般进行优化时,得保证查询至少达到 range 级别,最好能达到 ref

常见值情况

  • system:表示只有一行记录,相当于系统表,是 const 类型的特列,一般不会出现,可以忽略不计

  • const:表示通过索引一次找到匹配记录,只匹配一行数据,如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量,例如执行

    explain select * from t1 where id = 1;

    此时只匹配了一条记录

  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描,例如执行

    explain select * from t1, t2 where t1.id = t2.id;

    此时只匹配了一条记录

  • ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,例如

    首先创建非唯一索引

    create index idx_content on t2(content);

    执行查询

    explain select * from t2 where content = 't2_abc';
  • range:只检索给定范围的行记录,一般就是在 where 语句中出现 between、<、>、in 等范围条件查询,范围索引扫描比全表扫描要好,因为只需要开始于索引的某一点,结束于另一点,不用扫描全部索引

    例如执行查询

    explain select * from t2 where id < 2;
  • index:Full Index Scan,index 与 all 区别为 index 类型只遍历索引树的值,这通常比 all 快,因为索引文件通常比数据文件小,虽然 all 和 index 都是读全表,但 index 是从索引中读,而 all 是从硬盘中读

    例如执行

    explain select id from t1;
  • all:Full Table Scan,将遍历全表以找到匹配的行记录

字段 possible_keys

显示可能应用在这张表中的索引,一个或多个

查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

字段 key

实际使用的索引,如果为 NULL,则没有使用索引

查询中若使用了覆盖索引,则该索引仅出现在 key 列表中

字段 key_len

表示索引中使用的字节数,可通过该列计算查询中所使用的索引的长度,一般精度越高的情况下,索引长度越大

key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索得出的

字段 ref

显示哪些列或常量被用于查找索引列上的值

例如

explain select * from t1, t2, t3 where t1.id = t2.id and t2.id = t3.id and t2.content = '';

由于 id 相同,查询语句按照从上至下顺序执行

最先被执行的是 t2 表的查询语句,根据 content 字段先找到常量值 '' 引用

然后是 t3 表的查询语句,找到最先出现的 t2 表的字段 id 引用 test.t2.id,其中 test 是数据库名,t2 为表名,id 为被引用字段

最后是 t1 表的查询语句,找到最先出现的 t2 表的字段 id 引用

字段 rows

根据表统计信息及索引选用情况,MySQL 大致估算出找到所需的记录所需要读取的行数,越少越优

字段 extra

介绍

包含不适合在其他列中显示但十分重要的额外信息

常见值情况

  • using filesort:说明此时 MySQL 会对数据使用一个外部的排序,而不是按照表内的索引顺序进行读取,MySQL 中无法利用索引完成的排序操作称为文件排序

  • using temporary:MySQL 在对查询结果排序时使用临时表保存中间结果,常见于排序 order by 和分组查询 group by

  • using index:表示相应的 select 操作中使用了覆盖索引 (Covering Index),避免了访问表的数据行,效率更高

    如果同时出现 using where,表明索引被用来执行索引键值的匹配查找

    如果没有同时出现 using where,表明索引只是用来读取数据而非利用索引执行查找

    覆盖索引的理解,索引叶子节点存储了索引的数据值,当能通过读取索引得到所需数据时,就无需读取数据行,若 select 列表字段与 where 列表字段和索引本身所含的字段重合覆盖,此时所需要的字段叫做覆盖索引

  • using where:表明使用了 where 条件过滤

  • using join buffer:使用了连接缓存

  • impossible where:where 子句的值总是 false,例如

    select * from emp where name = 'roger' and name = 'jack';

查询优化

单表查询优化

建表准备

CREATE TABLE IF NOT EXISTS article( 
    id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    author_id INT(10) NOT NULL,
    category_id INT(10) NOT NULL,
    views INT(10) NOT NULL,
    comments INT(10) NOT NULL,
    title VARBINARY(255) NOT NULL,
    content TEXT NOT NULL
); 
INSERT INTO article(author_id, category_id, views, comments, title, content) 
VALUES (1, 1, 1, 1, '1', '1'), (2, 2, 2, 2, '2', '2'), (1, 1, 3, 3, '3', '3');

分析优化

需求

查询 category_id 为 1 且 comments 大于 1 的情况下,views 最多的 article_id

初始查询方法

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1; 

此时 type 是 ALL,Extra 里还出现了 Using filesort,为最坏的情况,需要进行优化

优化一

新建 category_id,comments 和 views 的索引

create index idx_article_ccv on article(category_id, comments, views); 

查看索引建立情况

show index from article;

此时再进行查询

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

type 由 all 变成了 range,但 extra 里仍然出现 Using filesort,说明字段排序时索引失效了

按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views

当 comments 字段在复合索引里的中间位置时,因为 comments > 1 条件是一个范围值,MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引失效

优化二

删除第一次建立的索引,重新建立

drop index idx_article_ccv on article;
create index idx_article_cv on article(category_id, views); 

再次进行查询

EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

此时 type 变为了 ref,Extra 中的 Using filesort 也消失了,完成优化

两表查询优化

建表准备

CREATE TABLE IF NOT EXISTS class(
    id INT(10) NOT NULL AUTO_INCREMENT,
    card INT(10) NOT NULL,
    PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS book(
    bookid INT(10) NOT NULL AUTO_INCREMENT,
    card INT(10) NOT NULL,
    PRIMARY KEY(bookid)
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

分析优化

需求

关联查询

初始查询方法

explain select * from class left join book on class.card = book.card; 

此时的 type 都为 all

优化

给左连接的右表添加索引

create index idx_card on book(card);

再次查询

explain select * from class left join book on class.card = book.card; 

此时 book 表查询的 type 变为了 ref,rows 查询行数也降低,优化效果明显

这是由左连接特性决定的,LEFT JOIN 条件用于确定如何从右表搜索行,因为左边一定都有,所以右边是关键点,需要建立索引

右连接同理

尽量使用小表驱动大表

三表查询优化

建表准备

CREATE TABLE IF NOT EXISTS phone(
    phoneid INT(10) NOT NULL AUTO_INCREMENT,
    card INT(10) NOT NULL,
    PRIMARY KEY(phoneid)
);
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));

分析优化

需求

三表关联查询

初始查询方法

explain select * from class
left join book on class.card = book.card
left join phone on class.card = phone.card;

此时三个查询操作的 type 都是 all

优化

给左连接右边的表字段添加索引

create index idx_card on book(card);
create index idx_card on phone(card);

再次进行查询

explain select * from class
left join book on class.card = book.card
left join phone on class.card = phone.card;

此时后两行的 type 都为 ref,且估计查询行数降低,优化效果明显

索引失效情况

建表准备

创建表插入数据

CREATE TABLE staffs(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(24) NULL DEFAULT '' COMMENT '姓名',
    age INT NOT NULL DEFAULT 0 COMMENT '年龄',
    pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
    add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) COMMENT '员工记录表';
INSERT INTO staffs(NAME, age, pos, add_time) VALUES('z3', 22, 'manager', NOW());
INSERT INTO staffs(NAME, age, pos, add_time) VALUES('July', 23, 'dev', NOW());
INSERT INTO staffs(NAME, age, pos, add_time) VALUES('2000', 23, 'dev', NOW());
INSERT INTO staffs(NAME, age, pos, add_time) VALUES(null, 23, 'dev', NOW());

创建索引

ALTER TABLE staffs ADD INDEX idx_name_age_pos(name, age, pos);

案例情况

一、没有遵守最佳左前缀法则

如果索引了多列,则需要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列

例如索引 idx_name_age_pos 建立时以 (name, age, pos) 的顺序建立,name 字段在最左边

当执行查询语句

explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
explain select * from staffs where name = 'July' and age = 23;
explain select * from staffs where name = 'July';
explain select * from staffs where pos = 'dev' and age = 23 and name = 'July';

都能正确使用到索引,尽管第 4 条查询语句的 name 字段不在最左边,但是通过 MySQL 的优化器优化,会自动对字段顺序进行重新排序

但如果执行查询语句

explain select * from staffs where age = 23 and pos = 'dev';

此时没有使用 name 字段开头,导致索引失效,进行全表扫描

explain select * from staffs where name = 'July' and pos = 'dev';

此时中间跳过 age 字段,查询语句虽然没有进行全表扫描,但是其索引长度与只有 name 字段时完全一致,说明没有使用到 pos 字段的索引

二、在索引列上做计算、函数和类型转换等操作

正常使用索引时

explain select * from staffs where name = 'July';

当对索引字段 name 进行操作时,例如套上函数

explain select * from staffs where left(name, 4) = 'July';

此时虽然查询结果相同,但是经过函数嵌套的索引字段无法生效,进而全表扫描

三、索引中使用范围条件字段右边的字段

若在同一个组合索引中,存在范围条件索引,则该范围条件索引右边的索引会失效

explain select * from staffs where name = 'July' and age > 23 and pos = 'dev';

此时 age 字段右边的 pos 字段索引失效

四、使用不等于 (!= 或者 <>) 操作运算

使用 != 和 <> 的字段索引失效,!= 针对数值类型,<> 针对字符类型

explain select * from staffs where name != 'July';

五、使用 is not null

在使用 is not null 条件时,索引会失效

explain select * from staffs where name is not null;

但使用 is null 时可以正常使用索引

explain select * from staffs where name is null;

六、like 以通配符开头

在使用 like 范围条件查询时,如果匹配条件使用通配符 % 开头,索引会失效,变成全表扫描

explain select * from staffs where name like '%July';
explain select * from staffs where name like '%July%';

当不以通配符开头时,则可以正常使用索引,此时索引类型为范围索引

explain select * from staffs where name like 'July%';

并且其右边的索引条件也不会失效

explain select * from staffs where name like 'July%' and age = 23;

如果查询条件需要以通配符开头,并且需要使用索引,则可以采取覆盖索引的方法

explain select name, age, pos from staffs where name like '%July%';

七、字符串不加单引号

如果 varchar 类型的值没有使用单引号,其底层会进行自动类型转换,导致索引字段失效

explain select * from staffs where name = 2000;

八、使用 or 连接

使用 or 连接查询条件时,会导致索引失效

explain select * from staffs where name = 'July' or name = 'z3';

order by 优化

介绍

MySQL 支持两种方式的排序,Filesort 和 Index

Index 排序的效率高,它指 MySQL 扫描索引时完成排序

Filesort 文件排序的方式效率较低,需要 MySQL 使用额外的排序方式进行操作

因此,尽量使用 Index 方式进行排序,避免使用 Filesort 排序

order by 满足两种情况时,会使用 Index 方式排序:

  • order by 语句使用索引最左前列
  • 若最左前列在 where 子句中并且为常量时,与 order by 子句的顺序条件列组合,可以让索引生效

而当 where 子句中出现索引的范围查询时,则会导致 order by 索引失效

Filesort 文件排序

介绍

Filesort 文件排序有两种算法,双路排序和单路排序

双路排序

MySQL 4.1 之前使用的是双路排序,就是两次扫描磁盘,最终得到数据

读取行指针和 order by 列进行排序,然后扫描排序好的列表,按照列表中的值重新从磁盘中读取对应的数据输出

双路排序需要借助磁盘进行排序,从磁盘取排序字段到 buffer 中进行排序,再从磁盘中取其他字段,进行了 2 次 IO 操作,因此速度比较慢

单路排序

取一批数据,要对磁盘进行两次扫描是很耗时的,所以在 MySQL 4.1 之后,出现了第二种改进的算法,就是单路排序

从磁盘读取查询需要的所有列,按照 order by 列在 buffer 进行排序,然后扫描排序后的列表直接进行输出,效率更快

其避免了第二次从磁盘中读取数据,并且把随机 IO 变成顺序 IO,但同时会使用更多的内存空间,因为它把每一行都保存在了内存中

在 sort_buffer 中,单路排序比双路排序占用更多的空间,因为单路排序是把所有字段都取出,所以可能取出的数据的总大小超出了 sort_buffer 的容量,导致每次只能从磁盘中取 sort_buffer 容量大小的数据进行排序,排完再取 sort_buffer 容量大小的数据再排,从而导致更多次的 IO 扫描

建表准备

CREATE TABLE tblA( 
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    age INT,
    birth TIMESTAMP NOT NULL,
    name varchar(200)
);
INSERT INTO tblA(age, birth, name) VALUES(22, NOW(), 'abc');
INSERT INTO tblA(age, birth, name) VALUES(23, NOW(), 'bcd');
INSERT INTO tblA(age, birth, name) VALUES(24, NOW(), 'def');
CREATE INDEX idx_age_birth_name ON tblA(age, birth, name);

案例分析

正常使用 index 索引排序

explain select * from tblA where age > 20 order by age;

正常使用 index 索引排序

explain select * from tblA where age > 20 order by age, birth;

由于 order by 之后没有按照索引字段 age 开头,导致出现文件内排序

explain select * from tblA where age > 20 order by birth;

出现文件排序同上

explain select * from tblA where age > 20 order by birth, age;

而当 where 语句后面的索引最左列是一个常量时,与 order by 语句的顺序组合可以让索引生效

explain select * from tblA where age = 20 order by birth;

此时由于字段 age 和 birth 的排序方向并不相同,导致出现了文件排序

explain select * from tblA where age > 20 order by age asc, birth desc;

当索引字段都为升序或都为降序时,也可以正常使用索引排序

explain select * from tblA where age > 20 order by age desc, birth desc;

group by 优化

group by 的实质是先排序后进行分组,遵照索引建的最佳左前缀原则

where 使用优先级高于 having,能写在 where 限定的条件就尽量不用 having 限定

锁机制

概述

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

在数据库中,除传统的计算资源的争用以外,数据也是一种供许多用户共享的资源,保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素

锁类型分类

按操作类型分

  • 读锁 (共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响

  • 写锁 (排它锁):当前写操作没有完成前,它会阻断其他绘画进行写锁和读锁

按操作粒度分

介绍

为了尽可能提高数据库的并发度,每次锁定的数据范围越小越好,理论上每次只锁定当前操作数据的方案会得到最大的并发度,但是管理锁是很耗资源的事情,涉及获取、检查和释放锁等动作,因此数据库系统需要在高并发响应和系统性能两方面进行平衡,产生了锁粒度 (Lock Granularity) 的概念

分类

  • 表锁

  • 行锁

表锁

特点

偏向 MyISAM 存储引擎,开销小、加锁快、无死锁、锁定粒度大,但发生锁冲突的概率最高,并发度也是最低

基本操作

增加表锁,读锁或写锁

lock table tablename read/write;

查看表加过的锁

show open tables;

释放表锁

unlock tables;

小结

MyISAM 在执行查询语句前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁

锁类型 本会话其他表操作 其他会话可读 其他会话可写
读锁
写锁

对 MyISAM 表加读锁,不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求,只有当该读锁释放后,其它进程的写操作才会执行

对 MyISAM 表加写锁,会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,其它进程的读写操作才会执行

行锁

特点

偏向 InnoDB 存储引擎,开销大、加锁慢、会出现死锁、锁定粒度最小,但发生锁冲突的概率最低,并发度也最高

InnoDB 与 MyISAM 的主要的两点不同是,InnoDB 支持事务,并且采用了行锁

开启事务操作时,默认使用的是行锁,直到 commit; 提交,释放锁资源

当索引字段使用不当时,会导致数据操作从行锁变为表锁,大幅拖低效率

共享锁和排他锁

增加共享锁

示例

begin;
select * from tablename where field = 1 lock in share mode;

直到 commit; 提交后才会释放锁

共享锁又称读锁,是读取操作创建的锁,其他会话可以并发读取数据,但任何事务都不能对数据进行修改,被阻塞直到释放共享锁

如果事务 T 对数据 A 加上共享锁后,则其他事务只能对 A 再加共享锁,不能加排他锁,获得共享锁资源的事务只能读数据,不能更新数据

增加排他锁

示例

begin;
select * from tablename where field = 1 for update;

直到 commit; 提交后才会释放锁

排他锁又称写锁,如果事务 T 对数据 A 加上排他锁后,则其他事务不能再对 A 增加任何类型的锁,获得排他锁的事务既能读数据,也能更新数据

间隙锁

介绍

当使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB 会给符合条件的已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做间隙 (Gap), InnoDB 也会对这个间隙加锁,这种锁机制就是间隙锁 (Gap Lock)

危害

因为查询执行过程中通过范围查找会锁定整个范围内所有的索引键值,即使这个键值并不存在,造成在锁定的时候无法插入锁定键值范围内的任何数据,在某些场景下这可能会对性能造成很大的危害

行锁分析

检查命令

通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+

状态量说明

状态量 说明
Innodb_row_lock_current_waits 当前正在等待锁定的数量
Innodb_row_lock_time 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg 每次等待所花平均时间
Innodb_row_lock_time_max 从系统启动到现在等待最久一次等待所花的时间
Innodb_row_lock_waits 系统启动后到现在总共等待的次数

当等待次数很高,而且每次等待时长也不小的时候,就需要分析系统中为什么会有如此多的等待,然后根据分析结果制定优化计划

可以通过

select * from information_schema.INNODB_TRX;

查询正在被锁阻塞的 SQL 语句

小结

对比

Innodb 存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定

当系统并发量较高的时候,Innodb 的整体性能和 MyISAM 相比就会有比较明显的优势

但是 Innodb 的行级锁定同样也有其脆弱的一面,当使用不当的时候,可能会让 Innodb 的整体性能表现不仅不能比 MyISAM 高,甚至可能会更差

优化建议

尽可能让所有数据检索都通过索引来完成,避免无效索引导致行锁变为表锁

尽可能较少检索条件,避免间隙锁

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

在逻辑允许的情况下,尽可能使用低级别的事务隔离

Comments ( 0 )

Sign in for post a comment

SQL
1
https://gitee.com/huangli1279/mysql_learning.git
git@gitee.com:huangli1279/mysql_learning.git
huangli1279
mysql_learning
mysql_learning
master

Search

132457 8cb2edc1 1899542 131848 70c8d3a4 1899542