本篇文章给人人带来的内容是关于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索引的相干学问引见的细致内容,更多请关注ki4网别的相干文章!