数据库索引及查询优化

MySQL索引和查询优化

一、索引的概念

​ 在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高效查找，这些数据结构就是索引。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度，就像字典的查找一样。索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，就像字典的前几页一样。

​ 需要注意的是：如果过多的使用索引将会造成滥用，虽然索引大大提高了查询速度，但同时却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

二、索引的类型

1. 主键索引 PRIMARY KEY

   主键索引是一种特殊的索引，我们一般在创建表时同时创建主键，主键是唯一的且不能为空

2. 唯一索引 UNIQUE

   唯一索引要求索引的列是唯一的，但可以为空

3. 普通索引 NORMAL

   普通索引是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以在之后创建

4. 组合索引

   组合索引，即一个索引包含多个列

5. 全文索引 FULLTEXT

   全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

6. SPATIAL 空间索引

   空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

三、索引的方法

1. B+树

   B+Tree类型的索引是普遍使用的索引方式，也是推荐的方式

2. 哈希树

   Hash这种方式对范围查询支持得不是很好，Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。

四、索引失效的情况

1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
3. 组合索引，不是使用第一列索引，索引失效
4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描
5. 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理
6. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描
7. 对索引字段进行计算操作、字段上使用函数
8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

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

1）主键自动建立唯一索引

2）频繁作为查询条件的字段（where后面的字段）

3）查询中与其他表关联的字段（各种join on后面的字段）

4）单值/复合索引选择？（高并发下倾向选择复合索引）

5）查询中排序的字段

6）查询中统计或分组的字段

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

1）表数据太少

2）频繁更新的字段

3）where后面用不到的字段

七、索引的利弊

优点： 提高数据的检索速度，降低数据库的IO成本；

缺点：查询需要更新索引信息带来额外的资源消耗，索引还会占用额外的存储空间

八、查询优化

1. 避免 SELECT *，只查询需要的字段
2. 小表驱动大表，即小的数据集驱动大的数据集
3. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
4. 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引
5. 尽量使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表
6. or 查询改写成 union 查询
7. 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描
8. 查询语句尽可能简单，大语句拆小语句，减少锁时间
9. 避免频繁创建和删除临时表，以减少系统表资源的消耗
10. 尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
11. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销
12. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert
13. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
14. 尽量避免大事务操作，提高系统并发能力
15. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
16. 如果排序字段没有用到索引，就尽量少排序
17. 使用合理的分页方式以提高分页的效率。
18. 巧用STRAIGHT_JOIN。inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join