索引
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这种数据结构就是索引，它能够帮助我们高效获取数。
索引的优点
- 提高数据查询速度，降低数据库的IO成本；
- 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
索引的缺点
- 降低INSERT、UPDATE、DELETE的操作数据，因为要更新索引；
- 索引也需要维护一张表，会占用物理空间。
哪些情况需要创建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
5.查询中排序的字段，排序的字段若通过索引去访问将大大提高排序速度
6.查询中统计或者分组字段
哪些情况不要创建索引
1.表记录太少
2.经常增删改的表。
3.某个数据列唯一性很差（包含许多重复的内容），为它建立索引就没有太大的实际效果。
4.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存储引擎中。
网友评论