MySQL索引基本语句（超实用）

MySQL索引课堂笔记

前言

MySQL的索引
说明：对数据库表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。
优点：在select访问表数据时是进行一遍又一遍的遍历查询，直到查找到对应的结果时才能够终止，通过索引即可对要查找的数据直接查找。
特点：索引是经过某种算法优化之后，对查找次数变少的过程。

一、索引的分类

1.索引的存储与分类

1.索引与存储引擎的关系

索引是在存储引擎中实现的，每种存储引擎所支持的索引类型不一定完全相同，而且并非所有的存储引擎都会支持所有的索引类型。

2.在物理上的索引分类

[1]. B-树索引:多数索引是以B树方式来存储的，也就是将索引采用树的数据结构存储在文件中，B-树索引使用时的约束:
(1).查询必须从索引的最左边的列开始
(2).查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配
(3).存储引擎不能使用索引中范围条件右边的列

[2] . 哈希索引:也称散列索引或HASH索引，这种索引的使用限制:
(1).可能会影响到系统的性能
(2).不能使用HASH索引排序
(3).不支持键的部分匹配，因为HASH值时通过整个索引值来计算的
(4).只支持等值比较

3. 在逻辑上的索引分类

（1）.普通索引(INDEX):最基本的索引类型，没有任何限制，创建普通索引使用INDEX或KEY
（2.）唯一索引(UNIQUE) :和普通索引基本相同，只有一点区别，索引列中的所有值只能出现一次，必须是唯一的，创建唯一索引， 使用关键字UNIQUE。
（3）.主键索引(PRIMARY KEY):主键是一种唯一索引，创建主键时，必须指定关键字PRIMARYKEY,主键列上不能有空值，
（4）.全文索引(FULLTEXT) :只能在VARCHAR或TEXT类型的列上创建，并且只能在NyISAM表中创建
（5）.空间索引: MyISAM存储引擎支持空间索引，即RTREE索引， 这种索引主要用于地理空间数据类型。

4.实际使用中的索引分类

1.单列索引:一个索引只包含原表中的一个列
2.组合索引:也称复合索引或多列索引，就是原表中的多个列共同组成一个索引。

5.按索引策略分类

(1)聚集索引:保证键值相近的记录元组存储的物理位置也相同，并且一个表只能有一一个聚集索引。
(2)覆盖索引:如果索引包含满足查询的所有数据，称为覆盖索引。由于只需读取索引而不用读取数据，所以覆盖索引能大大提高查询性能。有以下有点索引项通常比记录要小所以都按值的大小顺序存储，相对于随机访问记录需要更少的I/0大多数数据引擎能更好的缓存索引覆盖索引对于InnoDB表尤为有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

注意:不同的存储引擎实现覆盖索引的方式不同，而且并非所有存储引擎都支持覆盖索引

二、索引的创建使用步骤

说明

1.index_ type: BTREE|RTREE|HASH

2.myi sam和innodb存储引擎的表默认创建索引都是btree索引

3.index_ col_ name: col_ name [(length)] [ASC | DESC]

4.length:指定使用列前的length 个字符来创建索引

5.ASC:指定索引按照升序来排列

6.DESC:指定紫引按照降序来排列默认是ASC

1.建表时建立索引

1.在创建新表的同时创建该表的主键
[constraint [symbol]] primary key [index type] (index col_ name, …) [index _option]

2.在创建新表的同时创建该表的索引
index | key [index_ name][index_ type] (index_ col_ name, …) [index_ option]

3.在创建新表的同时创建该表的唯一索引
[constraint [symbol]] unique [index | key]
[index_ name][index_ type] (index_ col_ name, …) [index_ option]

4.在创建新表的同时创建该表的全文索引或空间索引
FULLTEXT | SPATIAL| [index|key] [index name](index_ col name, …)) [index_ option]

5.在创建新表的同时创建该表的外键
[constraint [symbol]] foreign key [index_name] (index._col_name,…) reference_def

案例：

mysql> create table seller(
    -> seller_id int not null auto_increment,
    -> seller_name char(50) not null,
    -> seller_address char(50),
    -> seller_contact char(50),
    -> product_type int(5),
    -> sales int,
    -> primary key(seller_id,product_type),
    -> index index_seller(sales));
Query OK, 0 rows affected (0.02 sec)

2.修改表时添加索引

结构方法（案例）：

mysql> alter table seller
    -> add index index_seller_name(seller_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc seller;
+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| seller_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| seller_name    | char(50) | NO   | MUL | NULL    |                |
| seller_address | char(50) | YES  |     | NULL    |                |
| seller_contact | char(50) | YES  |     | NULL    |                |
| product_type   | int(5)   | NO   | PRI | 0       |                |
| sales          | int(11)  | YES  | MUL | NULL    |                |
+----------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

3.索引的查看

结构：

SHOW INDEX | INDEXES | KEYS
from | in tb_ name
[from | in dts name]
[WHERE expr]
查看索引时会以表的形式显示结果

案例

mysql> show tables;
+----------------+
| Tables_in_data |
+----------------+
| info           |
| seller         |
| user           |
+----------------+
3 rows in set (0.00 sec)
mysql> desc seller;
+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| seller_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| seller_name    | char(50) | NO   | MUL | NULL    |                |
| seller_address | char(50) | YES  |     | NULL    |                |
| seller_contact | char(50) | YES  |     | NULL    |                |
| product_type   | int(5)   | NO   | PRI | 0       |                |
| sales          | int(11)  | YES  | MUL | NULL    |                |
+----------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> show index from info;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          1 | index_cust |            1 | uname       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_cust |            2 | upass       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from seller;
+--------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name          | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| seller |          0 | PRIMARY           |            1 | seller_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| seller |          0 | PRIMARY           |            2 | product_type | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| seller |          1 | index_seller      |            1 | sales        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| seller |          1 | index_seller_name |            1 | seller_name  | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

总结

table:索引所在表的名称

Non unique:显示该索引是否不是唯一索引， 是为1，否则0

Seq in_index:显示索引中列所对应的序列号，从1开始

Column_name :显示索引中列的名称

Key_ name:显示索引名称

Collation:用于显示列以什么顺序存储在索引中

Cardinality:显示索引中唯一值 数目的估计值

Sub part:显示索引列被编入索引的情况

Packed:显示关键字如何被压缩，没压缩显示NULL