浅谈数据库索引

1.什么是索引

​ 数据库索引就像是一本书的目录，可以加快数据库的查询速度。

​ 举个例子：有这样一条查询语句：select * from t1 where id = 10; 如果没有索引，检索过程必须遍历整个表，直到找到id为44的这一行；而如果id这一列建立了索引，直接在索引列（id这一列）找，就可以找到这一行的位置。索引起到定位作用，大大加快查询速度。

2.为什么要创建索引？（索引的优点）

• 可以加快数据查询效率，这也是最主要的原因
• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以加速表和表之间的连接
• 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
• 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能

那既然索引有这么多优点，为什么我们不在每个数据库字段都创建索引，来加快查询效率呢？

3.索引的弊端：

• 空间上：数据库索引的创建存储也会占用一定的物理空间
• 时间上：创建索引由于数据库表的复杂程度不同需要耗费一定的时间，而且在对数据进行增删改的时候，索引也要相应的维护，降低了维护效率。（创建索引可以加快查询效率，但会隐藏增删改的效率）

4.选择哪些字段创建索引？

• 经常会被搜索的列，可以加快搜索速度
• 经常跟在where，order by,group by,distinct条件后面的列，可以加快条件筛选速度
• 经常需要排序的列，索引本身已经排序，这样查询的时候会利用索引的排序，加快排序查询速度
• 经常用于连接的列，主要是一些外键，可以加快连接速度
• 作为主键的列，强制该列的唯一性和组织表中数据的排列结构

5.不应该选择哪些字段创建索引？

• 查询使用较少增删改较多的字段，不应该创建索引，因为所有可以加快查询效率但会影响增删改的效率
• 数据类型为text，image，bit的字段不应该增加索引。这是因为这些列的数据量要么很大，要么很小，不适合增加索引。
• 数据量很少的列不适合增加索引。比如性别字段，即使通过索引定位，也只有两种结果，并不能很大加快查询速度，反而会占用很多空间，影响修改效率。

6.使用索引的注意事项

1.索引不会包含含有null值的列
	只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
	对串列进行索引，如果可能应该指定一个前缀长度。例如，有一个CHAR（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
	MySql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
	一般情况下不鼓励使用like操作，如果非使用不可，要注意like“%aaa%”和like“%aaa”不会使用索引，而like“aaa%”可以使用索引。
5.不要在列上进行计算
	select * from user where YEAR(adddate)<2019;
	将在每个列上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
	select * from users where adddate<'2019-01-01';
6.不要使用NOT IN和<>操作

7.MySQL索引的应用

​ 索引是快速搜索的关键。MySQL索引的建立对于MySQL的高校运行是很重要的。下面介绍几种常见的MySQL索引类型。

​ 在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个mytable表：

​ create table mytable(id int not null,username varchar(16) not null);

​ 我们随机向里面插入10000条记录，其中有一条：5555，admin

​ 在查找username=‘admin’的记录 select * from mytable where username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，查询10000条记录。

​ 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。组合索引，一个索引包含多个列。

MySQL索引类型包括：
1.普通索引
	这是最基本的索引，它没有任何限制。它有以下几种创建方式：
	1）创建索引
	create index indexName on mytable(username(length));
	如果是char，varchar类型，length可以小于实际长度；如果是blob和text类型，必须指定length，下同。
	2）修改表结构
	alter mytable add index[indexName] on(username(length))
	3）创建表的时候直接指定
	create table mytable(id int not null,username varchar(16) not null,index[indexName](username(length)));
	4）删除索引
	drop index[indexName] on mytable;
2.唯一索引
	它与前面的普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
	1）创建索引
	create unique index indexName on mytable(username(length))
	2)修改表结构
	alter mytable add unique[indexName] on (username(length))
	3)创建表的时候直接指定
	create table mytable(id int not null,username varchar(16) not null,unique[indexName](username(length)))
3.主键索引
	它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：
	create table mytale(id int not null,username varchar(16) not null,primary key(id));
4.组合索引
	为了形象地对比单列索引和组合索引，为表添加多个字段：
	create table mytable(id int not null,username varchar(16) not null,city varchar(50) not null,age int not null);
	为了进一步榨取mysql的效率，就要考虑建立组合索引。就是将username，city，age建到一个索引里。
	alter table mytable add index name_city_age(username(10),city,age);
	建表时，username长度为16，这里用10.这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高insert的更新速度。
	如果分别在username，city，age上建立单列索引，让该表有3个单列索引，查询时和上述组合索引效率也会大不一样，远远低于我们的组合索引。虽热此时有了三个索引，但MySQL只能用其中的那个它认为最有效率的单列索引。