是什么
索引（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和<>操作
网友评论