mysql索引有哪些?mysql索引的相干见识介绍
本篇文章给大家带来的内容是对于mysql索引有哪些?mysql索引的相干见识介绍,有一定的参照 价值,有需要的伴侣可以参照 一下,但愿对你有所帮忙。
索引有哪些
索引就像是一本书的名目
索援用于迅速寻出在某个列中有一特定值的行,不运用索引,MySQL必需从首先笔记录开端读完备个表,直到寻出相干的行,表越大,查询数据所破费的工夫就越多,要是表中查询的列有一个索引,MySQL能够迅速抵达一个位置去搜寻数据文件,而无须查看所有数据,那么将会节俭很大一局部工夫。
长处与缺陷
长处
1、大大加速查询速度
2、所有字段类型均可以设定索引
缺陷
1、新建和保护索引需要工夫,数据量越多,耗时越多
2、索引占用存储空间,数据表中的数据也会有最大上线设定的,要是我们有批量的索引,索引文件可能会比数据文件更快达到上线值
3、当对表中的数据进行添加、删除、修改时,索引也需要动态的保护,落低了数据的保护速度
运用准则和场景
1、索引不是多多益善,需要视状况而定
2、频繁更新的表应尽量少的索引
3、频繁用于查询的字段进行构建索引
4、数据量小的字段尽量不要运用索引,查询所有数据破费的工夫比遍历索引的数据要短,索引将没有优化结果
5、字段不一样值少的字段尽量不要运用索引,如性别字段仅有男女两个不一样值。
索引分类
注意:索引是在存储引擎中实现的,也就是说不一样的存储引擎,会运用不一样的索引
MyISAM和InnoDB存储引擎:只支撑BTREE索引, 也就是说默许运用BTREE,不克不及够改换
MEMORY/HEAP存储引擎:支撑HASH和BTREE索引
1. 单列索引
一个索引只包括单个列,但一个表中可以有多个单列索引
1.1. 普通索引
MySQL中根本索引类型,没有什么限定,允许在定义索引的列中插入反复值和空值,纯粹为了查询数据更快一点。
1.2. 独一索引
索引列中的值必需是独一的,但是允许为空值
1.3. 主键索引
是一种特别的独一索引,不允许有空值
2. 组合索引
在表中的多个字段组合上新建的索引,只要在查询前提中运用了这些字段的左边字段时,索引才会被运用,运用组合索引时遵循最好左前缀规则
3. 全文索引
全文索引,只要在MyISAM引擎上才干运用,只能在CHAR,VARCHAR,TEXT类型字段上运用全文索引。全文索引,就是在一堆文字中,通过其中的某个关键字等,就能寻到该字段所属的记载行,比方有"你是个大煞笔,二货 ..." 通过大煞笔,可能就可以寻到该笔记录
4. 空间索引
空间索引是对空间数据类型的字段创立的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在新建空间索引时,运用SPATIAL关键字。请求,引擎为MyISAM,新建空间索引的列,必需将其声明为NOT NULL
索引方式
运用准则:要是值的悬殊性大,而且以等值查寻(=、 <=>、in)为主,Hash索引是更高效的选中,它有O(1)的查寻复杂度;要是值的悬殊性相对较差,而且以范畴查寻为主,B树是更好的选中,它支撑范畴查寻。
B-Tree索引
B树索引拥有范畴查寻和前缀查寻的能力,关于有N节点的B树,检索一笔记录的复杂度为O(LogN)。相当于二分查寻。
Hash索引
哈希索引只能做等于查寻,但是不管多大的Hash表,查寻复杂度都是O(1)。
索引新建和删除
新建
建表时新建
CREATE TABLE 表名[字段名 数据类型] [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) [ASC|DESC]
示例:
CREATE TABLE `NewTable` ( `id` INT NOT NULL AUTO_INCREMENT, `username` VARCHAR (255) NOT NULL, `name` VARCHAR (255) NOT NULL, `sex` TINYINT NOT NULL DEFAULT 0, `address` VARCHAR (255) NULL, PRIMARY KEY (`id`), # 主键索引 INDEX `name` (`name`) USING BTREE, # 普通索引 UNIQUE INDEX `username` (`username`) USING BTREE # 独一索引 INDEX `u_n_a` (`username`, `name`,`address`) USING BTREE # 组合索引 );
已存在表新建
ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
示例:
ALTER TABLE `test` ADD PRIMARY KEY (`id`), # 主键索引 ADD INDEX `name` (`name`) USING BTREE , # 普通索引 ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 独一索引 ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 组合索引
删除索引
ALTER TABLE 表名 DROP INDEX 索引名。
示例:
ALTER TABLE `test` DROP PRIMARY KEY, DROP INDEX `username`, DROP INDEX `name`, DROP INDEX `u_n_a`;
更新索引
先删后建
ALTER TABLE `test` DROP INDEX `username` , ADD UNIQUE INDEX `username1` (`username`) USING BTREE , DROP INDEX `name` , ADD INDEX `name2` (`name`) USING BTREE , DROP INDEX `u_n_a` , ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;
索引失效状况
1. 组合字段不遵循最好左前缀规则
2. 依稀查询,如like '%test
# 索引生效 select * from `test` where `name` like "123"; # 索引生效 select * from `test` where `name` like "123%"; # 索引失效 select * from `test` where `name` like "%123"; # 索引失效 select * from `test` where `name` like "%123%";
3. 在索引列上做如下任何操纵(盘算,函数,(主动或者手动)类型装换),会致使索引失效而致使全表扫描
如 sex 字段上增加索引
# 索引失效 select * from `test` where `sex`*0.5 = 1
4. 范畴索引(>,<,between and)后,无法命中组合索引右边的列
构建索引
ALTER TABLE `test` ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;
示例:
# 命中全部 select * from `test` where `sex` = 1 and `name` = 'a'; # 命中局部,sex命中,name失效 select * from `test` where `sex` > 1 and `name` = 'a';
5. !=, is null, is not null 没法运用索引
6. 字符串字段的值不加单引号(数字不报错,英文报错)索引失效
构建索引
ALTER TABLE `test` ADD INDEX `name` (`name`) USING BTREE ;
示例
# 索引失效 select * from `test` where `name` = 123; # 索引生效 select * from `test` where `name` = '123';
7. or 前提致使索引失效
构建索引
ALTER TABLE `test` ADD INDEX `sex` (`sex`) USING BTREE ; ADD INDEX `n_u` (`name`, `username`) USING BTREE ;
示例:
# 索引不生效 select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 # 索引sex生效 select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )
附录
最好左前缀规则
要是索引了多列,要恪守最左前缀规则。指的是查询要从索引的最左前列开端而且不跳过索引中的列
如下构建索引
ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;
如下查询状况
# 命中局部 select * from `test` where `username` = 'aaa'; # 命中局部 select * from `test` where `username` = 'aa' and `address` = 'aaa'; # 全命中 select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a'; # 不命中,首先前提字段不是username select * from `test` where `address` = 'aaa';
以上就是mysql索引有哪些?mysql索引的相干见识介绍的细致内容,更多请关注 百分百源码网 其它相干文章!