1.引言:
上一篇博客主要学习了innodb搜索引擎的结构和innodb内的索引结构,这一次和大家一起学习一下在实际操作sql时候该如何最优的索引。
2.创建:
我们都知道索引可以加快查询速度,所以具体如何建索引呢?
2.1建表时添加索引
CREATE TABLE t_user1(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX (userName) #关键字INDEX
);
建表同时建立唯一索引(可以是单或多)
CREATE TABLE t_user2(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
UNIQUE INDEX index_userName(userName) #关键字UNIQUE和INDEX
);
建表同时建立联合索引
CREATE TABLE t_user3(id INT ,
userName VARCHAR(20),
PASSWORD VARCHAR(20),
INDEX index_userName_password(userName,PASSWORD)
);
2.2给已存在表添加索引
单列索引
CREATE INDEX index_userName ON t_user(userName);
唯一索引
CREATE UNIQUE INDEX index_userName ON t_user(userName);
联合索引
CREATE INDEX index_userName_password ON t_user(userName,PASSWORD);
另一种写法与上面相似 ,但是有区别:
单列索引
ALTER TABLE t_user ADD INDEX index_userName(userName);
唯一索引
ALTER TABLE t_user ADD UNIQUE INDEX index_userName(userName);
联合索引
ALTER TABLE t_user ADD INDEX index_userName_password(userName,PASSWORD);
两种区别:
1、CREATE INDEX必须提供索引名,对于ALTER TABLE,将会自动创建,如果你不提供;
2、CREATE INDEX一个语句一次只能建立一个索引,ALTER TABLE可以在一个语句建立多个,如:
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有ALTER TABLE 才能创建主键,ADD INDEX 不能;
#####*2.3删除索引*
DROP INDEX index_userName ON t_user;
DROP INDEX index_userName_password ON t_user;
3.实践:
为了贴近实际,我拿工作中实际应用到的一张运单表,主要记录了订单生成之后,末端物流的相关信息,本地有84467条记录,有兴趣的可以试试。
/*
Target Server Type : MYSQL
Target Server Version : 50625
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_waybill
-- ----------------------------
DROP TABLE IF EXISTS `t_waybill`;
CREATE TABLE `t_waybill` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`BATCH_NO` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`CONT_CELL_PH` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`CONT_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`CREATE_DATE` datetime NOT NULL,
`DELIVERY_ADDRESS` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`DELIVERY_MEMO` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`DISPATCH_DATE` date DEFAULT NULL,
`IN_ORDER_NO` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`LATITUDE` double DEFAULT NULL,
`LONGITUDE` double DEFAULT NULL,
`MOD_DATE` datetime DEFAULT NULL,
`ORDER_NO` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`OTHER_REASON` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SITE_AREA_CODE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SITE_CODE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_NO` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`WB_NO` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`WB_STATE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`WB_TYPE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`FEE_STATE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`OF_PAY_AMONT` double DEFAULT NULL,
`ORDER_TYPE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`PAYMENT_TYPE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`UN_PAYMENT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`REFUND` double DEFAULT NULL,
`DM_ID` bigint(20) DEFAULT NULL,
`DM_MOBILE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`DM_NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SYNC_STATUS` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`ACCEPT_TIME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`DISPATCH_PERIOD` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`DELIVERY_TIME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`PACKAGE_NUM` int(11) DEFAULT NULL,
`ORDER_AMT` double DEFAULT NULL,
`ORDER_CHANNEL` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`ORDER_DATE` varchar(22) COLLATE utf8_unicode_ci DEFAULT NULL,
`INVOICE_TYPE` int(11) DEFAULT NULL,
`RECEIVE_TYPE` int(11) DEFAULT NULL,
`BUYER` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`BUYER_MOBILE` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`CITY` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`DC_FLAG` int(11) DEFAULT NULL,
`PROVINCE` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`REGION` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`ZL_FLAG` int(11) DEFAULT NULL,
`PROD_NUM` int(11) DEFAULT NULL,
`TC_FLAG` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `WB_NO` (`WB_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=2005963 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ok,那么我们现在有了操作的目标表,这个表在建好之后没有任何的索引,但是这里面的WB_NO字段是运单号,这个字段是唯一的,根据索引的建立规则,我们应该尽量在重复比例小的字段上建立索引。
1.在WB_NO上建立索引:
ALTER TABLE t_waybill ADD INDEX wb_no(WB_NO);
time:2.8s(所以说不考虑性能情况下,本地8w多条数据,建立一个单条索引需要2.8s时间,可见在几亿的表中建立索引会花费(阻塞)多久的时间)
对比(查一条记录):
SELECT * FROM t_waybill WHERE WB_NO=’151029001403’;
建立前:0.241s
建立后:0.003s
提升80倍左右
2.在DISPATCH_DATE(配送日期,它不是唯一的,一天会有很多的订单重复率较高)上建立索引:
ALTER TABLE t_waybill ADD INDEX DISPATCH_DATE(DISPATCH_DATE);
SELECT * FROM t_waybill WHERE DISPATCH_DATE=’2016-05-13’ AND WB_NO=’151029001403’;//为了确保唯一,我们加上WB_NO
建立前:0.230s
建立后:0.030s
提升7.6倍
3.在WB_NO和DISPATCH_DATE上建立联合索引:
ALTER TABLE t_waybill ADD INDEX wb_no_DISPATCH_DATE(DISPATCH_DATE,WB_NO);
SELECT * FROM t_waybill WHERE DISPATCH_DATE=’2016-06-20’ AND WB_NO=’160620012380’;
建立前:0.230s
建立后:0.003s
提升76倍
联合索引涉及到最左前缀原则,就是如果没有使用到DISPATCH_DATE这个字段的话,不会走索引的,因为他是最左边的。
4.在WB_NO和DISPATCH_DATE上建立多列索引
ALTER TABLE t_waybill ADD INDEX wb_no(WB_NO);
ALTER TABLE t_waybill ADD INDEX DISPATCH_DATE(DISPATCH_DATE);
多列索引通常只会用到一个,mysql会选举出最优的索引;
SELECT * FROM t_waybill WHERE DISPATCH_DATE=’2016-06-20’ AND WB_NO=’160620012380’;
建立前:0.230s
建立后:0.003s
提升76倍
explain发现只是用了wb_no索引,但是也很快,因为索引直接找到值了。
4.总结:
我们看到了索引合理的索引带来的巨大的查询性能的提升,那么是否我们对所有的查询条件的都建立索引呢?建立索引之后查询就会变快么?
从上面的例子我们可以看到,维护索引需要一定的时间和空间,那么要衡量建立索引带来的查询性能是否值得,如何数据量很少的表,不建议建立索引,频繁增删改的字段也不建议建立索引,还有字段本身很大的和重复率高的的字段,这些情况都是索引带来的好处小于他的成本,在实际应用中应该评估一下,哪些字段是十分常用的,并且表数据很大,那么请建立索引吧,会有大幅度的提升的。
有很多时候我的慢查询都来自索引的浪费,比如实际建立了索引但是索引没有起作用,有以下情况索引都不会起作用。
1.使用or来取并集,除非是每个字段都建立索引,否则只要有一个不建立,就不会使用索引
2.使用模糊查询like,以%开头(好像是bug),以%结尾可以
3.联合索引没有遵循最左前缀原则
4.如果索引的结果即很大,比如占总数量的百分之几十以上,不会使用索引,会全表扫面,这个由mysql来决定是否使用索引,可以使用limit来限制查询数量,这样可以走索引
5.where子句中索引字段包含函数运算,比如where id=id+1;
6.where子句中索引字段不要使用!=、not in()、is null
注:如果同时有联合索引,并且联合索引的每个字段都是单独列索引,那么使用时候具体走哪个索引有mysql来选出最优的。