Index
MySQL索引的基本操作CREATE INDEX idx_price on OrderItems(item_price);ALTER TABLE OrderItems DROP INDEX idx_order_num_price;
Explain
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了。
Explain各列的含义如下:
id: 每个 SELECT 都会自动分配一个唯一的标识符
select_type: SELECT 查询的类型
table: 查询的是哪个表
partitions: 匹配的分区
type: 访问类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行 这个是一个估计值
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息
其中Type是重点关注的字段，用以快速评价查询语句性能，常见type性能从差到好如下：
All < Index < Range < Ref < Const
Demo
以OrderItems表为例：
| OrderItems | CREATE TABLE `OrderItems` (
`order_num` int(11) NOT NULL,
`order_item` int(11) NOT NULL,
`prod_id` char(10) NOT NULL,
`quantity` int(11) NOT NULL,
`item_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`order_num`,`order_item`),
KEY `FK_OrderItems_Products` (`prod_id`),
KEY `idx_price` (`item_price`),
KEY `idx_order_num_price` (`order_num`,`item_price`),
CONSTRAINT `FK_OrderItems_Orders` FOREIGN KEY (`order_num`) REFERENCES `Orders` (`order_num`),
CONSTRAINT `FK_OrderItems_Products` FOREIGN KEY (`prod_id`) REFERENCES `Products` (`prod_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> select * from OrderItems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
| 20005 | 1 | BR01 | 100 | 5.49 |
| 20005 | 2 | BR03 | 100 | 10.99 |
| 20006 | 1 | BR01 | 20 | 5.99 |
| 20006 | 2 | BR02 | 10 | 8.99 |
| 20006 | 3 | BR03 | 10 | 11.99 |
| 20007 | 1 | BR03 | 50 | 11.49 |
| 20007 | 2 | BNBG01 | 100 | 2.99 |
| 20007 | 3 | BNBG02 | 100 | 2.99 |
| 20007 | 4 | BNBG03 | 100 | 2.99 |
| 20007 | 5 | RGAN01 | 50 | 4.49 |
| 20008 | 1 | RGAN01 | 5 | 4.99 |
| 20008 | 2 | BR03 | 5 | 11.99 |
| 20008 | 3 | BNBG01 | 10 | 3.49 |
| 20008 | 4 | BNBG02 | 10 | 3.49 |
| 20008 | 5 | BNBG03 | 10 | 3.49 |
| 20009 | 1 | BNBG01 | 250 | 2.49 |
| 20009 | 2 | BNBG02 | 250 | 2.49 |
| 20009 | 3 | BNBG03 | 250 | 2.49 |
+-----------+------------+---------+----------+------------+
18 rows in set (0.03 sec)
举例说明几个Explain type的查询：
all，直接全表查询
mysql> explain select item_price from OrderItems;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | OrderItems | NULL | ALL | NULL | NULL | NULL | NULL | 18 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
index，利用索引
mysql> explain select order_num from OrderItems;
+----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | FK_OrderItems_Products | 30 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------------------+---------+------+------+----------+-------------+
range，对索引进行范围查询，多见于between/in/<>等关键字
mysql> explain select order_num from OrderItems where order_num between 20003 and 20005;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
ref，使用普通索引
mysql> explain select order_num from OrderItems where order_num = 20005;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY | PRIMARY | 4 | const | 2 | 100.00 | Using index |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数。
Optimization
通过分析Explain语句结果可以优化查询性能。一般关注点有：一是type往好的方向优化，二是有order by时，尽量不要在extra中出现Using filesort。
- type优化，如阿里巴巴Java编程规范中定义的那样，一般不允许all和index的查询，会极大影响性能。尽量优化至range以上。方法一般就是建索引，不要为了节省插入性能而去缩减必要的索引。
mysql> CREATE INDEX idx_price on OrderItems(item_price);
然后再执行关于item_price的查询：
mysql> explain select order_num from OrderItems where item_price between 5 and 8;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | idx_price | idx_price | 4 | NULL | 2 | 100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
看到type位置变为range，possible_keys和key的位置出现了我们新建的索引
- 去除Using filesort
执行下列查询
mysql> explain select * from OrderItems where order_num = 20003 order by item_price;
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
使用了fileSort。我们建立索引并再次执行查询如下：
mysql> CREATE INDEX idx_order_num_price on OrderItems(order_num, item_price);
mysql> explain select * from OrderItems where order_num = 20003 order by item_price;
+----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | OrderItems | NULL | ref | PRIMARY,idx_order_num_price | idx_order_num_price | 4 | const | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+-----------------------------+---------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
如果建立了多重索引A-B(A和B是column name)，那么查询语句的where clause中仅使用了A也是可以利用该A-B索引的。事实上，只要查询条件从左至右依次匹配某索引，都是可以利用的。
Problems
另外遇到两个关于索引有序性使用的小坑。
- 查询语句中的字段匹配索引的前半部分，但如果它们是用于in/between，索引失效
比如在建立了order_num/item_price索引的情况下，还是会fileSort：
mysql> explain select order_num from OrderItems where order_num between 20003 and 20005 order by item_price;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where; Using filesort |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
- 单一Order时使用索引不分升降序，但如果对多字段排序，则要求索引顺序和查询语句’一致’
mysql> explain select item_price from OrderItems ORDER BY order_num, item_price;
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_order_num_price | 8 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select item_price from OrderItems ORDER BY order_num, item_price desc;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_price | 4 | NULL | 18 | 100.00 | Using index; Using filesort |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select item_price from OrderItems ORDER BY order_num desc, item_price desc;
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | OrderItems | NULL | index | NULL | idx_order_num_price | 8 | NULL | 18 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
如果理解了MySQL索引的物理实现（B+ Tree)，这些应该就比较容易理解了（TODO）。