Find a bad index in the MySQL database

To demonstrate, first build two tables with bad indexes and get some data.

mysql> show create table test1\g*************************** 1. row *****        Table: test1Create Table:  create table  ' test1 '   (   ' id '  int (one)  NOT NULL,   ' F1 '   Int (one)  DEFAULT NULL,   ' F2 '  int (one)  DEFAULT NULL,   ' F3 '  int ( One)  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=latin11 row in set  (0.00 sec) mysql> show create table  test2\g*************************** 1. row ***************************        table: test2create table: create table  ' test2 '   (   ' id1 '  int (one)  not  NULL DEFAULT  ' 0 ',   ' id2 '  int (one)  NOT NULL DEFAULT  ' 0 ',    ' B '  int (one)  DEFAULT NULL,  PRIMARY KEY  (' Id1 ', ' id2 '),   KEY  ' K1 '   (' B '))  engine=innodb default charset=latin11 row in set   (0.00 SEC) mysql> select count (*)  from test2 group by b;                                                                                                              +----------+| count (*)  |+----------+|        32 | |        17 |+----------+2 rows in set  (0.00  Sec

1. The index containing the primary key,
InnoDB itself, is a clustered table, and each two-level index itself contains a primary key, similar to F1, and the ID index is actually harmless, but reflects the user's lack of knowledge of the MySQL index. A similar ID, F1, is a redundant index that wastes storage space and affects data update performance. Indexes that contain primary keys can be found all with such a sentence of 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. The repeating index prefix
contains the index of the repeating prefix, which can be replaced entirely by another index that contains the prefix, which is an extra index. Redundant indexes can waste storage space and affect data update performance. Such an index can also be found with a single sentence of 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     ->&nbsP;    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        &NBsp;| 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. Low-sensitivity index
Indexes such as these are still scanned for a large number of records, and are often ignored in actual queries. However, it is still useful in some cases. It is therefore necessary to further analyze it according to actual situation. Here is an index with a sensitivity of less than 10%, which can be adjusted as needed.

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&NBSP;SEC)

4. Composite PRIMARY key
because InnoDB is a clustered table, each two-level index will contain primary key values. A composite primary key causes a two-level index to be large, affecting two-level index query performance and affecting update performance. The same needs to be further analyzed according to actual situation.

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    &NBsp;    | test2      | id1,id2                             |    2 |+--------------+------------+------------ -----------------------+------+1 rows in set  (0.01 sec)

Find a bad index in the MySQL database