数据库性能优化之索引

什么是索引

      简单来说，索引会对表中一列或者多列的值进行排序，使用索引能快速地查找出某列或某几列中特定值的记录。不使用索引，MySQL默认从第一条记录开始逐条读表，当数据量比较庞大时花的时间就会变长。但，为查询的列加上索引，MySQL能快速地定位一个位置去读取数据，而不是从第一条记录开始逐条读表。

表结构如下：

DROP TABLE IF EXISTS `tbl_order_tax`;

CREATE TABLE `tbl_order_tax` (

  `order_tax_id` varchar(60) NOT NULL COMMENT '车船税id',

  `order_id` varchar(60) DEFAULT NULL COMMENT '订单唯一号',

  `customername` varchar(300) DEFAULT NULL COMMENT '客户名称',

  `customertype` varchar(60) DEFAULT NULL COMMENT '客户类型',

  `certificatetype` varchar(60) DEFAULT NULL COMMENT '证件类型',

  `certificatecode` varchar(60) DEFAULT NULL COMMENT '证件号码',

  `taxpayerid` varchar(60) DEFAULT NULL COMMENT '纳税人识别号',

  `telno` varchar(60) DEFAULT NULL COMMENT '联系电话',

  `address` varchar(300) DEFAULT NULL COMMENT '联系地址',

  `zipcode` varchar(60) DEFAULT NULL COMMENT '邮编',

  `nationality` varchar(60) DEFAULT NULL COMMENT '国籍',

  `homeaddress` varchar(300) DEFAULT NULL COMMENT '居住地址',

  `pccode` varchar(60) DEFAULT NULL COMMENT '微机代码',

  `ussage` varchar(60) DEFAULT NULL COMMENT '车辆使用性质',

  `vehicletype` varchar(60) DEFAULT NULL COMMENT '车辆类型',

  `taxvehicletype` varchar(60) DEFAULT NULL COMMENT '车辆类型-税务',

  `licencetype` varchar(60) DEFAULT NULL COMMENT '牌照类型',

  `licencecolor` varchar(60) DEFAULT NULL COMMENT '车牌颜色',

  `licenceno` varchar(60) DEFAULT NULL COMMENT '牌照号码',

  `licencedate` date DEFAULT NULL COMMENT '行驶证领证日期',

  `oldlicenceno` varchar(60) DEFAULT NULL COMMENT '旧牌照号码',

  `engineno` varchar(60) DEFAULT NULL COMMENT '发动机号',

  `frameno` varchar(60) DEFAULT NULL COMMENT '车架号',

  `enginecapacity` decimal(16,0) DEFAULT NULL COMMENT '排量',

  `seatcount` int(10) DEFAULT NULL COMMENT '乘人汽车座次',

  `emptyweight` decimal(16,0) DEFAULT NULL COMMENT '整备质量',

  `carryingcapacity` decimal(16,0) DEFAULT NULL COMMENT '核定载质量',

  `totalweight` decimal(16,0) DEFAULT NULL COMMENT '总质量(公斤)',

  `registdate` datetime DEFAULT NULL COMMENT '车辆登记日期',

  `settledaddress` varchar(300) DEFAULT NULL COMMENT '车辆落户地址',

  `changenativedate` datetime DEFAULT NULL COMMENT '转籍日期',

  `policyno` varchar(60) DEFAULT NULL COMMENT '保单号',

  `endorsementno` varchar(60) DEFAULT NULL COMMENT '批单号',

  `policystartdate` datetime DEFAULT NULL COMMENT '交强险起始日期',

  `policyenddate` datetime DEFAULT NULL COMMENT '交强险截至日期',

  `taxtype` varchar(60) DEFAULT NULL COMMENT '缴税状态',

  `exemptionno` varchar(60) DEFAULT NULL COMMENT '完税凭证号',

  `taxtickettype` varchar(60) DEFAULT NULL COMMENT '税票类型',

  `taxticketno` varchar(60) DEFAULT NULL COMMENT '税票号码',

  `amount` double(16,2) DEFAULT NULL COMMENT '当年应缴',

  `backamount` double(16,2) DEFAULT NULL COMMENT '往年补缴',

  `latefee` double(16,2) DEFAULT NULL COMMENT '滞纳金',

  `derateamount` double(16,2) DEFAULT NULL COMMENT '减免税额',

  `totalamount` double(16,2) DEFAULT NULL COMMENT '总计金额',

  `derateno` varchar(60) DEFAULT NULL COMMENT '减免税证明号',

  `taxbureau` varchar(60) DEFAULT NULL COMMENT '减免税证明开具税务机关',

  `backstartdate` datetime DEFAULT NULL COMMENT '往年补缴起始日期',

  `backenddate` datetime DEFAULT NULL COMMENT '往年补缴截至日期',

  `latefeestartdate` datetime DEFAULT NULL COMMENT '滞纳金起始日期',

  `latefeeenddate` datetime DEFAULT NULL COMMENT '滞纳金截至日期',

  `taxstartdate` datetime DEFAULT NULL COMMENT '车船税起始日期',

  `taxenddate` datetime DEFAULT NULL COMMENT '车船税截至日期',

  `taxterm` varchar(60) DEFAULT NULL COMMENT '税款所属期',

  `remark` varchar(60) DEFAULT NULL COMMENT '备注',

  `balanceamount` double(16,2) DEFAULT NULL COMMENT '补差金额',

  `carryingcapacitytmp` int(60) DEFAULT NULL COMMENT '临时字段',

  `emptyweighttmp` int(60) DEFAULT NULL COMMENT '临时字段',

  `totalweighttmp` int(60) DEFAULT NULL COMMENT '临时字段',

  `taxdescription` varchar(60) DEFAULT NULL COMMENT '附加信息',

  `taxbureauname` varchar(300) DEFAULT NULL COMMENT '完税开具税务机关名称',

  `vehiclestyle` varchar(60) DEFAULT NULL COMMENT '交管车辆类型代码',

  `taxchangereasoncode` varchar(60) DEFAULT NULL COMMENT '车船税与询价不同的原因代码',

  `taxchangerensondesc` varchar(300) DEFAULT NULL COMMENT '船税与询价不同的原因描述',

  `taxamendpremium` decimal(16,2) DEFAULT NULL COMMENT '批改车船税合计',

  `taxactualamendpremium` decimal(16,2) DEFAULT NULL COMMENT '车船税实际退补费',

  `taxcancelpremium` decimal(16,2) DEFAULT NULL COMMENT '退车船税合计',

  `taxactualcancelpremium` decimal(16,2) DEFAULT NULL COMMENT '车船税实际退税金额',

  `amountchange` decimal(16,2) DEFAULT NULL COMMENT '当年应缴改变值',

  `backamountchange` decimal(16,2) DEFAULT NULL COMMENT '往年补缴改变值',

  `latefeechange` decimal(16,2) DEFAULT NULL COMMENT '滞纳金改变值',

  `totalamountchange` decimal(16,2) DEFAULT NULL COMMENT '总金额改变值',

  `registrynumber` varchar(60) DEFAULT NULL COMMENT '税务登记号',

  `startdate` datetime DEFAULT NULL COMMENT '起保时间',

  `effectdate` datetime DEFAULT NULL COMMENT '批单生效时间',

  `confirmsequenceno` varchar(60) DEFAULT NULL COMMENT '投保确认号',

  `amendconfirmno` varchar(60) DEFAULT NULL COMMENT '批改确认号',

  `declaredate` datetime DEFAULT NULL COMMENT '申报日期',

  `deductionduecode` varchar(60) DEFAULT NULL COMMENT '减免税原因代码',

  `deductionduetype` varchar(60) DEFAULT NULL COMMENT '减免税方案代码',

  `deductiondueproportion` decimal(16,0) DEFAULT NULL COMMENT '减免比例',

  `unitrate` decimal(16,0) DEFAULT NULL COMMENT '单位税额',

  `taxunittypecode` decimal(16,0) DEFAULT NULL COMMENT '课税数量',

  `createDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

  `updateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当不建索引的情况下，执行查询语句SELECT * FROM tbl_order_traffic where order_id = '1002016051100000000002101'; 耗时295ms

添加索引后执行 EXPLAINSELECT * FROM tbl_order_traffic where order_id = '1002016051100000000002101'; 耗时如下图

显然查询时间提高了7倍之多。

在性能分析时使用EXPLAIN语句可以分析select的查询过程 。

select_type=SIMPLE表示单表查询；

type=cons表示通过索引一次就找到了相关记录，type=all表示全表扫描；

key=primary，idx_ordertax_orderid表示使用了主键和idx_ordertax_orderid索引。

什么情况下使用索引

索引一般使用在表中存在大量数据，但要查询的数据在表中分布较少。比如说性别就没必要建索引，因为性别只有两个取值男和女（数据重复量比较大）。

不适合建索引的情况

在索引列上使用MySQL函数也会导致索引失效一般范围查询、模糊查询和JOIN查询都不使用索引。索引列上使用函数会导致索引失效。