html下拉列框安卓,MySQL优化之联合索引

仿佛轻云兮如敝月

于 2021-06-03 08:06:45 发布

阅读量97

点赞数

文章标签： html下拉列框安卓

1.表结构(root@localhost) [test]> show create table t_demoG;*************************** 1. row ***************************Table: t_demoCreate Table: CREATE TABLE `t_demo` ( `vin` varchar(17) NOT NULL DEFAULT '' COMMENT '底盘号', `lng` decimal(10,6) DEFAULT NULL COMMENT 'GPS经度', `lat` decimal(10,6) DEFAULT NULL COMMENT 'GPS纬度', `gps_time` datetime DEFAULT NULL, `crmkey` char(36) DEFAULT NULL , `veh_model_desc` varchar(30) DEFAULT NULL , `veh_series_desc` varchar(30) DEFAULT NULL , `update_time` datetime NOT NULL DEFAULT '002021年06月03日-00 00:00:00' , PRIMARY KEY (`vin`,`update_time`), KEY `idx_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_0731 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_0911 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_1009 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_1106 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 p20161121_1127 VALUES LESS THAN (736661) ENGINE = InnoDB, PARTITION p20161128_1204 VALUES LESS THAN (736668) ENGINE = InnoDB, PARTITION p20161205_1211 VALUES LESS THAN (736675) ENGINE = InnoDB, PARTITION p20161212_1218 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)

3.查看选择率(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.查询时间(root@localhost) [test]> SELECT COUNT(1) -> FROM t_demo tvghg -> WHERE tvghg.crmkey ='cf71ea2021年06月03日ff-4521-b336-fdc13846e2e2' -> AND tvghg.update_time >= '2021年06月03日 06:00:00' -> AND tvghg.update_time < '2021年06月03日 07:00:00' -> AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));+----------+| COUNT(1) |+----------+| 2695 |+----------+1 row in set (3.67 sec)

这里考虑索引怎么创建，涉及到多个字段，需要我们去做判断，查看选择率是我们创建索引的一个很重要的参考。这里的表是一个分区表，按照时间做的分区，查询字段里也包含时间字段，索引创建索引我们肯定要有时间字段的哦。

5.创建索引(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.查询，查看执行计划SELECT COUNT(1) FROM t_demo tvghg WHERE tvghg.crmkey ='cf71ea2021年06月03日ff-4521-b336-fdc13846e2e2' AND tvghg.update_time >= '2021年06月03日 06:00:00' AND tvghg.update_time < '2021年06月03日 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 ='cf71ea2021年06月03日ff-4521-b336-fdc13846e2e2' -> AND tvghg.update_time >= '2021年06月03日 06:00:00' -> AND tvghg.update_time < '2021年06月03日 07:00:00' -> AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+| id | select_type | table | 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 |+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+

创建索引绝对是一个技术活，在尽量占用少的磁盘空间，创建出合理的索引，还是要多了解业务，知己知彼。在上线前评估好，真的上线了，创建索引对系统也会有不小的影响。