MySQL优化3之索引使用

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作，为了尽量减少磁盘I/O。磁盘往往不是严格按需读取，而是每次都会预读，而且主存和磁盘以页为单位交换数据，所以在读取的数据不在主存中时，会从磁盘中读取一批数据（页）到主存中。

因此，要快速挺高性能，直接将常用的、少变更的数据直接读取到内存中，使用的时候就直接在内存上读取，而不去磁盘上读取，减少I/O操作，这样就能使程序更快。但由于内存容量的限制，也不可能将所有的数据都放内存中。

MySQL索引分类

普通索引：最基本的索引，没有任何限制。

唯一索引：与”普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它是一种特殊的唯一索引，不允许有空值。

全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

 

覆盖索引(Covering Indexes)就是直接走的索引，直接在内存中就拿到值，不需要查询数据库。如分页就要走覆盖索引，因为性能比较高。

 

聚簇索引(Clustered Indexes)，主键就是聚集索引。聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。

非聚簇索引：二级索引叶子节点保存的不是指行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行。InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

 

索引使用注意点：

1)     在什么是字段上建立索引，需要根据查询条件而定。

2)     大字段（blob）不要建立索引，查询也不会走索引。

3)     常用建立索引：主键、外键

4)     数据重复，且分布比较均匀比如性别字段不适合建索引

5)     频繁更新的字段不适合建索引

6)     where条件中用不到的字段不需要建索引

7)     查询优化考虑查询条件字段、排序字段 建立索引

8)     模糊查询可以使用前缀索引（like ‘xxx%’），like ‘%xxx%’不走索引。

9)     where条件中索引列参与计算，则不走索引, 比如：SELECT a1 FROM t WHERE a2 +10=30

10)   where条件中索引列使用函数，则不走索引，比如： SELECT `a1` FROM `t` WHERE LEFT(`createTime`,4) <2020；

11)   字符串与数字比较不使用索引,select a from t where a=1; a为字符串列。

12)   or不走索引，使用union all代替

13)   where条件中进行null判断不走索引

14)   where使用 != 或者<>不走索引

15)   连续值使用between代替in; 其他使用exists代替in

16)   单个表的索引建议不要建的太多，一方面资源消耗，一方面影响插入、更新