定义

索引是一种数据结构，通过使用索引，可以加快检索、排序。

底层通常是BTree，通过BTree可以将无序的数据相对有序。

索引的缺点是：

如果表中数据增删改，那么索引也要修改。
索引占用物理空间。
创建索引和维护索引耗费时间。

索引分类


单值索引

一个索引只包含单个列，一个表可以有多个单列索引


唯一索引

索引列的值必须唯一，允许为空值


复合索引

一个索引包含多个列



基本语法


创建

create [unique] index index_name on table(column_name(length));

alter table_name add [unique] index [index_name] on (column_name(length))


删除

drop index [index_name] on table_name;


查看

show index from table_name;



索引结构



BTree索引

原理：


Hash索引


full-text索引


R-Tree索引


建立索引注意事项


主键自动建立唯一索引
频繁作为查询的字段应该创建索引
外键建立索引
频繁更新的字段不要建立索引
where条件里用不到的字段不创建索引
优先组合索引
表记录少不要建立索引
经常增删改的表不要建立索引
数据重复且平均的字段不要建立索引（比如说性别字段）

Explain关键字

Explain关键字用于查询MySQL如何执行SQL语句的，进而依此优化表结构或查询语句。
作用

表的读取顺序
数据读取操作的操作类型.
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询



表的读取顺序

id相同，从上到下依次执行
id不同，由大到小依次执行

数字大的优先执行，数字相同的顺序执行

derived 衍生



select_type


Simple简单查询
Primary最外层查询标记为Primary
SubQUERY子查询
Derived
Union联合查询
Union Result

Type
访问类型：

最好到最差：

system > const > eq_ref > ref > range > index > ALL

至少达到range级别


system表只有一条记录（等于系统表）
const比较唯一索引（如where id = 1）
eq_ref唯一性索引扫描（如where t1.id = t2.id）
ref非唯一性索引扫描
range 索引范围查询（如查询索引大于10小于20）
index遍历全部索引
ALL全表查询

possibble_kesy和key
possible_keys显示可能应用在这张表的索引

key实际使用的索引

如果key为null，说明索引失效或不存在。
key_len
表示索引字段中使用的字节数，长度越短越好
ref
显示索引的那一列被使用了。如果可能，最好是const。
rows
根据表统计信息和索引选用情况，大致估算出找到所需的记录需要读取的行数。


extra
额外的重要信息。


Using Filesort ：文件排序（不要出现）


Using temporary：使用了临时表（order by和group by，不要出现这个）

优化这两个，group by一定要按照索引的顺序和个数来。如idx_col1_col2，使用group by col1,col2，而不是group by col2，否则会使用临时表或文件排序（对索引外部排序）



Using index： 使用了索引

覆盖索引：查询的列就是所建的索引



索引优化



左连接右表建立索引，右连接左表建立索引
join语句，小结果集驱动大结果集。
优先优化内层循环
保证join字段已经被索引

索引失效

全值匹配我最爱
最佳左前缀法则：查询从索引的最左前列并且不跳过索引中的列
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*
mysq|在使用不等于**(!=或者<>)**的时候无法使用索引会导致全表扫描
is null ,is not null也无法使用索引
like以通配符开头(’%abc… ')mysq|索引失效会变成全表扫描的操作（使用覆盖索引可以避免通配符匹配开头索引失效的问题）
字符串不加单引号索引失效
少用or,用它来连接时会索引失效


查询优化



慢查询

通过开启慢查询，可以把执行速度慢的SQL筛选出来



小表驱动大表


order by 的顺序和复合索引顺序一致就不会产生filesort

如index_AB 索引，使用order by A,B不会产生filesort ，使用order by B或者order by B,A就会filesort

order by 不要和select * 一起使用

尝试提高sort_buffer_size和max_length_for_sort_data参数






慢查询
慢查询用于筛选响应时间超过阈值的语句。阈值默认是10秒，可以配合explain语句进行分析。

select variables like ‘%slow_query_log%’;

查询是否开启慢查询


set global slow_query_log =1;

开启慢查询

如果要永久开启慢查询，则需要更改my.cnf文件

set global long_query_time = 3;

阈值设置为3秒

日志分析工具mysqldumpslow



Show Profile
mysql 提供用来分析当前会话中语句执行的资源消耗情况，用于SQL调优。

查询是否开启 show variables like ‘profiling’;

开启set profiling = on;

查看结果 show profiles;


诊断SQL:show profiles cpu,block io for query ;



如果结果表status中出现以下：

则说明出现问题。

背景：

为了提高数据库效率，建索引是家常便饭；那么当查询条件为2个及以上时，我们是创建多个单列索引还是创建一个联合索引好呢？他们之间的区别是什么？哪个效率高呢？我在这里详细测试分析下。

一、联合索引测试
注：Mysql版本为 5.7.20
创建测试表(表记录数为63188)：
CREATE TABLE `t_mobilesms_11` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id，创建任务时的userid',
  `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
  `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
  `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
  `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
  `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
  `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
  `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
  `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
  `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`id`),
  KEY `联合索引` (`userId`,`mobile`,`billMonth`)
) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情'

我们为userId, mobile, billMonth三个字段添加上联合索引！
我们选择 explain 查看执行计划来观察索引利用情况：

1.查询条件为 userid
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222'


可以通过key看到，联合索引有效

2.查询条件为 mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'



可以看到联合索引无效

3.查询条件为 billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'



联合索引无效

4.查询条件为 userid and mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'



联合索引有效

5.查询条件为 mobile and userid
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  mobile='13281899972' AND userid='2222' 



在4的基础上调换了查询条件的顺序，发现联合索引依旧有效

6.查询条件为 userid or mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' OR mobile='13281899972'



把 and 换成 or，发现联合所索引无效！

7.查询条件为 userid and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND billMonth='2018-04'



这两个条件分别位于联合索引位置的第一和第三，测试联合索引依旧有效！

8.查询条件为 mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'



这两个条件分别位于联合索引位置的第二和第三，发现联合索引无效！

9.查询条件为 userid and mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'



所有条件一起查询，联合索引有效！（当然，这才是最正统的用法啊！）

二、单列索引测试
创建三个单列索引：


1.查询条件为 userid and mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'



我们发现三个单列索引只有 userid 有效（位置为查询条件第一个），其他两个都没有用上。
那么为什么没有用上呢？按照我们的理解，三个字段都加索引了，无论怎么排列组合查询，应该都能利用到这三个索引才对！
其实这里其实涉及到了mysql优化器的优化策略！当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的索引去使用，也就是说，此处userid 、mobile 、billMonth这三个索引列都能用，只不过优化器判断使用userid这一个索引能最高效完成本次查询，故最终explain展示的key为userid。

2.查询条件为 mobile and billMonth
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'



我们发现此处两个查询条件只有 mobile 生效（位置也为查询条件第一个）

3.查询条件为 userid or mobile
EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972' 



这次把 and 换成 or，发现两个查询条件都用上索引了！
我们在网上可能常常看到有人说or会导致索引失效，其实这并不准确。而且我们首先需要判断用的是哪个数据库哪个版本，什么引擎？
比如我用的是mysql5.7版本，innodb引擎，在这个环境下我们再去讨论索引的具体问题。
关于or查询的真相是：

所谓的索引失效指的是：假如or连接的俩个查询条件字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描。我们从or的基本含义出发应该能理解并认可这种说法，没啥问题。
此刻需要注意type类型为index_merge。

我查资料说mysql 5.0 版本之前 使用or只会用到一个索引（即使如上我给userid和mobile都建立的单列索引），但自从5.0版本开始引入了index_merge索引合并优化！也就是说，我们现在可以利用上多个索引去优化or查询了。
index_merge作用：

1、索引合并是把几个索引的范围扫描合并成一个索引。

2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。

3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
index_merge应用场景：
1.对OR语句求并集，如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果
2.对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果
3.对AND和OR组合语句求结果

三、结论
通俗理解：

利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

重点：
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！

最左前缀原则：
顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上，

注：如果第一个字段是范围查询需要单独建一个索引

注：在创建联合索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。这样的话扩展性较好，比如 userid 经常需要作为查询条件，而 mobile 不常常用，则需要把 userid 放在联合索引的第一位置，即最左边

同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？
这个涉及到mysql本身的查询优化器策略了，当一个表有多条索引可走时,  Mysql  根据查询语句的成本来选择走哪条索引；

有人说where查询是按照从左到右的顺序，所以筛选力度大的条件尽量放前面。网上百度过，很多都是这种说法，但是据我研究，mysql执行优化器会对其进行优化，当不考虑索引时，where条件顺序对效率没有影响，真正有影响的是是否用到了索引！

联合索引本质：
当创建**(a,b,c)联合索引时，相当于创建了(a)单列索引**，(a,b)联合索引以及**(a,b,c)联合索引**

想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

注：这个可以结合上边的 通俗理解 来思考！

其他知识点：
1、需要加索引的字段，要在where条件中

2、数据量少的字段不需要加索引；因为建索引有一定开销，如果数据量小则没必要建索引（速度反而慢）

3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描

4、联合索引比对每个列分别建索引更有优势，因为索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢。另外建立多列索引时，顺序也是需要注意的，应该将严格的索引放在前面，这样筛选的力度会更大，效率更高。

最后的说明：
网上关于索引优化等文章太多了，针对各个数据库各个版本各种引擎都可能存在不一样的说法！
我们的SQL引擎自带的优化也越来越强大，说不定你的某个SQL优化认知，其SQL引擎在某次升级中早就自优化了。
所以要么跟进官方文档，要么关注数据库大牛的最新文章，要么在现有数据库环境下自己去亲手测试！
数据库领域的水很深。。大家加油。。共勉 ~

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
总结，索引优缺点：
I、优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
II、缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
查看表的索引：
show index from table_name（表名）

结果列表中各字段的含义：

· Non_unique

如果索引不能包括重复词，则为0。如果可以，则为1。

· Key_name

索引的名称。

· Seq_in_index

索引中的列序列号，从1开始。

· Column_name

列名称。

· Collation

列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

· Cardinality

索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。

· Sub_part

如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

· Packed

指示关键字如何被压缩。如果没有被压缩，则为NULL。

· Null

如果列含有NULL，则含有YES。如果没有，则该列含有NO。

· Index_type

用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）

一、TTL索引介绍
TTL全称是(Time To Live),TTL索引能对一个单列配置过期属性来实现对文档的自动过期删除，我们可以在对字段创建索引时添加expireAfterSeconds选项将索引转换为TTL索引，该字段需要是date类型，在以下几种场景下即使索引设置了expireAfterSeconds属性也不会生效
- 如果该字段不是date类型，则文档不会过期
- 如果文档没包含索引的这个字段，则文档不会过期

二、TTL索引运行逻辑

MongoDB会开启一个后台线程读取该TTL索引的值来判断文档是否过期，但不会保证已过期的数据会立马被删除，因后台线程每60秒触发一次删除任务，且如果删除的数据量较大，会存在上一次的删除未完成，而下一次的任务已经开启的情况，导致过期的数据也会出现超过了数据保留时间60秒以上的现象。
对于副本集而言，TTL索引的后台进程只会在primary节点开启，在从节点会始终处于空闲状态，从节点的数据删除是由主库删除后产生的oplog来做同步。
TTL索引除了有expireAfterSeconds属性外，和普通索引一样。

三、TTL索引的限制

只支持对单个字段创建TTL索引，复合索引不支持expireAfterSeconds选项
_id列不支持TTL索引
固定集合(capped collection)不支持TTL索引
不支持用createIndex() 修改expireAfterSeconds属性，但可以用collMod命令修改，或者重建索引，但重建对于大集合成本较高，建议用collMod方式
一个列只能创建普通索引或TTL索引，不能同时对一个列创建这2种类型索引(实际TTL索引本身就是普通索引，只是多了一个过期属性)
如果一个列已经存在索引，则需要先将该索引drop后才能重建为TTL索引，不能直接转换

四、TTL索引的使用场景
1. 指定具体的过期时间属性

该场景是在创建索引时明确指定一个expireAfterSeconds时间作为文档的过期时间

// 对log_events集合的createdAt字段创建TTL索引且设置expireAfterSeconds过期时间为3600秒(1小时)
onepiece:PRIMARY> db.log_events.createIndex( { "createdAt": 1 }, { expireAfterSeconds: 3600 } )

// 对文档插入数据，包含createdAt字段，则该文档会在1小时候字段删除
onepiece:PRIMARY> db.log_events.insert( {
   "createdAt": new Date(),
   "logEvent": 2,
   "logMessage": "Success!"
} )

2. 插入一个具体的过期时间

该场景是在创建索引时将expireAfterSeconds设置为0，在这种情况下由插入到字段的数据来控制文档何时过期,这种场景更加精细化，可灵活的控制文档的过期时间及控制在业务低峰期触发文档过期

// 对log_events集合的expireAt创建TTL索引，并设置expireAfterSeconds属性为0
onepiece:PRIMARY> db.log_events.createIndex( { "expireAt": 1 }, { expireAfterSeconds: 0 } )

// 对文档插入数据，包含expireAt字段，该文档过期时间就是expireAt字段记录的时间
onepiece:PRIMARY> db.log_events.insert( {
   "expireAt": new Date('Jan 16, 2020 14:00:00'),
   "logEvent": 2,
   "logMessage": "Success!"
} )

3. TTL属性的修改(collMod)

对于TTL索引的expireAfterSeconds的属性，可以用collMod方式进行修改

// 创建TTL索引设置1小时过期属性
onepiece:PRIMARY> db.log_events.createIndex( { "createdAt": 1 }, { expireAfterSeconds: 3600 } )
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

// 查看索引定义
onepiece:PRIMARY> db.log_events.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "test.log_events"
	},
	{
		"v" : 2,
		"key" : {
			"createdAt" : 1
		},
		"name" : "createdAt_1",
		"ns" : "test.log_events",
		"expireAfterSeconds" : 3600
	}
]

// 修改索引定义，将一小时文档过期改为60秒
onepiece:PRIMARY> db.runCommand( { collMod: "log_events",
			index: { keyPattern: { createdAt: 1 },
			expireAfterSeconds: 60
			}
})

// 返回值： { "expireAfterSeconds_old" : 3600, "expireAfterSeconds_new" : 60, "ok" : 1 }

五、Date类型

Date() method which returns the current date as a string.
new Date() constructor which returns a Date object using the ISODate() wrapper.
ISODate() constructor which returns a Date object using the ISODate() wrapper.

onepiece:PRIMARY> Date()
Thu Jan 16 2020 14:48:40 GMT+0800 (CST)

onepiece:PRIMARY> new Date()
ISODate("2020-01-16T06:48:48.655Z")

onepiece:PRIMARY> ISODate()
ISODate("2020-01-16T06:48:53.673Z")

六、参考文档

https://docs.mongodb.com/manual/core/index-ttl/index.html