Shulou(Shulou.com)06/01 Report--

1. Table structure

(root@localhost) [test] > show create table t_demo\ G * * 1. Row * * Table: t_demoCreate Table: CREATE TABLE `tdemo` (`vin`varchar (17) NOT NULL DEFAULT''COMMENT' chassis number', `lng` decimal (10L6) DEFAULT NULL COMMENT 'GPS longitude', `lat`decimal (10L6) DEFAULT NULL COMMENT 'GPS latitude', `gps_ time` datetime DEFAULT NULL `crmkey` char (36) DEFAULT NULL, `crmkey` varchar (30) DEFAULT NULL, `veh_series_ time`varchar (30) DEFAULT NULL, `update_ time`datetime NOT NULL DEFAULT '0000-00-0000: 00varchar 00', PRIMARY KEY (`vin`, `update_ time`), KEY `crmkey` (`crmkey`) ENGINE=InnoDB DEFAULT CHARSET=utf8 / *! 50100 PARTITION BY RANGE (to_days (update_time)) (PARTITION p20160724 VALUES LESS THAN (736535) ENGINE=InnoDB, PARTITION p20160725 VALUES LESS THAN (736542) ENGINE=InnoDB PARTITION p20160801 / 0807 VALUES LESS THAN (736549) ENGINE = InnoDB, PARTITION p20160808 / 0814 VALUES LESS THAN (736556) ENGINE = InnoDB, PARTITION p20160815 / 0821 VALUES LESS THAN (736563) ENGINE = InnoDB, PARTITION p20160822 / 0828 VALUES LESS THAN (736570) ENGINE = InnoDB, PARTITION p20160829 / 0904 VALUES LESS THAN (736577) ENGINE = InnoDB, PARTITION p20160905 VALUES LESS THAN (736584) ENGINE = InnoDB, PARTITION p20160912 / 0918 VALUES LESS THAN (736591) ENGINE = InnoDB, PARTITION p20160919 / 0925 VALUES LESS THAN (736598) ENGINE = InnoDB, PARTITION p20160926 / 1002 VALUES LESS THAN (736605) ENGINE = InnoDB PARTITION p20161003 / 109 VALUES LESS THAN (736612) ENGINE = InnoDB, PARTITION p20161010 / 1016 VALUES LESS THAN (736619) ENGINE = InnoDB, PARTITION p20161017 / 1023 VALUES LESS THAN (736626) ENGINE = InnoDB, PARTITION p20161024 / 1030 VALUES LESS THAN (736633) ENGINE = InnoDB, PARTITION p20161031 / 10106 VALUES LESS THAN (736640) ENGINE = InnoDB, PARTITION p20161107 / 1113 VALUES LESS THAN (736647) ENGINE = InnoDB, PARTITION p20161114 / 1120 VALUES LESS THAN (736654) ENGINE = InnoDB, PARTITION p20161121127 VALUES LESS THAN (736661) ENGINE = InnoDB, PARTITION p201611281204 VALUES LESS THAN (736668) ENGINE PARTITION p20161205 / 1211 VALUES LESS THAN (736675) ENGINE = InnoDB, PARTITION p2016121218 VALUES LESS THAN (736682) ENGINE = InnoDB, PARTITION p20161219 / 1225 VALUES LESS THAN (736789) ENGINE = InnoDB, PARTITION p20161226 / 170101 VALUES LESS THAN (736796) ENGINE = InnoDB) * / 1 row in set (0.00 sec)

two。 View the execution plan

(root@localhost) [test] > explain partitions SELECT COUNT (1)-> FROM t_demo tvghg-> WHERE tvghg.crmkey = 'cf71ea00-65ffmur4521murb336MuthFdc13846e2e2e2'-> AND tvghg.update_time > = '2016-07-19 06FROM t_demo tvghg 0000'-> AND tvghg.update_time

< '2016-07-19 07:00:00' ->

AND (tvghg.veh_series_desc IN ('A6legs,' A6L')) +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +- +-+ | 1 | SIMPLE | tvghg | p20160724 | ref | idx_crmkey | idx_crmkey | 109 | const | 1961366 | Using where | + -+-+ 1 row in set (0.00 sec)

3. View selection rate

(root@localhost) [test] > select count (distinct (crmkey)) / count (*) AS selectivity1,count (distinct (update_time)) / count (*) AS selectivity2, count (distinct (veh_series_desc)) / count (*) AS selectivity3 from t_demo +-+ | selectivity1 | selectivity2 | selectivity3 | +-+ | 0.0001 | 0.0000 | 0.0000 | +-- -+ 1 row in set (43.95 sec) select count (distinct (concat (crmkey) Update_time)) / count (*) from t_demo +-- + | count (distinct (concat (crmkey) Update_time)) / count (*) | +-- + | 0.0136 | + -- + 1 row in set (52.66 sec) 4. Query time (root@localhost) [test] > SELECT COUNT (1)-> FROM t_demo tvghg-> WHERE tvghg.crmkey = 'cf71ea00-65ffmur4521lu b336ltel fdc13846e2e2'-> AND tvghg.update_time > =' 2016-07-19 06GRV 0000'-> AND tvghg.update_time

< '2016-07-19 07:00:00' ->

AND (tvghg.veh_series_desc IN ('A6legs,' A6L')); +-+ | COUNT (1) | +-+ | 2695 | +-+ 1 row in set (3.67 sec)

Here to consider how to create the index, involving multiple fields, we need to make a judgment, looking at the selection rate is a very important reference for us to create the index. The table here is a partitioned table, partitioned according to time, and the query field also contains a time field. We must have a time field for index creation.

5. Create an index

(root@localhost) [(none)] > create index idx_tvghg_crmkey_update_time on `test`.`t _ demo` (crmkey,update_time); Query OK, 0 rows affected (4 min 40.21 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost) [test] > alter table `test`.`t _ demo` drop index idx_crmkey;Query OK, 0 rows affected (1.16 sec) Records: 0 Duplicates: 0 Warnings: 0

6. Query, view execution plan

SELECT COUNT (1) FROM t_demo tvghg WHERE tvghg.crmkey = 'cf71ea00-65ffly4521lyb336Mufdc13846e2e2e2' AND tvghg.update_time > =' 2016-07-1906 cf71ea00 0000' AND tvghg.update_time

< '2016-07-19 07:00:00' AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));+----------+| COUNT(1) |+----------+| 2695 |+----------+1 row in set (0.03 sec)(root@localhost) [test]>

Explain partitions SELECT COUNT (1)-> FROM t_demo tvghg-> WHERE tvghg.crmkey = 'cf71ea00-65ffly4521Muthb336Mutual fdc13846e2e2e2'-> AND tvghg.update_time > =' 2016-07-1906GRV 0000'-> AND tvghg.update_time

< '2016-07-19 07:00:00' ->

AND (tvghg.veh_series_desc IN ('A6legs,' A6L')) +- +-+ | id | select_type | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+ | 1 | SIMPLE | tvghg | p20160724 | range | | idx_tvghg_crmkey_update_time | idx_tvghg_crmkey_update_time | 114 | NULL | 12446 | Using where | + -+

Creating an index is definitely a technical task. In order to create a reasonable index with as little disk space as possible, we still need to know more about the business and know ourselves and the enemy. The evaluation is good before the launch, it is really online, and the creation of the index will also have a great impact on the system.

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.