Shulou(Shulou.com)06/02 Report--

This article is to share with you about how to optimize the index type in Mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1 Type of index

UNIQUE unique index

The same value cannot appear, there can be a null value.

INDEX General Index

The same indexed content is allowed.

PRIMARY KEY primary key index

The same value is not allowed and cannot be null, and a table can have only one primary_key index.

Fulltext index full-text index

The above three indexes all work on the value of the column, but the full-text index can target a word in the value, such as a word in an article, but it is not useful because it is only supported by myisam and English, and the efficiency is unflattering, but it can be accomplished by third-party applications such as coreseek and xunsearch.

2 CURD of index

Creation of index

ALTER TABLE

Suitable for adding tables after they have been created.

ALTER TABLE table name ADD index type (unique,primary key,fulltext,index) [index name] (field name)

ALTER TABLE `table_ name` ADD INDEX `index_ name` (`index list`)-the index name, but not; if not, the current index name is the field name. ALTER TABLE `table_ name` ADD UNIQUE (`column_ List`) ALTER TABLE `table_ name` ADD PRIMARY KEY (`table_ List`) ALTER TABLE `table_ name` ADD FULLTEXT KEY (`column_ List`)

CREATE INDEX

CREATE INDEX can add a normal index or an UNIQUE index to a table.

-- example: only these two indexes can be added: CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list)

In addition, you can add the following when creating a table:

CREATE TABLE `test1` (`id` smallint (5) UNSIGNED AUTO_INCREMENT NOT NULL,-- Note: if the primary key index is created below, there is no need to create `username` varchar (64) NOT NULL COMMENT 'username', `nickname` varchar (50) NOT NULL COMMENT 'nickname / name', `intro`text, PRIMARY KEY (`id`), UNIQUE KEY `unique1` (`username`),-- index name, but not Do not use the same KEY `index1` (`nickname`), FULLTEXT KEY `intro` (`intro`) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=' backend user table as the column name

Deletion of index

DROP INDEX `index_ name`ON `talbe_ name` ALTER TABLE `table_ name` DROP INDEX `index_ name`-these two sentences are equivalent: delete the index index_name; ALTER TABLE `table_ name` in table_name. Delete the primary key index. Note that the primary key index can only be deleted in this way.

View of the index

Show index from tablename

Changes to the index

Change the wool, delete it and rebuild it.

3 skills of creating index

Create an index for columns with high dimensions.

The number of non-duplicate values in the data column, the higher the number, the higher the dimension.

If there are 8 rows of data in the data table, the dimension of the table is 4.

To create an index for columns with high dimensions, such as gender and age, the age dimension is higher than gender.

Columns such as gender are not suitable for creating indexes because the dimensions are too low.

Use the index on the columns that appear in where,on,group by,order by.

Use indexes on smaller data columns, which makes the index file smaller and allows more index keys to be loaded in memory.

Use a prefix index for longer strings.

Do not create too many indexes, in addition to adding extra disk space, it has a great impact on the speed of DML operations, because each time it is added or deleted, it has to be re-indexed.

Using combined indexes, you can reduce the file index size and use it faster than multiple single-column indexes.

4 combined index and prefix index

Note that these two terms are a term for indexing techniques, not the type of index.

Combinatorial index

What is the difference between a MySQL single-column index and a combined index?

To visually compare the two, first create a table:

CREATE TABLE `myIndex` (`i_ testID` INT NOT NULL AUTO_INCREMENT, `vc_ Name` VARCHAR (50) NOT NULL, `vc_ City` VARCHAR (50) NOT NULL, `i_ INT NOT NULL, `i_ SchostID` INT NOT NULL, PRIMARY KEY (`itestID`))

Suppose there are 1000 pieces of data in the table, and five vc_Name= "erquan" records are distributed among the 10000 records, but the combination of city,age,school is different. Take a look at this T-SQL:

SELECT `i_ testID` FROM `myIndex` WHERE `vc_ name` = 'erquan' AND `vc_ City` =' Zhengzhou 'AND `i_ Age` = 25;-- Associated search

First consider building an MySQL single-column index:

An index is established on the vc_Name column. When executing T-SQL, MYSQL quickly targets five records in vc_Name=erquan and takes them out and puts them in an intermediate result set. In this result set, we first exclude the records whose vc_City is not equal to "Zhengzhou", then exclude the records whose i_Age is not equal to 25, and finally screen out the only records that meet the criteria. Although the index has been established on vc_Name, and MYSQL does not have to scan the whole table when querying, the efficiency has been improved, but it is still a long way from our requirements. Similarly, the efficiency of MySQL single-column indexes established in vc_City and i_Age is similar.

In order to further extract the efficiency of MySQL, it is necessary to consider the establishment of a composite index. Is to build the vc_Name,vc_City,i_Age into an index:

ALTER TABLE `myIndex`ADD INDEX `name_city_ age` (vc_Name (10), vc_City,i_Age)

When building a table, the length of vc_Name is 50. Why use 10 here? This is the prefix index we will talk about below, because in general, the length of the name will not exceed 10, which will speed up the index query, reduce the size of the index file, and improve the update speed of INSERT.

When performing T-SQL, MySQL does not need to scan any records to find a unique record!

If you set up a single-column index on vc_Name,vc_City,i_Age and let the table have three single-column indexes, is the query as efficient as the above combined index? The answer is very different, much lower than our combined index. Although there are three indexes at this time, MySQL can only use one of the single-column indexes that it seems to be the most efficient, and the other two are not needed, that is, a full table scan process.

The establishment of such a combined index is actually equivalent to the establishment of:

Vc_Name,vc_City,i_Age

Vc_Name,vc_City

Vc_Name

Such three combined indexes! Why is there no composite index like vc_City,i_Age? This is due to the result of the leftmost prefix of the mysql composite index. The simple understanding is to start with the leftmost combination. Not all queries that contain these three columns will use this composite index, but the following T-SQL will:

SELECT * FROM myIndex WHREE vc_Name= "erquan" AND vc_City= "Zhengzhou" SELECT * FROM myIndex WHREE vc_Name= "erquan"

The following will not be used:

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City= "Zhengzhou" SELECT * FROM myIndex WHREE vc_City= "Zhengzhou"

That is, name_city_age (vc_Name (10), vc_City,i_Age) indexes from left to right, and Mysql does not perform an index query if there is no left front index.

Prefix index

If the length of the index column is too long, this kind of column index will produce a large index file, which is not easy to operate. The index prefix index can be controlled at an appropriate point by using the prefix index method. Control at 0.31 gold value (greater than this value can be created).

SELECT COUNT (DISTINCT (LEFT (`title`, 10)) / COUNT (*) FROM Arctic;-A prefix index can be created if this value is greater than 0.31, and Distinct can repeat ALTER TABLE `user`ADD INDEX `uname` (title (10));-add the prefix index SQL to build the index of the person at 10, which can reduce the size of the index file and speed up the index query.

5 what kind of sql does not walk the index

Try to avoid these sql without indexing.

SELECT `sname`FROM `stu`stu`WHERE `age` + 10x30posure-Index will not be used because all index columns participate in the calculation of SELECT `sname` FROM `stu` WHERE LEFT (`date`, 4)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.