mysql数据库简单优化 – sql索引优化

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

1.在where从句，group by从句，order by从句，on从句中出现的列
2.索引字段越小越好
3.离散度大的列放到联合索引的前面

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

是index(staff_id,customer_id)好？还是index(customer_id,staff_id)好
通过判断customer_id和staff_id在表中唯一值的个数，唯一值越多，离散度越大.
由于customer_id的离散度更大，所以应该是后者，这里举例所用到的数据库是sakila数据库。

重复及冗余索引

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

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id) //这里和主键重复
)engine=innodb;

冗余索引是指多个索引的前缀列是相同的，或是在联合索引中包含了主键的索引

如下面中key(name,id)就是一个冗余索引

create table test(
    id int not null primary key,
    name varchar(10) not null,
    title varchar(50) 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 \G

会列出相关的某数据库中的某表的索引1和索引2某个字段重复。

当然也可以使用pt-duplicate-key-checker工具来查询，更加方便.会检查出重复冗余的索引，并给出删除建议
如:

pt-duplicate-key-checker -uroot -proot -h 127.0.0.1

查询未使用的索引

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

pt-index-usage -uroot -proot mysql-slow.log

MySQL数据库简单优化(一)
MySQL数据库简单优化(三)

MySQL数据库简单优化(二)