MySQL数据库索引

特别提示：本人博客部分有参考网络其他博客，但均是本人亲手编写过并验证通过。如发现博客有错误，请及时提出以免误导其他人，谢谢！欢迎转载，但记得标明文章出处：http://www.cnblogs.com/mao2080/

1、索引简介

　　索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

2、索引类型

　　1、BTREE

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

　　2、HASH

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

3、索引种类

• 　　普通索引：仅加速查询
• 　　唯一索引：加速查询 + 列值唯一（可以有null）
• 　　主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 　　组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 　　全文索引：对文本的内容进行分词，进行搜索

4、操作索引

　　1、创建索引

操作名称	SQL语句
创建普通索引	create index index_name on table_name(col_name);
创建唯一索引	create unique index index_name on table_name(col_name);
创建普通组合索引	create index index_name on table_name(col_name_1,col_name_2);
创建唯一组合索引	create unique index index_name on table_name(col_name_1,col_name_2);
通过修改表结构创建索引	alter table table_name add index index_name(col_name);
创建表时直接指定索引	CREATE TABLE table_name ( ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name) );

　　2、删除索引

操作名称	SQL语句
直接删除索引	drop index index_name on table_name;
通过修改表结构删除索引	alter table table_name drop index index_name;

5、使用时机

　　MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引

6、优点缺点

　　1、优点：

　　　　MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

　　2、缺点：

　　　　1、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

　　　　2、建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

7、注意事项

　　1、索引不会包含有NULL值的列

　　　　只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

　　2、使用短索引

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

　　3、索引列排序

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

　　4、like语句操作

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

　　5、不要在列上进行运算

　　　　例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。

8、参考资料

　　https://www.cnblogs.com/luyucheng/p/6289714.html

　　https://www.cnblogs.com/wicub/p/5898286.html

　　https://blog.csdn.net/liutong123987/article/details/79384395

　　https://blog.csdn.net/qq_32862391/article/details/54943993

　　https://www.cnblogs.com/luyucheng/p/6289048.html