索引

1、定义

        在关系数据库中，索引是对表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列的值的集合，而且其中包含了对应表中记录的引用指针。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

       要注意的是，索引也是表的组成部分，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立索引。 

        Mysql索引分为单列索引（主键索引、唯一索引、全文索引、普通索引），组合索引。

 

2、索引的创建

1）主键索引， 根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

create table a(
    id int primary key auto_increment,
    name varchar(20) not null default ''
);
//这里id就是表的主键
alter table table_name add primary key (column name);

2）普通索引， 用表中的普通列构建的索引，没有任何限制

create index 索引名 on table_name(column1,column2)；

3）唯一索引， 用来建立索引的列的值必须是唯一的，允许空值

create table d(id int primary key auto_increment , name varchar(32) unique);
ALTER TABLE 'table_name' ADD UNIQUE index_name('col');

4）全文索引， 用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5）组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值。

• 遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引 相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。
• 在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引。

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')； 

3、组合索引和单列索引选择。

    现在新建了一个组合索引（a,b,c），只有当查询条件中出现了a，索引才会生效。那这样为什么不建立一个单索引a呢？因为如果有组合索引的话，mysql可以从索引中取到要查询的数据。比如现在有一个查询语句select * from table where a=1 and b=1 and c=1，如果现在为a字段建立一个单索引，那么查询的时候就把范围限制在a=1 上，之后扫描满足b=1的结果，最后扫描满足c=1的结果，即最终结果，这中间查询的列数就是满足a=1条件的列数。      如果建立的是一个组合索引(a,b,c)，那么查询的时候就直接将结果限制在满足a=1,b=1,c=1上，查询的列数即最终结果的列数。

 

4、建立索引的时机

   什么时候要使用索引？

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

  什么时候不要使用索引？

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引；
• 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

 

5、索引的不足之处

    上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

   1） 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

   2） 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

 

转载于:https://www.cnblogs.com/sxhjoker/p/11392424.html