MySQL索引
一、索引的本质
索引是帮助MySQL高效获取数据的排好序的数据结构
索引数据结构
二叉树
红黑树
hash表 : 不支持范围查找、模糊查找
B-Tree
数据结构实验网站:
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B+树
- 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针连接,提高区间访问的性能
存储引擎
1.MyISAM存储引擎
MyISAM索引文件和数据文件是分离的(非聚集)
- frm文件存储表结构定义数据
- MYD文件存储Data数据
- MYI文件存储Index索引字段
2.InnoDB存储引擎(聚集)
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引——叶节点包含了完整的数据记录
- 为什么lnnoDB表必须有主键,并且推荐使用整型的自增主键?
- 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
- frm文件存储表结构定义数据
- ibd文件存储索引+数据
联合索引
1、创建索引
注:索引一经创建不能修改,如果要修改索引,只能删除重建。
- 主键索引 PRIMARY KEY :它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键。
- 唯一索引 UNIQUE :ALTER TABLE table_name ADD UNIQUE (column);
- 唯一组合索引 UNIQUE :ALTER TABLE table_name ADD UNIQUE (column1,column2);
- 普通索引 INDEX :ALTER TABLE table_name ADD INDEX index_name (column);
- 组合索引 INDEX :ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);
- 全文索引 FULLTEXT :ALTER TABLE table_name ADD FULLTEXT (column);
- 删除索引:DROP INDEX index_name ON table_name;
2、索引设计的原则
- 适合索引的列是出现在where子句中的列,或者连接子句中指定的列;
- 基数较小的类,索引效果较差,没有必要在此列建立索引;
- 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间;
- 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。