Consult everybody a great god, and in the Lord, the same table structure, the same data, the same SQL, walk from the library index, the main library doesn't walk, index
Show the index from tr_order command to see the index is effective, has not been closed,
From the library to use exlpain see SQL execution plan, index,
In the main library with exlpain see SQL execution plan, don't walk index,
Excuse me each great god have encountered such a problem? How to solve,

CodePudding user response:

Perhaps the statistical information,
If sure go faster index, using the force index binding

CodePudding user response:

Tried to use force index, he walk force index, but the number of row come with no index number is the same,

CodePudding user response:

CongKu index row is 16 main library didn't walk index row is 300000 main force index forced walk shows the index in the index key, but the row is 300000

CodePudding user response:

Table structure, index, execution plan, it's convenient for all posted look, little information is difficult to determine

CodePudding user response:

table of master-slave structure:
The CREATE TABLE ` tr_order ` (
` ctb_order_id ` bigint (20) NOT NULL AUTO_INCREMENT,
` order_no ` char (32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '13 + on the timestamp them roughly (3rd) + the machine IP (3rd) + PID (3) (2) + + data type currency ID (3rd) + random number (5)',
` customer_uuid ` char (32) COLLATE utf8mb4_bin NOT NULL COMMENT '[user ID] associated,
` currency_id ` int (11), NOT NULL COMMENT '[currency ID]',
` base_currency_id ` int (11), NOT NULL COMMENT '[ID] base currency,
` buyOrSell ` int (11) the DEFAULT NULL COMMENT '[trading] 1. Buy 2. Sell',
` type ` int (11) the DEFAULT NULL COMMENT '[commissioned category] 1. Price 2. The market',
` num ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[entrusts number],
` trade_num ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[clinch a deal amount],
` cancel_num ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[from single number],
` remain_num ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[number] unperformed part after clinch a deal the rest amount, initial value is equal to the delegate number remain_num=num - trade_num - cacel_num',
` price ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[commissioned price],
` source ` int (11) the DEFAULT '1' COMMENT '[commissioned source] 1. The web browser (DEFAULT)',
` order_time ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '[commissioned time] now equals Create_Time'
` fee ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT 'entrusted commission] [buyOrSell to 1: identification of digital currency 2: when the logo for the yuan',
` freeze_fee ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[freeze poundage] temporarily for the reserved field,
` freeze_amount ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '[amount frozen]',
` risk_flag ` int (11) the DEFAULT '1' COMMENT '[risk control mark] 1. No risk control early warning 2. Risk control early warning',
` cancel_time ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '[its] time,
` average_price ` decimal (32 16th) DEFAULT '0.0000000000000000' COMMENT '] [average price have after clinching a deal to write ',
` status ` int (11), NOT NULL DEFAULT '0' COMMENT '[state] 0. 1. Unperformed part volume 2. Volume 3. All entrust failed 4. All cancellations 5. Some clinch a deal the retreat single',
` create_time ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
` create_by ` varchar (50) COLLATE utf8mb4_bin DEFAULT NULL,
` last_edit_time ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
` last_edit_by ` varchar (50) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (` ctb_order_id `),
The UNIQUE KEY ` order_no_idx ` (` order_no `),
The KEY ` order_cuuuid_indx ` (` customer_uuid `) USING HASH,
The KEY ` order_currency_indx ` (` currency_id `) USING HASH,
The KEY ` order_basecurrency_indx ` (` base_currency_id `) USING HASH,
The KEY ` status_index ` (` status `),
The KEY ` link_index ` (` customer_uuid `, ` currency_id `, ` base_currency_id `, ` status `),
The KEY ` ordertime ` (` order_time `)
) ENGINE=InnoDB AUTO_INCREMENT=407749478 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='orders table;



SQL:
The EXPLAIN
SELECT
Order_no,
Customer_uuid,
Currency_id,
Base_currency_id,
BuyOrSell,
The type,
Num,
Trade_num,
Cancel_num,
Remain_num,
Price,
Order_time,
Fee,
Risk_flag,
Cancel_time,
Average_price,
The STATUS
The FROM
Tr_order
WHERE
1=1
AND customer_uuid='3462 a7a8845741089e628f2804be5c2b'
AND currency_id=219
AND base_currency_id=63
AND the STATUS IN (0, 1)
AND type=1
AND order_time & gt;
='2019-03-14'The ORDER BY
Ctb_order_id DESC
LIMIT 0,
50;

The main library execution plan:


From the library execution plan

CodePudding user response:

WHERE
1=1
AND customer_uuid='3462 a7a8845741089e628f2804be5c2b'
AND currency_id=219
AND base_currency_id=63
AND the STATUS IN (0, 1)
AND type=1
AND order_time & gt;
='2019-03-14'
The total return where clause for how much?

Stick this information:
Show the index from tr_order;

CodePudding user response:

This is the main library, that is, don't walk index library

CodePudding user response:

From the index, the library should be binding index link_index, force index (link_index)

You compare the force index (link_index) and force index (order_cuuuid_index) execution plan and time

CodePudding user response:

Have you found the reason? I settled, but found no reason