Mysql 索引学习总结

是什么

索引（Index）是帮助mysql高效获取数据的数据结构。

本质：数据结构，可以理解为排好序的快速查找数据结构

在数据之外，数据库还维护着满足特定查找的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这样的数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上。

优势

• 提高数据库索引效率，减低数据库的IO成本
• 减低数据库排序的成本，减低CPU的消耗

劣势

• 创建索引和维护索引要耗费时间

• 索引也是一张表也会占用一定的空间

• 会降低更新表的速度，如对表的INSERT、UPDATE、DELETE等。

因为更新表时，mysql不仅要保存数据，还要保存索引文件每次更新索引列的字段。

类型

我们平时说的索引，如果没有特别指明，都是指B树结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是用B+树索引，简称索引。除了B+树外类似索引外，还有哈希索引等等

创建索引方式

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 ,
    `name` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (name(length))
)

• 查询索引

show index from table_name

• 删除索引

DROP INDEX [index_name] ON table

普通索引

即一个索引只包含单个列，一个表可以有多个单列索引

CREATE INDEX [index_name] ON table(column(length))

如果char,varchar类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定长度。

唯一索引

索引列的指必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

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

主键索引

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

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

组合索引

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

ALTER TABLE `table` ADD INDEX [index_name] (name,city,age); 

全文索引

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

CREATE FULLTEXT INDEX [index_name] ON table(column)

索引结构

btree

在myisam里的形式和innodb稍有不同

在innodb里面有两种形态:其一是primary key形态其leafnode里存放的是数据.而且不仅存放了索引键的数据,还存放了其他字段的数据.其二是secondary index,其leafnode和普通的b-tree差不多,只是还存放了指向主键的信息. 而在myisam里,主键和其他的并没有太大区别.不过和innodb不太一样的地方是,在myisam里,leaf node里存放的不是主键的信息,存的是指向数据文件里的对应数据行的信息.

hash

• 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
• 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
• 只有memory和ndb cluster支持这种索引

full-text

full-text在mysql里仅有myisam支持它,而且支持full-text的字段只有char,varchar,以及text数据类型,full-text主要是用来代替like "%***%"效率低下的问题的.

R-Tree

r-tree在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有myisam,bdb,innodb,ndb,archive几种.相对于b-tree,r-tree的优势在于范围查找.

默认排序

数字索引：默认升序排序

文字索引：根据ASCII，汉子的机内码

那些情况需要建立索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发下倾向创建组合索引
• 查询中排列的字段
• 查询统计或分组的字段

那些情况不要创建索引

• 表记录太少
• 频繁更新的字段不适合建立索引
• where条件里用不到的字段不适合建立索引
• 数据列中包含很多重复的内容。

注意事项

• 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

• 索引列排序

查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

• like语句操作

一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

• 不要在列上进行运算

这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

• 不使用not in和<>操作