Mysql之正确使用索引

创建索引的几种方式
Mysql目前主要有以下几种索引方式:FULLTEXT,HASH,BTREE,RTREE。
那么,这几种索引有什么功能和性能上的不同呢?详情点击下面链接:
http://blog.sina.com.cn/s/blog_4aca42510102v5l2.html

常用索引的类型
Mysql常见索引有:主键索引(主键自带索引功能)、唯一索引、普通索引、全文索引、组合索引(联合索引,多列索引)
http://www.jb51.net/article/49346.htm**

如何正确的使用索引

索引要独立:索引在表达式或函数中会失效

索引长度要合适:建立长度合适的索引,在不影响性能下减少内存开销。注意!mysql只支持前缀索引,要想实现后缀索引则可以把对应的字段反向记录,记录的维护利用触发器。

尝试建立联合索引:在where中,若有二个及以上的字段进行过滤,则可以把这些字段进行联合索引。

建立联合索引时要特别注意字段的顺序:一个好的顺序可以大大的提高sql执行速度,具体如何确定顺序可参考《高性能mysql》一书

聚簇索引:聚簇索引并不是一种单独的索引类型,也就是不能手动建立的,它只是一种数据存储方式。聚簇索引是基于B-Tree索引的,在其叶子页(leaf page)中存放的是数据行,叶子页中包含的是数据行的全部数据,而节点页中只有索引列数据。具体可见下图:
这里写图片描述

需要注意的是,MySQL内建存储引擎不支持选择某个索引来建立聚簇索引,对于InnoDB来说,若表有主键的话,就利用主键来建立聚簇索引,如果没有主键,则选择一个唯一非空索引,若找不到这样的索引,则会隐式定义一个主键来建立聚簇索引。

聚簇索引的优缺点:

能大大提高磁盘IO密集型应用的性能。例如实现电子邮箱时,可以根据用户的ID来建立聚簇索引,这样在读取用户的邮件时,就能一次性读取全部的邮件,而不用多次IO来读取;若在内存中,效果就要大打折扣了。

访问速度更快。比二级索引(非聚簇索引)更快,这是因为二级索引要进行2次查询,而聚簇索引1次就可以查到。

二级索引为什么要进行二次查询呢?这是因为,第一次查询的结果值(叶子节点值)只是数据行主键值,而不是这数据行的物理地址。

数据的插入速度严重依赖于插入顺序,按照主键顺序插入是最快的,如果不是按照主键的顺序插入,在加载完成后最好使用optimize
table命令重新组织一下表。 更新聚簇索引的索引列的代价很高,因为会强制InnoDB将每个被更新的行移到新的位置。

插入数据有可能会导致“页分裂”的问题,这样会导致全表扫描速度变慢。

覆盖索引:什么是覆盖索引,就是索引的叶子节点中包含所有要查询的数据,这样就不需要“回表”查询了,大大提高了查询速度。

前缀压缩索引:使用前缀压缩索引,减小了内存的开销,在某些应用场景中,能极大地提高性能。

避免冗余和重复索引:建立索引时应该避免在同一字段上建立相同类型的索引,冗余索引与重复索引有一些不同,在满足前面的2个条件下,有如下区别,例如KEY(a,b)和KEY(a)就是在a上建立的冗余索引。

删除未使用的索引:通过查询INFORMATION_SCHMA.INDEX_STATISTICS(SCHMA索引统计信息)来查看各索引的具体信息。

SELECT * FROM STATISTICS WHERE INDEX_SCHEMA = ‘数据库名称’;

维护索引和表

找到并修复损坏的表

表损坏通常是系统崩溃导致的(特别是MyISAM),也可能是硬件问题。利用CHECK TABLE可以找出大多数表和索引的问题。利用REPAIR TABLE来修复表,但不是所有的引擎都支持,InnoDB可以采用不做任何操作的ATLER TABLE来修复表
ATLER TABLE table_name ENGINE = INNODB;

更新索引统计信息
减少索引和数据碎片
说明:以上两点具体详情可见《高性能mysql》一书
【补充】强制索引和禁止某个索引
mysql强制使用索引:force index(索引名或者主键PRI)
例如:

select * from table force index(PRI) limit 2;(强制使用主键)
select * from table force index(ziduan1_index) limit 2;(强制使用索引”ziduan1_index”)
select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引”PRI和ziduan1_index”)

mysql禁止某个索引:ignore index(索引名或者主键PRI)
例如:

select * from table ignore index(PRI) limit 2;(禁止使用主键)
select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引”ziduan1_index”)
select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)
  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL索引是用于提高查询效率的一种数据结构。它可以加速数据的查找和排序操作,减少数据库的IO负载。 使用索引的好处包括: 1. 提高查询速度:索引可以将数据按照特定的顺序存储,使得数据库可以更快地定位和检索数据,从而提高查询速度。 2. 减少磁盘IO:通过使用索引,数据库可以减少磁盘IO次数,从而减少数据检索所需的时间。 3. 加速排序操作:如果查询包含了排序操作,使用索引可以加快排序速度。 4. 提高数据的唯一性约束:通过在列上创建唯一索引,可以确保列中的值是唯一的。 5. 加速连接操作:在多表连接查询中,使用索引可以加快连接操作的速度。 要正确使用MySQL索引,需要注意以下几点: 1.选择合适的列进行索引:通常选择经常用于查询条件的列作为索引列,同时需要考虑选择性,即索引列的值是否具有较高的区分度。 2. 避免过多的索引:过多的索引会增加数据库的维护成本,并且可能降低写操作的性能。 3. 避免重复和冗余索引:重复和冗余的索引不仅浪费存储空间,还会增加索引维护的成本。 4. 更新索引统计信息:MySQL会根据索引统计信息来选择使用哪个索引,因此定期更新索引统计信息是很重要的。 5. 考虑联合索引使用多列的联合索引可以提高复合条件查询的效率。 总之,合理使用索引可以显著提高MySQL数据库的查询性能和整体性能。但是需要根据具体情况进行索引的创建和维护。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值