Mysql索引优化

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

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

2.索引字段越小越好

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

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

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

如何判断离散度:

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

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

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

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

查询重复及冗余索引

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

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
阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页