## 2. 索引优化评判标准

### 2.2 EXPLAIN语句

 1 SELECT store_id, film_id FROM sakila.inventory;

 1 EXPLAIN SELECT store_id, film_id FROM sakila.inventory;

## 3. 提升索引性能

### 3.1 选择性最大原理

 1 SELECT COUNT(DISTINCT col1)/COUNT(*) FROM A;

 1 2 3 4 5 6 7 SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3, COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4, COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5, COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6, COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7 FROM sakila.city_demo

### 3.2 频率最大原理

 1 SELECT * FROM user where sex IN ("male", "female") and ....;

### 3.3 为排序而设计索引

• 所有列的排序方向（倒序或正序）都一样
• ORDER BY的顺序必须满足索引列顺序最左前缀的要求
• 但是当前导列为常量时，ORDER BY子句可以不满足索引的最左前缀

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

### 3.4 使用聚簇索引

 1 2 3 4 5 6 CREATE TABLE layout_test ( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2) );

• 将相关数据保存在一起
• 顺序数据访问更快
• 与覆盖索引相结合可以避免二级索引的两次查找

## 5. 优化SQL语句

### 5.1 多条件查询索引设计

 1 2 3 4 WHERE sex IN("male","female") AND region IN("SH","SC","ZJ") AND career IN("EG","TH")

### 5.2 索引不作为的原因

 1 2 SELECT ... FROM ... WHERE age < 50 AND ... SELECT ... FROM ... WHERE salary > 10000 AND ...

## 7. 引用

[1] Balling D J, Zawodny J. High Performance MySQL[M]. Safari Tech Books Online, 2004.

[2] Lahdenmaki T, Leach M. Relational Database Index Design and the Optimizers 2nd [M]. John Wiley & Sons, 2005.

[3] 文平 . Oracle数据库性能优化的艺术, 2012