MySQL Repeating index discussion (continuous update in ...)

Information reference: Http://xiezhenye.com/2015/01/%E6%89%BE%E5%88%B0-mysql-%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E7%9A%84%E4%B8 %8d%e8%89%af%e7%b4%a2%e5%bc%95.html

< a > Create a ' problematic ' table1. Create a table test1

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))

2. Create a table Test2

CREATE TABLE test2 (id1 int) NOT NULL default 0, Id2 Int (one) not null default 0, B int (one) default NULL, PRIMARY K EY (ID1,ID2), KEY K1 (b))

< two > Problematic index1. Index containing the primary key

InnoDB itself is a clustered table, each level two index itself contains the primary key, similar to the F1,id index, although the actual harmless, but reflects the user of the MySQL index does not understand. This extra index, ID,F1, wastes storage space and affects data update performance. Indexes containing primary keys can be found in this sentence:

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,  '% ');

Results:

2. Duplicate indexes

An index that contains a repeating prefix that 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.

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;

Results:

3. Low-Sensitivity index

Such indexes are often ignored when they are actually queried because they still scan a large number of records. 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.

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;

Results:

4. Composite PRIMARY Key

Since 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.

SQL is:

Select Table_schema, TABLE_NAME, GROUP_CONCAT (column_name ORDER by Seq_in_index Separator ', ') cols, Max (Seq_in_index) Le N from Information_schema.  STATISTICS where index_name = ' PRIMARY ' and table_schema! = ' MySQL ' GROUP by TABLE_SCHEMA, TABLE_NAME have len>1;

The result is:

MySQL Repeating index discussion (continuous update in ...)