mysql索引浅析
谈起索引,首先简单的介绍一下Mysql的存储引擎。
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。
mysql> show engines;
+――――+―――-+―――――――――――――――――――――-+
| Engine | Support | Comment |
+――――+―――-+―――――――――――――――――――――-+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | YES | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+――――+―――-+―――――――――――――――――――――-+
mysql> show variables like ‘%storage_engine%’;
mysql> show create table 表名;
拥有较高的插入,查询速度,它提供高速存储和检索,以及全文搜索能力,但不支持事务,表级锁。
MyISAM把表存储在两个文件中。一个数据文件,一个索引文件。扩展名为.MYD和.MYI。也就是你可以把这两个文件拷贝在任意的平台上。
InnoDB :事务型数据库的首选引擎,支持ACID事务,支持行级锁定。
其他的如果您有兴趣可以去研究一下,相关这方面的资料很多的呵呵。
索引是在存储引擎层实现的,而不是服务器层。因此,它们并不是标准化的,每个引擎的索引工作方式略有不同。
即使多个引擎支持同样的索引,它们的实现方式也可能有所不同。
下面我们针对MyISAM存储引擎谈谈最频繁使用的B-Tree索引类型。
(这种索引在 Innodb 存储引擎中被称为 Secondary Index,后面稍加介绍)
1970年,R.Bayer和E.mccreight提出了一种适用于外查找的树,它是一种平衡的多叉树,称为B树。
如下图,主键索引和非主键索引除了叶子结点上存储的分别是PK value 和普通key value之外,结构完全一致。
1) 主键索引(PRIMARY KEY)
每张表只有一个,保证数据记录集的唯一性。
2)唯一性索引
主键索引与唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY KEY而不是UNIQUE。
3)普通索引(由关键字KEY或INDEX定义的索引)
4)全文索引(innodb不支持)(FULLTEXT)
可以在 CHAR、VARCHAR 或 TEXT 列上创建
5)多列索引(或复合索引)
根据查询的需求设置,可以完成多条件查询,尤其大数据量查询时可以大大提升查询速度。
我们来看一张示意图,只是表示关系图,请注意并上的数据并不是代表数据实际存储位置。
看图说话及提问:
能使用B-Tree索引的查询
局限性
我们来详细说明一些我们在写SQL时或使用索引经常会模糊的地方及要遵守的约定。
最完美的您设定的索引完全使用,而非全表扫描。
注:建立索引时的顺序非常关键,如果是复合索引,就是多列的,请一定要仔细分析业务查询,将限定查询的字段放在最前面,最常用必用的放在最前面,范围查询的字段尽量后靠。
因为索引是不能跳过索引中的列,所以一个不常用的或范围列放在前面会导致后面的索引字段无法使用。
通常mysql会调整自己where语句中查询条件字段的顺序,而不是以查询字段的顺序为准,它是进行查询优化,去选择合适的索引。
目前我的mysql 5.0.45或77中会根据索引情况去选择合适的索引使用,所以where条件用的顺序与索引并不完全相关,
但之前有些版本要求两者一致,所以我们尽量保持一致,这样符合我们业务及可读性,同样避免一些版本处理不同的问题。
当然您可以通过测试来看看执行计划进行索引分析。
索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。(上面的问题2应该知道答案了吧)
应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
如select a from t where a-5>100;如果a上有索引则用不到,会进行全表扫描;
Select a from t where a>105;则可以用上索引啦。
FROM tablename
FORCE INDEX ( uid_cid )
WHERE uid = 1
AND cid > 1(自己试试吧,呵呵!)
好啦,目前先说这些,都是一些归纳总结,主要就是先铺个概念,大家了解一下存储引擎,支持的索引类型,以及实际建立及使用时的注意事项。
后续如果有时间的话,会介绍查看执行计划的explain工具,以及mysql 中show 命令,你可以想show 什么就show 什么。或者是建立表时“set utf8 collate utf8_bin NOT NULL default ””这么一行有什么作用,呵呵,后续探讨。
同样大家感兴趣的话,可以自己去研究,还是依然推荐那本老少咸宜的宝书《高性能MYSQL》。
如理解上有偏差,欢迎各位同学批评指正,交流产生进步哈哈!
建议继续学习:
- 由浅入深探究mysql索引结构原理、性能分析与优化 (阅读:14943)
- 浅谈MySQL索引背后的数据结构及算法 (阅读:9861)
- 由浅入深理解索引的实现(2) (阅读:6314)
- HBase二级索引与Join (阅读:5763)
- 如何建立合适的索引? (阅读:5334)
- InnODB和MyISAM索引统计集合 (阅读:5160)
- Innodb 表和索引结构 (阅读:4774)
- mysql查询中利用索引的机制 (阅读:4685)
- MySQL索引背后的数据结构及算法原理 (阅读:4401)
- 多维度分类排行榜应用:用位图索引 (阅读:4003)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:lingxi 来源: 量子数科院
- 标签: 索引
- 发布时间:2011-06-30 13:51:31
- [66] Go Reflect 性能
- [65] Oracle MTS模式下 进程地址与会话信
- [64] 如何拿下简短的域名
- [59] android 开发入门
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [58] 图书馆的世界纪录
- [58] 【社会化设计】自我(self)部分――欢迎区
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [46] 读书笔记-壹百度:百度十年千倍的29条法则