Mysql uses indexes to optimize order sorting, and mysqlorder

Mysql uses indexes to optimize order sorting, and mysqlorder

Original address: http://www.phpthinking.com/archives/433

In the database, we usually perform index operations on some fields, which can improve the data query speed and improve the database performance such as order by and group.

Please note that
1> only one index can be used for one mysql query at a time. If you want to use an index for multiple fields, create a composite index.
2> in the order by operation, MySQL uses indexes only when the sorting condition is not a query condition expression.
Index description

MySQL indexes are usually used to increase the search speed of data rows matching other tables when the WHERE condition is matched or when the join operation is performed.
MySQL can also use indexes to quickly sort and group order by and group by statements.
Optimize the order by statement of MySQL through index optimization:
1. order by index optimization. If an SQL statement is like:
SELECT [column1], [column2],... FROM [TABLE] order by [sort];
You can create an index on the [sort] column to optimize order by using the index.
2. Optimize the index of WHERE + order by, for example:
SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] = [value] order by [sort];
Create a joint index (columnX, sort) to optimize order.
NOTE: If columnX corresponds to multiple values, the following statement cannot optimize order by using indexes.
SELECT [column1], [column2],... FROM [TABLE] WHERE [columnX] IN ([value1], [value2],…) Order by [sort];
3. WHERE + multiple fields ORDER
SELECT * FROM [table] WHERE uid = 1 ORDER x, y LIMIT 0, 10;
Creating an index (uid, x, y) to optimize order by is much better than creating an index (x, y, uid.
MySQL Order By cannot use indexes to optimize sorting
* Perform order by on different index keys: (create indexes for key1 and key2 respectively)
SELECT * FROM t1 order by key1, key2;
* Order by: (key_part1 and key_part2 are used to create a joint index; key2 is used to create an index)
SELECT * FROM t1 WHERE key2 = constant order by key_part2;
* Both ASC and DESC :( key_part1 and key_part2 are used to create a joint index)
SELECT * FROM t1 order by key_part1 DESC, key_part2 ASC;
* The index key used for searching records and the order by statement are not the same: (key1 and key2 create indexes respectively)
SELECT * FROM t1 WHERE key2 = constant order by key1;
* If expressions (functions) are applied in the WHERE and order by columns, indexes cannot be used to optimize order.
SELECT * FROM t1 order by year (logindate) LIMIT 0, 10;
MySQL supports many data types. Selecting the appropriate data type to store data has a great impact on performance.

Generally, the following guiding principles can be observed:
(1) smaller data types are generally better: smaller data types usually require less space in the disk, memory, and CPU cache for faster processing.
(2) A simple data type is better: the processing overhead of integer data is smaller than that of characters, because the strings are more complex. In MySQL, the built-in Date and Time data types should be used instead of strings to store the time, and the IP addresses of integer data types should be used to store the time.
(3) Avoid NULL as much as possible: the column should be specified as not null unless you want to store NULL. In MySQL, it is difficult to query and optimize columns with null values, because they make the index and index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.

MySql query sorting

0 if a single table is not sorted, the primary key order is used by default. This is because the data block is stored in the disk in the primary key order; if there is no primary key, it is in the order of data blocks on the disk (this is also the order when data is inserted );
If multiple tables are not sorted, and there is no group by clause, the order is the primary key order of the Left table, and then the order of the Left table again, similar to the following:
Left table primary key 1 right table primary key 1
Left table primary key 2 right table primary key 1
Left table primary key 1 right table primary key 2
Left table primary key 2 right table primary key 2
When group by is executed, memory sorting or temporary File Sorting will be used internally (this can be seen from the explain ).
2. choose whether to use order by. However, if you do not use order by, the business requirements are generally not met.
Try to optimize order.

MySQL database optimization (7): How does MySQL use indexes?

The index is used to quickly find records with specific values. If no index exists, MySQL must read the entire table from the first row of records to retrieve records. The larger the table, the larger the resource consumption. If the field has an index, MySQL can quickly determine the location where the data file is located to search for records without searching all the data. If the table contains 1000 records, this is at least 100 times faster than reading data sequentially. Note: If you need to access almost all 1000 records, sequential reading will be faster, because this will minimize the number of disk searches.
Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored as B trees. Only Spatial fields are stored in the R tree. MEMORY (HEAP) tables support hash indexes.
By default, strings are automatically compressed with spaces in the prefix and suffix.
Generally, indexes can be used in the following situations. The unique feature of hash indexes (for MEMORY tables) will be discussed later.
Find the record that matches the WHERE clause as soon as possible.
Exclude records based on conditions. If multiple indexes are available, MySQL usually selects the index with the least records.
Query records from other tables during table join queries.
You want to find its MIN () or MAX () value on the specified index field key_col. The optimizer will check the index
Before the key_col field, check whether the WHERE key_part _ # = constant clause is used in other indexes. In this case,
MySQL performs an index search separately for MIN () or MAX () expressions and replaces them with constants. When all expressions are replaced with constants, the query returns immediately. As follows:
Select min (key_part2), MAX (key_part2) FROM tbl_name WHERE key_part1 = 10;
Sort or group a table. When grouping or sorting an available leftmost prefix index (such as ORDER
BY key_part1, key_part2 ). If all the indexes are sorted by DESC, the indexes are sorted in reverse order.
In some cases, the query can be optimized so that results can be directly obtained without computing data. When the query uses a numeric field in the table and the field is the leftmost part of the index, the results may be obtained quickly from the index tree:
SELECTkey_part3FROMtbl_nameWHEREkey_part1 = 1
Assume that the SELECT statement is as follows:
If there is a multi-field index on col1 and col2, the corresponding records can be obtained directly.