大数据技术之_29_MySQL 高级面试重点串讲_01_存储引擎 + Join 查询 + 索引与数据处理 + 查看执行计划 -- Explain + 索引失效(应该避免) + 批量数据脚本

第1章存储引擎1.1 Mysql 逻辑架构介绍1.2 查看命令1.3 MyISAM 和 InnoDB 的对比1.4 阿里巴巴、淘宝用哪个第2章 Join 查询2.1 SQL 执行顺序(一般情况下)2.1.1 手写顺序2.1.2 机读顺序2.1.3 总结2.2 建表 SQL2.3 7 种 JOIN 图2.4 7 种 JOIN 实操第3章索引与数据处理3.1 索引是什么3.1.1 索引的优势3.1.2 索引的劣势3.2 索引分类3.3 查看执行计划 -- Explain3.3.1 Explain 是什么3.3.2 Explain 能干嘛3.3.3 Explain 怎么玩3.3.4 Explain 的各字段解释3.4 小案例3.5 索引失效(应该避免)3.5.1 全值匹配我最爱（数量和顺序全匹配）3.5.2 最佳左前缀法则3.5.3 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描3.5.4 存储引擎不能使用索引中范围条件右边的列3.5.5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，select * 尽量少用3.5.6 mysql 在使用不等于(!= 或者<>)的时候，可能无法使用索引会导致全表扫描3.5.7 注意 null/not null 对索引的可能影响，要看该字段是否定义为空3.5.8 like 以通配符开头('%abc…')，mysql 索引失效会变成全表扫描的操作3.5.9 字符串不加单引号索引失效3.5.10 少用 or，用它来连接时，索引可能会失效3.5.11 小总结3.6 批量数据脚本3.6.1 建表3.6.2 设置一个参数 log_bin_trust_function_creators3.6.3 创建函数，保证每条数据都不同3.6.4 创建存储过程3.6.5 调用存储过程

天上飞的理念必有落地的实现。

第1章存储引擎

1.1 Mysql 逻辑架构介绍

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

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

2、服务层
第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

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

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

1.2 查看命令

如何用命令查看

#登录客户端
[atguigu@hadoop102 ~]$ mysql -uroot -p

#看你的 mysql 中的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| commerce           |
| company            |
| db_telecom         |
| metastore          |
| mysql              |
| oozie              |
| performance_schema |
| rdd                |
| report             |
| test               |
+--------------------+

#看你的 mysql 的版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.24    |
+-----------+

#创建数据库
mysql> create database db0508;
Query OK, 1 row affected (0.00 sec)

#使用数据库
mysql> use db0508;
Database changed

#看你的 mysql 的版本
#看你的 mysql 现在已提供什么存储引擎：
mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

#看你的 mysql 当前默认的存储引擎：
mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+

1.3 MyISAM 和 InnoDB 的对比

1.4 阿里巴巴、淘宝用哪个

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

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

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

4、AliSql + AliRedis

第2章 Join 查询

2.1 SQL 执行顺序(一般情况下)

2.1.1 手写顺序

2.1.2 机读顺序

2.1.3 总结

2.2 建表 SQL

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

2.3 7 种 JOIN 图

2.4 7 种 JOIN 实操

0：原始数据

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;
+----+----------+--------+
| 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：A、B 两表共有

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：A、B 两表共有 +A 的独有

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   |
+----+------+--------+------+----------+--------+

3：A、B 两表共有 +B 的独有

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：A 的独有

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：B 的独有

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

6：AB 全有

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：A 的独有 +B 的独有

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)

小结：

1：A、B 两表共有
 select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

2：A、B 两表共有 +A 的独有
 select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

3：A、B 两表共有 +B 的独有
 select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;

4：A 的独有 
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null; 

5：B 的独有
 select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null; #B的独有

6：AB 全有
#MySQL Full Join 的实现：因为 MySQL 不支持 FULL JOIN，下面是替代方法
#left join + union(可去除重复数据)+ right join
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

7：A 的独有 +B 的独有
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;

第3章索引与数据处理

3.1 索引是什么

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

索引的目的在于提高查询效率，可以类比字典，如果要查 “mysql” 这个单词，我们肯定需要定位到 m 字母，然后从下往下找到 y 字母，再找到剩下的 sql。如果没有索引，那么你可能需要 a----z，如果我想找到 Java 开头的单词呢？或者 Oracle 开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

你可以简单理解为“排好序的快速查找数据结构”。图书馆 -- 图书管理员（索引） -- 书

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

下图就是一种可能的索引方式示例：

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

索引的数据结构是 BTree。（b-tree 就是 BTree、b+tree、b*tree）

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。我们平常所说的索引，如果没有特别指明，都是指 B 树（多路搜索树，并不一定是二叉的）结构组织的索引。
BTREE，B 树（Balance Tree 多路平衡查找树）。

3.1.1 索引的优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本。（如何用 Linux 命令查看 IO：iostat -d 2 3 每两秒钟取一次，共取三次）

通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗。（如何用 Linux 命令查看 CPU：vmstat -n 2 3 每两秒钟取一次，共取三次）

数据库 sql 慢，表现在两个硬件方面：检索慢(IO 多)、排序慢(CPU 慢)。

[atguigu@hadoop102 ~]$ iostat -d 2 3
Linux 2.6.32-642.el6.x86_64 (hadoop102)     2019年06月11日     _x86_64_    (4 CPU)

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               1.88        65.06         5.83     709528      63594

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               0.00         0.00         0.00          0          0

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               0.00         0.00         0.00          0          0

[atguigu@hadoop102 ~]$ vmstat -n 2 3
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 2851536 182004 171732    0    0     8     1   16   24  0  0 100  0  0    
 0  0      0 2851520 182004 171760    0    0     0     0   72   99  0  0 100  0  0    
 0  0      0 2851560 182004 171760    0    0     0     0   64   96  0  0 100  0  0

3.1.2 索引的劣势

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

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

索引只是提高效率的一个因素，如果你的 MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

3.2 索引分类

小示例

3.3 查看执行计划 -- Explain

3.3.1 Explain 是什么

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

官网 http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

3.3.2 Explain 能干嘛

3.3.3 Explain 怎么玩

3.3.4 Explain 的各字段解释

1、id（重要）
id 是 select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。

第一种情况：id 相同，执行顺序由上至下

第二种情况：id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

第三种情况：id 相同又不同，同时存在。id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行。衍生 = DERIVED

2、select_type

查询类型详解：

3、table
显示这一行的数据是关于哪张表的。

4、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。如果出现了 type 出现了 ALL，需要进行路径的寻址、sql 的优化和索引的建立。

显示查询使用了何种类型
从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL

5、possible_keys
6、key

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

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

难点：如何计算的？
key_len：数据库版本 + 存储引擎 + 定义的字段类型 + 定义的字段是否可以 null + 字符集编码
都会影响到 key_len 的使用和计算参考值。
例如：5.6.24 + InnoDB + char(4) + null + utf-8

数据类型长度
数值类型

日期和时间类型
datetime 类型在 5.6 中字段长度是 5 个字节，
datetime 类型在 5.5 中字段长度是 8 个字节。

字符串类型

计算方法估值表
key_len 表示索引使用的字节数，根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。

char 和 varchar 跟字符编码也有密切的联系,

latin1 占用 1 个字节，gbk 占用 2 个字节，utf8 占用 3 个字节。（不同字符编码占用的存储空间不同）

案例演示
字符型
(1) 字符型：索引字段为 char 类型 + 不可为 Null 时

(2) 字符型：索引字段为 char 类型 + 允许为 Null 时

(3) 字符型：索引字段为 varchar 类型 + 不可为 Null 时

(4) 字符型：索引字段为 varchar 类型 + 允许为 Null 时

整数/浮点数/时间类型的索引长度
NOT NULL=字段本身的字段长度
NULL=字段本身的字段长度 + 1(因为需要有是否为空的标记，这个标记需要占用 1 个字节)
注意：datetime 类型在 5.6 中字段长度是 5 个字节，datetime 类型在 5.5 中字段长度是8个字节。

小总结
变长字段需要额外的 2 个字节（VARCHAR 值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过 255，则使用两个字节)，所以 VARCAHR 索引长度计算时候要加 2），固定长度字段不需要额外的字节。

而 NULL 都需要 1 个字节的额外空间，所以索引字段最好不要为 NULL，因为 NULL 让统计更加复杂并且需要额外的存储空间。

所以，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

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

由 key_len 可知 t1 表的 idx_col1_col2 被充分使用，col1 匹配 t2 表的 col1，col2 匹配了一个常量，即 'ac'。
查询中与其它表关联的字段，外键关系建立索引。

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

10、Extra
包含不适合在其他列中显示但十分重要的额外信息。
(1) Using filesort
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
即你的 order by 的顺序没有复用索引的建立顺序，没有保持一致。
MySQL 中无法利用索引完成的排序操作称为 “文件排序”。
查询中排序的字段 和 排序中的排序字段 若通过索引去访问将大大提高排序速度。

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

(3) Using index
表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现 Using where，表明索引被用来执行索引键值的查找。
如果没有同时出现 Using where，表明索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index），一说为索引覆盖。
理解方式一：就是 select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
注意：
如果要使用覆盖索引，一定要注意 select 列表中只取出需要的列，不可用 select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。（一般建索引的字段的个数是 3 到 5 个。）

我在公司做过 MySQL 的性能分析和索引优化。

(4) Using where
表示使用了 where 过滤。

(5) Using join buffer
表示使用了连接缓存。

(6) Impossible where
where 子句的值总是 false，不能用来获取任何元组。

3.4 小案例

3.5 索引失效(应该避免)

建表 SQL

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',24,'dev',NOW());
INSERT INTO staffs (NAME,age,pos,add_time) VALUES ('3000',26,'dev',NOW());
SELECT * FROM staffs;

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

3.5.1 全值匹配我最爱（数量和顺序全匹配）

3.5.2 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

3.5.3 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

即等号左边不要有计算、函数、(自动or手动)类型转换。

3.5.4 存储引擎不能使用索引中范围条件右边的列

**3.5.5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，select * 尽量少用**

3.5.6 mysql 在使用不等于(!= 或者<>)的时候，可能无法使用索引会导致全表扫描

3.5.7 注意 null/not null 对索引的可能影响，要看该字段是否定义为空

情况一：

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());
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);
ALTER TABLE staffs ADD INDEX idx_staffs_name (NAME);

EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;

情况二：

测试脚本如下：

CREATE TABLE staffs2 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24),
  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 staffs2 (NAME, age, pos, add_time) VALUES ('z3', 22, 'manager', NOW());
ALTER TABLE staffs2 ADD INDEX idx_staffs2_nameAgePos (NAME, age, pos);
ALTER TABLE staffs2 ADD INDEX idx_staffs2_name (NAME);

EXPLAIN SELECT * FROM staffs2 WHERE NAME IS NULL;
EXPLAIN SELECT * FROM staffs2 WHERE NAME IS NOT NULL;

3.5.8 like 以通配符开头('%abc…')，mysql 索引失效会变成全表扫描的操作

问题：解决 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;

#drop table tbl_user
INSERT INTO tbl_user (NAME,age,email) VALUES ('1aa1',21,'b@163.com');
INSERT INTO tbl_user (NAME,age,email) VALUES ('2aa2',222,'a@163.com');
INSERT INTO tbl_user (NAME,age,email) VALUES ('3aa3',265,'c@163.com');
INSERT INTO tbl_user (NAME,age,email) VALUES ('4aa4',21,'d@163.com');
INSERT INTO tbl_user (NAME,age,email) VALUES ('aa',121,'e@163.com');

#before index
EXPLAIN SELECT NAME, age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT id, NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, NAME, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME, age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, NAME, age, email FROM tbl_user WHERE NAME LIKE '%aa%';

#create index
CREATE INDEX idx_user_nameAge ON tbl_user (NAME,age);

#delete index
DROP INDEX idx_user_nameAge ON tbl_user;

#after index
EXPLAIN SELECT * FROM tbl_user WHERE NAME =800 AND age = 33;

3.5.9 字符串不加单引号索引失效

3.5.10 少用 or，用它来连接时，索引可能会失效

如果数据量小，可以用 or

如果数据量巨大，or 使用 UNION ALL 进行进行替换

3.5.11 小总结

后面还有：挖掘技术看蓝翔，IT/DT技术学尚硅谷！

3.6 批量数据脚本

往表里插入 1000W 数据

3.6.1 建表

测试脚本如下：

# 新建库
create database bigData0508;
use bigData0508;

#1 建表 dept
CREATE TABLE dept(  
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
  dname VARCHAR(20) NOT NULL DEFAULT "",  
  loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=GBK;  

#2 建表 emp
CREATE TABLE emp (  
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
  empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
  ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
  job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
  mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
  hiredate DATE NOT NULL,/*入职时间*/  
  sal DECIMAL(7,2) NOT NULL,/*薪水*/  
  comm DECIMAL(7,2) NOT NULL,/*红利*/  
  deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=GBK;

3.6.2 设置一个参数 log_bin_trust_function_creators

为什么要设置这个参数？
答：当开启二进制日志后(可以执行 show variables like 'log_bin_trust_function_creators' 查看是否开启)，如果变量 log_bin_trust_function_creators 为 OFF，那么创建或修改存储函数就会报如下的错误：

“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”

【解决方法】

# 临时设置的方式：
show variables like 'log_bin_trust_function_creators'; 
set global log_bin_trust_function_creators=1;

# 这样添加了参数以后，如果 mysqld 重启，上述参数又会消失，永久设置的方式：
windows 下 my.ini[mysqld] 加上 log_bin_trust_function_creators=1 
linux下 /etc/my.cnf 下 my.cnf[mysqld] 加上 log_bin_trust_function_creators=1

官网解释如下：

3.6.3 创建函数，保证每条数据都不同

(1) 随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET return_str =CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52), 1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END $$

#假如要删除
#DROP function rand_string;

(2) 随机产生部门编号

#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
  DECLARE i INT DEFAULT 0;  
  SET i = FLOOR(100 + RAND()*10);  
  RETURN i;  
END $$


#假如要删除
#DROP function rand_num;

3.6.4 创建存储过程

(1) 创建往 emp 表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10), IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把 autocommit 设置成 0  
  SET autocommit = 0;    
    REPEAT  
      SET i = i + 1;  
      INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START+i), rand_string(6), 'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());  
      UNTIL i = max_num  
    END REPEAT;  
  COMMIT;  
END $$

#删除
DELIMITER ;
DROP PROCEDURE insert_emp;

(2) 创建往 dept 表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
  SET autocommit = 0;    
    REPEAT  
      SET i = i + 1;  
      INSERT INTO dept (deptno, dname, loc) VALUES ((START+i), rand_string(10), rand_string(8));  
      UNTIL i = max_num  
    END REPEAT;  
  COMMIT;  
END $$ 

#删除
DELIMITER ;
DROP PROCEDURE insert_dept;

3.6.5 调用存储过程

执行存储过程，往 dept 表添加 10 条数据

DELIMITER ;
CALL insert_dept(100, 10);

查看
select * from dept limit 10;

执行存储过程，往 emp 表添加 50 万条数据

DELIMITER ;
CALL insert_emp(100001, 500000); 

查看
select * from emp limit 200;

posted @ 2019-06-12 21:27 黑泽君阅读(626) 评论(0) 编辑收藏举报

刷新页面返回顶部

Bruce