索引(mysql相关知识)

一、什么是索引

　　索引是一种高效获取数据的数据结构。

二、索引的类型

　　FULLTEXT，（HASH，BTREE[mysql主要使用的两种]），RTREE。

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

　　2.2. HASH
　　由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
　　HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

　　2.3. BTREE
　　BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

　　2.4. RTREE
　　RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
　　相对于BTREE，RTREE的优势在于范围查找。

三、索引种类

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

 四、索引使用

　　4.1 创建索引

1 --创建普通索引CREATE INDEX index_name ON table_name(col_name);
2 --创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);
3 --创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
4 --创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

　　4.2 通过修改表结构创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

　　4.3 创建表时直接指定索引

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

　　4.4  删除索引

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

　　4.5 其它命令

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

五、索引失效的原因

　　1.全值匹配，相当于索引没有使用。

　　2.未满足最佳前缀法则也可能造成索引失效。

　　3.在索引上做关于（计算、函数、（自动or手动）类型转换），会造成索引失效而导致全表扫描。

　　4.mysql在使用不等于（<>，!=）的时候无法使用索引而导致全表扫描。

　　5.is null ，is not null 也无法使用索引。

　　6.like 以通配符开关（‘%abc‘)会导致索引失效而全表扫描。

　　7.字符串不加单引号索引会失效。

　　8.少用or，用or连接时会导致索引失效。

　　9.使用select * 查询，尽量使用覆盖索引。

六、mysql索引规约（引自阿里规范）

　　1. 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引（说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，

　　　　但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。）

mysql索引