mysql索引总结

1.索引的概念:

 它是一个单独的,存储在磁盘上的数据结构,它们包含着对数据库表里所有记录的引用指针,主要用于快速找出一列或者多列中有特定值的行,所有mysql列类型都可以被索引。

2.索引的存储类型:

索引是在存储引擎中实现的,mysql中索引的存储类型有俩种:BTREE和HASH,具体和表的存储引擎相关,MyISAM和InnoDB存储引擎只支持BTREE索引,MEMORY/HEAP存储引擎可以存储BTREE和HASH俩种索引。

3.索引的分类:

(1) 普通索引和唯一索引:普通索引可以在索引的列中插入重复的值和空值，唯一索允许有空值,主键索引不能为空。

(2) 单列索引和组合索引:一个表可以多个单列索引,组合索引指在表的多个字段组合上创建的索引,只有在查询时使用了这些字段的左边字段时,索引才能被使用

(3) 全文索引:在定义索引的列上支持值的全文查找,允许索引的列有重复值和空值,只能在列类型为char,varchar,text类型的列上建立,只有MyISAM存储引擎支持全文索引。

(4) 空间索引

4.索引的设计原则:

(1) 索引并不是越多越好,不仅占用磁盘空间,对增加,修改,删除性能也有影响,表的修改会导致索引的动态变化。

(2) 避免对经常更新的表使用过多的索引,经常查询的字段应该建立索引,但要避免添加不必要的字段。

(3) 数据量小的表最好不要使用索引,反而会降低查询速度。

(4) 在条件表达式中经常用到的不同值较多的列上建立索引,少的不要建立比如性别列男女俩项没有必要建立索引。

(5) 在频繁进行分组和排序的列上建立索引,如果待排序的字段有很多,可以在这些列上建立组合索引。

5.索引的创建:

(1)创建表的时候创建:

CREATE TABLE `tr_log_type` (

   `id` varchar(32) NOT NULL COMMENT '主键',

   `log_id` varchar(32) DEFAULT NULL COMMENT '日志id',

   `vehicle_id` varchar(32) DEFAULT NULL COMMENT '车辆id',

   PRIMARY KEY (`id`),

   INDEX(log_id) 普通索引,

   UNIQUE INDEX uni(log_id) 唯一索引,

   INDEX mindex(log_id,vehicle_id) 复合索引,

   FULLTEXT INDEX findex(log_id) 全文索引

)

(2) 表已经存在新添加索引:

第一种:alter table tr_log_type add index logIdIndex(log_id);

第二种:create index logIdIndex on tr_log_type(log_id);

6.索引的删除:

第一种:alter table tr_log_type drop index logIdIndex;

第二种:drop index logIdIndex on tr_log_type;

7.查看索引:

 show index from tr_log_type;

8.索引的优点:

(1) 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。

(2) 可以大大提高数据的查询速度

(3) 在实现数据的参考完整性方面,可以加速表与表之间的连接

(4) 在使用分组和排序子句进行查询时,也可以显著减少查询中的分组和排序的时间

9.索引的缺点:

(1) 创建索引和维护索引要花费大量的时间,数据量越大花费的时间越多。

(2) 索引会占用磁盘空间。

(3) 当对表数据进行增加,修改,删除,索引也要动态的维护,降低了数据的维护速度。

10.索引的创建位置:
索引一般建立在where和join字句以及order by 的字段上面

11.注意事项:

(1)使用全文索引注意事项:mysql默认的存储引擎是InnoDB,如果想使用全文索引必须将存储引擎修改为MyISAM

(2)使用组合索引注意事项:它遵从最左前缀原则,利用索引中最左边的列集匹配行,如果我们的组合索引为有id,name,age组成,

mysql能使用索引的组合为id,name,age或则id,name或则id

(3)索引失效:like以%开头