MySQL索引（基础）

最新推荐文章于 2024-07-12 14:26:59 发布

weixin_44636938

最新推荐文章于 2024-07-12 14:26:59 发布

阅读量89

点赞数

分类专栏：学习笔记文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_44636938/article/details/105244994

版权

学习笔记专栏收录该内容

13 篇文章 0 订阅

订阅专栏

9.1 索引简介

索引是对数据库表中一列或者多列的值进行排序的一种结构使用索引可以提高数据库中特定数据的查询速度。

含义：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。索引的建立可以提高数据库的查询速度。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都并不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎有关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。（本段为书上摘抄）
优点：
- 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
- 加快查询速度
- 在实现数据的参考完整性方面，可以加速表和表之间的连接
- 在分组和排序子句进行数据查询时，也可以减少查询中分组和排序的时间
缺点：
- 创建和维护索引要耗费时间，随着数量的增加，耗费的时间也会增加
- 索引还要占据磁盘空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
- 对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样会降低对数据的维护速度。
分类：
- 普通索引：允许在定义索引的列中插入重复值和空值
- 唯一索引：索引列的值必须唯一，但允许有空值，如果为组合索引，列值得组合必须唯一。主键索引是特殊的唯一索引，不允许有空值。
- 单列索引：一个索引只包含单列，一张表可以有多个单列索引
- 组合索引：在表的多个字段上创建索引，只有在查询条件中使用了这些字段的左边字段，索引才会被使用。（最左前缀集合）
- 全文索引：FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引在CHAR、VARCHAR或者TEXT类型的列上创建MySQL中只有MyISAM存储引擎支持全文索引。(InnoDB引擎也支持了）
- 空间索引：对空间数据类型的字段建立的索引。MySQL空间数据类型有4种:GEOMETRY、POINT、LINESTRING和POLYGON。MySQL 中使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
设计原则：
- 索引并非是越多越好。占用空间，影响性能（在数据更改时，索引也会进行调整和更新）
- 避免对经常更新的表进行过多的索引，且索引中的列尽可能的少。对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
- 数据量少的表最好不要添加索引。
- 在不同值很少的列上不用索引。如性别字段‘男’‘女’。
- 当唯一性是某种数据本身的特征时，指定唯一索引。能确保定义的列的数据完整性，以提高查询速度。
- 在频繁排序或者分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

9.2 创建索引

在创建表的时候创建索引
在已存在的表上建立索引
- 用ALTER TABLE语句
- 用CREATE INDEX创建（这个方法试验中不加索引名会报错，若有大佬看到，还请帮忙解释一下）

查看表结构：这里 KEY year(year)，第一个year为索引名，因为上述未指定索引名，为MySQL自动添加。

mysql> SHOW CREATE TABLE book\G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL AUTO_INCREMENT,
  `bookname` varchar(22) NOT NULL,
  `year` year(4) NOT NULL,
  PRIMARY KEY (`bookid`),
  KEY `year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

使用explain语句查看索引是否存在。
- select_type指定所使用的select查询类型。SIMPLE表示简单的，未使用UNION或者子查询。还可以为PRIMARY、UNION、SUBQUERY等。
- table：指定数据库读取的数据表的名字。
- type：指定本数据表和其他表的关系。有可能为system、const、eq_ref、ref、range、index、ALL。
- possible_keys：在搜索时可选用的索引
- key：实际选用的索引
- key_len：索引按字节计算的长度，key_len值越小越快。
- ref：给出关联关系中另一个数据表里数据列的名字。
- rows：在查询时预计会从这个表中读出的数据行的个数。
- Extra：提供与关联操作的有关的信息。

mysql> explain select * from book where year =2000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year
          key: year
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

使用SHOW INDEX 查看表中创建的索引
- Table：创建索引的表
- Non_unique：索引非唯一，1表示非唯一，0表示唯一索引
- Key_name：索引名称
- Seq_in_index：表示该字段在索引中的位置，单列索引该值为1，组合索引为该字段在索引中的顺序
- Column_name：定义索引的字段名
- Sub_part：索引长度
- Null：表示该字段是否能为空值
- Index_type：索引类型

mysql> SHOW INDEX FROM person\G
*************************** 1. row ***************************
        Table: person
   Non_unique: 1
     Key_name: nameIDX
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 20
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

9.2.1 创建普通索引

在创建表的时候创建索引

mysql> CREATE TABLE book(
    -> bookid int(11) primary key auto_increment,
    -> bookname varchar(22) NOT NULL,
    -> year YEAR NOT NULL,
    -> INDEX (year)
    -> );
Query OK, 0 rows affected (0.03 sec)

在已存在的表上建立索引

mysql> ALTER TABLE person ADD INDEX nameIDX(name(20));//20为索引长度，只有字符串类型的字段可以指定长度
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX idIDX ON person(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.2.2 创建唯一索引

在创建表的时候创建索引

mysql> CREATE TABLE book(
    -> bookid int(11),
    -> bookname varchar(22),
    -> UNIQUE INDEX idIDEX(bookid)
    -> );
Query OK, 0 rows affected (0.03 sec)

在已存在的表上建立索引

mysql> ALTER TABLE person ADD UNIQUE INDEX idIDX(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE UNIQUE INDEX nameIDX ON person(name(20));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.2.3 创建单列索引

在创建表的时候创建索引，上面两个例子都是单列索引

mysql> CREATE TABLE t1(
    -> id int(11),
    -> name varchar(22),
    -> INDEX (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

在已存在的表上建立索引

mysql> ALTER TABLE person ADD INDEX (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX nameIDX ON person(name(20));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.2.4 创建组合索引

在创建表的时候创建索引

mysql> CREATE TABLE t1(
    -> id int(11),
    -> name varchar(22),
    -> age int(11) ,
    -> INDEX MultiIDX(id,name(20),age)
    -> );
Query OK, 0 rows affected (0.02 sec)

在已存在的表上建立索引

mysql> ALTER TABLE book ADD INDEX (bookid,bookname(20));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX bookid ON book(bookid,bookname(20));
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.2.5 创建全文索引

在创建表的时候创建索引

mysql> CREATE TABLE t2(
    -> id int(11),
    -> name varchar(22),
    -> age int(2),
    -> FULLTEXT INDEX nameIDX(name(20))
    -> );
Query OK, 0 rows affected (0.16 sec)

在已存在的表上建立索引

mysql> CREATE FULLTEXT INDEX nameIDX ON book(bkname(20));
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> ALTER TABLE t2 ADD FULLTEXT INDEX NAME(name(20));
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.2.6 创建空间索引

必须为NOT NULL ,引擎为MyISAM

在创建表的时候创建索引

mysql> CREATE TABLE t3(
    ->  g GEOMETRY NOT NULL,
    -> SPATIAL INDEX spatIDX (g)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

在已存在的表上建立索引

mysql> CREATE SPATIAL INDEX spatIDX ON t3(g);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t3 ADD SPATIAL INDEX spatIDX(g);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

9.3 删除索引

使用ALTER TABLE

mysql> ALTER TABLE t3 DROP INDEX spatIDX;//spatIDX是索引名
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DROP INDEX id ON book;//id为索引名
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

weixin_44636938

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引（基础）

9.1 索引简介索引是对数据库表中一列或者多列的值进行排序的一种结构使用索引可以提高数据库中特定数据的查询速度。含义：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。索引的建立可以提高数据库的查询速度。索引是在存储引擎中实现的，因此，每种存储引擎的索引都并不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最...
复制链接

扫一扫