MySQL的索引

最新推荐文章于 2024-06-17 15:31:11 发布

Micky_Yang

最新推荐文章于 2024-06-17 15:31:11 发布

阅读量183

点赞数

分类专栏： MySQL学习

本文链接：https://blog.youkuaiyun.com/Micky_Yang/article/details/87116619

版权

MySQL学习专栏收录该内容

16 篇文章

订阅专栏

1、索引的概述
索引是一种将数据库中单列或者多列的值进行排序的结构。应用良好的索引可以提高查询速度，降低服务器的负载；提高数据库系统的整体性能。但是，创建索引和维护需要耗费时间，索引也需要占用物理空间，会给数据的维护带来一些麻烦。
不同的存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持16个索引。总索引长度至少为256个字节。有些存储引擎支持更多的索引数和更大的索引长度。索引有两种存储类型，包括B树（BTREE）索引和哈希（HASH）索引。其中，B树索引为系统默认索引。

2、索引的分类
1）普通索引
即不应用任何限制条件的索引，该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。创建该类型索引后，用户在查询时，便可以通过索引进行查询。在某数据表的某一字段中，建立普通索引后。用户需要查询数据时，只需根据该索引进行查询便可。
创建普通索引：
方法一：

MariaDB [aaa]> create table test1(id int(10) not null,name char(20) not null,age int(10) not null,index (id));
Query OK, 0 rows affected (0.13 sec)

MariaDB [aaa]> 
MariaDB [aaa]> show create  table test1\G;
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) NOT NULL,
  `name` char(20) NOT NULL,
  `age` int(10) NOT NULL,
  KEY `id` (`id`)               //创建该表索引为id列
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

查看数据是否从索引获取：

MariaDB [aaa]> insert into test1  values(1,'xiaoming',10),(2,'xiaohong',11),(3,'xiaodong',12);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [aaa]> 
MariaDB [aaa]> 
MariaDB [aaa]> select * from test1;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | xiaoming |  10 |
|  2 | xiaohong |  11 |
|  3 | xiaodong |  12 |
+----+----------+-----+
3 rows in set (0.00 sec)

MariaDB [aaa]> show indexes from test1\G;             //查看该表的索引信息
*************************** 1. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]> 
MariaDB [aaa]> explain select * from test1 where id=3\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: id
          key: id
      key_len: 4
          ref: const
         rows: 1               //此时数据走索引只检索了一行
        Extra: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

删除索引再试试：

MariaDB [aaa]> drop index id on test1;             //删除表中的id索引列
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]> 
MariaDB [aaa]> show index from test1\G;        //此时再看表中已经没有索引字段了
Empty set (0.00 sec)

MariaDB [aaa]> explain select * from test1 where id=3 \G;            //再次查看id为3的字段
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3                   //此时检索了表的三个字段，比没有索引更加消耗服务器资源
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

方法二：

MariaDB [aaa]> create index age_inx on test1(age);        //创建一个名称为agx_inx的age字段的索引
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]> show index from test1\G;                 //查看表中的索引
*************************** 1. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: age_inx
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.01 sec)

ERROR: No query specified
MariaDB [aaa]> explain select * from test1  where age=12\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: age_inx
          key: age_inx
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

删除该索引：

MariaDB [aaa]> drop index age_inx on test1;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]>

方法三：

MariaDB [aaa]> alter table test1 add index id_inx(id);              //添加一个名称为id_inx的id字段的索引
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]> show index from test1\G;             //查看表中的索引
*************************** 1. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: id_inx
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

2）唯一性索引
使用UNIQUE参数可以设置唯一索引。创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录，主键是一种特殊唯一索引。

MariaDB [aaa]> create unique index age_inx on test1(age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]> show index from test1\G;
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: age_inx
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

3）全文索引
使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，查询带有文章回复内容的字段，可以应用全文索引的方式。需要注意的是，在默认情况下，应用全文索引搜索大小写不敏感。如果索引的列使用二进制排序后，可以执行大小写敏感的全文索引。只有MyISAM类型的数据表支持FULLTEXT全文索引，InnoDB或者其他类型的数据表不支持全文索引。

4）单列索引
单列索引即只对应一个字段的索引。其可以包括上述叙述的3种索引方式。应用该索引条件只需要保证该索引值对应一个字段即可。

5）多列索引
多列索引是在表的多个字段上创建索引。该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。要想应用该索引，用户必须使用这些字段中的第一个字段。触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。

MariaDB [aaa]> create index inx_id_age on test1(id,age);     //创建一个名称为inx_id_age的id字段和age字段的所有
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [aaa]> show index from test1\G;         //查看表中的索引
*************************** 1. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: inx_id_age
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: test1
   Non_unique: 1
     Key_name: inx_id_age
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]>

测试不查询第一个字段是否会走索引：

MariaDB [aaa]> explain select * from test1 where age=2\G;          //只查询age字段
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3                   //并没有走索引
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [aaa]> 
MariaDB [aaa]> explain select * from test1 where id=2 \G;       //只查询id字段
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: inx_id_age
          key: inx_id_age
      key_len: 4
          ref: const
         rows: 1              //有走索引
        Extra: 
1 row in set (0.01 sec)

ERROR: No query specified

MariaDB [aaa]> 
MariaDB [aaa]> explain select * from test1 where id=3 and age=3 \G;       //查询两个字段
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test1
         type: ref
possible_keys: inx_id_age
          key: inx_id_age
      key_len: 8
          ref: const,const
         rows: 1                        //同样有走索引
        Extra: 
1 row in set (0.00 sec)

ERROR: No query specified

提示：按条件列查询数据时，联合索引是有前缀生效特性的。
index（a，b，c）仅a，ab，abc三个查询条件可以走索引。b，bc，ac，c等无法使用索引了。

6）空间索引
使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

索引列的创建及生效条件：
1）因为索引不但占用系统空间，更新数据库时还需要维护索引数据的，因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表要少去建立索引。索引不是越多越好。
2）需要在哪些列上创建索引呢？
例如：select user，host from mysql.user where host=…，索引一定要创建在where后条件列上，而不是select后的选择的数据的列。另外，我们要尽量选择在唯一值多的大表上的列建立索引。要在表的列上创建索引。