索引列仍然需要8秒，不使用索引(mysql 5)

Question

下面是一个耗时近8秒的查询结果，其中有索引可用，但未被使用。只有40,000条记录。

mysql> SELECT * FROM (`phppos_customers`) 
   JOIN `phppos_people` ON `phppos_customers`.`person_id`=`phppos_people`.`person_id`
   WHERE `deleted` = 0 ORDER BY `last_name` asc LIMIT 20
    -> ;
** PERSONAL DATA REMOVED **

mysql> EXPLAIN SELECT * FROM (`phppos_customers`) JOIN `phppos_people` ON `phppos_customers`.`person_id`=`phppos_people`.`person_id` WHERE `deleted` = 0 ORDER BY `last_name` asc LIMIT 20
    -> ;
+----+-------------+------------------+--------+-------------------+---------+---------+--------------------------------+-------+---------------------------------+
| id | select_type | table            | type   | possible_keys     | key     | key_len | ref                            | rows  | Extra                           |
+----+-------------+------------------+--------+-------------------+---------+---------+--------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | phppos_customers | ref    | person_id,deleted | deleted | 4       | const                          | 22545 | Using temporary; Using filesort |
|  1 | SIMPLE      | phppos_people    | eq_ref | PRIMARY           | PRIMARY | 4       | pos.phppos_customers.person_id |     1 |                                 |
+----+-------------+------------------+--------+-------------------+---------+---------+--------------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

mysql> describe phppos_customers;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| person_id      | int(10)      | NO   | MUL | NULL    |       |
| account_number | varchar(255) | YES  | UNI | NULL    |       |
| company_name   | varchar(255) | NO   |     | NULL    |       |
| taxable        | int(1)       | NO   |     | 1       |       |
| deleted        | int(1)       | NO   | MUL | 0       |       |
+----------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> describe phppos_people;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| first_name   | varchar(255) | NO   | MUL | NULL    |                |
| last_name    | varchar(255) | NO   | MUL | NULL    |                |
| phone_number | varchar(255) | NO   |     | NULL    |                |
| email        | varchar(255) | NO   | MUL | NULL    |                |
| address_1    | varchar(255) | NO   |     | NULL    |                |
| address_2    | varchar(255) | NO   |     | NULL    |                |
| city         | varchar(255) | NO   |     | NULL    |                |
| state        | varchar(255) | NO   |     | NULL    |                |
| zip          | varchar(255) | NO   |     | NULL    |                |
| country      | varchar(255) | NO   |     | NULL    |                |
| comments     | text         | NO   |     | NULL    |                |
| person_id    | int(10)      | NO   | PRI | NULL    | auto_increment |
+--------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

编辑:显示创建表格phppos_customers；

| phppos_customers | CREATE TABLE `phppos_customers` (
  `person_id` int(10) NOT NULL,
  `account_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `taxable` int(1) NOT NULL DEFAULT '1',
  `deleted` int(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `account_number` (`account_number`),
  KEY `person_id` (`person_id`),
  KEY `deleted` (`deleted`),
  CONSTRAINT `phppos_customers_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `phppos_people` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

编辑:显示创建表格phppos_people；

| phppos_people | CREATE TABLE `phppos_people` (
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci NOT NULL,
  `person_id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`person_id`),
  KEY `first_name` (`first_name`),
  KEY `last_name` (`last_name`),
  KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=45870 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

编辑显示索引：

mysql> show index from phppos_customers;
+------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table            | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
| phppos_customers |          0 | account_number |            1 | account_number | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |
| phppos_customers |          1 | person_id      |            1 | person_id      | A         |       46217 |     NULL | NULL   |      | BTREE      |         |
| phppos_customers |          1 | deleted        |            1 | deleted        | A         |           2 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> show index from phppos_people;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| phppos_people |          0 | PRIMARY    |            1 | person_id   | A         |       45699 |     NULL | NULL   |      | BTREE      |         |
| phppos_people |          1 | first_name |            1 | first_name  | A         |       45699 |     NULL | NULL   |      | BTREE      |         |
| phppos_people |          1 | last_name  |            1 | last_name   | A         |       45699 |     NULL | NULL   |      | BTREE      |         |
| phppos_people |          1 | email      |            1 | email       | A         |       45699 |     NULL | NULL   |      | BTREE      |         |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.04 sec)

Answer 1

看一下explain输出，这就是mysql现在所做的：

1. 从phppos_customers表开始，在deleted上使用索引查找每行(剩余22k行)包含deleted = 0
2. For的行为同一联接(deleted)从phppos_people查找信息。
3. 将所有内容放在一个临时表中，不使用任何索引进行排序，因为它是一个已联接的结果集，因此必须对所有行进行排序才能找到前20行。

这看起来不像是运行此查询的最佳方式，因为某些原因，优化器选择了错误的联接顺序。

尝试使用STRAIGHT_JOIN强制不同的联接顺序：

SELECT * FROM `phppos_people` STRAIGHT_JOIN `phppos_customers` 
  ON `phppos_customers`.`person_id`=`phppos_people`.`person_id` 
WHERE `deleted` = 0 
ORDER BY `last_name` ASC LIMIT 20;

这一次，mysql应该：

1. 从phppos_people开始，按last_name对该列使用索引排序。每行的
2. 连接phppos_customers并检查是否限制了优化，并在首先找到20后停止。