数据库学习之让索引加快查询速度(四)

数据库学习之让索引加快查询速度

目录

索引简介

MySQL的索引分类

创建索引

添加与删除索引

 

索引简介

索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是查询优化最有效的手段了。
索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引特点：创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高。
索引的功能就是加速查找。
mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能。

MySQL的索引分类

索引分类
1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引（多列索引）
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

　　

创建索引

 创建索引的语法

    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );

　　

 创建表时创建索引

创建 普通索引示例：
CREATE TABLE emp1(
	id INT,
	name VARCHAR(20),
	resume VARCHAR(50),
	INDEX index_emp_name (name)
);




创建唯一索引示例：
CREATE TABLE emp2(
	id INT,
	name VARCHAR(30),
	bank_num CHAR(10),
	resume VARCHAR(50),
	UNIOUE INDEX index_emp_name (name)
);



创建 全文索引示例：
CREATE TABLE emp3(
	id INT,
	name VARCHAR(30),
	resume VARCHAR(50),
	FULLTEXT INDEX index_name_resume (resume)
);


创建多列索引示例：
CREATE TABLE emp4(
	id INT,
	name VARCHAR(30),
	resume VARCHAR(50),
	INDEX index_name_resume (name,resume)
);

　　

添加与删除索引

添加索引的语法

CREATE 在已存在的表上创建索引
CREATE [UNIOUE | FULLTEXT  |  SPATIAL]  INDDX  索引名 ON 表名 [字段名(长度)]  [ASC | DESC];


ALTER TABLE 在已存在的表上创建索引
ALTER TABLE 表名  ADD [UNIOUE | FULLTEXT  |  SPATIAL]  INDDX 索引名 [字段名(长度)]  [ASC | DESC];

　　

添加索引的例子

CREATE INDEX index_emp_name on emp1(name); #创建普通索引
ALTER TABLE emp2 ADD UNIOUE INDEX index_emp_name(name) #创建唯一索引
alter table emp2 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
create index index_emp_name on emp5(id,name); #添加普通联合索引

　　

删除索引语法

语法：DROP INDEX 索引名 on 表名;

　　

删除索引的例子

DROP INDEX index_emp_name on emp1; #删除普通索引
DROP INDEX index_emp_name on emp2; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

　　

举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

#除此之外还有全文索引，即FULLTEXT
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

#其他的如空间索引SPATIAL，了解即可，几乎不用

　　

索引基础总结

索引是什么：Mysql官方定义：索引是帮助Mysql高效获取数据的数据结构，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引，索引的实现通常使用B树及其变种B+树。

我们平常所说的索引，如果没有特别指明，都是指B树（平衡树」(非二叉)）结构组织的索引。
其中聚焦复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。
当然，除来B树这种类型的索引之外，还有哈希索引（hash index）等

优点
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，降低了数据库的IO成本，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引实际上是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以列也是要占用物理空间，除此之外，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，当对表中的数据进行增加、删除和修改的时候，，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

索引可以建立在数据库表中哪些列的上面？
1.在经常需要搜索的列上，可以加快搜索的速度；
2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
总结：
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中于其它表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引
5.where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题，在高并发倾向创建组合索引
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

索引不可以建立在数据库表中哪些列的上面？
1.对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2.对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3.对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4.当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
总结：
1.表记录太少
2.经常增删改的表
3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引

索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引
唯一索引是索引列的值必须唯一，单允许有空值。

复合索引
即一个索引包含多个列。

基本语法
创建索引语法：
CREATE [UNIQUE] INDEX [indexName] ON [table_name(columnname(length))]
ALTER [table_name] ADD [UNIQUE] INDEX [indexName] ON (columnname(length))

删除索引语法:
DROP INDEX [indexName] ON [table_name]

查看索引语法:
SHOW INDEX FROM [table_name]

mysql索引结构
BTree索引
Hash索引
full-text全文索引
R-Tree索引