索引
在数据之外, 数据库系统还维护着满足特定查找算法的数据结构, 这些数据结构以某种方式引用 (指向) 数据. 这种数据结构就是索引, 它能够帮助我们高效获取数.
索引的优点
提高数据查询速度, 降低数据库的 IO 成本;
通过索引对数据进行排序, 降低数据排序的成本, 降低了 CPU 的消耗.
索引的缺点
降低 INSERT,UPDATE,DELETE 的操作数据, 因为要更新索引;
索引也需要维护一张表, 会占用物理空间.
哪些情况需要创建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段, 外键关系建立索引
单键 / 组合索引的选择问题, who?(在高并发下倾向创建组合索引)
查询中排序的字段, 排序的字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
哪些情况不要创建索引
表记录太少
经常增删改的表.
某个数据列唯一性很差(包含许多重复的内容), 为它建立索引就没有太大的实际效果.
WHERE 条件里用不到的字段不创建索引
MySQL 中的索引
索引的类型
MySQL 目前主要有以下几种索引类型:
普通索引
唯一索引
主键索引
组合索引
全文索引
和索引相关的 SQL
1. 查看索引
show index from table_name;
2. 删除索引
drop index [index_name] on table_name;
3. 新增索引
(1). 直接创建索引
CREATE INDEX index_name ON table(column(length))
(2). 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
(3). 创建表的时候同时创建索引
- CREATE TABLE `table` (
- `id` int(11) NOT NULL AUTO_INCREMENT ,
- `title` char(255) CHARACTER NOT NULL ,
- PRIMARY KEY (`id`),
- INDEX index_name (title(length))
- )
3.1 创建不同类型的索引
-- 主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
-- 唯一索引
ALTER TABLE table_name ADD UNIQUE index_name (column_list);
-- 普通索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
-- 全文索引
ALTER TABLE table_name ADD FULLTEXT index_name (column_list);
根据所使用的数据结构, MySQL 中所使用的索引有如下几种
B-Tree 索引
Hash 索引
Full-text 全文索引
R-Tree 索引
B-Tree 索引
B-Tree 索引的物理文件大多以 Balance Tree 来存储, 也就是实际的数据都存放于 Tree 的 Leaf Node 当中, 且从 Root Node 到任何一个 Leaf Node 的最短路径的长度都是完全相同.
B-Tree 索引是 MySQL 中使用最为频繁的索引类型, 除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引. 不过在 InnoDB 中, 它使用的数据结构是 B-Tree 的变种 B+Tree, 其在 B-Tree 的基础上做了改动:
每一个 Leaf Node 上面除了存放索引键的相关信息之外, 还存储了指向与该 Leaf Node 相邻的后一个 Leaf Node 的指针信息, 这主要是为了加快检索多个相邻 Leaf Node 的效率
.
Hash 索引
Hash 索引通过 Hash 算法来实现, 目前主要用在 MySQL 的 Memory 和 NDB Cluster 存储引擎当中.
我们知道 Hash 的效率是非常高的, 它通过计算 hash 值能够一次定位到数据, 也就是说一次 IO 操作就可以完成数据的检索. 而不像 B-Tree 索引要从根节点找到叶子节点, 树有多高就需要多少次 IO 操作. 既然如此, 那么为啥不直接都使用 Hash 索引呢? 因为 Hash 索引存在以下缺点:
Hash 索引无法引用在范围查找中;
无法通过 Hash 索引来避免数据的排序操作;
当 Key 发生了 Hash 冲突时, 查询就变得麻烦了(可能需要全表扫描).
Full-text 索引
Full-text 索引在 MySQL 中, 仅有 MyISAM 存储引擎支持它, 且只有数据类型为 CHAR,VARCHAR,TEXT 的列可以创建 Full-txt 索引.
Fulltext 索引主要用来替代效率低下的 LIKE '%***%'操作, Full-text 索引在中文支持方面还不太好, 须要借助第三方的补丁或插件来完成, 且 Full-text 的创建所消耗的资源也比较大, 所以在应用于实际生产环境之前还是尽量做好评估.
R-Tree 索引
主要用来解决空间数据检索的问题, 常用于 MyISAM 存储引擎中.
来源: http://www.jianshu.com/p/dc31e65e6d1b