MySQL——索引结构

索引：用于快速查找数据。

索引是将数据的一些关键信息通过特定的数据结构存储到一片新的空间中，这样在文件查找的时候能快速找到。

mysql索引类型：

• B+TREE、HASH、R-TREE、FULL TEXT

B+Tree:B+树，MySQL常用的一种索引类型。

哈希索引：基于哈希表实现，取数据的哈希值，把这个哈希值来作为索引。

R-Tree：和地理位置相关信息的索引，例如查询附近的人等

全文索引：例如在一个文本中查找关键词出现的次数等

• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起

innodb这个存储引擎就是聚集索引，因为索引和数据是放在一起的。

而myisam就是非聚集索引，索引和数据分开存放的。

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

主键索引：主键自带索引，在表中建立主键的同时，会按照主键的次序来将数据进行排序。

二级索引：一张表原来有索引，又添加了一个新的索引，这个新添加的索引就是二级索引

• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引: 是否是多个字段的索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

MySQL管理索引：

创建索引：

create index index_name on tb_name(col_name[(length)]) #如果选择作为索引的字段是一个字符串，可以只取这个字符串的一部分

alter table tb_name add index index_name(col_name)

mysql> create index index_name on students(name(5));
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

#name(5)表示使用name这个字段来作为索引值，但是只取name这个字符串的前五个字符

删除索引：

drop index index_name on tb_name

alter table tb_name drop index index_name(col_name)

mysql> drop index index_name on students;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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.00 sec)

查看索引：

show index from tb_name

mysql> 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.00 sec)

table：当前执行操作的表名

Non_unique:是否是唯一键索引

Key_name:索引名，说明是主键索引

Column_name：建立索引的字段

Index_type：索引类型，B+树

mysql> create index index_name on students(name(5));
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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 | index_name |            1 | Name        | A         |          25 |        5 | NULL   |      | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

explain工具

explain可以用来判断是否使用了索引，显示执行的细节：
格式：explain Sql语句

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

id:执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1

select_type:查询类型，simple是简单查询。复杂查询：PRIMARY

table:当前正在操作的表

type:表示的是访问类型。 all：全表扫描（表从头到位扫描一遍） ref：表示参考了索引来进行查找

possible_keys:可以使用的索引

key:显示mysql决定采用哪个索引来优化查询

key_len:显示mysql在索引里使用的字节数

ref:当使用索引列等值查询时,与索引列进行等值匹配的对象信息

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

extra:附加信息

范例：

mysql> explain select * from students where name like 'S%';
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | students | range | index_name    | index_name | 17      | NULL |    3 | Using where |
+----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from students where name like '%S';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 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)

mysql> explain select * from students where name like '%S%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 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)

查找以某个字母开头的可以利用索引，例如like S%或 %S%就不能使用到索引

MySQL很智能，如果发现利用索引的效率还没有不利用索引的效率高，就会选择不利用索引。

profile工具

显示sql语句执行的详细过程。

set profiling = ON; #设置这个变量的值为ON默认就开启了这个工具

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (1.60 sec)

开启以后通过：
mysql> show profiles;
可以看到曾今执行过的命令以及花费的时间。

mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 1.63420125 | select @@profiling     |
|        2 | 0.00017425 | select * from students |
+----------+------------+------------------------+
2 rows in set, 1 warning (0.00 sec)

#显示语句的详细执行步骤和时长
Show profile for query num

mysql> Show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000040 |
| checking permissions | 1.634089 |
| Opening tables       | 0.000010 |
| init                 | 0.000008 |
| optimizing           | 0.000004 |
| executing            | 0.000008 |
| end                  | 0.000003 |
| query end            | 0.000003 |
| closing tables       | 0.000003 |
| freeing items        | 0.000016 |
| cleaning up          | 0.000019 |
+----------------------+----------+
11 rows in set, 1 warning (0.10 sec)

Original: https://www.cnblogs.com/heyongshen/p/16685288.html
Author: 背对背依靠
Title: MySQL——索引结构