目录

• 几个基础概念

• 索引的作用

• 索引种类

• 索引为何不可用

• 索引原则与误区

• 执行计划

• 测试案例

几个基础概念

用户

api@’192.168.1.1’
api@’192.168.1.%’
api@’%’

权限

应用程序：增删改查
用户：只读->从库
其他操作：自动化运维平台

索引的作用

• 提高查询效率

• 消除数据分组/排序

• 避免”回表”查询

• 优化聚合查询

• 用于多表JOIN关联查询

• 利用唯一性约束，保证数据唯一性

• InnoDB行锁实现

• 增加I/O成本

• 增加磁盘空间

• 不适合的索引，或索引过多，都不是好事

索引种类

• BTREE，InnoDB & MyISAM

• Fractal TREE，TokuDB

• HASH HEAP NDB InnoDB AHI

• RTREE

• FULLTEXT

索引讲解

• 聚集索引

• 主键索引

• 唯一索引

• 联合索引

• 覆盖索引

• 前缀索引

聚集索引

• 聚集索引是一种特殊的索引，该索引中键值的逻辑顺序决定了表数据行的物理顺序；

• 每张表只能建一个聚集索引，除了TokuDB引擎；

• InnoDB中，聚集索引即表，表即聚集索引；

• MyISAM没有聚集索引的概念

• 聚集索引优先选择列

1. INT/BIGINT;

2. 数据连续（单调顺序）递增/自增；

• 不建议的聚集索引

1. 修改频繁的列；

2. 新增数据太过离散随机；

• InnoDB聚集索引选择次序原则

1. 显示声明的主键；

2. 第一个NOT NULL的唯一索引

3. ROWID（实例级，6bytes）

创建测试表1(显示指定主键)

mysql> create table t(id int auto_increment,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.02 sec)

TABLE: name test/t, id 23, flags 1, columns 5, indexes 2, appr.rows 0
  COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name PRIMARY, id 25, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR name
  INDEX: name idx_name, id 26, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name id

从上面可以看到InnoDB选择了主键id作为唯一索引，而索引idx_name为普通索引

创建测试表2(不指定主键且指定一个非空唯一索引)

mysql> create table t1(id int ,name varchar(10) not null,unique key idx_name(name));
Query OK, 0 rows affected (0.01 sec)

TABLE: name test/t1, id 25, flags 1, columns 5, indexes 1, appr.rows 0
  COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4; name: DATA_VARMYSQL DATA_NOT_NULL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name idx_name, id 29, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name DB_TRX_ID DB_ROLL_PTR id

从日志看到，聚集索引选择的是idx_name

创建测试表3(不指定主键也无非空唯一索引)

mysql> create table t2(id int ,name varchar(10),key idx_name(name));
Query OK, 0 rows affected (0.02 sec)

TABLE: name test/t2, id 26, flags 1, columns 5, indexes 2, appr.rows 0
  COLUMNS: id: DATA_INT DATA_BINARY_TYPE len 4; name: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name GEN_CLUST_INDEX, id 30, fields 0/5, uniq 1, type 1
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
  INDEX: name idx_name, id 31, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name DB_ROW_ID

从日志可以看出，InnoDB选择的是rowid作为聚集索引

创建测试表4(包含以上三种索引)

mysql> create table t3(id int auto_increment,name1 varchar(10) not null,name2 varchar(10),primary key(id),unique key idx_name1(name1),key idx_name2(name2));
Query OK, 0 rows affected (0.02 sec)

TABLE: name test/t3, id 27, flags 1, columns 6, indexes 3, appr.rows 0
  COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; name1: DATA_VARMYSQL DATA_NOT_NULL len 30; name2: DATA_VARMYSQL len 30; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; 
  INDEX: name PRIMARY, id 32, fields 1/5, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  id DB_TRX_ID DB_ROLL_PTR name1 name2
  INDEX: name idx_name1, id 33, fields 1/2, uniq 1, type 2
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name1 id
  INDEX: name idx_name2, id 34, fields 1/2, uniq 2, type 0
   root page 5, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  name2 id

从日志看出，InnoDB还是选择的是主键作为聚集索引

主键索引

• 主键由表中一个或多个字段组成，它的值用于唯一地标识表中的某一条记录；

• 在表引用中，主键在一个表中引用来自于另一个表中的特定记录（外键foreign key应用）；

• 保证数据的完整性；

• 加快数据的操作速度；

• 主键值不能重复，也不能包含NULL;

• 主键选择建议

1. 对业务透明，无意义，免受业务变化的影响；

2. 很少修改和删除

3. 最好是自增；

4. 不要具有动态属性，例如随机值；

• 糟糕的主键选择：

1. UUID

2. char/varchar

辅助索引

• 非聚集索引，或者二级索引，俗称普通索引

• 当通过InnoDB辅助索引来查找数据的时候，辅助索引会通过页级的指针来找到主键索引的主键，然后通过该主键索引找到相应的行数据

• 索引定义时，不管有无显示包含主键，实际都会存储主键值；

• 在5.6.9后，优化器已能自动识别索引末尾的主键值（Index Extensions）,在这之前则需要显式加上主键列才可以被识别；
  WHERE c1 = ? AND PK = ?
  WHERE c1 = ? ORDER BY PK

唯一索引

• 不允许具有索引值相同的行，从而禁止重复的索引或键值

• 在唯一约束上，和主键一样（以MyISAM引擎为代表）

• 其他不同的方面：

1. 唯一索引允许有空值（NULL）

2. 一个表只能有一个主键，但可以有多个唯一索引

3. InnoDB表中主键必须是聚集索引，但聚集索引可能不是主键

4. 唯一索引约束可临时禁用，但主键不能

联合索引

• 多列组成，所以也叫多列索引

• 适合WHERE条件中的多列组合

• 有时候，还可以用于避免回表（覆盖索引）

• MySQL还不支持多列不同的排序规则（MySQL 8.0起支持）

• 联合索引建议

A.WHERE条件中，经常同时出现的列放在联合索引中
B.把选择性（过滤性/基数）大的列放在联合索引的最左边

• 如果第一列是范围查询，就无法用到后面的列了，可以利用ICP的特性

• 覆盖索引就是只要利用索引数就可以得到所有的数据了

示例

root@localhost [xucl]>show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [xucl]>explain select * from test where a='a';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 33      | const |    5 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [xucl]>explain select * from test where a='a' and b='b';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 66      | const,const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [xucl]>explain select * from test where a='a' and b='b' and c='c';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_a_b_c     | idx_a_b_c | 99      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

覆盖索引

选择的列+where就可以覆盖索引
执行计划里面type=index，表示full index scan，
extra中显示using index表示覆盖索引

示例

root@localhost [xucl]>create table tx(
    -> id int(11) not null auto_increment comment '记录ID',
    -> shid int(11) not null comment '商店ID',
    -> gid int(11) not null comment '物品ID',
    -> type tinyint(11) not null comment '支付方式',
    -> price int(11) not null comment '物品价格',
    -> comment varchar(200) not null comment '备注',
    -> primary key(id),
    -> unique key uk_shid_gid(shid,gid),
    -> key idx_price(price),
    -> key idx_type(type)
    -> )engine=innodb auto_increment=1 default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
root@localhost [xucl]>explain select id,shid,gid from tx order by shid,gid;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tx    | NULL       | index | NULL          | uk_shid_gid | 8       | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

前缀索引

• 部分索引的原因

A.char/varchar太长全部做索引的话，效率太差，存在浪费
B.或者blob/text类型不能整列作为索引列，因此需要使用前缀索引

• 部分索引选择建议

A.统计平均值
B.满足80%~90%覆盖度就够

• 缺点

无法利用前缀索引完成排序

示例1

root@localhost [xucl]>create table t_text(id int auto_increment primary key,url text);
Query OK, 0 rows affected (0.02 sec)
root@localhost [xucl]>select * from t_text\G
*************************** 1. row ***************************
 id: 1
url: http://www.seomofo.com/experiments/title-and-h1-of-this-post-but-for-the-sake-of-keyword-prominence-stuffing-im-going-to-mention-it-again-using-various-synonyms-stemmed-variations-and-of-coursea-big-fat-prominent-font-size-heres-the-stumper-that-stumped-me-what-is-the-max-number-of-chars-in-a-url-that-google-is-willing-to-crawl-and-index-for-whatever-reason-i-thought-i-had-read-somewhere-that-googles-limit-on-urls-was-255-characters-but-that-turned-out-to-be-wrong-so-maybe-i-just-made-that-number-up-the-best-answer-i-could-find-was-this-quote-from-googles-webmaster-trends-analyst-john-mueller-we-can-certainly-crawl-and-index-urls-over-1000-characters-long-but-that-doesnt-mean-that-its-a-good-practice-the-setup-for-this-experiment-is-going-to-be-pretty-simple-im-going-to-edit-the-permalink-of-this-post-to-be-really-really-long-then-im-going-to-see-if-google-indexes-it-i-might-even-see-if-yahoo-and-bing-index-iteven-though-no-one-really-cares-what-those-assholes-are-doing-url-character-limits-unrelated-to-google-the-question-now-is-how-many-characters-should-i-make-the-url-of-this-post-there-are-a-couple-of-sources-ill-reference-to-help-me-make-this-decision-the-first-is-this-quote-from-the-microsoft-support-pages-microsoft-internet-explorer-has-a-maximum-uniform-resource-locator-url-length-of-2083-characters-internet-explorer-also-has-a-maximum-path-length-of-2048-characters-this-limit-applies-to-both-post-request-and-get-request-urls-the-second-source-ill-cite-is-the-http-11-protocol-which-says-the-http-protocol-does-not-place-any-a-priori-limit-on-the-length-of-a-uri-servers-must-be-able-to-handle-the-uri-of-any-resource-they-serve-and-should-be-able-to-handle-uris-of-unbounded-length-if-they-provide-get-based-forms-that-could-generate-such-uris-a-server-should-return-414-request-uri-too-long-status-if-a-uri-is-longer.html
1 row in set (0.00 sec)

root@localhost [xucl]>select length(url) from t_text;
+-------------+
| length(url) |
+-------------+
|        1855 |
+-------------+
1 row in set (0.00 sec)

root@localhost [xucl]>create index idx_url on t_text(url);
ERROR 1170 (42000): BLOB/TEXT column 'url' used in key specification without a key length

root@localhost [xucl]>create index idx_url on t_text(url(255));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost [xucl]>show index from t_text;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_text |          0 | PRIMARY  |            1 | id          | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| t_text |          1 | idx_url  |            1 | url         | A         |           1 |      255 | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

示例2

root@localhost [xucl]>create table t_varchar(id int auto_increment primary key,url varchar(4000));
Query OK, 0 rows affected (0.02 sec)
root@localhost [xucl]>create index idx_url on t_varchar(url);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
root@localhost [xucl]>create index idx_url on t_varchar(url(1000));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost [xucl]>explain select * from t_varchar where url like 'http%';
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_varchar | NULL       | range | idx_url       | idx_url | 3003    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 索引最大长度767bytes

• 启用innodb_large_prefix，增加到3072bytes，只针对DYNAMIC、COMPRESSED格式管用

• 对于REDUNDANT、COMPACT格式，最大索引长度还是767bytes

• MyISAM表索引最大长度是1000bytes

• 最大默认排序长度1024bytes

索引为何不可用

• 通过索引扫描的记录数超过20%-30%，可能会变成全表扫描

• 联合索引中，第一个查询条件不是最左索引列

• 模糊查询条件最左以通配符%开始

• 多表关联时，排序字段不属于驱动表，无法利用索引完成排序

• JOIN查询时，关联数据类型（字符集）不一致也会导致索引不可用

索引原则与误区

原则1：单表索引数不要超过5个

root@localhost [xucl]>show create table wcmchnldoc\G
*************************** 1. row ***************************
       Table: wcmchnldoc
Create Table: CREATE TABLE `wcmchnldoc` (
  `CHNLID` int(11) NOT NULL,
  `DOCID` int(11) NOT NULL,
  `DOCORDER` int(11) NOT NULL DEFAULT '0',
  `DOCSTATUS` int(11) NOT NULL DEFAULT '0',
  `CRUSER` varchar(100) NOT NULL DEFAULT 'admin',
  ....
  PRIMARY KEY (`RECID`),
  KEY `IX_WCMCHNLDOC_CHNL_DOC` (`CHNLID`,`DOCID`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CHNL_ORDER` (`CHNLID`,`DOCORDER`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRDEPT` (`CRDEPT`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRTIMEUDSCSDF` (`CRTIME`,`CRUSER`,`DOCSTATUS`,`CHNLID`,`SITEID`,`DOCFORM`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRTIMEUDSCSDO` (`CRTIME`,`CRUSER`,`DOCSTATUS`,`CHNLID`,`SITEID`,`DOCOUTUPID`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRTIMEUDSCSM` (`CRTIME`,`CRUSER`,`DOCSTATUS`,`CHNLID`,`SITEID`,`MODAL`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRUSER` (`CRUSER`) USING BTREE,
  KEY `IX_WCMCHNLDOC_DOCFIRSTPUBTIME` (`DOCFIRSTPUBTIME`) USING BTREE,
  KEY `IX_WCMCHNLDOC_DOCTYPE` (`DOCTYPE`) USING BTREE,
  KEY `IX_WCMCHNLDOC_DOCID_MODAL` (`DOCID`,`MODAL`) USING BTREE,
  KEY `IX_WCMChnlDoc_OriginDocId_SiteId_CrTime` (`OriginDocId`,`SITEID`,`CRTIME`) USING BTREE,
  KEY `IX_WCMChnlDoc_SiteId_OriginDocId` (`SITEID`,`OriginDocId`) USING BTREE,
  KEY `IX_WCMChnlDoc_CrUserStatus` (`DOCKIND`,`CRUSER`,`DOCSTATUS`),
  KEY `IX_WCMChnlDoc_SiteIdStatus` (`DOCKIND`,`SITEID`,`DOCSTATUS`),
  KEY `IX_WCMChnlDoc_PubTimeChnlStatus` (`DOCKIND`,`DOCPUBTIME`,`CHNLID`,`DOCSTATUS`),
  KEY `IX_WCMChnlDoc_CrUserSiteStatus` (`DOCKIND`,`CRUSER`,`SITEID`,`DOCSTATUS`),
  KEY `idx_DocId_SrcMetaDataId` (`DOCID`,`SrcMetaDataId`),
  KEY `idx_SrcMetaDataId` (`SrcMetaDataId`),
  KEY `idx_doc_chl_sta_pub` (`DOCKIND`,`CHNLID`,`DOCSTATUS`,`ISTIMINGPUBLISH`,`OPERTIME`),
  KEY `idx_chnlid_docstatus_dockind_docpubtime` (`CHNLID`,`DOCSTATUS`,`DOCKIND`,`DOCPUBTIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
root@localhost [xucl]>show create table wcmchnldoc\G
*************************** 1. row ***************************
       Table: wcmchnldoc
Create Table: CREATE TABLE `wcmchnldoc` (
  `CHNLID` int(11) NOT NULL,
  `DOCID` int(11) NOT NULL,
  `DOCORDER` int(11) NOT NULL DEFAULT '0',
  `DOCSTATUS` int(11) NOT NULL DEFAULT '0',
  `CRUSER` varchar(100) NOT NULL DEFAULT 'admin',
  `CRTIME` datetime DEFAULT NULL,
 ....
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_recid` (`RECID`),
  KEY `idx_docpuburl_doctype` (`DOCPUBURL`,`DOCTYPE`)
) ENGINE=InnoDB AUTO_INCREMENT=7824427 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

大小对比

MariaDB [information_schema]> select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 from tables where table_name = 'wcmchnldoc';
+-----------------------------------------------------+
| (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 |
+-----------------------------------------------------+
|                                      7.618774414063 |
+-----------------------------------------------------+
1 row in set (0.02 sec)
root@localhost [xucl]>select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 from information_schema.tables where table_name = 'wcmchnldoc';
+-----------------------------------------------------+
| (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 |
+-----------------------------------------------------+
|                                      1.877914428711 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

数据导入对比

root@localhost [xucl]>load data infile '/tmp/wcmchnldoc' into table wcmchnldoc;                                                                                                                                   
Query OK, 1000000 rows affected (2 min 20.64 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0
root@localhost [xucl]>load data infile '/tmp/wcmchnldoc' into table wcmchnldoc;                                                                                                                                   
Query OK, 1000000 rows affected (23.89 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

140S VS 24S

原则2：单个索引列不要超过5个

原则3：多列索引满足最左匹配原则

原则4：区分度高的列放在索引的左边

root@localhost [xucl]>show create table ttt\G
*************************** 1. row ***************************
       Table: ttt
Create Table: CREATE TABLE `ttt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` int(11) DEFAULT NULL,
  `chnnel_id` int(11) DEFAULT NULL,
  `docid` int(11) DEFAULT NULL,
  `remark` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_docid` (`docid`),
  KEY `idx_status_chnnel_docid` (`status`,`chnnel_id`,`docid`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [xucl]>select * from ttt;
+-----+--------+-----------+-------+--------+
| id  | status | chnnel_id | docid | remark |
+-----+--------+-----------+-------+--------+
|  19 |      1 |         1 |   120 |        |
|  20 |      1 |         1 |   121 |        |
|  21 |      1 |         2 |   122 |        |
|  22 |      1 |         2 |   123 |        |
|  23 |      0 |         3 |   124 |        |
|  24 |      0 |         3 |   125 |        |
|  25 |      0 |         4 |   126 |        |
|  26 |      0 |         5 |   127 |        |
      |
...
|  99 |      1 |         1 |   201 |        |
| 100 |      1 |         1 |   202 |        |
| 101 |      1 |         2 |   203 |        |
| 102 |      1 |         2 |   204 |        |
| 103 |      0 |         3 |   205 |        |
| 104 |      0 |         3 |   206 |        |
| 105 |      0 |         4 |   207 |        |
| 106 |      0 |         5 |   208 |        |
| 107 |      1 |         1 |   209 |        |
| 108 |      1 |         1 |   210 |        |
| 109 |      1 |         2 |   211 |        |
| 110 |      1 |         2 |   212 |        |
| 111 |      0 |         3 |   213 |        |
| 112 |      0 |         3 |   214 |        |
| 113 |      0 |         4 |   215 |        |
| 114 |      0 |         5 |   216 |        |
| 115 |      1 |         1 |   217 |        |
| 116 |      1 |         1 |   218 |        |
| 117 |      1 |         2 |   219 |        |
| 118 |      1 |         2 |   220 |        |
| 119 |      0 |         3 |   221 |        |
| 120 |      0 |         3 |   222 |        |
| 121 |      0 |         4 |   223 |        |
| 122 |      0 |         5 |   224 |        |
+-----+--------+-----------+-------+--------+
103 rows in set (0.00 sec)
root@localhost [xucl]>select count(distinct(status))/count(*),count(distinct(chnnel_id))/count(*),count(distinct(docid))/count(*) from ttt;
+----------------------------------+-------------------------------------+---------------------------------+
| count(distinct(status))/count(*) | count(distinct(chnnel_id))/count(*) | count(distinct(docid))/count(*) |
+----------------------------------+-------------------------------------+---------------------------------+
|                           0.0194 |                              0.0485 |                          1.0000 |
+----------------------------------+-------------------------------------+---------------------------------+
1 row in set (0.00 sec)
root@localhost [xucl]>explain select * from ttt where status=1 and chnnel_id=2 and docid in (120,121,122,130);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ttt   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |    12.50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [xucl]>create index idx_status_chnnel_docid on ttt(status,chnnel_id,docid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
root@localhost [xucl]>show index from ttt;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ttt   |          0 | PRIMARY                 |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| ttt   |          0 | uk_docid                |            1 | docid       | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| ttt   |          1 | idx_status_chnnel_docid |            1 | status      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| ttt   |          1 | idx_status_chnnel_docid |            2 | chnnel_id   | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| ttt   |          1 | idx_status_chnnel_docid |            3 | docid       | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)
root@localhost [xucl]>explain select * from ttt where status=1 and chnnel_id=2 and docid in (120,121,122,130);
+----+-------------+-------+------------+-------+----------------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys                    | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | ttt   | NULL       | range | uk_docid,idx_status_chnnel_docid | idx_status_chnnel_docid | 15      | NULL |    4 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#假如有如下SQL如何能够利用到该索引呢？
root@localhost [xucl]>explain select * from ttt where chnnel_id=2 and docid in (120,121,122,130);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ttt   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  103 |     4.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [xucl]>explain select * from ttt where status in (0,1) and chnnel_id=2 and docid in (120,121,122,130);
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | ttt   | NULL       | range | idx_status_chnnel_docid | idx_status_chnnel_docid | 15      | NULL |    8 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

原则5：default ‘’与default null

误区1：谓词’!=’无法用到索引

root@localhost [xucl]>show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [xucl]>select * from test where a is not null;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  2 | a    | a    |      |
|  3 | a    | a    | d    |
|  1 | a    | b    | c    |
|  4 | a    | b    | d    |
|  5 | a    | e    | d    |
|  6 | b    | e    | d    |
|  7 | e    | e    | d    |
|  8 | g    | e    | d    |
|  9 | h    | e    | d    |
| 10 | h    | i    | d    |
+----+------+------+------+
10 rows in set (0.00 sec)
root@localhost [xucl]>desc select * from test where a !='a';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_a_b_c     | idx_a_b_c | 33      | NULL |    6 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

误区2：谓词’is not null’无法用到索引

root@localhost [xucl]>desc select * from test where a is not null;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | idx_a_b_c     | idx_a_b_c | 99      | NULL |   10 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

执行计划

示例

root@localhost [xucl]>explain SELECT wcmchnldoc0_.recid AS recid1_1_, wcmchnldoc0_.chnlid AS chnlid2_1_, wcmchnldoc0_.docfirstpubtime AS docfirst3_1_, wcmchnldoc0_.docid AS docid4_1_,  wcmchnldoc0_.docpubtime AS docpubti5_1_, wcmchnldoc0_.docpuburl AS docpubur6_1_, wcmchnldoc0_.doctype AS doctype7_1_, wcmchnldoc0_.modal AS modal8_1_  FROM wcmchnldoc wcmchnldoc0_  WHHERE wcmchnldoc0_.docpuburl = 'http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml'  AND wcmchnldoc0_.doctype <> 4;
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | wcmchnldoc0_ | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 7531425 |     9.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意事项

• type：all全表扫描，最糟糕的情况；index全索引扫描，大部分情况下一样糟糕

• key_len计算规则

  • 正常地，等于索引列字节长度

  • 字符串类型需要同时考虑字符集因素

  • 若允许NULL再+1

  • 变长类型（VARCHAR），再+2

  • key_len只计算利用索引完成数据过滤时的索引长度

  • 不包括用于GROUP BY/ORDER BY的索引的长度

• extra说明:

  • Using filesort

    • 没有办法利用现有索引进行排序，需要额外排序

    • 建议：根据排序需要，创建相应合适的索引

  • Using index

    • 利用覆盖索引，无需回表即可取得结果数据

  • Using temporary

    • 需要用临时表存储结果集，通常是因为group by的列上没有索引也有可能是因为同时有group by和order by，但group by和order by的列又不一样

优化案例

案例1(hash)

root@localhost [xucl]>show create table wcmchnldoc\G
*************************** 1. row ***************************
       Table: wcmchnldoc
Create Table: CREATE TABLE `wcmchnldoc` (
  `CHNLID` int(11) NOT NULL,
  `DOCID` int(11) NOT NULL,
  `DOCORDER` int(11) NOT NULL DEFAULT '0',
  `DOCSTATUS` int(11) NOT NULL DEFAULT '0',
  `CRUSER` varchar(100) NOT NULL DEFAULT 'admin',
  `CRTIME` datetime DEFAULT NULL,
  `DOCPUBTIME` datetime DEFAULT NULL,
  `DOCPUBURL` varchar(300) DEFAULT NULL,
  `RECID` int(11) NOT NULL,
  `DOCORDERPRI` int(11) NOT NULL DEFAULT '0',
  `INVALIDTIME` datetime DEFAULT NULL,
  `OPERUSER` varchar(50) DEFAULT NULL,
  `OPERTIME` datetime DEFAULT NULL,
  `MODAL` int(11) DEFAULT '1',
  `DOCRELTIME` datetime DEFAULT NULL,
  `DOCCHANNEL` int(11) DEFAULT NULL,
  `DOCFLAG` int(11) DEFAULT NULL,
  `DOCKIND` int(11) DEFAULT '0',
  `SITEID` int(11) NOT NULL DEFAULT '0',
  `SRCSITEID` int(11) NOT NULL DEFAULT '0',
  `DOCFIRSTPUBTIME` datetime DEFAULT NULL,
  `NODEID` int(11) DEFAULT '0',
  `CRDEPT` varchar(200) DEFAULT NULL,
  `DOCOUTUPID` int(11) DEFAULT '0',
  `DOCFORM` int(11) DEFAULT '0',
  `DOCLEVEL` int(11) DEFAULT NULL,
  `attachpic` smallint(6) DEFAULT NULL,
  `POSCHNLID` int(11) DEFAULT '0',
  `ISPUSHTOPCHNL` int(2) DEFAULT '0',
  `HIDDEN` int(2) DEFAULT '0',
  `DOCTYPE` int(2) DEFAULT '0',
  `ISTIMINGPUBLISH` int(2) DEFAULT '0',
  `GDORDER` int(2) DEFAULT NULL,
  `setTopInfo` varchar(100) DEFAULT NULL,
  `OriginDocId` int(9) DEFAULT NULL,
  `ATTACHVIDEO` int(2) DEFAULT '0',
  `ATTACHAUDIO` int(2) DEFAULT '0',
  `SrcMetaDataId` int(11) DEFAULT NULL,
  `pubUser` varchar(50) DEFAULT NULL,
  `mrsFlag` int(2) DEFAULT NULL,
  `timingPubUser` varchar(50) DEFAULT NULL,
  `isTransmit` int(2) DEFAULT NULL,
  `TIMINGPUBUSERDEPT` varchar(200) DEFAULT NULL,
  `PUBUSERDEPT` varchar(200) DEFAULT NULL,
  `DocOldStatus` int(11) DEFAULT '0',
  `isZhengShen` int(2) DEFAULT '0',
  `ClientExamine` int(2) DEFAULT '0',
  `srcChannelId` int(11) DEFAULT NULL,
  PRIMARY KEY (`RECID`),
  KEY `IX_WCMCHNLDOC_CHNL_DOC` (`CHNLID`,`DOCID`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CHNL_ORDER` (`CHNLID`,`DOCORDER`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRDEPT` (`CRDEPT`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRTIMEUDSCSDF` (`CRTIME`,`CRUSER`,`DOCSTATUS`,`CHNLID`,`SITEID`,`DOCFORM`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRTIMEUDSCSDO` (`CRTIME`,`CRUSER`,`DOCSTATUS`,`CHNLID`,`SITEID`,`DOCOUTUPID`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRTIMEUDSCSM` (`CRTIME`,`CRUSER`,`DOCSTATUS`,`CHNLID`,`SITEID`,`MODAL`) USING BTREE,
  KEY `IX_WCMCHNLDOC_CRUSER` (`CRUSER`) USING BTREE,
  KEY `IX_WCMCHNLDOC_DOCFIRSTPUBTIME` (`DOCFIRSTPUBTIME`) USING BTREE,
  KEY `IX_WCMCHNLDOC_DOCTYPE` (`DOCTYPE`) USING BTREE,
  KEY `IX_WCMCHNLDOC_DOCID_MODAL` (`DOCID`,`MODAL`) USING BTREE,
  KEY `IX_WCMChnlDoc_OriginDocId_SiteId_CrTime` (`OriginDocId`,`SITEID`,`CRTIME`) USING BTREE,
  KEY `IX_WCMChnlDoc_SiteId_OriginDocId` (`SITEID`,`OriginDocId`) USING BTREE,
  KEY `IX_WCMChnlDoc_CrUserStatus` (`DOCKIND`,`CRUSER`,`DOCSTATUS`),
  KEY `IX_WCMChnlDoc_SiteIdStatus` (`DOCKIND`,`SITEID`,`DOCSTATUS`),
  KEY `IX_WCMChnlDoc_PubTimeChnlStatus` (`DOCKIND`,`DOCPUBTIME`,`CHNLID`,`DOCSTATUS`),
  KEY `IX_WCMChnlDoc_CrUserSiteStatus` (`DOCKIND`,`CRUSER`,`SITEID`,`DOCSTATUS`),
  KEY `idx_DocId_SrcMetaDataId` (`DOCID`,`SrcMetaDataId`),
  KEY `idx_SrcMetaDataId` (`SrcMetaDataId`),
  KEY `idx_doc_chl_sta_pub` (`DOCKIND`,`CHNLID`,`DOCSTATUS`,`ISTIMINGPUBLISH`,`OPERTIME`),
  KEY `idx_chnlid_docstatus_dockind_docpubtime` (`CHNLID`,`DOCSTATUS`,`DOCKIND`,`DOCPUBTIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
root@localhost [xucl]>SELECT
    -> wcmchnldoc0_.recid AS recid1_1_,
    -> wcmchnldoc0_.chnlid AS chnlid2_1_,
    -> wcmchnldoc0_.docfirstpubtime AS docfirst3_1_,
    -> wcmchnldoc0_.docid AS docid4_1_,
    -> wcmchnldoc0_.docpubtime AS docpubti5_1_,
    -> wcmchnldoc0_.docpuburl AS docpubur6_1_,
    -> wcmchnldoc0_.doctype AS doctype7_1_,
    -> wcmchnldoc0_.modal AS modal8_1_ 
    -> FROM
    -> wcmchnldoc wcmchnldoc0_ 
    -> WHERE
    -> wcmchnldoc0_.docpuburl = 'http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml' 
    -> AND wcmchnldoc0_.doctype <> 4;
+-----------+------------+---------------------+-----------+---------------------+--------------------------------------------------------------------+-------------+-----------+
| recid1_1_ | chnlid2_1_ | docfirst3_1_        | docid4_1_ | docpubti5_1_        | docpubur6_1_                                                       | doctype7_1_ | modal8_1_ |
+-----------+------------+---------------------+-----------+---------------------+--------------------------------------------------------------------+-------------+-----------+
|   8114327 |       4297 | 2018-07-21 20:49:13 |   7834159 | 2018-07-21 20:49:13 | http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml |           2 |         1 |
+-----------+------------+---------------------+-----------+---------------------+--------------------------------------------------------------------+-------------+-----------+
1 row in set (50.47 sec)
root@localhost [xucl]>explain SELECT wcmchnldoc0_.recid AS recid1_1_, wcmchnldoc0_.chnlid AS chnlid2_1_, wcmchnldoc0_.docfirstpubtime AS docfirst3_1_, wcmchnldoc0_.docid AS docid4_1_,  wcmchnldoc0_.docpubtime AS docpubti5_1_, wcmchnldoc0_.docpuburl AS docpubur6_1_, wcmchnldoc0_.doctype AS doctype7_1_, wcmchnldoc0_.modal AS modal8_1_  FROM wcmchnldoc wcmchnldoc0_  WHHERE wcmchnldoc0_.docpuburl = 'http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml'  AND wcmchnldoc0_.doctype <> 4;
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | wcmchnldoc0_ | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 7531425 |     9.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
优化1:
root@localhost [xucl]>show create table wcmchnldoc\G
*************************** 1. row ***************************
       Table: wcmchnldoc
Create Table: CREATE TABLE `wcmchnldoc` (
  `CHNLID` int(11) NOT NULL,
  `DOCID` int(11) NOT NULL,
  `DOCORDER` int(11) NOT NULL DEFAULT '0',
  `DOCSTATUS` int(11) NOT NULL DEFAULT '0',
  `CRUSER` varchar(100) NOT NULL DEFAULT 'admin',
  `CRTIME` datetime DEFAULT NULL,
  `DOCPUBTIME` datetime DEFAULT NULL,
  `DOCPUBURL` varchar(300) DEFAULT NULL,
  `RECID` int(11) NOT NULL,
  `DOCTYPE` int(2) DEFAULT '0',
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_recid` (`RECID`),
  KEY `idx_docpuburl_doctype` (`DOCPUBURL`,`DOCTYPE`)
) ENGINE=InnoDB AUTO_INCREMENT=7824427 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost [xucl]>SELECT wcmchnldoc0_.recid AS recid1_1_, wcmchnldoc0_.chnlid AS chnlid2_1_, wcmchnldoc0_.docfirstpubtime AS docfirst3_1_, wcmchnldoc0_.docid AS docid4_1_, wcmchnldoc0_.docpubtime AS docpubti5_1_, wcmchnldoc0_.docpuburl AS docpubur6_1_, wcmchnldoc0_.doctype AS doctype7_1_, wcmchnldoc0_.modal AS modal8_1_  FROM wcmchnldoc wcmchnldoc0_  WHERE wcmchnldoc0_.docpuburl = 'http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml'  AND wcmchnldoc0_.doctype <> 4;
+-----------+------------+---------------------+-----------+---------------------+--------------------------------------------------------------------+-------------+-----------+
| recid1_1_ | chnlid2_1_ | docfirst3_1_        | docid4_1_ | docpubti5_1_        | docpubur6_1_                                                       | doctype7_1_ | modal8_1_ |
+-----------+------------+---------------------+-----------+---------------------+--------------------------------------------------------------------+-------------+-----------+
|   8114327 |       4297 | 2018-07-21 20:49:13 |   7834159 | 2018-07-21 20:49:13 | http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml |           2 |         1 |
+-----------+------------+---------------------+-----------+---------------------+--------------------------------------------------------------------+-------------+-----------+
1 row in set (0.00 sec)
优化2：
增加列
docpuburl_crc32
增加索引
idx_crc32
root@localhost [xucl]>show create table wcmchnldoc\G
*************************** 1. row ***************************
       Table: wcmchnldoc
Create Table: CREATE TABLE `wcmchnldoc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `DOCPUBURL` varchar(300) DEFAULT NULL,
  `RECID` int(11) NOT NULL,
  `DOCORDERPRI` int(11) NOT NULL DEFAULT '0',
  `INVALIDTIME` datetime DEFAULT NULL,
  `OPERUSER` varchar(50) DEFAULT NULL,
  `DOCTYPE` int(2) DEFAULT '0',
  `docpuburl_crc32` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_recid` (`RECID`),
  KEY `idx_docpuburl_doctype` (`DOCPUBURL`,`DOCTYPE`),
  KEY `idx_crc32` (`docpuburl_crc32`)
) ENGINE=InnoDB AUTO_INCREMENT=7824427 DEFAULT CHARSET=utf8
root@localhost [xucl]>desc SELECT wcmchnldoc0_.recid AS recid1_1_, wcmchnldoc0_.chnlid AS chnlid2_1_, wcmchnldoc0_.docfirstpubtime AS docfirst3_1_, wcmchnldoc0_.docid AS docid4_1_, wcmmchnldoc0_.docpubtime AS docpubti5_1_, wcmchnldoc0_.docpuburl AS docpubur6_1_, wcmchnldoc0_.doctype AS doctype7_1_, wcmchnldoc0_.modal AS modal8_1_  FROM wcmchnldoc wcmchnldoc0_  WHEREE   wcmchnldoc0_.docpuburl_crc32 = 466484933 AND wcmchnldoc0_.doctype <> 4;
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | wcmchnldoc0_ | NULL       | ref  | idx_crc32     | idx_crc32 | 9       | const |    2 |    90.00 | Using where |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
问题点：
hash值重复
解决方法
root@localhost [xucl]>explain SELECT wcmchnldoc0_.recid AS recid1_1_, wcmchnldoc0_.chnlid AS chnlid2_1_, wcmchnldoc0_.docfirstpubtime AS docfirst3_1_, wcmchnldoc0_.docid AS docid4_1_,  wcmchnldoc0_.docpubtime AS docpubti5_1_, wcmchnldoc0_.docpuburl AS docpubur6_1_, wcmchnldoc0_.doctype AS doctype7_1_, wcmchnldoc0_.modal AS modal8_1_  FROM wcmchnldoc wcmchnldoc0_  WHHERE   wcmchnldoc0_.docpuburl_crc32 = 466484933 AND wcmchnldoc0_.doctype <> 4 AND wcmchnldoc0_.docpuburl = 'http://photo.zjol.com.cn/yuanchuang/201807/t20180721_7834159.shtml';
+----+-------------+--------------+------------+------+---------------------------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys                   | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------------------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | wcmchnldoc0_ | NULL       | ref  | idx_docpuburl_doctype,idx_crc32 | idx_crc32 | 9       | const |    2 |     2.50 | Using where |
+----+-------------+--------------+------------+------+---------------------------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

案例2(索引字段顺序)

MariaDB [trswcmtest]> explain select SITEID,RECID,CHNLID,DOCID,DOCORDER,DOCSTATUS,CRUSER,CRTIME,DOCPUBTIME,DOCPUBURL,DOCORDERPRI,INVALIDTIME,OPERUSER,OPERTIME,MODAL,DOCRELTIME,DOCCHANNEL,DOCFLAG,DOCKIND,'zjrb_mlf',now(),now(),'xuguozheng','xuguozheng' from wcmchnldoc where DOCPUBTIME >= date_add(STR_TO_DATE('2018-08-14', '%Y-%m-%d'), interval -14 day) and DOCPUBTIME <= STR_TO_DATE('2018-08-14', '%Y-%m-%d') and SITEID=198 and DOCSTATUS=10;
+------+-------------+------------+------+----------------------------------+----------------------------------+---------+-------+---------+-------------+
| id   | select_type | table      | type | possible_keys                    | key                              | key_len | ref   | rows    | Extra       |
+------+-------------+------------+------+----------------------------------+----------------------------------+---------+-------+---------+-------------+
|    1 | SIMPLE      | wcmchnldoc | ref  | IX_WCMChnlDoc_SiteId_OriginDocId | IX_WCMChnlDoc_SiteId_OriginDocId | 4       | const | 2362574 | Using where |
+------+-------------+------------+------+----------------------------------+----------------------------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)
....
|    198 | 8255364 |  12425 | 7966282 |    27419 |        10 | mll             | 2018-08-08 06:59:38 | 2018-08-08 07:02:26 | NULL                                                                     |           0 | NULL        | mll             | 2018-08-08 07:02:26 |     1 | 2018-08-08 06:59:38 |      12425 |    NULL |       6 | zjrb_mlf | 2018-08-14 17:52:12 | 2018-08-14 17:52:12 | xuguozheng | xuguozheng |
Ctrl-C -- query killed. Continuing normally.
+--------+---------+--------+---------+----------+-----------+-----------------+---------------------+---------------------+--------------------------------------------------------------------------+-------------+-------------+-----------------+---------------------+-------+---------------------+------------+---------+---------+----------+---------------------+---------------------+------------+------------+
10227 rows in set (1.42 sec)
 create index idx_siteid_docstatus_pubtime on wcmchnldoc(siteid,docstatus,DOCPUBTIME);
MariaDB [trswcmtest]> explain select SITEID,RECID,CHNLID,DOCID,DOCORDER,DOCSTATUS,CRUSER,CRTIME,DOCPUBTIME,DOCPUBURL,DOCORDERPRI,INVALIDTIME,OPERUSER,OPERTIME,MODAL,DOCRELTIME,DOCCHANNEL,DOCFLAG,DOCKIND,'zjrb_mlf',now(),now(),'xuguozheng','xuguozheng' from wcmchnldoc where DOCPUBTIME >= date_add(STR_TO_DATE('2018-08-14', '%Y-%m-%d'), interval -14 day) and DOCPUBTIME <= STR_TO_DATE('2018-08-14', '%Y-%m-%d') and SITEID=198 and DOCSTATUS=10;
+------+-------------+------------+-------+---------------------------------------------------------------+------------------------------+---------+------+-------+-----------------------+
| id   | select_type | table      | type  | possible_keys                                                 | key                          | key_len | ref  | rows  | Extra                 |
+------+-------------+------------+-------+---------------------------------------------------------------+------------------------------+---------+------+-------+-----------------------+
|    1 | SIMPLE      | wcmchnldoc | range | IX_WCMChnlDoc_SiteId_OriginDocId,idx_siteid_docstatus_pubtime | idx_siteid_docstatus_pubtime | 14      | NULL | 20312 | Using index condition |
+------+-------------+------------+-------+---------------------------------------------------------------+------------------------------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
...
        | 2018-08-07 11:01:23 |     1 | 2018-08-03 07:41:44 |      11494 |    NULL |       6 | zjrb_mlf | 2018-08-14 18:02:01 | 2018-08-14 18:02:01 | xuguozheng | xuguozheng |
Ctrl-C -- query killed. Continuing normally.
+--------+---------+--------+---------+----------+-----------+-----------------+---------------------+---------------------+--------------------------------------------------------------------------+-------------+-------------+-----------------+---------------------+-------+---------------------+------------+---------+---------+----------+---------------------+---------------------+------------+------------+
10227 rows in set (0.05 sec)