手记

MySQL索引

1. 什么是索引

 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息.SQL索引在数据库优化中占非常大的比例,一个好的索引设计,可以让你的效率提高几十甚至几百倍.

2. 深入浅出理解索引

 实际上,你可以把索引理解为一种特殊的目录.数据库索引中按照存放的物理位置顺序(很多地方都说了分类,但没有说如何分类,此处详见百度百科) ,可分为两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。 下面我们举例来说明一下聚集索引和非聚集索引的区别.

 其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

 如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

 通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序

3. 索引的优缺点

优点

  1. 可以通过建立唯一索引或者主键索引 , 保证数据库表中每一行数据的唯一性

  2. 建立索引可以大大提高检索的数据 , 以及减少表的检索行数

  3. 在表连接的连接调节,可以加速表语表直接的相连

  4. 在分组和排序字句进行检索 , 可以减少查询时间中分组和排序所消耗的时间(数据库的记录会重新排序)

  5. 建立索引 , 在查询中使用索引 , 可以提高性能.

优势总结: 可以快速检索,减少I/O次数,加快检索速度;根据索引分组和排序,可以加快分组和排序.

劣势

  1. 在创建索引和维护索引 , 会耗费时间 , 随着数据量的增加而增加

  2. 索引文件会占用物理空间 , 除了表需要占用物理空间外 , 每个索引还会占用一定的物理空间

  3. 当对表的数据进行 INSERT , UPDATE , DELETE 的时候 , 索引也要动态的维护 , 这样就降低数据的维护速速,(建立索引会占用磁盘空间的索引文件 , 一般情况下这个问题太严重 , 但如果你在一个大表上创建了多种组合索引 , 索引文件会膨胀很快).

劣势总结: 索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间是数据库表的1.5倍;索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,添加,修改)的效率,因为在修改数据表的同事还需要修改索引表.

4. 索引分类

按数据存放的物理为止顺序划分 :

  • 聚簇索引

  • 非聚簇索引

按数据库的功能 划分:

  • 唯一索引

  • 主键索引

  • 聚集索引

4.1 主键索引

  1. 主键索引(创建表时添加)

CREATE TABLE Primary_key (    id INT UNSIGNED COMMENT '无符号' PRIMARY KEY AUTO_INCREMENT    
);

1.2 主键索引(建表后添加)

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

 注: 下面的示例 table_name 是表名 , col是列名

4.2 唯一索引

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

ALTER TABLE 'table_name' ADD UNIQUE('col');

4.3 普通索引

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

ALTER TABLE 'table_name' ADD INDEX index_name('col');

4.4 全文索引

用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT('col');

4.5 组合索引

用多个列组合构建的索引 , 这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');
  • *遵循“最左前缀”原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了col1,col1col2,col1col2col3三个索引,而col2或者col3是不能使用索引的。

  • *在使用组合索引的时候可能因为列名长度过长而导致索引的key太大,导致效率降低,在允许的情况下,可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2(3));
# 表示使用col1的前4个字符和col2的前3个字符作为索引

5. MySQL中索引的语法

创建索引

  • 在建表的时候添加索引

    CREATE TABLE tablename(id INT(10) PRIMARY KEY,content TEXT,name CHAR(10) NOT NULL, 
    [UNIQUE] INDEX name_index (name(4)))ENGION MyISAM DEFAULT CHARSE utf8;
  • 在创建表以后添加索引

    ALTER my_table ADD [UNIQUE] INDEX my_index(my_text);CREATE INDEX my_index ON my_table(my_text);

根据索引查询

SELECT * FROM tablename WHERE MATCH(content) AGAINST('word1','word2',...);

删除索引

DROP my_index ON tablename;

查看表中的索引

SHOW INDEX FROM tablename;

6. 使用索引的原则

什么情况下要使用索引

  • 主键自动建立唯一索引

  • 经常作为查询调节在where 或者order by 语句中出现的列要建立索引

  • 作为排序的列要建立索引

  • 查询中与其他表关联的字段 , 外键关系建立索引

  • 高并发条件下倾向组合索引

说明时候不要使用索引

  • 经常增删的列不要建立索引

  • 有大量重复的列不需要建立索引

  • 表记录太少不需要建立索引

原文参考作者 : https://www.cnblogs.com/chenshishuo/p/5030029.html

https://www.cnblogs.com/hyd1213126/p/5828937.html

https://www.cnblogs.com/softidea/p/5977860.html

https://blog.csdn.net/tongdanping/article/details/79878302

作者:有_味

原文链接:https://www.cnblogs.com/zo99/p/10490629.html


0人推荐
随时随地看视频
慕课网APP