MySQL学习一：索引类型

用户6788802882491
112 阅读6分钟

索引可以提高查询速度，会影响where查询，以及order by排序。

查看表的索引：


SHOW INDEX FROM tablename;

示例：


mysql> show index from user;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| user  |          0 | PRIMARY  |            1 | id          | A        |          0 |    NULL |  NULL |      | BTREE      |        |              | YES    | NULL      |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

1 row in set (0.20 sec)

mysql>

MySQL索引类型如下：

  • 从应用层次划分：普通索引、唯一索引、主键索引、复合索引

  • 从索引键值类型划分：主键索引、辅助索引（二级索引）

  • 从数据存储和索引键值逻辑关系划分：聚簇索引、非聚簇索引

1.1 普通索引

基于普通字段建立的索引，没有任何限制。

普通索引的创建方法如下：


CREATE INDEX <索引名> ON tablename (字段名);

示例：


mysql> create index idx_user_name on user (name);

Query OK, 0 rows affected (2.23 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql>



ALTER TABLE tablename ADD INDEX [索引名] (字段名);



CREATE TABLE tablename ([...],INDEX [索引名](字段名);

1.2 唯一索引

与普通索引类似，不同之处在于：唯一索引的字段值必须唯一，允许有空值。在创建或者修改表时，添加唯一约束，就会自动创建对应的唯一索引。

唯一索引的创建方法如下：


CREATE UNIQUE INDEX <索引名> ON tablename (字段名);



ALTER TABLE tablename ADD UNIQUE INDEX [索引名] (字段名);



CREATE TABLE tablename ([...],UNIQUE [索引名](字段名);

1.3 主键索引

主键索引是一种特殊的唯一索引，不允许有空值，在创建表的时候加上主键约束即可，每个表只能有一个主键（可以是单个字段，也可以是联合主键）

创建主键索引的方法如下：


CREATE TABLE tablename ([...],PRIMARY KEY(字段名);



ALTER TABLE tablename ADD PRIMARY KEY [索引名] (字段名);

1.4 复合索引

单一索引是指索引列为1列的情况。用户可以在多列上创建索引，这种索引就叫做复合索引。相比多个单一索引，复合索引需要的开销更小。

索引同时有两个概念，窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引是指索引列大于2列的索引。索引设计的一个重要原则是能使用窄索引不用宽索引，因为窄索引往往比宽索引更有效。

复合索引创建方法如下：


CREATE INDEX <索引名> ON tablename (字段名1,字段名2);



ALTER TABLE tablename ADD INDEX [索引名] (字段名1,字段名2);



CREATE TABLE tablename ([...],INDEX [索引名](字段名1,字段名2);

复合索引使用的注意事项：

  • 使用复合索引要根据where条件建立索引，注意不要过多使用索引，过多使用索引会对更新效率有很大影响

  • 如果表已经建立了索引(col1,col2)就没有必要单独建(col1)

  • 如果表已经建立了索引(col1)，如果查询需要col1、col2当条件，可以建立符合索引(col1,col2)，对于查询效率有一定的提高。

1.5 全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like高很多。（MySQL5.6前的版本只有MyISAM存储引擎支持全文索引，从MySQL5.6开始，以后的版本，MyISAM和InnoDB存储引擎均支持）

全文索引的创建方式如下：

CREATE FULLTEXT INDEX <索引名> ON tablename (字段名);

ALTER TABLE tablename ADD FULLTEXT [索引名] (字段名);

CREATE TABLE tablename ([...],FULLTEXT KEY [索引名](字段名);

全文索引和常用的like模糊查询不同，全文索引有自己的语法格式，使用match和against关键字。例如：

select * from user  where match(name) against('aaa');

全文索引使用注意事项：

  • 全文索引必须建立在字符串或者文本字段上

  • 全文索引字段必须在最小值和最大值之间才有效。InnoDB默认为3-84，MyISAM默认为4-84,最大值只能通过修改配置文件，然后重启MySQL服务生效。

  • 全文索引会进行切词处理。按照syntax字符进行切割，例如b+aaa会切割成b和aaa

  • 全文索引匹配查询默认使用等值查询，例如aaa会匹配aaa，不会匹配aaab、aaac。如果想要匹配，可以在布尔模式下搜索aaa*

select * from user where match(name) against('aaa*' in boolean mode);

全文索引使用示例：


-- 1、建一张表user

mysql> CREATE TABLE `user` (

  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',

  `name` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',

  `sex` int NOT NULL COMMENT '性别0 女 1 男',

  `age` int NOT NULL COMMENT '年龄',

  `info` json DEFAULT NULL COMMENT '信息',

  PRIMARY KEY (`id`),

  FULLTEXT KEY `idx_user_name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ;

-- 2、在name上建立全文索引

mysql> create fulltext index idx_user_name on user (name);

-- 3、查看索引

mysql> show index from user;

+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |

+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

| user  |          0 | PRIMARY      |            1 | id          | A        |          0 |    NULL |  NULL |      | BTREE      |        |              | YES    | NULL      |

| user  |          1 | idx_user_name |            1 | name        | NULL      |          4 |    NULL |  NULL |      | FULLTEXT  |        |              | YES    | NULL      |

+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

2 rows in set (0.10 sec)

-- 4、插入一些测试数据

mysql> select * from user;

+----+------+-----+-----+------+

| id | name | sex | age | info |

+----+------+-----+-----+------+

|  1 | a    |  0 |  14 | NULL |

|  2 | aaa  |  0 |  14 | NULL |

|  3 | aa  |  0 |  14 | NULL |

|  4 | aaab |  0 |  14 | NULL |

|  5 | aaac |  0 |  14 | NULL |

+----+------+-----+-----+------+

5 rows in set (0.00 sec)

-- 5、测试like查询 以a开头的有5条数据

mysql> select count(1) from user where name like 'a%';

+----------+

| count(1) |

+----------+

|        5 |

+----------+

1 row in set (0.11 sec)

-- 6、测试match

-- 由于全文索引默认为全词匹配，由于InnoDB存储引擎的全文索引的最大值和最小值是3-84，所以匹配a的结果是0.匹配aaa的结果是1

mysql> select count(1) from user where match(name) against('a');

+----------+

| count(1) |

+----------+

|        0 |

+----------+

1 row in set (0.10 sec)

mysql> select count(1) from user where match(name) against('aaa');

+----------+

| count(1) |

+----------+

|        1 |

+----------+

1 row in set (0.00 sec)

-- 采用boolean模式匹配，因为最小长度是3，所以a和aa不会被匹配到，a*和aaa*的匹配结果均为3条数据

mysql> select count(1) from user where match(name) against('a*' in boolean mode);

+----------+

| count(1) |

+----------+

|        3 |

+----------+

1 row in set (0.00 sec)

mysql> select count(1) from user where match(name) against('aaa*' in boolean mode);

+----------+

| count(1) |

+----------+

|        3 |

+----------+

1 row in set (0.00 sec)

-- 7、测试分词

-- 插入一条数据。name为abc+def

-- 测试发现查询匹配abc* def* *def都能匹配到这条数据

mysql> insert into user (name,sex,age) values ('abc+def',0,14);

Query OK, 1 row affected (0.20 sec)

mysql> select * from user where match(name) against('abc*' in boolean mode);

+----+---------+-----+-----+------+

| id | name    | sex | age | info |

+----+---------+-----+-----+------+

|  6 | abc+def |  0 |  14 | NULL |

+----+---------+-----+-----+------+

1 row in set (0.00 sec)

mysql> select * from user where match(name) against('def*' in boolean mode);

+----+---------+-----+-----+------+

| id | name    | sex | age | info |

+----+---------+-----+-----+------+

|  6 | abc+def |  0 |  14 | NULL |

+----+---------+-----+-----+------+

1 row in set (0.00 sec)

mysql> select * from user where match(name) against('*def' in boolean mode);

+----+---------+-----+-----+------+

| id | name    | sex | age | info |

+----+---------+-----+-----+------+

|  6 | abc+def |  0 |  14 | NULL |

+----+---------+-----+-----+------+

1 row in set (0.00 sec)

mysql> select * from user where name like 'a';

+----+------+-----+-----+------+

| id | name | sex | age | info |

+----+------+-----+-----+------+

|  1 | a    |  0 |  14 | NULL |

+----+------+-----+-----+------+

1 row in set (0.00 sec)

mysql> select * from user where name like 'abc%';

+----+---------+-----+-----+------+

| id | name    | sex | age | info |

+----+---------+-----+-----+------+

|  6 | abc+def |  0 |  14 | NULL |

+----+---------+-----+-----+------+

1 row in set (0.00 sec)

mysql> select * from user where name like 'def%';

Empty set (0.00 sec)

mysql> select * from user where name like '%def';

+----+---------+-----+-----+------+

| id | name    | sex | age | info |

+----+---------+-----+-----+------+

|  6 | abc+def |  0 |  14 | NULL |

+----+---------+-----+-----+------+

1 row in set (0.00 sec)

mysql>
avatar
文章
阅读
粉丝