Scenario analysis

The order sheet

CREATE TABLE test_innodb.torder ( `id` int(11) NOT NULL, order_no varchar(100) NOT NULL, Order_status vARCHar (100) NOT NULL, order_create_time DATETIME NOT NULL, order_amount BIGINT NOT NULL,  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;Copy the code

The query demand

  • Query all orders with an order status within the last 30 days
  • Count all orders that are in an order state
  • Query the status of the order according to the order number

KEY order_status- order_NO (order_status, order_no), KEY order_status-order_create_time (order_status, order_create_time) order_create_time), UNIQUE KEY order_no (order_no) USING BTREE

Citizens table

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
Copy the code

The query demand

  1. Query all information based on the citizen id number
  2. Check the citizen’s name against his id number

KEY ID_card (ID_card), KEY name_age (name,age)

Optimization principle:

1. Left-most matching principle

The leftmost matching principle states that Mysql matches the index by the leftmost N fields of the union index or by the leftmost M characters of the string index.

If it is possible to reduce the number of indexes by adding and adjusting the order of the union indexes, this order can be given priority.

2. Overwrite indexes

Overwriting the index and the core principle is simple, is to avoid the search tree through the primary key index during the query process, reduce the number of times back to the table, that is, reduce disk I/O access.

If demand increase coverage index can match to the high frequency, can give priority to set up the index, at the same time should pay attention to is the index increased after the first moment space consumption, the second will degrade the performance of the insert data (through reading and writing, to a certain extent separation can solve), but the two points above under the general business scenario is tolerable.

3. Index push down

Index condition Pushdown (introduced in MySQL 5.6) allows you to determine the fields in an index during index traversal and filter out records that do not meet the conditions to reduce the number of entries back to the table.

This optimization principle can be judged by analogy with the overwriting index principle, which analyzes the performance of specific optimizations based on specific SQL.