mysql单列索引怎么用 mysql 多列索引

【mysql】索引类型的划分

了解mysql的索引类型的时候,我觉得按照以下4中方式划分逻辑是比较清晰的。

创新互联建站主要从事成都网站建设、网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务盐津,十年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:13518219792

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

mysql 主键索引,联合索引,单列索引使用场景

表button 

CREATE TABLE `button` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,  --主键索引

`button_name` varchar(45) NOT NULL COMMENT '功能名称',

`app_id` bigint(20) NOT NULL,

`permission_id` bigint(20) DEFAULT NULL,  -- permission_id 和 app_id 联合索引。

`api_id` bigint(20) NOT NULL, --api_id单独索引

PRIMARY KEY (`id`),

KEY `index_app_permission_lianhe` (`permission_id`,`app_id`) USING BTREE,

KEY `index_api_id_dange` (`api_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

主键索引,单独索引,组合索引使用场景及优化

表button 有3个索引,分别是:id主键,联合索引(permission_id,app_id),api_id(单列索引)

button_name 无索引

查询where条件中:

主键索引:

1、主键索引与联合索引同时存在,使用主键索引

2、主键索引与单个索引同时存在,使用主键索引

结论:只要主键索引在,使用主键索引。

联合索引 :

1、联合索引与单列索引列 同时存在,使用单列索引

2、联合索引中列顺序颠倒无影响。

3、联合索引实行最左侧原则,即:单独查询条件中只有permission_id可以使用联合索引,单独查询条件中只有app_id不实用联合索引。

4、如果查询条件中只有app_id,但是select 条件中有 permission_id,则也使用联合索引。

5、select id,app_id from button where app_id=1001; 使用联合索引

6、explain select id,app_id,button_name from button where app_id=1001;不使用联合索引

结论:索引优先级:主键索引,单列索引,组合索引

联合索引中遵从最左侧列原则。

当查询条件和返回结果中仅仅包含联合索引中索引项,也使用联合索引。如第4条。

当查询条件中出现联合索引中非最左侧索引列,返回结果中含义联合索引中的列或者主键则也使用联合索引。

单个索引:

1、查询条件中有单列索引,则使用,无不使用。

事例:

MySQL索引机制(详细+原理+解析)

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单列索引怎么用 mysql 多列索引
文章出自:http://pwwzsj.com/article/doddehe.html