-
您可以到这里下载本篇博文所使用的数据库以及优化工具mysqldumpslow和pt-query-digest:
-
关于explain语句的结果参数详解也可以参考如上博文。
前言:
如果一个索引包含(或覆盖)所有需要查询的字段的值,则称该索引为“覆盖索引”,对于那些经常出现,且只需要查找部分数据的select语句,我们就可以建立覆盖索引对其进行优化。
索引小知识:
唯一索引:
唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如,如果在employee表中职员的姓(lname)上创建了唯一索引,则任何两个员工都不能同姓。主键索引:
数据库表经常有一列或多列组合,其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时,它还允许对数据的快速访问。聚集索引:
在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比,聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别,如字典默认按字母顺序排序,读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字,则需按字典前面的索引,举例按偏旁进行定位,找到该字对应的页数,再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。索引列:
可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为姓和名两列设置判据,那么在这两列上创建多列索引将很有意义。
如何选择合适的列建立索引?
- 在where从句,group by从句,order by从句, on从句中出现的列
- 索引字段越小越好
解释:我们所建立的索引在数据库中是以页的方式进行存取,每一页的内存是固定的,建立索引列的字段越小,每一页所能存取的索引就越多,查找索引时扫描的页数就越小,IO也就越小。 - 离散度大的列放到联合索引的前面
针对于第三条,我们做一下详细的解释:
- 离散度:一组数据相对于给定中心的偏离程度,数值越大数据越分散,越“平均”。
- 联合索引:如果经常要用到多个字段的多条件查询,可以考虑建立联合索引,一般是除第一个字段外的其它字段不经常用于条件筛选情况,比如说a,b 两个字段,如果经常用a条件或者a+b条件去查询,而很少单独用b条件查询,那么可以建立a,b的联合索引。如果a和b都要分别经常独立的被用作查询条件,那还是建立多个单列索引。
举个栗子:
select * from payment where staff_id = 2 and customer_id = 584;
是index(staff_id, customer_id)好? 还是index(customer_id, staff_id)好?
由于customer_id的离散度更大,可选择性更高,所以应该使用index(customer_id, staff_id),那么如何去判断离散度呢?
payment表结构如下:
mysql> desc payment;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| customer_id | smallint(5) unsigned | NO | MUL | NULL | |
| staff_id | tinyint(3) unsigned | NO | MUL | NULL | |
| rental_id | int(11) | YES | MUL | NULL | |
| amount | decimal(5,2) | NO | | NULL | |
| payment_date | datetime | NO | MUL | NULL | |
| last_update | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
我们一般使用查看唯一值的方法来判断列的离散程度,唯一值越多,离散程度越高:
mysql> select count(distinct customer_id), count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
| 599 | 2 |
+-----------------------------+--------------------------+
显然,customer_id的值更多,离散度更大,所以把它作为联合索引的第一列:
create index cust_staff_index on payment(customer_id,staff_id);