前言

Github:https://github.com/HealerJean

博客:http://blog.healerjean.com

1、索引是什么

索引是表的目录,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,以此快速定位查询数据。对于索引,会保存在额外的文件中

索引可以提高查询速度,会减慢写入速度,
索引的缺点是创建和维护索引需要耗费时间。

索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,

所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过5个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

1.1、什么样的字段适合创建索引

1、表的主键、外键必须有索引;外键是唯一的,而且经常会用来查询

2、经常与其他表进行连接的表,在连接字段上应该建立索引

3、数据量超过300的表应该有索引;

4、重要的SQL或调用频率高的SQL,比如经常出现在where子句中的字段,order by , group by, distinct的字段都要添加索引

5、经常用到排序的列上,因为索引已经排序。

6、经常用在范围内搜索的列上创建索引,因为索引已经排序了,其指定的范围是连续的

1.2、什么场景不适合创建索引

1、 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 

2、对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了,所以没有必要。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 

3、对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

4、当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。

5、不会出现在where条件中的字段不该建立索引

6、如果列均匀分布在 1 和 100 之间,却只是查询中where key_part1 > 1 and key_part1 < 90不应该增加索引

1.3、索引使用以及设计规范

1、越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂

2、尽量避免null:应该指定列为not null, 含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂,也可能导致复合索引无效

3、主键选择策略

1) 每个表必须显示指定主键;
2) 主键尽量为一个字段,且为数字类型,避免使用字符串;
3) 主键尽量保持增长趋势,建议使用id的生成器;
4)主键尽量杜绝联合索引

4、每个表的索引个数尽量少于5个,避免创建重复冗余索引;每个组合索引尽量避免超过3个字段,索引不是越多越好,谨慎添加索引,综合考虑数据分布和数据更新

5、重要的SQL或调用频率高的SQL

1) update/select/delete的where条件列字段都要添加索引;
2) order by , group by, distinct的字段都要添加索引

6、避免出现index merge(单索引or的查询);合理利用covering index

7、组合索引创建时,把区分度(选择性)高的字段放在前面;根据SQL的特性,调整组合索引的顺序

8、对于varchar字段加索引,建议使用前缀索引,从而减小索引大小

2、索引分类

2.1,普通索引:

仅加速查询 最基本的索引,没有任何限制,是我们大多数情况下使用到的索引。

CREATE INDEX  index_name  on user_info(name)  ;

2.2,唯一索引:

与普通索引类型,不同的是:加速查询 + 列值唯一(可以有null)

CREATE UNIQUE INDEX  mail  on user_info(name)  ;

2.3,全文索引:

全文索引(fulltext)仅可以适用于MyISAM引擎的数据表;作用于CHAR、VARCHAR、TEXT数据类型的列。

2.4,组合索引:

将几个列作为一条索引进行检索,使用最左匹配原则。

3、索引的创建和删除

注意:对于创建索引时如果是blob 和 text 类型,必须指定length。

3.1、创建表的时候同事创建索引

create table healerjean (
  id   bigint(20)  NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT  '主键',
  name  VARCHAR(32) NOT NULL COMMENT '姓名',
  email VARCHAR(64) NOT NULL COMMENT  '邮箱',
  message text DEFAULT  NULL COMMENT '个人信息',
  INDEX index_name (name) COMMENT '索引name'
) comment  = '索引测试表';

3.2、在存在的表上创建索引

create index index_name on healerjean(name)
create index index_name on healerjean(message(200));

alter table employee add index emp_name (name);   

3.3、删除索引

drop index_name on healerjean;
alter table users  drop index  name_index ;

3.4、查看索引

这个时候,我们会发现其实主键id也是一个索引

show index from healerjean;

WX20180727-155546

4、联合索引和单索引

大神博客:https://blog.csdn.net/Abysscarry/article/details/80792876

4.1、联合索引

B+数:联合索引的使用有一个好处,就是索引的下一个字段是会自动排序的

减少开销。建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。如果我们每多创建一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!

覆盖索引。对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知!

4.1.1、普通使用

解释 :可以通过key_len的长度来判断联合索引使用到了那些
CREATE TABLE `d001_index` (
    `id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(128) DEFAULT NULL,
    `age` bigint(20) DEFAULT '0',
    `country` varchar(50) DEFAULT NULL,
    `a` int(11) DEFAULT '0',
    `b` int(11) DEFAULT '0',
    `c` int(11) DEFAULT '0',
    `d` int(11) DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `idx_a_b_c_d` (`a`,`b`,`c`,`d`),
    KEY `idx_age` (`age`),
    KEY `idx_name` (`name`)
)



INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (1, 'zhangyj', 25, 'chine', 1, 2, 3, 4);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (2, 'healerjean', 24, 'china', 2, 3, 4, 5);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (3, 'n', 22, 'a', 2, 4, 5, 6);
INSERT INTO `hlj-mysql`.d001_index (id, name, age, country, a, b, c, d) VALUES (4, 'k', 2, 'b', 3, 5, 6, 8);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'zhangyj', 25, 'chine', 1, 2, 3, 4);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'healerjean', 24, 'china', 2, 3, 4, 5);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'n', 22, 'a', 2, 4, 5, 6);
INSERT INTO `hlj-mysql`.d001_index ( name, age, country, a, b, c, d) VALUES ( 'k', 2, 'b', 3, 5, 6, 8);

4.1.1.1、查询条件为 a

用到了索引 a(key_len 为 5)

explain  SELECT * from d001_index WHERE a = 1 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d5const1100NULL

4.1.1.2、查询条件为 b

未用到索引(key_len 为 null)

explain  SELECT * from d001_index WHERE b = 1 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where

4.1.1.3、查询条件为 c

未用到索引 ,同查询条件b(key_len 为 null)

explain  SELECT * from d001_index WHERE c = 1 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where

4.1.1.4、查询条件为 b and c

未用到索引(key_len 为 null)

explain  SELECT * from d001_index WHERE b = 1 and c = 2 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL425Using where

4.1.1.5、 查询条件为 a and b

用到了联合索引 a 、b(key_len 为 10)

explain  SELECT * from d001_index WHERE a = 1 and b = 2 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d10const,const1100NULL

4.1.1.6、查询条件为 a and c

key_len 为 10,只用到了联合索引 a

explain  SELECT * from d001_index WHERE a = 1 and c = 3 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d5const125Using index condition

4.1.1.7、查询条件为 a、b、c、d

用到了联合索引a b c d (key_len 为 20)

explain  SELECT * from d001_index WHERE a = 1 and b = 2 and c = 3  and d = 4 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrefidx_a_b_c_didx_a_b_c_d20const,const,const,const1100NULL

4.1.1.8、查询条件为 a or b :

未用到索引(key_len 为 null)

explain  SELECT * from d001_index WHERE a = 1 or b = 2;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLidx_a_b_c_dNULLNULLNULL450Using where

4.1.2、范围查询

解释:范围查询使用到第几列,则联合索引该列后面的字段的不能使用索引

注意:不要取极端值测试,因为mysql优化器会通过索引查找的数量造成一定的影响,即使使用了索引,但是索引却没能生效 ,比如下的 3 变成 1 会受到影响,因为我的数据量中a最小就是 1

explain  select * from d001_index WHERE a > 1  ; 没有使用索引,因为数据均匀分布在1 以上

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLidx_a_b_c_dNULLNULLNULL875Using where
explain  select * from d001_index WHERE a > 3  ; 使用到了索引

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d5NULL1100Using index condition

4.1.2.1、查询条件为:a > 3

使用了索引 a (长度为 5 )

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d5NULL1100Using index condition

4.3.1.2、查询条件为:a = 1 and b > 1

使用了联合索引 a、b(长度为10)

explain SELECT * from d001_index WHERE a = 1 and  b > 1 ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d10NULL2100Using index condition

4.3.1.3、查询条件为:a = 5 and b > 6 and c = 7

使用了联合索引中的 a、b(长度为10)

explain SELECT * from d001_index WHERE a = 5 AND b > 6 AND c = 7

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLrangeidx_a_b_c_didx_a_b_c_d10NULL112.5Using index condition

4.3.1.4、归纳总结

A>5						——索引使用  A
A>5 AND B=2             ——索引使用  A

A=5 AND B=6 AND C=7		——索引使用  A B C
A=5 AND B>6				——索引使用  A B
A=5 AND B>6 AND C=2     ——索引使用  A B

4.1.3、排序查询

注意:表中的数据量和查询的数据量会造成影响,所以我这里都普遍使用了limit 1 ,博主测试有一些结果没有写,测试失败了,应该是由于数据量的原因,

explain  SELECT * from d001_index  order by  a  limit  1; 

#order by a 使用到了联合索引 a b c d 按理说应该只用到a才对,这里博主有些疑惑

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLindexNULLidx_a_b_c_d20NULL1100NULL
explain  SELECT * from d001_index  order by b  limit  1;    

#order by b 未使用索引

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLALLNULLNULLNULLNULL8100Using filesort

1、order后面,可以用到组合索引

ORDER BY A 					——首列排序

A=5 ORDER BY B				——第一列过滤后第二列排序

ORDER BY A DESC, B DESC		——注意,此时两列以相同顺序排序

A>5 ORDER BY A				——数据检索和排序都在第一列

2、order后面,不可以用到组合索引

ORDER BY B 					——排序在索引的第二列

A>5 ORDER BY B 				——范围查询在第一列,排序在第二列

A IN(1,2) ORDER BY B 		——范围查询在第一列,排序在第二列

ORDER BY A ASC, B DESC 		——注意,此时两列以不同顺序排序,我现在还无法理解

4.2、单列索引

4.2.1、查询条件为 name

用到了索引 name(key_len = 515 = 4 * 128 + 2)

explain  SELECT * from d001_index WHERE name = 'zhangyj' ;

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLconstidx_nameidx_name515const1100NULL

4.2.2、查询条件为 name and age

只用到了索引 name(key_len = 515 = 4 * 128 + 2)

<!DOCTYPE html>

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEd001_indexNULLconstidx_name,idx_ageidx_name515const1100NULL

4.2.3、查询条件为 name or age :

两个索引都用上了 ` type = index_merge`合并索引 ,我这里的测试失败了,应该是由于我的数据表数据量比较小的原因

4.3、归纳总结

1、 联合索引:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上

2、单列索引:多个单列索引在多条件查询时只会生效第一个索引所以多条件联合查询时最好建联合索引

当创建(a,b,c)联合索引时,相当于创建了(a)单列索引,(a,b)联合索引以及(a,b,c)联合索引,想要索引生效的话,只能使用 aa,ba,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到 ,具体 使用 a b c 的顺序无关,mysql会自动优化,但是我们建议按照索引的顺序进行查询,而且尽量将筛选力度大的放到前面

5、使用索引

如果以错误的方式使用,则即使建立索引也会不奏效。

5.1、正确使用索引

1、使用 like 的查询

对于使用 like 的查询,只有%号不在第一个字符,索引才可能会被使用:

2、使用负向查询(not ,not in, not like ,<> ,!= ,!> ,!< ) 不会使用索引

2.1、使用is null /is not null会进行全表扫描,索引无法存储null值,所以where的判断条件如果对字段进行了null值判断,将导致数据库放弃索引而进行全表查询

select id from t where num is null 

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

2.2、使用where IN()过滤时,IN集合个数必须小于500,因为in的数据少的时候,mysql优化器会可能会使用索引(在in里面的列表值mysql是会自动从小到大排序,加锁也是一条条从小到大加的锁)但是当数据太多以后就不一定了,可以让MySQL按照ID顺序进行查询这可能比随机的关联要更高效,如

select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3



很多时候用 exists 代替 in 是一个好的选择: 
select num from a where num in(select num from b) 
用下面的语句替换: 
select num from a where exists(select 1 from b where num=a.num) 

3、列是字符型,,传入的是数字,则不上‘ ' 不会使用索引

5、条件中有or,应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描(),如:

select id from t where num=10 or num=20 

可以这样查询:

select id from t where num=10 union all select id from t where num=20

7、索引列上做(计算,函数,(自动或者手动)类型装换),会导致索引失效而导致全表扫描。

8、如果是组合索引的话,如果不按照索引的顺序进行查找,比如直接使用第三个位置上的索引而忽略第一二个位置上的索引时,则会进行全表查询

9、如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引,如

因为mysql优化器会通过索引查找的数量造成一定的影响,即使使用了索引,但是索引却没能生效 ,比如下的 3 变成 1 会受到影响,因为我的数据量中a最小就是 1(包括:数据量小的时候)

explain  select * from d001_index WHERE a > 1  ; 没有使用索引,因为数据均匀分布在1 以上

ContactAuthor