一、MySQL架构
MySQL简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。
MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL是开源的，所以你不需要支付额外的费用。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。
MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
高级MySQL
MySQL内核
SQL优化攻城狮
MySQL服务器的优化
各种参数常量设定
查询语句优化
主从复制
软硬件升级
容灾备份
SQL编程
完整的MySQL优化需要很深的功底，大公司甚至有专门的DBA写上述。
###Linux下安装解压版(tar.gz)MySQL5.7
原文链接
####一、清理老的mysql
（1）卸载已有的mysql
查找以前是否装有mysql命令：
rpm -qa|grep -i mysql
停止mysql服务，卸载之前安装的mysql
rpm -ev 包名
如果卸载过程中报依赖错误，直接在卸载命名后面加参数 --nodeps
rpm -ev 包名 --nodeps
（2）查找之前老版本mysql的文件并删除老版本mysql的文件
find / -name mysql
出来一堆列表, 一个一个删除就ok
####二、安装前的准备
（1）安装mysql之前需要确保系统中有libaio依赖
yum search libaio
yum install libaio
（2）下载安装包（tar.gz的包，非rpm和yum）
cd /usr/
mkdir jone
cd jone
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-el7-x86_64.tar.gz
也可以自行去官网下载，使用文件传输的形式将该文件进行传输
（3）解压
解压到当前文件夹，并进行重命名
tar -zxvf mysql-5.7.22-el7-x86_64.tar.gz
mv mysql-5.7.22-el7-x86_64 mysql5.7 #重命名为mysql5.7
####三、安装
（1）添加用户和组
添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql(使用-r参数表示mysql用户是一个系统用户，不能登录)
useradd -r -g mysql mysql
#添加完用下面命令测试,能看到mysql用户的信息
id mysql
#查看mysql用户的所属组别
（2）手动创建MySQL data目录
cd /usr/jone/mysql5.7/
mkdir data
# 这里将数据路径安置在解压后的mysql路径下
（3）目录权限设置
将mysql及其下所有的目录所有者和组均设为mysql
chown -R mysql:mysql /usr/database/mysql5.7/
查看是否设置成功，执行下面命令，可以看到文件的所有者和组都变成了mysql
cd /usr/jone/
ll
ll指令能够查看路径的创建者，路径的从属的组，和路径可以有权限访问的用户
（4）配置my.cnf文件
此文件非常重要，初始化之前要把此文件放到 /etc 目录下
rm -rf /etc/my.cnf
vim /etc/my.cnf
#此文件内容如下(路径根据自己的实际情况):
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
init-connect='SET NAMES utf8'
basedir=/usr/database/mysql5.7 #根据自己的安装目录填写
datadir=/usr/database/mysql5.7/data #根据自己的mysql数据目录填写
socket=/tmp/mysql.sock
max_connections=200 # 允许最大连接数
character-set-server=utf8 # 服务端使用的字符集默认为8比特编码的latin1字符集
default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎
（5）初始化mysql
/usr/database/mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/database/mysql5.7 --datadir=/usr/database/mysql5.7/data
#注意：mysqld --initialize-insecure初始化后的mysql是没有密码的
#重新修改下各个目录的权限
chown -R root:root /usr/database/mysql5.7/ #把安装目录的目录的权限所有者改为root
chown -R mysql:mysql /usr/database/mysql5.7/data/ #把data目录的权限所有者改为mysql
（6）启动mysql
/usr/database/mysql5.7/bin/mysqld_safe --user=mysql &
（7）修改密码
cd /usr/database/mysql5.7/bin/
./mysql -u root -p # 默认没有密码，但是上一步初始化过程中会赋予一个随机的，不能丢失，要通过那个进行密码修改
修改密码
alter user user() identified by "新密码";
（8）测试登录
cd /usr/database/mysql5.7/bin/
./mysql mysql -u root -p
输入密码后，应该就连接上了
show databases;
exit; #退出
（9）copy启动脚本并将其添加到服务且设置为开机启动
#mysql启动脚本为：/usr/database/mysql5.7/support-files/mysql.server
cp /usr/database/mysql5.7/support-files/mysql.server /etc/init.d/mysql
#添加服务
chkconfig --add mysql
# 显示服务列表
chkconfig --list
# 开机启动
chkconfig --level 345 mysql on
# 测试添加的服务是否能用
service mysql status #查看状态
service mysql start #启动mysql服务
service mysql stop #停止mysql服务
####四、设置外网可以访问
在mysql的bin目录下执行：mysql -uroot -p密码 登陆到数据：
执行：use mysql;
执行：select host,user from user;
可以看到user为root，host为localhost的话，说明mysql只允许本机连接，那么外网，本地软件客户端就无法连接了。
调整方法：
执行：update user set host='%' where user ='root';
执行刷新:flush privileges;
OK!现在可以访问了！
如果还访问不了，那可能是防火墙问题,修改下防火墙就ok，修改方法这里就不提了，网上很多资料
####五、相关说明
（1）mysql服务的启动和停止命令
service mysql status #查看状态
service mysql start #启动mysql服务
service mysql stop #停止mysql服务
（2）怎么在Linux中登录mysql
进入mysql安装目录的bin目录，然后输入以下命令
./mysql -u root -p
然后输入密码就登录成功
exit;#退出mysql
常用操作
开启mysql服务器
/usr/jone/mysql5.7/bin/mysqld --user=mysql &
用户登录
/usr/jone/mysql5.7/bin/mysql -u root -p
修改用户和组的权限
chown -R mysql:mysql /usr/jone/mysql5.7/data/ #把data目录的权限所有者改为mysql
chown -R root:root /usr/jone/mysql5.7/ #把安装目录的目录的权限所有者改为rootc
###修改字符集
查看字符集
show variables like 'character%";
show variables like ‘%char%";
在/etc/my.cnf中可以设定字符集的相关参数
如
[mysqld]
character-set-server=utf8
character-set-client=utf8
character-server=utf8-general-ci
二、索引优化分析
###性能下降原因
- 查询语句问题
- 索引失效
如何写索引？
单值 为user表的name字段创建一个索引，其索引名为idx_user_name
select * from user where name='';
create index idx_user_name on user(name);
符合索引 为name和email字段建立联合索引
select * from user where name='' and email='';
create index idx_user_name on user(name, email);
- 关联查询太多
频繁使用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>
编译器解读顺序
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
总结
七种JOIN理论
准备工作，创建以下表，插入新数据：
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
执行后的结果：
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
1.inner join
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)
2.left join
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)
3.right join
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)
4.left join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)
``
5.right join 2
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptid is null;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)
6.full join
MySQL不支持full join，不过可以换种方法表示
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)
7.full join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)
###索引
####定义
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的目的在于提高查询效率，可以类比字典。
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要逐个逐个寻找，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
是不是觉得如果没有索引，这个事情根本无法完成？
可以得到索引的本质：索引是数据结构。可以简单理解为“排好序的快速查找数据结构”。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
####索引的优劣势
优势
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的（占空间）
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MysQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。
总结
索引，空间换取时间。
####索引分类
MySQL索引分类：
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
- 唯一索引：索引列的值必须唯一，但允许有空值。
- 复合索引：即一个索引包含多个列
- 基本语法：
创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM tableName;
使用alter命令 - 有四种方式来添加数据表的索引
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，用于全文索引。
####索引结构与检索原理
MySQL索引结构 - BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
初始化介绍
一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示),如磁盘块1包含数据项17和35，包含指针P1、P2、P3,P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
查找过程
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
####哪些情况适合建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
- Where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
####哪些情况不适合建索引
- 表记录太少
- 经常增删改的表
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率天约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
###性能分析
MySQL Query Optimizer
Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间）
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
####explain使用简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
用途
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
用法
explain + sql语句
执行计划包含的信息
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> explain select * from tbl_dept;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_dept | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
逐个分析explain结果表中的字段含义。
#####explain之id介绍
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
当id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行，衍生=DERIVED
小结
id越大越先查询
#####explain之select_type和table介绍
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
select_type有哪些？
- SIMPLE - 简单的select查询,查询中不包含子查询或者UNION。
- PRIMARY - 查询中若包含任何复杂的子部分，最外层查询则被标记为。
- SUBQUERY - 在SELECT或WHERE列表中包含了子查询。
- DERIVED - 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVED。
- UNION RESULT - 从UNION表获取结果的SELECT。
table：显示这一行的数据是关于哪张表的。
#####explain之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：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
匹配一个
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
匹配多个
range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
匹配范围
index：全索引扫描，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
all：全表扫描，将遍历全表以找到匹配的行。
#####explain之possible_keys和key
possible_keys
可选用索引，显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。
key
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中，即可用为null，实用为某一索引。查询字段内容恰好索引中都有。
#####explain之key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
#####explain之ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。
查询中与其它表关联的字段，外键关系建立索引。
#####explain之rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
#####explain之Extra
包含不适合在其他列中显示但十分重要的额外信息。
Using filesort 使用文件排序
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"
Using temporary 使用临时变量表
使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index 使用索引
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index）,一说为索引覆盖。
索引包含查找字段内容，不回表，直接返回查询结果。
理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
Using where
表明使用了where过滤。
Using join buffer
使用了连接缓存。
impossible where 不可能条件
where子句的值总是false，不能用来获取任何元组。
select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
#####举例说明
第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name… 】
第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,namefrom t1 where other_column=’’】
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
#####索引单表优化案例
建表SQL
CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARCHAR(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');
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
任务
查询category_id为1且comments 大于1的情况下，views最多的article_id。
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.00 sec)
得到查询结果↑
mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
查看该表索引↑
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
解释分析，查找类型是ALL，全表扫描，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的。
进行优化
mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 1 | category_id | A | 2 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
新建索引 ↑
或者使用添加索引的方式↓
ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
创建之后查看效果↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
Extra里还是出现了Using filesort，创建这索引作用不大。↑
如果comments > 1换成comments = 1，可以让Using filesort消失，但不符题目要求。↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
将索引删除↓
mysql> DROP INDEX idx_article_ccv ON article;
分析：
type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
但是我们已经建立了索引，为啥没用呢？
这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。
当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
改进
跟上次创建索引相比，这次不为comments字段创建索引。
mysql> create index idx_article_cv on article(category_id, views);
再次分析解释↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，type变为了ref，Extra中的Using filesort也消失了，结果非常理想。
#####索引两表优化案例
建表
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 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)));
建表结果
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 17 |
| 2 | 2 |
| 3 | 18 |
| 4 | 4 |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
| 8 | 1 |
| 9 | 18 |
| 10 | 6 |
| 11 | 15 |
| 12 | 15 |
| 13 | 12 |
| 14 | 15 |
| 15 | 18 |
| 16 | 2 |
| 17 | 18 |
| 18 | 5 |
| 19 | 7 |
| 20 | 1 |
| 21 | 2 |
+----+------+
21 rows in set (0.00 sec)
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 8 |
| 2 | 14 |
| 3 | 3 |
| 4 | 16 |
| 5 | 8 |
| 6 | 12 |
| 7 | 17 |
| 8 | 8 |
| 9 | 10 |
| 10 | 3 |
| 11 | 4 |
| 12 | 12 |
| 13 | 9 |
| 14 | 7 |
| 15 | 6 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 5 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
进行explani分析 ↓
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
类型都是all，表示进行全表扫描，此时进行优化。
为book.card建立索引。
mysql> ALTER TABLE `book` ADD INDEX Y(`card`);
进行explain分析↓
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | my.class.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
可以看到第二行的type变为了ref，rows也变少了，优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引。
那么我们删去右表的索引，为左表建立索引，进行explain分析则有
mysql> drop index y on book;
mysql> ALTER TABLE `class` ADD INDEX Y(`card`);
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | index | NULL | Y | 4 | NULL | 21 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
发现虽然使用了左表的索引，但是依然把左表的所有数据行都走了一遍。可见右边是我们的关键点，要想优化需要在右表建立索引。
在此索引不变的情况下，使用右连接查询，并看解析。
mysql> EXPLAIN SELECT * FROM class right JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ref | Y | Y | 4 | my.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
发现class表作为左表，查询行数只有一条，此时索引起作用了。这也是左外联查询和右外联查询的特点。哪张表作为驱动表，他的全部数据都会查一遍，类型，即使使用了索引，也会把索引都查一遍。所以要给另一张表建立索引，或者外联查询时调换顺序。
总结
索引两表优化，左连接右表建索引，右连接左表建索引。
索引三表优化案例
建表语句
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
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)));
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)));
建立三张表，phone（新表）,book,class;
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
| 1 | 10 |
| 2 | 13 |
| 3 | 17 |
| 4 | 5 |
| 5 | 12 |
| 6 | 7 |
| 7 | 15 |
| 8 | 17 |
| 9 | 17 |
| 10 | 14 |
| 11 | 19 |
| 12 | 13 |
| 13 | 5 |
| 14 | 8 |
| 15 | 2 |
| 16 | 8 |
| 17 | 11 |
| 18 | 14 |
| 19 | 13 |
| 20 | 5 |
+---------+------+
20 rows in set (0.00 sec)
把刚才两表优化中class表的索引Y给去了,可以用
show index from [表名] 进行索引查看
mysql> show index from class;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| class | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | |
| class | 1 | Y | 1 | card | A | 12 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
mysql> drop index y on class;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
为phone.card 和 book.card建立新的索引↓
mysql> alter table `phone` add index z(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table `book` add index y(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用explain解析三表连接时的情况
mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | y | y | 4 | my.class.card | 1 | 100.00 | Using index |
| 1 | SIMPLE | phone | NULL | ref | z | z | 4 | my.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
后2行的 type 都是ref且总 rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。
结论
Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环，保证Join语句中被驱动表上Join条件字段已经被索引。
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。
####索引失效举例
首先了解索引失效的常见情况，并结合顺口溜记忆。
- 最佳左前缀法则，如果建立的聚合索引，那么希望使用到索引时，要遵循左前缀法则。指的是查询时，从索引的左前列开始，并且不跳过复合索引的中间列。
- 不对索引列进行操作(计算，函数，or，类型转换），否则会导致索引失效而进行全表扫描。
3.存储引擎不能使用索引中范围条件右边的列，按序使用索引时，如果某一个中间字段在where中使用了和范围有关的条件限制，那么这个字段后面的字段不会被当成索引来查询。 - 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
- 不使用不等于操作。mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
- 不使用判空非空操作。is null, is not null 也无法使用索引。
- 模糊查询使用通配符不要写在字串开头。like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
- 字符串不加单引号底层会调用函数做转化，索引失效。
- 不使用or，用它来连接时会索引失效。
优化总结口诀
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。
#####例子1 跳过复合索引的中间字段 没有做到全值匹配
建表↓
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT 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'入职时间'
)CHARSET utf8 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());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
使用带头大哥进行查询，看看有没有用上索引↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现使用上了索引，那么用带头大哥和一个中间兄弟呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现也用上了，那么三个都按照顺序使用呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现也用上了，满足全值匹配，那么中间兄弟没用上呢，查询时去掉name，只用中间age和最后一个pos
mysql> EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
发现没有用上索引，说明带头大哥不能死，那么带头大哥和中间兄弟都没有呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
发现确实也失效了，并没有用上索引。
引出最佳左前缀法则：查询时，从索引的最左前列开始，并且不跳过索引的中间列。但是可以忽略尾列，可以使用尾列之前的字段所组成的索引↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
用上了name和pos组成的索引。
#####例子2 索引列上做河外操作
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4)='July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
left函数，取name字段的开头4位，看是否和’July’相等。由于使用了函数，可以看到并没有调用索引。
例子3 限定符合索引某列的范围
存储引擎不能使用索引中范围条件右边的列， where A = ‘a’ AND B > 10 AND C = ‘c’; 这种情况就会建立字段A和B的索引，而ABC三个组合起来的索引并不会被使用上。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
↑，我们可以看到，全值匹配时，索引长度key_len(单位字节)是140，而使用范围时，长度是78，和单独使用前两个字段作索引时相当，佐证了我们的观点。语句类型从ref(固定参考范围列)变成了range（某一范围），范围扩大了。
例子4 select * 返回全部字段
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 当条件都用上索引字段时，全值匹配
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 就是查多少，就写在select后，尽量能够直接从索引列中找到
例子5 使用不等于
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，使用了不等于，连单独的索引列都没有用到
例子6 使用判空或者非空
is null, is not null 也无法使用索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
name是带头大哥，也没有用上长度为72的单列索引。
Extra打印为Impossible WHERE，是因为我们在创建staffs表，设置name字段的属性为not null。
下面额外演示Extra为Impossible WHERE情况。
mysql> EXPLAIN SELECT * FROM staffs WHERE 1=1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE 1!=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
例子7 模糊查询时通配符左打头
like以通配符%开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
↑，可以看到，如果百分号写右边就能使用上name作为索引
问题：解决like '%字符串%'时索引不被使用的方法？
新起一张表
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2aa2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3aa3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4aa4',26,'d@163.com');
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | a@163.com |
| 2 | 2aa2 | 23 | b@163.com |
| 3 | 3aa3 | 24 | c@163.com |
| 4 | 4aa4 | 26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)
那么在不含索引时，看看对于name字段作条件时，不同查找字段时的索引使用情况
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 非常明显，没有索引，咋查都是全表扫描 ，查找过程类型是全表扫描 ALL
那么关于name字段和age字段，创建一个聚合索引
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
那么再使用模糊查询，只对name和age查呢
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到，用上了name字段的索引，因为select的是name和age两个字段，索引完全包含了这两个字段的内容，直接返回索引中对应的结果而不用回表，故并未产生全表扫描。索引覆盖。
那么还有没有索引覆盖的例子呢？
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
↑id是主键，主键自然在索引中，所以也用上了
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
name就是单列索引，自然也用上了
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
↑ 为什么查age也覆盖了啊… age字段也在聚合索引中…吗…？
那么看看哪些没用上呢
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑，可以看出，查多了就没用上。email不在索引中。
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为
如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
小结
解决like '%字符串%'时索引不被使用的方法？复合索引，然后覆盖索引。
例子8：字符串是一个数字时却不带引号
数目字符串不加单引号索引失效。
mysql> SELECT * FROM staffs WHERE NAME=2000;
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+-----+---------------------+
| 3 | 2000 | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM staffs WHERE NAME='2000';
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+-----+---------------------+
| 3 | 2000 | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM staffs WHERE NAME=2000;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> explain SELECT * FROM staffs WHERE NAME='2000';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#####例子9 使用关键字OR
少用or，用它来连接时会索引失效。
mysql> explain SELECT * FROM staffs WHERE NAME='July' or name='z3';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM staffs WHERE NAME='July' or name='z3';
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2021-04-03 14:03:18 |
| 2 | July | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+---------+---------------------+
2 rows in set (0.00 sec)
索引失效综合例题
建表 test03 含有5个字段 c1 c2 c3 c4 c5，为前4个建立联合索引
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
查看数据和索引
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
mysql> show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
```##一、MySQL架构
###MySQL简介
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。
MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL是开源的，所以你不需要支付额外的费用。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。
MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
###高级MySQL
MySQL内核
SQL优化攻城狮
MySQL服务器的优化
各种参数常量设定
查询语句优化
主从复制
软硬件升级
容灾备份
SQL编程
完整的MySQL优化需要很深的功底，大公司甚至有专门的DBA写上述。
###Linux下安装解压版(tar.gz)MySQL5.7
[原文链接](https://www.cnblogs.com/zeng1994/p/f883e0a2832808455039ff83735d6579.html)
####一、清理老的mysql
**（1）卸载已有的mysql**
查找以前是否装有mysql命令：
```rpm -qa|grep -i mysql ```
停止mysql服务，卸载之前安装的mysql
```rpm -ev 包名```
如果卸载过程中报依赖错误，直接在卸载命名后面加参数 --nodeps
```rpm -ev 包名 --nodeps ```
**（2）查找之前老版本mysql的文件并删除老版本mysql的文件**
```find / -name mysql```
出来一堆列表, 一个一个删除就ok
####二、安装前的准备
**（1）安装mysql之前需要确保系统中有libaio依赖**
```yum search libaio ```
```yum install libaio ```
**（2）下载安装包（tar.gz的包，非rpm和yum）**
cd /usr/
mkdir jone
cd jone
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-el7-x86_64.tar.gz
也可以自行去官网下载，使用文件传输的形式将该文件进行传输
**（3）解压**
解压到当前文件夹，并进行重命名
tar -zxvf mysql-5.7.22-el7-x86_64.tar.gz
mv mysql-5.7.22-el7-x86_64 mysql5.7 #重命名为mysql5.7
####三、安装
**（1）添加用户和组**
添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql(使用-r参数表示mysql用户是一个系统用户，不能登录)
useradd -r -g mysql mysql
#添加完用下面命令测试,能看到mysql用户的信息
id mysql
#查看mysql用户的所属组别
**（2）手动创建MySQL data目录**
cd /usr/jone/mysql5.7/
mkdir data
这里将数据路径安置在解压后的mysql路径下
**（3）目录权限设置**
将mysql及其下所有的目录所有者和组均设为mysql
chown -R mysql:mysql /usr/database/mysql5.7/
查看是否设置成功，执行下面命令，可以看到文件的所有者和组都变成了mysql
cd /usr/jone/
ll
ll指令能够查看路径的创建者，路径的从属的组，和路径可以有权限访问的用户
（4）配置my.cnf文件
此文件非常重要，初始化之前要把此文件放到 /etc 目录下
rm -rf /etc/my.cnf
vim /etc/my.cnf
#此文件内容如下(路径根据自己的实际情况):
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
init-connect='SET NAMES utf8'
basedir=/usr/database/mysql5.7 #根据自己的安装目录填写
datadir=/usr/database/mysql5.7/data #根据自己的mysql数据目录填写
socket=/tmp/mysql.sock
max_connections=200 # 允许最大连接数
character-set-server=utf8 # 服务端使用的字符集默认为8比特编码的latin1字符集
default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎
（5）初始化mysql
/usr/database/mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/database/mysql5.7 --datadir=/usr/database/mysql5.7/data
#注意：mysqld --initialize-insecure初始化后的mysql是没有密码的
#重新修改下各个目录的权限
chown -R root:root /usr/database/mysql5.7/ #把安装目录的目录的权限所有者改为root
chown -R mysql:mysql /usr/database/mysql5.7/data/ #把data目录的权限所有者改为mysql
（6）启动mysql
/usr/database/mysql5.7/bin/mysqld_safe --user=mysql &
（7）修改密码
cd /usr/database/mysql5.7/bin/
./mysql -u root -p # 默认没有密码，但是上一步初始化过程中会赋予一个随机的，不能丢失，要通过那个进行密码修改
修改密码
alter user user() identified by "新密码";
（8）测试登录
cd /usr/database/mysql5.7/bin/
./mysql mysql -u root -p
输入密码后，应该就连接上了
show databases;
exit; #退出
（9）copy启动脚本并将其添加到服务且设置为开机启动
#mysql启动脚本为：/usr/database/mysql5.7/support-files/mysql.server
cp /usr/database/mysql5.7/support-files/mysql.server /etc/init.d/mysql
#添加服务
chkconfig --add mysql
# 显示服务列表
chkconfig --list
# 开机启动
chkconfig --level 345 mysql on
# 测试添加的服务是否能用
service mysql status #查看状态
service mysql start #启动mysql服务
service mysql stop #停止mysql服务
####四、设置外网可以访问
在mysql的bin目录下执行：mysql -uroot -p密码 登陆到数据：
执行：use mysql;
执行：select host,user from user;
可以看到user为root，host为localhost的话，说明mysql只允许本机连接，那么外网，本地软件客户端就无法连接了。
调整方法：
执行：update user set host='%' where user ='root';
执行刷新:flush privileges;
OK!现在可以访问了！
如果还访问不了，那可能是防火墙问题,修改下防火墙就ok，修改方法这里就不提了，网上很多资料
####五、相关说明
（1）mysql服务的启动和停止命令
service mysql status #查看状态
service mysql start #启动mysql服务
service mysql stop #停止mysql服务
（2）怎么在Linux中登录mysql
进入mysql安装目录的bin目录，然后输入以下命令
./mysql -u root -p
然后输入密码就登录成功
exit;#退出mysql
常用操作
开启mysql服务器
/usr/jone/mysql5.7/bin/mysqld --user=mysql &
用户登录
/usr/jone/mysql5.7/bin/mysql -u root -p
修改用户和组的权限
chown -R mysql:mysql /usr/jone/mysql5.7/data/ #把data目录的权限所有者改为mysql
chown -R root:root /usr/jone/mysql5.7/ #把安装目录的目录的权限所有者改为rootc
###修改字符集
查看字符集
show variables like 'character%";
show variables like ‘%char%";
在/etc/my.cnf中可以设定字符集的相关参数
如
[mysqld]
character-set-server=utf8
character-set-client=utf8
character-server=utf8-general-ci
二、索引优化分析
###性能下降原因
- 查询语句问题
- 索引失效
如何写索引？
单值 为user表的name字段创建一个索引，其索引名为idx_user_name
select * from user where name='';
create index idx_user_name on user(name);
符合索引 为name和email字段建立联合索引
select * from user where name='' and email='';
create index idx_user_name on user(name, email);
- 关联查询太多
频繁使用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>
编译器解读顺序
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
总结
[外链图片转存中…(img-dTv3dgrS-1634390106414)]
七种JOIN理论
[外链图片转存中…(img-AsPL6XhU-1634390106424)]
准备工作，创建以下表，插入新数据：
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
执行后的结果：
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
1.inner join
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)
2.left join
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)
3.right join
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)
4.left join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)
``
5.right join 2
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptid is null;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)
6.full join
MySQL不支持full join，不过可以换种方法表示
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)
7.full join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)
###索引
####定义
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的目的在于提高查询效率，可以类比字典。
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要逐个逐个寻找，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
是不是觉得如果没有索引，这个事情根本无法完成？
可以得到索引的本质：索引是数据结构。可以简单理解为“排好序的快速查找数据结构”。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
[外链图片转存中…(img-NaJ1c3RS-1634390106429)]
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
####索引的优劣势
优势
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的（占空间）
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MysQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。
总结
索引，空间换取时间。
####索引分类
MySQL索引分类：
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
- 唯一索引：索引列的值必须唯一，但允许有空值。
- 复合索引：即一个索引包含多个列
- 基本语法：
创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM tableName;
使用alter命令 - 有四种方式来添加数据表的索引
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，用于全文索引。
####索引结构与检索原理
MySQL索引结构 - BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
[外链图片转存中…(img-A13yFd89-1634390106432)]
初始化介绍
一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示),如磁盘块1包含数据项17和35，包含指针P1、P2、P3,P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
查找过程
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
####哪些情况适合建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
- Where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
####哪些情况不适合建索引
- 表记录太少
- 经常增删改的表
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率天约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
###性能分析
MySQL Query Optimizer
Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间）
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
####explain使用简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
用途
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
用法
explain + sql语句
执行计划包含的信息
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> explain select * from tbl_dept;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_dept | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
逐个分析explain结果表中的字段含义。
#####explain之id介绍
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
当id相同，执行顺序由上至下
[外链图片转存中…(img-OVRi3oUJ-1634390106433)]
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
[外链图片转存中…(img-eO2nKxj0-1634390106434)]
id相同不同，同时存在
[外链图片转存中…(img-RE10k2ix-1634390106435)]
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行，衍生=DERIVED
小结
id越大越先查询
#####explain之select_type和table介绍
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
select_type有哪些？
- SIMPLE - 简单的select查询,查询中不包含子查询或者UNION。
- PRIMARY - 查询中若包含任何复杂的子部分，最外层查询则被标记为。
- SUBQUERY - 在SELECT或WHERE列表中包含了子查询。
- DERIVED - 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVED。
- UNION RESULT - 从UNION表获取结果的SELECT。
table：显示这一行的数据是关于哪张表的。
#####explain之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：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
[外链图片转存中…(img-csetVCJi-1634390106436)]
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
匹配一个
[外链图片转存中…(img-qGo6m02g-1634390106437)]
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
匹配多个
[外链图片转存中…(img-oU4QBbIW-1634390106438)]
range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
匹配范围
[外链图片转存中…(img-Ost1ZzmF-1634390106438)]
index：全索引扫描，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
[外链图片转存中…(img-zlqRSplJ-1634390106439)]
all：全表扫描，将遍历全表以找到匹配的行。
[外链图片转存中…(img-auUn2RvG-1634390106441)]
#####explain之possible_keys和key
possible_keys
可选用索引，显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。
key
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中，即可用为null，实用为某一索引。查询字段内容恰好索引中都有。
[外链图片转存中…(img-rJfoj91C-1634390106442)]
#####explain之key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
[外链图片转存中…(img-6Gjl76Kc-1634390106443)]
#####explain之ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
[外链图片转存中…(img-TSIblNxd-1634390106444)]
由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。
查询中与其它表关联的字段，外键关系建立索引。
#####explain之rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
[外链图片转存中…(img-DkenMfuI-1634390106445)]
#####explain之Extra
包含不适合在其他列中显示但十分重要的额外信息。
Using filesort 使用文件排序
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"
[外链图片转存中…(img-nlxGwuWe-1634390106445)]
Using temporary 使用临时变量表
使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
[外链图片转存中…(img-zqEK8E3d-1634390106447)]
Using index 使用索引
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
[外链图片转存中…(img-5TBCxQTb-1634390106448)]
覆盖索引（Covering Index）,一说为索引覆盖。
索引包含查找字段内容，不回表，直接返回查询结果。
理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
Using where
表明使用了where过滤。
Using join buffer
使用了连接缓存。
impossible where 不可能条件
where子句的值总是false，不能用来获取任何元组。
[外链图片转存中…(img-PGxw2uPj-1634390106449)]
select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
#####举例说明
[外链图片转存中…(img-0tRYD0oQ-1634390106450)]
第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name… 】
第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,namefrom t1 where other_column=’’】
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
#####索引单表优化案例
建表SQL
CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARCHAR(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');
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
任务
查询category_id为1且comments 大于1的情况下，views最多的article_id。
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.00 sec)
得到查询结果↑
mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
查看该表索引↑
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
解释分析，查找类型是ALL，全表扫描，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的。
进行优化
mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 1 | category_id | A | 2 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
新建索引 ↑
或者使用添加索引的方式↓
ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
创建之后查看效果↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
Extra里还是出现了Using filesort，创建这索引作用不大。↑
如果comments > 1换成comments = 1，可以让Using filesort消失，但不符题目要求。↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
将索引删除↓
mysql> DROP INDEX idx_article_ccv ON article;
分析：
type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
但是我们已经建立了索引，为啥没用呢？
这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。
当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
改进
跟上次创建索引相比，这次不为comments字段创建索引。
mysql> create index idx_article_cv on article(category_id, views);
再次分析解释↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，type变为了ref，Extra中的Using filesort也消失了，结果非常理想。
#####索引两表优化案例
建表
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 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)));
建表结果
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 17 |
| 2 | 2 |
| 3 | 18 |
| 4 | 4 |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
| 8 | 1 |
| 9 | 18 |
| 10 | 6 |
| 11 | 15 |
| 12 | 15 |
| 13 | 12 |
| 14 | 15 |
| 15 | 18 |
| 16 | 2 |
| 17 | 18 |
| 18 | 5 |
| 19 | 7 |
| 20 | 1 |
| 21 | 2 |
+----+------+
21 rows in set (0.00 sec)
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 8 |
| 2 | 14 |
| 3 | 3 |
| 4 | 16 |
| 5 | 8 |
| 6 | 12 |
| 7 | 17 |
| 8 | 8 |
| 9 | 10 |
| 10 | 3 |
| 11 | 4 |
| 12 | 12 |
| 13 | 9 |
| 14 | 7 |
| 15 | 6 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 5 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
进行explani分析 ↓
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
类型都是all，表示进行全表扫描，此时进行优化。
为book.card建立索引。
mysql> ALTER TABLE `book` ADD INDEX Y(`card`);
进行explain分析↓
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | my.class.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
可以看到第二行的type变为了ref，rows也变少了，优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引。
那么我们删去右表的索引，为左表建立索引，进行explain分析则有
mysql> drop index y on book;
mysql> ALTER TABLE `class` ADD INDEX Y(`card`);
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | index | NULL | Y | 4 | NULL | 21 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
发现虽然使用了左表的索引，但是依然把左表的所有数据行都走了一遍。可见右边是我们的关键点，要想优化需要在右表建立索引。
在此索引不变的情况下，使用右连接查询，并看解析。
mysql> EXPLAIN SELECT * FROM class right JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ref | Y | Y | 4 | my.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
发现class表作为左表，查询行数只有一条，此时索引起作用了。这也是左外联查询和右外联查询的特点。哪张表作为驱动表，他的全部数据都会查一遍，类型，即使使用了索引，也会把索引都查一遍。所以要给另一张表建立索引，或者外联查询时调换顺序。
总结
索引两表优化，左连接右表建索引，右连接左表建索引。
索引三表优化案例
建表语句
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
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)));
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)));
建立三张表，phone（新表）,book,class;
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
| 1 | 10 |
| 2 | 13 |
| 3 | 17 |
| 4 | 5 |
| 5 | 12 |
| 6 | 7 |
| 7 | 15 |
| 8 | 17 |
| 9 | 17 |
| 10 | 14 |
| 11 | 19 |
| 12 | 13 |
| 13 | 5 |
| 14 | 8 |
| 15 | 2 |
| 16 | 8 |
| 17 | 11 |
| 18 | 14 |
| 19 | 13 |
| 20 | 5 |
+---------+------+
20 rows in set (0.00 sec)
把刚才两表优化中class表的索引Y给去了,可以用
show index from [表名] 进行索引查看
mysql> show index from class;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| class | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | |
| class | 1 | Y | 1 | card | A | 12 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
mysql> drop index y on class;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
为phone.card 和 book.card建立新的索引↓
mysql> alter table `phone` add index z(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table `book` add index y(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用explain解析三表连接时的情况
mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | y | y | 4 | my.class.card | 1 | 100.00 | Using index |
| 1 | SIMPLE | phone | NULL | ref | z | z | 4 | my.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
后2行的 type 都是ref且总 rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。
结论
Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环，保证Join语句中被驱动表上Join条件字段已经被索引。
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。
####索引失效举例
首先了解索引失效的常见情况，并结合顺口溜记忆。
- 最佳左前缀法则，如果建立的聚合索引，那么希望使用到索引时，要遵循左前缀法则。指的是查询时，从索引的左前列开始，并且不跳过复合索引的中间列。
- 不对索引列进行操作(计算，函数，or，类型转换），否则会导致索引失效而进行全表扫描。
3.存储引擎不能使用索引中范围条件右边的列，按序使用索引时，如果某一个中间字段在where中使用了和范围有关的条件限制，那么这个字段后面的字段不会被当成索引来查询。 - 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
- 不使用不等于操作。mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
- 不使用判空非空操作。is null, is not null 也无法使用索引。
- 模糊查询使用通配符不要写在字串开头。like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
- 字符串不加单引号底层会调用函数做转化，索引失效。
- 不使用or，用它来连接时会索引失效。
优化总结口诀
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。
#####例子1 跳过复合索引的中间字段 没有做到全值匹配
建表↓
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT 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'入职时间'
)CHARSET utf8 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());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
使用带头大哥进行查询，看看有没有用上索引↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现使用上了索引，那么用带头大哥和一个中间兄弟呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现也用上了，那么三个都按照顺序使用呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现也用上了，满足全值匹配，那么中间兄弟没用上呢，查询时去掉name，只用中间age和最后一个pos
mysql> EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
发现没有用上索引，说明带头大哥不能死，那么带头大哥和中间兄弟都没有呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
发现确实也失效了，并没有用上索引。
引出最佳左前缀法则：查询时，从索引的最左前列开始，并且不跳过索引的中间列。但是可以忽略尾列，可以使用尾列之前的字段所组成的索引↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
用上了name和pos组成的索引。
#####例子2 索引列上做河外操作
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4)='July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
left函数，取name字段的开头4位，看是否和’July’相等。由于使用了函数，可以看到并没有调用索引。
例子3 限定符合索引某列的范围
存储引擎不能使用索引中范围条件右边的列， where A = ‘a’ AND B > 10 AND C = ‘c’; 这种情况就会建立字段A和B的索引，而ABC三个组合起来的索引并不会被使用上。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
↑，我们可以看到，全值匹配时，索引长度key_len(单位字节)是140，而使用范围时，长度是78，和单独使用前两个字段作索引时相当，佐证了我们的观点。语句类型从ref(固定参考范围列)变成了range（某一范围），范围扩大了。
例子4 select * 返回全部字段
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 当条件都用上索引字段时，全值匹配
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 就是查多少，就写在select后，尽量能够直接从索引列中找到
例子5 使用不等于
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，使用了不等于，连单独的索引列都没有用到
例子6 使用判空或者非空
is null, is not null 也无法使用索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
name是带头大哥，也没有用上长度为72的单列索引。
Extra打印为Impossible WHERE，是因为我们在创建staffs表，设置name字段的属性为not null。
下面额外演示Extra为Impossible WHERE情况。
mysql> EXPLAIN SELECT * FROM staffs WHERE 1=1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE 1!=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
例子7 模糊查询时通配符左打头
like以通配符%开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
↑，可以看到，如果百分号写右边就能使用上name作为索引
问题：解决like '%字符串%'时索引不被使用的方法？
新起一张表
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2aa2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3aa3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4aa4',26,'d@163.com');
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | a@163.com |
| 2 | 2aa2 | 23 | b@163.com |
| 3 | 3aa3 | 24 | c@163.com |
| 4 | 4aa4 | 26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)
那么在不含索引时，看看对于name字段作条件时，不同查找字段时的索引使用情况
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 非常明显，没有索引，咋查都是全表扫描 ，查找过程类型是全表扫描 ALL
那么关于name字段和age字段，创建一个聚合索引
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
那么再使用模糊查询，只对name和age查呢
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到，用上了name字段的索引，因为select的是name和age两个字段，索引完全包含了这两个字段的内容，直接返回索引中对应的结果而不用回表，故并未产生全表扫描。索引覆盖。
那么还有没有索引覆盖的例子呢？
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
↑id是主键，主键自然在索引中，所以也用上了
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
name就是单列索引，自然也用上了
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
↑ 为什么查age也覆盖了啊… age字段也在聚合索引中…吗…？
那么看看哪些没用上呢
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑，可以看出，查多了就没用上。email不在索引中。
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为
如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
小结
解决like '%字符串%'时索引不被使用的方法？复合索引，然后覆盖索引。
例子8：字符串是一个数字时却不带引号
数目字符串不加单引号索引失效。
mysql> SELECT * FROM staffs WHERE NAME=2000;
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+-----+---------------------+
| 3 | 2000 | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM staffs WHERE NAME='2000';
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+-----+---------------------+
| 3 | 2000 | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)
mysql> explain SELECT * FROM staffs WHERE NAME=2000;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> explain SELECT * FROM staffs WHERE NAME='2000';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
#####例子9 使用关键字OR
少用or，用它来连接时会索引失效。
mysql> explain SELECT * FROM staffs WHERE NAME='July' or name='z3';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM staffs WHERE NAME='July' or name='z3';
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2021-04-03 14:03:18 |
| 2 | July | 23 | dev | 2021-04-03 14:03:18 |
+----+------+-----+---------+---------------------+
2 rows in set (0.00 sec)
索引失效综合例题
建表 test03 含有5个字段 c1 c2 c3 c4 c5，为前4个建立联合索引
create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
查看数据和索引
mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1 | c2 | c3 | c4 | c5 |
+----+------+------+------+------+------+
| 1 | a1 | a2 | a3 | a4 | a5 |
| 2 | b1 | b2 | b3 | b4 | b5 |
| 3 | c1 | c2 | c3 | c4 | c5 |
| 4 | d1 | d2 | d3 | d4 | d5 |
| 5 | e1 | e2 | e3 | e4 | e5 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
mysql> show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | |
| test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。
MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL是开源的，所以你不需要支付额外的费用。
MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL使用标准的SQL数据语言形式。
MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Per、PHP、Eifel、Ruby和Tcl等。Mysql对PHP有很好的支持，PHP是目前最流行的Web开发语言。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。
高级MySQL
MySQL内核
SQL优化攻城狮
MySQL服务器的优化
各种参数常量设定
查询语句优化
主从复制
软硬件升级
容灾备份
SQL编程
完整的MySQL优化需要很深的功底，大公司甚至有专门的DBA写上述。
###Linux下安装解压版(tar.gz)MySQL5.7
原文链接
一、清理老的mysql
（1）卸载已有的mysql
查找以前是否装有mysql命令：
rpm -qa|grep -i mysql
停止mysql服务，卸载之前安装的mysql
rpm -ev 包名
如果卸载过程中报依赖错误，直接在卸载命名后面加参数 --nodeps
rpm -ev 包名 --nodeps
（2）查找之前老版本mysql的文件并删除老版本mysql的文件
find / -name mysql
出来一堆列表, 一个一个删除就ok
二、安装前的准备
（1）安装mysql之前需要确保系统中有libaio依赖
yum search libaio
yum install libaio
（2）下载安装包（tar.gz的包，非rpm和yum）
cd /usr/
mkdir jone
cd jone
wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-el7-x86_64.tar.gz
也可以自行去官网下载，使用文件传输的形式将该文件进行传输
（3）解压
解压到当前文件夹，并进行重命名
tar -zxvf mysql-5.7.22-el7-x86_64.tar.gz
mv mysql-5.7.22-el7-x86_64 mysql5.7 #重命名为mysql5.7
三、安装
（1）添加用户和组
添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql(使用-r参数表示mysql用户是一个系统用户，不能登录)
useradd -r -g mysql mysql
#添加完用下面命令测试,能看到mysql用户的信息
id mysql
#查看mysql用户的所属组别
（2）手动创建MySQL data目录
cd /usr/jone/mysql5.7/
mkdir data
# 这里将数据路径安置在解压后的mysql路径下
（3）目录权限设置
将mysql及其下所有的目录所有者和组均设为mysql
chown -R mysql:mysql /usr/database/mysql5.7/
查看是否设置成功，执行下面命令，可以看到文件的所有者和组都变成了mysql
cd /usr/jone/
ll
ll指令能够查看路径的创建者，路径的从属的组，和路径可以有权限访问的用户
（4）配置my.cnf文件
此文件非常重要，初始化之前要把此文件放到 /etc 目录下
rm -rf /etc/my.cnf
vim /etc/my.cnf
#此文件内容如下(路径根据自己的实际情况):
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
init-connect='SET NAMES utf8'
basedir=/usr/database/mysql5.7 #根据自己的安装目录填写
datadir=/usr/database/mysql5.7/data #根据自己的mysql数据目录填写
socket=/tmp/mysql.sock
max_connections=200 # 允许最大连接数
character-set-server=utf8 # 服务端使用的字符集默认为8比特编码的latin1字符集
default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎
（5）初始化mysql
/usr/database/mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/database/mysql5.7 --datadir=/usr/database/mysql5.7/data
#注意：mysqld --initialize-insecure初始化后的mysql是没有密码的
#重新修改下各个目录的权限
chown -R root:root /usr/database/mysql5.7/ #把安装目录的目录的权限所有者改为root
chown -R mysql:mysql /usr/database/mysql5.7/data/ #把data目录的权限所有者改为mysql
（6）启动mysql
/usr/database/mysql5.7/bin/mysqld_safe --user=mysql &
（7）修改密码
cd /usr/database/mysql5.7/bin/
./mysql -u root -p # 默认没有密码，但是上一步初始化过程中会赋予一个随机的，不能丢失，要通过那个进行密码修改
修改密码
alter user user() identified by "新密码";
（8）测试登录
cd /usr/database/mysql5.7/bin/
./mysql mysql -u root -p
输入密码后，应该就连接上了
show databases;
exit; #退出
（9）copy启动脚本并将其添加到服务且设置为开机启动
#mysql启动脚本为：/usr/database/mysql5.7/support-files/mysql.server
cp /usr/database/mysql5.7/support-files/mysql.server /etc/init.d/mysql
#添加服务
chkconfig --add mysql
# 显示服务列表
chkconfig --list
# 开机启动
chkconfig --level 345 mysql on
# 测试添加的服务是否能用
service mysql status #查看状态
service mysql start #启动mysql服务
service mysql stop #停止mysql服务
四、设置外网可以访问
在mysql的bin目录下执行：mysql -uroot -p密码 登陆到数据：
执行：use mysql;
执行：select host,user from user;
可以看到user为root，host为localhost的话，说明mysql只允许本机连接，那么外网，本地软件客户端就无法连接了。
调整方法：
执行：update user set host='%' where user ='root';
执行刷新:flush privileges;
OK!现在可以访问了！
如果还访问不了，那可能是防火墙问题,修改下防火墙就ok，修改方法这里就不提了，网上很多资料
五、相关说明
（1）mysql服务的启动和停止命令
service mysql status #查看状态
service mysql start #启动mysql服务
service mysql stop #停止mysql服务
（2）怎么在Linux中登录mysql
进入mysql安装目录的bin目录，然后输入以下命令
./mysql -u root -p
然后输入密码就登录成功
exit;#退出mysql
常用操作
开启mysql服务器
/usr/jone/mysql5.7/bin/mysqld --user=mysql &
用户登录
/usr/jone/mysql5.7/bin/mysql -u root -p
修改用户和组的权限
chown -R mysql:mysql /usr/jone/mysql5.7/data/ #把data目录的权限所有者改为mysql
chown -R root:root /usr/jone/mysql5.7/ #把安装目录的目录的权限所有者改为rootc
###修改字符集
查看字符集
show variables like 'character%";
show variables like ‘%char%";
在/etc/my.cnf中可以设定字符集的相关参数
如
[mysqld]
character-set-server=utf8
character-set-client=utf8
character-server=utf8-general-ci
二、索引优化分析
性能下降原因
- 查询语句问题
- 索引失效
如何写索引？
单值 为user表的name字段创建一个索引，其索引名为idx_user_name
select * from user where name='';
create index idx_user_name on user(name);
符合索引 为name和email字段建立联合索引
select * from user where name='' and email='';
create index idx_user_name on user(name, email);
- 关联查询太多
频繁使用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>
编译器解读顺序
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
总结
七种JOIN理论
准备工作，创建以下表，插入新数据：
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE tbl_emp (
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY fk_dept_Id (deptId)
#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
执行后的结果：
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
| 1 | z3 | 1 |
| 2 | z4 | 1 |
| 3 | z5 | 1 |
| 4 | w5 | 2 |
| 5 | w6 | 2 |
| 6 | s7 | 3 |
| 7 | s8 | 4 |
| 8 | s9 | 51 |
+----+------+--------+
8 rows in set (0.00 sec)
1.inner join
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)
2.left join
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)
3.right join
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)
4.left join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)
``
5.right join 2
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptid is null;
+------+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)
6.full join
MySQL不支持full join，不过可以换种方法表示
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
-> union
-> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 1 | z3 | 1 | 1 | RD | 11 |
| 2 | z4 | 1 | 1 | RD | 11 |
| 3 | z5 | 1 | 1 | RD | 11 |
| 4 | w5 | 2 | 2 | HR | 12 |
| 5 | w6 | 2 | 2 | HR | 12 |
| 6 | s7 | 3 | 3 | MK | 13 |
| 7 | s8 | 4 | 4 | MIS | 14 |
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)
7.full join 2
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+--------+------+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+------+------+--------+------+----------+--------+
| 8 | s9 | 51 | NULL | NULL | NULL |
| NULL | NULL | NULL | 5 | FD | 15 |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)
索引
定义
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的目的在于提高查询效率，可以类比字典。
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要逐个逐个寻找，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
是不是觉得如果没有索引，这个事情根本无法完成？
可以得到索引的本质：索引是数据结构。可以简单理解为“排好序的快速查找数据结构”。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
####索引的优劣势
优势
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的（占空间）
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MysQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。
总结
索引，空间换取时间。
####索引分类
MySQL索引分类：
- 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
- 唯一索引：索引列的值必须唯一，但允许有空值。
- 复合索引：即一个索引包含多个列
- 基本语法：
创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM tableName;
使用alter命令 - 有四种方式来添加数据表的索引
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，用于全文索引。
####索引结构与检索原理
MySQL索引结构 - BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
初始化介绍
一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示),如磁盘块1包含数据项17和35，包含指针P1、P2、P3,P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
查找过程
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO。在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
####哪些情况适合建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段，外键关系建立索引- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
- Where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
####哪些情况不适合建索引
- 表记录太少
- 经常增删改的表
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率天约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
性能分析
MySQL Query Optimizer
Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间）
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态
explain使用简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
用途
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
用法
explain + sql语句
执行计划包含的信息
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
+----+----------+--------+
5 rows in set (0.00 sec)
mysql> explain select * from tbl_dept;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_dept | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
逐个分析explain结果表中的字段含义。
explain之id介绍
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
当id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行，衍生=DERIVED
小结
id越大越先查询
explain之select_type和table介绍
select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
select_type有哪些？
- SIMPLE - 简单的select查询,查询中不包含子查询或者UNION。
- PRIMARY - 查询中若包含任何复杂的子部分，最外层查询则被标记为。
- SUBQUERY - 在SELECT或WHERE列表中包含了子查询。
- DERIVED - 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
- UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVED。
- UNION RESULT - 从UNION表获取结果的SELECT。
table：显示这一行的数据是关于哪张表的。
explain之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：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
匹配一个
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
匹配多个
range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
匹配范围
index：全索引扫描，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
all：全表扫描，将遍历全表以找到匹配的行。
explain之possible_keys和key
possible_keys
可选用索引，显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。
key
实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中，即可用为null，实用为某一索引。查询字段内容恰好索引中都有。
explain之key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
explain之ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。
查询中与其它表关联的字段，外键关系建立索引。
#####explain之rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
explain之Extra
包含不适合在其他列中显示但十分重要的额外信息。
Using filesort 使用文件排序
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"
Using temporary 使用临时变量表
使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index 使用索引
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index）,一说为索引覆盖。
索引包含查找字段内容，不回表，直接返回查询结果。
理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
Using where
表明使用了where过滤。
Using join buffer
使用了连接缓存。
impossible where 不可能条件
where子句的值总是false，不能用来获取任何元组。
select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
#####举例说明
第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name… 】
第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,namefrom t1 where other_column=’’】
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
#####索引单表优化案例
建表SQL
CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARCHAR(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');
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
任务
查询category_id为1且comments 大于1的情况下，views最多的article_id。
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
| 3 | 1 |
+----+-----------+
1 row in set (0.00 sec)
得到查询结果↑
mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
查看该表索引↑
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
解释分析，查找类型是ALL，全表扫描，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的。
进行优化
mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 1 | category_id | A | 2 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
新建索引 ↑
或者使用添加索引的方式↓
ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );
创建之后查看效果↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
Extra里还是出现了Using filesort，创建这索引作用不大。↑
如果comments > 1换成comments = 1，可以让Using filesort消失，但不符题目要求。↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
将索引删除↓
mysql> DROP INDEX idx_article_ccv ON article;
分析：
type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
但是我们已经建立了索引，为啥没用呢？
这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。
当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
改进
跟上次创建索引相比，这次不为comments字段创建索引。
mysql> create index idx_article_cv on article(category_id, views);
再次分析解释↓
mysql> explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，type变为了ref，Extra中的Using filesort也消失了，结果非常理想。
#####索引两表优化案例
建表
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 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)));
建表结果
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 17 |
| 2 | 2 |
| 3 | 18 |
| 4 | 4 |
| 5 | 4 |
| 6 | 8 |
| 7 | 9 |
| 8 | 1 |
| 9 | 18 |
| 10 | 6 |
| 11 | 15 |
| 12 | 15 |
| 13 | 12 |
| 14 | 15 |
| 15 | 18 |
| 16 | 2 |
| 17 | 18 |
| 18 | 5 |
| 19 | 7 |
| 20 | 1 |
| 21 | 2 |
+----+------+
21 rows in set (0.00 sec)
mysql> select * from book;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 8 |
| 2 | 14 |
| 3 | 3 |
| 4 | 16 |
| 5 | 8 |
| 6 | 12 |
| 7 | 17 |
| 8 | 8 |
| 9 | 10 |
| 10 | 3 |
| 11 | 4 |
| 12 | 12 |
| 13 | 9 |
| 14 | 7 |
| 15 | 6 |
| 16 | 8 |
| 17 | 3 |
| 18 | 11 |
| 19 | 5 |
| 20 | 11 |
+--------+------+
20 rows in set (0.00 sec)
进行explani分析 ↓
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
类型都是all，表示进行全表扫描，此时进行优化。
为book.card建立索引。
mysql> ALTER TABLE `book` ADD INDEX Y(`card`);
进行explain分析↓
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | Y | Y | 4 | my.class.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
可以看到第二行的type变为了ref，rows也变少了，优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引。
那么我们删去右表的索引，为左表建立索引，进行explain分析则有
mysql> drop index y on book;
mysql> ALTER TABLE `class` ADD INDEX Y(`card`);
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | index | NULL | Y | 4 | NULL | 21 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
发现虽然使用了左表的索引，但是依然把左表的所有数据行都走了一遍。可见右边是我们的关键点，要想优化需要在右表建立索引。
在此索引不变的情况下，使用右连接查询，并看解析。
mysql> EXPLAIN SELECT * FROM class right JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | class | NULL | ref | Y | Y | 4 | my.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
发现class表作为左表，查询行数只有一条，此时索引起作用了。这也是左外联查询和右外联查询的特点。哪张表作为驱动表，他的全部数据都会查一遍，类型，即使使用了索引，也会把索引都查一遍。所以要给另一张表建立索引，或者外联查询时调换顺序。
总结
索引两表优化，左连接右表建索引，右连接左表建索引。
索引三表优化案例
建表语句
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
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)));
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)));
建立三张表，phone（新表）,book,class;
mysql> select * from phone;
+---------+------+
| phoneid | card |
+---------+------+
| 1 | 10 |
| 2 | 13 |
| 3 | 17 |
| 4 | 5 |
| 5 | 12 |
| 6 | 7 |
| 7 | 15 |
| 8 | 17 |
| 9 | 17 |
| 10 | 14 |
| 11 | 19 |
| 12 | 13 |
| 13 | 5 |
| 14 | 8 |
| 15 | 2 |
| 16 | 8 |
| 17 | 11 |
| 18 | 14 |
| 19 | 13 |
| 20 | 5 |
+---------+------+
20 rows in set (0.00 sec)
把刚才两表优化中class表的索引Y给去了,可以用
show index from [表名] 进行索引查看
mysql> show index from class;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| class | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | |
| class | 1 | Y | 1 | card | A | 12 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)
mysql> drop index y on class;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from book;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
为phone.card 和 book.card建立新的索引↓
mysql> alter table `phone` add index z(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table `book` add index y(`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用explain解析三表连接时的情况
mysql> explain SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | y | y | 4 | my.class.card | 1 | 100.00 | Using index |
| 1 | SIMPLE | phone | NULL | ref | z | z | 4 | my.book.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
后2行的 type 都是ref且总 rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。
结论
Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环，保证Join语句中被驱动表上Join条件字段已经被索引。
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。
####索引失效举例
首先了解索引失效的常见情况，并结合顺口溜记忆。
- 最佳左前缀法则，如果建立的聚合索引，那么希望使用到索引时，要遵循左前缀法则。指的是查询时，从索引的左前列开始，并且不跳过复合索引的中间列。
- 不对索引列进行操作(计算，函数，or，类型转换），否则会导致索引失效而进行全表扫描。
3.存储引擎不能使用索引中范围条件右边的列，按序使用索引时，如果某一个中间字段在where中使用了和范围有关的条件限制，那么这个字段后面的字段不会被当成索引来查询。 - 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。
- 不使用不等于操作。mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
- 不使用判空非空操作。is null, is not null 也无法使用索引。
- 模糊查询使用通配符不要写在字串开头。like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
- 字符串不加单引号底层会调用函数做转化，索引失效。
- 不使用or，用它来连接时会索引失效。
优化总结口诀
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。
例子1 跳过复合索引的中间字段 没有做到全值匹配
建表↓
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT 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'入职时间'
)CHARSET utf8 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());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
使用带头大哥进行查询，看看有没有用上索引↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现使用上了索引，那么用带头大哥和一个中间兄弟呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现也用上了，那么三个都按照顺序使用呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发现也用上了，满足全值匹配，那么中间兄弟没用上呢，查询时去掉name，只用中间age和最后一个pos
mysql> EXPLAIN SELECT * FROM staffs WHERE age=25 AND pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
发现没有用上索引，说明带头大哥不能死，那么带头大哥和中间兄弟都没有呢↓
mysql> EXPLAIN SELECT * FROM staffs WHERE pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
发现确实也失效了，并没有用上索引。
引出最佳左前缀法则：查询时，从索引的最左前列开始，并且不跳过索引的中间列。但是可以忽略尾列，可以使用尾列之前的字段所组成的索引↓
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
用上了name和pos组成的索引。
例子2 索引列上做河外操作
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE left(NAME,4)='July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
left函数，取name字段的开头4位，看是否和’July’相等。由于使用了函数，可以看到并没有调用索引。
例子3 限定符合索引某列的范围
存储引擎不能使用索引中范围条件右边的列， where A = ‘a’ AND B > 10 AND C = ‘c’; 这种情况就会建立字段A和B的索引，而ABC三个组合起来的索引并不会被使用上。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
↑，我们可以看到，全值匹配时，索引长度key_len(单位字节)是140，而使用范围时，长度是78，和单独使用前两个字段作索引时相当，佐证了我们的观点。语句类型从ref(固定参考范围列)变成了range（某一范围），范围扩大了。
例子4 select * 返回全部字段
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 当条件都用上索引字段时，全值匹配
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND pos='dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME='July' AND age>25 AND pos='dev';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT name FROM staffs WHERE NAME='July' AND age=25;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 就是查多少，就写在select后，尽量能够直接从索引列中找到
例子5 使用不等于
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME!='July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME<>'July';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到，使用了不等于，连单独的索引列都没有用到
例子6 使用判空或者非空
is null, is not null 也无法使用索引
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME is not null;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
name是带头大哥，也没有用上长度为72的单列索引。
Extra打印为Impossible WHERE，是因为我们在创建staffs表，设置name字段的属性为not null。
下面额外演示Extra为Impossible WHERE情况。
mysql> EXPLAIN SELECT * FROM staffs WHERE 1=1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE 1!=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)
例子7 模糊查询时通配符左打头
like以通配符%开头（’%abc…’），mysql索引失效会变成全表扫描的操作。
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME='July';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like '%July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
↑，可以看到，如果百分号写右边就能使用上name作为索引
问题：解决like '%字符串%'时索引不被使用的方法？
新起一张表
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2aa2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3aa3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4aa4',26,'d@163.com');
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age | email |
+----+------+------+-----------+
| 1 | 1aa1 | 21 | a@163.com |
| 2 | 2aa2 | 23 | b@163.com |
| 3 | 3aa3 | 24 | c@163.com |
| 4 | 4aa4 | 26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)
那么在不含索引时，看看对于name字段作条件时，不同查找字段时的索引使用情况
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT id,NAME,age,email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tbl_user | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
↑ 非常明显，没有索引，咋查都是全表扫描 ，查找过程类型是全表扫描 ALL
那么关于name字段和age字段，创建一个聚合索引
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
那么再使用模糊查询，只对name和age查呢
mysql> EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tbl_user | NULL | index | NULL | idx_user_nameAge | 68 | NULL | 4 | 25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到，用上了name字段的索引，因为select的是name和age两个字段，索引完全包含了这两个字段的内容，直接返回索引中对应的结果而不用回表，故并未产生全表扫描。索引覆盖。
扫一扫
5万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
