This is the fifth day of my participation in Gwen Challenge

preface

If you want to query data efficiently and quickly in MySQL database, the correct creation and use of indexes is the key, this article will introduce how to give full play to the advantages of these indexes.

Separate columns

If the columns in the query are not independent, MySQL does not use indexes. “Independent column” means that the index column cannot be part of an expression or arguments to a function.

Indexed columns cannot be part of an expression

For example, if we want to retrieve a student whose score is less than 10 points in the grade table, we might write the following SQL

SELECT	* FROM	`chinese_score` WHERE	120- total_score < 10
Copy the code

Then, the index will not be hit because the index column appears as part of the expression

If you want to use index SQL should deduct the score after the total score in the program calculated after the query

SELECT	* FROM	`chinese_score` WHERE total_score >110
Copy the code

Index columns cannot be arguments to a function

For example, we want to query the data of delivery time and receipt time greater than 10 days

SELECT.WHERE TO_DAYS(Receive_Date) - TO_DAYS(Send_Date) > 10;
Copy the code

The index column is the parameter of the function, and this SQL does not use the index.

Prefix index and index selectivity

Index a long column index can begin to part of the characters, so can greatly save the index space, and enhance efficiency index, of course also reduced the selectivity of index, which is not repeat index and data table (the number of T) the ratio of total number of records, range from 1 to 1 / T, selective, the higher the efficiency of the query, The selectivity of the unique index is 1. For BLOB and TEXT or long VARCHAR type columns, prefix indexes must be used. Another way to calculate the appropriate prefix length is to calculate the full column selectivity and make the prefix selectivity close to the full column selectivity. Calculates the selectivity of the full column

SELECT COUNT(DISTINCT name)/COUNT(*) FROM'db_team'. 'Order item statistics';Copy the code

Computes the selectivity of different prefix lengths

SELECT COUNT(DISTINCT LEFT(name,3))/COUNT(*) as sel3,
      COUNT(DISTINCT LEFT(name,4)/COUNT(*) as sel4,
      COUNT(DISTINCT LEFT(name, 5))/COUNT(*) as se15,
      COUNT(DISTINCT LEFT(name, 6))/COUNT(*) as sel6,
      COUNT(DISTINCT LEFT(name, 7))/CoUNT(*) as sel7
      FROM'db_team'. 'Order item statistics';Copy the code

Note that prefix indexes need to be created in cases where the data is not evenly distributed to prevent poor selectivity when searching for the data. Use statements to create prefix indexes

ALTER TABLE'db_team'. 'Order item statistics'ADDINDEX 'idx_name' (' commodity name '(5));
Copy the code

Create it directly from Navicat

Prefix indexes are an effective way to make indexes smaller and faster, but on the other hand, they have their drawbacks :MySQL cannot use prefix indexes for ORDER BY and GROUP BY, and cannot use prefix indexes for overwrite scans.

conclusion

Today we have covered how MySQL can create high performance indexes. Tomorrow we will continue to cover other methods of creating high performance indexes. See you in the next article!