Tips and notes for using mysql indexes, and tips for using mysql Indexes

Tips and notes for using mysql indexes, and tips for using mysql Indexes

I. Functions of Indexes

In general application systems, the read/write ratio is around, and insert operations and general update operations rarely encounter performance problems. The most common problems are also the most common problems. They are also complicated query operations, therefore, the optimization of query statements is obviously the top priority.

When the data volume and access volume are small, mysql access is very fast, and whether to add an index has little impact on access. However, when the data volume and traffic volume increase dramatically, mysql may slow down or even drop down. Therefore, you must optimize the SQL statements and create correct and reasonable indexes for the database, is an important means of mysql optimization.

The purpose of indexing is to improve the query efficiency. It can be analogous to a dictionary. If you want to query the word "mysql", you must locate the m letter and find the y letter from the bottom down, find the remaining SQL. If there is no index, you may need to read all the words to find what you want. In addition to dictionaries, examples of indexes that can be seen everywhere in daily life, such as train stations, trains, and directories of books. They work in the same way. By Constantly narrowing down the scope of data to be obtained, we can filter the final results and turn random events into ordered events, that is, we always lock data through the same search method.

When creating an index, you need to consider which columns will be used for SQL queries and then create one or more indexes for these columns. In fact, indexes are also a type of table that stores the primary key or index fields and a pointer that can direct each record to the actual table. Database users cannot see indexes. They are only used to accelerate queries. The database search engine uses indexes to quickly locate records.

The INSERT and UPDATE statements take more time to execute in an indexed table, but the SELECT statement runs faster. This is because the database also needs to insert or update the index value when inserting or updating data.

2. Create and delete Indexes

Index type:

1. UNIQUE (UNIQUE index): the same value cannot appear, and the value can be NULL.
2. INDEX: allows the same INDEX content to appear.
3. Promary key (primary KEY index): the same value cannot appear
4. Fulltext index
5. Composite Index: in essence, multiple fields are created into one index, and the combination of column values must be unique.

(1) Use the alter table statement to create a cable

After the table is created, add the table.

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

// Alter table table_name add index index_name (column_list); // alter table table_name add unique (column_list ); // primary key index alter table table_name add primary key (column_list );

Alter table can be used to create common indexes, UNIQUE indexes, and primary key indexes. table_name is the name of the TABLE to add indexes. column_list indicates which columns are indexed, multiple columns are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index column. In addition, alter table allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

(2) Use the create index statement to add an INDEX to a table

Create index can be used to add a common INDEX or unique index to a table. It can be used to CREATE an INDEX when creating a table.

CREATE INDEX index_name ON table_name(username(length)); 

For CHAR and VARCHAR types, the length can be smaller than the actual length of the field. For BLOB and TEXT types, the length must be specified.

// Create can only add these two types of indexes. create index index_name ON table_name (column_list) create unique index index_name ON table_name (column_list)

Table_name, index_name, and column_list have the same meaning as the alter table statement, and the index name is not optional. In addition, you cannot use the create index statement to CREATE a primary key index.

(3) Deleting an index

You can use the alter table or drop index statement to delete an INDEX. Drop index can be processed as a statement in alter table. The format is as follows:

drop index index_name on table_name ;alter table table_name drop index index_name ;alter table table_name drop primary key ;

In the preceding two statements, the index index_name in table_name is deleted. In the last statement, the primary key index is used only to delete the primary key index. Because a table may only have one primary key index, you do not need to specify the index name. If the primary key index is not created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index.

If you delete a column from the table, the index will be affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

(4) combined index and prefix Index

It should be pointed out that composite indexes and prefix indexes are a kind of name for indexing techniques, and are not the index type. For better presentation, create a demo table as follows.

Create table USER_DEMO (ID int not null auto_increment comment 'Primary key', LOGIN_NAME varchar (100) not null comment 'login name', PASSWORD varchar (100) not null comment 'Password ', CITY varchar (30) not null comment 'city', AGE int not null comment 'age', SEX int not null comment 'gender (0: Female 1: Male )', primary key (ID ));

To further extract mysql efficiency, you can consider creating a composite index, that is, to add LOGIN_NAME, CITY, and AGE to an index:

Copy codeThe Code is as follows:
Alter table USER_DEMO add index name_city_age (LOGIN_NAME (16), CITY, AGE );

When creating a table, the length of LOGIN_NAME is 100. Here we use 16 because the name length generally does not exceed 16, which will speed up the index query and reduce the size of the index file, increases the UPDATE speed of INSERT and UPDATE.

If you create a single-column index for LOGIN_NAME, CITY, and AGE respectively, so that the table has three single-column indexes, the query efficiency is significantly different from that of the combined index, or even far lower than our combined index. Although there are three indexes at this time, mysql can only use one of them, which seems to be the most efficient single-column index, and the other two are not used, that is, a full table scan process.

The establishment of such a composite index is equivalent to the establishment of the following three composite indexes:

LOGIN_NAME,CITY,AGELOGIN_NAME,CITYLOGIN_NAME

Why are there no combined indexes such as CITY and AGE? This is because mysql Composite Index "leftmost prefix" results. A simple understanding is that the composite index is used only from the leftmost combination, not all queries containing the three columns. That is to say, name_city_age (LOGIN_NAME (16), CITY, AGE) performs an index from left to right. If there is no left index, mysql does not perform an index query.

If the index column length is too long, this type of column index will generate a large index file, which is not easy to operate. You can use the prefix index method for indexing, prefix indexes should be controlled at an appropriate point and at a value of 0.31 gold (larger than this value can be created ).

Select count (DISTINCT (LEFT ('title', 10)/COUNT (*) FROM Arctic; -- a prefix index can be created if the value is greater than 0.31, distinct repeats alter table 'user' add index 'uname' (title (10); -- adds the prefix index SQL, and builds the INDEX of the person's name on 10, this reduces the size of the index file and speeds up index query.

Iii. Index usage and precautions

EXPLAIN helps developers analyze SQL problems. It shows how mysql uses indexes to process select statements and connect tables. It can help select better indexes and write more optimized query statements.

You can add the Explain statement before the select statement:

Explain select * from user where id=1;

Avoid these SQL statements that do not take indexes as much as possible:

SELECT 'sname' FROM 'std' WHERE 'age' + 10 = 30; -- no index is used, because all index columns are involved in the calculation of SELECT 'sname' FROM 'stu' where left ('date', 4) <1990; -- indexes are not used because function operations are used, the principle is the same as that above: SELECT * FROM 'houdunwang 'WHERE 'uname' LIKE' backing % '-- SELECT * FROM 'houdunwang' WHERE 'uname' LIKE "% backing %" -- not going index -- Regular Expressions Do not use indexes, this should be well understood, so it is difficult to see the reason for the regexp keyword in SQL-strings and numbers do not use indexes; create table 'A' ('A' char (10); explain select * FROM 'A' WHERE 'a' = "1" -- Index explain select * FROM 'A' WHERE 'a' = 1 -- select * from dept where dname = 'xxx' or loc = 'xx' or deptno = 45 -- if the condition contains or, it is not used even if there is a condition with an index. In other words, all fields required must be indexed. We recommend that you avoid using the or keyword whenever possible.

-- If mysql estimates that full table scan is faster than indexing, no index is used.

Although the index has many advantages, the excessive use of the index may lead to the opposite problem, and the index also has disadvantages:

1. Although the index greatly improves the query speed, it also reduces the table UPDATE speed, such as INSERT, UPDATE, and DELETE operations on the table. When updating a table, mysql not only needs to save data, but also stores the index file.
2. Index files that occupy disk space. This problem is not serious in general, but if you want to create multiple composite indexes for large tables, the index file will expand very wide.

Index is only a way to improve efficiency. If mysql has a large data volume of tables, it takes time to study the optimal index or Optimize Query statements.

Tips for using indexes:

1. The index does not contain columns with NULL values.

As long as a column contains a NULL value, it will not be included in the index. If a column in the composite index contains a NULL value, this column is invalid for this index.

2. Use short Indexes

Index a string or column. If you can, specify a prefix length. For example, if a char (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

3. Index column sorting

Mysql queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, when the database sorting by default can meet the requirements, do not use sorting operations, try not to include the sorting of multiple columns, if you need to create a composite index for these columns.

4. like statement operation

Generally, like operations are not encouraged. If not, pay attention to the correct method of use. Like '% aaa %' does not use indexes, but like 'aaa % 'can use indexes.

5. Do not perform operations on columns

6. Do NOT use not in, <> ,! = Operation, but <, <=, =,>,> =, BETWEEN, IN can use the index.

7. Indexes should be created on fields that frequently perform select operations.

This is because, if these columns are rarely used, the query speed cannot be significantly changed with or without indexes. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

8. The index should be created on a field with unique values.

9. Indexes should not be added for columns defined as text, image, or bit data types. Because the data volume of these columns is either large or the value is small.

10. You must create an index for the columns that appear in the where and join operations.

11. The where query condition contains a non-equal sign (where column! = ...), Mysql cannot use indexes.

12. If the where clause uses a function (for example, where DAY (column) =…) in the query condition ...), Mysql cannot use indexes.

13. in the join Operation (when data needs to be extracted from multiple data tables), mysql can use indexes only when the data types of primary keys and Foreign keys are the same. Otherwise, indexes created in time will not be used.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.