Shulou(Shulou.com)05/31 Report--

Editor to share with you how to properly add the index of Mysql, I hope you will gain something after reading this article, let's discuss it together!

Here's a brief introduction to the index:

Add an index to improve database query performance, the index is the most inexpensive thing, no memory, no program change, no sql, as long as the implementation of a correct create index, the query speed may be increased a hundredfold, this is tempting, but there is no free lunch, the improvement of query speed is at the expense of insert update delete speed. And the index size is generally 1/3 of the data, coupled with the index to be loaded into memory, if all fields are indexed at the expense of memory, so it is necessary to add the index appropriately.

Here is a brief introduction to the indexes commonly used in mysql:

Before adding an index, it is best to check which indexes already exist in the table: show index from table name

1. Primary key index

Note: there can be only one primary key index in a table, but you can add multiple indexes such as unique index, general index, full-text index.

Primary key index: generally added when building a table, such as: id is generally primary key index plus automatic increment.

Add primary key index after table creation: alter table table_name add primary key (column name)

The characteristics of the primary key index: cannot be empty and unique.

2. General index

Create a normal index: alter table table_name add index index name (column1,column2)

3. Unique index

Create a unique index: ALTER TABLE `table_ name` ADD UNIQUE (`column`)

The difference between a unique index and a primary key index

Unique index: there can be multiple null, but the data content cannot be duplicated

Primary key index: cannot be null, and the content can only be unique.

The difference between the two is that the primary key index cannot be null unique. It can have multiple null. The rest is the same.

4. Full-text index

Only MyISAM is valid for full-text indexing (InnoDB also supports full-text indexing after mysql5.6). [5.7 does not support MyISAM]

Full-text indexing is mainly aimed at text files, such as articles and titles.

Create a full-text index when you create a table:

CREATE TABLE `title` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar (200) DEFAULT NULL, `content` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`, `content`) ENGINE=MyISAM (InnoDB also supports full-text indexing after 5.6,5.7 does not support MyISAM engine) DEFAULT CHARSET=utf8

Create a full-text index in an existing table:

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article (title,content)

There are also some points to pay attention to when using after creating a full text index:

It is well known that fuzzy queries in a database are queried using the like keyword, for example:

SELECT * FROM article WHERE content LIKE'% query string%'

So, are we using full-text indexes in the same way? Of course not, we must use unique syntax to query using full-text indexing. For example, if we want to retrieve the specified query string in the title and content columns of the article table, we can write the SQL statement as follows:

SELECT * FROM article WHERE MATCH (title,content) AGAINST ('query string')

Strong note: the full-text index provided by MySql can only search full-text in English, but not in Chinese at present. If we need to carry out full-text retrieval of text data including Chinese, we need to use Sphinx / Coreseek technology to deal with Chinese.

Note: currently, when using the full-text index included with MySql, if the length of the query string is too short, you will not be able to get the desired search results. The default minimum length of words that can be found by MySql full-text index is 4 characters. In addition, if the string of the query contains a stop word, the stop word will be ignored.

If possible, try to create a table and insert all the data before creating a full-text index, rather than creating a full-text index directly when you create the table, because the former is more efficient than the latter.

Delete index sql statement: alter table table_name drop index index name

After the brief introduction above, which fields should be indexed?

1. An index should be created for fields that are queried frequently.

2. Fields that are updated very frequently should not be indexed.

3. Fields with poor uniqueness, such as the gender field, should not be indexed.

4. Fields that do not appear after the where condition should not be indexed.

If the following conditions are met, an index should be created:

1. Fields that are frequently queried, which often appear after the where condition, should be indexed.

2. For fields that are updated infrequently, you can create an index.

Considerations for the use of indexes

1. For a multi-column index created, the index is generally used as long as the query condition uses the leftmost column.

For example, we added a composite index to title,content.

Select * from table_name where title = 'test'; will use the index

Select * from table_name where content = 'test'; will not use the index

two。 For queries that use like, the index is not used if the query is'% a', while the index is used by like'a%'. Changes such as% and _ cannot be used at the front.

3. If there is an or in the condition, it will not be used even if there is a conditional index in it.

4. If the column type is a string, be sure to quote the data in quotation marks in the condition.

View the usage of the index: show status like'Handler_read%'

Handler_read_key: this value is as high as possible, which indicates the number of times it has been queried using the index.

Handler_read_rnd_next: the higher this value, the less efficient the query.

After reading this article, I believe you have a certain understanding of "how to properly add an index to Mysql". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.