Related operations of adding and deleting indexes in mysql

1. The role of index

In general application systems, the read-write ratio is about 10:1, and insert operations and general update operations rarely have performance issues. The most common and most problematic operations are some complex query operations, so the optimization of query statements is obviously a top priority.

When the amount of data and the number of accesses are not large, MySQL access is very fast, and whether or not to add an index has little impact on access. However, when the amount of data and the number of visits increase dramatically, you will find that MySQL becomes slower or even crashes. In this case, you must consider optimizing SQL. Establishing correct and reasonable indexes for the database is an important means of optimizing MySQL.

The purpose of the index is to improve query efficiency. It can be compared to a dictionary. If we want to look up the word "mysql", we must locate the letter m, then find the letter y from the bottom to the bottom, and then find the rest of sql. If there is no index, you may need to look through all the words to find what you want. In addition to dictionaries, examples of indexes can be found everywhere in life, such as train schedules at train stations, book catalogs, etc. Their principles are the same, which is to filter out the final desired results by continuously narrowing the scope of the data you 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 creating an index, you need to consider which columns will be used in SQL queries and then create one or more indexes for those columns. In fact, an index is also a table that stores the primary key or index field and a pointer that points each record to the actual table. Indexes are invisible to database users; they are only used to speed up queries. Database search engines use indexes to locate records quickly.

INSERT and UPDATE statements take longer to execute on a table with an index, while SELECT statements execute faster. This is because, when an insert or update is performed, the database also needs to insert or update the index value.

2. Creating and deleting indexes

Types of indexes:

• UNIQUE (unique index): The same value cannot appear, and NULL values ​​are allowed
• INDEX (normal index): allows the same index content to appear
• PROMARY KEY (primary key index): Duplicate values ​​are not allowed
• Fulltext index: can be used to target a word in a value, but the efficiency is not satisfactory.
• Composite index: In essence, multiple fields are built into one index, and the combination of column values ​​must be unique

(1) Use the ALTER TABLE statement to create a

Used to add after the table is created.

ALTER TABLE table name ADD index type (unique, primary key, fulltext, index) [index name] (field name)
//Ordinary index alter table table_name add index index_name (column_list);
//Unique 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: normal index, UNIQUE index and PRIMARY KEY index. table_name is the name of the table to which the index is to be added, column_list indicates which columns to index, and if there are multiple columns, the columns are separated by commas. The index name index_name is optional. By default, MySQL will assign a name based on the first index column. Additionally, ALTER TABLE allows multiple tables to be changed in a single statement, so multiple indexes can be created at the same time.

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

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

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

If it is CHAR or VARCHAR type, length can be smaller than the actual length of the field; if it is BLOB or TEXT type, length must be specified.

//create 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 meanings as in the ALTER TABLE statement. The index name is not optional. In addition, you cannot create a PRIMARY KEY index using the CREATE INDEX statement.

(3) Delete index

Deleting an index can be done using the ALTER TABLE or DROP INDEX statements. DROP INDEX can be processed as a single statement inside ALTER TABLE, with 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 deleted. In the last statement, it is only used to delete the PRIMARY KEY index. Because a table can only have one PRIMARY KEY index, there is no need to specify the index name. If no PRIMARY KEY index is created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index.

If you remove a column from a table, the index will be affected. For an index with multiple columns, if you delete one of the columns, the column will also be deleted from the index. If you drop all the columns that make up an index, the entire index will be dropped.

(4) Composite index and prefix index

It should be pointed out here that composite index and prefix index are names for indexing techniques, not types of indexes. In order to better explain it, a demo table is created 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 further squeeze the efficiency of MySQL, you can consider creating a composite 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 creating a table, the length of LOGIN_NAME is 100. 16 is used here because the length of a name generally does not exceed 16. This will speed up index queries, reduce the size of the index file, and increase the update speed of INSERT and UPDATE.

If we create single-column indexes for LOGIN_NAME, CITY, and AGE respectively, so that the table has three single-column indexes, the efficiency of querying will be very different from that of the combined index, and may even be far lower than our combined index. Although there are three indexes at this time, MySQL can only use the single-column index that it thinks seems to be the most efficient. The other two are not used, which means that it is still a full table scan process.

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

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

Why is there no combined index like CITY, AGE, etc? This is because MySQL composite index "leftmost prefix" result. The simple understanding is that the combination starts from the leftmost column, and not all queries containing these three columns will use this combined index. That is to say, name_city_age(LOGIN_NAME(16),CITY,AGE) is indexed from left to right. If there is no left front index, MySQL will not perform index query.

If the index column is too long, a large index file will be generated when indexing this column, which is inconvenient to operate. You can use prefix indexing to index. The prefix index should be controlled at an appropriate point, which can be controlled within the golden value of 0.31 (it can be created if it is greater than this value).

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- If this value is greater than 0.31, you can create a prefix index and use Distinct to remove duplicates. ALTER TABLE `user` ADD INDEX `uname`(title(10)); -- Add a prefix index SQL and create an index of name at 10, which can reduce the size of the index file and speed up index queries.

3. Use and precautions of index

EXPLAIN can help developers analyze SQL problems. 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 it, just add Explain before the select statement:

Explain select * from user where id=1;

Try to avoid these SQL statements that do not use indexes:

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- The index will not be used because all index columns are involved in the calculation.SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- The index will not be used because function operation is used, and the principle is the same as above.SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- Go with the index.SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- Do not go with the index.-- Regular expressions do not use indexes, which should be easy to understand, so why it is difficult to see the regexp keyword in SQL.-- String comparison with numbers does not use indexes;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- Use index EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- Do not use index select * from dept where dname='xxx' or loc='xx' or deptno=45 --If there is an or in the condition, it will not be used even if there is an index in the condition. In other words, all fields that are used must be indexed. We recommend that you avoid using the or keyword if possible. If MySQL estimates that a full table scan is faster than an index, then the index is not used.

Although indexes have many benefits, excessive use of indexes may bring about the opposite problem. Indexes also have disadvantages:

• Although indexes greatly increase query speed, they also reduce the speed of updating tables, such as INSERT, UPDATE, and DELETE. Because when updating a table, MySQL not only saves the data, but also saves the index file.
• Building an index takes up disk space for index files. Generally, this problem is not serious, but if you create multiple combined indexes on a large table, the index file will become very wide.

Indexing is just one way to improve efficiency. If MySQL has a table with large data volumes, you need to spend time researching how to create the best index or optimize query statements.

There are a few tips when using indexes:

1. The index will not contain columns with NULL

As long as the column contains NULL values, it will not be included in the index. As long as one column in the composite index contains NULL values, this column will be invalid for this composite index.

2. Use short indexes

When indexing a list column, a prefix length should be specified if possible. For example, if you have a char(255) column, if most values ​​are unique within the first 10 or 20 characters, then don't index the entire column. Short indexes can not only improve query speed but also save disk space and I/O operations.

3. Sorting index columns

MySQL queries use only one index, so if the index is already used in the where clause, the columns in the order by clause will not use the index. Therefore, if the default sorting of the database can meet the requirements, do not use the sorting operation. Try not to include sorting of multiple columns. If necessary, it is best to create a composite index for these columns.

4.Like statement operation

Generally speaking, the use of the like operation is not encouraged. If you must use it, pay attention to the correct way to use it. like '%aaa%' will not use the index, but like 'aaa%' can use the index.

5. Don’t perform operations on columns

6. Do not use NOT IN, <>, ! = operation, but <, <=, =, >, >=, BETWEEN, and IN can use indexes.

7. Indexes should be created on fields that are frequently selected.

This is because if these columns are rarely used, then having or not having an index will not significantly change the query speed. On the contrary, the addition of indexes reduces the system maintenance speed and increases the space requirements.

8. The index should be created on fields with relatively unique values.

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

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

11. If there is an inequality sign in the where query condition (where column != ...), MySQL will not be able to use the index.

12. If a function is used in the query condition of the where clause (such as where DAY(column)=…), MySQL will not be able to use the index.

13. In the join operation (when data needs to be extracted from multiple tables), MySQL can only use the index when the data type of the primary key and the foreign key is the same, otherwise the index will not be used even if it is established.

The following are the additions from other netizens

As shown below:

ALTER TABLE xxxxx ADD INDEX `tid` (`tid`) USING BTREE;
DROP INDEX uid ON xxxx;
show index from xxxx

The above operations on adding and deleting indexes in MySQL are all what I want to share with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.