一切MySQL列范例能够被索引。依据存储引擎定义每一个表的最大索引数和最大索引长度。
一切存储引擎支撑每一个表最少16个索引,总索引长度最少为256字节。大多数存储引擎有更高的限定。
索引的存储范例现在只要两种(btree和hash),细致和存储引擎情势相干:
MyISAM btree
InnoDB btree
MEMORY/Heap hash,btree
默许状况MEMORY/Heap存储引擎运用hash索引
MySQL的btree索引和hash索引的区分
hash 索引构造的特别性,其检索效力异常高,索引的检索能够一次定位,不像btree(B-Tree)索引须要从根节点到枝节点,末了才接见到页节点如许屡次的IO接见,所以 hash 索引的查询效力要远高于 btree(B-Tree) 索引。
虽然 hash 索引效力高,然则 hash 索引自身由于其特别性也带来了许多限定和弊病,重要有以下这些。
(1)hash 索引仅仅能满足=,<=>,IN,IS NULL或许IS NOT NULL查询,不能运用局限查询。
由于 hash 索引比较的是举行 hash 运算以后的 hash 值,所以它只能用于等值的过滤,不能用于基于局限的过滤,由于经由响应的 hash 算法处置惩罚以后的 hash 值的大小关联,并不能保证和hash运算前完整一样。
(2)hash 索引没法被用来防止数据的排序操纵。
由于 hash 索引中寄存的是经由 hash 盘算以后的 hash 值,而且hash值的大小关联并不一定和 hash 运算前的键值完整一样,所以数据库没法应用索引的数据来防止任何排序运算;
(3)hash 索引不能应用部份索引键查询。
关于组合索引,hash 索引在盘算 hash 值的时刻是组合索引键兼并后再一起盘算 hash 值,而不是零丁盘算 hash 值,所以经由过程组合索引的前面一个或几个索引键举行查询的时刻,hash 索引也没法被应用。
(4)hash 索引在任何时刻都不能防止表扫描。
前面已晓得,hash 索引是将索引键经由过程 hash 运算以后,将 hash运算效果的 hash 值和所对应的行指针信息寄存于一个 hash 表中,由于差异索引键存在雷同 hash 值,所以纵然取满足某个 hash 键值的数据的纪录条数,也没法从 hash 索引中直接完成查询,照样要经由过程接见表中的现实数据举行响应的比较,并获得响应的效果。
(5)hash 索引碰到大批hash值相称的状况后机能并不一定就会比B-Tree索引高。
关于选择性比较低的索引键,假如竖立 hash 索引,那末将会存在大批纪录指针信息存于统一个 hash 值相干联。如许要定位某一条纪录时就会异常贫苦,会糟蹋屡次表数据的接见,而形成团体机能低下
B-Tree 索引是 MySQL 数据库中运用最为频仍的索引范例,除了 Archive 存储引擎以外的其他一切的存储引擎都支撑 B-Tree 索引。不仅仅在 MySQL 中是云云,现实上在其他的许多数据库治理体系中B-Tree 索引也一样是作为最重要的索引范例,这重要是由于 B-Tree 索引的存储构造在数据库的数据检 索中有异常优秀的表现。
平常来说, MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的构造来存储的,也就是一切现实须要的数据都寄存于 Tree 的 Leaf Node ,而且到任何一个 Leaf Node 的最短途径的长度都是完整雷同的,所以我们人人都称之为 B-Tree 索引固然,能够种种数据库(或 MySQL 的种种存储引擎)在寄存本身的 B-Tree 索引的时刻会对存储构造稍作革新。
如 Innodb 存储引擎的 B-Tree 索引现实运用的存储构造现实上是 B+Tree ,也就是在 B-Tree 数据构造的基础上做了很小的革新,在每一个Leaf Node 上面出了寄存索引键的相干信息以外,还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息,这重要是为了加速检索多个相邻 Leaf Node 的效力斟酌。
在 Innodb 存储引擎中,存在两种差异情势的索引,一种是 Cluster 情势的主键索引( Primary Key ),别的一种则是和其他存储引擎(如 MyISAM 存储引擎)寄存情势基础雷同的平常 B-Tree 索引,这类索引在 Innodb 存储引擎中被称为 Secondary Index 。
在 Innodb 中假如经由过程主键来接见数据效力是异常高的,而假如是经由过程 Secondary Index 来接见数据的话, Innodb 起首经由过程 Secondary Index 的相干信息,经由过程响应的索引键检索到 Leaf Node以后,须要再经由过程 Leaf Node 中寄存的主键值再经由过程主键索引来猎取响应的数据行。
MyISAM 存储引擎的主键索引和非主键索引差异很小,只不过是主键索引的索引键是一个唯一且非空 的键罢了。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储构造也基础雷同,重要的区分只是 MyISAM 存储引擎在 Leaf Nodes 上面出了寄存索引键信息以外,
再寄存能直接定位到 MyISAM 数据文件中响应的数据行的信息(如 Row Number ),但并不会寄存主键的键值信息。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表能够有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。
MySQL索引范例包含:
(1)平常索引,这是最基础的索引,它没有任何限定。它有以下几种竖立体式格局:
-- 竖立索引
CREATE INDEX indexName ON mytable(username(10)); -- 单列索引
-- CREATE INDEX indexName ON mytable(username(10),city(10)); -- 组合索引
-- indexName为索引名,mytable表名,username和city为列名,10为前缀长度,即索引在该列从最左字符最先存储的信息长度,单元字节
-- 假如是CHAR,VARCHAR范例,前缀长度能够小于字段现实长度;假如是BLOB和TEXT范例,必需指定 前缀长度,下同。
-- 修正表构造来竖立索引
ALTER TABLE mytable ADD INDEX indexName (username(10));
-- ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));
-- 此处 indexName 索引名可不写,体系自动赋名 username ,username_2 ,username_3,...
-- 竖立表的时刻直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
INDEX indexName (username(10))-- INDEX indexName (username(10),city(10))
);
-- 此处 indexName 索引名一样能够省略
(2)唯一索引,它与前面的平常索引相似,差异的就是:索引列的值必需唯一,但许可有空值。假如是组合索引,则列值的组合必需唯一。它有以下几种竖立体式格局(仅仅在竖立平常索引时关键字 INDEX 前加 UNIQUE):
-- 竖立索引
CREATE UNIQUE INDEX indexName ON mytable(username(10));
-- 修正表构造来竖立索引
ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10));-- 也可简写成 ALTER TABLE mytable ADD UNIQUE indexName (username(10));
-- 竖立表的时刻直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
UNIQUE INDEX indexName (username(10)) -- 也可简写成 UNIQUE indexName (username(10))
);
(3)主键索引,它是一种特别的唯一索引,不许可有空值。在建表的时刻同时竖立的主键即为主键索引
主键索引无需定名,一个表只能有一个主键。主键索引同时但是唯一索引或许全文索引,但唯一索引或全文索引不能共存在统一索引:
-- 修正表构造来竖立索引ALTER TABLE mytable ADD PRIMARY KEY (id);
-- 竖立表的时刻直接指定CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,PRIMARY KEY(id)
);
(4)全文索引,InnoDB存储引擎不支撑全文索引:
-- 竖立索引CREATE FULLTEXT INDEX indexName ON mytable(username(10));
-- 修正表构造来竖立索引ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));
-- 也可简写成 ALTER TABLE mytable ADD FULLTEXT indexName (username(10));
-- 竖立表的时刻直接指定CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
age INT,
FULLTEXT INDEX indexName (username(10))
-- 也可简写成 FULLTEXT indexName (username(10)))ENGINE=MYISAM;
-- 建表时竖立全文索引,要设置该表的存储引擎为MYISAM,新版mysql默许InnoDB存储引擎不支撑全文索引
-- 删除索引DROP INDEX indexName ON mytable;
虽然索引大大提高了查询速率,同时却会下降更新表的速率,如对表举行INSERT、UPDATE和DELETE。由于更新表时,MySQL不仅要保留数据,还要保留一下索引文件。
竖立索引会占用磁盘空间的索引文件。平常状况这个题目不太严峻,但假如你在一个大表上竖立了多种组合索引,索引文件的会膨胀很快。
以上是我对Mysql的竖立索引总结出来的东西,愿望以后会对人人有所协助。
相干文章:
PHP中安插数据到myql显现乱码
myql5.7.7优化设置参数_MySQL
以上就是给Myql竖立索引的细致内容,更多请关注ki4网别的相干文章!