SQL高级优化（六）之MySQL索引

一、索引概述

1. 索引的优点

​ 为什么要创建索引？这是因为，创建索引可以大大提高系统的查询性能。如果不使用索引，查询时从第一行开始查询。如果使用了索引，所以就可以更加快速的找到希望的数据。

  • 第一、通过创建唯一性索引，可以保证数据库表找那个每一行数据的唯一性。
  • 第二、可以大大加快数据的检索速度，这也是创建所以的最主要的原因。
  • 第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  • 第四、在使用分组和排序字句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
  • 第五、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。
2. 索引的缺点

​ 也许有人要问：增加索引有如此多的优点，为什么不对表中的每一列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面：

  • 第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
  • 第二、索引需要占屋里空间，除了数据表占数据空间之外，每一个索引还要占一定的屋里空间。如果要建立聚簇索引，那么需要的空间就会更大。
  • 第三、当对表中的数据进行增加。删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
3. 什么样的字段适合创建索引

​ 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引：

  • 第一、在经常需要搜索的裂伤，可以加快搜索的速度；
  • 第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
  • 第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
  • 第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。
  • 第五、在经常需要排序的裂伤创建索引，因为索引已经排序，这杨查询可以利用索引的排序，加快排序查询时间；
  • 第六、在经常使用WHERE字句中的列上面创建索引，加快条件判断速度。 ​ 建立索引，一般按照select的where条件来建立，比如：select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上建立索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。
4. 什么样的字段不适合创建索引：

​ 同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下述特点：

  • 第一、对于那些在查询鸿很少使用到，因此有所应或者无缩影，并不能提高出行速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
  • 第二、对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
  • 第三、对于那些定义为text,image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
  • 第四、当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

二、MySQL中的索引种类

1. 单列索引

就是给一个列添加索引。

  • 普通索引：不考虑过多情况，主要是为了让查询更快一些。
  • 唯一缩影：列中值不可以重复，可以是null。
  • 主键索引：列中值不可以重复，又不可以为null。
2. 组合索引

​ 给表中大于等于两个列添加索引。但是需要满足最左前缀。

3. 全文索引（full-text）
  • 只能在MyISAM存储引擎才可以使用
  • 只能在char、varchar、text等字段才可以使用全文索引
  • 全文索引是抽取一列内容的关键字，通过关键字建立索引。 全文索引适用于含有like的查询。但是也只能解决'xxxx%'模糊查询低效的问题。

三、MySQL中的索引管理

​ 建立索引可以在建立表时直接指定索引，也可以后期添加索引。且要注意逐渐约束具有自动带有主键索引，唯一约束自动带有唯一索引。在MySQL中，对索引的查看和删除操作是所有索引类型通用的。

1. 普通索引

​ 这是最基本的索引，他没有任何限制MyISAM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

  • 创建索引
CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30),PRIMARY KEY(id),INDEX index_name(title(5)))
 
show index from test;
create index normal_index on test(title);
# 查看执行计划后，直观感受查询时间
explain select * from test where title='正负零 0';
  • 查看索引
SHOW INDEX FROM [table_name];
SHOW KEYS FROM [table_name]; # 旨在MySQL中可以使用keys关键字
  • 删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
2. 唯一索引

​ 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类

  • 创建索引
CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30),PRIMARY KEY(id),UNIQUE INDEX index_name(title(5)))
 
create unique index unique_index on tescher(name);
explain select * from teacher where name='老师3';
drop index unique_index on teacher;
3. 全文索引

​        MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引在MySQL5.6之前尽可用与MyISAM表，在MySQL5.7后InnoDB也支持；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句中的一部分被创建，或是随后使用ALTER TABLE或CREATE INDEX被添加。 ​ 对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。 ​ 全文索引对中文支持不好，如果搜索中文就只能按照最左对照进行搜索，如果是英文就可以匹配中间。

  • 创建索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name(column);
CREATE TABLE table_name(id int not null auto_increment,title varchar(30),PRIMARY KEY(id),FULLTEXT index_name(title))
  • 创建后的全文索引需要配合match(列，列)against('内容')使用。
    • match中列必须和创建全文索引的列一样。例如创建全文索引时（id,name）,match(name)无法使用全文索引，必须单独建立name列的全文索引。
alter table teacher add column(address varchar(200));
update teacher set address='北京海淀';
create fulltext index full_index3 on teacher(name,address);
explain select * from teacher where match(address) against('北京');
select * from student where match(address) against('bei' in NATURAL LANGUAGE mode);
drop index full_index3 on teacher;
    • against中内容有三种模式
    • 自然语言模式：IN NATURAL LANGUAGE MODE
    • 布尔模式：IN BOOLEAN MODE
    • 查询扩展模式：WITH QUERY EXPANSION
    • 自然语言模式
update teacher et address='山西晋城' where id=2;
update teacher set address='山西晋中' where id=3;
create fulltext index full_index_address on teacher(address);
show index from teacher;
select * from teacher;
# 北京昌平可以查询到，但是北京无法查询到
explain select * from teacher where match(address) against('山西晋城' in natural language mode);
    • 布尔模式：支持特殊符号。及时对没有全文索引的列也可以进行搜索，但是非常慢。查询时必须从最左开始查询，例如：山西晋城，按照金恒无法查询
+ 一定要有（不含有噶关键词的数据条均被忽略）。
- 不可以有（排除指定关键词，含有该关键词的均被忽略）
> 提高该条匹配数据的权重值
< 降低该条匹配数据的权重值
~ 将其相关性由正装复，表示拥有该字会降低相关性（但不像-将之排除），只是排在较后面权重值降低。
* 万用字，不想其他愈发方子前面，这个要接在字符串后面。
"" 用双引号将一段句子抱起来表示要完全相符，不可拆字
 
# 查询山西*可以，但是*晋城不可以
explain select * from teacher where match(address) against('山西*' in boolean mode);
    • 查询扩展。查询时进行扩展查询，反向和条件有关系的内容都查询出来
update teacher set address='oracle beijing changping' where id=1;
update teacher set address='haidian fengtai beijing' where id=2;
update teacher set address='oracle is database' where id=3;
# 只能查询两行
explain select * from teacher where match(address) against('beijing changping' in natural language mode);
# 查询到三行，因为oracle出现在beijing changping同行，查询时认为oracle和beijing changping有关，所以按照oracle进行查询
explain select * from teacher where match(address) against('beijing changing' with query EXPANSION);
  • 中文拆瓷器ngram

    由于中文是么有空格的，MySQL从5.7.6开始内置ngram中文分词插件。可以设置整个中文按照指定大小进行拆词。

    • 1. 在my.ini中[mysqld]下添加参数，设置拆词长度
ngram_token_size=2
    • 2. 建立表，插入数据
create table ft ( id int primary key auto_increment, name varchar(20), address varchar(200) ); insert into ft values(1, '张三'， '北京市昌平区建材城85号院'); insert into ft values(2, '李四'， '上海市虹桥机场'); insert into ft values(3, '王五'， '河北省保定市'); insert into ft values(4, '赵六'， '中华人民共和国北京市海淀区');
    • 3. address创建全文索引。注意后面的with parse ngram
create fulltext index index3 on ft(address) with parse ngram;
    • 4. 看是否已经对ft表保存索引信息。如果没有，设置ft中索引保存到索引表中。修改的前提是，这个表有全文索引。
show variables like '%onnodb_ft_aux_table%'; 
set global innodb_ft_aux_table='optimization/ft';
    • 5. 查看索引信息
select * from information_schema.INNODB_FT_INDEX_CACHE;
    • 6. 测试，查询时条件值无论是什么会对查询条件进行拆词，按照匹配优秀级进行匹配。
mysql explain select * from ft where MATCH(address) AGAINST('北京123市');
4. 组合索引

​ 组合索引是创建索引时至少有两个列添加索引。创建了组合索引时实际上是创建了多个索引。所以在联合索引时把最常用的列放在最左边。

show index from teacher;
create index mul_index on teacher(name, address);
# 使用索引, type=ref
explain select * from teacher where name='老师1';
# 没有使用索引, type=index
explain select * from teacher where address='oracle is a database';
# 使用索引，type=ref
explain select * from teacher name='老师1' and address='o';
  • 创建索引
CREATE INDEX index_name ON table_name(column_list);
5. MySQL中的索引优化

​ 上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE次数大于查询次数时，放弃索引。因此更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太眼中，但如果你在一个大表上创建了多种组合索引，索引文件会膨胀很快。索引知识提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

  • 使用短索引（前缀索引） 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。段缩影不仅可以提高查询速度，而且可以节省磁盘空间和I/O操作。
CREATE INDEX index_name ON table_name(column(length));
  • 索引列排序 MySQL查询只使用一个索引，因此如果where字句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
  • 排序时，排序行数过多可能导致索引失效。
  • 尽量不要使用*查询全部列,可能导致索引失效。（包含索引列）
  • like语句操作 一般情况下不鼓励使用like操作，如果非食用不可，如何使用也是一个问题，like='%333%'不会使用索引，而like"aaa%"(非前导模糊查询)可以使用索引。使用后，优化到range级别
explain select * from teahcer where address like '%oracle%';
  • 不要在列上进行运算 例如：select * from users where YEAR(adddate) < '2007-01-01';应该把计算放在业务代码上完成，而不是交给数据库
  • 范围列使用索引 范围条件有：<、<=、 >、>=、between等。 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。所以where中把最主要的查询条件放在第一个。
alter table teacher add column(age int(3));
alter table teacher add column(weight int(3));
select * from teacher;
update teacher set age=10,weight=90 where id=1;
update teacher set age=20,weight=100 where id=2;
update teacher set age=30,weight=100 where id=3;
 
create index age_index on teacher(age);
create index weight_index on teacher(weight);
 
explain select * from teacher where age between 10 and 20 and weight between 90 and 100;
  • 类型转换会导致索引无效 当列是文本类型时，把数值类型当做列的条件会弃用索引
explain select * from teacher where name=20;
6. 索引总结

​      最后总结一下，MySQL只对以下操作才是用索引：<,<=,=,>=,between,in以及某些时候的like（不以通配符%或_开头的情形）。理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。

  • 建议：一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
7. MySQL中的SQL的常见优化策略
  • NULL值 在老版本中含有NULL的列无法触发索引，但是在MySQL5.7中已经可以触发索引了。但是可能出现“无法预料的结果"。所以在建立表时给列添加not null约束或default默认值。
  • 避免全表扫描 对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。
  • 避免负向条件 也尽量避免在where字句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。 负向条件有：!=、<>、not in、 not exists、 not like等
explain select * from teacher where address != 'aa';
  • 避免使用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;
  • 在目前MySQL5.7中or已经可以触发索引了，老版本不可以。
  • 慎用in和not in逻辑 in 可以代替union all操作，虽然对CPU性能稍微增加一些，但是也可以忽略不计了。 in 和not in也要慎用，否则会导致全表扫描，如：
select id from t1 where num in(select id from t2 where id > 10);
此时外层查询会全表扫描，不适用索引。可以修改为：

SQL
select id from t1, (select id from t1 where id > 10) t2 where t1.id = t2.id;
此时索引被使用，可以明显提升查询效率。
  • 注意模糊查询 下面的查询也将导致全表扫描
select id from t where name like '%abc%';

         模糊查询如果是必要条件时，可以使用select id from t where name like 'abc%'来使用模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elasticsearch、Lucene、Solr等）。

  • 避免查询条件中字段计算 应尽量避免在where字句中对字段进行表达式操作，浙江导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100;
应改为
select id from t where num = 100*2;
  • 避免查询条件中对字段进行函数操作 也尽量避免在where字句中对字段进行函数操作，浙江导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name, 1, 3)='abc'
name以abc开头的id 应改为
select id from t where name like 'abc%';
  • WHERE字句'='左边注意点 不要在where字句中"="左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。
  • 组合索引使用 在使用索引字段作为条件时，如果该索引是符合索引，那么必须使用到该缩影中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  • 不要定义无意义的查询 不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into # from t where 1=0;
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
  • exists 很多时候用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)
  • 索引也可能失效 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如表中有字段sex，male、female几乎各占一半，那么即使在sex上建了索引也对查询效率起不了作用。
  • 表格字段类型选择 尽量使用数字型字段，若致函数值信息的字段尽量不要设计为字符型，这样会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。尽可能使用varchar代替char，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  • 查询语法中的字段 任何地方都不要使用select * from t,用具体的字段列表代表“*”，不要返回用不到的任何字段。
  • 索引无关优化

不使用*、尽量不适用union,union all等关键字，尽量不适用or关键字、尽量使用等值判断。

表连接建议不超过5个。如果超过5个，则考虑表格的设计。（互联网应用中）.

表连接方式使用外联由于内联。 外连接有基础数据存在。如：A left join B,基础数据是A。 A inner join B,没有基础数据的，先使用笛卡尔积完成全连接，再根据连接条件得到内连接结果集。

大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。

select * from table limit 1000000, 10;
select * from table where id in (select pk from table limit 1000000, 10);
 
作者：张亚飞
出处：https://www.cnblogs.com/zhangyafei
gitee：https://gitee.com/zhangyafeii
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/zhangyafei/p/13903681.html