MySQL从基础到高级五
1. MySQL 架构介绍
MySQL 简介
概述：
MySQL 是一种关系型数据库管理系统，由瑞典 MySQL AB 公司开发，后来被 sun 公司收购，再后来 sun 公司被 oracle 公司收购，所以现在归 oracle 公司旗下。
MySQL 是一种关系型数据库管理系统，将数据保存在不同的表中，而不是将所有数据保存在一个仓库内，这样增加了速度并提供了灵活性。
MySQL 提供开源免费的社区版本，也提供高级收费版本。
MySQL 支持大型的数据库，可以处理上千万条记录的大型数据库。
MySQL 支持标准的 SQL 作为数据库的操作语言。
MySQL 可以允许多系统共用，并且支持多种语言，这些编程语言包括：C、C++、Java、Python、Perl、PHP、EIffel、Ruby 和 Tcl 等。
MySQL 对 PHP 有很好的支持，PHP 是目前最流行的 Web 开发语言。
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 为系统表文件最大可支持 4GB，64 为系统表文件最大可支持 8TB。
MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。
高级 MySQL
完整的 MySQL 优化需要很深的功底，在大公司中甚至会有专职的 DBA 做数据库优化。
MySQL 内核
SQL 优化攻城狮
MySQL 服务器优化
各种参数常量定制
查询语句优化
主从复制
软硬件升级
容灾备份
SQL 编程
MySQL 的 Linux 版本安装
点击下载
注意：
①开发上使用要下载 GA 版本(general availability)
②下载两个安装包：mysql—server，mysql—client
③把两个安装包上传到 Linux 系统 opt 目录下
检查当前系统是否安装过 MySQL
# 检查命令
——rpm -qa|grep -i mysql
有安装：无返回信息
无安装：打印软件版本信息
安装 MySQL 软件
# 进入 opt 目录
——cd /opt
# 安装 mysql—server(-ivh：打印进度条和安装日志)
——rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm
# 安装 mysql-client
——rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm
查询 MySQL 安装时创建的 mysql 用户和 mysql 组
# 查看 mysql 用户组
——cat /etc/passwd|grep mysql
——cat /etc/group|grep mysql
# 检查 MySQL 是否安装成功，打印版本信息
——mysqladmin --version
MySQL 服务的启动和停止
# 启动服务
——service mysql start
# 停止服务
——service mysql stop
# 设置 MySQL 随机启动
——chkconfig mysql on
# 检查是否成功设置随机启动
——chkconfig --list|grep mysql
# 查看随机启动服务级别
——cat /etc/inittab
# 查看随机启动服务级别(界面式)
——ntsysv
修改 MySQL 登录密码
# 修改登录密码
——/usr/bin/mysqladmin -u root password 123456
MySQL 的安装位置
# Linux 环境下查看 MySQL 安装目录
——ps -ef|grep mysql
| 路径 | 解释 | 备注 |
|---|---|---|
| /var/lib/mysql/ | MySQL 数据库文件的存放路径 | /var/lib/mysql/目录下存放各种库文件 |
| /usr/share/mysql/ | MySQL 配置文件目录 | mysql.server 命令及其配置文件 |
| /etc/mysql/ | MySQL 配置文件目录 | mysql.server 命令及其配置文件 |
| /usr/bin/ | MySQL 相关命令目录 | mysqladmin mysqldump等命令集合文件 |
| /etc/init.d/mysql/ | MySQL 启动和停止服务相关脚本 |
修改配置文件位置
①如果 MySQL 默认把配置文件存放到 /usr/share/mysql/目录下，就复制一份到 /etc/mysql/ 目录下，保留原本的配置文件做参照
——cp /usr/share/mysql/配置文件名 /etc/mysql/my_cp.cnf
②如果 MySQL 默认把配置文件存放到 /etc/mysql/ 目录下，也要复制一份副本方便以后修改
——cp /etc/mysql/配置文件名 /etc/mysql/my_cp.cnf
修改字符集和数据存储路径
# 1、查看数据库字符集格式
——show variables like 'character%';
——show variables like '%char%';
# 2、修改数据库字符集格式
①进入 MySQL 配置文件
——vim /etc/mysql/my_cp.cnf
①找到 [client] 字眼，在该段末添加如下命令
——default-character-set=utf-8
②找到 [mysqld] 在段落的 port 命令下添加如下命令
——character_set_server=utf-8
——character_set_client=utf-8
——collation-server=utf-8_general_ci
③找到 [mysql] 段末添加如下命令
——default-character-set=utf-8
# 3、修改完成配置文件后，需要重启 mysql 服务
——service mysql restart
# 4、修改 mysql 数据存储路径
①在 /var/lib/mysql/ 目录下创建一个 data 文件夹
——mkdir /var/lib/mysql/data
②进入 my_cp.cnf 配置文件
——vim /etc/mysql/my_cp.cnf
③找到 [mysqld] 段落把 datadir 变量改为如下值
——datadir=/var/lib/mysql/data
MySQL配置文件
二进制日志log-bin
数据库对数据进行的所有 insert、update 和 delete 语句的日志信息均在里面，主要用于后面的主从复制
错误日志log-error
默认是关闭的，记录着数据库运行期间产生严重的警告和错误信息，每次启动和关闭的详细信息等。
查询日志
默认是关闭的，记录着数据库对数据进行的所有 select 语句，如果开启会降低 mysql 的整体性能，因为记录日志也是需要消耗系统资源的，而且 select 语句是执行最频繁的。
数据文件
# 1、两系统：
①window：D:\mysql\MySQLServer5.5\data 目录下可以看到很多数据库文件
②linux：默认路径：/var/lib/mysql/，修改后路径：/var/lib/mysql/data/，在此目录下也可以看到很多数据库文件
# 数据库文件：
①frm 文件：存放表结构
②myd 文件：存放表数据
③myi 文件：存储表索引
MySQL 逻辑架构简介
①mysql 被设计成为分层软件系统，总共有四个层面：连接层、服务层、引擎层、存储层
②它的架构可以在多种不同应用场景中使用并发挥良好的作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和
其它的系统任务以及数据的的存储提取相分离，这种架构可以根据业务需求和实际需要选择合适的存储引擎。
连接层
最上面一层是客户端和连接服务，包括本地 sock 通信和大多数基于客户端/服务端工具实现类似于 tcp/ip 通信。主要完成一些类似于连接处理、授权认证以及相关安全方案。在该层面上引入线程池的概念，为通过安全认证接入的客户端提供线程服务。同样在该层上可以实现可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要是完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化及部分内置函数的执行。所有跨存储引擎的功能都在这一层实现，如存储过程、函数等。在该层，服务器会解析查询并会创建相应的解析树，并对其完成相应的优化如确定查询表的顺序，是否使用索引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存，如果内部缓存足够大，这样在解决大量读操作的环境中能够很好的提升系统性能。
引擎层
存储引擎层，存储引擎真正负责了 MySQL 中数据的存储和提取，服务器通过 api 和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己实际的需要进行选取对应的存储引擎。
存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
MySQL 存储引擎
查看命令
# 使用命令查看存储引擎
——show engines;
# 查看当前 MySQL 默认使用的存储池引擎
——show variable like '%storage_engines%';
MyISAM 和 InnoDB
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住张表，不合适高并发的操作 | 行锁，操作时只会锁住当前行记录，不对其它行有影响，合适高并发场景 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存数据，对内存要求比较高，而且内存大小对性能有决定性影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | 是 | 是 |
索引优化分析
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容——百度百科
执行时间长、等待时间长，性能下降，SQL执行慢
①查询语句写得烂
②索引失效：
单值：create index idx_表名_字段名 on 表名(字段名);
复合：create index idx_表名_字段名1_字段名2 on 表名(字段名1, 字段名2);
③关联查询太多join（设计缺陷或者不得已的需求）
④服务器调优及各个参数设置（缓存大小、线程数等）
常见通用的 join 查询
SQL 执行顺序
## 手写
SELECT DISTINCT
< select_list >
FROM
< left_table < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
## 机读
FROM
< left_table < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
SELECT
DISTINCT < select_list >
ORDER BY
< order_by_condition >
LIMIT < limit_number >
③ 总结：
如下鱼尾图所示
Join 图
建表 SQL
## 创建一张用户表
CREATE TABLE `user_tab` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`gender` int(2) DEFAULT 0,
`email` varchar(255) DEFAULT NULL,
`dept_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FKmmw63q4abgfsybnahm1q9we80` (`dept_id`),
CONSTRAINT `FKmmw63q4abgfsybnahm1q9we80` FOREIGN KEY (`dept_id`) REFERENCES `department_tab` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
## 创建一张部门表
CREATE TABLE `department_tab` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`d_name` varchar(255) DEFAULT NULL,
`d_code` varchar(255) DEFAULT NULL,
`d_person_num` int(10) DEFAULT 0,
`d_manage` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
7 种 Join 联合查询
## 建立一个左连接（如：图1）
SELECT u.*, d.*
FROM user_tab AS u
LEFT JOIN department_tab AS d
ON u.dept_id = d.id
## 建立一个有连接（如：图2）
SELECT u.*, d.*
FROM user_tab AS u
RIGHT JOIN department_tab AS d
ON u.`dept_id` = d.`id`
## 建立一个内连接（如：图3）
SELECT u.*, d.*
FROM user_tab AS u
INNER JOIN department_tab AS d
ON u.`dept_id` = d.`id`
## 建立一个对用户信息独有的连接查询（如：图4）
SELECT u.*, d.*
FROM user_tab AS u
LEFT JOIN department_tab AS d
ON u.`dept_id` = d.`id`
WHERE d.`id` IS NULL
## 建立一个对部门信息独有的连接查询（如：图5）
SELECT u.*, d.*
FROM user_tab AS u
RIGHT JOIN department_tab AS d
ON u.`dept_id` = d.`id`
WHERE u.`dept_id` IS NULL
## 建立一个对用户信息和部门信息的并集连接查询（如：图6）
SELECT u.*, d.*
FROM user_tab AS u
FULL OUTER JOIN department_tab AS d
ON u.`dept_id` = d.`id`
## mysql 不支持 full join，可以使用union联合查询代替（如：图6）
SELECT u.*, d.*
FROM user_tab AS u
LEFT JOIN department_tab AS d
ON u.dept_id = d.id
UNION
SELECT u.*, d.*
FROM user_tab AS u
RIGHT JOIN department_tab AS d
ON u.`dept_id` = d.`id`
WHERE u.`dept_id` IS NULL
## 建立一个对用户信心和部门信息去除重叠部分的连接查询（如：图7）
SELECT u.*, d.*
FROM user_tab AS u
FULL OUTER JOIN department_tab AS d
ON u.`dept_id` = d.`id`
WHERE u.`dept_id` IS NULL
OR d.`id` IS NULL
## mysql 不支持 full join，可以使用union联合查询代替（如：图7）
SELECT u.*, d.*
FROM user_tab AS u
LEFT JOIN department_tab AS d
ON u.`dept_id` = d.`id`
WHERE d.`id` IS NULL
UNION
SELECT u.*, d.*
FROM user_tab AS u
RIGHT JOIN department_tab AS d
ON u.`dept_id` = d.`id`
WHERE u.`dept_id` IS NULL
索引简介
是什么
1、简单理解：预先排好序，可以快速查询的数据结构
2、官方翻译：数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构称之为索引。
1、为了加快 Col2 的查找，可以维护一棵右边所示的平衡二叉树，每一个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找算法在一定的复杂度内获取到相应的数据，从而加快检索出符合条件的记录。
2、一般来说索引本身也是耗空间的，不可能全部保存到内存当中，因此索引往往以索引文件的形式存储在磁盘上
3、我们平常说的索引，如果没有特别说明，都是指 B 树(多路搜索树，不一定是二叉树)结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、唯一索引默认都是使用 B+ 树索引，统称索引。当然除了使用 B+ 树索引，还可以使用哈希索引(hash index)等。
优势
1、类似大学图书馆建立目录索引，提高数据检索效率，降低数据库的 I/O 成本。
2、通过索引列对数据进行排序，降低数据排序成本，降低了 CPU 的消耗。
劣势
1、实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引也是要占用空间的
2、虽然索引提高了数据查询的效率，同时却降低了更新表的速度，如对表进行 insert、update、delete 操作。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
3、索引只是提高效率的一个因素，如果 MySQL 有大数据量的表，就需要花费大量时间去研究建立最优秀的索引，或者优化查询索引。
索引分类
1、单值索引：即一个索引只包含单个字段列，一张表可以有多个单值索引(最好不超过5个索引)
2、唯一索引：索引列的值必须唯一，但允许有空值
3、复合索引：即一个索引包含多个字段列
4、基本语法：
# 创建
CREATE [UNIQUE] INDEX indexName ON tableName(columnname(length));
ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
## 删除
DROP INDEX [indexName] ON tableName;
## 查看
SHOW INDEX FROM tableName\G
## 使用 ALTER 命令添加索引
ALTER TABLE tableName ADD PRIMARY KEY(column_list); # 添加主键索引，索引值唯一，且不能为空
ALTER TABLE tableName ADD UNIQUE indexName(column_list); # 添加一个唯一索引，值可以为空
ALTER TABLE tableName ADD INDEX indexName(column_list); # 添加一个普通索引，索引可以不唯一
ALTER TABLE tableName ADD FULLTEXT indexName(column_list); # 添加一全文索引
5、合适创建索引的场景
一、主键自动建立唯一索引
二、频繁作为查询条件的字段应该创建索引
三、查询中与其它表进行关联的字段(外键)应该创建索引
四、where 条件里用不到的字段不创建索引
五、单值/符合索引的选择问题，who？(在高并发下倾向创建组合索引)
六、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
七、查询中用于统计或者分组的字段应该创建索引
6、不合适创建索引的场景
一、表数据量太少(MySQL数据库的性能瓶颈在300w左右数据量)
二、频繁更新的字段不合适创建索引：因为每次更新字段不仅仅是更新数据还要更新索引空间
三、数据重复且分布平均的表字段，因此应该为最经常查询或者排序的字段列创建索引(索引列数值的重复率越低索引性能越高)
性能分析
1、MySQL Query Optimizer(内置优化器)
一、MySQL 中有专门负责优化 select 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供 MySQL
认为最优的执行计划(他认为最优的数据检索方式，但不一定是实际业务场景最优的，这部分很消耗时间)
二、当客户端向 MySQL 发送一条 Query，命令解析器模块完成请求分类，区分出是 select 语句并转给 MySQL Query Optimizer 时，
MySQL Query Optimizer 首先会对整条 Query 语句进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的
查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结构调整等。然后分析 Query 中的 Hint 信息(如果有)，看 Hint 信息
是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，
根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
2、MySQL 常见瓶颈
CPU：CPU 在饱和的时候一般会发生在数据载入内存和或者从磁盘中读取数据的时候
I/O：磁盘 I/O 瓶颈发生在将一次性载入内存的数据量远大于内存容量的时候
硬件：服务器硬件的性能瓶颈：top、free、iostat 和 vmstat 来查看系统的性能状态
3、EXPLAIN
一、是什么(执行计划)：
使用 EXPLAIN 关键字可以模拟优化器执行 Query 语句，从而知道 MySQL 是如何执行 SQL语句的，分析 Query 语句或者是表结构的性能瓶颈
官网地址：http://dev.mysql.com/doc/refman/5.6/en/explain-output.html
二、能干什么：
① 表的读取顺序
② 数据读取操作的操作类型
③ 哪些索引可以使用
④ 哪些索引被实际使用
⑤ 表之间的引用
⑥ 每张表有多少张被优化器查询
三、怎么用：
① EXPLAIN + SQL 语句
② 执行计划包含的信息
id select_type table type possible_keys key key_len ref rows Extra
四、各字段解释：
id：
一、select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
二、三种情况：
a：id 相同，执行顺序由上至下
b：id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
c：id 相同/不同，同时存在：先 b 后 a 的顺序流程
select_type：
一、类型：simple、primary、subquery、derived、union、union result
二、查询类型主要用于区分普通查询、联合查询、子查询等复杂查询
a：simple——简单 select 查询，查询中不包含子查询或者 union
b：primary——查询中包含任何复杂子部分，最外层查询的标志
c：subquery——在 select 或 where 列表中的子查询
d：derived——在 from 列表中包含的子查询被标志为 derived(衍生)，MySQL会递归执行这些子查询，把结果放到临时表中。
e：union——若第二个 select 出现在 union 之后，则被标志为 union，若 union 包含在 from 子句的子查询中，外层 select 将会被标志为 derived
f：union result——从 union 表获取结果的 select
table：显示这一行数据关于哪一张表的
type：
一、ALL index range ref eq_ref const system NULL
二、访问排序类型：
type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > range > ALL
三、显示查询使用了何种类型，从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL
1、system：表中只有一条数据(相当于系统表)，这是 const 类型的特列，平时不会出现，这个也可以忽略不计
例子：select * from table_name(数据表只有一条数据)
2、const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快，
如将主键置于 where 条件列表上，MySQL 就能把该条件转换成一个常量
例子：select * from table_name where index_field = value1(index_field——唯一索引字段，value1是常量)
3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
例子：select * from table_name where index_field = value1(index_field——唯一索引字段，value1是变量)
4、ref：非唯一索引扫描，返回某个单独值的所有行数据，本质上也是一种索引访问，它返回所有匹配某个单独值的行数据，
然而，它可能会找到多个符合条件的行数据，所以它应该属于查找和扫描的混合体
例子：select * from table_name where index_field = value1(index_field——非唯一索引字段，value1是变量)
5、range：只检索给定范围的行数据，使用一个索引来选择行，key 列显示使用了某个索引，一般是出现在 where 列表中出现了
between、<、>、in 等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，而不用扫描全索引。
例子：
一、select * from table_name where index_field between value1 and value2(index_field——索引字段，value是变量)
二、select * from table_name where index_field > value1(index_field——索引字段，value1是变量)
三、select * from table_name where index_field in(value1, value2) (index_field——索引字段，value1是变量)
6、index ：full index scan，index 与 All 区别为 index 类型只遍历索引树，这通常 ALL 快，因为索引文件通常比数据文件小
(也就是说虽然 ALL 和 index 都是读全表，但 index 是从索引中读取，而 ALL 是从硬盘中读取)
例子：select index_field from table_name(index_field——索引字段，value1是变量)
7、ALL：full table scan，将全表扫描找到要匹配的行数据
例子：select * from table_name
四、一般来说，开发中得保证查询至少是 range 级别，最好是 ref 级别。
possible_keys：
一、显示可能应用在这张表中的索引列表
二、查询涉及到的字段中若存在索引，则会被列出来，但不一定会在实际使用
key：
一、实际使用的索引，如果为 NULL，则没有使用索引
二、查询中若使用了覆盖索引，则该索引仅出现在 key 列表中
三、覆盖索引：查询列表的字段列表和顺序跟联合索引字段的个数和顺序一致的情况称为覆盖索引
key_len：
一、表示索引中使用到的字节数，可通过该列计算查询中的索引长度，在不损失查询条件精确性的情况下，长度越短越好
二、key_len 值表示索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表设计时的字段长度得到，不是通过表内检索得到。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常量，哪些列或常量被用于查询索引列上的值
rows：根据表统计信息和索引选用情况，大致估算出找到目标的记录需要读取的行数
Extra：包含一些不合适在其他列中显示，但又十分重要的额外信息
一、using filesort：说明 MySQL 会对数据进行一个外部的索引排序，而不是按照表内的索引顺序进行读取(MySQL 中无法使用索引完成排序操作称为“文件排序”)
二、using temporary：使用了临时表保存中间数据，MySQL 对查询结果进行排序时使用了临时表，常见用于排序 order by 和分组查询 group by 。
三、using index：
① 表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问表的数据行，查询效率高，
如果同时出现了 using where 表示索引被用来执行索引值的查找
如果没有同时出现 using where，表示索引用来读取数据而非执行查找操作。
② 覆盖索引：
理解一：就是 select 查找的数据列只用在索引中就能取得结果，不比读取数据行，MySQL 可以利用索引返回 select 列表中的字段，
而不必根据索引再次读取数据文件，换句话说就是查询列被创建的索引所覆盖
理解二：索引是高效查找数据行的方法，但一般数据库也能用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了
它们索引的数据，当然能通过索引找到想要的数据，就不需要读取行了，一个索引包含了(覆盖了)满足查找结果的数据就叫覆盖索引。
③ 如果想要使用覆盖索引查找数据，一定要注意 select 列表中只取需要的列，避免出现 select * 的情况，
如果将所有字段一起做成索引会造成索引文件过大，查询性能也会下降
四、using where：表示查询使用了 where 条件列表过滤
五、using join buffer：表示查询使用了连接缓存(如频繁出现，可以适当的在配置文件中把 join buffer 参数调大)
六、impossible where：where 子句的总值是 false，不能用来获取任何元组
七、select table optimized alway：在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 搜索引擎优化
count(*) 操作，不比等到执行阶段再计算，查询执行计划生成阶段即可完成优化。
八、distinct：优化 distinct 操作，再找到匹配的第一元祖后，即可停止找同样值的动作。
sql 执行顺序：
执行顺序1：select_type 为 union，说明第四个 select 是 union 的第二个 select，最先执行【select name, id from t2】
执行顺序2：id 为 3，是整个查询中第三个 select 的一部分，因查询包含在 from 内，所以为 derived。【select id, name from t1 where other_column = ''】
执行顺序3：select 列表中的子查询 select_type 为 subquery，为整个查询中的第二个 select。【select id from t3】
执行顺序4：id 列为1，表示是 union 里的第一个 select，select_type 列的primary 表示该查询为外层查询，table 列被标记为<derived3>，
表示查询结果来自一张 衍生表，其中 derived3 的 3 表示该查询衍生至第三个 select 查询，即 id 为 3 的select
【select d1.name,(select id from t3) d2 from (select id, name from t1 where other_column = '') d1】
执行顺序5：代表从 union 的临时表中读取行的阶段，table 列的 <union1,4> 表示用第一个和第四个 select 的结果进行 union 操作。【两个结果 union 操作】
索引优化
1. 创建测试表
# 创建一张文章信息表：
CREATE TABLE `article` (
`id` bigint NOT NULL AUTO_INCREMENT,
`author_id` bigint DEFAULT NULL,
`category_id` bigint DEFAULT NULL,
`views` int DEFAULT NULL,
`comments` int DEFAULT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`content` text COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
# 创建一张作者信息表：
CREATE TABLE `author` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# 创建类别信息表
CREATE TABLE `class` (
`id` bigint NOT NULL AUTO_INCREMENT,
`category_id` bigint DEFAULT NULL,
`name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`remark` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
2. 初始化测试数据
# 向 article 表中插入数据
INSERT article(author_id, category_id, views, comments, title, content) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(3, 3, 3, 3, '3', '3'),
(4, 4, 4, 4, '4', '4'),
(5, 5, 5, 5, '5', '5');
# 向 author 表中插入数据
INSERT INTO `author`(username, PASSWORD, phone) VALUES
('张三', '123456', '134123'),
('李四', '123456', '134123'),
('王五', '123456', '134123'),
('赵六', '123456', '134123'),
('钱七', '123456', '134123');
# 向 class 表中插入数据
INSERT INTO class(category_id, NAME, remark) VALUES
(1, 'hklhsf', '干哈寰谛凤翎看回放'),
(2, 'gfddg', '和SVN换了卡恢复了'),
(1, 'xbggsd', '更何况近些年即可当'),
(2, 'sgdfg', '健康的更合理的后果'),
(1, 'gdjdfa', '物化生科技范副科级');
3. 优化案例1
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 案例1：查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id.
EXPLAIN SELECT author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1
# 为 article 表的 comments、views 字段创建联合索引
CREATE INDEX idx_cat_com_vie ON article(category_id, comments, views);
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 案例1：查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id.
EXPLAIN SELECT author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1
# 删除索引
DROP INDEX idx_cat_com_vie ON article;
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 为 article 表的 comments、views 字段创建联合索引
CREATE INDEX idx_comments_views ON article(comments, views);
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 案例1：查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id.
EXPLAIN SELECT author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1
# 删除索引
DROP INDEX idx_comments_views ON article;
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 为 article 表的 comments、views 字段创建联合索引
CREATE INDEX idx_cat_vie ON article(category_id, views);
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 案例1：查询 category_id 为 1 且 comments 大于 1 的情况下，views 最多的 article_id.
EXPLAIN SELECT author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1
4. 优化案例2
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 案例2：联合查询 article 和 author 表的索引优化
EXPLAIN SELECT * FROM article AS ar
LEFT JOIN author AS au
ON ar.`author_id` = au.`author_id`
# 对左表(即 article 的 author_id 键创建索引)
CREATE INDEX idx_article_author_id ON article(author_id)
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 案例2：联合查询 article 和 author 表的索引优化
EXPLAIN SELECT * FROM article AS ar
LEFT JOIN author AS au
ON ar.`author_id` = au.`author_id`
# 删除左表连接字段的索引
DROP INDEX idx_article_author_id ON article
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 对右表表(即 author 的 author_id 键创建索引)
CREATE INDEX idx_article_author_id ON author(author_id)
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 案例2：联合查询 article 和 author 表的索引优化
EXPLAIN SELECT * FROM article AS ar
LEFT JOIN author AS au
ON ar.`author_id` = au.`author_id`
# 删除右表表连接字段的索引
DROP INDEX idx_article_author_id ON author
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 对左表表(即 article 的 author_id 键创建索引)
CREATE INDEX idx_article_author_id ON article(author_id)
# 对右表表(即 author 的 author_id 键创建索引)
CREATE INDEX idx_author_author_id ON author(author_id)
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 案例2：联合查询 article 和 author 表的索引优化
EXPLAIN SELECT * FROM article AS ar
LEFT JOIN author AS au
ON ar.`author_id` = au.`author_id`
5. 优化案例3
多表联查情况(举例使用 3 表联查)
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 查询 author 表的索引情况
SHOW INDEX FROM class
# 使用左连接联合查询 article、author、class 三表
EXPLAIN SELECT * FROM article AS ar
LEFT JOIN author AS au ON ar.`author_id` = au.`author_id`
LEFT JOIN class AS cl ON ar.`category_id` = cl.`category_id`
# 对左表表(即 article 的 author_id 键创建索引)
CREATE INDEX idx_class_category_id ON class(category_id)
# 对右表表(即 author 的 author_id 键创建索引)
CREATE INDEX idx_author_author_id ON author(author_id)
# 查询 article 表的索引情况
SHOW INDEX FROM article
# 查询 author 表的索引情况
SHOW INDEX FROM author
# 查询 class 表的索引情况
SHOW INDEX FROM class
# 使用左连接联合查询 article、author、class 三表
EXPLAIN SELECT * FROM article AS ar
LEFT JOIN author AS au ON ar.`author_id` = au.`author_id`
LEFT JOIN class AS cl ON ar.`category_id` = cl.`category_id`
6. join 语句优化结论
一、在联合查询中左连接查询要对右表的关联字段创建索引优化。反之，右连接查询要对左表的关联字段创建索引优化。
二、尽可能减少 join 语句中的 NestedLoop 的循环总次数，永远用小结果集驱动大结果集。
三、优先优化 NestedLoop 的内层循环
四、保证 join 语句中被驱动表上 join 条件字段已经被索引
五、当无法保证被驱动表的 join 条件字段被索引且内存资源充足的情况下，不要太吝啬 JoinBuffer 的设置
7. 灵魂思考
假如目前已经对左表(article)的 author_id 字段建立索引，在不改变索引结构的情况下，如何才能使得联合查询用到索引字段呢？
答案：因为已经证明：左连接查询对右表建立索引，右连接对左表建立索引。所以我们只要把左连接和右连接进行互换即可。
索引失效
# 创建一张 customer 表
CREATE TABLE `customer` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` VARCHAR(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`gander` INT DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_usr_pas_pho` (`username`,`password`,`phone`)
) ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# 向customer 表中插入测试数据
insert into `customer`(`id`,`username`,`password`,`phone`,`gander`) values
(1,'李白','libai','123456',1),
(2,'杜甫','dufu','123456',1),
(3,'李清照','liqingzhao','123456',0),
(4,'苏东波','sudongbo','123456',1),
(5,'武则天','wuzetian','123456',0),
(6,'方冰冰','fanye','123456',0),
(7,'刘德华','liudehua','123456',1);
# 给该表的 username, password, phone 字段创建一个联合索引
CREATE INDEX idx_usr_pas_pho ON customer(username, PASSWORD, phone)
# 查看索引情况
SHOW INDEX FROM customer
一、全值索引匹配最好
# 使用全值索引查询
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND PASSWORD = 'libai' AND phone = '123456'
二、最佳左前缀法则：如果索引多列，要遵循索引最左前缀法则，指的是查询从索引的最左列开始，并且不要跳过中间的索引列
# 使用最左两个索引查询
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND PASSWORD = 'libai'
# 使用最右两个索引查询
EXPLAIN SELECT * FROM customer WHERE PASSWORD = 'libai' AND phone = '123456'
# 使用前后两个索引查询
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND phone = '123456'
三、不在索引列上做任何操作(计算、函数、自动或手动的类型转换)，会导致索引失效而转向全表扫描
# 使用了计算条件导致索引失效
EXPLAIN SELECT * FROM customer WHERE username = '李白' + '1'
# 使用了函数导致索引失效
EXPLAIN SELECT * FROM customer WHERE LEFT(username, 2) = '李白'
# 手动或自动的类型转换导致索引失效
EXPLAIN SELECT * FROM customer WHERE phone = 123456
四、存储索引不能使用索引中范围条件右边的列
# 存储索引不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND PASSWORD > 'libai' AND phone = '123456'
五、尽量使用覆盖索引(只访问索引的查询，且索引列和查询列一致)，减少使用 select *
# 尽量使用覆盖索引(只访问索引的查询，且索引列和查询列一致)，减少使用 select *
EXPLAIN SELECT username, PASSWORD, phone FROM customer WHERE username = '李白' AND PASSWORD = 'libai' AND phone = '123456'
六、MySQL 在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描(8.0以后可以使用Using index condition)
# MySQL 在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描(8.0以后可以使用Using index condition)
EXPLAIN SELECT * FROM customer WHERE username != '李白'
七、is null，is not null 也无法使用索引(8.0以后可以使用Using index condition)
# is null，is not null 也无法使用索引(8.0以后可以使用Using index condition)
EXPLAIN SELECT * FROM customer WHERE username IS NULL
EXPLAIN SELECT * FROM customer WHERE username IS NOT NULL
八、like 以通配符开头(’%abc…’)mysql 索引会失效变成全表扫描
# like 以通配符开头('%abc....')mysql 索引会失效变成全表扫描(可以使用覆盖索引解决这个问题)
EXPLAIN SELECT * FROM customer WHERE username LIKE '%李'
EXPLAIN SELECT * FROM customer WHERE username LIKE '李%'
# 利用覆盖索引解决通配符开头('%abc....')导致索引失效问题
EXPLAIN SELECT username FROM customer WHERE username LIKE '%李'
九、字符串不加单引号会引起索引失效
# 字符串不加单引号会引起索引失效(原因是mysql内核自动做了类型转换：varchar -> int)
EXPLAIN SELECT * FROM customer WHERE phone = 123456
十、少用 or，用它来连接时会索引失效
# 少用 or，用它来连接时会索引失效(可以使用 union 解决这个问题)
EXPLAIN SELECT * FROM customer WHERE username = '李白' OR PASSWORD = 'libai'
# 利用 union 解决 sql 中使用了or 关键字导致索引失效问题
EXPLAIN
SELECT * FROM customer WHERE username = '李白'
UNION
SELECT * FROM customer WHERE PASSWORD = 'libai'
十一、总结口诀
①全值匹配我最爱，最左前缀要遵守
②带头大哥不能死，中间兄弟不能断
③索引列上少计算，范围之后全失效
④ like 百分加右边，覆盖索引不写星
⑤不等空值还有 or，索引失效要少用
| where 语句 | 索引是否被使用 |
|---|---|
| where a = 3 | Y，使用到 a |
| where a = 3 and b = 5 | Y，使用到 a，b |
| where a = 3 and b = 5 and c = 4 | Y，使用到 a，b，c |
| where a = 3 or b = 5 or c = 4 | N |
| where a = 3 and c = 4 | 使用到 a，但 c 不可以，因为 b 中断了 |
| where a = 3 and b > 5 and c = 4 | 使用到 a 和 b，但 c 不可以，因为 c 在 b 范围之后 |
| where a = 3 and b like ‘fas%’ and c = 4 | 使用到 a，b，c |
| where a = 3 and b like ‘%fas’ and c = 4 | 使用到 a |
| where a = 3 and b like ‘%fas%’ and c = 4 | 使用到 a |
| where a = 3 and b like ‘a%fas%’ and c = 4 | 使用到 a，b，c |
like 查询只要是常量开头就可以使用索引
223
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
