mysql索引失效及sql常见优化

索引失效

1.索引不会包含有null值的列

只要列中包含null值将不被包含在索引中，组合索引中只要有一列包含null值，索引就会失效，设计数据库不要将默认值设置为null。

2.使用短索引

对串列进行索引，如果可以应该制定一个前缀长度，例如，如果有一个char(255)的列，如果在前10或者20个字符以内多数值是唯一的，那么就不要对整个列进行创建索引，短索引不仅可以提高查询效率，还可以减少磁盘占用空间和减少I/O操作

 create index index_name on table_name(column(length))

3.索引列排序

mysql索引只使用一个索引，如果where语句中已经使用了索引的话，那么order by 中的列是不会使用索引的，因此数据库默认排序满足要求的前提下不要使用排序操作，尽量不要包含多个列的排序，如果需要给这些列尽量创建复合索引。

 

 

4.like语句

一般情况下不鼓励使用like操作，如果非要使用，like '%aaaa%'，不会使用索引，like 'aaaa%'可以使用索引

 

5.不要在列上进行运算

例如 select * from where YEAR(addDate) < 2007,将在每个行上进行运算，将会导致索引失效而进行全盘扫描。因此可以改成 select * from where addDate < '2007-11-23'

sql常见优化 

 

 

 

 

 

索引何种情况建立

大多数情况下，业务SQL十分复杂，没法优化。所以就要建立索引了。这个时候，参照如下规则建立索引

(1)索引并非越多越好，大量的索引不仅占用磁盘空间，而且还会影响insert,delete,update等语句的性能

(2)避免对经常更新的表做更多的索引，并且索引中的列尽可能少；对经常用于查询的字段创建索引，避免添加不必要的索引

(3)数据量少的表尽量不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

(4)在条件表达式中经常用到不同值较多的列上创建索引，在不同值很少的列上不要建立索引。比如性别字段只有“男”“女”俩个值，就无需建立索引。如果建立了索引不但不会提升效率，反而严重减低数据的更新速度

(5)在频繁进行排序或者分组的列上建立索引，如果排序的列有多个，可以在这些列上建立联合索引。
从物理存储角度:
 聚簇索引和非聚簇索引
从数据结构角度:
 B+树索引、hash索引、FULLTEXT索引、R-Tree索引

从逻辑角度:

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

普通索引或者单列索引

多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

唯一索引或者非唯一索引

空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。

如何避免回表查询?什么是索引覆盖?

这个问题，如果要看详细版，请参阅文章《Innodb中索引的原理》

这里简单说一下。

例如此时有一张表table1,有一个联合索引(a,b)

执行如下SQL

select a,b from table1
在索引上就能找到结果，就不用回表去查询！

select a,b,c from table2
c列在索引上不存在，就需要回表查询。

需要说明的是覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B+ tree索引做覆盖索引。

为什么唯一索引的插入速度比不上普通索引？为什么唯一索引的查找速度比普通索引快？

这么做的优点:能将多个插入合并到一个操作中，就大大提高了非聚簇索引的插入性能。

InnoDB 从 1.0.x 版本开始引入了 Change Buffer，可以算是对 Insert Buffer 的升级。从这个版本开始，InnoDB 存储引擎可以对 insert、delete、update 都进行缓存。

唯一速度的插入比普通索引慢的原因就是:

唯一索引无法利用Change Buffer

普通索引可以利用Change Buffer

唯一索引的搜索速度比普通索引快的原因就是:

• 普通索引在找到满足条件的第一条记录后，还需要判断下一条记录，直到第一个不满足条件的记录出现。

• 唯一索引在找到满足条件的第一条记录后，直接返回，不用判断下一条记录了。