IT评测·应用市场-qidao123.com

标题: MySQL 之INDEX 索引（Index Index of MySQL） [打印本页]

---

作者: 小秦哥    时间: 2025-3-10 14:46
标题: MySQL 之INDEX 索引（Index Index of MySQL）
MySQL 之INDEX 索引

1.4 INDEX 索引
1.4.1 索引先容

索引：是排序的快速查找的特别数据布局，定义作为查找条件的字段上，又称为键 key，索引通过存储引擎实现。
长处

• 大大加快数据的检索速率;
  

• 创建唯一性索引，保证数据库表中每一行数据的唯一性;
  

• 加速表和表之间的毗连;
  

• 在使用分组和排序子句进行数据检索时，可以明显减少查询中分组和排序的时间。
  缺点
  
• 索引必要占物理空间。
  
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，低沉了数据的维护速率。
  索引类型
  
• B+ TREE、HASH、R TREE、FULL TEXT
  
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
  

• 主键索引、二级（辅助）索引
  

• 稠密索引、稀疏索引：是否索引了每一个数据项
  

• 简朴索引、组合索引: 是否是多个字段的索引
  

• 左前缀索引：取前面的字符做索引
  

• 覆盖索引：从索引中即可取出要查询的数据，性能高
  

1.4.2 索引布局

1. 参考链接：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

复制代码

树的简介
树跟数组、链表、堆栈一样，是一种数据布局。它由有限个节点，构成具有层次关系的集合。由于它看起来像一棵树，以是得其名。
树是包含n(n为整数，大于0)个结点， n-1条边的有穷集，它有以下特点

• 每个结点大概无子结点大概只有有限个子结点
  

• 有一个特别的结点，它没有父结点，称为根结点
  

• 每一个非根节点有且只有一个父节点
  

• 树里面没有环路
  概念相关
  
• 结点的度：一个结点含有的子结点个数称为该结点的度
  
• 树的度：一棵树中，最大结点的度称为树的度
  

• 父结点：若一个结点含有子结点，则这个结点称为其子结点的父结点
  

• 深度：对于任意结点N，N的深度为从根到n的唯一起径长，根结点的深度为0
  

• 高度：对于任意结点N，N的高度为从n到一片树叶的最长路径长，所有树叶的高度为0
  树的分类
  

按照有序性，可以分为有序树和无序树：

• 无序树：树中任意节点的子结点之间没有顺序关系
  

• 有序树：树中任意节点的子结点之间有顺序关系
  按照节点包含子树个数，可以分为B树和二叉树，二叉树可以分为以下几种：
  
• 二叉树：每个节点最多含有两个子树的树称为二叉树
  
• 二叉查找树：首先它是一颗二叉树，若左子树不空，则左子树上所有结点的值均小于它的根结点的值；若右子树不空，则右子树上所有结点的值均大于它的根结点的值，左、右子树也分别为二叉排序树
  

• 满二叉树：叶节点除外的所有节点均含有两个子树的树被称为满二叉树
  

• 完全二叉树：如果一颗二叉树除去末了一层节点为满二叉树，且末了一层的结点依次从左到右分布
  

• 霍夫曼树：带权路径最短的二叉树
  

• 红黑树：红黑树是一种特别的二叉查找树，每个节点都是黑色大概红色，根节点、叶子节点是黑色。如果一个节点是红色的，则它的子节点必须是黑色的
  

• 平衡二叉树(AVL)：一 棵空树或它的左右两个子树的高度差的绝对值不超过1，而且左右两个子树都是一棵平衡二叉树
  二叉树
  

二叉树（binary tree）是指树中节点的度不大于2的有序树，它是一种最简朴且最紧张的树。二叉树的递归定义为：二叉树是一棵空树，大概是一棵由一个根节点和两棵互不相交的，分别称作根的左子树和右子树构成的非空树；左子树和右子树又同样都是二叉树。

1. 参考链接：https://www.cs.usfca.edu/~galles/visualization/BST.html

复制代码

红黑树
红黑树（Red Black Tree） 是一种自平衡二叉查找树，是在进行插入和删除操纵时通过特定操纵保持二叉查找树的平衡，从而得到较高的查找性能。
红黑树特点

• 根节点是黑色的，叶节点是不存储数据的黑色空节点
  

• 任何相邻的两个节点不能同时为红色，红色节点被黑色节点隔开，红色节点的子节点是黑色的
  

• 任意节点到其可到达的叶节点间包含相同数目的黑色节点，保证任何路径相差不会超出2倍，从而实现基本平衡
  

1. 参考链接：https://www.cs.usfca.edu/~galles/visualization/RedBlack.html

复制代码

B-树
B-树，读作B树，中间的横线是连字符，不是减号，B树将一个节点的巨细设置为每个数据页(Page，也可以称为块，block)的巨细，一般是16KB，而且，B树中是将数据和索引放在一起的，以减少IO次数，加快查询速率，一个节点能放多少数据，通常取决于一条数据占用的空间巨细。

1. <strong>mysql> show variables like '%page_size%';+------------------+-------+| Variable_name    | Value |+------------------+-------+| innodb_page_size | 16384 || large_page_size  | 0     |+------------------+-------+2 rows in set (0.00 sec)</strong>

复制代码

B+树索引

B+树是B-树的变体，也是一棵多路搜索树，MySQL普遍使用B+树来实现索引。

1. https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

复制代码

B+树和B-树的主要区别：

• B-树内部节点是生存数据的，而B+树内部节点是不生存数据的，只作索引作用，它的叶子节点才生存数据。
  

• B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。
  

• 查找过程中，B-树在找到具体的数值以后就竣事，而B+树则必要通过索引找到叶子结点中的数据才竣事
  

• B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。
  可以使用B+Tree索引的查询类型（假设条件：姓，名，年龄三个字段创建了一个复合索引）
  
• 全值匹配：精确所有索引列，如：姓zhang，名ming，年龄20
  
• 匹配最左前缀：即只使用索引的第一列，如：姓zhang
  

• 匹配列前缀：只匹配一列值开头部分，如：姓以w开头的记录
  

• 匹配范围值：如：姓wang和姓zhang之间
  

• 精确匹配某一列并范围匹配另一列：如：姓wang，名以x开头的记录
  

• 只访问索引的查询
  B+Tree索引的限定
  
• 如不从最左列开始，则无法使用索引，如：查找名为xiaoming，或姓为g末端
  
• 不能跳过索引中的列：如：查找姓为wang，年龄30的，只能使用索引第一列
  特别提示
  
• 索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
  
• 为优化性能，可能必要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
  Hash索引
  
• Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引布局紧凑，查询性能好。
  
• Memory 存储引擎支持显式 hash 索引，InnoDB 和 MyISAM 存储引擎不支持。
  

• 实用场景：只支持等值比较查询，包括=，<=>，IN()。
  不适合使用hash索引的场景
  
• 不实用于顺序查询：索引存储顺序的不是值的顺序
  
• 不支持暗昧匹配
  

• 不支持范围查询
  

• 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
  地理空间数据索引 R-Tree（ Geospatial indexing ）
  

MyISAM 支持地理空间索引，可使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多。
InnoDB从MySQL5.7之后也开始支持
全文索引（FULLTEXT）
在文本中查找关键词，而不是直接比较索引中的值，雷同搜索引擎
InnoDB 从 MySQL 5.6 之后也开始支持
聚簇和非聚簇索引，主键和二级索引
在 MySQL 的 InnoDB 引擎中，每个索引都会对应一棵 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，以是使用非聚簇索引还必要回表查询，因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个：

• 聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。
  

• 聚簇索引查询效率更高，而非聚簇索引必要进行回表查询，因此性能不如聚簇索引。
  

• 聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数目上的限定。
  冗余和重复索引
  
• 冗余索引：（A），（A，B），注意如果同时存在，仍可能会使用(A)索引
  
• 重复索引：已经有索引，再次创建索引
  1.4.3 索引优化
  

参考资料：阿里的《Java开辟手册》

1. https://developer.aliyun.com/topic/java2020

复制代码

• 独立地使用列：尽量避免其到场运算，独立的列指索引列不能是表达式的一部分，也不能是函数的
  

• 参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操纵和表达式操纵）
  

• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
  

• 多列索引：AND操纵时更适合使用多列索引，而非为每个列创建单独的索引
  

• 选择符合的索引列顺序：无排序和分组时，将选择性最高放左侧
  

• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  

• 对于经常在where子句使用的列，最好设置索引
  

• 对于有多个列where大概order by子句，应该创建复合索引
  

• 对于like语句，以 % 大概 _ 开头的不会使用索引，以 % 末端会使用索引
  

• 尽量不要使用not in和<>操纵，固然可能使用索引，但性能不高
  

• 不要使用RLIKE正则表达式会导致索引失效
  

• 查询时，能不要就不用，尽量写全字段名，好比：select id,name,age from students;
  

• 大部分环境毗连效率弘大于子查询
  

• 在有大量记录的表分页时使用limit
  

• 对于经常使用的查询，可以开启查询缓存
  

• 多使用explain和profile分析查询语句
  

• 检察慢查询日志，找出实验时间长的sql语句优化
  1.4.4 管理索引
  

检察帮助

1. mysql> help index

复制代码

检察索引

1. SHOW INDEX FROM [db_name.]tbl_name;

复制代码

​​

1. <strong>mysql> desc student;+--------+------------------+------+-----+---------+----------------+| Field  | Type             | Null | Key | Default | Extra          |+--------+------------------+------+-----+---------+----------------+| id     | int unsigned     | NO   | PRI | NULL    | auto_increment || name   | varchar(20)      | NO   |     | NULL    |                || age    | tinyint unsigned | YES  |     | NULL    |                || gender | enum('M','F')    | YES  |     | M       |                |+--------+------------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
2. #默认会在主键上创建索引mysql> show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 6Sub_part: NULLPacked: NULLNull:Index_type: BTREE   #B树索引Comment:Index_comment:Visible: YESExpression: NULL1 row in set (0.01 sec)</strong>

复制代码

检察语句是否利用索引

1. <strong>mysql> explain select * from student where id=12\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: const   #只读取一次possible_keys: PRIMARYkey: PRIMARY   #使用了主键索引key_len: 4ref: constrows: 1   #扫描了1条数据filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
2. mysql> explain select * from student where name="wangwu"\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL   #全表扫描possible_keys: NULL   #没有使用索引字段key: NULL   #没有使用索引字段key_len: NULLref: NULLrows: 6   #扫描了6条数据，整个表就是6条数据filtered: 16.67Extra: Using where   #在存储引擎检索行后再进行过滤1 row in set, 1 warning (0.00 sec)</strong>

复制代码

创建索引

1. <strong>CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);</strong>

复制代码

1. <strong>mysql> create index idx_name on student(name);Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0#create index idx_name on student(name(10)); 表示取 name 字段中的前 10 个字符做索引</strong>

复制代码

再次检察​​​​​​​​​​​

1. <strong>mysql> show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 6Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: idx_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 6Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL2 rows in set (0.00 sec)</strong>

复制代码

再次测试​​​​​​​

1. <strong>mysql> explain select * from student where name="wangwu"\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: refpossible_keys: idx_name   #使用了索引key: idx_name   #使用了索引key_len: 62ref: constrows: 1   #只扫描1条数据filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)</strong>

复制代码

不是所有查询都能用到索引，B+树索引是左前缀特性，即左匹配可以使用索引​​​​​​​

1. <strong>#like 查询左匹配可以使用索引mysql> explain select * from student where name like 'g%'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: rangepossible_keys: idx_name   #索引key: idx_name   #索引key_len: 62ref: NULLrows: 1filtered: 100.00Extra: Using index condition1 row in set, 1 warning (0.00 sec)
2. #like 查询右匹配不使用索引mysql> explain select * from student where name like '%g'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL   #全表扫描possible_keys: NULL   #不走索引key: NULL   #不走索引key_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where1 row in set, 1 warning (0.00 sec)
3. #like 查询包含匹配不使用索引mysql> explain select * from student where name like '%g%'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where1 row in set, 1 warning (0.00 sec)</strong>

复制代码

mariadb 中关于索引利用的优化​​​​​​​

1. <strong>MariaDB [testdb]> select count(*) from student;+----------+| count(*) |+----------+| 28       |+----------+1 row in set (0.000 sec)
2. MariaDB [testdb]> select count(*) from student where name like 'm%';+----------+| count(*) |+----------+| 22       |+----------+1 row in set (0.000 sec)
3. MariaDB [testdb]> select count(*) from student where name like 'z%';+----------+| count(*) |+----------+| 3        |+----------+1 row in set (0.000 sec)</strong>​​​​​​​

复制代码

1. <strong>MariaDB [testdb]> show index from student\G*************************** 1. row ***************************Table: studentNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 28Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: studentNon_unique: 1Key_name: idx_nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 28Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:2 rows in set (0.001 sec)</strong>​​​​​​​

复制代码

1. <strong>MariaDB [testdb]> explain select * from student where name like 'm%'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studenttype: ALL   #全表扫描possible_keys: idx_name   #可能会用到的索引key: NULL   #没有使用索引key_len: NULLref: NULLrows: 28Extra: Using where1 row in set (0.000 sec)
2. MariaDB [testdb]> explain select * from student where name like 'z%'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studenttype: range   #范围查询possible_keys: idx_name   #可能会用到的索引key: idx_name   #实际使用了索引key_len: 62ref: NULLrows: 3Extra: Using index condition1 row in set (0.000 sec)</strong>​​​​​​

复制代码

1. <strong>全表总共28条记录，name 字段中，以 m 开头的有22条，以 z 开头的有3条，所以在此情况下，查询以 m 开头内容，直接全表扫描反而会更快这是 mariadb 中的优化，MySQL8.0 中也有此功能，但旧版本中没有此优化</strong>

复制代码

删除索引​​​​​​​

1. <strong>DROP INDEX index_name ON tbl_name;ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);</strong>​​​​​​

复制代码

1. <strong>#删除索引mysql> drop index idx_name on student;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0</strong>

复制代码

再次测试​​​​​​​

1. <strong>mysql> explain select * from student where name="wangwu"\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL   #全表扫描possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 6filtered: 16.67Extra: Using where1 row in set, 1 warning (0.01 sec)</strong>

复制代码

检察索引的使用​​​​​​​

1. <strong>SET GLOBAL userstat=1;SHOW INDEX_STATISTICS;</strong>​​​​​​

复制代码

1. <strong>#仅在mariadb中使用MariaDB [testdb]> select @@userstat;+------------+| @@userstat |+------------+| 0          |+------------+1 row in set (0.000 sec)
2. #开启MariaDB [testdb]> SET GLOBAL userstat=1;Query OK, 0 rows affected (0.000 sec)
3. #查看MariaDB [testdb]> SHOW INDEX_STATISTICS;Empty set (0.001 sec)
4. #查询MariaDB [testdb]> select * from student where id=13;+----+----------+------+--------+| id | name     | age  | gender |+----+----------+------+--------+| 13 | zhangfei | 20   | F      |+----+----------+------+--------+1 row in set (0.000 sec)
5. MariaDB [testdb]> select * from student where name like 'z%';+----+-------------+------+--------+| id | name        | age  | gender |+----+-------------+------+--------+| 13 | zhangfei    | 20   | F      || 11 | zhangsan    | 12   | M      || 16 | zhugeiliang | 39   | M      |+----+-------------+------+--------+3 rows in set (0.001 sec)
6. #再次查看MariaDB [testdb]> SHOW INDEX_STATISTICS;+--------------+------------+------------+-----------+| Table_schema | Table_name | Index_name | Rows_read |+--------------+------------+------------+-----------+| testdb       | student    | idx_name   | 3         || testdb       | student    | PRIMARY    | 1         |+--------------+------------+------------+-----------+2 rows in set (0.000 sec)</strong>

复制代码

优化表空间

1. OPTIMIZE TABLE tb_name;

复制代码

对 MySQL 进行大量或频繁的写操纵(insert，delete，update)，容易产生碎片，这些碎片会影响MySQL 性能。在此环境下，我们可以通过 optimize 下令来进行优化。此下令在使用时会锁表，必要保证在不对业务产生影响的环境下使用。
这里的碎片指的是，经过某些操纵，导致数据库中的表对应的硬盘上的物理文件中的数据不是紧密排列的。
1.4.5 EXPLAIN 工具

可以通过EXPLAIN来分析索引的有效性，获取查询实验筹划信息，用来检察查询优化器怎样实验查询。
官方文档​​​​​​​

1. <strong>https://dev.mysql.com/doc/refman/5.7/en/explain-output.htmlhttps://dev.mysql.com/doc/refman/8.0/en/explain-output.html</strong>

复制代码

格式
​​​​​​​

1. EXPLAIN SELECT clause

复制代码

• 
  

1. <strong>mysql> explain select * from student where id=13\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: constpossible_keys: PRIMARY   #可能用到的键key: PRIMARYkey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)</strong>

复制代码

EXPLAIN输出信息说明

• id：实验编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的 select，每行都将显示1。否则，内层的 select 语句一般会顺序编号，对应于其在原始语句中的位置，如果explain的结果包括多个id值，则数字越大越先实验；而对于相同id的行，则表示从上往下依次实验。
  

• select_type：查询类型，具体见下表。
  

• table：具体查询的表名，如查 SQL 语句中定义了别名，则此处显示别名 。
  

• partitions：当前查询匹配记录的分区。对于未分区的表，返回 NULL。
  

• type：关联类型或访问类型，即 MySQL 决定的怎样去查询表中的行的方式，具体见下表。
  

• possible_keys：查询可能会用到的索引，此处列出的索引字段是在真正实验查询前的优化过程中创建的，因此有些不会被使用。
  

• key：实际查询中用到的索引 。
  

• key_len：实际查询中，使用索引数据中的字节数，可通过该列计算查询中使用的索引的长度，key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的，不损失精确性的环境下，长度越短越好 。
  

• ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息，列与索引的比较，表示上述表的毗连匹配条件，即哪些列或常量被用于查找索引列上的值，如果 ref 是一个函数，则使用的值是函数的结果。要想检察是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。
  

• rows：为了找到所需的行而必要读取的行数，估算值，不精确。
  

• filtered：按表条件过滤的行百分比，表示符合查询条件的数据百分比，最大100。用 rows × filtered 可得到和下一张表毗连的行数。
  

• Extra：展示有关本次查询的附加信息，具体见下表。
  

select_type 查询类型具体说明

关键字	说明
SIMPLE	简朴查询（没有使用 UNION 或子查询)
PRIMARY	最外层的查询
UNION	联合查询，在 UNION 中的第二个和随后的 SELECT 被标记为 UNION，如果UNION 被 FROM 子句中的子查询包含，那么它的第一个 SELECT 会被标记为DERIVED
DEPENDENTUNION	UNION 中的第二个或后面的查询，依赖了外层查询
UNION RESULT	UNION 查询结果
SUBQUERY	子查询中的第一个 SELECT 查询
DEPENDENT SUBQUERY	子查询中的第一个 SELECT 查询，依赖了外层查询
DERIVED	用来表示包含在 FROM 子句的子查询中的 SELECT , MySQL 会递归实验并将结果放到一个临时表中，MySQL 内部将此临时表称为 DERIVED table（派生表），由于该临时表是从子查询中派生出来的
DEPENDENT DERIVED	派生表，而且有依赖于其它表
MALTERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	子查询，但结果无法缓存，必须对于外部查询的每一行重新评估
UNCACHEABLEUNION	UNION 属于 UNCACHEABLE SUBQUERY 的第二个或后面的查询

type 关联类型具体说明，性能从好到坏排序

关键字	说明
NULL	MySQL 在优化过程中分解语句，实验时甚至不用访问表或索引，此种查询最高效
system	该表只有一行（相当于系统表），system 是 const 类型的特例
const	针对主键或唯一索引的等值查询扫描,，最多只返回一行数据， const 查询速率非常快, 它仅仅读取一次即可。
eq_ref	当使用了索引的全部构成部分，而且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型
ref	当满足索引的最左前缀规则，大概索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行
fulltext	全文索引
ref_or_null	该类型雷同于 ref，但是 MySQL 会额外搜索哪些行包含了 NULL。这种类型常见于解析子查询。
index_merge	此类型表示使用了索引归并优化，表示一个查询里面用到了多个索引
unique_subquery	该类型和 eq_ref 雷同，但是使用了 IN 查询，且子查询是主键大概唯一索引
index_subquery	和 unique_subquery 雷同，只是子查询使用的好坏唯一索引
range	范围扫描，表示检索了指定范围的行，主要用于有限定的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操纵符
index	全索引扫描，和ALL雷同，只不外index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型
ALL	全表扫描，性能最差

Extra 额外信息说明

关键字	关键字
Using where	不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示 MySQL 服务器将在存储引擎检索行后再进行过滤。
Using temporary	表示 MySQL 必要使用临时表来存储结果集，常见于排序和分组查询，常见 group by，order by。
Using filesort	当 Query 中包含 order by 操纵，而且无法利用索引完成的排序操纵称为 “文件排序”。
Using join buffer	改值夸大了在获取毗连条件时没有使用索引，而且必要毗连缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体环境可能必要添加索引来改进能。
Impossible where	这个值夸大了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
No tables used	Query语句中使用from dual 或不含任何from子句。

1. <strong>mysql> explain select * from student where id=12\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: const   #只读取一次possible_keys: PRIMARYkey: PRIMARY   #使用了主键索引key_len: 4ref: constrows: 1   #扫描了1条数据filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)
2. mysql> explain select * from student where name="wangwu"\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: studentpartitions: NULLtype: ALL   #全表扫描possible_keys: NULL   #没有使用索引字段key: NULL   #没有使用索引字段key_len: NULLref: NULLrows: 6   #扫描了6条数据，整个表就是6条数据filtered: 16.67Extra: Using where   #在存储引擎检索行后再进行过滤1 row in set, 1 warning (0.00 sec)</strong>

复制代码

1.4.6 profile 工具
开启 profiling 设置可以记录 SQL 语句实验的具体过程​​​​​​​

1. <strong>mysql> select @@profiling;+-------------+| @@profiling |+-------------+| 0           |+-------------+1 row in set, 1 warning (0.00 sec)
2. #无记录mysql> show profiles;Empty set, 1 warning (0.00 sec)
3. #开启mysql> set profiling=1;Query OK, 0 rows affected, 1 warning (0.00 sec)
4. #执行SQL语句mysql> select * from student where name="wangwu";+----+--------+------+--------+| id | name   | age  | gender |+----+--------+------+--------+| 12 | wangwu | 13   | M      |+----+--------+------+--------+1 row in set (0.00 sec)
5. #再次查看mysql> show profiles;+----------+------------+-------------------------------------------+| Query_ID | Duration   | Query                                     |+----------+------------+-------------------------------------------+| 1        | 0.00091900 | select * from student where name="wangwu" |+----------+------------+-------------------------------------------+1 row in set, 1 warning (0.00 sec)
6. #删除索引mysql> drop index idx_name on student;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
7. #再次查询mysql> select * from student where name="wangwu";+----+--------+------+--------+| id | name   | age  | gender |+----+--------+------+--------+| 12 | wangwu | 13   | M      |+----+--------+------+--------+1 row in set (0.01 sec)
8. #两次用时对比mysql> show profiles;+----------+------------+-------------------------------------------+| Query_ID | Duration   | Query                                     |+----------+------------+-------------------------------------------+| 1        | 0.00091900 | select * from student where name="wangwu" || 2        | 0.01600600 | drop index idx_name on student || 3        | 0.00112750 | select * from student where name="wangwu" |   #数据量太小，不能体现索引优势+----------+------------+-------------------------------------------+3 rows in set, 1 warning (0.00 sec)</strong>

复制代码

导入大表再次对比

1. <strong>[root@rocky86 ~]# cat testlog.sqlcreate table testlog (id int auto_increment primary key,name char(10),salary int default 20);delimiter $$create procedure sp_testlog()begindeclare i int;set i = 1;while i <= 100000do insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000));set i = i +1;end while;end$$delimiter;</strong>​​​​​​​

复制代码

1. <strong>#导入[root@rocky86 ~]# mysql testdb < testlog.sqlmysql> use testdb;Database changed
2. mysql> show tables;+------------------+| Tables_in_testdb |+------------------+| student          || t1               || testlog          |+------------------+3 rows in set (0.00 sec)
3. mysql> select count(*) from testlog;+----------+| count(*) |+----------+| 0        |+----------+1 row in set (0.01 sec)
4. #执行存储过程mysql> call sp_testlog;Query OK, 1 row affected (1 min 2.02 sec)
5. mysql> select count(*) from testlog;+----------+| count(*) |+----------+| 100000   |+----------+1 row in set (0.01 sec)
6. #查询mysql> select * from testlog limit 5;+----+-----------+--------+| id | name      | salary |+----+-----------+--------+| 1 | wang64276  | 606675 || 2 | wang10506  | 705314 || 3 | wang21136  | 940888 || 4 | wang7034   | 529049 || 5 | wang43421  | 583940 |+----+-----------+--------+5 rows in set (0.00 sec)</strong>​​​​​​​

复制代码

1. <strong>#默认主键索引mysql> show index from testlog\G;*************************** 1. row ***************************Table: testlogNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 98399Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:Visible: YESExpression: NULL1 row in set (0.00 sec)
2. #查询mysql> select * from testlog where salary=583940;+----+-----------+--------+| id | name      | salary |+----+-----------+--------+| 5 | wang43421  | 583940 |+----+-----------+--------+1 row in set (0.01 sec)
3. #查看索引使用情况mysql> explain select * from testlog where salary=583940\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: testlogpartitions: NULLtype: ALL   #全表扫描possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 98399filtered: 10.00Extra: Using where1 row in set, 1 warning (0.00 sec)
4. #查看执行时间mysql> show profiles;+----------+------------+-------------------------------------------+| Query_ID | Duration   | Query                                     |+----------+------------+-------------------------------------------+| 1        | 0.01881575 | select * from testlog where salary=583940 |+----------+------------+-------------------------------------------+1 row in set, 1 warning (0.00 sec)</strong>​​​​​​​

复制代码

1. <strong>#在salary列创建索引mysql> create index idx_salary on testlog(salary);Query OK, 0 rows affected (0.16 sec)Records: 0 Duplicates: 0 Warnings: 0
2. #再次查询mysql> select * from testlog where salary=529049;+----+----------+--------+| id | name     | salary |+----+----------+--------+| 4 | wang7034  | 529049 |+----+----------+--------+1 row in set (0.00 sec)
3. #对比mysql> show profiles;+----------+------------+--------------------------------------------+| Query_ID | Duration   | Query                                      |+----------+------------+--------------------------------------------+| 1        | 0.01881575 | select * from testlog where salary=583940  || 2        | 0.15655150 | create index idx_salary on testlog(salary) || 3        | 0.00038900 | select * from testlog where salary=529049  |+----------+------------+--------------------------------------------+3 rows in set, 1 warning (0.00 sec)
4. mysql> explain select * from testlog where salary=529049\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: testlogpartitions: NULLtype: refpossible_keys: idx_salarykey: idx_salarykey_len: 5ref: constrows: 1   #只扫描1条记录filtered: 100.00Extra: NULL1 row in set, 1 warning (0.00 sec)</strong>

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4