重庆小潘seo博客

当前位置:首页 > 重庆网络营销 > 小潘杂谈 >

小潘杂谈

mysql索引是什么?mysql索引的相关知识介绍

时间:2020-09-22 16:00:07 作者:重庆seo小潘 来源:
本篇文章给大家带来的内容是关于mysql索引是什么?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= 14. 范围索引(>,<,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';# 不命中,第一条件字段不是usernameselect * from `test` where`address` = 'aaa';以上就是mysql索引是什么?mysql索引的相关知识介绍的详细内容,更多请关注小潘博客其它相关文章!