MySQL——索引

sqrt_2

于 2019-07-21 23:13:28 发布

阅读量97

点赞数 1

分类专栏： MySQL 文章标签： MySQL索引

本文链接：https://blog.csdn.net/sqrt_2/article/details/96514341

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在查询过程中，对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。因此多个数据表都会对经常被查询的字段添加索引。
针对为什么要使用索引的问题，可以参考博客：https://www.cnblogs.com/tianhuilove/archive/2011/09/05/2167795.html

1.数据库索引

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。通过对比以下几种查询策略，可以发现索引的建立是有要求的：
1.顺序遍历（效率低）
2.二分查找（要求数据有序）
3.B+树（符合要求）
数据库索引：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种的B+树。
为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

2.MySQL索引分类

在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令 MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。
2.1普通索引（normal）
这是最基本的索引类型，而且它没有唯一性之类的限制。MyIASM中默认的BTREE类型的索引，普通索引可以通过以下几种方式创建：
（1）创建索引，例如CREATE INDEX 索引的名字 ON tablename (列名1，列名2,…);
（2）修改表，例如ALTER TABLE tablename ADD INDEX 索引的名字 (列名1，列名2,…);
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( […], INDEX 索引的名字 (列名1，列名 2,…) );
2.2唯一索引（unique）
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，但允许有空值（注意和主键不同）。唯一性索引可以用以下几种方式创建：
（1）创建索引，例如CREATE UNIQUE INDEX 索引的名字 ON tablename (列的列表);
（2）修改表，例如ALTER TABLE tablename ADD UNIQUE 索引的名字 (列的列表);
（3）创建表的时候指定索引，例如CREATE TABLE tablename ( […], UNIQUE 索引的名字 (列的列表) );
2.3主键
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。（主键相当于聚合索引，是查找最快的索引）
2.4全文索引（FULLTEXT）
MySQL从3.23.23版开始支持全文索引，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
具体可以参考博客：
https://blog.csdn.net/mrzhouxiaofei/article/details/79940958
2.5单列索引和多列索引
索引可以是单列索引，也可以是多列索引。
（1）单列索引就是常用的一个列字段的索引，常见的索引。
（2）多列索引就是含有多个列字段的索引

alter table student add index sy(name,age，score); 
&emsp;&emsp;索引sy就为多列索引，多列索引在以下几中情况下才能有效： 
select * from student where name='jia' and age>='12' //where条件中含有索引的首列字段和 第二个字段 
select * from student where name='jia' //where条件中只含有首列字段 
select * from student where name='jia' and score<60//where条件中含有首列字段和第三个字段

总结：多列索引只有在where条件中含有索引中的首列字段时才有效
2.6. 组合索引（最左前缀）
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引。

3.聚簇索引和非聚簇索引

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。具体请看以下博文，一定要看：
https://blog.csdn.net/alexdamiao/article/details/51934917
总结1：
MyISAM（三个文件.frm .myl存放索引 .myd存放数据）
B+树
数据和索引分离非聚集索引
叶节点存储数据的地址
主索引：存储数据地址的索引
辅助索引：查询一次
在这里插入图片描述

总结2：
InnoDB
B+树
索引当成数据的一部分存储聚集索引
叶节点存储真实数据数据的存放依赖索引
系统会自动创建索引
1.主键主键索引————>主索引
主索引：存储数据的索引
辅助索引：查询两次，第一次按Name在叶节点查询ID，然后按照id查询数据
2.唯一键唯一索引————>主索引
3.添加一个隐藏字段，6字节，autoincreament(自增)
在这里插入图片描述

3.索引优化

3.1不适合建立索引
不经常查询的字段
数值少的字段
数值大的字段（大文本字段）
写的操作频率大于读的效率
3.2适合建立索引
经常用于排序的字段
主键的列
范围搜索的列上

4.注意事项

索引的建立可以带来很多好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件就会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

何时使用聚集索引或非聚集索引？

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	使用	使用
返回某范围内的数据	使用	不使用
一个或极少不同值	不使用	不使用
小数目的不同值	使用	不使用
大数目的不同值	不使用	使用
频繁更新的列	不使用	使用
外键列	使用	使用
主键列	使用	使用
频繁修改索引列	不使用	使用

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

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