1. 性能下降SQL查询缓慢的原因
① 查询语句性能低下；
② 索引失效单值/复合索引；
③ 关联查询使用过多 JOIN 关联的数据库过多也会导致查询过慢（设计缺陷或者业务需求）
④ 服务器调优各个参数设置问题（缓冲/线程池等）；
2. 常见通用的Join查询
2.1 SQL 的执行顺序
① 传入的 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_conditon>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
② 优化后的 SQL 语句
伴随着 MySQL 版本的迭代升级，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗而进行动态的调整执行顺序，以下是经常出现的查询顺序：
FROM
<left_table>
<join_type> JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_conditon>
SELECT DISTINCT
<select_list>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
SQL 解析流程图
MySQL 服务器在执行 SQL 语句时是从
FROM处开始执行的；
2.2 SQL JOIN
2.2.1 创建数据库表：
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
创建完成后的表结构：
mysql> show tables;
+---------------------+
| Tables_in_base_crud |
+---------------------+
| tbl_dept |
| tbl_emp |
+---------------------+
2 rows in set (0.00 sec)
mysql> select * from tbl_emp;
+----+--------------+------+--------+--------+
| id | name | age | deptId | empno |
+----+--------------+------+--------+--------+
| 1 | 风清扬 | 90 | 1 | 100001 |
| 2 | 岳不群 | 50 | 1 | 100002 |
| 3 | 令狐冲 | 24 | 1 | 100003 |
| 4 | 洪七公 | 70 | 2 | 100004 |
| 5 | 乔峰 | 35 | 2 | 100005 |
| 6 | 灭绝师太 | 70 | 3 | 100006 |
| 7 | 周芷若 | 20 | 3 | 100007 |
| 8 | 张三丰 | 100 | 4 | 100008 |
| 9 | 张无忌 | 25 | 5 | 100009 |
| 10 | 韦小宝 | 18 | NULL | 100010 |
+----+--------------+------+--------+--------+
10 rows in set (0.00 sec)
mysql> select * from tbl_dept;
+----+----------+-----------+
| id | deptName | address |
+----+----------+-----------+
| 1 | 华山 | 华山 |
| 2 | 丐帮 | 洛阳 |
| 3 | 峨眉 | 峨眉山 |
| 4 | 武当 | 武当山 |
| 5 | 明教 | 光明顶 |
| 6 | 少林 | 少林寺 |
+----+----------+-----------+
6 rows in set (0.00 sec)
2.2.2 内连接：
INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "TableA" 表中的行在 "TableB" 中没有匹配，则不会列出这些行，即 TableA 和 TableB 的公共部分。
SELECT
<select_list>
FROM
TableA A
INNER JOIN TableB B ON A.key = B.key
示例：
mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
+----+--------------+------+--------+--------+----+----------+-----------+
| id | name | age | deptId | empno | id | deptName | address |
+----+--------------+------+--------+--------+----+----------+-----------+
| 1 | 风清扬 | 90 | 1 | 100001 | 1 | 华山 | 华山 |
| 2 | 岳不群 | 50 | 1 | 100002 | 1 | 华山 | 华山 |
| 3 | 令狐冲 | 24 | 1 | 100003 | 1 | 华山 | 华山 |
| 4 | 洪七公 | 70 | 2 | 100004 | 2 | 丐帮 | 洛阳 |
| 5 | 乔峰 | 35 | 2 | 100005 | 2 | 丐帮 | 洛阳 |
| 6 | 灭绝师太 | 70 | 3 | 100006 | 3 | 峨眉 | 峨眉山 |
| 7 | 周芷若 | 20 | 3 | 100007 | 3 | 峨眉 | 峨眉山 |
| 8 | 张三丰 | 100 | 4 | 100008 | 4 | 武当 | 武当山 |
| 9 | 张无忌 | 25 | 5 | 100009 | 5 | 明教 | 光明顶 |
+----+--------------+------+--------+--------+----+----------+-----------+
9 rows in set (0.00 sec)
2.2.3 左连接：
LEFT JOIN 关键字从左表（TableA）返回所有的行，即使右表（TableB）中没有匹配。如果右表中没有匹配，则结果为 NULL
SELECT
<select_list>
FROM
TableA A
LEFT JOIN TableB B ON A.key = B.key
仅包含左表：
TableA 独有 TableB 中没有的
SELECT
<select_list>
FROM
TableA A
INNER JOIN TableB B ON A.key = B.key
WHERE
B.key IS NULL
示例：
# 左连接
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+--------------+------+--------+--------+------+----------+-----------+
| id | name | age | deptId | empno | id | deptName | address |
+----+--------------+------+--------+--------+------+----------+-----------+
| 1 | 风清扬 | 90 | 1 | 100001 | 1 | 华山 | 华山 |
| 2 | 岳不群 | 50 | 1 | 100002 | 1 | 华山 | 华山 |
| 3 | 令狐冲 | 24 | 1 | 100003 | 1 | 华山 | 华山 |
| 4 | 洪七公 | 70 | 2 | 100004 | 2 | 丐帮 | 洛阳 |
| 5 | 乔峰 | 35 | 2 | 100005 | 2 | 丐帮 | 洛阳 |
| 6 | 灭绝师太 | 70 | 3 | 100006 | 3 | 峨眉 | 峨眉山 |
| 7 | 周芷若 | 20 | 3 | 100007 | 3 | 峨眉 | 峨眉山 |
| 8 | 张三丰 | 100 | 4 | 100008 | 4 | 武当 | 武当山 |
| 9 | 张无忌 | 25 | 5 | 100009 | 5 | 明教 | 光明顶 |
| 10 | 韦小宝 | 18 | NULL | 100010 | NULL | NULL | NULL |
+----+--------------+------+--------+--------+------+----------+-----------+
10 rows in set (0.00 sec)
# 仅包含左边
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+-----------+------+--------+--------+------+----------+---------+
| id | name | age | deptId | empno | id | deptName | address |
+----+-----------+------+--------+--------+------+----------+---------+
| 10 | 韦小宝 | 18 | NULL | 100010 | NULL | NULL | NULL |
+----+-----------+------+--------+--------+------+----------+---------+
1 row in set (0.01 sec)
2.2.4 右连接：
RIGHT JOIN 关键字从右表（TableB）返回所有的行，即使左表（TableA）中没有匹配。如果左表中没有匹配，则结果为 NULL。
SELECT
<select_list>
FROM
TableA A
RIGHT JOIN TableB B ON A.key = B.key
仅包含右表：
TableB 独有 TableA 中没有的
SELECT
<select_list>
FROM
TableA A
INNER JOIN TableB B ON A.key = B.key
WHERE
A.key IS NULL
示例：
# 右连接
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
+------+--------------+------+--------+--------+----+----------+-----------+
| id | name | age | deptId | empno | id | deptName | address |
+------+--------------+------+--------+--------+----+----------+-----------+
| 1 | 风清扬 | 90 | 1 | 100001 | 1 | 华山 | 华山 |
| 2 | 岳不群 | 50 | 1 | 100002 | 1 | 华山 | 华山 |
| 3 | 令狐冲 | 24 | 1 | 100003 | 1 | 华山 | 华山 |
| 4 | 洪七公 | 70 | 2 | 100004 | 2 | 丐帮 | 洛阳 |
| 5 | 乔峰 | 35 | 2 | 100005 | 2 | 丐帮 | 洛阳 |
| 6 | 灭绝师太 | 70 | 3 | 100006 | 3 | 峨眉 | 峨眉山 |
| 7 | 周芷若 | 20 | 3 | 100007 | 3 | 峨眉 | 峨眉山 |
| 8 | 张三丰 | 100 | 4 | 100008 | 4 | 武当 | 武当山 |
| 9 | 张无忌 | 25 | 5 | 100009 | 5 | 明教 | 光明顶 |
| NULL | NULL | NULL | NULL | NULL | 6 | 少林 | 少林寺 |
+------+--------------+------+--------+--------+----+----------+-----------+
10 rows in set (0.00 sec)
# 仅包含右表
mysql> select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
+------+------+------+--------+-------+----+----------+-----------+
| id | name | age | deptId | empno | id | deptName | address |
+------+------+------+--------+-------+----+----------+-----------+
| NULL | NULL | NULL | NULL | NULL | 6 | 少林 | 少林寺 |
+------+------+------+--------+-------+----+----------+-----------+
1 row in set (0.00 sec)
2.2.5 全连接：
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
SELECT
<select_list>
FROM
TableA A
FULL OUTER JOIN TableB B ON A.key = B.key
去除公共部分：
SELECT
<select_list>
FROM
TableA A
FULL OUTER JOIN TableB B ON A.key = B.key
WHERE
A.key IS NULL
OR
B.key IS NULL
示例：
# 全连接
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 | age | deptId | empno | id | deptName | address |
+------+--------------+------+--------+--------+------+----------+-----------+
| 1 | 风清扬 | 90 | 1 | 100001 | 1 | 华山 | 华山 |
| 2 | 岳不群 | 50 | 1 | 100002 | 1 | 华山 | 华山 |
| 3 | 令狐冲 | 24 | 1 | 100003 | 1 | 华山 | 华山 |
| 4 | 洪七公 | 70 | 2 | 100004 | 2 | 丐帮 | 洛阳 |
| 5 | 乔峰 | 35 | 2 | 100005 | 2 | 丐帮 | 洛阳 |
| 6 | 灭绝师太 | 70 | 3 | 100006 | 3 | 峨眉 | 峨眉山 |
| 7 | 周芷若 | 20 | 3 | 100007 | 3 | 峨眉 | 峨眉山 |
| 8 | 张三丰 | 100 | 4 | 100008 | 4 | 武当 | 武当山 |
| 9 | 张无忌 | 25 | 5 | 100009 | 5 | 明教 | 光明顶 |
| 10 | 韦小宝 | 18 | NULL | 100010 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 6 | 少林 | 少林寺 |
+------+--------------+------+--------+--------+------+----------+-----------+
11 rows in set (0.00 sec)
# 去除公共的部分
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 | age | deptId | empno | id | deptName | address |
+------+-----------+------+--------+--------+------+----------+-----------+
| 10 | 韦小宝 | 18 | NULL | 100010 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 6 | 少林 | 少林寺 |
+------+-----------+------+--------+--------+------+----------+-----------+
2 rows in set (0.00 sec)
MySQL 中不支持
FULL OUTER JOIN，因此使用UNION关联左连接和右连接实现查找全部操作。
3. 索引简介
3.1 概述
MySQL 官方对索引的定义：索引（Index）是帮助 MySQL 高效获取数据的数据结构。索引的本质就是数据结构，可以理解为排好序的快速查找数据结构。
索引会影响到
WHERE后的查找和ORDER BY后面的排序
数据库中在数据本身之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找方法，这种数据结构就是索引，即BTREE索引 。
一般来讲索引本身也很大，不可能全部存储在内存中，因此索引文件通常以索引文件的形式存储在磁盘上，即 myi 文件。
数据库中的数据最好不要删除，设置标记位置进行逻辑删除，被删除的数据可能还会被用于数据分析，更重要的是频繁的增删操作会造成索引失效；
**我们通常讲的索引，如果没有特别指明，都是指 BTREE（多路搜索树，并不一定是二叉树）结构组织的索引。**其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用的B+树索引，统称索引。除去 B+树索引结构外，也存在哈希（hash index） 等索引。
索引的优势：
- 提高数据的检索效率，降低数据库的IO陈本；
- 通过索引列对数据进行排序，降低数据排序成本，降低了 CPU 的消耗；
索引的劣势：
- 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的；
- 虽然索引大大提高了查询速度，但是会降低表的更新效率，如对表进行
INSERT、UPDATE、DELETE操作。因为更新表的时候，MySQL 不仅要保存数据，还需要保存索引文件。每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。 - 索引只是提高效率的一个因素，如果MySQL 中有大数据量的表，建立优秀的索引或优化查询语句。
3.2 索引的分类
3.2.1 单值索引
一个索引仅包含单个列，一个表可以有多个单列索引。
创建命令：CREATE INDEX idx_表名_字段名 ON 表名(字段名);
3.2.2 唯一索引
索引列的值必须唯一，但允许有空值。如：银行卡号，身份证号等。
创建命令：CREATE UNIQUE INDEX idx_表名_字段名 ON 表名(字段名);
3.3.3 复合索引
一个索引包含多个列
创建命令：CREATE INDEX idx_表名_字段1_字段2_字段n ON 表名(字段1,字段2,...,字段n);
① 创建复合索引优于单值索引，频繁使用的字段建立单值索引；
② 一张表的索引最多不要超过 5 个；
同一时间内只会用到一个索引；
3.3.4 主键索引
设定为主键后数据库会自动创建索引，InnoDB 为聚簇索引。
单独建主键索引：ALTER TABLE 表名 ADD PRIMARY KEY 表名(字段名);
删除主键索引：ALTER TABLE 表名 DROP PRIMARY KEY;
修改主键索引：即先删除主键索引，在建立新的主键索引。
3.3.5 索引的基本语法
3.3.5.1 创建
CEREATE [UNIQUE] INDEX indexName ON tableName(columnName(length))
ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (columnName(length))
如果字段是
char、varchar类型，length 可以小于实际长度；如果是blob、text类型，必须指定 length
indexName一般命名为idx_tableName_columnName
3.3.5.2 删除
DROP INDEX [indexName] ON tableName
3.3.5.3 查看
SHOW INDEX FROM tableName
3.3.5.4 使用 ALTER 命令
添加一个主键，也就意味着这个索引值是唯一的，且不能为 NULL
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
创建一个唯一索引，索引值必须是唯一的，但可以为 NULL 也可以出现多次
ALTER TABLE table_name ADD UNIQUE index_name (column_list)
添加普通索引，索引值可能出现多次
ALTER TABLE table_name INDEX index_name (column_list)
指定索引为 FULLTEXT，用于全文索引
ALTER TABLE table_name ADD FULLTEXT index_name (column_list)
3.3 索引的结构
3.3.1 B Tree 索引
图示说明：
- 浅蓝色--磁盘块；深蓝色--数据项；黄色--指针；
- 如：磁盘块1包含：数据项 17、35和指针 P1、P2、P3；
- P1 表示小于 17 的磁盘块；P2 表示大于 17 小于 35 的磁盘块；P3 表示大于 35 的磁盘块；
- 非叶子节点不存储真实数据，只存储指引搜索方向的数据项，如：数据项 17 和 35 并不存在于数据表中
- 真实的数据存储在叶子节点，即磁盘5-11之间；
查找过程：如查找数据 60
① 首先将磁盘1加载到内存中，产生一次 IO 操作，在内存使用二分查找确定 60 大于 35（在内存中的比对时间很快可以忽略不记），锁定P3 指针；
② 通过 P3 指针找到磁盘4，将磁盘4 加载到内存中，进行第二次磁盘 IO，在内存中使用二分查找确定 60 小于 65，锁定 P1 指针；
③ 通过 P1 指针找磁盘块9，进行第三次磁盘 IO，此时查询结束。
在实际的 MySQL 查询时通常面对的是上百万条数据，如果依次进行遍历会进行大量的磁盘 IO，严重影响性能，而通过三次磁盘 IO 就可以查找到对应的数据项，对于性能的提升是非常显著的。
3.3.2 B+ Tree 索引
B+ Tree 和 B- Tree 的区别：
① B- Tree 的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+ Tree 的非叶子节点中只有关键字和指向下一个节点的索引，记录仅放在叶子节点。
② 在 B- Tree 中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+ Tree 中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且叶子节点中还要再比较关键字。从这个角度看B- Tree 的性能似乎比 B+ Tree 更好，但在实际应用中 B+ Tree 的性能更好。因为 B+ Tree 的非叶子节点不存放实际数据，这样每个节点可以容纳的元素个数比 B- Tree 更多，树高比 B- 树小，这样的好处在于减少磁盘访问次数。尽管 B+Tree 找到一个记录所需的时间比 B- Tree 的次数更多，但是磁盘 IO 的时间相当于成百上千次内存比较的时间，因此实际中 B+ Tree 的性能可能更好，而且 B+ Tree 的叶子节点使用指针连接在一起，方便遍历（例如查看一个目录下的所有文件，一个表的所有记录等），也是很多数据库和文件系统使用 B+ Tree 的缘故。
B+ Tree 比 B- Tree 更适合作为操作系统中文件索引和数据库索引的原因：
① B+ Tree 的磁盘代价更小
B+ Tree 的内部节点并没有指向关键字具体信息的指针。因此其内部节点相比 B- Tree 更小。如果把所有同一内部节点的关键字存放在同以盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存中的需要查找的关键字也就越多，相对的磁盘 IO 次数也就降低了。
② B+ Tree 的查询效率更加稳定
由于非终结点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找必须走一条从根节点到叶子节点的路。所有关键字查询的路径长度相同，导致每个数据的查询效率相当。
3.3.3 聚簇和非聚簇索引
聚集索引并不是一种单独的索引类型，而是一种数据存储方式。 “聚簇” 数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行再磁盘的排列和索引排序保持一致。
聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不需要从多个数据块中提取数据，节省了大量的 IO 操作。
聚簇索引的限制：
由于 MySQL 数据库目前只有 InnoDB 数据存储引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。
由于数据物理存储排列方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引，一般情况下就是该表的主键。
为了充分利用聚簇索引的特性，所以 InnoDB 表的主键列尽量选用有顺序的 i，而不是采用无序的 id，如 uuid 这种。
其他的索引类型：
- Hash 索引
- Full-text 全文索引
- R-Tree 索引
3.4 建立索引的时机
3.4.1 需要建立索引
① 主键自动建立唯一索引；
② 频繁作为查询条件的字段应该建立索引；
③ 查询中排序的字段，排序字段若通过索引访问将大大提高排序的速度；
④ 查询中统计或分组的字段；
创建单列索引还是组合索引？高并发场景下倾向创建组合索引
3.4.2 不需要建立索引
① 表记录太少（MySQL 的性能足够支持300万左右的数据量）
② where 条件里用不到的字段不创建索引；
③ 频繁增删的表或经常更新的字段不适合创建索引；
④ 数据重复且分布平均的表字段；
更新字段的时候不仅仅更新记录还会更新索引，会加重 IO 负担。
注：如果某个数据列包含很多重复的内容，为此创建索引没有太大的实际效果。
索引的选择性：索引列中不同值得数目与表中记录数得比。如：一个表中有 2000 条记录，表的索引有 1980 个不同的值，那么这个索引的选择性就是 1980/2000 = 0.99。索引的选择性越接近1，这个索引的效率就越高。
