1 索引简介
1.1 索引的介绍
(1)什么是索引
索引是一个单独的、存储在磁盘上的数据库结构,它们包含着对数据表里所有记录的引用指针。索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。
MySQL中索引的存储类型有两种:BTREE和HASH。MyISAM和InnoDB存储引擎只支持BTREE索引;MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
(2)为什么要索引
使用索引用于快速找出在某个或多个列中有一特定值的行。例如,数据库中有2W条记录,执行一条查询语句:SELECT * FROM table WHERE num=10000.如果没有索引,必须遍历整个表,直到num等于10000这一行被找到为止;如果在num列上建立索引,那么MySQL不需要任何扫描,直接在索引里面找10000,就可以得知这一行的位置。所以,索引的建立可以提高数据库的查询速度。
(3)索引的优点
①通过创建唯一索引可以保证数据库表中每一行数据的唯一性
②可以大大加快数据的查询速度
③在实现数据的参考完整性方面,可以加速表和表之间的连接
④可以显著减少查询中分组和排序的时间
(4)索引的缺点
①创建和维护索引需要消耗时间
②索引需要占用磁盘空间
③当对表进行CRUD操作时,索引也要动态维护,降低了数据库维护速度。
1.2 索引的分类
1.2.1 普通索引和唯一索引
普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值;
唯一索引,索引列的值必须是唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。主键索引是一种特殊的唯一索引,不允许有空值。
1.2.2 单列索引和组合索引
单列索引即一个索引只包含单个列,一个表可以有多个单列索引;
组合索引值在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。
1.2.3 全文索引
全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
1.2.4 空间索引
空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有4种:GEOMETRY、POINT、LINESTRING和POLYGON。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISAM的表中创建。
1.3 索引的设计原则
(1)索引并非越多越好。占用磁盘,影响插入、更新等语句性能。
(2)对经常更新的表避免过多的索引,且索引中的列尽可能少;对经常查询的字段应该创建索引,但要避免添加不必要的字段。
(3)数据量少的表最好不要使用索引。
(4)在条件表达式中经常用到的不同值较多的列上建立索引,而在不同值少的列上不要建立索引。比如“性别”字段只有“男”或“女”,则无须建立索引。
(5)当唯一性是某种数据本身的特征时,指定唯一索引。
(6)在频繁进行排序或分组(group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。
2 创建索引
2.1 创建表的时候创建索引
使用CREATE TABLE创建表时,在定义约束的同时相当于在指定列上创建了一个索引。语法格式:
CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]
2.1.1 创建普通索引
最基本的索引类型,没有唯一性之类的限制,作用只是加快对数据的访问速度。
例如,在book表中未year_publication字段建立普通索引:
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication)
);
使用SHOW CREATE TABLE table_name查看表结构:
mysql> SHOW CREATE TABLE book;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book | CREATE TABLE `book` (
`bookid` int(11) NOT NULL,
`bookname` varchar(255) NOT NULL,
`authors` varchar(255) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
使用EXPLAIN语句查看索引是否正在使用:
mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1990;
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
| 1 | SIMPLE | book | ref | year_publication | year_publication | 1 | const | 1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set
EXPLAIN语句输出结果说明:
- select_type:指定所用的SELECT查询类型,SIMPLE表示简单的SELECT,不使用UNION或子查询,其他可能的值:PRIMARY,UNION,SUBQUERY等。
- table:指定数据库读取的数据表名
- type:指定本数据表与其他数据表之间的关联关系,可能的值有:system、const、eq_ref、ref、range、index和ALL
- possible_keys:列出了MySQL在搜索数据记录时可选的各个索引
- key:MySQL实际选用的索引
- key_len:给出索引按字节计算的长度,key_len数值越小,表示越快
- ref:给出关联关系中另一个数据表里的数据列名字。
- rows:执行这个查询时预计会从这个数据表里读出的数据行的个数
- extra:提供与关联操作有关的信息
2.1.2 创建唯一索引
创建唯一索引时,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
mysql> CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
2.1.3 创建单列索引
单列索引是在数据表中的某一个字段上创建的索引,一个表中可以创建多个单列索引。(前两个创建的都是单列索引)
mysql> CREATE TABLE t2(
-> id INT NOT NULL,
-> name CHAR(50) NULL,
-> INDEX SingleIdx(name(20))
-> );
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE t2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` char(50) DEFAULT NULL,
KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
2.1.4 创建组合索引
组合索引实在多个字段上创建一个索引。
mysql> CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, info(100))
);
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE t3;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `MultiIdx` (`id`,`name`,`info`(100))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
mysql> EXPLAIN SELECT * FROM t3 WHERE id=1 AND name='joe';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| 1 | SIMPLE | t3 | ref | MultiIdx | MultiIdx | 34 | const,const | 1 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set
2.1.5 创建全文索引
FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR,VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。
mysql> CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE t4;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
全文索引非常适合于大型数据集,对于小的数据集,它的用处可能比较小。
2.1.6 创建空间索引
空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
mysql> CREATE TABLE t5 ( g GEOMETRY NOT NULL, SPATIAL INDEX spatIdx(g) )ENGINE=MyISAM;
Query OK, 0 rows affected
mysql> SHOW CREATE TABLE t5;
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`g` geometry NOT NULL,
SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
1 row in set
2.2 在已经存在的表上创建索引
2.2.1 使用ALTER TABLE语句创建索引
基本语法:
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]
[index_name] (col_name[length],...) [ASC|DESC]
例如,在book表中的bookname字段上建立名为BkNameIdx的普通索引:
①添加之前,使用SHOW INDEX查看指定表中创建的索引:
mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set
其中各个参数的含义:
- Table:表示创建索引的表
- Non_unique:表示索引非唯一,1代表非唯一索引,0代表唯一索引
- Key_name:表示索引的名称
- Seq_in_index:表示该字段在索引中的位置,单列索引为1,组合索引为每个字段在索引定义中的顺序
- Column_name:表示定义索引的列字段
- Sub_part:表示索引的长度
- Null:表示该字段是否能为空值
- Index_type:表示索引类型
②使用ALTER TABLE添加索引:
mysql> ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
③使用SHOW INDEX查看表中索引:
mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |
| book | 1 | BkNameIdx | 1 | bookname | A | 0 | 30 | NULL | | BTREE | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
④建立组合索引:
mysql> ALTER TABLE book ADD INDEX BkAuAndInfoIdx(authors(20),info(50));
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |
| book | 1 | BkNameIdx | 1 | bookname | A | 0 | 30 | NULL | | BTREE | | |
| book | 1 | BkAuAndInfoIdx | 1 | authors | A | 0 | 20 | NULL | | BTREE | | |
| book | 1 | BkAuAndInfoIdx | 2 | info | A | 0 | 50 | NULL | YES | BTREE | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set
2.2.2 使用CREATE INDEX创建索引
基本语法:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name(col_name[length],...) [ASC|DESC]
例如,在book表中的comment字段上建立单列索引:
mysql> CREATE INDEX BkcmtIdx ON book(comment(50));
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |
| book | 1 | BkNameIdx | 1 | bookname | A | 0 | 30 | NULL | | BTREE | | |
| book | 1 | BkAuAndInfoIdx | 1 | authors | A | 0 | 20 | NULL | | BTREE | | |
| book | 1 | BkAuAndInfoIdx | 2 | info | A | 0 | 50 | NULL | YES | BTREE | | |
| book | 1 | BkcmtIdx | 1 | comment | A | 0 | 50 | NULL | YES | BTREE | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set
3 删除索引
3.1 使用ALTER TABLE删除索引
基本语法:
ALTER TABLE table_name DROP INDEX index_name;
例如,删除book表明为BkcmtIdx的索引:
mysql> ALTER TABLE book DROP INDEX BkcmtIdx;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |
| book | 1 | BkNameIdx | 1 | bookname | A | 0 | 30 | NULL | | BTREE | | |
| book | 1 | BkAuAndInfoIdx | 1 | authors | A | 0 | 20 | NULL | | BTREE | | |
| book | 1 | BkAuAndInfoIdx | 2 | info | A | 0 | 50 | NULL | YES | BTREE | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set
添加AUTO_INCREATEMENT约束字段的唯一索引不能被删除。
3.2 使用DROP INDEX语句删除索引
基本语法:
DROP INDEX index_name ON table_name;
例如,删除book表中名为BkAuAndInfoIdx的索引:
mysql> DROP INDEX BkAuAndInfoIdx ON book;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book | 1 | year_publication | 1 | year_publication | A | 0 | NULL | NULL | | BTREE | | |
| book | 1 | BkNameIdx | 1 | bookname | A | 0 | 30 | NULL | | BTREE | | |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
4 说明
阅读《MySQL5.5 从零开始学》摘抄。