mysql怎么查看索引值,查看mysql的索引的命令

用mysql查询某字段是否有索引怎么做?

显示一个表所有索引的SQL语句是:

成都服务器托管,创新互联公司提供包括服务器租用、成都移动机房、带宽租用、云主机、机柜租用、主机租用托管、CDN网站加速、申请域名等业务的一体化完整服务。电话咨询:18982081108

show index from 数据库名.表名

查看某表某一列上的索引使用下面的SQL语句:

show index from 数据库名.表名 where column_name like '列名'

下面的SQL语句在我的数据库上执行成功:

show index from web.clubuser where column_name like 'user'。

MySQL前缀索引

前缀索引顾名思义,定义字符串的一部分当做索引,而不是把整个字符串当做索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。

假设一张表有 id,name,email 2个字段

1.创建email列的普通索引应该是: alter table T add index idx_email1( email )

2.前缀索引的创建规则为: alter table table T add index idx_email2( email(6) )

当然第一索引包含是的整个字符串,第二个是该字段前6个字节(注意是字节)

对于这2中索引,B+树怎么存储呢?

INSERT INTO T (email) VALUES ('瞎子','zhangsh1234@163.com'), ('剑圣','lisi1998883@163.com'), ('露娜','zhangssxyz@163.com'), ('李白','zhangsy1998@163.com'), ('韩信','zhaq5481993@163.com'), ('百里玄策','hhaq5481993@163.com');

【谁还不是个野王啊】

普通索引存储为:

是的你没看错,前缀索引那颗树上的存储的是email的前6位字节,也就是你创建前缀索引时指定的前缀字节长度。2种树相比,前缀索引存储了更少的数据,那么他所耗费的空间也就相比较少,这正是他的一个优点。同样的也就相对的增加了扫描行数。

什么增加了扫描行数???? 这是为什么呢?

那么小朋友咱们一起来看下吧。

假设SQL如此这般: select id,name,email from T where email = 'zhangsh1234@163.com'

那么这2个SQL,应该怎么操作呢。

idx_email1:

2.到主键上查到主键为ID1的,判断email值是否正确【为什么判断呢,其实我理解是为了二次判断保证数据一致性吧,比较官方的解释尚未找到】,正确放入结果集

3.取 idx_email1 索引树上刚刚查到的位置的下一条记录,如此往复。

循环过程中,需要回主键取1次数据,所以系统可以认为只扫描了一行【1次是数第一棵树数出来的】

idx_email2:

1.从 索引数上找到满足索引值为 'zhangs'的该记录,取得 ID1的值

2.到主键上查到主键值是 ID1 的行,判断出 email 的值是’ zhangsh1234@xxx.com ’,这行记录放入结果集【不是要的值,丢弃,进行下一步】

3.取 idx_email2 上刚刚查到的位置的下一条记录,重复以上步骤

在这个过程中,要回主键索引取 3 次数据,也就是扫描了 3 行。通过这个对比,你很容易就可以发现,使用前缀索引后,可能会导致查询语句读数据的次数变多。

但是,对于这个查询语句来说,如果你定义的 idx_email2 不是 email(6) 而是 email(8),也就是说取 email 字段的前 8 个字节来构建索引的话,即满足前缀’zhangsh’的记录只有一个,也能够直接查到 ID1,只扫描一行就结束了。也就是说使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。

那么问题来了,到底定义多长才算是合理呢?

一般的定义原则是 count(distinct(columnName))/count(*) ,当前缀索引【count(distinct(columnName(length))),length是你想要创建列的前缀字节长度】越接近此值越好,当有多个前缀字节都一样且都等于这个值时怎么选择呢,当然是 字节越少越好了哈,字节越少越省空间。索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索的效率也就会越低。

count(distinct(columnName(length))) 翻译到SQL 为: count(dictinct(left(colunmName, length)))

前面我们说了使用前缀索引可能会增加扫描行数,这会影响到性能。其实,前缀索引的影响不止如此,我们再看一下另外一个场景。

来呀,上SQL: select id,email from T where email='zhangsh1234@163.com'

如果按照email全字段索引,那么此SQL 是不需要回表的【为什么不需要回表?兄嘚,这个相当于覆盖索引了哈】

那么如果按照前缀索引是否需要回表呢?答案是的。

因为当判断前6个字节相等后,需要拿到id 回表拿到email的全部内容进行比较,如果不相同,丢弃这行,否则加入结果集。

那么有人会问了,我把长度放大点,包含所有字节不就好了吗?

那么此时会有如下问题。

1.当你此时的长度是囊括了全字段,但是系统是不知道的,他还是需要回表再次判断的,去确定前缀索引的定义是否截断了完整信息。

2.此时长度是够了,那么能肯定因为业务日后不会增加长度吗?

3.尽可能的加长长度,还不如直接建立全字段索引呢

综上,使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是你在选择是否使用前缀索引时需要考虑的一个因素。

前面说到的是,可以根据字段前面几个字节进行查询的,那么对于身份证这种,一共 18 位,其中前 6 位是地址码,所以同一个县的人的身份证号前 6 位一般会是相同的。

或许你会说,多弄几个字节不就好吗?那么请问下自己为什么使用前缀索引呢,不就是为了节省空间吗?

那么这么做合适吗? 不合适对吗? 乖~,快去反省下吧

那么采用前缀索引显示是不行的,那么如果用前缀索引怎么办呢,聪明的你应该已经猜到了,采用倒叙存储,然后建立前缀索引。

放到SQL 中就应该是这样的: select field_list from t where id_card = reverse('id_card_string');

当然了,这种逻辑建议放到业务逻辑中实现,而不是放到SQL 中。

按照上述第4节的内容,有人或许会有另一个想法,还倒叙建立前缀索引复杂不,hash索引或者hash字段不香吗?

有人会问了,为什么要在创建一个值来存储hash值呢,如果不存储你知道原值是什么吗? 同时hash算法是有一定重复可能的(hash值碰撞)

【可以了解下partition算法哦:[ 】。如果重复了,不存储原值,你是无法判断出正确数据的。

注:【hash字段不代表hash索引,hash索引原理正在快马加鞭】,简单说下hash索引,hash索引不需要创建一个值来存储hash值,而是有hasn表来存储【hash值碰撞时,由一个链表来搞定了】,存储的内容为 hash值和每行的行指针 。

说回来啊,跑题了

查询时: select field_list from t where id_card_crc=crc32('id_card_string') and id_card='id_card_string'

不过有个问题相信你也想到了,不管是hash存储值还是hash索引都是不支持范围查询的。

来总结下这2个优缺点吧

1.从占用空间来看呢,倒叙索引不需要额外开辟存储空间,而hash字段需要额外的一个字段,所以从这点上看倒叙索引更胜一筹,NO!并不准确,如果前缀长度过长,那么这2个情况额外的空间也就相差无几了

3.从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数

1.全字段完整索引比较占空间,但是而走覆盖索引

2.前缀索引,节省空间,但会增加扫描 次数 并且不能使用覆盖索引【每次都需回表校验】

3.倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题。【倒叙方法建立放到业务逻辑中】

4.hash字段索引,相比前缀索引性能较为稳定,但是有额外的存储空间和计算消耗,同时也 不 支持范围查询

mysql中如何查看和删除唯一索引

mysql中如何查看和删除唯一索引。

查看唯一索引:

show

index

from

mytable;//mytable

是表名

查询结果如下:

查询到唯一索引后,如何删除唯一索引呢,使用如下命令:

alter

table

mytable

drop

index

mdl_tag_use_ix;//mdl_tag_use_ix是上表查出的索引名,key_name

怎么查看表的索引mysql

查看索引的语法格式如下:

SHOW INDEX FROM 表名 [ FROM 数据库名]

语法说明如下:

表名:指定需要查看索引的数据表名。

数据库名:指定需要查看索引的数据表所在的数据库,可省略。比如,SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

示例

使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息,SQL 语句和运行结果如下所示。

mysql SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要参数说明如下:

参数 说明

Table 表示创建索引的数据表名,这里是 tb_stu_info2 数据表。

Non_unique 表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。

Key_name 表示索引的名称。

Seq_in_index 表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。

Column_name 表示定义索引的列字段。

Collation 表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。

Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。

Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。

Packed 指示关键字如何被压缩。若没有被压缩,值为 NULL。

Null 用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。

Index_type 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。

Comment 显示评注。


名称栏目:mysql怎么查看索引值,查看mysql的索引的命令
文章链接:http://scyanting.com/article/dsgocje.html