十年网站开发经验 + 多家企业客户 + 靠谱的建站团队
量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决
MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。
成都创新互联公司专注于巩留企业网站建设,响应式网站开发,商城系统网站开发。巩留网站建设公司,为巩留等地区提供建站服务。全流程按需求定制制作,专业设计,全程项目跟踪,成都创新互联公司专业和态度为您提供的服务
集一个索引包含多个列(最左前缀匹配原则)
索引列的值必须唯一,但允许有空值
全文索引为FUllText,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值,全文索引可以在CHAR,VARCHAR,TEXT类型列上创建
设定主键后数据会自动建立索引,InnoDB为聚簇索引
即一个索引只包含单个列,一个表可以有多个单列索引
覆盖索引是指一个查询语句的执行只用从所有就能够得到,不必从数据表中读取,覆盖索引不是索引树,是一个结果,当一条查询语句符合覆盖索引条件时候,MySQL只需要通过索引就可以返回查询所需要的数据,这样避免了查到索引后的回表操作,减少了I/O效率
查看索引
列名解析:
删除索引
查看:
删除前:
删除后:
普通的索引,没有什么介绍
查看:(注意和前缀索引Sub_part的区别)
当索引的列是unique的时候,会生成唯一索引,唯一索引关于null有下列两种情况
SQLSERVER 下的唯一索引的列,允许null值,但最多允许有一个空值
MYSQL下的唯一索引的列,允许null值,并且允许多个空值
查看:
会建立两个索引,一个非聚簇索引,一个是唯一索引
结果:
可以插入两个空值(明人不说暗话,我喜欢MySQL)
一方面,它不会索引所有字段所有字符,会减小索引树的大小.
另外一方面,索引只是为了区别出值,对于某些列,可能前几位区别很大,我们就可以使用前缀索引。
一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。
查看:
查看:
复合索引的最左前缀匹配原则 :
对于复合索引,查询在一定条件才会使用该索引
减少开销。 建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
覆盖索引。 对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
效率高。 索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w。
在模糊搜索中很有效,搜索全文中的某一个字段,可以参考这篇博文
:
我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。
查看:
我们插入进去的时候,数据的id都是乱序的,为什么这里最后select查询出来的结果都是进行了排序?
这是因为InnoDB索引底层实现的是B+tree,B+tree具有下列的特点:
所以上面的排序是为了使用B+tree的结构 ,B+tree为了范围搜索,将主键按照从小到大排序后,拆分成节点。后续还有新的节点进入的时候,和B-tree相同的操作,会进行分裂。
一般来说,聚簇索引的B+tree都是三层
InnoDB中主键索引一定是聚簇索引,聚簇索引一定是主键索引。
为什么这里辅助索引叶子结点不直接存储数据呢?
MYISAM只有非聚簇索引,索引最终指向的都是物理地址。
Q:既然有回表的存在,那么聚簇索引的优势在哪里?
Q:主键索引作为聚簇索引需要注意什么
在查询语句中使用LIke关键字进行查询时,如果匹配字符串的第一个字符为"%",索引不会使用。如果“%”不是在第一位,索引就会使用
多列索引是在表的多个字段上创建的索引,满足最左前缀匹配原则,索引才会被使用
查询语句只有Or关键字时候,如果OR前后的两个条件都是索引,这这次查询将会使用索引,否则Or前后有一个条件的列不是索引,那么查询中将不使用索引
了解mysql的索引类型的时候,我觉得按照以下4中方式划分逻辑是比较清晰的。
1.存储结构 2.物理存储 3.作用字段 4.功能
按照数据存储的结构可以分B树索引和hash索引。
又称为 BTREE 索引,目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构。
基于这种树形数据结构,表中的每一行都会在索引上有一个对应值。因此,在表中进行数据查询时,可以根据索引值一步一步定位到数据所在的行。
查询必须从索引的最左边的列开始。
查询不能跳过某一索引列,必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。
也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。
其中,MEMORY 存储引擎可以支持 B-树索引和 HASH 索引,且将 HASH 当成默认索引。
HASH 索引不是基于树形的数据结构查找数据,而是根据索引列对应的哈希值的方法获取表的记录行。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较,如“=”“IN()”或“=”。
HASH 索引不支持键的部分匹配,因为在计算 HASH 值的时候是通过整个索引值来计算的。
聚集索引是按照所以把数据排好序了,所以一个表只能存在一个聚集索引,其它的都是非聚集索引。
因这个特性,聚集索引是查询数据范围的时候有很大的性能优势。
但是也需要注意的是如果频繁更新的列不适合设置为聚集索引,
原因很简单,每次更新都需要从新排序,频繁的更新给的压力也大。
如果不指定的话,默认主键为聚集索引。
一个表里除了一个聚集索引外其他的都是非聚集索引,虽然不能把数据按照索引排序,但是索引数据是可以排序的。
所以非聚集索引查询范围的时候是先找索引列的范围,再通过这个索引查询行的值。
单列索引即一个索引只包含单个列。
组合索引指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合
Primary Key(聚集索引):InnoDB存储引擎的表会存在主键(唯一非null),如果建表的时候没有指定主键,则会使用第一非空的唯一索引作为聚集索引,否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
Key(普通索引):是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值
Unique(唯一索引):索引列的值必须唯一,但允许有空值。若是组合索引,则列值的组合必须唯一。
主键索引是一种特殊的唯一索引,不允许有空值。
既不是主键索引也不是唯一索引的一般索引。
FULLTEXT(全文索引):全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。
全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
空间索引主要用于地理空间数据类型 GEOMETRY。
下面是 mysql官网给出的几个存储引擎和索引之间的关系 。
欢迎大家的意见和交流
email: li_mingxie@163.com
先正面回答你的问题
数据是否重复不是建立索引的重要依据,甚至都不是依据。
只要不完全重复(所有元组的该元素都一样),那么建立索引就是有意义的。
即使当前数据完全重复,也不是不能建立索引,这种情况有点复杂,不细说了。
对于你后面的疑问,可以给你一个如何建立索引的忠告,“如何查就如何建”。
索引的建立,唯一的原因就是为了查询(广义的查询),实际上建立索引会使得数据存储所占空间变大,有时索引所占的空间会查过数据本身的空间。索引的建立也会使得数据插入时变慢,特殊情况下,慢的难以忍受,所以dba的重要工作之一,就是检查索引层级并优化。
索引建立的唯一好处,就是按照索引查询时,变快了。type,status这2个字段是否适合建立索引,就要看你是否要按照这2个字段进行检索。而检索的顺序决定了如何建立索引。
对于索引类型和索引方式,我建议就
normal
和
btree
就适用于大多数情况。若你参与的是一个大数据处理项目,对数据存储和检索有特别要求,那么需要分析多个层面,比如数据吞吐量、数据的方差、平均差等等很多参数才考虑是否用聚集索引等(mysql好像还没聚集索引),至于是否是唯一索引,我建议不使用,即使能判定数据是唯一的也不要用,全文索引也没有必要。
在涉及LBS的服务开发过程中,经常需要存储地理空间的位置并进行一定计算(附近商家等需求),本文主要介绍mysql对于LBS的支持。
Mysql的空间扩展主要提供一下几个方面的功能:
其中前两点对InnoDB,MyISAM,NDB,ARCHIVE等mysql存储引擎都支持,第三点只有对InnoDB和MyISAM的支持,由于InnoDB的支持行锁以及事务的特性,现在基本上已经是默认存储引擎了,所以本文以下内容都默认使用InnoDB。
创建空间列以及空间索引的语句如下:
Mysql的空间数据类型与OpenGIS的数据类型相对应。
Mysql的空间数据有不同表示格式,其中咱能看懂的也就第一种
因为上文提到了SRID,这里说下什么是SRID,SR是指Spatial Reference,也就是我们常说的空间参考系,mysql支持卡迪尔坐标系和地理坐标系,其中地理坐标系又有好多种,下面说几种常用的空间参考系
Mysql的所有空间坐标系都存在表 mysql.st_spatial_reference_system 中,这个表是隐藏的,看不见的,但是你可以通过 infomation_shcema.st_spatial_reference_system 中查看参考系的信息,这个表就是 mysql.st_spatial_reference_system 的一个视图的实现。
mysql的空间索引的数据结构是R树,R树实际上就是多维的B树,B树的数据结构在我的另一篇博客中有介绍,这里就不展开了,说几点在应用的时候需要注意的。
最后转一篇博文