MySql高级

img

1. MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql是开源的，所以你不需要支付额外的费用。

Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL使用标准的SQL数据语言形式。

Mysql可以允许安装于多个系统上，并且支持多种语言。这些编程语言包括C、C#,C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

2. MySQL安装

官网下载地址：http://dev.mysql.com/downloads/mysql/

img

建议安装课前资料中提供的mysql安装包。

img

2.1. 检查是否安装过MySQL

检查当前系统是否安装过mysql

执行安装命令前，先执行查询命令

1． CentOS6

1
rpm -qa|grep mysql

如果存在mysql-libs的旧版本包如下：

img

请先执行卸载命令：rpm -e --nodeps mysql-libs

  1. 7：

    卸载5.7版本的mysql

    1、搜索安装的mysql

    ​ rpm -qa|grep -i mysql

    image-20191224085944153

    2、将搜索到的mysql安装程序全部卸载

    1
    2
    rpm -e --nodeps   mysql-community-client-5.7.16-1.el7.x86_64

    rpm -e –nodeps mysql-community-server-5.7.16-1.el7.x86_64

rpm -e –nodeps mysql-community-common-5.7.16-1.el7.x86_64

rpm -e –nodeps mysql-community-libs-5.7.16-1.el7.x86_64

1
2
3、搜索残留文件进行手动删除

find / -name mysql

1

rm -rvf mysql路径

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
   
   ![image-20191224090251286](assets/image-20191224090251286.png)

2． CentOS7

先执行查询：`rpm -qa|grep mariadb`

如果存在mariadb执行删除：`rpm -e --nodeps  mariadb-libs`

![img](assets\clip_image004.png)

 

## 2.2.   安装MySQL

在mysql的安装文件目录下执行：（必须按照顺序执行）

​```shell
rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm

rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm

查看安装版本：mysqladmin --version

查看mysql用户：id mysql

img

2.3. MySQL服务初始化

执行下面的命令初始化：

1
mysqld --initialize --user=mysql

使用mysql系统用户以安全模式为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码

查看密码：cat /var/log/mysqld.log

img

2.4. 修改初始化密码

要修改MySQL，首先必须启动MySQL服务，使用初始化密码登录后，才能修改密码：

启动MySQL服务：systemctl start mysqld.service

关闭MySQL服务：systemctl stop mysqld.service

img

修改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

img

2.5. MySQL自启动

查看mysql是否自启动（默认自启动）

mysql5.5: chkconfig –list

mysql5.7: systemctl list-unit-files|grep mysqld.service

image-20191224074109399

2.6. 修改字符集问题

测试mysql的使用：

img

出现了编码问题，原因：查看mysql编码（show variables like ‘%character%’），发现默认编码是latin1

img

修改编码，需要修改mysql配置文件：vi /etc/my.cnf

在mysqld下添加一行编码配置：character_set_server=utf8

img

保存并退出，重启mysql服务：systemctl restart mysqld

已生成的库表字符集如何变更？

修改数据库的字符集：alter database mydb character set 'utf8';

修改数据表的字符集：alter table mytbl convert to character set 'utf8';

img

2.7. 远程访问MySQL

接下来用宿主机navicat客户端访问MySQL服务，报如下错误：（确保防火墙已关闭）1584959279953

原因：默认情况下，mysql不允许远程连接。只允许localhost连接

执行：select host,user,authentication_string,select_priv,insert_priv from mysql.user;

img

% 表示所有远程通过 TCP方式的连接

IP地址 如 (192.168.1.2,127.0.0.1) 通过制定ip地址进行的TCP方式的连接

机器名：通过制定i网络中的机器名进行的TCP方式的连接

::1 IPv6的本地ip地址 等同于IPv4的 127.0.0.1

localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p 123xxx 方式的连接。

User: 表示用户名，同一用户通过不同方式链接的权限是不一样的。

password：密码，所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为MYSQLSHA1 ，不可逆 。

​ mysql 5.7 的密码保存到 authentication_string 字段中不再使用password 字段。

select_priv , insert_priv等，为该用户所拥有的权限。

解决方案：

  1. 创建新的用户

  2. 修改root用户访问权限

2.7.1. 创建新用户

创建新用户并指定密码：create user liuyan identified by 'liuyan';

img

修改root用户密码：set password =password('123456');

修改某个用户的密码：update mysql.user set authentication_string=password('123456') where user='liuyan';

修改用户名：update mysql.user set user='li4' where user='wang5';

删除用户：drop user li4 ;

flush privileges; #所有通过user表的修改，必须用该命令才能生效。

2.7.2. 授予root远程访问权限

授权命令：

grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;

该权限如果发现没有该用户，则会直接新建一个用户。

比如 

1
2
3
grant select,insert,delete,drop on atguigudb.* to root@localhost ; #给root用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。

grant all privileges on *.* to root@'%' identified by 'root';  #授予通过网络方式登录的的root用户，对所有库所有表的全部权限，密码设为root.

img

扩展：

查看当前用户权限：show grants;

查看当前用户的全局权限：select * from user ;

查看某用户的某个表的权限：select * from tables_priv;

收回权限命令：

revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ;

收回全库全表的所有权限：REVOKE ALL PRIVILEGES ON mysql.* FROM li4@localhost;

收回mysql库下的所有表的插删改查权限：REVOKE select,insert,update,delete ON mysql.* FROM li4@localhost;

必须用户重新登录后才能生效

2.7.3. 测试连接

连接测试：

img

2.8. 导入数据

把课前资料中的《1.数据准备.sql》导入到mysql数据库

2.9. sql_mode

需求：根据部门id查询每个部门的员工平均年龄。

sql_mode是个很容易被忽视的变量，默认值是空值（mysql5.5），在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

img

查看：show variables like ‘sql_mode’;

暂时修改：set sql_mode=’ONLY_FULL_GROUP_BY’;

sql_mode常用值如下：

ONLY_FULL_GROUP_BY：

​ 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：

​ 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：

​ 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。提高性能

NO_ZERO_IN_DATE：

​ 在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：

​ 设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

​ 在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：

​ 禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：

​ 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：

​ 将”||”视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：

​ 启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

ORACLE：

​ 设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER

3. MySQL逻辑架构

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

img

  1. 连接层

    最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  2. 服务层

    a) Management Serveices & Utilities： 系统管理和控制工具

    b) SQL Interface: SQL接口

    ​ 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

​ c) Parser: 解析器

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

​ d) Optimizer: 查询优化器。

​ SQL语句在查询之前会使用查询优化器对查询进行优化。

​ 用一个例子就可以理解： select uid,name from user where gender= 1;

​ 优化器来决定先投影还是先过滤。

​ 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤

​ 这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤

​ e) Cache和Buffer： 查询缓存。

​ 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

​ 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

  1. 引擎层

    存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

  2. 存储层

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

3.1. 查询流程说明

执行流程

首先，mysql的查询流程大致是：

​ mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

​ 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析树是否合法。

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

3.2. SQL执行计划

利用show profiles 可以查看sql的执行周期。需要先开启该功能：

  1. 开启查询缓存功能。

    查询缓存是否开启：show variables like ‘%query_cache%’;

    修改配置文件/etc/my.cnf，新增一行：query_cache_type=1

    重启mysql

  2. 查看参数表中profiling(性能分析工具)：show variables like '%profiling%';

    开启：set profiling=1;

执行：

1
2
3
select * from t_emp;

select * from t_dept;
  1. 显示最近几次查询：show profiles;

img

  1. 查看程序的具体执行步骤：show profile cpu,block io for query 查询id

    show profile的常用查询参数。

    ​ ①ALL：显示所有的开销信息。

    ​ ②BLOCK IO：显示块IO开销。

    ​ ③CONTEXT SWITCHES：上下文切换开销。

    ​ ④CPU：显示CPU开销信息。

    ​ ⑤IPC：显示发送和接收开销信息。

    ​ ⑥MEMORY：显示内存开销信息。

    ​ ⑦PAGE FAULTS：显示页面错误开销信息。

    ​ ⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。

    ​ ⑨SWAPS：显示交换次数开销信息。

img

checking permissions：检查权限

Opening tables：打开表
init ： 初始化
System lock ：系统锁
optimizing ： 优化sql
statistics ： 统计
preparing ：准备执行
executing ：执行sql
Sending data ：发送数据
Sorting result ：排序
end ：结束
query end ：查询 结束
closing tables ： 关闭表 ／去除TMP 表
freeing items ： 释放物品
cleaning up ：清理

3.3. SQL语法顺序

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。

需求：查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人，显示人数最多的第一名部门信息

下面是经常出现的查询顺序：

img

3.4. MySQL存储引擎

如何用命令查看：show engines;

img

也可以通过show variables like '%storage_engine%';查看默认的存储引擎。

3.4.1. 各引擎简介

  1. InnoDB存储引擎

    InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

  2. MyISAM存储引擎

    MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

  3. Archive引擎

    Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

    Archive表适合日志和数据采集类应用。

    根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

  4. Blackhole引擎

    Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

  5. CSV引擎

    CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。

    CSV引擎可以作为一种数据交换的机制，非常有用。

    CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

  6. Memory引擎

    如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。

  7. Federated引擎

    Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

3.4.2. MyISAM和InnoDB的区别(重点)

对比项 MyISAM InnoDB
外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整个表 不适合高并发的操作 行锁,操作时只锁某一行，不对其它行有影响 适合高并发的操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点 节省资源、消耗少、简单业务 并发写、事务、更大资源
默认安装 Y Y
用户表默认使用 N Y
自带系统表使用 Y N

3.4.3. 阿里巴巴、淘宝用哪个

graphic

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,

阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。

AliSql+AliRedis

4. 认识索引

影响性能下降SQL慢体现在：执行时间长或者等待时间长

影响sql性能的常见情况：

l 数据过多：分库分表(根据微服务划分库、按照地域或时间分表存储、按照数据的特定字段对分库数量求余)

l 关联了太多的表，太多join：SQL优化

l 没有充分利用到索引：索引建立

l 服务器调优及各个参数设置：调整my.cnf

4.1. SQL预热

常见七种通用的Join查询

img

练习：

#1. 所有有门派的人员以及部门信息 （ A、B两表共有）

#2. 列出所有用户，并显示其部门信息 （A的全集）

#3. 列出有部门的人员以及所有部门（B的全集）

#4. 所有不入门派的人员 （A的独有）

#5. 所有没有员工的部门（B的独有）

#6. 列出所有人员和部门的对照关系(AB全有)

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join

#7. 列出所有没部门的人员和没人的部门（A的独有+B的独有）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#1.    所有有门派的人员信息 （ A、B两表共有）

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptId=b.id;

#2.    列出所有用户，并显示其机构信息 （A的全集）

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id;

#3.    列出所有门派（B的全集）

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id;

#4.    所有不入门派的人员 （A的独有）

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId IS NULL;

#5.    所有没人入的门派（B的独有）

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId IS NULL;

#6.    列出所有人员和机构的对照关系(AB全有)

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id

UNION

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId IS NULL;

#7.    列出所有没入派的人员和没人入的门派（A的独有+B的独有）

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId IS NULL

UNION

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptId=b.id WHERE a.deptId IS NULL;

新增掌门人字段：

1
2
3
4
5
6
7
ALTER TABLE t_dept add CEO INT(11);

update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;

练习：

  1. 求各个门派对应的掌门人名称

  2. 求所有当上掌门人的平均年龄

  3. 求所有人物对应的掌门名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 求各个门派对应的掌门人名称
select * from t_dept a left join t_emp b on a.CEO=b.id;

# 求所有当上掌门人的平均年龄
select avg(a.age) from t_emp a inner join t_dept b on a.id=b.CEO;

# 求所有人物对应的掌门名称
# No1
select c.name,c.id,v.name from t_emp c 
	LEFT JOIN (select a.deptName,a.id,b.name from t_dept a left join t_emp b on a.ceo=b.id) v on c.deptId=v.id
# No2
select c.name,c.id,v.name from (select a.deptName,a.id,b.name from t_dept a left join t_emp b on a.ceo=b.id) v
	LEFT JOIN t_emp c on c.deptId=v.id
# No3
select a.name,a.id,c.name from t_emp a 
	LEFT JOIN t_dept b on a.deptId=b.id
	LEFT JOIN t_emp c on b.ceo=c.id
# No4
select a.name,a.id,(select c.name from t_emp c where c.id=b.ceo) from t_emp a 
	LEFT JOIN t_dept b on a.deptId=b.id

作业：

课前资料中的《2.作业8个sql.txt》

4.2. 索引简介

4.2.1. 什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。

索引的目的在于提高查询效率，可以类比字典，

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩下的sql。

如果没有索引，那么你可能需要a—-z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？

是不是觉得如果没有索引，这个事情根本无法完成？

你可以简单理解为“排好序的快速查找数据结构”。

​ 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

1562152362590

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

结论：

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

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

4.2.2. 索引的优劣势

优势：

  1. 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。

  2. 通过索引列对数据进行排序或分组，降低数据排序的成本，降低了CPU的消耗。

劣势：

  1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

  2. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

AVLTree

​ 高度平衡树。增加和删除可能需要通过一次或多次树旋转来重新平衡这个树.

4.3. MySQL的索引结构

4.3.1. BTree索引

1562153499004

【初始化介绍】
一颗b树，浅蓝色的块我们称之为一个磁盘块(innodb默认16kb一个磁盘块)，可以看到每个磁盘块包含几个数据项（深蓝色所示）、指向关键字具体信息的指针（红色）和指向其他磁盘块的指针（黄色所示）
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于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，显然成本非常非常高。

4.3.2. B+Tree索引

1562160873117

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读写次数也就降低了。
  2. B+树的查询效率更加稳定
    由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

4.3.3. 时间复杂度（O）

算法复杂度分为时间复杂度和空间复杂度。其作用： 时间复杂度是指执行算法所需要的计算工作量；而空间复杂度是指执行这个算法所需要的内存空间。（算法的复杂性体现在运行该算法时的计算机所需资源的多少上，计算机资源最重要的是时间和空间（即寄存器）资源，因此复杂度分为时间和空间复杂度）。

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

案例：计算1~99的总和。

​ 方法1： int sum = 49*(1+99)+50；

​ 方法2:

​ int sum=0;

​ for(int i=1;i<=99;i++){

​ sum+=i;

​ }

1562162692682

1562162701261

4.3.4. 聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，非聚簇索引又称辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。

img

什么是聚簇索引？

聚簇索引不是一种索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行。当表有聚餐索引时，它的行数据实际上存放在索引的叶子页中，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点：① 可以把相关数据保存在一起。② 数据访问更快，聚簇索引将索引和数据保存在同一个 B-Tree 中，因此获取数据比非聚簇索引要更快。③ 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：① 聚簇索引最大限度提高了 IO 密集型应用的性能，如果数据全部在内存中将会失去优势。② 更新聚簇索引列的代价很高，因为会强制每个被更新的行移动到新位置。③ 基于聚簇索引的表插入新行或主键被更新导致行移动时，可能导致页分裂，表会占用更多磁盘空间。④ 当行稀疏或由于页分裂导致数据存储不连续时，全表扫描可能很慢。

InnoDB使用聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引的好处：

  • 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

  • 聚簇索引适合用在排序的场合，非聚簇索引不适合

  • 取出一定范围数据的时候，使用用聚簇索引

  • 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据

聚簇索引的限制：

  • 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
  • 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。
    • 一般情况下就是该表的主键。
    • 如果没有primary key,会以(not null unique key)非空的唯一索引保存数据
    • 内部自己生成一个字段保存数据
  • 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序非空的字段，而不建议用无序的id，比如uuid这种。

4.4. MySQL索引分类

  1. 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
  2. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
  3. 唯一索引：索引列的值必须唯一，但允许有空值
  4. 复合索引：即一个索引包含多个列
1
2
3
4
5
6
7
8
9
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 [UNIQUE ] INDEX [indexName] ON table_name(column)) 

1
2
3
4
5
6
7
ALTER TABLE customer ADD PRIMARY KEY customer(customer_no);  # 主键索引

CREATE INDEX idx_customer_name ON customer(customer_name);  # 单值索引

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); # 唯一索引

CREATE INDEX idx_no_name ON customer(customer_no,customer_name); # 复合索引

删除索引：DROP INDEX [indexName] ON mytable; 

1
2
3
ALTER TABLE customer drop PRIMARY KEY;  # 删除主键索引

DROP INDEX idx_customer_name  on customer; # 删除单值、唯一、复合索引

查看索引：SHOW INDEX FROM table_name\G;

修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引

使用ALTER命令：

1
2
3
4
5
6
7
8
9
10
11
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 ，用于全文索引。

4.5. 索引的使用场景

哪些情况需要创建索引：

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题， 组合索引性价比更高
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段

哪些情况不要创建索引：

  • 表记录太少

  • 经常增删改的表或者字段。

    Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

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

  • 过滤性不好的不适合建索引

5. 索引优化

四种：1.主键 2.单值 3.唯一 4.复合

5.1. 性能分析（explain）

很多现象需要在5.5才能演示成功。

5.1.1. explain是什么?

模拟优化器查看执行计划

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

5.1.2. explain能干什么？

  • 表的读取顺序

  • 那些索引可以使用

  • 数据读取操作的操作类型

  • 那些索引被实际使用

  • 表之间的引用

  • 每张表有多少行被物理查询

5.1.3. explain怎么玩？

explain + SQL语句

官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information

数据准备：

1
2
3
4
5
6
7
8
9
10
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));

# 以下新增sql多执行几次，以便演示
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 select * from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;

1562200394763

5.1.4. 各字段解释

5.1.4.1. id(重要)

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

三种情况：

  1. id相同，执行顺序由上至下。例如上图

  2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

    1
    2
    3
    4
    5
    6
    7
    8
    9
    EXPLAIN SELECT * 
    FROM t1 WHERE t1.content =(
    	SELECT t2.content
    	FROM t2 WHERE t2.content=(
    		SELECT t3.content
    		FROM t3
    		WHERE t3.content=""
    	)
    );

    1562201486076

  3. id既有相同又有不同

    image-20191205085646036

关注点：每个id号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

5.1.4.2. select_type(不会用于优化)

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

SIMPLE 简单的 SELECT(没有 使用UNION或者 子查询（PS：单表查询）)
PRIMARY 最外层的Select 作为primary 查询。(PS:含有子查询的情况,但是并不复杂)案例1
DERIVED 在from 查询语句中的（派生，嵌套很多）子查询.(PS:递归操作这些子查询)
SUBQUERY 在SELECT或WHERE列表中包含了子查询。案例2
DEPENDENT SUBQUERY 第一个查询是子查询，依赖于外部查询（相关查询）。案例3
MATERIALIZED 在非相关子查询中 并且需要进行物化时会出现MATERIALIZED关键词。案例4
UNCACHEABLE SUBQUERY 子查询结果(系统变量)不能被缓存， 而且必须重写（分析）外部查询的每一行。案例5
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION。案例6
若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT 结果集是通过union 而来的。案例6

案例1：explain select * from (select t2.* from t2) s2

1562208316690

案例2：explain select * from t1 where t1.id=(select t2.id from t2 where t2.id=1)

1562209032431

案例3：explain select t1.*,(select t2.content from t2 where t2.id=t1.id) from t1 where t1.id=1;

1562211356010

案例4：explain select * from t1 where t1.content in (select t2.content from t2 where t2.content in (select t3.id from t3 where t3.content=’’)) #需要5.7版本演示

1562215537278

案例5：EXPLAIN SELECT * from t1 where t1.id=(select t2.id from t2 where t2.id=@@sort_buffer_size);

1562215950297

案例6：explain select * from t1 UNION select * from t2;

1562216269373

5.1.4.3. table

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

5.1.4.4. partitions

代表分区表中的命中情况，非分区表，该项为null

5.1.4.5. type(重要)

1562218532562

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

null>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。

null: MySQL不访问任何表或索引，直接返回结果

system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。（select * from t1 where t1.id=1）

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。（select * from t1,t2 where t1.id=t2.id）

ref: 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。（select * from t1 where t1.id<10）

index：出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。（explain select id from t1）

all：Full Table Scan，将遍历全表以找到匹配的行。（explain select * from t1）

index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。

ref_or_null：对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

index_subquery：利用索引来关联子查询，不再全表扫描。

unique_subquery：该联接类型类似于index_subquery。 子查询中的唯一索引。

创建多个索引：

1562219873386

index_merge:

1
EXPLAIN SELECT * FROM t3 WHERE t3.content IS NULL OR t3.id=10;

1562219784299

ref_or_null:

1
EXPLAIN SELECT * FROM t3 WHERE t3.content IS NULL OR t3.content='aaaa';

1562219809669

index_subquery:

1
EXPLAIN SELECT * FROM t2 WHERE t2.content IN (SELECT t3.content FROM t3);

1562219837483

unique_subquery:

1
EXPLAIN SELECT * FROM t2 WHERE t2.id IN (SELECT t3.id FROM t3);

1562219964718

5.1.4.6. possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

5.1.4.7. key(优化重要指标)

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

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

1562220435426

5.1.4.8. key_len(重要)

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

key_len字段能够帮你检查是否充分的利用上了索引。

1562222065554

如何计算
1 、先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
2 、如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘 3,GBK要乘2，
3 、varchar这种动态字符串要加2个字节
4、 允许为空的字段要加1个字节

5.1.4.9. ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

5.1.4.10. rows

rows列显示MySQL认为它执行查询时必须检查的行数。越少越好

5.1.4.11. filtered

explian extended select … 时会显示此字段

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

5.1.4.12. extra(优化重要指标)

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

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”

1562222556488

这类SQL语句性能极差，需要进行优化。

在一个非索引列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序(只查询索引列的值)。

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

1562222953260

group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

USING index：利用索引进行了排序或分组。表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！（EXPLAIN select * from t_emp where age=30 ORDER BY name）
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

Using where：表明使用了where过滤

using join buffer：使用了连接缓存，非主键关联

1562223358888

impossible where：where子句的值总是false，不能用来获取任何元组。（EXPLAIN select * from t_emp where false;）

select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在innodb中：

1562223678535

在Myisam中：

1562223753932

5.1.5. 小结（重要）

表的读取顺序：id

​ id趟数越少越好，id相同执行顺序由上至下，id不同 大的先执行

查询方式：select_type

那些索引可以使用：possible_keys

数据读取操作的操作类型：type

​ range index all

那些索引被实际使用：key

​ 创建的索引是否能够被实际应用

使用索引的长度：key_len

​ 命中的索引匹配的长度

​ 计算索引长度：

​ utf-8

​ varchar(len) =使用len*3+2 (如果字段可以为null，再+1)

​ char(len) =使用len*3 (如果字段可以为null，再+1)

​ int(len) = 4 (如果字段可以为null，再+1)

表之间的引用：table

每张表有多少行被物理查询：rows

​ 行数越少越好

额外优化信息：extra

​ using filesort和 using temporary 和 using join buffer 需要考虑优化

​ 其他情况性能都可以无需优化

5.2. 数据准备

在做优化之前，要准备大量数据。接下来创建两张表，并往员工表里插入50W数据，部门表中插入1W条数据。

建表sql：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

怎么快速插入50w条数据呢？ 存储过程

怎么保证插入的数据不重复？函数

以部门表分析：

​ id：自增长

​ deptName：随机字符串，允许重复

​ address：随机字符串，允许重复

​ CEO：1-50w之间的任意数字

以员工表分析：

​ id：自增长

​ empno：可以使用随机数字，或者从1开始的自增数字，不允许重复

​ name：随机生成，允许姓名重复

​ age：区间随机数

​ deptId：1-1w之间随机数

总结：需要产生随机字符串和区间随机数的函数。

5.2.1. 创建函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO  
		SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
		SET i = i + 1;
	END WHILE;
	RETURN return_str;
END $$

#用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;  
END$$

#假如要删除
#drop function rand_string;
#drop function rand_num;

5.2.2. 存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 插入员工存储过程
DELIMITER $$
CREATE PROCEDURE  insert_emp(START INT, max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	#set autocommit =0 把autocommit设置成0  
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

 
#往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

5.2.3. 调用存储过程

1
2
3
4
5
6
7
#执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000); 

#执行存储过程，往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000);

mysql5.5可能会报如下错误：

1
ERROR 1436 (HY000): Thread stack overrun:  8976 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.

解决方案：修改/etc/my.cnf，把thread_stack从128K改成512K

1562296803226

5.2.4. 批量删除表索引

批量删除某个表上的所有索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DELIMITER $$
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE ct INT DEFAULT 0;
	DECLARE _index VARCHAR(200) DEFAULT '';
	DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY'  ;
	DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
	OPEN _cur;
		FETCH _cur INTO _index;
		WHILE  _index<>'' DO 
			SET @str = CONCAT("drop index ",_index," on ",tablename ); 
			PREPARE sql_str FROM @str ;
			EXECUTE sql_str;
			DEALLOCATE PREPARE sql_str;
			SET _index=''; 
			FETCH _cur INTO _index; 
		END WHILE;
	CLOSE _cur;
END$$

执行批量删除：

1
CALL proc_drop_index("dbname","tablename"); # 库名称和表名称

5.3. 单表优化

5.3.1. 索引优化原则

  1. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  2. like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
  3. mysql 在使用**不等于(!=或者<>)**的时候无法使用索引会导致全表扫描
  4. is not null 也无法使用索引，但是is null是可以使用索引的
  5. 字符串不加单引号索引失效

  1. 以下两个sql，那个写法更好：

    案例：

    ​ 1.1 查找姓名以”abc”开头的员工信息

    ​ 1.2 查找姓名含有”abc”的员工信息

    ​ 1.3 查找年龄不等于25的员工

    ​ 1.4 查找姓名不为空的员工信息

    ​ 1.5 查找姓名等于”123”的员工信息

1
2
3
4
5
6
7
# 创建索引
create index idx_emp_age on emp(age);
create index idx_emp_name on emp(name);

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

sql访问类型range > ALL；使用索引idx_emp_name > NULL； 使用索引长度63 > NULL; 扫描行数25 < 498951

1562232210955

第一个sql用时：0.00s

1562232088509

第二个sql用时：0.37s

1562232127804

由此可见第一个sql优于第二个sql：第一个走了索引，第二个走了全表扫描。

  1. 把第一个sql的like查询条件改成‘%abc%’，会怎样呢？

1562232614822

可以发现改成’%abc%’之后，第一个sql失去了索引优势，走了全表扫描。

  1. 再来看这两个sql：不等于(!=或者<>)
1
2
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age!=30;

1562232994746

  1. is not null和is null

1562233143967

  1. 字符串加引号

1562233243786

5.3.2. 组合索引原则

  1. 全值匹配我最爱
  2. 符合最左原则：不跳过索引中的列。
  3. 如果where条件中是OR关系，加索引不起作用
  4. 存储引擎不能使用索引中范围条件右边的列

首先删除之前创建的索引：

1
CALL proc_drop_index("mydb","emp");
  1. 全值匹配我最爱
1
2
3
SELECT SQL_NO_CACHE * FROM emp WHERE age=30 and deptId=1 and name='abc';
create index idx_age_deptId_name on emp(age, deptId, name);
SELECT SQL_NO_CACHE * FROM emp WHERE age=30 and deptId=1 and name='abc';

1562234172760

  1. 最左匹配原则

1562234348724

  1. OR关联

1562234532109

  1. 范围条件右边的列

1562236752738

5.3.3. 小结

一般性建议：

  1. 对于单键索引，尽量选择针对当前query过滤性更好的索引
  2. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  3. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
  5. 书写sql语句时，尽量避免造成索引失效的情况

假设index(a,b,c)
1562230949642

5.4. 关联查询优化

接下来再次创建两张表，并分别导入20条数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE IF NOT EXISTS `class` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`card` INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
	`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`card` INT(10) UNSIGNED 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)));

5.4.1. 关联案例

explain分析一下两个sql：

1
2
3
4
5
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

1562289354599

给book.card创建索引：

1
ALTER TABLE `book` ADD INDEX idx_card ( `card`);

然后explain分析：

1562290556023

删除旧索引，添加新索引：

1
2
3
# 删除旧索引 + 新建 + 第3次explain
drop index idx_card on book;
ALTER TABLE class ADD INDEX index_class_card (card);

再次explain分析：

1562291150030

同时给两张表的card字段添加索引：（class(card)索引已有：index_class_card，只需给book（card）添加索引）

1
ALTER TABLE `book` ADD INDEX idx_card ( `card`);

最后explain分析：

1562291418458

5.4.2. 优化建议

  1. 保证被驱动表的join字段已经创建了索引
  2. left/right join 时，选择小表作为驱动表，大表作为被驱动表。(5.6已经优化掉了，5.5需要手动编写)
  3. inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
  4. 子查询尽量不要放在被驱动表，有可能使用不到索引。
  5. 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

5.4.3. 四种实现的比较

5.5. 子查询优化

尽量不要使用not in 或者 not exists

尽量不要使用子查询

5.6. 排序优化

以下三种情况不走索引：

  1. 无过滤，不索引
  2. 顺序错，不索引
  3. 方向反，不索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create index idx_age_deptid_name on emp (age,deptid,name)

# 以下  是否能使用到索引，能否去掉using filesort

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 ;

5.6.1. 优化演示

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

执行案例前先清除emp上的索引，只留主键

1
# 查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME;

1562314980042

1562315067430

结论：很显然，执行时间为0.477s，type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

优化思路： 尽量让where的过滤条件和排序使用上索引。

现在过滤条件使用了两个字段（age，empno）排序使用了name。

我们建一个三个字段的组合索引可否？

1
CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);

再次explain测试：

1562315510720

我们发现using filesort 依然存在，所以name 并没有用到索引。

原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。

所以我们建一个3值索引是没有意义的
那么我们先删掉这个索引：DROP INDEX idx_age_empno_name ON emp

为了去掉filesort我们可以把索引建成

1
CREATE INDEX idx_age_name ON emp(age,NAME);

也就是说empno 和name这个两个字段只能二选其一。
这样我们优化掉了 using filesort。

1562315894113

执行一下sql：

1562315987115

速度果然提高了4倍。

假如：选择创建age和empno会速度会怎样呢，自己试试有惊喜！

1562316267214

结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。何故？

原因：是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论： 当范围条件和group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

5.6.2. 了解filesort算法

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

5.6.2.1. 双路排序

​ MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

也就是：从磁盘取排序字段，再buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

5.6.2.2. 单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题：由于单路是后出的，总体而言好过双路。

但是用单路有问题：在sort_buffer中，比双路排序要多占用很多空间，因为单路排序是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

5.6.2.3. 优化策略

  1. Order by时select * 是一个大忌，只Query需要的字段， 这点非常重要。在这里的影响是
  • 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
  • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

  1. 尝试提高 sort_buffer_size

    不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整

  2. 尝试提高 max_length_for_sort_data

    提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。1024-8192之间调整

5.7. 分组优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

只要对分组的列创建索引

5.8. 覆盖索引

最后使用索引的手段：覆盖索引

什么是覆盖索引？
简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

1562317562345

1
explain select * from emp where name like '%abc';

1562317631831

1562317642152

使用覆盖索引后

1562317665303

5.9. 实现并优化8个SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
#删除两个表的所有索引
#1、列出自己的掌门比自己年龄小的人员
EXPLAIN SELECT * FROM t_emp c 
	INNER JOIN (SELECT a.id, a.deptName, b.age FROM t_dept a INNER JOIN t_emp b ON a.CEO=b.id) v ON c.deptId=v.id
WHERE c.age > v.age;

EXPLAIN SELECT SQL_NO_CACHE * FROM t_dept a 
	INNER JOIN t_emp b ON a.CEO=b.id 
	INNER JOIN t_emp c ON c.deptId=a.id
WHERE c.age > b.age;

create index idx_deptId on t_emp(deptId);

#2、列出所有年龄低于自己门派平均年龄的人员
EXPLAIN select SQL_NO_CACHE * from t_emp c 
	LEFT JOIN (select b.deptId,AVG(b.age) avgage  from emp b GROUP BY b.deptId
	) v on c.deptId=v.deptId
where c.age < v.avgage;

EXPLAIN select * from (select b.deptId, AVG(b.age) avgage from emp b GROUP BY b.deptId) v 
	LEFT JOIN t_emp c on c.deptId=v.deptId
where c.age < v.avgage;

CREATE INDEX idx_deptId on emp(deptId);


#3、列出至少有2个年龄大于40岁的成员的门派

explain select SQL_NO_CACHE a.deptId, b.deptName, count(*) cou 
from t_emp a 
	INNER JOIN t_dept b on a.deptId=b.id 
where a.age>40 
GROUP BY a.deptId 
HAVING cou >= 2;

explain select SQL_NO_CACHE b.id, b.deptName, count(*) cou 
from t_dept b 
	STRAIGHT_JOIN t_emp a on a.deptId=b.id 
where a.age>40 
GROUP BY b.id
HAVING cou >= 2;

create INDEX idx_dept on t_emp(deptId, age);

#4、至少有2位非掌门人成员的门派

EXPLAIN select SQL_NO_CACHE a.deptId,b.deptName,count(*) cou
from t_emp a 
	INNER JOIN t_dept b on a.deptId=b.id 
where a.id!=b.CEO 
GROUP BY a.deptId
HAVING cou >= 2;

explain select a.deptId,c.deptName,count(*) cou from t_emp a LEFT JOIN t_dept b on a.id=b.ceo INNER JOIN t_dept c on a.deptId=c.id 
where b.ceo is null
GROUP BY a.deptId
HAVING cou>=2

CREATE INDEX idx_deptId on t_emp(deptId);
CREATE INDEX idx_ceo on t_dept(ceo);

#5、列出全部人员，并增加一列备注“是否为掌门”，如果是掌门人显示是，不是掌门人显示否
select a.id, a.`name`, CASE WHEN b.CEO IS NULL THEN '是' ELSE '否' END '是否掌门人' 
from t_emp a LEFT JOIN t_dept b on a.id=b.CEO;

#6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50显示“老鸟”，否则显示“菜鸟”
select a.id,a.deptName,AVG(b.age),IF(AVG(b.age)>50, '老鸟', '菜鸟') '老鸟or菜鸟' 
from t_dept a INNER JOIN t_emp b on a.id=b.deptId GROUP BY a.id;


#7、显示每个门派年龄最大的人
EXPLAIN select SQL_NO_CACHE b.deptId, b.age from (select a.deptId,MAX(a.age) maxage from t_emp a GROUP BY a.deptId) v
	INNER JOIN t_emp b on v.deptId=b.deptId and v.maxage=b.age

CREATE INDEX idx_deptId on t_emp(deptId);
CREATE INDEX idx_age on t_emp(age);

#8、显示每个门派年龄第二大的人·
SET @last_deptid=0;
SELECT a.id,a.deptid,a.name,a.age,a.rk
 FROM(    
    SELECT t.*,
     IF(@last_deptid=deptid,@rank:=@rank+1,@rank:=1) AS rk,
     @last_deptid:=deptid AS last_deptid
    FROM t_emp t
    ORDER BY deptid,age DESC
 )a WHERE a.rk=2;

UPDATE t_emp SET age=100 WHERE id = 2

SET @rank=0;
SET @last_deptid=0;
SET @last_age=0;
SELECT t.*,
	IF(@last_deptid=deptid, IF(@last_age = age, @rank, @rank:=@rank+1),@rank:=1) AS rk, 
	@last_deptid:=deptid AS last_deptid,
	@last_age :=age AS last_age
FROM t_emp t
ORDER BY deptid,age DESC

CALL proc_drop_index('mydb', 't_emp');
CALL proc_drop_index('mydb', 't_dept');
CALL proc_drop_index('mydb', 'emp');
CALL proc_drop_index('mydb', 'dept');
#java #mysql
MySql高级
http://www.kju147.com/2021/08/05/MySql高级/
作者
个人学习
发布于
2021年8月5日
许可协议