本篇文章给大家带来的内容是关于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类型字段上使用全文索引。全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有”你是个大煞笔，二货 …” 通过大煞笔，可能就可以找到该条记录
纳米搜索
纳米搜索：360推出的新一代AI搜索引擎
30 查看详情
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';# 不命中，第一条件字段不是usernameselect * from `test` where `address` = 'aaa';
以上就是mysql索引是什么？mysql索引的相关知识介绍的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/379624.html
微信扫一扫 
支付宝扫一扫 
