MySQL 索引

================================================================================

概述:

 本章将主要讲解MySQL数据库中有关索引的创建和使用,具体内容如下:

  • MySQL索引的作用;

  • MySQL的索引类型

      ·B+ TREE 索引

      ·Hash索引:

  • 索引优点及高性能索引策略:

  • 索引的创建、查看、修改和删除;

  • EXPLAIN来分析索引有效性

================================================================================

MySQL的索引:

定义:

  • 索引:提取索引的创建在的表上字段中的数据,构建出一个独特的数据结构;

作用:加速查询操作;

表中数据子集:

  • 把表中某个或某些字段(WHERE子句中用到的字段)的数据提取出来另存为一个特定数据结构组织的数据;

MySQL的索引类型:B+ TREE,HASH

 1.B+ TREE 索引

B+ TREE:

  • 顺序存储,每一个叶子结点到根结点的距离相同;

  • 左前缀索引适合于范围类型的数据查询;

适用于B+ TREE索引的查询类型:全键值、键值范围或键前缀;

全值匹配:精确匹配某个值;

    eg:WHERE CLOUMN = 'value';

匹配最左前缀:只精确匹配起头的部分;

    eg:WEHRE COLUMN LIKE 'PREFIX%';

匹配范围值:(顺序排列)

    eg:WEHRE Age > 30;

精确匹配某一列,范围匹配另一列;

只用访问索引的查询:覆盖索引;(仅通过索引就能得到最终数据)

    eg:index(Name)

         SELECT Name FROM students WHERE Name LIKE 'L%';

不适用B+ TREE索引的查询类型:

如查询条件不是从最左侧列开始,索引无效;

    eg:index(age,name); WHERE name='Jerry'(无效);WHERE age>30(有效);

不能跳过索引中的某列;

    eg:index(name,age,gender)

          WHERE name LIKE 'j%' and gender='M' (无效,不能跳过阿age字段)

如果查询中的某个列是为范围查询,那么其右侧的列都无法再使用索引优化查询;

    eg:index(name,age,gender)

          WHERE second_name='black' and age > 30;(有效,如果把范围放前面就无效了)

 2.Hash索引:

基于哈希表实现,特别适用于值的精确匹配查询;

适用场景:

  • 只支持等值比较查询,例如=, IN(), <=>

不用场景:

  • 所有非精确值查询;

  • MySQL仅对 memory 存储引擎支持显式的hash索引;也就是说在mysql中只能使用B+ TREE 索引

 3.索引优点及高性能索引策略:

索引优点:

  • 降低需要扫描的数据量,减少了I/O次数;

  • 可以帮助避免排序操作,避免使用临时表;

  • 帮助将随机I/O转为顺序I/O;

高性能索引策略:

在WHERE中独立使用列,尽量避免其参与运算;

左前缀索引:

   索引构建于字段的最左侧的多少个字符,要通过索引选择性来评估;

  • 索引选择性:不重复的索引值和数据表的记录总数的比值;

  • 尽量使用主键来构建索引,因为出现的重复率较小,效率最高;

多列索引:

  • AND连接的多个查询条件更适合使用多列索引,而非多个单键索引;

选择合适的索引列次序:选择性最高的放左侧; 

 4.索引的创建、查看、修改和删除:

创建

语法:

  • CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON tbl_name (index_col_name,...) 索引字段名称

参数:

  • index_col_name:col_name [(length)] [ASC | DESC]

  • {INDEX|KEY}  普通索引创建

  • {FULLTEXT|SPATIAL} 全文索引,空间索引

删除:

  • DROP INDEX index_name ON tbl_name

查看:

  • SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name  [{FROM | IN} db_name] [WHERE expr]

使用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 ,用于全文索引。

 5.EXPLAIN来分析索引有效性:

语法:

  • EXPLAIN [explain_type] SELECT select_options

explain_type:

  • EXTENDED

  • PARTITIONS

输出结果:

id:当前查询语句中,第几个SELECT语句的编号;

复杂的查询的类型主要三种:

  • 简单子查询;

  • 用于FROM中的子查询;

  • 联合查询

注意:

  • 联合查询的分析结果会出现一个额外的匿名临时表;

select_type:查询类型:

简单查询:SIMPLE

复杂查询:

  • 简单子查询:SUBQUERY

  • 用于FROM中的子查询:DERIVED

  • 联合查询中的第一个查询:PRIMARY

  • 联合查询中的第一个查询之后的其它查询:UNION

  • 联合查询生成的临时表:UNION RESULT

table:查询针对的表;

type:关联类型,或称为访问类型,即MySQL如何去查询表中的行

ALL:

  • 全表扫描;

index:

  • 根据索引的顺序进行的全表扫描;但同时如果Extra列出现了"Using index”表示使用了覆盖索引;

range:

  • 有范围限制地根据索引实现范围扫描;扫描位置始于索引中的某一项,结束于另一项;

ref:

  • 根据索引返回的表中匹配到某单个值的所有行(匹配给定值的行不止一个);

eq_ref:

  • 根据索引返回的表中匹配到某单个值的单一行,仅返回一个行,但需要与某个额外的参考值比较,而不是常数;

const,system:

  • 与某个常数比较,且只返回一行;

possiable_keys:

  • 查询中可能会用到的索引;

key:

  • 查询中使用的索引;

key_len:

  • 查询中用到的索引长度;

ref:

  • 在利用key字段所显示的索引完成查询操作时所引用的列或常量值; 

rows:

  • MySQL估计出的为找到所有的目标项而需要读取的行数;

Extra:额外信息

  • Using index:使用了覆盖索引进行的查询;

  • Using where:拿到数据后还要再次进行过滤; 

  • Using temporary:使用了临时表以完成查询;

  • Using filesort:对结果使用了一个外部索引排序;

演示:

 1.在hellodb库上创建索引,并使用EXLLAIN测试是否用到索引;

# 查看当前索引,可以发现只有一个主键索引
MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.04 sec)

# 在Name字段上创建name索引;
MariaDB [hellodb]> CREATE INDEX name ON students(Name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看索引
MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name     |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

# 查询数据
MariaDB [hellodb]> SELECT * FROM students WHERE Name = 'Xi Ren';
+-------+--------+-----+--------+---------+-----------+
| StuID | Name   | Age | Gender | ClassID | TeacherID |
+-------+--------+-----+--------+---------+-----------+
|     7 | Xi Ren |  19 | F      |       3 |      NULL |
+-------+--------+-----+--------+---------+-----------+
1 row in set (0.01 sec)

# 使用EXPLAIN测试可以发现可能用到的字段为name,用到的key为name,可见索引是有效的,只查找了一行就可以找到数据
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name = 'Xi Ren';
+------+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | students | ref  | name          | name | 152     | const |    1 | Using index condition |
+------+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

# 为了测试不加索引的效果,我把刚创建的Name字段的索引删除掉
MariaDB [hellodb]> DROP INDEX name ON students;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.02 sec)

# 再次使用EXPLAIN可以发现,这次可用的键为空,查找了25行才能找到在数据,可见添加索引的功效
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name = 'Xi Ren';
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> CREATE INDEX name ON students(Name);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> CREATE INDEX age ON students(Age);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 在Name,Age字段创建索引,查看如下:
MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name     |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age      |            1 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

# 查询Age>30的可以发现,用到了Age字段的age索引,仅查询了5行就把结果输出来了
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Age > 30;
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | age           | age  | 1       | NULL |    5 | Using index condition |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

# 可以发现是结果是按顺序排列的,不用做全表扫描,直接截取符合条件的行;
MariaDB [hellodb]> SELECT * FROM students WHERE Age > 30;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)

 2.创建多键索引,测试,得出结论:要想使索引生效,查询条件一定要从最左侧开始,不能跳过字段;

# 首先删除原来创建的索引;
MariaDB [hellodb]> DROP INDEX name ON students;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [hellodb]> DROP INDEX age ON students;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建一个多级索引
MariaDB [hellodb]> CREATE INDEX name_age ON students(name,age);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看可以发现,两个字段共同使用一个索引 name_age
MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            2 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.02 sec)

# 现在,基于Name为过滤条件,发现多键索引依然有效,可见索引为左前缀精确匹配;
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name = 'Xi Ren';
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | students | ref  | name_age      | name_age | 152     | const |    1 | Using index condition |
+------+-------------+----------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

# 但是,如果过滤条件为Age的Where子句,发现索引无效,可见索引的次序非常重要,必须要从最左侧开始
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Age > 30;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

# 创建age字段位于最左侧的索引age_name,测试
MariaDB [hellodb]> CREATE INDEX age_name ON students(age,name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 可以发现成功使用索引,可见索引的次序非常重要,查询条件必须要从最左侧开始!!!
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Age > 30;
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | age_name      | age_name | 1       | NULL |    5 | Using index condition |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            2 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age_name |            1 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age_name |            2 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)


 3.要想使索引生效,左前缀一定要做精确匹配,如果为范围匹配,索引是无效的

# 查找条件左侧为精确匹配,然后再是范围匹配,可以看到用到的索引为name_age,过滤出来后再做范围匹配
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name = 'Xi Ren' AND Age > 30;
+------+-------------+----------+-------+-------------------+----------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys     | key      | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+-------------------+----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | name_age,age_name | name_age | 153     | NULL |    1 | Using index condition |
+------+-------------+----------+-------+-------------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

# 查找结果为空,没有符合条件这个数据
MariaDB [hellodb]> SELECT * FROM students WHERE Name = 'Xi Ren' AND Age > 30;
Empty set (0.00 sec)

# 左前缀为范围匹配,发现左侧的索引无效,有效的仅为右侧的查找条件的索引
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%' AND Age > 30;
+------+-------------+----------+-------+-------------------+----------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys     | key      | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+-------------------+----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | name_age,age_name | age_name | 1       | NULL |    5 | Using index condition |
+------+-------------+----------+-------+-------------------+----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

# 删除右侧查找条件的索引
MariaDB [hellodb]> DROP INDEX age_name ON students;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 再次执行,可以得出结论:要想使索引有效,左前缀一定要做精确匹配,如果为范围匹配,索引是无效的
MariaDB [hellodb]> EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%' AND Age > 30;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | name_age      | NULL | NULL    | NULL |   25 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

  4.多表连接构建索引

MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age_name |            1 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age_name |            2 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            2 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.01 sec)

# 查询学生对应的老师信息,就要使用多表连接
MariaDB [hellodb]> SELECT s.name,t.name FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID;
+-------------+---------------+
| name        | name          |
+-------------+---------------+
| Yu Yutong   | Song Jiang    |
| Shi Zhongyu | Miejue Shitai |
| Ding Dian   | Lin Chaoying  |
+-------------+---------------+
3 rows in set (0.00 sec)

# 分析可见,老师的那张表有一个主键索引,学生的TezcherID字段没有索引,所以,一共要查找100行数据
MariaDB [hellodb]> EXPLAIN SELECT s.name,t.name FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|    1 | SIMPLE      | s     | ALL  | NULL          | NULL | NULL    | NULL |   25 |                                                 |
|    1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL | NULL    | NULL |    4 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.00 sec)

# 创建学生TeacherID的索引
MariaDB [hellodb]> CREATE INDEX tid ON students(TeacherID);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 再次使用EXPLAIN分析,可以发现这次紧紧查找了4行数据,即teacher表的4行都直接对应stuents的一行数据
MariaDB [hellodb]> EXPLAIN SELECT s.name,t.name FROM students AS s,teachers AS t WHERE s.TeacherID=t.TID;
+------+-------------+-------+------+---------------+------+---------+---------------+------+-----------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref           | rows | Extra                 |
+------+-------------+-------+------+---------------+------+---------+---------------+------+-----------------------+
|    1 | SIMPLE      | t     | ALL  | PRIMARY       | NULL | NULL    | NULL          |    4 |                       |
|    1 | SIMPLE      | s     | ref  | tid           | tid  | 5       | hellodb.t.TID |    1 | Using index condition |
+------+-------------+-------+------+---------------+------+---------+---------------+------+-----------------------+
2 rows in set (0.00 sec)

 5.覆盖索引和多表联合查找构建索引

MariaDB [hellodb]> SHOW INDEX FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | StuID       | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age_name |            1 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | age_name |            2 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            1 | Name        | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_age |            2 | Age         | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | tid      |            1 | TeacherID   | A         |          25 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.01 sec)

# 最后显示Using index的为覆盖索引,查询数据直接从索引中返回
MariaDB [hellodb]> EXPLAIN SELECT Name FROM students;
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | students | index | NULL          | age_name | 153     | NULL |   25 | Using index |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

# 用到的是主键索引
MariaDB [hellodb]> EXPLAIN SELECT StuID FROM students;
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | index | NULL          | tid  | 5       | NULL |   25 | Using index |
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [hellodb]> EXPLAIN SELECT Age FROM students;
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
|    1 | SIMPLE      | students | index | NULL          | age_name | 153     | NULL |   25 | Using index |
+------+-------------+----------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

# ClassID字段上没有索引,所以扫描全表
MariaDB [hellodb]> EXPLAIN SELECT ClassID FROM students;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |   25 |       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

# =======================================================================================

# 联合查找,可以看到生成了三张表,第三张表为两张表联合生成后的表;因为没有加where子句,所以,
# 扫描的是整个表,但是students因为有age_name索引并且为覆盖索引所以直接从索引中返回数据,
# teacher因为没有相应的索引,所以全表扫描返回数据
MariaDB [hellodb]> EXPLAIN SELECT Name FROM teachers UNION SELECT Name FROM students;
+------+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type  | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
|    1 | PRIMARY      | teachers   | ALL   | NULL          | NULL     | NULL    | NULL |    4 |             |
|    2 | UNION        | students   | index | NULL          | age_name | 153     | NULL |   25 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL     | NULL    | NULL | NULL |             |
+------+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
3 rows in set (0.00 sec)

# 可以发现,teachers表为主键索引,students为age_name的索引,并且为覆盖索引
MariaDB [hellodb]> EXPLAIN SELECT Name FROM teachers WHERE TID < 3 UNION SELECT Name FROM students;
+------+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
| id   | select_type  | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+------+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
|    1 | PRIMARY      | teachers   | range | PRIMARY       | PRIMARY  | 2       | NULL |    2 | Using where |
|    2 | UNION        | students   | index | NULL          | age_name | 153     | NULL |   25 | Using index |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL     | NULL    | NULL | NULL |             |
+------+--------------+------------+-------+---------------+----------+---------+------+------+-------------+
3 rows in set (0.00 sec)

MariaDB [hellodb]> SELECT Name FROM teachers WHERE TID  UNION SELECT Name FROM students;
+---------------+
| Name          |
+---------------+
| Song Jiang    |
| Zhang Sanfeng |
| Miejue Shitai |
| Lin Chaoying  |
| Lin Daiyu     |
| Lu Wushuang   |
| Xue Baochai   |
| Diao Chan     |
| Wen Qingqing  |
| Xi Ren        |
| Yue Lingshan  |
| Ren Yingying  |
| Xiao Qiao     |
| Duan Yu       |
| Xu Zhu        |
| Huang Yueying |
| Shi Potian    |
| Shi Zhongyu   |
| Hua Rong      |
| Ma Chao       |
| Yuan Chengzhi |
| Lin Chong     |
| Yu Yutong     |
| Xu Xian       |
| Ding Dian     |
| Tian Boguang  |
| Shi Qing      |
| Xie Yanke     |
| Sun Dasheng   |
+---------------+
29 rows in set (0.01 sec)

 6.使用WHERE的简单子查询,可见查询类型为PRIMARY和SUBQUERY

MariaDB [hellodb]> EXPLAIN SELECT Name,Age FROM students WHERE Age > (SELECT avg(age) FROM students);
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table    | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | PRIMARY     | students | index | age           | name_age | 153     | NULL |   25 | Using where; Using index |
|    2 | SUBQUERY    | students | index | NULL          | age      | 1       | NULL |   25 | Using index              |
+------+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
2 rows in set (0.01 sec)

 7.const,system:与某个常数比较,且只返回一行;查询级别最高,最有效的查询,主键索引使用较多

MariaDB [hellodb]> explain select * from students where stuid = 3;
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | students | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+------+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)