MySQL索引创建及优化

联合索引中 索引建立在离散度大的字段上面

离散度指利用COUNT(DISTINCT ..)获取字段的唯一值,唯一值越多的离散度越大

索引的优化----重复及冗余索引

重复索引：指相同的列以相同的顺序建立的同类型的索引，如下表

CREATE TABLE IF NOT EXISTS test(

        id SMALLINT PRIMARY KEY AUTO_INCREMENT,

        name CHAR(10) NOT NULL,

        UNIQUE(id)

）ENGINE=INNODB;

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

CREATE TABLE IF NOT EXISTS test(

        id SMALLINT PRIMARY KEY AUTO_INCREMENT,

        name CHAR(10) NOT NULL,

        KEY(name,id)

）ENGINE=INNODB;

如何发现数据库中的重复和冗余索引

1.进入MySQL自带的information_schema-----use information_schema;

2.执行如下SQL语句

SELECT a.TABLE_SCHEMA AS '数据库名',a.TABLE_NAME AS '表名',a.INDEX_NAME AS '索引一',b.INDEX_NAME 

AS '索引二',a.COLUMN_NAME AS '重复列名' FROM STATISTICS AS a

JOIN STATISTICS AS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME 

AND a.COLUMN_NAME = b.COLUMN_NAME AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX

WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <>b.INDEX_NAME;