mysql数据库索引

索引

1、索引概述

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

优点

• 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 索引可以帮助服务器避免排序和创建临时表
• 索引可以将随机IO变成顺序IO
• 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
• 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
• 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
• 对于非常小的表，大部分情况下简单的全表扫描更高效；

2、索引的分类

从数据结构的角度对索引进行分类

• B+tree
• Hash
• Full-texts索引

从物理存储的角度对索引进行分类

• 聚簇索引
• 非聚簇索引

从索引字段特性角度分类

• 主键索引
• 唯一索引
• 普通索引
• 前缀索引

从组成索引的字段个数角度分类

• 单列索引
• 联合索引（复合索引）

什么是聚簇索引？

很简单记住一句话：找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

什么是非聚簇索引？

索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引与非聚簇索引的区别

聚簇索引：

• 表数据按顺序存储，即索引顺序和表记录物理存储顺序一致。
• 聚簇索引 叶子节点存储数据行和B-Tree索引。
• 在一个表中只能有一个聚簇索引，因为真实物理存储顺序只能有一种。
• 聚簇索引是稀疏索引，数据页上一级索引存储是页指针，非行指针。

非聚簇索引：

• 表数据存储顺序与索引顺序无关。
• 对于非聚簇索引，叶节点包含行主键值和主键值作为指向行的“指针”，通过二级索引查找，两次B-Tree查找 InnoDB自适应哈希索引能够减少这样重复工作。
• 非聚簇索引是密集索引，在数据页的上一级索引页它为每一个数据行存储一条记录

证明：Myisam引擎不支持HASH索引。

MySQL最常用的存储引擎为InnoDB和MyISAM。它们默认的存储引擎都是B-Tree。他们本身都是不支持hash索引的。但是在建表时给某些字段添加hash索引，或者后期为某表添加hash索引时，如果他们的存储引擎为InnoDB或MyISAM，则sql脚本本身是不会报错的，但是我们会发现，该hash索引字段的index_type仍然为BTREE。如果存储引擎为Memory,因为这个引擎本是就是支持hash索引的，索引其hash索引字段的index_type自然为HASH。

3、Btree索引和Hash索引

1、B+Tree索引

1、B+Tree首先是有序结构，为了不至于树的高度太高，影响查找效率，在叶子节点上存储的不是单个数据，提高了查找效率； 为了更好的支持范围查询，B+树在叶子节点冗余了非叶子节点数据，为了支持翻页，叶子节点之间通过指针相连； 2、B+树算法： 通过继承了B树的特征，通过非叶子节点查询叶子节点获取对应的value，所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。

缺点：因为有冗余节点数据，会比较占内存。

2、Hash索引

1、Hash是k,v形式，通过一个散列函数，能够根据key快速找到value

2、哈希索引就是采用一定的hash算法，把键值换成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需要一次hash算法即可立即定位到相应的位置，速度非常快。

缺点: 因为底层数据结构是散列的，无法进行比较大小，不能进行范围查找

3、B+树索引和hash索引的明显区别：

1、如果是等值查询，那么hash索引有明显的优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个键值是唯一的，如果不唯一，则需要先找到下标位置再链式查找。

2、从示意图可以知道，hash索引无法支持范围查询，因为原先是有序的键值，但是经过hash算法后，有可能变成不连续的，就没有办法利用索引完成范围查询检索数据。

3、同样，hash索引也没办法利用索引完成排序，以及like xxx%这样的模糊查询（范围查询）。

4、hash索引也不支持多列联合索引的最左前缀匹配规则。

5、B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键的情况下，hash索引的效率也是极低的，因为存在hash碰撞问题。

4、各种存储引擎

1、MyISAM存储引擎

默认的存储引擎，提供高速存储和检索，以及全文搜索能力。
不支持事务。表级锁。不能在表损坏后恢复数据。
每个表会生成三个文件(文件名就是表名)：

• frm 表结构；
• MYD 数据；
• MYI 索引。

适合在以下几种情况下使用：

1.做很多count的计算

2.查询非常频繁

2、InnoDB存储引擎

具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。
基于聚簇索引建立，聚簇索引对主键查询有很高的性能。不过它的二级索引(secondary index，非主键索引)中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此表上的索引较多的话，主键应当尽可能的小。
支持事务和外键。行级锁。
适合在以下几种情况下使用：
1.更新和查询都相当的频繁，多重并发
2.要求事务，或者可靠性要求比较高
3.外键约束，MySQL支持外键的存储引擎只有InnoDB
一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择。

3、MEMORY（HEAP）引擎

数据保存在内存中，拥有极高的插入、更新和查询效率。但是不稳定，重启以后数据都会丢失。
不支持事务。支持表级锁，因此并发写入的性能较低。
支持长度不变的数据类型，不支持BLOB或TEXT长度可变的数据类型。VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。
每个表会生成一个.frm文件，该文件只存储表的结构。
支持HASH索引和B-Tree索引，擎默认使用HASH索引。B-Tree索引的优于HASH索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。HASH索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此HASH索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。
在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制MEMORY表的大小。

5、索引的创建

1.普通索引
是最基本的索引，它没有任何限制。它有以下几种创建方式：
（1）直接创建索引

CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引

DROP INDEX index_name ON table

2.唯一索引
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3.主键索引
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5.全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

6、索引的优化

1、SQL语句优化

（1）使用limit对查询结果的记录进行限定
（2）避免select *，将需要查找的字段列出来
（3）使用连接（join）来代替子查询
（4）拆分大的delete或insert语句

2、选择合适的数据类型

（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

3、选择合适的索引列

（1）查询频繁的列，在where，group by，order by，on从句中出现的列
（2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

4、使用命令分析

（1）SHOW查看状态
1.显示状态信息

mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';

session（默认）：取出当前窗口的执行
global：从mysql启动到现在
（a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）

mysql> SHOW STATUS LIKE 'com_select';

（b）查看连接数(登录次数)

mysql> SHOW STATUS LIKE 'connections';

（c）数据库运行时间

mysql> SHOW STATUS LIKE 'uptime';

（d）查看慢查询次数

mysql> SHOW STATUS LIKE 'slow_queries';

（e）查看索引使用的情况：

mysql> SHOW STATUS LIKE 'handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next：这个值越高，说明查询低效。
2.显示系统变量

mysql> SHOW VARIABLES LIKE '%Variables_name%';

3.显示InnoDB存储引擎的状态

mysql> SHOW ENGINE INNODB STATUS;

（2）EXPLAIN分析查询

mysql> EXPLAIN SELECT column_name FROM table_name;

• explain查询sql执行计划，各列含义：

• table：表名；

• type：连接的类型

  -const：主键、索引；
  -eq_reg：主键、索引的范围查找；
  -ref：连接的查找（join）
  -range：索引的范围查找；
  -index：索引的扫描；
  -all：全表扫描；

• possible_keys：可能用到的索引；

• key：实际使用的索引；

• key_len：索引的长度，越短越好；

• ref：索引的哪一列被使用了，常数较好；

• rows：mysql认为必须检查的用来返回请求数据的行数；

• extra：using filesort、using temporary（常出现在使用order by时）时需要优化。
  -Using filesort 额外排序。看到这个的时候，查询就需要优化了
  -Using temporary 使用了临时表。看到这个的时候，也需要优化

7、设计索引的原则

• 最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
  使用惟一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
• 如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
• 利用最左前缀。
• 不要过度索引。
• **对于 InnoDB 存储引擎的表，记录默认会按照一定的顺序保存，**对于InnoDB的表，最好手动创建主键。如果没有主键，但有唯一索引，那么就按唯一索引顺序进行排序。如果不指定主键和唯一索引，系统会默认创建一个内部列当做主键,在查询时，主键和内列的访问是最快的，所以主键最好是经常当做条件的列。另外，InnoDB的普通索引都会保存主键的值，所以主键的数据类型越短越好
• 如果设置组合索引，在进行数据检索时，检索条件只有包含其第一索引列的情况才会使用到该索引
• 如果存在大量重复值时，需要进行范围查询，如: =、>、〈等和ORDER BY、GROUP BY发生的列可以建立聚簇索引

InnoDB 表的普通索引都会保存主键的键值，**所以主键要尽可能选择较短的数据类型，**可以有效地减少索引的磁盘占用，提高索引的缓存效果。

8、设计索引的误区

• 不是所有表都需要索引

  代码表、配置表、数据量小的表等，除了主键外，创建索引没有什么意义，索引扫描和全表扫描没有太大提升。而数据量大的表，每一次全表扫描，对系统都是非常大的冲击，所以大表的删改查操作，要尽可能通过索引的方式进行。可以对经常使用SQL语句进行分析，提炼出经常做条件的列创建索引

• 不要过度索引

  索引的不是创建越多越好，索引也是要占用存储空间的，并会降低写入数据的性能。在修改表数据的时候，索引必须进行更新，有时候还会重构，因此，索引越多，插入和更新操作所花费的时间也越多

• 尽量不要创建低选择度的索引

  对于选择性低并且数据分布平均的列，因为过滤后的结果集较大，创建索引效果通常不好

9、索引创建的一般步骤

• 整理表上的所有SQL，重点包括SELECT、UPDATE、DELETE操作中的WHERE子句所用到的列、关联查询的关联条件等
• 整理所有查询SQL的预期执行率
• 整理列的选择度，列的值重复度越低，选择度越好·遵循上述原则，为表选择合适的主键
• 优先给执行率最高的列创建索引
• 按执行率排序，依次检查是否需要为每个SQL创建索引
• 索引合并，利用复合索引降低总索引数量。充分利用最左侧原则，让索引尽可能被复用
• 上线之后，通过慢查询分析、执行计划分析、索引使用统计来做出调整