Techniques and Points for Attention in Using mysql Index

1. The role of indexes

1-like application system, read-write ratio is about 10: 1, and insertion operation and 1-like update operation rarely have performance problems, and the most encountered and most prone to problems are 1 complex query operation, so the optimization of query statement is obviously the most important thing.

In the case of small data volume and access volume, mysql access is very fast, and whether to add indexes has little effect on access. However, when the amount of data and visits increases dramatically, it will be found that mysql slows down or even down drops, so it is necessary to consider optimizing sql. Establishing a correct and reasonable index for the database is an important means to optimize mysql.

The purpose of the index is to improve the query efficiency, which can be compared with a dictionary. If we want to look up the word "mysql", we definitely need to locate the letter m, then find the letter y from bottom to bottom, and then find the remaining sql. If there is no index, you may need to read all the words once to find what you want. Besides dictionaries, examples of indexes can be seen everywhere in life, such as train schedules at railway stations, catalogues of books and so on. Their principles are all the same. We filter out the final desired results by constantly narrowing the scope of the data we want to obtain, and at the same time turn random events into sequential events, that is, we always lock the data through the same search method.

When you create an index, you need to consider which columns will be used in an SQL query, and then create one or more indexes for those columns. In fact, an index is also a table that holds a primary key or index field, and a pointer that points each record to the actual table. Database users can't see indexes, they are only used to speed up queries. Database search engines use indexes to quickly locate records.

INSERT and UPDATE statements take more time to execute in indexed tables, while SELECT statements execute faster. This is because the database also needs to insert or update index values when inserting or updating.

2. Create and delete indexes

Type of index:

UNIQUE (only 1 index): You can't have the same value, you can have NULL value INDEX (Common Index): Allow the same index content PROMARY KEY (primary key index): The same value is not allowed fulltext index (full-text indexing): You can target a word in the value, but the efficiency is really not flattering Combined index: In essence, multiple fields are built into one index, and the combination of column values must be only 1

(1) Use the ALTER TABLE statement to create a simple

Apply to add after the table is created.

ALTER TABLE  Table name  ADD  Index type   ( unique,primary key,fulltext,index ) [ Index name ] (Field name) 

// General index 
alter table table_name add index index_name (column_list) ;
// Only 1 Index 
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 three index formats: ordinary index, UNIQUE index and PRIMARY KEY index. table_name is the name of the table to be indexed, and column_list indicates which columns are indexed. When there are multiple columns, each column is separated by commas. The index name index_name is optional and, by default, MySQL is assigned a name based on the first index column. In addition, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.

(2) Indexing tables using the CREATE INDEX statement

CREATE INDEX can be used to add a normal index to a table or an UNIQUE index, which can be used to create an index when building a table.

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

If it is CHAR, VARCHAR type, length can be less than the actual length of the field; length must be specified for BLOB and TEXT types.

//create You can only add these two 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 in the ALTER TABLE statement, and the index name is not optional. In addition, you cannot create an PRIMARY KEY index with the CREATE INDEX statement.

(3) Delete index

The index can be dropped using the ALTER TABLE or DROP INDEX statements. DROP INDEX can be processed as a statement inside ALTER TABLE in the following format:

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

In the previous two statements, the index index_name in table_name is dropped. In the last statement, it is only used in dropping the PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so there is no need to specify an index name. If an PRIMARY KEY index is not created, but the table has one or more UNIQUE indexes, MySQL drops the first UNIQUE index.

If you delete a column from a table, the index is affected. For an index with multiple columns, if you delete a column, 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) Combination index and prefix index

It should be pointed out here that composite index and prefix index are a term for indexing skills, not the type of index. For better expression, establish an 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)
);

In order to extract the efficiency of mysql in one step, we can consider establishing a combined index, that is, building LOGIN_NAME, CITY and AGE into one index:

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE); 

When building a table, the length of LOGIN_NAME is 100, and 16 is used here, because the length of names will not exceed 16 under normal circumstances, which will speed up index query, reduce the size of index files and improve the update speed of INSERT and UPDATE.

If LOGIN_NAME, CITY and AGE are given single-column indexes respectively, and the table has three single-column indexes, the efficiency of query and combined index is quite different, 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 to say, it is still a full table scanning process.

Establishing such a combined index is equivalent to establishing the following three combined indexes:

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

Why are there no composite indexes such as CITY, AGE, etc.? This is because mysql combines the index "leftmost prefix" as a result. The simple understanding is that only the leftmost index is combined, and not all queries containing these three columns will use this combined index. That is, name_city_age (LOGIN_NAME (16), CITY, AGE) is indexed from left to right, and mysql does not perform index queries without a left front index.

If the index column length is too long, this kind of column index will produce a large index file, which is inconvenient to operate. Prefix index can be used for index. Prefix index should be controlled at a suitable point, and it can be controlled at a golden value of 0.31 (if it is greater than this value, it can be created).

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; --  This value is greater than 0.31 You can create a prefix index ,Distinct De-repetition 

ALTER TABLE `user` ADD INDEX `uname`(title(10)); --  Add prefix index SQL, Index the names of people in the 10, This reduces the size of the index file , Speed up index query 

3. Use of indexes and precautions

EXPLAIN can help developers analyze SQL problems, and explain shows how mysql uses indexes to process select statements and join tables, which can help choose better indexes and write more optimized query statements.

To use the method, just add Explain before the select statement:

Explain select * from user where id=1;

Try to avoid these non-indexed sql:

SELECT `sname` FROM `stu` WHERE `age`+10=30;--  Indexes will not be used , Because all index columns participate in the calculation 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; --  Indexes will not be used , Because the function operation is used , The principle is the same as above 

SELECT * FROM `houdunwang` WHERE `uname` LIKE' Backing %' --  Indexing 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "% Backing %" --  Do not take the index 

--  Regular expressions do not use indexes , This should be easy to understand , So why is it that SQL It's hard to see in regexp The reason for the keyword 

--  Strings are compared with numbers without indexes ;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" --  Indexing 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 --  Do not take the index 

select * from dept where dname='xxx' or loc='xx' or deptno=45 -- If there is one in the condition or, Even if there is a conditional band index, it will not be used. In other words , Is all the fields required to use , You must build an index ,  We suggest that you avoid using it as much as possible or  Keyword 

If mysql estimates that full table scanning is faster than using indexes, no indexes are used

Although indexes have many advantages, excessive use of indexes may bring the opposite problems, and indexes also have disadvantages:

While indexes greatly speed up queries, they slow down table updates such as INSERT, UPDATE, and DELETE on tables. Because when updating the table, mysql not only saves the data, but also saves the index file under 1 Index files that take up disk space when indexing. In general, this problem is not serious, but if you want to build multiple composite indexes on a large table, the index file will swell very wide

Indexing is only one way to improve efficiency. If mysql has a large amount of data, it will take time to study and establish the optimal index or optimize the query statement.

When using indexes, there are one trick:

1. The index will not contain columns with NULL

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

Step 2 Use short indexes

Indexing a serial column, you should specify a prefix length if you can. For example, if you have a column of char (255), do not index the entire column if multiple values are 1-only within the first 10 or 20 characters. Short indexes not only improve query speed but also save disk space and I/O operations.

3. Index column sorting

The mysql query uses only one index, so columns in order by do not use an index if an index is already used in the where clause. Therefore, if the default sorting of the database can meet the requirements, do not use sorting operation, try not to include sorting of multiple columns, and if necessary, it is best to build composite indexes for these columns.

4. like statement operation

1 Under normal circumstances, the use of like operation is not encouraged. If it is necessary to use it, pay attention to the correct use mode. like '% aaa%' does not use indexes, while like 'aaa%' can use indexes.

5. Do not operate on columns

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

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

This is because, if these columns are rarely used, the presence or absence of an index does not significantly change the query speed. On the contrary, due to the increase of index, the maintenance speed of the system is reduced and the space requirement is increased.

8. The index should be built on the field whose value comparison is only 1.

9. Indexes should not be added to columns defined as text, image, and bit data types. Because the data volume of these columns is either quite large or has few values.

10. Columns that appear in where and join need to be indexed.

11. There is an unequal sign in the query condition of where (where column! =...), mysql will not be able to use the index.

12. If a function is used in the query condition of the where sentence (e.g. where DAY (column) = …), mysql will not be able to use the index.

13. In join operations (when data needs to be extracted from multiple data tables), mysql can only use indexes if the data types of primary keys and foreign keys are the same, otherwise it will not be used if the index is established in time.