Mysql高级

shell->MySql

1、Mysql底层的四层构架

1、连接层
比如java层面的JDBC，主要完成一些类似于连接处理，授权认证等，在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程
2、服务层
处理sql语句，对sql进行分析，和优化如确定查询表的顺序，是否利用索引，最后生成相应的执行操作
3、引擎层
存储引擎层，真正的负责了MySql中数据的存储和提取。不同的存储引擎功能不同。常用的MyISAM和InnoDB
4、存储层
数据存储层，主要将数据存储磁盘上，并完成与存储引擎的交互。

2、MyISAM和InnoDB

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表。不适合高并发	行锁，操作时只锁某一行，不对其他行有影响。适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

3、SQL性能

SQL性能分析：

• 性能下降SQL慢
• 执行时间长
• 等待时间长

原因：

• 查询语句写的烂
• 索引失效
• 关联查询太多join（执行等待时间长）
• 服务器调优和各个参数设置（缓冲，线程数等）

SQL执行顺序

在服务层中执行

对于Mysql而言先从FROM后开始执行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d6O5jses-1635784540144)(C:\Users\deku\AppData\Roaming\Typora\typora-user-images\image-20211031145511940.png)]

七种JOIN理论

• 插入数据

mysql> show databases;#查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wzw                |
+--------------------+
2 rows in set (0.01 sec)

mysql> use wzw #进入指定数据库
Database changed
mysql> show table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> show tables;
Empty set (0.00 sec)
# 创建表
mysql> 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`)
    -> )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
Query OK, 0 rows affected (0.03 sec)
# 查看所有表
mysql> show tables;
+---------------+
| Tables_in_wzw |
+---------------+
| tbl_emp       |
+---------------+
1 row in set (0.00 sec)
# 创建表
mysql> 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 CHARACTER SET = utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_wzw |
+---------------+
| tbl_dept      |
| tbl_emp       |
+---------------+
2 rows in set (0.00 sec)
# 插入数据
mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD', 11);
me,locAdd) VALUES('FD', 15);Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR', 12);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK', 13);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS', 14);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD', 15);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('z3', 1);
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);Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('z4', 1);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('z5', 1); 
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('w5', 2);
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('w6', 2);
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('s7', 3);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('s8', 4);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> INSERT INTO tbl_emp(NAME, deptId) VALUES('s9', 51);
Query OK, 1 row affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_wzw |
+---------------+
| tbl_dept      |
| tbl_emp       |
+---------------+
2 rows in set (0.00 sec)

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)

1、Inner join

mysql> show tables;
+---------------+
| Tables_in_wzw |
+---------------+
| tbl_dept      |
| tbl_emp       |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from tb1_dept;
ERROR 1146 (42S02): Table 'wzw.tb1_dept' doesn't exist
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)

mysql> select * from tbl_dept d
    -> inner join tbl_emp e
    -> on d.id=e.deptId;
+----+----------+--------+----+------+--------+
| id | deptName | locAdd | id | name | deptId |
+----+----------+--------+----+------+--------+
|  1 | RD       | 11     |  1 | z3   |      1 |
|  1 | RD       | 11     |  2 | z4   |      1 |
|  1 | RD       | 11     |  3 | z5   |      1 |
|  2 | HR       | 12     |  4 | w5   |      2 |
|  2 | HR       | 12     |  5 | w6   |      2 |
|  3 | MK       | 13     |  6 | s7   |      3 |
|  4 | MIS      | 14     |  7 | s8   |      4 |
+----+----------+--------+----+------+--------+
7 rows in set (0.00 sec)

mysql>

2、left join

mysql> select * from tbl_dept d
    -> left join tbl_emp e
    -> on d.id=e.deptId;
+----+----------+--------+------+------+--------+
| id | deptName | locAdd | id   | name | deptId |
+----+----------+--------+------+------+--------+
|  1 | RD       | 11     |    1 | z3   |      1 |
|  1 | RD       | 11     |    2 | z4   |      1 |
|  1 | RD       | 11     |    3 | z5   |      1 |
|  2 | HR       | 12     |    4 | w5   |      2 |
|  2 | HR       | 12     |    5 | w6   |      2 |
|  3 | MK       | 13     |    6 | s7   |      3 |
|  4 | MIS      | 14     |    7 | s8   |      4 |
|  5 | FD       | 15     | NULL | NULL |   NULL |
+----+----------+--------+------+------+--------+
8 rows in set (0.00 sec)

3、left join 去掉inner join

mysql> select * from tbl_dept d
    -> left join tbl_emp e
    -> on d.id =e.deptId
    -> where e.deptId is null;
+----+----------+--------+------+------+--------+
| id | deptName | locAdd | id   | name | deptId |
+----+----------+--------+------+------+--------+
|  5 | FD       | 15     | NULL | NULL |   NULL |
+----+----------+--------+------+------+--------+
1 row in set (0.00 sec)

4、Right join

mysql> select * from tbl_dept d
    -> right join tbl_emp e
    -> on d.id=e.deptId;
+------+----------+--------+----+------+--------+
| id   | deptName | locAdd | id | name | deptId |
+------+----------+--------+----+------+--------+
|    1 | RD       | 11     |  1 | z3   |      1 |
|    1 | RD       | 11     |  2 | z4   |      1 |
|    1 | RD       | 11     |  3 | z5   |      1 |
|    2 | HR       | 12     |  4 | w5   |      2 |
|    2 | HR       | 12     |  5 | w6   |      2 |
|    3 | MK       | 13     |  6 | s7   |      3 |
|    4 | MIS      | 14     |  7 | s8   |      4 |
| NULL | NULL     | NULL   |  8 | s9   |     51 |
+------+----------+--------+----+------+--------+
8 rows in set (0.00 sec)

5、Right join 去掉inner join

mysql> select * from tbl_dept d
    -> right join tbl_emp e
    -> on d.id =e.deptId
    -> where d.id is null;
+------+----------+--------+----+------+--------+
| id   | deptName | locAdd | id | name | deptId |
+------+----------+--------+----+------+--------+
| NULL | NULL     | NULL   |  8 | s9   |     51 |
+------+----------+--------+----+------+--------+
1 row in set (0.00 sec)

6、Full outer join Mysql不支持；union；

mysql> select * from tbl_dept d
    -> full outer join tbl_emp e
    -> on d.id =e.deptId
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join tbl_emp e
on d.id =e.deptId' at line 2

变通：union:合并加去重

mysql> select * from tbl_dept d
    -> right join tbl_emp e
    -> on d.id =e.deptId
    -> union
    -> select * from tbl_dept d
    -> left join tbl_emp e
    -> on d.id =e.deptId;
+------+----------+--------+------+------+--------+
| id   | deptName | locAdd | id   | name | deptId |
+------+----------+--------+------+------+--------+
|    1 | RD       | 11     |    1 | z3   |      1 |
|    1 | RD       | 11     |    2 | z4   |      1 |
|    1 | RD       | 11     |    3 | z5   |      1 |
|    2 | HR       | 12     |    4 | w5   |      2 |
|    2 | HR       | 12     |    5 | w6   |      2 |
|    3 | MK       | 13     |    6 | s7   |      3 |
|    4 | MIS      | 14     |    7 | s8   |      4 |
| NULL | NULL     | NULL   |    8 | s9   |     51 |
|    5 | FD       | 15     | NULL | NULL |   NULL |
+------+----------+--------+------+------+--------+
9 rows in set (0.00 sec)

7、查询A，B各自的独有

mysql> select * from tbl_dept d                                                                                          
    -> left join tbl_emp e                                                                                               
    -> on d.id =e.deptId
    -> where e.deptId is null
    -> union 
    -> select * from tbl_dept d
    -> right join tbl_emp e
    -> on d.id =e.deptId
    -> where d.id is null;
+------+----------+--------+------+------+--------+
| id   | deptName | locAdd | id   | name | deptId |
+------+----------+--------+------+------+--------+
|    5 | FD       | 15     | NULL | NULL |   NULL |
| NULL | NULL     | NULL   |    8 | s9   |     51 |
+------+----------+--------+------+------+--------+
2 rows in set (0.00 sec)

4、索引(index)是什么

• 索引（index）：是帮助mysql高效获取数据的数据结构

• 索引就是数据结构

• 可以简单的理解为排好序的快速查找数据结构

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

• 索引以索引文件的形式存储在磁盘上

一般我们常用的索引，都是B树结构组织的索引

其中聚集索引，次要索引，覆盖索引，前缀索引，唯一索引都是默认的B+树索引

除B+树这种索引之外，还有一种哈希索引（hashIndex）等

优势：

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的成本

劣势

• 实际索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，是要占用空间的
• 虽然索引大大提高了检索的效率，缺减少了更新表的的速度。 因为更新表时，Mysql不仅要保存数据，还要保存以下索引文件每次更新添加了索引列的字段

1、索引分类

• 单值索引

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

• 唯一索引

索引列的值必须唯一，但允许有空值（比如说银行卡号）

• 复合索引

即一个索引包含多个列

2、基本语法

indexName 索引名

myTable 表名

columnname(length) 字段

• 创建

CREATE [union] INDEX indexName ON myTable(columnname(length));

ALTER mytable ADD [union] INDEX [indexName] on (columnname(length))

• 删除

DROP INDEX [indexName] ON mytable;

• 查看

SHOW INDEX FROM table_ name;

3、索引结构

• BTree索引

检索原理：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LqNrgrU5-1635784540148)(C:\Users\deku\AppData\Roaming\Typora\typora-user-images\image-20211031170320984.png)]

• Hash索引
• full-text全文索引
• R-Tree索引

4、哪些情况下需要创建索引

• 主键 自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引
• where条件里用不到的字段不创建索引
• 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
• 查询中统计或者分组字段（count，group by）

5、哪些情况下不创建索引

• 表记录太少
• 经常增删改的表
• 数据重复且平均分布的表字段

6、性能分析

1.MySql Query Optimizer： mysql服务层中的性能优化器

• mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求QUERY提供他认为最优的执行计划
• 当客户端向MySql请求一条Query，命令解析器模块完成请求分类，区别出SELECT并转发给MySql Query Optimizer时，MySql Query Optimizer首先会对整条Query进行优化。

2、MySql常见瓶颈：

• CPU ：CPU 饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top，free，iostat，vmstat，来查看系统的性能状态

3、Explain

5、Explain

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

mysql> explain select * from tbl_emp;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tbl_emp | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from tbl_dept d
    -> right join tbl_emp e
    -> on d.id =e.deptId;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | e     | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL                                               |
|  1 | SIMPLE      | d     | ALL  | PRIMARY       | NULL | NULL    | NULL |    5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

1、id

表的读取和加载顺序

• select查询的一个序列号，包含了一组数字，表示查询中执行select字句或操作表的顺序
• id值有三种情况
  • id相同，执行顺序由上至下
  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  • id相同不同，同时存在：id相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

2、select_type和table

常见值：主要用来区别，普通查询，联合查询，子查询等的复杂查询

• SIMPLE：简单的 select 查询，查询中不包含子查询和 UNION
• PRIMARY：查询若包含人和复杂的子部分，最外层的部分则被标记为 PRIMARY
• SUBQUERY：在 SELECT 或 WHERE列表中包含了子查询
• DERIUDE：在FROM列表中包含的子查询被标记为DERIUDE（衍生），mysql会递归这些子查询，把结果放在临时表里
• UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 DERIUDE
• UNION RESULT：从union表获取结果的SELECT

mysql> explain select * from tbl_dept d left join tbl_emp e  on d.id =e.deptId where e.deptId is null 
    -> union
    -> select * from tbl_dept d right join tbl_emp e on d.id =e.deptId  where d.id is null; 
+----+--------------+------------+------+---------------+------------+---------+----------+------+----------------------------------------------------------------+
| id | select_type  | table      | type | possible_keys | key        | key_len | ref      | rows | Extra                                                          |
+----+--------------+------------+------+---------------+------------+---------+----------+------+----------------------------------------------------------------+
|  1 | PRIMARY      | d          | ALL  | NULL          | NULL       | NULL    | NULL     |    5 | NULL                                                           |
|  1 | PRIMARY      | e          | ref  | fk_dept_id    | fk_dept_id | 5       | wzw.d.id |    1 | Using where                                                    |
|  2 | UNION        | e          | ALL  | NULL          | NULL       | NULL    | NULL     |    8 | NULL                                                           |
|  2 | UNION        | d          | ALL  | PRIMARY       | NULL       | NULL    | NULL     |    5 | Using where; Not exists; Using join buffer (Block Nested Loop) |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL       | NULL    | NULL     | NULL | Using temporary                                                |
+----+--------------+------------+------+---------------+------------+---------+----------+------+----------------------------------------------------------------+
5 rows in set (0.07 sec)

• table：就是表示当前执行属于哪张表

3、type

访问类型排列

显示查询使用了何种类型，

从最好到最差依次是：

system>const>eq_ref>ref>range>index>ALL

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略
• const：表示通过索引一次就找到了，const 用于比较primary key或者unique索引。因为只匹配一行数据，所以很快

mysql> explain select * from tbl_emp where id=1;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | tbl_emp | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

mysql> explain select * from tbl_emp,tbl_dept where tbl_emp.id=tbl_dept.id;
+----+-------------+----------+--------+---------------+---------+---------+-----------------+------+-------+
| id | select_type | table    | type   | possible_keys | key     | key_len | ref             | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+-----------------+------+-------+
|  1 | SIMPLE      | tbl_dept | ALL    | PRIMARY       | NULL    | NULL    | NULL            |    5 | NULL  |
|  1 | SIMPLE      | tbl_emp  | eq_ref | PRIMARY       | PRIMARY | 4       | wzw.tbl_dept.id |    1 | NULL  |
+----+-------------+----------+--------+---------------+---------+---------+-----------------+------+-------+
2 rows in set (0.00 sec)

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
• range：只检索给定范围的行，使用一个索引来选择行。key列显示了使用那个索引。一般就是在你的where语句中出现了between、<、>、in的查询。这种范围扫描要比全表扫描要好，因为他只需要开始与索引的某一个点，而结束于另一个点，不用扫描全部索引
• index： Full Index Scan，index与All都是读全表，区别是index类型之遍历索引数，总的来说index是从索引中读取的，而All是从硬盘中读取的

mysql> explain select id from tbl_emp;
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_emp | index | NULL          | fk_dept_id | 5       | NULL |    8 | Using index |
+----+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

• ALL：就是SELECT * 查询所有数据

一般来说，得保证查询至少达到 range 级别

4、possible_keys和key和key_len

• possible_keys：

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

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

• key：表示实际上使用到的索引，

• key_len：

• rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

表示索引中使用的字节数，可通过该列计算查询中使用索引的长度。在不损失精确度的情况下，长度越短越好

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

5、Extra

• Using filesort
• Using temporary
• USING index
• Using where
• using join buffer 使用了连接缓存
• impossible where
• select tables optimized away
• distainct

6、索引分析

单表

• 建表

mysql> 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` VARBINARY(255) NOT NULL,
    -> `content` TEXT NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_wzw |
+---------------+
| article       |
| tbl_dept      |
| tbl_emp       |
+---------------+
3 rows in set (0.01 sec)

mysql> INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
    -> (1,1,1,1,'1','1'),
    -> (2,2,2,2,'2','2'),
    -> (3,3,3,3,'3','3'),
    -> (1,1,1,3,'3','3'),
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

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 |         3 |           3 |     3 |        3 | 3     | 3       |
|  4 |         1 |           1 |     1 |        3 | 3     | 3       |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)

mysql> 

案例：

1、查询 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 |
+----+-----------+
|  4 |         1 |
+----+-----------+
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   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
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         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

#开始优化-建索引：category_id,comments,views
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         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

# 查看效果：range，但是还是有Using filesort的问题（原因:comments >1 大于号出现问题）
mysql> explain SELECT id,author_id FROM article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
         type: range
possible_keys: idx_article_ccv
          key: idx_article_ccv
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using index condition; Using filesort
1 row in set (0.00 sec)
# >号变为= 号 Using filesort消失 type变为ref（找到问题）
# 原因：大于号表示的范围会导致索引失效
mysql> explain SELECT id,author_id FROM article WHERE category_id=1 AND comments =1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
         type: ref
possible_keys: idx_article_ccv
          key: idx_article_ccv
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

# 不改变题意，另寻思路，删除索引
mysql>  DROP INDEX idx_article_ccv ON article；
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 重建索引，去掉commits字段上的索引
mysql> create index idx_article_cv on article(category_id,views);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 完美，检索加排序都用上了索引
mysql> explain SELECT id,author_id FROM article WHERE category_id=1 AND comments >1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
         type: ref
possible_keys: idx_article_cv
          key: idx_article_cv
      key_len: 4
          ref: const
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

两表

• 建表：记住左连接索引加右表，又连接索引加左表

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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)));

• 分析性能，问题来了，我们应该在哪张表上加索引呢

# type=All，出现了 Using join buffer (Block Nested Loop)，性能太差
mysql> explain select * from class left join book on class.card = book.card\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set (0.00 sec)

#第一种情况，在book表Card字段上上加索引 
mysql> create index Y on book(card);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 我们可以看到Using join buffer (Block Nested Loop)消失了，
# book表的type=ref，但是class的type还是等于all
mysql> explain select * from class left join book on class.card = book.card\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: class
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ref
possible_keys: Y
          key: Y
      key_len: 4
          ref: wzw.class.card
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

# 第二种情况，在class上加索引
 mysql> drop index Y on book;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index Y on class(card);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
# class的type变为index，没有ref效果好
mysql> explain select * from class left join book on class.card = book.card\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: class
         type: index
possible_keys: NULL
          key: Y
      key_len: 4
          ref: NULL
         rows: 20
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set (0.00 sec)

对于左连接：

• 在左表和右表的连接字段上都不建立索引：左右表都是全表扫描，查询效率最低；
  仅在左表的连接字段上建立索引：左表使用了覆盖索引扫描，扫描行数没变，查询效率得到优化；b表使用了全表扫描，且扫描行数没变，查询效率未得到优化；
• 仅在右表连接字段上建索引： 左表表使用了全表扫描，扫描行数没变，查询效率未得到优化；右表使用了非唯一性索引单值扫描ref，扫描行数减少，查询晓得得到优化；
• 在左表和右表的连接字段上都建索引后： 左表使用了覆盖索引扫描，扫描行数没变，查询效率得到优化；右表使用了非唯一性索引单值扫描ref，扫描行数减少，查询效率得到优化；

对于内连接

• 在左表和右表的连接字段上都不建立索引：左右表都是全表扫描，查询效率最低；
  仅在左表的连接字段上建立索引： 左表使用了覆盖索引扫描，扫描行数减少，查询效率得到优化；右表使用了全表扫描，扫描行数没变，查询效率没有得到优化；
• 仅在右表连接字段上建索引： 左表使用了全表扫描，扫描行数没变，查询效率未优化； 右表使用了非唯一性索引单值扫描，扫描行数减少，查询效率得到优化；
• 在左表a和右表b的连接字段上都建索引后：左表使用了覆盖索引，扫描行数不变，查询效率得到优化；右表使用了非唯一性索引扫描，扫描行数减少，查询效率得到优化；

三表

• 建表

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)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)));

# 在book，phone上建索引
mysql> create index Y on phone(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 三表查询
mysql> select * from class
    -> left join book 
    -> on class.card=book.card
    -> left join phone 
    -> on book.card=phone.card;
# 查看性能
mysql> explain select * from class inner join book on class.card=book.card left join phone on book.card=phone.card;
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref            | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL           |   20 | NULL        |
|  1 | SIMPLE      | book  | ref  | M             | M    | 4       | wzw.class.card |    1 | Using index |
|  1 | SIMPLE      | phone | ref  | Y             | Y    | 4       | wzw.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+
3 rows in set (0.00 sec)

结论

join语句的优化

• 尽可能减少Join语句中NestedLoop的循环总次数：“永远用小的结果集驱动大的结果集”。
• 优先优化NestedLoop的内层循环
• 保证Join语句被驱动表上的join条件字段已经被索引
• 当无法保证被驱动表的join条件字段被索引且内存资源充足的下，不要太吝啬JoinBuffer的设置

7、索引优化（重点）

• 全值匹配我最爱
• 最佳左前缀法则
• 不在索引列上做任何操作(计算、函数、（自动or手动）类型转换)
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引，（只访问索引的查询(索引列和查询)），减少 select *
• mysql 在使用不等于 (!= 或者<>) 的时候无法使用索引会导致全表扫描
• is null 和 is not null 也无法使用索引
• like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描
• 字符串不加单引号索引失效
• 少用or，用它来连接时会索引失效

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

#建立索引
mysql> ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#mysql 在使用不等于 (!= 或者<>) 的时候无法使用索引会导致全表扫描
mysql> explain select * from staffs where name='z3' and age<>23 and pos='dev';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

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

```shell
#建立索引
mysql> ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#mysql 在使用不等于 (!= 或者<>) 的时候无法使用索引会导致全表扫描
mysql> explain select * from staffs where name='z3' and age<>23 and pos='dev';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)