您现在的位置是：首页 > 数据库 > MySQLMySQL

MySQL索引

第十三双眼睛2019-07-21【MySQL】人已围观

简介索引用于快速找出在某个列中有一特定值的行，不使用索引，必须从第一行记录开始读完整个表，直到找出相关的行，表越大，花费的时间越多，如果表中查询的列有一个索引，那就能够快速的达到某个位置去搜寻数据文件，而不必查询所有数据，本章介绍与索引相关的内容，包括索引的含义，特点，分类，设计原则和如何创建和删除索引

索引简介
索引是对数据库表中一列或者多列的值进行排序的一种数据结构，使用索引可以提高数据库中特定数据的查询速度。
索引是一种单独的，存储在磁盘上数据结构，他们包含着对数据表里所有数据的引用指针，也就是地址，使用索引用于快速的找出在某个或者多个列中一特定值的行，MySQL中所有的类型都可以被索引，对相关列使用索引是提高查询速度的最佳途径
例如：在数据库中有2万条数据，如果要执行SELECT * FROM TABLENAME WHERE ID=10000,如果没有索引，必须遍历整个表，知道id=10000这一行，如过有索引，则直接在索引里找到id=10000数据的地址，可见，索引的建立可以提高数据库的查询速度。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，且每种存储引擎也不支持所有类型的索引，根据存储引擎定义的每个表的最大索引数和最大索引长度，所有存储引擎支持每个表至多16个索引，从索引长度至少256各字节，大多数存储引擎有更高的限制，MySQL中有两种索引，BETREE,HASH,具体和表的存储引擎相关，MyISM和InnoDB存储引擎只支持BTREE索引，MEMORY和HEAP存储引擎可以支持HASH和BTREE索引

mysql常用的索引：
B+Tree索引：使用于范围查找
Hash索引：适用于精确查找

索引的优点主要有以下几条
1通过创建唯一索引，可以保证数据库中每一行的数据的唯一性
2可以大大加快数据的查询速度，这是创建索引的主要原因
3在实现数据的参考完整性方面，可以加速表和表之间的连接
4在使用分组和排序子句进行数据查询的时候，可以显著减少查询中分组和排序的时间

索引的缺点主要表现在以下几个方面
1创建索引和维护索引要耗费时间，随着数据量的增加，维护索引的时间也会增加
2索引需要占用磁盘空间，除了数据表占数据空间之外，每一个索引页会占用一定的物理空间，如果有大量的索引，索引文件可能占用比数据文件更大的空间
3对表中的数据进行增加，删除和修改的时候，索引也要动态维护，这样就降低了数据的更新速度。

普通索引和唯一索引
普通索引是MySQL中基本索引类型，允许在定义索引的列中插入空值和重复值
唯一索引，索引列的值必须唯一，但允许有空值，所以空值只能有一个，如果是组合索引，则列值得组合必须唯一，主键索引是一种特殊得唯一索引，不允许有空值

单列索引和组合索引
单列索引即只包含一个列，一个表可以有多个单列索引
组合索引指在多个字段上创建得索引，只有在查询条件中使用了这些字段得左边字段时，索引才会被使用

全文索引
全文索引类型为FULLTEXT,在定义索引得列上支持值得全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在VARCHAR，CHAR,或者TEXT类型上创建，MySQL中只有MyISM存储引擎支持全文索引

空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY,POINT,LINESTRING,POLYGON,MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引，创建空间索引的列，必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISM的表中创建

索引的设计元则
所以设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍，高效的索引对于获得良好的性能非常重要，设计索引时，应考虑以下元则，
1索引并非越多越好，索引太多，会占用磁盘空间，会影响INSERT UPDATE DELETE,等语句的性能，因为表中的数据在更改时，也会进行索引的调整和更新
2避免对经常更新的表进行过多的索引，并且，索引中的列尽可能少，对于经常查询的列应该添加索引，
3数据量小的表最好不要进行索引，由于数据少，查询数据的时间可能比查询索引的时间还要短
4在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引，比如在学生的性别字段上，
5当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引需要能确保定义的列的数据的完整性，以提高查询速度，
6在频繁进行排序分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引

MySQL支持多种方法创建索引，可以在定义表的时候指定索引列，也可以使用ALTER关键字在定义完表之后添加索引，或者使用CREATE INDEX语句创建索引
创建表的时候创建索引
使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束，外键约束，或者唯一性约束，不论创建那种约束，在定义约束的同时相当于在指定列上创建了一个索引，基本语法如下：
CREATE TABLE TABLENAME(COLUMN NAME DATA_TYPE[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEX_NAME|] COLUMN[LENGTH])[ASC|DESC]
UNIQUE,FULLTEXT,SPATIAL为可选参数，表示唯一索引，全文索引，空间索引，INDEX与KEY相同，用来指定创建索引，COLUMN指定需要创建索引的列，INDEX_NAME为索引的名字，为可选参数，如果不指定，MySQL默认COLUMN默认COLUMN_NAME为索引值，LENGTH为可选参数，表示索引长度，只有字符串类型的字段才能指定索引长度，ASC或者DESC指定升序或者降序的索引值存储

创建普通索引
最基本类型的索引，没有唯一性之类的限制，其作用只是加快查询速度
如：
CREATE TABLE BOOK(
bookid int not null,
bookname varchar(20) not null,
year_publication year not null
index(year_publication)
)
上面的建表语句执行之后，就会建立一个索引，名字为year_publication

创建唯一索引
创建唯一索引的主要原因是，减少查询索引列操作的时间，尤其是对比较庞大的数据表，它与前面的普通索引类似，不同的就是，索引列的值必须唯一，允许有空值，如果是组合索引，则组合必须唯一
如：创建一个表，在表中的ID字段上使用UNIQUE关键字创建唯一索引

创建单列索引
单列所用是在数据库表中的某一列上创建的索引，一个表中可以创建多个单列索引

创建组合索引
组合索引是在多个列上创建一个索引，创建的语法如下：
CREATE TABLE TAB1(
ID INT NOT NULL ,
NAME VARCHAR(20) NOT NULL
INDEX MULTIINDEX(ID,NAME(10))
)
组合索引可以起几个索引的作用，但是并不是随便查询哪个字段都可以使用索引，而是遵循最左前缀，利用索引中最左边的列集来匹配，如果不按照构成索引最左面的前缀，则不能使用索引，

创建全文索引
FULLTEXT可以用于全文搜索，只有MyISM存储引擎支持，并且只为CHAR,VARCHAR,TEXT列创建索引，索引总是对整个列进行，不支持局部索引
创建的语法如下：CREATE TABLE TAB1(
ID INT NOT NULL,
NAME VARCHAR(20),
AGE IINT NOT NULL,
INFO VARCHAR(255),
FULLTEXT INDEX FULLTEXTINDEX(INFO)
)ENGINE=MyISM
全文索引适用于大型数据集，对于小的数据集，它的用处比较小

创建空间索引
空间索引必须在MyISM类型的表中创建，且空间类型的字段必须为非空

在已经存在的表上创建索引
在已经存在的表中创建索引，可以使用ALTER TABLE 语句或者CREATE INDEX语句来创建索引
使用ALTER TABLE语句来创建索引的语法如下：
ALTER TABLE TABLENAME ADD 索引类型索引名要创建索引的字段如：
ALTER TABLE BOOK ADD INDEX BOOKNAMEINDEX (BOOKNAME(30));

使用CREATE INDEX语句来创建索引
CREATE INDEX语句可以在已经存在的表上创建索引，具体语法如下：
CREATE INDEX 索引类型 INDEX INDEX_NAME ONT TABLENAME COLUMNNAME(LENGTH)
CREATE INDEX BOOKNAME OB BOOK(BOOKNAME)

删除索引
删除索引使用ALTER TABLE DROP 或者DROP INDEX 语句，两者实现的功能都一样
使用ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;;
使用DROP INDEX删除索引
DROP INDEX INDEX_NAME ON TABLE_NAME;

数据库的索引对数据库性能如此重要，如何使用它
为数据库选择正确的索引是一个复杂的工程，如果索引列比较少，则需要的磁盘空间和维护的开销都比较少，如果在一个大表上创建了多中组合索引，索引文件也会很庞大，另一方面，索引较多，可以覆盖更多的查询，可以添加删除，修改索引，而不影响数据库的架构，或应用程序设计，因此，应尝试多个不同的索引从而建立最优的索引

尽量使用短索引
对字符串类型的列进行索引，如果可能应该指定一个前缀长度，如，有一个字段，在前10个或者前30个字符内，多数值是一样的，则不需要对整个列进行索引，短索引不仅可以提高查询速度，而且可以节省磁盘空间，减少IO操作

Hash索引特点
Hash索引只包含hash值和行指针
只支持精确匹配，不支持范围查询，模糊查询及排序
hash取值速度非常快，但是索引选择性低时不建议使用
mysql目前只有memory引擎支持hash索引

InnoDb中的hash索引
InnoDb只支持显示创建B-tree索引
数据精准匹配时，mysql会自动生成hashCode，并存入缓存

索引使用注意
索引选择性好,使用索引
索引选择性差,全表扫描
<>/not in 无法使用索引
where 子句跳过左侧索引列,直接查询右侧索引字段，不会使用索引
复合索引查询条件必须包含左侧列，直接书写右侧列将导致数据无法查询
对索引列进行计算或者使用函数,将会使索引失效
当排序出现了索引左侧列,则允许使用索引排序
左侧字段单字段排序时,索引支持升降序
在多字段情况下,左侧字段必须是升序,且顺序不允许打乱

删除冗余索引
pt-duplicate-key-checker是percona-toolkit工具包中的实用组件，它可以帮助你监测表中的重复的索引或者主键，
下载地址：https://www.percona.com/software/database-tools/percona-toolkit
用以下语句查询索引的使用情况：
#索引使用情况

SELECT
   object_type,object_schema,object_name,index_name,
   count_read,count_fetch,   count_insert,
   count_update,count_delete
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY sum_timer_wait desc;

减少表与索引的碎片
重新统计索引信息
analyze table t_content
optimize 优化表空间,释放表空间
锁表,一定要在维护期间,否则会造成IO阻塞
optimize table t_content

Tags：MySQL 数据库 MySQL数据库索引

很赞哦！ ()

上一篇：MySQL插入更新删除数据

下一篇：存储过程与函数