什么是索引？MySQL常见的几种索引类型和原理

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。更通俗的说，数据库索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。虽然索引很大程度的提高了查询速度，但是却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件，并且建立索引会占用磁盘空间的索引文件。

常见的索引类型

MySQL常见有以下几种索引类型：FULLTEXT，NORMAL，SPATIDX，UNIQUE。

1、FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2、NORMAL即为普通索引，使用字段关键字建立的索引，主要是提高查询速度。

3、SPATIDX即为空间索引，目前只有MyISAM引擎支持并且空间类型的字段不能为空。

4、UNIQUE即为唯一索引，字段数据是唯一的，数据内容里面能否为 null,在一张表里面，是可以添加多个唯一索引。 

在比较老的版本中，只有MyISAM引擎支持全文索引，在INNODB5.6后引擎也支持全文索引，在MySQL中全文索引不支持中文。我们一般使用SPATIDX集合。CORESEEK来实现中文的全文索引。

索引的分类　　

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引。索引我们分为四类：单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引。

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：索引列中的值必须是唯一的，但是允许为空值，

主键索引：是一种特殊的唯一索引，不允许有空值。

组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个靓仔，靓女 ..." 通过靓仔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思，如果感兴趣进一步深入使用它，那么看下面测试该索引时，会给出一个博文，供大家参考。

空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。

操作索引

1. 创建索引

--创建普通索引

CREATE INDEX index_name ON table_name(col_name);

--创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

--创建普通组合索引

CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

--创建唯一组合索引

CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2. 通过修改表结构创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

3. 创建表时直接指定索引

CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

4. 删除索引

--直接删除索引
DROP INDEX index_name ON table_name;
--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

5. 其它相关命令

--查看表结构
desc table_name;
--查看生成表的SQL
show create table table_name;
--查看索引
show index from  table_name;
--查看执行时间
set profiling = 1;
SQL...
show profiles;

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10S。

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化，如下SQL语句：

explain select * from table_name;

查看慢日志参数：

--查询配置命令
show variables like '%query%';
--当前配置参数
binlog_rows_query_log_events    OFF
ft_query_expansion_limit    20
have_query_cache    YES
--时间限制，超过此时间，则记录
long_query_time    10.000000
query_alloc_block_size    8192
query_cache_limit    1048576
query_cache_min_res_unit    4096
query_cache_size    1048576
query_cache_type    OFF
query_cache_wlock_invalidate    OFF
query_prealloc_size    8192
--是否开启慢日志记录
slow_query_log    OFF
--日志文件
slow_query_log_file    /usr/local/mysql/logs/yoodb-log.log

查看MySQL慢日志命令如下：

mysqldumpslow -s at -a  /usr/local/mysql/yoodb-slow.log

参数说明：

-s是order的顺序；al 平均锁定时间；ar 平均返回记录时间；at 平均查询时间（默认）；c 计数；l 锁定时间；r 返回记录；t 查询时间；-t是top n的意思，即为返回前面多少条的数据；-g后边可以写一个正则匹配模式，大小写不敏感的。

为了使索引的效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引，建议如下：

1）选择唯一性索引

2）经常需要排序、分组和联合操作的字段建立索引

3）经常作为查询条件的字段建立索引

4）限制索引的数目

5）尽量使用数据量少的索引

6）尽量使用前缀来索引

7）删除不再使用或者很少使用的索引

索引使用注意事项

1）只要列中包含NULL值将不会被包含在索引中，组合索引只要有一列含有NULL值，那么这一列对于组合索引就是无效的，所以我们在设计数据库的时候最好不要让字段的默认值为NULL;

2）使用短索引

如果可能应该给索引指定一个长度，例如：一个VARCHAR(255)的列，但真实储存的数据只有20位的话，在创建索引时应指定索引的长度为20，而不是默认不写。

推荐阅读

更多推荐↓↓↓

 

关注微信公众号“Java精选”（w_z90110），回复关键字领取资料：如Hadoop，Dubbo，CAS源码等等，免费领取资料视频和项目。 

涵盖：程序人生、搞笑视频、算法与数据结构、黑客技术与网络安全、前端开发、Java、Python、Redis缓存、Spring源码、各大主流框架、Web开发、大数据技术、Storm、Hadoop、MapReduce、Spark、elasticsearch、单点登录统一认证、分布式框架、集群、安卓开发、iOS开发、C/C++、.NET、Linux、Mysql、Oracle、NoSQL非关系型数据库、运维等。