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

This article mainly explains "the syntax of the index in MySQL and the advantages and disadvantages of the index". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. please follow the editor's way of thinking to study and learn "the syntax of the index in MySQL and the advantages and disadvantages of the index".

I. the syntax of the index in MySQL

Create an index

Add an index when you create a table

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (16) NOT NULL, INDEX [indexName] (username (length)

Add an index after the table is created

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name (column_name); or CREATE INDEX index_name ON my_table (column_name)

Note:

1. The index needs to take up disk space, so when creating the index, consider whether the disk space is enough or not.

2. The table needs to be locked when creating the index, so the actual operation needs to be done during the idle period of the business.

Query according to index

Specific query: SELECT * FROM table_name WHERE column_1=column_2; (indexed for column_1)

Or fuzzy query SELECT * FROM table_name WHERE column_1 LIKE'% 3 'SELECT * FROM table_name WHERE column_1 LIKE' 3% 'SELECT * FROM table_name WHERE column_1 LIKE'% 3%

SELECT * FROM table_name WHERE column_1 LIKE'_ good _'

If you want to indicate that there are both An and B in the string, the query statement is: SELECT * FROM table_name WHERE column_1 LIKE'% A% 'AND column_1 LIKE'% B%'

SELECT * FROM table_name WHERE column_1 LIKE'[Zhang Li Wang] San'; / it means that all matching Zhang San, Li San and Wang San in column_1 can be SELECT * FROM table_name WHERE column_1 LIKE'[Zhang Li Wang] San'; / / it means that all three matching in column_1 except Zhang San, Li San and Wang San can be done.

/ / in a fuzzy query,% represents any 0 or more characters; _ represents any single character (there is and only) and is usually used to limit the length of a string; [] represents one of the characters; [^] represents all characters except the characters in it.

Or fuzzy query SELECT * FROM table_name WHERE MATCH (content) AGAINST ('word1','word2',...) in the full-text index

Delete index

DROP INDEX my_index ON tablename; or ALTER TABLE table_name DROP INDEX index_name

View the indexes in the table

SHOW INDEX FROM tablename

View the use of indexes by query statements

/ / explain plus query statement explain SELECT * FROM table_name WHERE column_1='123'; II. Advantages and disadvantages of index

Advantages: it can be retrieved quickly, reducing the number of Imax O and speeding up the retrieval speed; grouping and sorting according to the index can speed up the grouping and sorting

Disadvantages: the index itself is also a table, so it takes up storage space, generally speaking, the space occupied by the index table is 1.5 times that of the data table; the maintenance and creation of the index table requires time cost, which increases as the amount of data increases; building an index reduces the efficiency of data table modification operations (delete, add, modify), because you need to modify the index table as well as the data table

III. Classification of indexes

Common index types are: primary key index, unique index, general index, full-text index, combined index.

1. Primary key index: that is, the primary index, which is indexed according to the primary key pk_clolum (length). Repetition and null values are not allowed.

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index (' col')

2. Unique index: the value of the column used to build the index must be unique, allowing null values

ALTER TABLE 'table_name' ADD UNIQUE index_name (' col')

3. Normal index: an index built with ordinary columns in a table without any restrictions

ALTER TABLE 'table_name' ADD INDEX index_name (' col')

4. Full-text indexing: an index built with columns of large text objects (explained in the next section)

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index (' col')

5. Composite index: an index built with a combination of multiple columns. Null values are not allowed in these columns.

ALTER TABLE 'table_name' ADD INDEX index_name (' col1','col2','col3')

* in accordance with the principle of "leftmost prefix", the columns most commonly used for retrieval or sorting are placed on the left, decreasing in turn. The combined index is equivalent to the establishment of the three indexes of col1,col1col2,col1col2col3, while col2 or col3 cannot use the index.

* when using a combined index, the key of the index may be too large because the length of the column name is too long, resulting in reduced efficiency. If allowed, only the first few characters of col1 and col2 can be taken as the index.

ALTER TABLE 'table_name' ADD INDEX index_name (col1 (4), col2 (3))

Indicates that the first four characters of col1 and the first three characters of col2 are used as indexes

Thank you for reading, the above is the content of "the syntax of the index in MySQL and the advantages and disadvantages of the index". After the study of this article, I believe you have a deeper understanding of the syntax of the index in MySQL and the advantages and disadvantages of the index, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.