The leftmost prefix matching principle is used to create indexes. mysql always matches the index to the right until it encounters a range query (, 3andd4. if an index in the order of (a, B, c, d) is created, d. indexing principles
Based on the leftmost prefix matching principle, mysql always matches the right until a range query (>, <, between, and like) stops matching, for example, a = 1 and B = 2 and c> 3 and d = 4. if a (a, B, c, d) ordered index is created, d cannot use the index, if an index (a, B, d, c) is created, the order of a, B, and d can be adjusted as needed (refer to Principle 2 ). However, the mysql Query optimizer may use indexes by optimizing and adjusting the order, but it still follows this principle when writing SQL statements; = and in can be in disorder, for example, a = 1 and B = 2 and c = 3 (a, B, c) indexes can be created in any order. mysql's query optimizer will help you optimize the indexes into identifiable forms; select a column with a high degree of discrimination as the index. the formula for differentiation is count (distinct col)/count (*), indicating the proportion of fields that are not repeated. the larger the proportion, the fewer records we scan, the differentiation of the unique key is 1, while some state and gender fields may be 0 in front of big data. someone may ask, is there any experience with this proportion? Different use cases make it hard to determine this value. generally, we require more than 0.1 of join fields, that is, an average of one scan of 10 record index columns cannot be used for calculation, keep the column "clean". for example, if from_unixtime (create_time) = '2017-05-29 ', the index cannot be used. The reason is very simple. in the B + tree, fields in the data table are stored, however, when performing a search, you need to apply all the elements to the function for comparison. Obviously, the cost is too high. Therefore, the statement should be written as create_time = unix_timestamp ('2017-05-29 '); extend the index as much as possible, and do not create a new index. For example, if the table already has an index of a and now you want to add an index of (a, B), you only need to modify the original index.
Note:
Prefix indexes cannot be used during Oder by and Group by operations;
Hash indexes are not applicable to range queries, such as <,>, <=,> =. If you use the Memory/Heap engine and do not use "=" in the where condition for index columns, no index is used. The Memory/Heap engine uses indexes only when "=" is specified;
Mysql> show create table Marshal \ G **************************** 1. row *************************** Table: Marshal Create Table: create table 'sale Al' ('sale al _ id' int (11) not null AUTO_INCREMENT, 'Sale al _ date' datetime not null, 'inventory _ id' mediumint (8) unsigned not null, 'Customer _ id' smallint (5) unsigned not null, 'Return _ date' datetime default null, 'Staff _ id' tinyint (3) unsigned not null, 'last _ update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP, primary key ('Partition _ id'), unique key 'Partition al _ date' ('Partition al _ date ', 'inventory _ id', 'Customer _ id'), KEY 'idx _ fk_inventory_id '('inventory _ id '), KEY 'idx _ fk_mermer_id '('customer _ id'), KEY 'idx _ fk_staff_id' ('staff _ id '), CONSTRAINT 'fk _ polical_customer 'foreign key ('customer _ id') REFERENCES 'customer' ('customer _ id') on update cascade, CONSTRAINT 'fk _ financial_inventory 'foreign key ('inventory _ id') REFERENCES 'inventory' ('inventory _ id') on update cascade, CONSTRAINT 'fk _ comment al_staff 'foreign key ('staff _ id') REFERENCES 'staff' ('staff _ id') on update cascade) ENGINE = InnoDB AUTO_INCREMENT = 16050 default charset = utf8 1 row in set (0.00 sec)
Mysql> alter table partition al drop index partition al_date; Query OK, 16044 rows affected (0.92 sec) Records: 16044 Duplicates: 0 Warnings: 0
Ql> alter table partition al add index idx_rental_date (rental_date, inventory_id, customer_id); Query OK, 16044 rows affected (0.48 sec) Records: 16044 Duplicates: 0 Warnings: 0
// Match the full value. execute a specific value for all columns in the index, that is, the condition that all columns in the index have an equivalent match. Mysql> explain select * from equivalent al where partition al_date = '2017-05-25 17:22:10 'and inventory_id = 2005 and customer_id = 373 \ G (the query conditions can be sorted in disorder in equivalent queries) * *************************** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: ref // use a general index, so it is ref. when you use a unique index or primary key for query, it is const possible_keys: keys, idx_fk_customer_id, idx_external_date key: idx_external_date key_len: 13 ref: const, const, const // display which fields or constants are used together with keys to query records from the table rows: 1 Extra: 1 row in set (0.00 sec)
Mysql> alter table partition al drop index idx_partition al_date; Query OK, 16044 rows affected (0.65 sec) Records: 16044 Duplicates: 0 Warnings: 0
Mysql> alter table partition al add unique index idx_rental_date (rental_date, inventory_id, customer_id); Query OK, 16044 rows affected (0.56 sec) Records: 16044 Duplicates: 0 Warnings: 0
Mysql> explain select * from empty al where export al_date = '2017-05-25 17:22:10 'and inventory_id = 2005 and customer_id = 373 \ G ************ * *************** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: const // when using the unique index or primary key for query, it is const possible_keys: idx_1_al_date, expiration, idx_fk_customer_id key: idx_1_al_date key_len: 13 ref: const, const, const rows: 1 row in set (0.00 sec)
Note: the equal sign or in can be in an out-of-order mysql> explain select * from orders al where customer_id = 5 and explain al_date = '2017-05-30 10:00:00 '\ G ********** * ***************** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: ref possible_keys: idx_fk_customer_id, idx_1_al_date key: idx_1_al_date key_len: 10 ref: const, const rows: 1 Extra: 1 row in set (0.01 sec) mysql> explain select * from Marshal where marshal_date = '2017-05-30 10:00:00 'and customer_id = 5 \ G **************** * *********** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: ref possible_keys: idx_fk_customer_id, idx_1_al_date key: idx_1_al_date key_len: 10 ref: const, const rows: 1 Extra: 1 row in set (0.00 sec)
// Query the range of matched values, the index value can be queried in the range mysql> explain select * from distinct al where customer_id> = 373 and customer_id <= 400 \ G ************* * ************** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 745 Extra: Using where // Using where indicates that the optimizer not only accelerates access based on indexes, data is also returned to the table based on the index. 1 row in set (0.00 sec)
Using where
A where clause is used to restrict which rows to match against the next table or send to the client. unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
// Match the leftmost match. only the leftmost column in the index is used for search.
Mysql> show create table payment \ G **************************** 1. row *************************** Table: payment Create Table: create table 'payment' ('payment _ id' smallint (5) unsigned not null AUTO_INCREMENT, 'Customer _ id' smallint (5) unsigned not null, 'Staff _ id' tinyint (3) unsigned not null, 'Marshal _ id' int (11) default null, 'amount 'decimal (5, 2) not null, 'payment _ date' datetime not null, 'Last _ update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP, primary key ('payment _ id '), KEY 'idx _ fk_staff_id '('staff _ id'), KEY 'idx _ fk_mermer_id' ('customer _ id '), KEY 'fk _ payment_rental '('Marshal _ id'), CONSTRAINT 'fk _ payment_customer' foreign key ('customer _ id') REFERENCES 'custom' ('customer _ id ') on update cascade, CONSTRAINT 'fk _ payment_rental 'foreign key ('Marshal _ id') REFERENCES 'marshal' ('Marshal _ id') on delete set null on update cascade, CONSTRAINT 'fk _ payment_staff 'foreign key ('staff _ id') REFERENCES 'staff' ('staff _ id') on update cascade) ENGINE = InnoDB AUTO_INCREMENT = 16050 default charset = utf8 1 row in set (0.00 sec)
Mysql> alter table payment add index idx_payment_date (payment_date, amount, last_update); Query OK, 16049 rows affected (2.85 sec) Records: 16049 Duplicates: 0 Warnings: 0
Mysql> explain select * from payment where payment_date = '2017-02-14 15:16:03 'and last_update = '2017-02-15 22:12:32' \ G ********* * ****************** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 8 ref: const rows: 182 Extra: Using where 1 row in set (0.00 sec)
Mysql> explain select * from payment where amount = 3 and last_update = '2017-02-15 22:12:32 '\ G **************** * *********** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: ALL // not use leftmost match. full table scan possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)
// Only query the index. when the queried field is in the indexed field, the query efficiency is higher. You do not need to return Table data. Mysql> explain select last_update from payment where payment_date = '2017-02-14 15:16:03 'and amount = 2006 \ G **************** * *********** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 11 ref: const, const rows: 8 Extra: Using index // using index indicates that access to the index is sufficient to obtain the required data, you do not need to go back to the table through the index. using index is often referred to as covering index scanning. Only access the data that must be accessed. In general, reducing unnecessary data access can improve efficiency. 1 row in set (0.04 sec)
// Match the column prefix mysql> show create table film_text \ G *************************** 1. row *************************** Table: film_text Create Table: create table 'film _ text' ('film _ id' smallint (6) not null, 'title' varchar (255) not null, 'description' text, primary key ('film _ id'), fulltext key 'idx _ title_description '('title', 'Description ')) ENGINE = MyISAM default charset = utf8 1 row in set (0.00 sec) mysql> create index idx_title_desc_part on film_text (title (10), description (20); Query OK, 1000 rows affected (0.40 sec) Records: 1000 Duplicates: 0 Warnings: 0
Mysql> explain select title from film_text where title like 'African % '\ G ************************ * ** 1. row ************************** id: 1 select_type: SIMPLE table: film_text type: range possible_keys: idx_title_desc_part, idx_title_description key: idx_title_desc_part // use column prefix matching. The global index idx_title_description key_len: 32 ref: NULL rows: 1 Extra: using where 1 row in set (0.16 sec)
// Index matching is partially accurate, while other parts perform range matching. Mysql> alter table partition al drop index idx_partition al_date; Query OK, 16044 rows affected (0.86 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> alter table partition al add index idx_partition al_date (partition al_date, customer_id, inventory_id); Query OK, 16044 rows affected (1.43 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> explain select inventory_id from each AL where partition al_date = '2017-02-14 15:16:03' D customer_id> = 300 and customer_id <= 400 \ G ***************************** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: range possible_keys: idx_fk_customer_id, idx_1_al_date key: idx_1_al_date key_len: 10 ref: NULL rows: 24 Extra: Using where; Using index 1 row in set (0.00 sec) the execution process is to first use the index's first field rental_date to filter the indexes that match rental_date = '2017-02-14 15:16:03 'and retrieve the data through IO (back to the table, because And then filter records by customer_id> = 300 <= 400.
Note: the mysql Query optimizer will optimize the range statement to a query that can use an index. Mysql> explain select * from distinct al where customer_id> 5 and between al_date = '2017-05-30 10:00:00 '\ G **************** * *********** 1. row ************************* id: 1 select_type: SIMPLE table: Partition Al type: range possible_keys: idx_fk_customer_id, idx_1_al_date key: idx_1_al_date key_len: 10 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec)
// If the column name is an index, the index is used when column_name is null. Mysql> show create table payment \ G **************************** 1. row *************************** Table: payment Create Table: create table 'payment' ('payment _ id' smallint (5) unsigned not null AUTO_INCREMENT, 'Customer _ id' smallint (5) unsigned not null, 'Staff _ id' tinyint (3) unsigned not null, 'Marshal _ id' int (11) default null, 'amount 'decimal (5, 2) not null, 'payment _ date' datetime not null, 'Last _ update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP, primary key ('payment _ id '), KEY 'idx _ fk_staff_id '('staff _ id'), KEY 'idx _ fk_mermer_id' ('customer _ id '), KEY 'fk _ payment_rental '('salary al _ id'), KEY 'idx _ payment_date' ('payment _ date', 'amount ', 'Last _ update '), CONSTRAINT 'fk _ payment_customer 'foreign key ('customer _ id') REFERENCES 'customer' ('customer _ id') on update cascade, CONSTRAINT 'fk _ payment_polical 'foreign key ('Marshal _ id') REFERENCES 'marshal' ('Marshal _ id') on delete set null on update cascade, CONSTRAINT 'fk _ payment_staff 'foreign key ('staff _ id') REFERENCES 'staff' ('staff _ id') on update cascade) ENGINE = InnoDB AUTO_INCREMENT = 16050 default charset = utf8 1 row in set (0.00 sec) mysql> explain select * from payment where your al_id is null \ G *************************** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: fk_payment_marshal key: fk_payment_marshal key_len: 5 ref: const rows: 5 Extra: Using where 1 row in set (0.00 sec)
Indexes cannot be used:
Like queries starting with % cannot use the B-TREE index. it is generally recommended to use the full text index to solve the full text search problem (fuzzy query );
Indexes are not used when data types are implicitly converted. in particular, strings must be enclosed by quotation marks to use indexes. By default, mysql converts the input constants before retrieval. Mysql> show create table actor \ G **************************** 1. row *************************** Table: actor Create Table: create table 'actor '('actor _ id' smallint (5) unsigned not null AUTO_INCREMENT, 'First _ name' varchar (45) not null, 'last _ name' varchar (45) not null, 'Last _ update' timestamp not null default CURRENT_TIMESTAMP ON update CURRENT_TIMESTAMP, primary key ('actor _ id '), KEY 'idx _ actor_last_name '('last _ name') ENGINE = InnoDB AUTO_INCREMENT = 201 default charset = utf8 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = 1 \ G // constants are caused ****************** * ******** 1. row ************************* id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: idx_actor_last_name key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = '1' \ G ************************* ** 1. row ************************* id: 1 select_type: SIMPLE table: actor type: ref possible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
3. indexes are not used if the leftmost condition is not met. 4. conditions separated by or. if there is an index in the column before or and there is no index in the column after or, the indexes involved will not be used. Because the query of the following columns requires full table scan, there is no need to use the previous column for an index scan. Mysql> explain select * from payment where customer_id = 203 or amount = 3.96 \ G *********************** * *** 1. row ************************** id: 1 select_type: SIMPLE table: payment type: ALL possible_keys: idx_fk_customer_id key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)
Mysql> show status like 'handler _ read % '; + metric + ------- + | Variable_name | Value | + metric + ------- + | Handler_read_first | 2 | Handler_read_key | 2 | Handler_read_next | 0 | Handler_read_prev | 0 | metric | 0 | | Handler_read_rnd_next | 33123 | + --------------------- + ------- + 6 rows in set (0.00 sec)
If the index is working, the value of Handler_read_key will be very high, which indicates the number of times a row is read by the index value. a very low value indicates that the performance improved by increasing the index is not high, because indexes are not frequently used. Handler_read_rnd_next indicates the number of requests to read the next row in the data file. If you are performing a large number of table scans and the value of Handler_read_rnd_next is high, it usually indicates that the table index is incorrect or the index is not used when writing the query.
Basic steps for slow query optimization
Run it first to see if it is really slow. pay attention to setting SQL _NO_CACHE;
Query the where condition in a single table to lock the minimum returned record table. This statement applies the where statement of the query statement to the table with the minimum number of records returned from the table and starts to check. each field in a single table is queried separately to see which field has the highest degree of discrimination;
Explain to check whether the execution plan is as expected (from the table with fewer locked records );
Order by limit SQL statements give priority to SORTED tables;
Understand business scenarios;
Refer to the indexing principles when adding indexes;
The observed results do not meet the expectation and continue from 1 analysis;
