Considerations for mysql indexes and considerations for mysql Indexes
Advantages of Indexes
- Greatly speed up Data Query
- When you use grouping and sorting to query data, you can significantly reduce the time for grouping and sorting during query.
- Create a unique index to ensure the uniqueness of each row of data in the database table
- It can accelerate the connection between tables in terms of data reference integrity.
Index disadvantages
- It takes time to create and maintain indexes. As the amount of data increases, the time will also increase.
- Index needs to occupy disk space
- Add, modify, and delete data in a data table, and maintain indexes dynamically, reducing the maintenance speed.
Principles for creating Indexes
- Indexes should not be set for frequently updated columns.
- Do not use indexes for tables with a small amount of data (after all, do you need a directory for a total of 2 pages of documents ?)
- Fields with multiple duplicate data cannot be indexed (for example, gender, only male and female. Generally, an index should not be created if the duplicate data exceeds 15%)
- First, you should consider creating an index on the columns involved in where and order.
Supplement: Check whether the index is used: explain select username from user where id = 1
Result:
Field description:
- Select_typeSimple indicates that there are other simple queries, such as primary, union, and subquery.
- TableTable Name
- PartitionsMatched Partition
- TypeThe engine finds the required rows in the table in the following ways: all (full table scan), index (only traversing the index tree), range (index range scan, common in between,>, <query in progress), ref (non-unique index scan), eq_ref (unique index scan), and const/system (when MySQL optimizes a certain part of the query, use these types of access when converting to a constant), null (MySQL breaks down statements during optimization, and even does not need to access tables or indexes during execution)
- Possible_keysAvailable Indexes
- KeyIndex Used
- Key_lenThe length of the number of index bytes. The smaller the value, the faster the running speed.
- RefConnection matching condition, that is, which columns or constants are used to find the value of the index Column
- RowsNumber of returned data rows
- FilteredPercentage of rows filtered by table Conditions
- ExtraAdditional information type: using index (overwrite index Used in the select Operation), using where (mysql Server performs "post filter" after the storage engine is recorded), using temporary (indicating that mysql needs to use a temporary table to store the result set, which is common in sorting and grouping queries) and using filesort (mysql cannot complete sorting operations by using indexes and becomes "File Sorting ")
Note: The key can be used to determine whether the index is executed.
Optimize mysql query statements
- Do not compute functions, operators, or expressions on the left of the where Condition Statement '=', such as select name from tb_user where age/2 = 20, because the index does not take effect (the engine will discard the index for full table scanning)
- Do not use <> ,! =, Not in, because the index does not take effect
- Avoid null judgment on the field, because the index does not take effect (a value can be used to replace null, such as-999)
- When like is used for Fuzzy queries, like '% xx %' causes the index to fail. like 'xx % 'indexes can be used, so avoid using the first
- To avoid using or, you can replace it with union (if you want to use or to make the index take effect, you must add an index for each column in the or condition)
- Use exist instead of in (the more data in the table, the more efficient exist is than in)
- The data type is implicitly converted, and the index does not take effect: for example, select name from user where phone = 13155667788; (the phone field is of the varchar type in the database and should be changed to phone = '123 ')
- The combined index takes effect only in order. For example, if the order of the created index is a, B, where a = "xx" and B = "xx, however, B = "xx" and a = "xx" will not take effect. Supplement: If a = "xx" is not followed, the index will also take effect.
- Avoid using cursors whenever possible (low cursor efficiency)
- Do not use select *
- There are many more...
Create an index on an existing table
Delete Index
