mysql索引优化

1.LIKE

mysql不能在索引中执行LIKE操作。这是底层存储引擎API的限制，mysql5.5和更早的版本中只允许在索引中做简单比较操作（例如等于，不等于以及大于）。mysql能在索引中做最左前缀匹配的LIKE比较，因为该操作可以转换为简单的比较操作，但是如果是通配符开头的LIKE查询，存储引擎就无法做比较匹配。这种情况下，mysql服务器只能提取数据行的值而不是索引值来做比较。

2.独立的列

我们通常会看到一些查询不当地使用索引，或者使得mysql无法使用已有的索引。如果查询中的列不是独立的，则mysql就不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面这个查询无法使用actor_id列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id+1=5;

凭肉眼很容易看出WHERE中的表达式其实等价于actor_id=4，但是mysql无法自动解析这个方程式。这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

 

下面是另一个常见的错误：

SELECT ... WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10;

3.选择合适的索引列顺序

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。

4.使用索引扫描来做排序

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（顺序或正序）都一样时，mysql才能使用索引来对结果做排序。

例如，Sakila示例数据库的表rental在列（rental_date，inventory_id，customer_id）上有名为rental_date的索引

(rental_date,inventory_id,customer_id):
    CREATE TABLE rental(
       ...
       PRIMARY KEY(rental_id),
       UNIQUE KEY rental_date(rental_date,inventory_id,customer_id),
       KEY idx_fk_inventory_id(inventory_id),
       KEY idx_fk_customer_id(customer_id),
       KEY idx_fk_staff_id(staff_id),
       ...
    );

1.下面这个查询使用了两种不同的排序方向，但是索引列都是正序排序的：

...WHERE rental_date='2005-05-25' ORDER BY inventory_id DESC,customer_id ASC

2.下面这个查询的ORDER BY子句中引用了一个不在索引中的列：

...WHERE rental_date='2005-05-25' ORDER BY inventory_id,staff_id;

3.下面这个查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀：

...WHERE rental_date='2005-05-25' ORDER BY customer_id;

4.下面这个查询在索引列的第一列上是范围条件，所以mysql无法使用索引的其余列：

...WHERE rental_date>'2005-05-25' ORDER BY inventory_id,customer_id;

5.这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：

...WHERE rental_date='2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;