【原】MySQL建立索引

Myth丶恋晨 2021-12-22 04:37 390阅读 0赞

索引是一种特殊的数据结构，可以用来快速查询数据库的特定记录，建立是一中提高数据库性能的重要方式。

内容：索引的意义，索引的设计，创建和删除

### 索引简介 ###

**索引是建立在表上的，有一列或者多列组成，并对这一列或者多列进行排序的一种结构。**

所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，索引有两种存储类型，包括 B型树索引和 哈希索引。

索引的优点是可以提高检索的速度，但是创建和维护索引需要耗费时间，这个时间随着数据量的增加而增加。

索引可以提高查询的速度，但是会影响插入的速度，当要插入大量的数据时，最好的办法是先删除索引，插入数据后再建立索引。

MySQL的索引分为：普通索引，唯一性索引，全文索引，单列索引，多列索引和空间索引。

目前只有MyISAM存储引擎支持全文索引，InnoDB引擎还不支持全文索引。

### 索引的设计原则 ###

1.  选择唯一性索引。
2.  为经常需要排序，分组和联合操作的字段建立索引。
3.  为常作为查询条件的字段建立索引。
4.  限制索引的数目。
5.  尽量使用数据量少的索引。
6.  尽量使用前缀来索引。如果字段的值很长，最好使用值的前缀来索引，如果只检索子酸的前面的若干字符，可以提高检索的速度。
7.  删除不再使用或者很少使用的索引。

原则只是参考而不能拘泥。

### 创建索引 ###

三种方式：在创建表是创建索引，在已存在的表上创建索引和使用alter table语句创建索引。

mysql > **show** tables;  
\+ \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \+  
| Tables\_in\_kiwi |  
\+ \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \+  
| stu |  
\+ \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \+  
1 **row** **in** **set** ( 0.00 sec)  
  
mysql > **create** **table** indexTest(id **int**, **name** **varchar**( 20), sex **boolean**, **index** index\_id(id));  
Query OK, 0 **rows** affected ( 0. 08 sec)  
  
mysql > **desc** indextest;  
\+ \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \+ \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \+  
| Field | **Type** | **Null** | **Key** | **Default** | Extra |  
\+ \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \+ \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \+  
| id | **int**( 11) | YES | MUL | **NULL** | |  
| **name** | **varchar**( 20) | YES | | **NULL** | |  
| sex | tinyint( 1) | YES | | **NULL** | |  
\+ \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \+ \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \+  
3 **rows** **in** **set** ( 0. 01 sec)  
  
mysql > **explain** **select** \* **from** indextest **where** id = 1 \\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
id: 1  
select\_type: **SIMPLE**  
**table**: indextest  
**type**: **ref**  
possible\_keys: index\_id  
**key**: index\_id  
key\_len: 5  
**ref**: const  
**rows**: 1  
Extra: **Using** **where**  
1 **row** **in** **set** ( 0.00 sec)

`创建单列索引，subject(10)是为了不查询全部信息而提高检索的速度。`

mysql > **create** **table** singleRow(id **int**, **name** **varchar**( 20),subject **varchar**( 30), **index** index\_st(subject( 10)));  
Query OK, 0 **rows** affected ( 0. 17 sec)  
  
mysql > **show** **create** **table** singlerow\\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
**Table**: singlerow  
**Create** **Table**: **CREATE** **TABLE** \`singlerow\` (  
\`id\` **int**( 11) **DEFAULT** **NULL**,  
\` **name**\` **varchar**( 20) **DEFAULT** **NULL**,  
\`subject\` **varchar**( 30) **DEFAULT** **NULL**,  
**KEY** \`index\_st\` (\`subject\`( 10))  
) ENGINE =InnoDB **DEFAULT** CHARSET =latin1  
1 **row** **in** **set** ( 0.00 sec)

`多列索引，空间索引类似。`

`在已存在的表上建立索引`

`语法为： create [unique|fulltext|spatial] index index_name on table_name (property_name[length] [asc|desc]);`

mysql > **desc** stu;  
\+ \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \+ \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \+  
| Field | **Type** | **Null** | **Key** | **Default** | Extra |  
\+ \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \+ \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \+  
| id | **int**( 10) | **NO** | PRI | **NULL** | auto\_increment |  
| s\_num | **int**( 10) | YES | MUL | **NULL** | |  
| course | **varchar**( 20) | YES | | **NULL** | |  
| score | **varchar**( 4) | YES | | **NULL** | |  
\+ \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \+ \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \+ \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \- \+  
4 **rows** **in** **set** ( 0. 05 sec)  
  
mysql > **show** **create** **table** stu \\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
**Table**: stu  
**Create** **Table**: **CREATE** **TABLE** \`stu\` (  
\`id\` **int**( 10) **NOT** **NULL** AUTO\_INCREMENT,  
\`s\_num\` **int**( 10) **DEFAULT** **NULL**,  
\`course\` **varchar**( 20) **DEFAULT** **NULL**,  
\`score\` **varchar**( 4) **DEFAULT** **NULL**,  
**PRIMARY** **KEY** (\`id\`),  
**UNIQUE** **KEY** \`id\` (\`id\`),  
**UNIQUE** **KEY** \`index\_id\` (\`id\`),  
**KEY** \`grade\_fk\` (\`s\_num\`)  
) ENGINE =MyISAM **DEFAULT** CHARSET =latin1  
1 **row** **in** **set** ( 0.00 sec)

`使用alter table创建索引`

`语法为：`

**alter** **table** **table\_name** **add** \[ **unique**|fulltext|spatial\] **index** index\_name(property\_name\[ **length**\] \[ **asc**| **desc**\]);

mysql > **create** **table** index\_1(id **int**, **name** **varchar**( 20), **class** **int**);  
Query OK, 0 **rows** affected ( 0. 11 sec)  
  
mysql > **show** tables;  
\+ \----------------+  
| Tables\_in\_kiwi |  
\+ \----------------+  
| index\_1 |  
| singlerow |  
| stu |  
\+ \----------------+  
3 **rows** **in** **set** ( 0.00 sec)  
  
mysql > **show** **create** **table** index\_1 \\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
**Table**: index\_1  
**Create** **Table**: **CREATE** **TABLE** \`index\_1\` (  
\`id\` **int**( 11) **DEFAULT** **NULL**,  
\` **name**\` **varchar**( 20) **DEFAULT** **NULL**,  
\` **class**\` **int**( 11) **DEFAULT** **NULL**  
) ENGINE =InnoDB **DEFAULT** CHARSET =latin1  
1 **row** **in** **set** ( 0.00 sec)  
  
mysql > **alter** **table** index\_1 **add** fulltext **index** index\_alter ( **name** **desc**);  
ERROR 1214 (HY000): The used **table** **type** doesn't support FULLTEXT indexes  
mysql > **alter** **table** index\_1 engine =myisam;  
Query OK, 0 **rows** affected ( 0. 36 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
  
mysql > **alter** **table** index\_1 **add** fulltext **index** index\_alter ( **name** **desc**);  
Query OK, 0 **rows** affected ( 0. 13 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
  
mysql > **show** **create** **table** index\_1 \\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
**Table**: index\_1  
**Create** **Table**: **CREATE** **TABLE** \`index\_1\` (  
\`id\` **int**( 11) **DEFAULT** **NULL**,  
\` **name**\` **varchar**( 20) **DEFAULT** **NULL**,  
\` **class**\` **int**( 11) **DEFAULT** **NULL**,  
FULLTEXT **KEY** \`index\_alter\` (\` **name**\`)  
) ENGINE =MyISAM **DEFAULT** CHARSET =latin1  
1 **row** **in** **set** ( 0.00 sec)

### 删除索引 ###

语法：

**drop** **index** index\_name **on** **table\_name**;

mysql > **show** **create** **table** index\_1 \\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
**Table**: index\_1  
**Create** **Table**: **CREATE** **TABLE** \`index\_1\` (  
\`id\` **int**( 11) **DEFAULT** **NULL**,  
\` **name**\` **varchar**( 20) **DEFAULT** **NULL**,  
\` **class**\` **int**( 11) **DEFAULT** **NULL**,  
FULLTEXT **KEY** \`index\_alter\` (\` **name**\`)  
) ENGINE =MyISAM **DEFAULT** CHARSET =latin1  
1 **row** **in** **set** ( 0.00 sec)  
  
ERROR:  
**No** query specified  
  
mysql > **drop** **index** index\_alter **on** index\_1;  
Query OK, 0 **rows** affected ( 0. 11 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
  
mysql > **show** **create** **table** index\_1 \\ **G**;  
\* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* 1. **row** \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \* \*  
**Table**: index\_1  
**Create** **Table**: **CREATE** **TABLE** \`index\_1\` (  
\`id\` **int**( 11) **DEFAULT** **NULL**,  
\` **name**\` **varchar**( 20) **DEFAULT** **NULL**,  
\` **class**\` **int**( 11) **DEFAULT** **NULL**  
) ENGINE =MyISAM **DEFAULT** CHARSET =latin1  
1 **row** **in** **set** ( 0.00 sec)

转载于:https://www.cnblogs.com/kiwi/archive/2012/11/17/2774945.html