MySQL索引
MySQL索引机制概述
## 数据库索引的本质 索引就是用来帮助表快速检索目标数据的。 索引本质上在数据库中是什么呢?数据库是基于磁盘工作的,所有的数据都会放到磁盘上存储,而索引也是数据的一种,因此与表数据相同,最终创建出的索引也会在磁盘生成本地文件。 不过索引文件在磁盘中究竟以何种方式存储,这是由索引的数据结构来决定的。同时,由于索引机制最终是由存储引擎实现,因此不同存储引擎下的索引文件,其保存在本地的格式也并不相同。 在这里有一个点需要注意:建立索引的工作在表数据越少时越好,如果你想要给一张百万、千万条数据级别的表新创建一个索引,那创建的耗时也不短,这是为什么呢? 索引本质上和表是一样的,都是磁盘中的文件,那也就代表着创建一个索引,并不像单纯的给一张表加个约束那么简单,而是会基于原有的表数据,重新在磁盘中创建新的本地索引文件。假设表中有一千万条数据,那创建索引时,就需要将索引字段上的1000W个值全部拷贝到本地索引文件中,同时做好排序并与表数据产生映射关系。 ## MySQL索引的创建方式 - ①使用CREATE语句创建 ```sql CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]); ``` 这种创建方式可以给一张已存在的表结构添加索引,其中需要指定几个值: - indexName:当前创建的索引,创建成功后叫啥名字。 - tableName:要在哪张表上创建一个索引,这里指定表名。 - columnName:要为表中的哪个字段创建索引,这里指定字段名。 - length:如果字段存储的值过长,选用值的前多少个字符创建索引。 - ASC|DESC:指定索引的排序方式,ASC是升序,DESC是降序,默认ASC。 当然,上述语句中的INDEX也可更改为KEY,作用都是创建一个普通索引,而对于其他的索引类型,这点在后续的索引分类中再聊。 - ②使用ALTER语句创建 ```sql ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]); ``` - ③建表时DDL语句中创建 ```sql CREATE TABLE tableName( columnName1 INT(8) NOT NULL, columnName2 ...., ....., INDEX [indexName] (columnName(length)) ); ``` 这种方式就比较适合在库表设计时,已经确定了索引项的情况下建立。 ## 查询、删除、指定索引 但不管通过哪种方式建立索引,本质上创建的索引都是相同的,当索引创建完成后,可通过SHOW INDEX FROM tableName;这条命令查询一个表中拥有的索引,如下:  简单的概述一下查询后,每个字段的含义: - ①Table:当前索引属于那张表。 - ②Non_unique:目前索引是否属于唯一索引,0代表是的,1代表不是。 - ③Key_name:当前索引的名字。 - ④Seq_in_index:如果当前是联合索引,目前字段在联合索引中排第几个。 - ⑤Column_name:当前索引是位于哪个字段上建立的。 - ⑥Collation:字段值以什么方式存储在索引中,A表示有序存储,NULL表无序。 - ⑦Cardinality:当前索引的散列程度,也就是索引中存储了多少个不同的值。 - ⑧Sub_part:当前索引使用了字段值的多少个字符建立,NULL表示全部。 - ⑨Packed:表示索引在存储字段值时,以什么方式压缩,NULL表示未压缩, - ⑩Null:当前作为索引字段的值中,是否存在NULL值,YES表示存在。 - ⑪Index_type:当前索引的结构(BTREE, FULLTEXT, HASH, RTREE)。 - ⑫Comment:创建索引时,是否对索引有备注信息。 这条命令在后续排除问题、性能调优时,会有不小的作用,比如可以通过分析其中的Cardinality字段值,如果该值少于数据的实际行数,那目前索引有可能失效。 在MySQL中并未提供修改索引的命令,也就说当你建错了索引,只能先删再重新建立一次,删除索引的语句如下: ```sql DROP INDEX indexName ON tableName; ``` 当然,当建立了一条索引后,也可以强制性的为SELECT语句指定索引,如下: ```sql SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....; ``` FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询,但要牢记的是:如果当前的查询SQL压根不会走指定的索引字段,哪这种方式是行不通的,这个关键字的用法是:一条查询语句在有多个索引可以检索数据时,显式指定一个索引,减少优化器选择索引的耗时。 但要注意:如果你对于你整个业务系统十分熟悉,那可以这样干。但如果不熟悉的话,还是交给优化器来自行选择,否则会适得其反!
顶部
收展
底部
[TOC]
目录
MySQL索引机制概述
MySQL的索引分类
MySQL索引的创建使用方式
MySQL各索引的优劣分析
MySQL索引失效
MySQL 正确建立索引
MySQL 正确使用索引
相关推荐
MySQL教程
MySQL命令
MySQL事务
MySQL锁机制
MySQL版本特性