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

Editor to share with you the MySQL index creation principles of the example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

First, it is suitable for creating index 1. The numerical value of the field is limited to uniqueness.

According to the Alibaba specification, fields that have unique characteristics in the business, even combined fields, must be unique indexes.

For example, the student number in the student table has a unique field, to establish a unique index for this field can quickly query the information of a student, if the name is used, there may be the same name, thus slowing down the query speed.

2. Fields frequently used as Where query criteria

A field is often used in the Where condition of the Select statement, so it is necessary to create an index for this field, especially in the case of a large amount of data, creating a general index can greatly improve the efficiency of the query.

For example, the test table student_info has 1 million data. Suppose the user information of student_id=112322 is queried. If no index is created on the student_id field, the query result is as follows:

Select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322 bot # cost 211ms

After you create an index for student_id, the query results are as follows:

Alter table student_info add index idx_sid (student_id); select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322th # cost 3ms

3. Columns of regular Group by and Order by

Indexing is to let the data be stored or retrieved in a certain order, so when you use Group by to query the data or use Order by to sort the data, you need to index the grouped or sorted fields. If there are multiple columns to be sorted, you can build a composite index on those columns.

For example, students' draft courses are grouped according to student_id, showing different student_id and the number of courses, showing 100. If you do not create an index on student_id, the query results are as follows:

Select student_id,count (*) as num from student_info group by student_id limit 100th # cost 2.466s

After you create an index for student_id, the query results are as follows:

Alter table student_info add index idx_sid (student_id); select student_id,count (*) as num from student_info group by student_id limit 100th # cost 6ms

For query statements with both group by and order by, it is recommended to establish a federated index and put the fields in group by in front of the order by field to meet the "leftmost prefix matching principle". In this way, the utilization of the index will be high, and the efficiency of the natural query will be high. At the same time, the version after 8.0 supports descending indexes. if the fields after order by are in descending order, you can consider creating a descending index directly, which will also improve the query efficiency.

4. Where condition columns of Update and Delete

Query the data according to certain conditions and then perform the operation of Update or Delete. If you create an index on the Where field, you can reply to improve efficiency. The reason is that the record needs to be retrieved according to the Where condition column, and then updated or deleted. If the updated field is a non-indexed field, the efficiency improvement will be more obvious because the fee index field update does not need to be maintained.

For example, modify the student_id to 110119 for the data in the student_ info table where the name field is sdfasdfas123123. Without indexing the name field, the execution is as follows:

Update student_info set student_id = 110119 where name = 'sdfasdfas123123';# cost 549ms

After adding the index, the execution is as follows:

Alter table student_info add index idx_name (name); update student_info set student_id = 110119 where name = 'sdfasdfas123123';# cost 2ms

5. The index of Distinct field needs to be created.

Sometimes you need to de-duplicate a field and use Distinct, so creating an index on this field will also improve query efficiency.

For example, query the different student_id in the course schedule. If no index is created for the student_id, the execution is as follows:

Select distinct (student_id) from student_id;# costs 2ms

After the index is created, the execution is as follows:

Alter table student_info add index idx_sid (student_id); select distinct (student_id) from student_id;# costs 0.1ms6 and multi-table Join join operations, considerations for index creation

First of all, the amount of data of the join table should not exceed 3 as far as possible, because each additional table is equivalent to adding a nested loop, and the order of magnitude increases very fast, which seriously affects the query efficiency. Secondly, create an index on the Where condition, because Where is the filtering of the data condition, if the amount of data is very large, it is very scary when there is no Where condition filtering. Finally, create an index for the connected fields, and change the field and then the type in multiple tables must be consistent.

For example, only create an index on student_id, and the query result is as follows:

Select course_id, name, student_info.student_id,course_namefrom student_info join courseon student_info.course_id = course.course_idwhere name = 'aAAaAA'; # cost 176ms

After indexing the name field, the query result is as follows:

Alter table student_info add index idx_name (name); select course_id, name, student_info.student_id,course_namefrom student_info join courseon student_info.course_id = course.course_idwhere name = 'aAAaAA'; # cost 2ms

7. Create an index with a small type of column

The type small value here means the size of the data range represented by the type. For example, the type of specified column to be displayed when defining the table structure, take the integer type as an example, there are TINYINT, MEDIUMINT, INT, BIGINT and so on, their storage space is increased in turn, and the range of data that can be expressed is also increased at one time. If indexing is relative to an integer column, if the range of integers represented allows, try to use smaller types for index columns. For example, you can use INT instead of BIGINT and MEDIUMINT instead of INT for the following reasons:

The smaller the data type, the faster the comparison operation is performed during the query.

The smaller the data type, the less space the index takes up, and more records can be stored in one data page, thus reducing the performance loss caused by disk I and O, which means that more data can be stored in the data page. improve the efficiency of reading and writing.

The above is suitable for the primary key, because both the data and the index are stored in the clustered index, which can well reduce the disk Imax O, while for the secondary index, it takes one more return table operation to find the complete data, that is, you can add a disk Ibank O.

8. Create an index using a string prefix

According to the Alibaba development manual, when building an index on a string, you must specify the index length, and there is no need to index the whole field.

For example, if there is a product table with a long product description field, the prefix index on the description field is as follows:

Create table product (id int, desc varchar (120) not null); alter table product add index (desc (12))

The degree of differentiation can be calculated using count (distinct left (column name, index length)) / count (*).

9. Columns with high discrimination are suitable for use as indexes

When the cardinality of a column is worth, the number of non-duplicated data in a column, for example, a column contains a value of 2, 5, 5, 3, 3, 6, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, The cardinality index of the column here is very important and directly affects whether the index can be used effectively. It is best to index columns with large cardinality, but it is not good to index columns with too small cardinality.

The formula select count (distinct col) / count (*) from table can be used to calculate the degree of differentiation, and the closer it is to 1, the better.

10. The most frequently used columns are placed on the left side of the federated index

This is commonly known as the leftmost prefix matching principle. Generally speaking, the condition field that is often used after the Where condition is placed on the far left of the index, and the one that is used less frequently is placed on the right.

11. In the case of multiple fields to create an index, the federated index is not suitable for creating an index because of single-valued index 2, and do not set an index for fields that cannot be used in where

Usually, the establishment of the index has a price. If the indexed field does not appear in the where condition (including group by, order by), it is recommended not to create the index or delete the index in the first place, because the existence of the index will also take up space.

2. It is best not to use indexes on tables with small amounts of data. 3. Do not build indexes on columns with a lot of duplicate data.

Index on columns with more different values that are often used in conditional expressions, but there is no need to create an index if there is a large amount of duplicate data in the field. For example, the gender field in the student table has only male and female values, so there is no need to establish an index. If the index is established, it will not improve the query efficiency, but will seriously reduce the speed of data update.

4. Avoid creating too many indexes on frequently updated tables

It is not necessary to create an index for fields that are updated frequently, because when updating data, the index is also updated. If there are too many indexes, it will cause pressure on the server and affect the efficiency.

Avoid creating too many indexes on frequently updated tables and have as few columns in the index as possible. Although it improves the query speed, it also slows down the speed of updating the table.

5. Unordered values are not recommended as indexes

For example, ID card, UUID (need to be converted to ASCII when index comparison, and may cause page splitting when inserting), MD5, HASH, unordered long strings, and so on.

6. Delete indexes that are not in use or are rarely used

After the data in the table is heavily updated or the way the data is used is changed, some of the original indexes may not be used. DBA should periodically find these indexes and delete them, thereby reducing the impact of useless indexes on update operations.

7. Do not define redundant or duplicate indexes

For example, ID card, UUID (need to be converted to ASCII when index comparison, and may cause page splitting when inserting), MD5, HASH, unordered long strings, and so on.

8. Delete indexes that are not in use or are rarely used

After the data in the table is heavily updated or the way the data is used is changed, some of the original indexes may not be used. DBA should periodically find these indexes and delete them, thereby reducing the impact of useless indexes on update operations.

9. Do not define redundant or duplicate indexes. these are all the contents of this article entitled "sample Analysis of MySQL Index creation principles". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.