升职加薪之SQL索引

1，索引原理？

2，索引的优缺点？

3，如何提高查询效率？

4，索引分类

5，如何使用索引？

6，索引什么时候会失效？

7，SQL语句执行分析

1，索引的原理？

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据量非常大的时候，可以先通过索引表找到该行数据对应的物理地址然后访问相应的数据（可以把索引想象成书的目录，可以根据目录中的页码快速找到所需的内容）

2，索引的优缺点？

优点：

• 加快检索速度，降低数据库的IO成本（不需要全表扫描）
• 根据索引分组和排序，可以加快分组和排序，降低cpu的消耗

缺点：

• 索引实际上也是一张表，因此会占用存储空间
• 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表
• 索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大

3，如何提高查询效率？

• 不用select * 查询，仅查询需要的字段值

• 尽量不使用like、in等无法走索引的关键字

• 正确使用索引，对常用的字段，如where关键字或order by关键字后的字段加索引

• 如果数据量很大，建议分库分表

• 使用缓存，如redis存入热点数据

4，索引分类

5，如何使用索引？

• 主键索引（primary key）：它是一种特殊的唯一索引，每个表只能有一个主键，在创建或修改表时追加主键索引即可，例如：

	create table 表名 (..., primary key(字段名));
	alter table 表名 add primary key(字段名);
1
2

• 唯一索引（unique index）：在创建或修改表时追加唯一索引即可，例如：

	create table 表名 (..., unique index 索引名(字段名));
	alter table 表名 add unique index 索引名(字段名));
1
2

• 普通索引（index）：是最基本的索引类型，基于普通字段建立索引，例如：

	create table 表名 (..., index 索引名(字段名));
	alter table 表名 add index 索引名(字段名));
1
2

• 全文索引（fulltext index）：必须在字符串、文本字段上建立；mysql5.6之前的版本，只有MyISAM存储引擎支持全文索引，5.6版本开始MyISAM和InnoDB存储引擎都支持；查询操作在数据量比较少时，可以用like，对于数据量大的时候，使用全文索引比like快很多倍。索引建立如下：

	create table 表名 (..., fulltext key 索引名(字段名));
	alter table 表名 add fulltext index 索引名(字段名));
1
2

• 组合索引（index ）：列表搜索需要多列查询，可以使用组合索引，注意（符合最左匹配原则，如果单独查col2或col3或col2和col3，那么索引就不会起作用），索引建立如下：

	create table 表名 (..., index 索引名(col1, col2, col3));
	alter table 表名 add index 索引名(col1, col2, col3));
1
2

6，索引什么时候会失效？

• 组合索引中列的值为null，组合索引就会失效

• like操作中，‘%路飞%’ 索引会失效，但是’路飞%’就不会失效(分情况)

• 使用表达式或者函数索引会失效，例如：select * from user_info where year(gmt_modified) < 2021，这将导致索引失效而进行全表扫描，因此我们可以改成：gmt_modified<’2021-01-01′

• 查询条件中 < 符号，> 符号和 != 会导致索引失效，如果对主键索引使用 != 则不会失效，对主键索引或者整数类型的索引使用 < 符号或者 > 符号索引也不会失效

• 查询条件中使用is null或者is not null会导致索引失效

• 字符串不加单引号会导致索引失效

• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR连接的每个条件都加上索引

7，SQL语句执行分析

explain关键字可以模拟执行SQL查询语句，分析SQL查询语句性能，查看SQL可以优化的地方，例如：

explain列的含义：

• id（查询序列号）：相同，执行顺序由上到下；不同，如果时子查询，id序号递增，id越大执行的优先级越高

• select_type（查询类型）：表示select的类型。

  • SIMPLE：代表简单表，不用表连接或子查询；
  • PRIMRY ：主查询（外层查询）；
  • UNION： UNION中的第二个或者后面的查询语句；
  • SUBQUERY： 子查询中的第一个SELECT，结果不依赖于外部查询

• table（表名或者别名）：输出结果集的表

• type（访问类型）：从左到右，性能由最差到最好，ALL（全表扫描）,>=,between等操作）< ref < eq_ref < const < system < NULL

• possible_keys（可能用到的索引）：查询中可能用到的索引

• key（实际用到的索引）：查询中实际用到的索引

• key_len（索引长度）：索引的长度

• ref（与索引比较的列）：显示之前的表在key列记录的索引中查找值所用的列或者常量

• rows（估算的行数）：扫描的行数

• filtered（按表条件筛选的行百分比）：列针对表中符合某个条件(where子句或者联接条件)的记录数的百分比所做的一个悲观估算

• Extra（额外信息）：

  • using filesort：说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置

  • using temporary：建立临时表来保存中间结果，查询完成之后把临时表删除

  • using index：这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找

  • using where：使用where进行条件过滤

  • using join buffer：使用连接缓存

  • impossible where：where语句的结果总是false