mysql索引分类以及创建使用

全屏阅读
  • 基本信息
  • 作者:
  • 作者已发布:923篇文章
  • 发布时间:2023年08月31日 0:07:04
  • 所属分类:Mysql优化, PHP+MySql
  • 阅读次数:196次阅读
  • 标签:

一、索引

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

二、索引类型

Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。

1. FULLTEXT

即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH

由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引。

HASH索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。但是,这种高效是有条件的,即只在“=”和“in”条件下高效,对于范围查询、排序及组合索引仍然效率不高。

3. BTREE

BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中(二叉树),每次查询都是从树的入口root开始,依次遍历node,获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE

RTREE在MySQL很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE,RTREE的优势在于范围查找。

 

按字段特性分类

MySQL索引按字段特性分类可分为:主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。

    1. 主键索引(PRIMARY KEY)
      建立在主键上的索引被称为主键索引,一张数据表只能有一个主键索引,索引列值不允许有空值,通常在创建表时一起创建。

    2. 唯一索引(UNIQUE)
      建立在UNIQUE字段上的索引被称为唯一索引,一张表可以有多个唯一索引,索引列值允许为空,列值中出现多个空值不会发生重复冲突。

    3. 普通索引(INDEX)
      建立在普通字段上的索引被称为普通索引。

    4. 全文索引(FULLTEXT)
      MyISAM 存储引擎支持Full-text索引,用于查找文本中的关键词,而不是直接比较是否相等。Full-text索引一般使用倒排索引实现,它记录着关键词到其所在文档的映射。

     

     

    三、索引种类

    • 普通索引:仅加速查询

    • 唯一索引:加速查询 + 列值唯一(可以有null)

    • 主键索引:加速查询 + 列值唯一(不可以有null)+ 表中只有一个

    • 组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并

    • 全文索引:对文本的内容进行分词,进行搜索 

    ps.

    索引合并,使用多个单列索引组合搜索
    覆盖索引,select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖

    四、操作索引

     

    //创建索引语法CREATE TABLE table_name[col_name data type]
    [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

     

     

    1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;

    2、index和key为同义词,两者作用相同,用来指定创建索引

    3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;

    4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;

    5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;

    6、asc或desc指定升序或降序的索引值存储

     

     主键索引:

    # 查看创建的索引关键词PRIMARY KEY (`id`)

     

     唯一索引:

    • 索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一

    # 创建唯一索引mysql> alter table t1 add unique idx_u1(`u1`); 
    # 查看创建的索引关键词UNIQUE KEY `idx_u1` (`u1`)

     

     普通索引-单列索引:

    • 即索引创建在单个列上

     

    # 创建单列索引mysql> alter table t1 add index idx_u1(`u1`); 
    # 查看创建的索引关键词KEY `idx_u1` (`u1`)

     

     

      普通索引-多列索引:

    • 创建多列索引时记得遵循最左匹配原则,把区分度较高的字段放前面

    # 创建多列索引mysql> alter table t1 add index idx_u1_u2(`u1`,`u2`); 
    # 查看创建的多列索引关键词KEY `idx_u1_u2` (`u1`,`u2`)

     

     

     案例:

    复制代码

    DROP TABLE IF EXISTS `{dbprefix}order_cart`;
    CREATE TABLE IF NOT EXISTS `{dbprefix}order_cart` (
      `id` BIGINT(18) unsigned NOT NULL AUTO_INCREMENT,
      `mid` varchar(100) NOT NULL COMMENT '模块mid',
      `uid` int(10) unsigned NOT NULL COMMENT '购买者',
      `agent` varchar(100) NOT NULL COMMENT '客户端编码',
      `cid` int(10) unsigned NOT NULL COMMENT '商品id',
      `price` decimal(10,2) NOT NULL COMMENT '售价',
      `quantity` int(10) NOT NULL COMMENT '数量',
      `sku_name` text NOT NULL COMMENT '商品属性名称',
      `sku_value` varchar(255) NOT NULL COMMENT '商品属性参数',
      `title` varchar(255) NOT NULL COMMENT '商品名称',
      `thumb` varchar(255) NOT NULL COMMENT '缩略图',
      `url` varchar(255) NOT NULL COMMENT '商品地址',
      `inputtime` int(10) NOT NULL COMMENT '加入时间',
      PRIMARY KEY (`id`),  KEY `mid` (`mid`),  KEY `uid` (`uid`),  KEY `agent` (`agent`),  KEY `price` (`price`),  KEY `sku_value` (`sku_value`),  KEY `inputtime` (`inputtime`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='我的购物车表';

    复制代码

     

    自动草稿

     

     

     

     

     

     

     

     

    顶一下
    (0)
    100%
    订阅 回复
    踩一下
    (0)
    100%
    » 郑重声明:本文由mpxq168发布,所有内容仅代表个人观点。版权归恒富网mpxq168共有,欢迎转载, 但未经作者同意必须保留此段声明,并给出文章连接,否则保留追究法律责任的权利! 如果本文侵犯了您的权益,请留言。

    目前有 0 条留言 其中:访客:0 条, 博主:0 条

    给我留言

    您必须 [ 登录 ] 才能发表留言!