【数据库基础】MySQL索引初识

xiaoxiao2021-02-28  27

简介弊端种类 普通索引唯一索引全文索引 索引的删除查看索引索引失效 like查询字段计算NOT IN操作字符串忘加引号 常见问题 主键和唯一索引

简介

索引(INDEX或KEY)是我们引用的为了高效查询的一种方式,在需要作为where条件的字段上加索引,避免了从头开始全表扫描和过分的磁盘IO。

可以将索引理解为目录(跳跃式的查找),底层为B+树(更少的IO,有序等便利)

是一种典型的以空间换时间的处理方式。

这里不展开了。

详见下面这篇文章,别人写的很好!

数据库索引到底是什么,是怎样工作的?


弊端

虽然索引能够大大提高我们查询的效率,但是也不是索引越多越好。

过多的索引占用大量的磁盘空间。因为索引是独立于表数据单独存储的。减慢了数据库修改删除更新的性能。

以这两个核心弊端为出发点,总结一些索引使用的注意点

加索引的字段一定要出现在where条件中(废话)经常作为条件的字段应该加索引,特别是大表的字段。表的数据过于少就别加索引。索引应该建在选择性高的字段上。 举个例子,比如一个表几百万个人的数据就有几百万个手机号,手机号作为索引无可厚非。但是如果你将性别男或者女作为索引就太傻比了。。。 类似的还有状态0,1 等这类字段不适合做索引。 频繁更新的字段不要加索引。这里的频繁是指相对于查询而言索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,可以利用索引的排序加快查询;

种类

创建索引的时候可以设定索引使用字段的长度。 比如:

CREATE INDEX index_name ON table_name(column(length)) 如不指定,则使用该字段的全长作为索引的参考。

普通索引

这是最基本的索引类型,而且它没有唯一性之类的限制。

CREATE INDEX 索引的名字 ON tablename (列的列表(length)); ALTER TABLE tablename ADD INDEX 索引的名字 (列的列表(length));

唯一索引

唯一索引要求该字段不存在重复值,如果数据表里该字段已有重复的值的话,建立唯一所以会失败(比如下面这个15555555555重复了)

对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。

ERROR 1062 (23000): Duplicate entry '15555555555' for key 'tel_key'

创建方法

create table xxx( ... UNIQUE 索引名 (字段名(length)) ) CREATE UNIQUE INDEX index_name ON table_name (column_list(length)); ALTER TABLE table_name ADD UNIQUE index_name(column_list(length));

全文索引

在MySQL中,全文索引的索引类型为FULLTEXT。 FULLTEXT索引仅可用于 MyISAM 全文索引 (适合在进行模糊查询的时候使用)

MySQL从3.23.23版开始支持全文索引和全文检索。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

创建方法

CREATE FULLTEXT index_name on table_name (column_list(length)); ALTER TABLE table_name ADD FULLTEXT index_name(column_list(length));

对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

索引的删除

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。

DROP INDEX index_name ON talbe_name ALTER TABLE table_name DROP INDEX index_name ALTER TABLE table_name DROP PRIMARY KEY

其中,前两条语句是等价的,删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

查看索引

mysql> show index from table_name; mysql> show keys from table_name;

二者效果相同。

Non_unique 如果索引不能包括重复词,则为0。如果可以,则为1。 Key_name 索引的名称Seq_in_index 索引中的列序列号,从1开始。Column_name 加索引的列名称。Collation 列以什么方式存储在索引中。值‘A’(升序)或NULL(无分类)。Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL。Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO (那空白呢?)Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)

索引失效

like查询

mysql在使用like查询的时候只使用后面的%时,才会使用到索引。

比如: select * from table where name like “xxx%”

以下两种情况 select * from table where name like “%xxx%” select * from table where name like “%xxx” 索引失效。

字段计算

不要对加索引的字段进行计算。

select .... where id/2=100;

这样id的索引不会被使用。 应该改成 select .... where id=100*2;

NOT IN操作

不使用NOT IN和操作 NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替,id3则可使用id>3 or id

字符串忘加引号

如果字符串列加了索引,一定要在where条件中带上引号,否则会不使用索引执行全表扫描。

一个单引号引发的MYSQL性能损失

更多情况参考

mySQL索引失效的几种情况

常见问题

主要是面试中常问的

主键和唯一索引

参考

https://www.cnblogs.com/sweet521/p/6203360.html https://www.cnblogs.com/xuzhengzong/p/7680464.html https://www.cnblogs.com/lihuiyong/p/5623191.html

转载请注明原文地址: https://www.6miu.com/read-2600143.html

最新回复(0)