mysql下的索引使用原则_MySQL

mysql下的索引使用原则

为表建立索引，无疑是对数据库比较好的优化方式之一。以下是自己对索引的总结。

MYSQL QUERY Optimizer对索引的选择

1.即使一个SQL可以选择多个索引，但是大多数情况下它都会选择一个索引，而放弃其它的索引。

使用索引的前提：

1.索引的存是在where条件之后的。

2.在MYSQL中不同的存储引擎对索引的对待也是有点不一样的。

常用的索引类型以及情况

前缀索引(又叫短索引)

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

示例代码:

//创建短索引

create index ix_test on t(col(200))

//适用的情况

SELECT userName,income FROM t WHERE col like 'john%';

也就是说： 短索引的适用范围主要是在like之后第一个非’%’的实例。

组合索引

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。

对于组合索引，MYSQL数据库所进行查询的原理：

select * from users where area=’beijing’ and age=22;

select * from users where area=’beijing’;

如果area有索引,则以上的SQL语句都会使用索引，因为组合索引有最佳左前缀的特性，而select * from users where age=22; 则不会使用索引。

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

单键索引还是组合索引

在一般的应用场景中，只要不是其中某个过滤字段在大多数场景下能过滤90%以上的数据（这种可能性很小），而其他的过滤字段会频繁的更新，一般更倾向于创建组合索引，尤其是在并发量较高的场景下。因为当并发量较高的时候，即使只为每个Query节省了很少的 IO 消耗，但因为执行量非常大，所节省的资源总量仍然是非常可观的。

当然，创建组合索引并不是说就须要将查询条件中的所有字段都放在一个索引中，还应该尽量让一个索引被多个 Query 语句利用，尽量减少同一个表上的索引数量，减少因为数据更新带来的索引更新成本，同时还可以减少因为索引所消耗的存储空间。

有OR在SQL语句中来使用索引的情况

如果有or，where后面就必须全面条件有索引，否则索引将不会起作用,所以一定要为or的全部字段创建索引

SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

ordery by以及索引排序

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。

因此数据库默认排序可以符合要求的情况下不要使用排序操作；(排序功能由索引来完成)尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。