MySQL知识（十三）——索引
来源：互联网 发布：天刀胡歌大叔捏脸数据 编辑：程序博客网 时间：2024/05/29 13:43
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 setEXPLAIN语句输出结果说明：
- 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 affectedmysql> 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 set2.1.3 创建单列索引
单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。（前两个创建的都是单列索引）
mysql> CREATE TABLE t2( -> id INT NOT NULL, -> name CHAR(50) NULL, -> INDEX SingleIdx(name(20)) -> );Query OK, 0 rows affectedmysql> 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 set2.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 affectedmysql> 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 setmysql> 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 set2.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 affectedmysql> 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 affectedmysql> 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 set2.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 affectedRecords: 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 affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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 set2.2.2 使用CREATE INDEX创建索引
基本语法：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_nameON table_name(col_name[length],...) [ASC|DESC]例如，在book表中的comment字段上建立单列索引：
mysql> CREATE INDEX BkcmtIdx ON book(comment(50));Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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 set3 删除索引
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 affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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 affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> 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 set4 说明
阅读《MySQL5.5 从零开始学》摘抄。
1 0
- MySQL知识（十三）——索引
- MySQL高级十三——通过索引优化SQL
- MySQL知识（二十三）——表的导出和导入
- Oracle数据库操作大全（十三）——约束，索引
- 知识梳理-理解MySQL——索引与优化
- mysql索引小知识
- mysql索引知识笔记
- mysql索引知识
- MySQL索引知识整理
- MySQL索引知识总结
- 总结Mysql索引相关知识
- MySQL中高级知识-索引优化
- 知识积累（十三）——JSF+Spring+Hibernate的实例讲解（一）
- MYSQL——索引
- 【MySQL】——索引
- 自学mysql—mysql索引
- ITPUB知识索引贴——全文索引
- ITPUB知识索引贴——全文索引
- JAVA中获取当前系统时间
- UVA 815(p99)----Flooded!
- html下拉框select及其选项option的初探
- PHP程序的原子性和PHP的文件锁
- UVA 820(p380)----Internet Bandwidth
- MySQL知识（十三）——索引
- Android Studio——android坐标
- Android 大图查看器
- Nim 游戏
- 从C简单程序的汇编代码入手，以理解计算机工作原理。
- UVA 821(p379)----Page Hopping
- linux(ubuntu)设置以太网络
- React-JSX详解
- UVA 839(p157)----Not so Mobile
