一、MySQL 索引介绍
1、什么是索引
索引(Index)是帮助MySQL高效获取数据的数据结构。其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录、类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL一般在300万条记录左右性能会开始逐渐下降(官方文档是500~800w记录)，在大数据量下建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。
2、索引有什么作用
传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数据从头到尾遍历一遍。在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，找到相应的键从而获取数据。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
加快数据的查询速度
唯一索引，可以保证数据库表中每一行数据的唯一性
在实现数据的参考完整性方面，可以加速表和表之间的连接
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间
3、mysql索引类型
MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
4、创建索引的原则
查询的结果集：超过了总数行数25%，优化器觉得就没有必要走索引了。
越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。
尽量避免NULL：应该指定列为NOT nuLL,在MySQL中， 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。
最左前缀：索引的最左前缀和和B+Tree中的“最左前缀原理”有关，如：设置了组合索引三个字段，那么以下3中情况可以使用索引：a1，，，其他情况，，a2，a3等都是不能使用索引的。根据最左前缀原则，我们一般把排序分组频率最高的字段放在最左边，以此类推。
带索引的模糊查询优化：使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。这种情况，只能使用全文索引来进行优化(下文有讲到全文索引)。为检索的条件构建全文索引，然后使用SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘aaa’);
使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的字段，如果在前20个字符内可以判定多数值是惟一的，勿要对整个字段进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5、适合创建索引的字段特征
表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询 。
数据量超过300万条记录的表应该有索引。
经常与其他表进行连接的表，查询中与其他表关联的字段应建立索引，外键关系因建立索引；经常连接查询，需要有索引
经常出现在Where子句中的字段，为了加快判断速度，特别是大表的字段，应该建立索引，一般用在select ……where f1 and f2 查询建立索引时，我们在f1或者f2上建立索引是没用的。只有两个字段使用联合索引才能有用，高并发条件下倾向组合索引。
经常用在排序和分组的字段上，因为索引已经排序。
数据量大，用于聚合函数的字段可以建立索引。
经常用在范围内搜索的字段上创建索引，因为索引已经排序了，其指定的范围是连续的
6、索引有什么缺点
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。但过多的使用索引将会造成滥用。
索引也会有它的缺点：
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
7、不适合创建索引的场景
表记录太少不要建立索引，只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
对于那些在查询中很少使用或者参考的列不应该创建索引。由于增加了索引，反而降低了系统的维护速度和增大了空间需求，但查询不常使用，对查询优化也没起到作用。
对于那些只有很少数据值的列不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。增加索引，并不能明显加快检索速度，反而增加空间需求和减慢更新速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
更新非常频繁(经常增删改)的字段不适合作为索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。
唯一性太差(有大量重复值)的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
尽量避免NULL：应该指定列为NOT nuLL,在MySQL中， 含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。注意：如果字段是索引,使用 column_name is null 还是会使用索引的 select * from t1 where name is null。
不会出现在where条件中的字段不该建立索引。
8、导致索引失效的情况
在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
在索引的字段上使用表达式运算或者函数会使索引失效；其它通配符同样，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
在查询条件中使用不等于，包括、！=、<> 往往会导致索引失效(主键索引除外)。其中整数类型的索引使用 符号不会使索引失效。非主键索引避免使用 <> ，not in - 原因：不走索引。
在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
字符串不加引号(类型不一致)会导致索引失效。如：字段a为字符串类型，使用WHERE a=99 则会导致失败，应该改为WHERE a='99'。
在查询条件中使用OR连接多个条件会导致索引失效(OR链接的每个条件都加上索引不会失效)，可以改为两次查询，然后用UNION [ALL]连接起来。
如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。当排序的是主键索引时select * 不会导致索引失效。
尽量不要包括多字段排序，如果一定要，最好为这队字段构建组合索引。
9、总结注意事项
索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新
避免对经常更新的表设计过多的索引，并且索引中的列尽可能要少，而对经常用于查询的字 段应该创建索引，但要避免添加不必要的字段
数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引时间还要短，索引可能不会产生优化效果
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值较少的列上不要建立索引，比如性别字段只有男和女，就没必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度
在频繁排序或分组(即group by或order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引
二、索引的查看、创建与删除
·查看索引
SHOW INDEX FROM tablename; \G
SHOW INDEXES FROM tablename; \G
SHOW KEYS FROM tablename; \G
·查看索引使用情况
explain select ......from.....; \G
mysql> explain select * from sales2 where company_id = 2006\G;*************************** 1. row ***************************id: 1select_type: SIMPLEtable: sales2type: refpossible_keys: ind_sales2_companyid_moneys208key: ind_sales2_companyid_moneyskey_len: 5ref: constrows: 1Extra: Using where1 row in set (0.00 sec)·创建与删除索引
1、普通索引
创建索引时，如果字段是CHAR，VARCHAR类型，length可以小于字段实际长度(表示索引字段的子部分)；如果是BLOB和TEXT类型，必须指定 length，同时只有字符串类型的字段才能指定索引长度。
普通索引，它没有任何限制。创建普通索引的方法：
-- ①新建普通索引
CREATE INDEX indexName ON mytable(column_list(length));
#解释： 创建 索引 索引名 在表上(创建索引的字段(字段长度[可以省略]))
-- ②修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(column_list)
-- ③创建表的时候直接指
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (column_list(length)) ); -- ④删除索引的语法
DROP INDEX [indexName] ON mytable;
alter table mytable drop index indexName;
2、唯一索引
创建索引时，如果字段是CHAR，VARCHAR类型，length可以小于字段实际长度(表示索引字段的子部分)；如果是BLOB和TEXT类型，必须指定 length，同时只有字符串类型的字段才能指定索引长度。
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
-- ①新建唯一索引
CREATE UNIQUE INDEX indexName ON mytable(column_list(length))
-- ②修改表结构(添加唯一索引)
ALTER table mytable ADD UNIQUE [indexName] (column_list(length))
-- ③创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (column_list(length))); -- ④删除索引的语法
DROP INDEX [indexName] ON mytable;
alter table mytable drop index indexName;
3、主键索引
主键其实就是主键索引的简称
alert table tablename add primary key (
字段名); #添加主键索引ALTER TABLE tablename DROP PRIMARY KEY; #删除主键[索引]
4、全文索引
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。索引总是对整个列进行，不支持局部索引。全文索引非常适合大型数据库，而对于小的数据集，它的用处可能比较小。全文索引效率远高于like的模糊查找。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); #该语句指定了索引为 FULLTEXT
CREATE INDEX indexName ON mytable(column_list); #该语句指定了索引为 FULLTEXT
DROP INDEX [indexName] ON mytable; #删除索引
alter table mytable drop index indexName; #删除索引
案例：CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR (200), body TEXT, FULLTEXT (title, body) WITH PARSER ngram) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表'; #使用自然语言搜索如下:mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN NATURAL LANGUAGE MODE);+----+-----------------+-------------------------+| id | title | body |+----+-----------------+-------------------------+| 1 | 弘扬正能量 | 贯彻党的18大精神 |+----+-----------------+-------------------------+1 row in set (0.00 sec) mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');+----+-----------------+-------------------------+| id | title | body |+----+-----------------+-------------------------+| 1 | 弘扬正能量 | 贯彻党的18大精神 |+----+-----------------+-------------------------+1 row in set (0.00 sec)#可以看到,搜索结果命中了一条,且在不指定搜索模式的情况下,默认模式为自然语言搜索. #使用boolean搜索如下:mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神' IN BOOLEAN MODE);+----+-----------------+-------------------------+| id | title | body |+----+-----------------+-------------------------+| 1 | 弘扬正能量 | 贯彻党的18大精神 |+----+-----------------+-------------------------+1 row in set (0.00 sec) mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神 -贯彻' IN BOOLEAN MODE);Empty set (0.01 sec)# 当搜索必须命中精神时,命中了一条数据,当在加上不能包含贯彻的时候,无命中结果.5、组合索引
组合索引是在多个字段上创建一个索引，通常这些字段在查询时用作多字段分组、排序、拼接、逻辑判断等时才建立组合索引。
案例：CREATE TABLE `sc` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名', INDEX `idx_group_customer` (`customer_id`,`customer_name`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表'; -- ALTER TABLE sc ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`); SHOW INDEX FROM sc;
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
