???????????????????????????????????????ū????????
????mysql> show create table test1/G
????*************************** 1. row ***************************
????Table: test1
????Create Table: CREATE TABLE `test1` (
????`id` int(11) NOT NULL??
????`f1` int(11) DEFAULT NULL??
????`f2` int(11) DEFAULT NULL??
????`f3` int(11) DEFAULT NULL??
????PRIMARY KEY (`id`)??
????KEY `k1` (`f1`??`id`)??
????KEY `k2` (`id`??`f1`)??
????KEY `k3` (`f1`)??
????KEY `k4` (`f1`??`f3`)??
????KEY `k5` (`f1`??`f3`??`f2`)
????) ENGINE=InnoDB DEFAULT CHARSET=latin1
????1 row in set (0.00 sec)
????mysql> show create table test2/G
????*************************** 1. row ***************************
????Table: test2
????Create Table: CREATE TABLE `test2` (
????`id1` int(11) NOT NULL DEFAULT '0'??
????`id2` int(11) NOT NULL DEFAULT '0'??
????`b` int(11) DEFAULT NULL??
????PRIMARY KEY (`id1`??`id2`)??
????KEY `k1` (`b`)
????) ENGINE=InnoDB DEFAULT CHARSET=latin1
????1 row in set (0.00 sec)
????mysql> select count(*) from test2 group by b;                                                                                                       
????+----------+
????| count(*) |
????+----------+
????|       32 |
????|       17 |
????+----------+
????2 rows in set (0.00 sec)
??????? mysql ??????е????????
??????????????webfish.se??
????1. ??????????????
????innodb ?????????????????????????????????????? f1?? id ??????????????к?????????????????? mysql ??????????????? id?? f1 ??????????????????洢???????????????????????????????????????????? sql ????????????
????mysql> select c.*?? pk from
????->   (select table_schema?? table_name?? index_name?? concat('|'?? group_concat(column_name order by seq_in_index separator '|')?? '|') cols
????->     from INFORMATION_SCHEMA.STATISTICS
????->     where index_name != 'PRIMARY' and table_schema != 'mysql'
????-> group by table_schema?? table_name?? index_name) c??
????->   (select table_schema?? table_name?? concat('|'?? group_concat(column_name order by seq_in_index separator '|')?? '|') pk
????->     from INFORMATION_SCHEMA.STATISTICS
????->     where index_name = 'PRIMARY' and table_schema != 'mysql'
????-> group by table_schema?? table_name) p 
????-> where c.table_name = p.table_name and c.table_schema = p.table_schema and c.cols like concat('%'?? pk?? '%');
????+--------------+------------+------------+---------+------+
????| table_schema | table_name | index_name | cols    | pk   |
????+--------------+------------+------------+---------+------+
????| test         | test1      | k1         | |f1|id| | |id| |
????| test         | test1      | k2         | |id|f1| | |id| |
????+--------------+------------+------------+---------+------+
????2 rows in set (0.04 sec)
????2. ?????????
??????????????????????????????????????????????????????棬?????????????????????????洢?????????????????????????????????????? sql ???????????
????mysql> select c1.table_schema?? c1.table_name?? c1.index_name??c1.cols??c2.index_name?? c2.cols from
????->   (select table_schema?? table_name?? index_name?? concat('|'?? group_concat(column_name order by seq_in_index separator '|')?? '|') cols
????->     from INFORMATION_SCHEMA.STATISTICS
????->     where table_schema != 'mysql' and index_name!='PRIMARY'
????-> group by table_schema??table_name??index_name) c1??  
????->   (select table_schema?? table_name??index_name?? concat('|'?? group_concat(column_name order by seq_in_index separator '|')?? '|') cols
????->     from INFORMATION_SCHEMA.STATISTICS
????->     where table_schema != 'mysql' and index_name != 'PRIMARY'
????-> group by table_schema?? table_name?? index_name) c2
????-> where c1.table_name = c2.table_name and c1.table_schema = c2.table_schema and c1.cols like concat(c2.cols?? '%') and c1.index_name != c2.index_name;
????+--------------+------------+------------+------------+------------+---------+
????| table_schema | table_name | index_name | cols       | index_name | cols    |
????+--------------+------------+------------+------------+------------+---------+
????| test         | test1      | k1         | |f1|id|    | k3         | |f1|    |
????| test         | test1      | k4         | |f1|f3|    | k3         | |f1|    |
????| test         | test1      | k5         | |f1|f3|f2| | k3         | |f1|    |
????| test         | test1      | k5         | |f1|f3|f2| | k4         | |f1|f3| |
????+--------------+------------+------------+------------+------------+---------+
????4 rows in set (0.02 sec)
????3. ???????????
???????????????????????????????????????????????????????????Щ??????????????????????????????????????????????????????С?? 10% ????????????????????????????
????mysql> select p.table_schema?? p.table_name?? c.index_name?? c.car?? p.car total from
????->   (select table_schema?? table_name?? index_name?? max(cardinality) car
????->     from INFORMATION_SCHEMA.STATISTICS
????-> where index_name != 'PRIMARY'
????-> group by table_schema?? table_name??index_name) c??
????->   (select table_schema?? table_name?? max(cardinality) car
????->     from INFORMATION_SCHEMA.STATISTICS
????-> where index_name = 'PRIMARY' and table_schema != 'mysql'
????-> group by table_schema??table_name) p
????-> where c.table_name = p.table_name and c.table_schema = p.table_schema and p.car > 0 and c.car / p.car < 0.1;
????+--------------+------------+------------+------+-------+
????| table_schema | table_name | index_name | car  | total |
????+--------------+------------+------------+------+-------+
????| test         | test2      | k1         |    4 |    49 |
????+--------------+------------+------------+------+-------+
????1 row in set (0.04 sec)
????4. ????????
???????? innodb ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????
????mysql> select table_schema?? table_name?? group_concat(column_name order by seq_in_index separator '??') cols?? max(seq_in_index) len
????->    from INFORMATION_SCHEMA.STATISTICS
????->    where index_name = 'PRIMARY' and table_schema != 'mysql'
????->    group by table_schema?? table_name having len>1;
????+--------------+------------+-----------------------------------+------+
????| table_schema | table_name | cols                              | len  |
????+--------------+------------+-----------------------------------+------+
????| test         | test2      | id1??id2                           |    2 |
????+--------------+------------+-----------------------------------+------+
????1 rows in set (0.01 sec)