MySQL優(yōu)化之聯(lián)合索引

1.表結(jié)構(gòu)

成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)、漢陽網(wǎng)絡(luò)推廣、成都微信小程序、漢陽網(wǎng)絡(luò)營銷、漢陽企業(yè)策劃、漢陽品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供漢陽建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：muchs.cn

(root@localhost) [test]> show create table t_demo\G;
*************************** 1. row ***************************
       Table: t_demo
Create Table: CREATE TABLE `t_demo` (
  `vin` varchar(17) NOT NULL DEFAULT '' COMMENT '底盤號',
  `lng` decimal(10,6) DEFAULT NULL COMMENT 'GPS經(jīng)度',
  `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 '0000-00-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)

2.查看執(zhí)行計(jì)劃

(root@localhost) [test]> explain partitions SELECT COUNT(1)
    -> FROM t_demo tvghg
    -> WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
    ->   AND tvghg.update_time >=  '2016-07-19 06:00:00' 
    ->  AND tvghg.update_time < '2016-07-19 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  | ref  | idx_crmkey    | idx_crmkey | 109     | const | 1961366 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+-------------+
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.查詢時(shí)間

(root@localhost) [test]> SELECT COUNT(1)
    -> FROM t_demo tvghg
    -> WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
    ->   AND tvghg.update_time >=  '2016-07-19 06:00:00' 
    ->  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 (3.67 sec)

這里考慮索引怎么創(chuàng)建，涉及到多個(gè)字段，需要我們?nèi)プ雠袛啵榭催x擇率是我們創(chuàng)建索引的一個(gè)很重要的參考。這里的表是一個(gè)分區(qū)表，按照時(shí)間做的分區(qū)，查詢字段里也包含時(shí)間字段，索引創(chuàng)建索引我們肯定要有時(shí)間字段的哦。

5.創(chuàng)建索引

(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.查詢，查看執(zhí)行計(jì)劃

SELECT COUNT(1)
     FROM t_demo tvghg
     WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
       AND tvghg.update_time >=  '2016-07-19 06:00:00' 
      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-65ff-4521-b336-fdc13846e2e2'
    ->        AND tvghg.update_time >=  '2016-07-19 06:00:00' 
    ->       AND tvghg.update_time < '2016-07-19 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 |
+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+

創(chuàng)建索引絕對是一個(gè)技術(shù)活，在盡量占用少的磁盤空間，創(chuàng)建出合理的索引，還是要多了解業(yè)務(wù)，知己知彼。在上線前評估好，真的上線了，創(chuàng)建索引對系統(tǒng)也會有不小的影響。

分享題目：MySQL優(yōu)化之聯(lián)合索引
網(wǎng)站鏈接：http://muchs.cn/article22/jioejc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制網(wǎng)站、做網(wǎng)站、營銷型網(wǎng)站建設(shè)、搜索引擎優(yōu)化、網(wǎng)站收錄、企業(yè)建站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容