Linux——MySQL索引

最新推荐文章于 2024-02-18 23:22:38 发布

有趣的灵魂！

最新推荐文章于 2024-02-18 23:22:38 发布

阅读量171

点赞数

分类专栏： MySQL Linux系列文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45191791/article/details/112251650

版权

Linux系列同时被 2 个专栏收录

73 篇文章 0 订阅

订阅专栏

MySQL

20 篇文章 1 订阅

订阅专栏

一、简介

1.1 索引概述

Mysql索引的简历对于Mysql的高效运行是很重要的，索引可以大大提高Mysql的检索速度，创建索引时，需要确保索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件），建立索引会占用磁盘空间的索引文件！

每种搜索引擎支持的索引是不同的，如下：

MylSAM、InnoDB支持btree索引；
Memory支持btree和hash索引；

1.2 索引的优势：

加快查询速度
创建唯一索引来保证数据表中数据的唯一性
实现数据的完整性，加速表和表之间的连接
减少分组和排序的时间

1.3索引的劣势：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

1.4 索引的分类

类型	描述
普通索引	MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
唯一索引	索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
单列索引	只是一个索引只包含单个列，一个表可以有多个单列索引
组合索引	在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
全文索引	用于全文搜索，只有MyISAM存储引擎支持，并且只为CHAR\VARCHAR和TEXT 列。索引总是对整个列进行，不支持局部索引，适合大型数据的表创建
空间索引	必须在MyISAM类型的表中创建，且空间类型的字段必须为非空

1.5 索引规则

创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能。因为当表中的数据更改时，索引也会进行调整和更新。
数据量小得表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要长。
避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引。
在条件表达式中经常用到的不同值较多的列创建索引
当唯一性是某种数据本身的特征时，我们创建唯一性索引
在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建组合索引

创建索引的方式：

创建表时创建索引

mysql> create table xxx
    -> (
        ......
        索引类型  index 索引名 字段名
    -> );

创建表后添加索引

mysql> create table xxx
......
mysql> alter tbale xxx add 索引类型 index 索引名 字段名 ;

创建表后创建索引

mysql> create table xxx
......
mysql> create 索引类型 index  索引名 on 表名 字段名 ;

PS：不添加索引名默认为字段名，但是用create index 添加索引名必须加索引名

二、创建表时创建索引

2.1 普通索引

mysql> create table t1
    -> (
    -> a int not null,
    -> b varchar(255) not null , 
    -> index (a)    #此处的索引名可以自己命名，在不命名的情况默认为所设置的字段名为索引名！！！
    -> );
Query OK, 0 rows affected (0.11 sec)

# 查看创建的索引
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` varchar(255) NOT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# explain可以判断索引是否正在被使用
mysql> explain select * from t1 where a=1999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref    # TYPE的取值范围 System const ref eq_ref index all range
possible_keys: a
          key: a
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

2.2 唯一索引

mysql> create table t2 
    -> ( 
    -> a int not null,
    -> b char(30) not null,
    -> unique index weiyi(a)    #unique  唯一
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` char(30) NOT NULL,
  UNIQUE KEY `weiyi` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.3 单列索引

mysql> create table t3
    -> (
    -> a int not null,
    -> b char(50) null,
    -> index danlie (b)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` char(50) DEFAULT NULL,
  KEY `danlie` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.4 组合索引

遵循最左前缀原则。最左前缀索引最左边的列来匹配行
组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀

mysql> create table t4
    -> ( 
    -> a int not null,
    -> b char(30) not null,
    -> c int not null,
    -> index zuhe (a,b,c)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `a` int(11) NOT NULL,
  `b` char(30) NOT NULL,
  `c` int(11) NOT NULL,
  KEY `zuhe` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

2.5 全文索引

mysql> create table t5
    -> (
    -> a int not null,
    -> b char(30) not null,
    -> fulltext index quanwen(b(100))  #fulltext：全文索引
    -> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `a` int(11) NOT NULL,
  `b` char(30) NOT NULL,
  FULLTEXT KEY `quanwen` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.6 空间索引

MySQL中的空间数据类型有4中，分别是：geometry、point、linstring和polygon

mysql> create table t6
    -> (
    -> a int not null,
    -> b geometry not null,
    -> spatial index kongjian (b)   #spatial：空间索引
    -> ) engine= myisam;
Query OK, 0 rows affected (0.10 sec)

mysql> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `a` int(11) NOT NULL,
  `b` geometry NOT NULL,
  SPATIAL KEY `kongjian` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

三、创建表后添加索引

3.1 创建表

mysql> create table t7
    -> (
    -> a int not null,
    -> b int not null,
    -> c char(30) not null,
    -> d char(30) not null,
    -> e geometry not null，
    -> f char(30) not null
    -> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` char(30) NOT NULL,
  `d` char(30) NOT NULL,
  `e` geometry NOT NULL,
  `e` geometry NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.2 添加索引

#普通
mysql> alter table t7 add index putong(a);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#唯一
mysql> alter table t7 add unique index weiyi(b);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#单列
mysql> alter table t7 add index danyi(c);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#全文
mysql> alter table t7 add fulltext index quanwen (d);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#空间
mysql> alter table t7 add  spatial index kongjian(e);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#组合
mysql> alter table t7 add index zuhe(f);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table t7 add index zuhe(a,b,c,f);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.3 查看索引

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` char(30) NOT NULL,
  `d` char(30) NOT NULL,
  `e` geometry NOT NULL,
  `f` char(30) NOT NULL,
  UNIQUE KEY `weiyi` (`b`),
  KEY `putong` (`a`),
  KEY `danyi` (`c`),
  SPATIAL KEY `kongjian` (`e`),
  KEY `zuhe` (`f`),
  FULLTEXT KEY `quanwen` (`d`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> show index from t7\G
*************************** 1. row ***************************
        Table: t7
   Non_unique: 0
     Key_name: weiyi
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: putong
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: danyi
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: kongjian
 Seq_in_index: 1
  Column_name: e
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: zuhe
 Seq_in_index: 1
  Column_name: f
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 6. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: quanwen
 Seq_in_index: 1
  Column_name: d
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
6 rows in set (0.00 sec)

四、创建表后创建索引

4.1 创建表

mysql> create table t8
    -> (
    -> a int not null,
    -> b int not null,
    -> c char(30) not null,
    -> d char(30) not null,
    -> e char(30) not null,
    -> f geometry not null
    -> ) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` char(30) NOT NULL,
  `d` char(30) NOT NULL,
  `e` char(30) NOT NULL,
  `f` geometry NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.2 创建索引

#普通
mysql> create index putong on t8(a);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#唯一
mysql> create unique index weiyi on t8(b);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#单列
mysql> create  index danlie on t8(c);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#组合
mysql> create index zuhe on t8(a,b,c,d);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#全文
mysql> create fulltext index  quanwen on t8(e);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
#空间
mysql> create  spatial index kongjian on t8(f);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.3 查看索引

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` char(30) NOT NULL,
  `d` char(30) NOT NULL,
  `e` char(30) NOT NULL,
  `f` geometry NOT NULL,
  UNIQUE KEY `weiyi` (`b`),
  KEY `putong` (`a`),
  KEY `danlie` (`c`),
  KEY `zuhe` (`a`,`b`,`c`,`d`),
  SPATIAL KEY `kongjian` (`f`),
  FULLTEXT KEY `quanwen` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from t8\G
*************************** 1. row ***************************
        Table: t8
   Non_unique: 0
     Key_name: weiyi
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: putong
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: danlie
 Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: kongjian
 Seq_in_index: 1
  Column_name: f
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: quanwen
 Seq_in_index: 1
  Column_name: e
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
6 rows in set (0.00 sec)
*************************** 6. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: zuhe
 Seq_in_index: 1
  Column_name: a
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 7. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: zuhe
 Seq_in_index: 2
  Column_name: b
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 8. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: zuhe
 Seq_in_index: 3
  Column_name: c
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 9. row ***************************
        Table: t8
   Non_unique: 1
     Key_name: zuhe
 Seq_in_index: 4
  Column_name: d
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

五、删除索引

索引删除分为：

ALTER TABLE删除
DROP INDEX删除

5.1 查看索引

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` char(30) NOT NULL,
  `d` char(30) NOT NULL,
  `e` char(30) NOT NULL,
  `f` geometry NOT NULL,
  UNIQUE KEY `weiyi` (`b`),
  KEY `putong` (`a`),
  KEY `danlie` (`c`),
  KEY `zuhe` (`d`),
  SPATIAL KEY `kongjian` (`f`),
  FULLTEXT KEY `quanwen` (`e`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5.2 ALTER TABLE

#删除t8的普通索引
mysql> alter table t8 drop index putong;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

5.3 DROP INDEX删除

#删除t8的空间索引
mysql> drop index kongjian on t8;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

有趣的灵魂！

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Linux——MySQL索引

一、简介1.1 索引概述Mysql索引的简历对于Mysql的高效运行是很重要的，索引可以大大提高Mysql的检索速度，创建索引时，需要确保索引是应用在SQL查询语句的条件（一般作为WHERE子句的条件），建立索引会占用磁盘空间的索引文件！每种搜索引擎支持的索引是不同的，如下：MylSAM、InnoDB支持btree索引；Memory支持btree和hash索引；1.2 索引的优势：加快查询速度创建唯一索引来保证数据表中数据的唯一性实现数据的完整性，加速表和表之间的连接减少分组和排序
复制链接

扫一扫