MySQL redundancy and duplicate Indexes

Redundancy and duplicate Indexing

Concept of redundancy and repeated indexing:

MySQL allows you to create multiple indexes on the same column, either intentionally or unintentionally. MySQL needs to maintain duplicate indexes separately, and the optimizer also needs to consider them one by one when optimizing the query, which will affect the performance.

Duplicate index: an index of the same type is created on the same column in the same order. Avoid creating duplicate indexes in this way and remove them immediately after discovery.

Eg: sometimes duplicate indexes are created inadvertently.

CREATE TABLE test (  id INT NOT NULL PRIMARY KEY,  a  INT NOT NULL,  INDEX(ID))ENGINE=InnoDB;

An inexperienced user may want to create a primary key and then add an index for query. In fact, the primary key is the index. So there is no need to add an INDEX (ID.

There are some differences between redundant indexes and duplicate indexes. If an index (A, B) is created and then an index (A) is created, it is A redundant index, because this is only the prefix index of the previous index. Therefore, indexes (A and B) can also be used as indexes (A) (This redundancy is only for B-Tree indexes ). Redundant indexes usually occur when a new index is added to a table. For example, someone may add A new index (A, B) instead of extending an existing index (). Another case is to extend an index to (A, ID), where ID is the primary key. For InnoDB, the primary key column is already included in the secondary index, and the index is redundant.

In most cases, no redundant indexes are required. You should try to expand existing indexes instead of creating new ones. However, sometimes redundant indexes are required for performance considerations, because the expansion of existing indexes will lead to too large, thus affecting the performance of other queries using this index.

Eg: if you have an index on an integer column, you need to add an extra long VARCHAR column to expand the index, and the performance may drop sharply. In particular, when a query regards this index as a covered index, or this is a MyISAM table and there are many range queries.

Note: The more indexes in the table, the slower the insert speed. In general, adding a new index will slow down INSERT, UPDATE, DELETE, and other operations, especially when the new index reaches the memory bottleneck.

Solutions to redundant and duplicate indexes:

The solution to redundant indexes and duplicate indexes is simple. You can delete these indexes, but the first thing you need to do is to find such indexes.

Method:

1: You can write some complex queries to access the INFORMATION_SCHEMA table.

2: Use some views in common_schema to locate

3: Use the pt-duplicate-key-checker tool in Percona Toolkit.

Eg: Use of pt-duplicate-key-checker

First, install the pt-duplicate-key-checker tool. For more information, see the relevant official manual.

Syntax:

pt-duplicate-key-checker[OPTIONS][DSN]

Main parameters:

-U: Specifies the user name used to connect to the database.

-P: password used to connect to the database

-- Charset: Specifies the character set.

-- Database: List of database names to be checked

Example:

pt-duplicate-key-checker -udbuser -pdbpaswd --charset=gbk \--database=dbname

After the execution, the duplicate and redundant indexes of the dbname database will be calculated as follows:

# ######################################################################### dbname.test1                                              # ######################################################################### vkey is a left-prefix of keydesc_index# Key definitions:#   KEY `vkey` (`VehicleKey`),#   KEY `keydesc_index` (`VehicleKey`,`Description`)# Column types:#         `vehiclekey` char(8) not null default ''#         `description` char(255) not null default ''# To remove this duplicate index, execute:ALTER TABLE `dbname`.`test1` DROP INDEX `vkey`;# ######################################################################### dbname.test2                                               # ######################################################################### vkey is a duplicate of PRIMARY# Key definitions:#   KEY `vkey` (`VehicleKey`),#   PRIMARY KEY (`VehicleKey`),# Column types:#         `vehiclekey` varchar(8) not null default '0'# To remove this duplicate index, execute:ALTER TABLE `dbname`.`test2` DROP INDEX `vkey`;

It counts all repeated and redundant indexes and provides SQL statements to be executed. Is it convenient.

For details about all the parameters or usage of the tool, see pt-duplicate-key-checker.

This article is from the Focus on the database Blog, please be sure to keep this source http://lgdvsehome.blog.51cto.com/3360656/1278740