数据库索引与SQL优化

一、什么是索引

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。

二、索引的分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

三、索引的语法

--创建索引
CREATE INDEX 索引名  on 表名 (字段名);

--查看索引
SHOW INDEX FROM 表名;

--删除索引
DROP INDEX 索引名 on 表名;

四、SQL性能分析

使用explain关键字可以查询执行计划

EXPLAIN执行计划各字段含义：

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）

select_type：表示SELECT的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT／WHERE之后包含了子查询）等

type： 表示连接类型，性能由好到差的连接类型为NULL、system、const、eq＿ref、ref、range、index、all。

possiblkey：显示应用在该表上的索引

Key：实际使用的索引，如果为NULL，则没有使用索引。

Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

五、索引的优势

1.提高数据检索效率，降低磁盘IO成本
2.通过对数据的排序，降低排序成本

六、索引的劣势

1.索引虽提高了查询效率，但同时降低了更新、修改、删除的效率，因为MySQL不仅要保存数据，还要维护数据和索引的关系。
2.需要成本去维护索引。一个性能良好的索引需要不断的去尝试，以找到最优解。

七、什么情况下适合建立索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段（where后面的字段)。
3. 查询中与其他表关联的字段（各种join on后面的字段）。
4. 单值/复合索引选择？（高并发下倾向选择复合索引)。
5. 查询中排序的字段。
6. 查询中统计或分组的字段

八、什么情况下不适合建立索引

1）表数据太少
2）频繁更新的字段
3）where后面用不到的字段

九、什么时候会出现索引失效

1）like以通配符开头("%abc)会导致索引失效，违反最左前缀法则
最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）

2）在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描

3）存储引擎不能使用索引中范围条件右边的列，举例：select id，name from student where id >
50 and name ='张三’，会导致name索引失效

4）尽量使用覆盖索引，不要select*

5）MySQL在使用不等于(1=或<>)的时候无法使用索引会导致全表扫描，理由也很简单，B+Tree叶子节点用指针相连且是排好序的，这种数据结构只能解决有序的定值查询，像不等于这种无法利用索引查询

6）IS NULL、IS NOT NULL无法使用索引，理由同上

7）字符串不加单引号索引失效
隐式转换->函数操作

8）用or连接时会导致索引失效