一、首先介绍下什么是索引、索引的作用及原理是什么?
索引:索引在MySQL中也叫作“键”,是存储引擎用于快速找到记录的一种数据结构。
索引的数据结构:B+Tree
索引的作用:MySQL索引的建立对于MySQL的高效运行是很重要的,尤其是当表中的数据量越来越大时,索引可以大大提高MySQL的检索速度。
索引的原理:实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录;
创建索引后,以文件形式存储在磁盘上,索引检索需要磁盘I/O操作。
二、MySQL的索引分类
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
普通索引:最基本的索引,它没有任何限制;
唯一索引:它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
联合索引:将几个列作为一条索引进行检索,使用最左匹配原则。
全文索引:全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表,也称为全文检索;
索引的创建及添加:
普通索引创建索引、添加索引、删除索引:
CREATE INDEX indexName ON mytable(username(length));
ALTER table tableName ADD INDEX indexName(columnName);
DROP INDEX [indexName] ON mytable;
唯一索引创建索引、添加索引:
CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;
ALTER table mytable ADD UNIQUE [indexName] (username(length));
创建表时指定索引,实例:
CREATE TABLE `pay_check_file_detail` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
`jrn_no` varchar(64) DEFAULT NULL COMMENT '批次号',
`merc_id` varchar(32) NOT NULL COMMENT '商户id',
`merc_ord_no` varchar(64) DEFAULT NULL COMMENT '商户订单号',
`trans_date` varchar(8) NOT NULL COMMENT '交易日',
`ord_no` varchar(32) NOT NULL COMMENT '支付平台订单号',
`trans_type` varchar(10) NOT NULL COMMENT '业务类型 1 申购 2 认购 3 定投',
`usr_name` varchar(128) NOT NULL COMMENT '用户姓名',
`id_type` varchar(2) NOT NULL COMMENT '证件类型',
`id_no` varchar(128) NOT NULL COMMENT '身份证',
`contract_id` varchar(32) DEFAULT NULL COMMENT '协议号',
`create_time` datetime NOT NULL COMMENT '入库时间',
`ord_sts` varchar(2) NOT NULL COMMENT '订单状态',
`trans_remark` varchar(128) DEFAULT NULL COMMENT '交易备注',
`is_confirm` varchar(1) DEFAULT NULL COMMENT '1',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`tx_amt` decimal(18,2) DEFAULT NULL COMMENT '交易金额',
PRIMARY KEY (`id`), ---唯一索引
UNIQUE KEY `uk_ord_no` (`jrn_no`,`ord_no`), ---唯一联合索引,数据唯一性,加快查询速度;
KEY `statement_info_ni1` (`merc_ord_no`,`merc_id`), ---普通联合索引,数据可以重复,加快查询速度;
KEY `statement_info_ni2` (`create_time`), ---普通索引;
KEY `statement_info_ni3` (`trans_date`,`merc_id`),
KEY `statement_info_ni4` (`jrn_no`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='表名';
三、索引优缺点
优点:
1.通过创建唯一性的索引,可以保证表中每一行数据的唯一性;
2.可以大大加快表中数据的检索素的,这也是创建索引的主要原因;
3.可以加快表与表之间的链接,特别是在实现表与表之间的参考完整性实现有特别的意义;
4.通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统性能。
缺点:
1.创建索引和维护索引耗时,时间随着数据的增加而增加,成正比;
2.索引需要占物理空间,除了数据表占数据空间外,每一个索引还要占一定的物理空间,如果建立聚簇索引,占得物理空间会更大;
3.当对表中的数据进行维护时,对索引也要进行维护,这样就降低了数据的维护速度。
索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
四、测试要点
分析代码中的sql语句:
1. 检查已创建的索引是否合理或有必要,例如:
a.对于在查询过程中很少使用或参考的列,不应该创建索引;
b.对于那些只有很少数据值的列,不应该创建索引;
c.使用其作为查询条件的概率很小,不应搞创建索引;
2. 检查必要的索引是否创建,是否会导致查询慢SQL的问题,例如:
a.需要保证数据唯一性的索引;
b.数据量较大,使用某个或某几个字段进行查询的频率较高;
3. 检查索引的数量。
PS:测试过程中需要增加对索引的关注,索引是提高系统性能的关键手段之一,希望引起大家的重视。