Mysql索引优化

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

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)。

如何判断离散度:

123456
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列上的索引就是重复索引:

12345
大专栏  Mysql索引优化lass="line">create table test(id int not null primary key,//主键name varchar(10) not null,unique(id)//唯一索引)engine = innodb;

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

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

查询重复及冗余索引

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

12
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

相关推荐