1.什么是索引?为什么要用索引?
1.1索引的含义
数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据.索引的实现通常使用B树和变种的B+树(mysql常用的索引就是B+树)。除了数据之外,数据库系统还维护为满足特定查找算法的数据结构,这些数据结构以某种方式引用数据.这种数据结构就是索引!
简言之,索引就类似于书本,字典的目录!
1.2为什么用?
打个比方,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。
一言以蔽之,合理使用索引,可以加快数据库的查询效率和提升程序性能!
2.索引的作用与缺点
2.1作用
①通过创建索引,可以在查询的过程中,提高系统的性能
②通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
③在使用分组和排序子句进行数据检索时,可以减少查询中分组和排序的时间
2.2缺点
①创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
②索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
③在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护
3.索引的使用场景
3.1应创建索引的场景
①经常需要搜索的列上
②作为主键的列上
③经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
④经常需要根据范围进行搜索的列上
⑤经常需要排序的列上
⑥经常使用在where子句上面的列上
3.2不应创建索引的场景
①查询中很少用到的列
②对于那些具有很少数据值的列.比如数据表中的性别列,bit数据类型的列
③对于那些定义为text,image的列.因为这些列的数据量相当大
④当对修改性能的要求远远大于搜索性能时.因为当增加索引时,会提高搜索性能,但是会降低修改性能
4.索引的分类与说明
4.1主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引
#随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
#使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer2 (id INT(10) UNSIGNED,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id)
);
#单独建主键索引:
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
#删除建主键索引:
ALTER TABLE customer drop PRIMARY KEY ;
#修改建主键索引:
#必须先删除掉(drop)原索引,再新建(add)索引
4.2单列索引
一个索引只包含单个列,一个表可以有多个单列索引
#随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
#随表一起建立的索引 索引名同 列名(customer_name)
#单独建单值索引:
CREATE INDEX idx_customer_name ON customer(customer_name);
#删除索引:
DROP INDEX idx_customer_name ;