MySql索引基础内容及测试要点

一、首先介绍下什么是索引、索引的作用及原理是什么？

索引：索引在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：测试过程中需要增加对索引的关注，索引是提高系统性能的关键手段之一，希望引起大家的重视。