索引是用来加速查询的技术的选择之一,在通常情况下,造成查询速度差异 的因素就是索引是否使用得当。当我们没有对数据表的某一字段段或者多个 字段添加索引时,实际上执行的全表扫描操作,效率很低。而如果我们为某 些字段添加索引,mysql
在执行搜索时便可以通过扫描索引,然后再找出索 引对应的值,从而提高效率。
实际上索引的类型不多,以下只是针对个人以前遇到的索引概念的解释,有 可能某个索引有多种称呼,只是取决于你用哪个角度去描述它。
b
树索引:采用b-tree
s数据结构存储索引,比如primary key
,unique
,index
。hash
索引:将一个散列函数应用于每一个列值,最终的散列值都会被存入索引,用于执行查找。r
树索引:采用r-trees
数据结构存储索引,比如spatial index
。(空间数据类型的索引)(fulltext index)
:一般在char
,varchar
或者text
列上创建此索引。可用来代替like ‘%xx%’
实现模糊查询。(col1,col2,col3)
这三列进行索引时,只有(col1)
,(col1,col2)
,(col1,col2,col3)
才能进行索引搜索。(col1,col3)
也不能进行索引。innodb
表都有一个特殊的索引称为聚簇索引,一般来说,当为一个表定义一个primay key
时,innodb
就会使用它作为聚簇索引。如果没有定义primary key
时,mysql
就会查找第一个非空的 unique index
作为聚簇索引。如果以上两种情况都不满足的话,innodb
内部会在表的每一行产生一个隐藏的并且名为gen_clust_index
的聚簇索引。这个聚簇索引是一个六个字节
长度的行id
字段,id
值随着新行的插入而单调增长。实际上,除了聚簇索引,其他索引都称为二级索引。在innodb
中,二级索引的每一行(将索引假设为行方便理解,实际上索引的存储方式取决于具体的存储引擎)中都包含着一个primary key
列,innodb
使用primary key
这一列的列值在聚簇索引中查找相对应的数据(可以将聚簇 索引理解为中间值),从而最后得到最终的结果集。聚簇索引的数据分布如下图:(图来自《高性能mysql》)primary key
),叶子页存放着所对应的数据,节点页和叶子页这个整体就称为聚簇索引,由此可见,聚簇索引更像是一种数据存储结构。last_name
、first_name
创建了多列索引,并且在查询的时候只查询这两列的结果,因此mysql
会使用覆盖索引查询数据,这也意味着mysql
不会对实际的数据行进行查询,因为所需结果已经可以从索引中查找出来了。sql
语句:sql
语句中,我们想查询id
和last_name
的值,而id
是主键,last_name
是多列索引中的最左索引,但是此时的查询依旧使用覆盖索引查询。原因在于id
实际是作为聚簇索引的,而多列索引自然就是二级索引了,上面提到,二级索引都包含着一列primary key
列,而列值就是聚簇索引的索引值,因此此时mysql
可以直接使用覆盖索引中查找出对应的结果集。innodb
存储引擎和myisam
存储引擎都只支持b树索引(实际上innodb
还支持自适应的hash
索引,只是不能人为创建),memory
存储引擎默认使用hash
索引,但它也支持b
树索引。<
、<=
、=
、>=
、>
、<>
、!=
和between
运算符,进行精确比较或者范围比较时,使用b
树索引会带来高效。如果匹配模式是以一个纯字符串,而不是一个通配符作为开头的,那么b
树索引还可以用在使用like
进行模式匹配的操作里。hash
索引,在使用运算符=
或者<=>
(安全等于的意思,当比较的值含有null
值的时候,来返回一个布尔值)完成精确(这里说精确是因为hash
索引是用一个hash
函数对整个列值hash
,而不是某几个字符或者字节)匹配的比较操作里,散列索引的速度非常快。where
子句中的列、连接子句中的列、或者出现在order by
或group by
子句中的列创建索引是比较好的。25
个字符,那么就不要用char(200)
,其他数据类型同理。特别是innodb
表来说,因为它使用的是聚簇索引,如果主键过长的话,会导致二级索引占用的存储空间过大。索引确实可以加快检索速度,但是它同时也降低了索引列的插入、删除和更新值的速度,因为写入一个行不仅是写入一个数据行,还要更改索引。表的索引越多,需要做出的更改就越多,平均性能下降得也就越多。并且当所创建的索引过多时,mysql
查询优化器在选择使用哪种索引方案时,也会降低一定的效率。其次,索引也会占用磁盘空间,多个索引会占据更大的空间。与没有索引相比,使用索引很快便达到表的大小极限。
create table
创建索引(index_name可选)
primary key
或spatial
,则它必须为not null
的。其他索引列允许包含null
值。primary key
或unique
索引。 这两种索引很像,主要区别有一下两点:primary key
。因为primary key
的名字总是为primary
,而同一个表不允许有两个同名的索引。可以在一个表里放置多个unique
索引。primary key
不可以包含null
值,而unique
索引可以。如果某个unique
索引包含了null
值,那么它就可以包含多个null
值。因为null
值不会与任何值相等,包括它本身。最后,我们可以通过drop index
或alter table
语句来删除索引
drop index
删除索引首先来看一下mysql
创建表的语句:(图来自《mysql官方文档》,图太大所以省略了一部分)
从上图可以看出,实际上index
和key
是同义词,之所以同时存在主要是为了与其他数据库系统做兼容,另外还有以下两个结论。
primary key
与 unique[index|key]
很相似,具体区别可以查看上面的内容。index和key
允许出现相同的列值,但是unique[index|key]
不允许出现相同的列值。(记住null != null)
如对本文有疑问, 点击进行留言回复!!
centos8使用Apache httpd2.4.37安装web服务器的步骤详解
网友评论