Mysql-index optimization strategy

Later in this section, practical and easy-to-understand examples will be used to illustrate the actual triggering of the index, so as to optimize the strategy used when adding indexes.

1. Index type

1.1 B-tree index

Note: It is called btree index. From a broad perspective, balanced trees are used, but in terms of specific implementation ,Each engine is slightly different,

For example, strictly speaking, the NDB engine uses T-tree, Myisam, and innodb uses B-tree index by default

But abstractly- --B-tree system can be understood as "sorted fast search structure". For more analysis, see Mysql-Index-BTree Type [Simplified]

1.2 Hash Index

In the memory table, the default is hash index. The theoretical query time complexity of hash is O(1)

Question: Since hash search is so efficient, why not use hash index?

Answer:

1: The result calculated by the hash function is random. If the data is placed on the disk, for example, the primary key is id, then as the id grows, The rows corresponding to the id are randomly placed on the disk.

2: Unable to optimize range queries.

3: Unable to use prefix index. For example, in btree, The value of the field column "hellopworld", and add index, query xx=helloword, you can naturally use the index, xx=hello, you can also use the index. (left prefix index). Because the relationship between hash('helloword') and hash('hello') is still random. In fact, it is because HASH is accurate.

4: Sorting cannot be optimized.

5: Row backing is required. That is to say, to get the data location through the index, you must go back to the table to get the data

2. Common misunderstandings of btree indexes

2.1 Where Indexes are added to columns commonly used in conditions

Example: where cat_id=3 and price>100; //Query the third column, products over 100 yuan

Error: on cat_id, and, Indexes are added to price.

Error: Only cat_id or Price index can be used, because it is an independent index structure, and only one can be used at the same time. For detailed arrangement description, please see Mysql-Intuitive Illustration of Index Structure

2.2 For the index to work on a multi-column index, it needs to meet the left prefix requirement.

Error : After creating an index on multiple columns, the index will work no matter which column is queried

Take index(a,b,c) as an example,

3. Summary:

1. The order of the joint index follows the left prefix principle and must be consistent layer by layer. The where condition in the SQL statement has no context, such as the query examples 4 and 5 above

2 . Involving range queries like , the index after this query cannot be used like 7

3. Under the premise of nesting one layer at a time, order sorting is used, and order sorting does not include the where condition. , pay attention to the problem of [where field a order by field a]. In fact, [order a] is a false proposition, because it is already equal to a, which sub-order should be arranged?

4. In the where in the select query, there are no multiple conditions The order, but you must pay attention to the order when adding the index, the same.

5. The working principle of group in grouping: first order_by sort, create a temporary table, if you build an index, you can save the need to create a temporary table, so the index is valid for the group

The above is the content of Mysql-index optimization strategy. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!