mysql索引

尽一份心出一份力
117 阅读3分钟

黑马mysql视频笔记 www.bilibili.com/video/BV1eD…

一.索引的基本使用

# 创建

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]
-- 示例
create index_idx_city_name on city(city_name);

# 查看
show index  from  table_name;

# 删除
DROP  INDEX  index_name  ON  tbl_name;

alter命令

# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter  table  tb_name  add  primary  key(column_list); 

# 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）	
alter  table  tb_name  add  unique index_name(column_list);
	
# 添加普通索引， 索引值可以出现多次。	
alter  table  tb_name  add  index index_name(column_list);
	
# 该语句指定了索引为FULLTEXT， 用于全文索引
alter  table  tb_name  add  fulltext  index_name(column_list);

二.索引的分类

  1. 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
  2. 唯一索引 ：索引列的值必须唯一，但允许有空值
  3. 复合索引 ：即一个索引包含多个列

三.索引设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

  • 对查询频次较高，且数据量比较大的表建立索引。
  • 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
  • 使用唯一索引，区分度越高，使用索引的效率越高。
  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
  • 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
  • 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
创建复合索引:
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

四.索引概念介绍

mysql inndb常用索引类型是Btree索引，5.6版本支持full-text索引。

索引InnoDB引擎MyISAM引擎Memory引擎
BTREE索引支持支持支持
HASH 索引不支持不支持支持
R-tree 索引不支持支持不支持
Full-text5.6版本之后支持支持不支持

mysql的b+tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。只是简单介绍下mysql的b+tree，想要了解更多，还需要专门学习

image.png

五.索引优化

TODO
avatar
文章
阅读
粉丝