摘要:在创建空间索引时,使用关键字。相当于二分查找。索引哈希索引只能做等于查找,但是无论多大的表,查找复杂度都是。
索引是什么
索引就像是一本书的目录
索引用于快速找出在某个列中有一特定值的行,不使用索引,MySQL必须从第一条记录开始读完整个表,直到找出相关的行,表越大,查询数据所花费的时间就越多,如果表中查询的列有一个索引,MySQL能够快速到达一个位置去搜索数据文件,而不必查看所有数据,那么将会节省很大一部分时间。
优点与缺点 优点大大加快查询速度
所有字段类型均可以设置索引
缺点创建和维护索引需要时间,数据量越多,耗时越多
索引占用存储空间,数据表中的数据也会有最大上线设置的,如果我们有大量的索引,索引文件可能会比数据文件更快达到上线值
当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度
使用原则和场景索引不是越多越好,需要视情况而定
频繁更新的表应尽量少的索引
频繁用于查询的字段进行构建索引
数据量小的字段尽量不要使用索引,查询所有数据花费的时间比遍历索引的数据要短,索引将没有优化效果
字段不同值少的字段尽量不要使用索引,如性别字段仅有男女两个不同值。
索引分类注意:索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引
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"; # 不命中,第一条件字段不是username select * from `test` where `address` = "aaa";
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/48351.html
摘要:的插件式存储引擎主要包括等。和在严格意义来说,并不能算作一个存储引擎。实现外键实现了外键引用这一数据库的重要特性,使在数据库端控制部分数据的完整性成为可能。 什么是存储引擎 mysql中的数据用各种不同的技术存储在文件(或者内存)中。 这些技术中每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术,你能够获得额外的速度和功能,从而...
摘要:实际应用中,查询的返回结果会有条记录。修改保存文件后,重启服务。通常优化至少到级别,最好能优化到指出使用哪个索引在该表找到行记录。如果该值为,说明没有使用索引,可以建立索引提高性能显示实际使用的索引。 ...
摘要:如果我们要进行范围查找,例如查找为的人,哈希表同样不支持,只能遍历全表。索引字段通过哈希映射成哈希码,如果很多字段都刚好映射到相同值的哈希码的话,那么形成的索引结构将会是一条很长的链表,这样的话,查找的时间就会大大增加。MySQL 索引你真的懂吗?这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则? 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 sh...
摘要:串行最高的隔离级别,完全服从的隔离级别。但是这将严重影响程序的性能。此外,垂直分区可以简化表的结构,易于维护。 我自己总结的Java学习的一些知识点以及面试问题,目前已经开源,会一直完善下去,欢迎建议和指导欢迎Star: https://github.com/Snailclimb/Java_Guide 书籍推荐 《高性能MySQL : 第3版》 文字教程推荐 MySQL 教程(菜鸟教程...
摘要:串行最高的隔离级别,完全服从的隔离级别。但是这将严重影响程序的性能。此外,垂直分区可以简化表的结构,易于维护。 我自己总结的Java学习的一些知识点以及面试问题,目前已经开源,会一直完善下去,欢迎建议和指导欢迎Star: https://github.com/Snailclimb/Java_Guide 书籍推荐 《高性能MySQL : 第3版》 文字教程推荐 MySQL 教程(菜鸟教程...
阅读 2112·2021-09-28 09:45
阅读 3405·2021-09-24 09:48
阅读 2035·2021-09-22 15:49
阅读 2925·2021-09-08 16:10
阅读 2784·2021-09-04 16:45
阅读 399·2019-12-30 16:05
阅读 2371·2019-12-27 11:52
阅读 1318·2019-08-30 15:54