如何选择合适的列建立索引

1.在where从句，group by从句，order by从句，on从句中出现的列

2.索引字段越小越好

3.离散度大的列放到联合索引的前面

1
select * from payment where staff_id = 2 and customer_id = 584;

由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)，而不是使用index(staff_id,ustomer_id)。

如何判断离散度:

1
2
3
4
5
6
Mysql> select count(distinct customer_id),count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                        2 |
+-----------------------------+--------------------------+

可以看出customer_id的离散度更大

索引的维护及优化

正常情况下，我们认为建立索引可以优化我们的查询效率，但是会降低写入语句的效率。实际情况下，过多的索引不但会对写入语句有影响，还会影响查询语句的效率。这是因为数据库在使用索引查询的时候，首先要分析使用哪一个索引进行查询，索引越多，分析的时间就越多。所以我们需要维护我们的索引。

重复及冗余的索引

重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中primary key和id列上的索引就是重复索引：

1
2
3
4
5
create table test(
id int not null primary key,//主键
name varchar(10) not null,
unique(id)//唯一索引
)engine = innodb;

冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个例子中key(name,id)就是一个冗余索引：

1
2
3
4
5
create table test(
id int not null primary key,//主键
name varchar(10) not null,
key(name,id)//索引
)engine = innodb;

查询重复及冗余索引

用sql的方式查询索引问题：

1
2
use information_schema;
select a.table_schema as '数据名',a.table_name as '表名',a.index_name as '索引1',b.index_name as '索引2',a.column_name as '重复列名' from statistics a join statistics b on a.table_schema = b.table_schema and a.table_name = b.table_name and a.seq_in_index = b.seq_in_index and a.column_name = b.column_name where a.seq_in_index = 1 and a.index_name <> b.index_name;

使用pt-duplicate-key-checker工具检查重复及冗余索引

1
pt-duplicate-key-checker -uxxx -pxxx -hxxx

业务变更导致不再使用的索引（删除）

通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析

1
pt-index-usage -uxxx -pxxx /xxx/Mysql-slow.log