MySQL索引

---

一、MySQL数据引擎

数据库引擎是数据库用于存储、处理和保护数据的核心服务，不同的数据库引擎有其各自的特点，如存储机制、索引技巧、主键的处理、锁的粒度等特点便随着引擎的不同而变化。因此，针对自己项目特点选择合适的数据库引擎可以改善服务器端存储性能。

MySQL最常用数据引擎有：innodb 和MyISAM

SHOW ENGINES命令来查看MySQL使用的引擎

• 在MySQL中不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎
• support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎
  简单理解：存储引擎就是把数据存储到磁盘上的规则,数据的存储方式,针对表

InnoDB存储引擎

InnoDB是一个事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种隔离级别，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发情形）及外键支持（所有数据库引擎中独一份，仅有它支持外键）

支持事务：支持4个事务隔离界别，支持多版本读
行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
支持外键约束，不支持全文索引(5.5之前)，以后的都支持了

InnoDB缺点

• 该引擎不支持FULLTEXT类型的索引
• 没有保存表的行数，在执行select count(*) from 表名时，需要遍历扫描全表

InnoDB适合

• 可靠性要求比较高或者要求事务，适合处理多重并发的更新请求
• 支持事务和外键约束
• 表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定数据引擎的创建
• 可以从灾难中恢复（通过bin-log日志等）
• 支持自动增加列属性auto_increment

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务

MyIsam引擎是MySQL主流引擎之一，但它相比起InnoDB，没有提供对数据库事务的支持，不支持细粒度的锁（行锁）及外键，当表Insert与update时需要锁定整个表，因此效率会低一些，在高并发时可能会遇到瓶颈，但MyIsam引擎独立于操作系统，可以在windows及linux上使用。

MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。

MyISAM缺点

• 不能在表损坏后恢复数据

MyISAM适合

• 极度强调快速读取
• 表中自动存储了表的行数，需要时直接获取即可，即支持快速count(*)操作
• 插入不频繁，查询非常频繁
• 没有事务、外键功能及需要对整个表加锁的情形

二、MySQL索引

数据库中提供了存储共享数据的功能，但是更重要的是快速高效的获取指定的数据。

在没有索引的情况下，如果要寻找特定行，数据库可能要遍历整个数据库，使用索引后，数据库可以根据索引找出这一行，极大提高查询效率。

MySQL官方对索引的定义为：索引Index是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。
数据库查询是数据库的最主要功能之一，希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。

MySQL目前主要有普通索引、唯一索引、主键索引、组合索引和全文索引共5种索引类型。

普通索引和唯一索引、主键索引

• 普通索引和唯一索引是根据索引的功能来划分。
• 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
• 唯一索引的索引列的值必须唯一，但允许空值。如果是组合索引，则列的值得纸盒必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
• 主键索引，一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引

单列索引和组合索引

• 单列索引和组合索引的划分是根据创建索引时所引用的列的数量来划分。
• 单列索引是只一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引指在表的多个字段组合上创建索引，但只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。
• 组合索引可以起到多个索引的作用，但是使用时并不是随便哪个字段都可以使用索引的，而是遵从“最左前缀”：利用索引中最左边的列集列匹配行，这样的列集称为最左前缀。

全文索引

• 全文索引类型为FULLTEXT，在定义索引的列上至此值需得全文查找，允许在这些索引列上插入空值和重复值

• like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。

• 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

• 在具体的应用中，一般不会使用数据库管理系统的全文索引，而是引入ElasticSearch这种NoSQL数据库实现

空间索引

空间索引可以用作地理数据存储，通过R树来实现。空间索引会从所有维度来索引数据，使得空间搜索变得高效。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据。

索引的优缺点

优点：

• 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性

• 建立索引可以大大提高检索的数据,以及减少表的检索行数

• 在表连接的连接条件 可以加速表与表直接的相连

• 在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)

• 建立索引,在查询中使用索引 可以提高性能，但是会影响修改的性能

索引的缺点

• 在创建索引和维护索引 会耗费时间,随着数据量的增加而增加，所以一般建议一个表上创建的索引数不要超过6个
• 索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
• 当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)

三、创建及删除索引

创建索引

创建索引的方式有直接创建、修改表添加和创建时直接创建三种

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name 
(column(length),…) [ASC|DESC]
-- 普通索引
create index bknameIndex on book(bookname) ;

-- 唯一索引
create unique index unique_Index on book(bookId) ;

--单列索引
create index single_Index on book(comment(10)) ;

-- 多列索引
create index mutil_Index on book(authors(20) , info(20)) ;

--全文索引
create fulltext index fulltext_Index on book(info) ;

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 
index_name (column(length),…) [ASC|DESC]

-- 普通索引
alter table book add index bkName(bookname(30)) ;

--唯一索引
alter table book add unique index uniqueIndex(bookId) ;

--单列索引
alter table book add index signalIndex2 on(comment(50)) ;

--多列索引
alter table book add index mutilIndex (authors(30) , info(50)) ;

--创建全文索引
alter table book add fulltext index fulltextIndex (info) ;

CREATE TABLE table_name (
……（创建字段和约束）,
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [INDEX|KEY] [index_name] 
(col_name [length]) [ASC|DESC]
)

-- 普通索引
create table book(
     ……
	index pub_index(year_publication)
);

-- 唯一索引
create table book(
    ……
    unique index pub_index(bookid)
);

-- 单列索引
create table book(
    ……
    index single_index(authors)
);

-- 多列索引
create table book(
     ……
     index mutil_index(bookid, authors)
);

-- 全文索引,需要修改表的存储引擎为MyISAM
create table book(
     ……
     fulltext index fulltext_index(info)
);

删除索引

使用ALTER TABLE删除索引
有AUTO_INCREMENT约束的字段的唯一索引不能被删除
删除book表中名为fulltext_Index的索引alter table book drop index fulltext_Index ;

 ALTER TABLE table_name DROP INDEX index_name ;

使用DROP INDEX删除索引
删除book表中名为fulltext_Index的索引drop index mutil_index on book ;

DROP INDEX index_name ON table_name ;

不适合创建索引的数据文件

• 二进制多媒体数据
• 超大文本数据
• web应用中活跃性大但是数据量不是很大的数据