Shulou(Shulou.com)05/31 Report--

This article mainly shows you "SQL how to use index sorting", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "SQL how to use index sorting" this article.

Recently, I have been doing slow SQL optimization of the database. From the slow log of the database, I can see that the execution time of a SQL is about 1.7 seconds, and the peak time is more than ten seconds.

Mysql > SELECT id, service_seq, order_item_seq, status, order_time_type->, reserve_time, user_name, user_contact, service_address_type, service_address_id->, service_address, service_mode, insured_name, insured_identity, policy_no->, create_time, update_time, over_time, artisan_id, product_id->, user_id, longitude, latitude, refund_status, settle_status->, comment_status, remark, insured_name Insured_identity, is_del->, postpone_status, last_overdue_time, source_from, operator_id-> FROM order_service_item-> WHERE status = '10'-> AND reserve_time BETWEEN' 2019-07-1006 AND is_del = 0-> ORDER BY id DESC Empty set (1.77 sec)

Execute the plan:

Index is used, but the number of rows scanned is 489300, indicating that the index is not efficient.

View the table structure:

Show create table order_service_item | | order_service_item | CREATE TABLE `order_service_ item` (`id`bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', `service_ seq` varchar (40) COLLATE utf8_bin NOT NULL COMMENT' service serial number', `order_item_ seq` varchar (40) COLLATE utf8_bin NOT NULL COMMENT 'order item serial number' | `status` varchar (2) COLLATE utf8_bin DEFAULT NULL COMMENT 'service status: 00: new order 10: waiting service 20: cancel processing 30: in progress 40: on the road 50: arrive 60: manicurist processing completed 90: user processing completed', `user_ time`datetime DEFAULT NULL COMMENT 'appointment', `user_ name`varchar (40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'customer name' `service_ contact` varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT 'customer contact number', `service_ id` varchar (40) COLLATE utf8_bin DEFAULT NULL, `service_ address` varchar 'COLLATE utf8_bin DEFAULT NULL COMMENT' service address', `service_ mode `tinyint (2) DEFAULT'0' COMMENT 'service type: 0: normal 1: class class', `policy_ no`varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT 'policy number `update_ name` varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT 'insured name', `insured_ identity` varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT 'insured ID card', 'create_ datetime NOT NULL COMMENT' establishment time', `update_ time`datetime NOT NULL COMMENT 'update time', `artisan_ id`varchar (40) COLLATE utf8_bin DEFAULT NULL COMMENT 'craftsman ID', `product_ id`varchar (40) COLLATE utf8_bin DEFAULT NULL COMMENT' work ID' `user_ id` varchar (40) COLLATE utf8_bin DEFAULT NULL COMMENT 'customer ID', `longitude` varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT' longitude', `latitude` varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT 'latitude', `refund_ status`tinyint (2) DEFAULT'0' COMMENT 'refund status: 0 No refund 2 pending 3 refund request failed 4 refund request 5 failed 6 completed 8 refund successful' `comment_ status` tinyint (2) DEFAULT'0' COMMENT 'settlement status', `comment_ status` tinyint (2) DEFAULT'0' COMMENT 'comment status 0 not commented 1 has been commented', `remark` varchar (200) COLLATE utf8_bin DEFAULT NULL COMMENT 'remarks', `last_update_ time`datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `order_time_ type`tinyint (4) DEFAULT NULL COMMENT 'service time type\ nThe manicure 2 purchase contract separation\ nis available on call. `over_ type`varchar (20) COLLATE utf8_bin DEFAULT NULL COMMENT 'service address types: home (customer home), store (store customized by independent craftsman), store_self (store owned by store craftsman), studio (formerly small gathering point), `over_ time`service completion time', `is_ del`tinyint (1) DEFAULT'0' COMMENT 'deletion mark 0 not deleted 1 has been deleted' `postpone_ status` tinyint (2) DEFAULT'0' COMMENT 'extension logo: 0 has not expired 1 has expired 2 extension application 3 has been extended 4 expired cancellation 5 rejected extension', `source_ time`datetime DEFAULT NULL COMMENT 'last expiration time', `source_ from `int (3) DEFAULT NULL COMMENT'20 represents the ID' of the craftsman who specifically executes the service from the package reservation', `operator_ id`varchar (40) COLLATE utf8_bin DEFAULT NULL COMMENT' `verificat_ code` varchar''COMMENT' Tmall verification code', PRIMARY KEY (`id`), UNIQUE KEY `index_service_ seq` (`service_ seq`), KEY `index_order_item_ seq` (`order_item_ seq`), KEY `index_user_ id` (`user_ id`), KEY `index_artisan_ id` (`artisan_ id`), KEY `index_ status` (`status`), KEY `index_product_ id` (`product_ id`), KEY `idx_last_update_ time` (`last_update_ time`) KEY `idx_comment_ status` (`comment_ status`, `status`) ENGINE=InnoDB AUTO_INCREMENT=11593152 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT=' order service item table'|

For this SQL, create a federated index in the status,reserve_time,is_del and id fields

Alter table order_service_item add index idx_sta_rt_del_id (status,reserve_time,is_del,id)

View the execution plan:

The execution plan takes the newly created federated index, and the number of rows retrieved becomes 1 row.

View execution time:

Mysql > SELECT id, service_seq, order_item_seq, status, order_time_type->, reserve_time, user_name, user_contact, service_address_type, service_address_id->, service_address, service_mode, insured_name, insured_identity, policy_no->, create_time, update_time, over_time, artisan_id, product_id->, user_id, longitude, latitude, refund_status, settle_status->, comment_status, remark, insured_name Insured_identity, is_del->, postpone_status, last_overdue_time, source_from, operator_id-> FROM order_service_item-> WHERE status = '10'-> AND reserve_time BETWEEN' 2019-07-1006 AND is_del = 0-> ORDER BY id DESC Empty set (0.00 sec)

The execution time has been reduced to the ms level.

The above is all the content of the article "how SQL uses Index sorting". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.