MySQL——索引

最新推荐文章于 2024-12-29 16:05:07 发布
原创 最新推荐文章于 2024-12-29 16:05:07 发布 · 323 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

在MySQL中，如果我们要在一个表中找到一条记录，那么就要从第一个表开始查找，直到找到该记录、或者遍历整个表，然后返回空，表示该表没有该记录，非常浪费时间，而索引就是用于快速找出在某个列中有意特定值的行，也就是记录。

索引简介

索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针，使用它可以快速定位到特定的列或行，所有MySQL的类型都可以被索引，并且对相关列使用索引是提高查询操作速度的最佳途径。

索引的优点：

  1. 通过创建索引，可以保证数据库表中的每一行数据的唯一性。
  2. 可以大大加快数据的查询速度，这也是床创建索引的主要原因。
  3. 在实现数据参考完整性方面，可以加速表和表之间的连接。
  4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

缺点：

  1. 创建和维护索引要耗费时间。
  2. 索引需要占用磁盘空间。
  3. 对表进行各种操作时，索引也要动态的维护，这就降低了数据的维护速度。
索引的分类
  1. 普通索引和唯一索引
    普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入空值和重复值
    唯一索引：要求索引所在列中的值都是唯一的，但允许有空值
  2. 单列索引和组合索引
    单列索引：是一个索引包含单个列，自然一个表可以有多个单列索引
    组合索引：多个字段的索引，只有使用了其中的列，索引才会被使用,且组合中必须包含有第一列，才能使用索引。
  3. 全文索引
    全文索引类型为：FULLTEXT，在定义索引的列上支持值的全文查找，并允许插入重复值和空值，可在CHAR、VARCHAR、TEXT类型上创建。MySQL中只有MyISAM存储引擎支持全文索引。索引总是对整个列进行，不支持局部（前缀）索引。
  4. 空间索引
    空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有：GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SOPATIAL关键字进行扩展，使用能够创建正规索引类似的语法创建空间索引。创建空间索引的列，必须声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
索引的设计原则
  1. 索引并非越多越好，一个表中有大量索引，会占用大量的磁盘空间，对INSERT、DELETE、UPDATE等这些更改数据的语句的性能有影响，因为在更改数据时，索引也要动态的更改。
  2. 对经常更改数据的表要尽量避免大量索引，索引内的列也要尽量的少。应该对经常用于查询的表常见索引，索引可能不会产生优化的效果。
  3. 数据量少的表，不建议创建索引，因为索引的空间可能比存储表和数据的空间要大，且遍历一次表的速度也可能比使用索引快。
  4. 对列中有不同值较多的列使用索引，而相同值多的列则不建议使用索引。
  5. 当数据有唯一性，那么索引必须也是唯一的。
  6. 在频繁进行排序和分组的列上建立索引，如果有多个列就建立组合索引。

创建索引

MySQL支持多种方法在单列或多列上创建索引；在创建表的定义语句上CREATE TABLE 中指定索引列，使用ALTER TABLE语句已创建的表中添加索引，或者使用CREATE INDEX语句在已存在的表中添加索引，主要介绍以上三种方法。

创建表时创建索引
语法格式：CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
//详解：
//table_name [col_name data_type]: 就是创建列的数据及其数据类型；
//[UNIQUE | FULLTEXT | SPATIAL]：可选，分别是唯一索引，全文索引和空间索引；
//[INDEX | KEY]：两个此时同义词，都是一样的作用，用来指定创建索引；
//[index_name]：索引名称，若不写，则默认为列或数据的名称；
//(col_name [length])：索引值及其长度，长度为可选参数；
//[ASC | DESC]：正序和倒序显示。
  1. 创建普通索引
    最基本的索引，没有唯一性之类的要求，其作用只是加快对数据的访问速度。
mysql> CREATE TABLE book
    -> (
    -> bookid INT NOT NULL,
    -> bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL,
    -> info VARCHAR(255) NULL,
    -> comment VARCHAR(255) NULL,
    -> year_publication YEAR NOT NULL,
    -> INDEX(year_publication)
    -> );
Query OK, 0 rows affected (0.15 sec)

//查看表的详细情况，可以看到索引的名称默认为字段的名称
mysql> SHOW CREATE table book\g
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book  | CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

//使用EXPLAIN语句查看索引是否正在使用：
mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1990 \g
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | book  | NULL       | ref  | year_publication | year_publication | 1       | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

//我们详细看看这几个EXPLAIN的参数的含义：
//select_ type行指定所使用的 SELECT查询类型，这里值为SIMPLE,表示简单的 SELECT,不使用 UNION或子查询。其他可能的取值有PRIMARY、UNION、SUBQUERY 等.
//table行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。
//type行指定了本数据表与其他数据表之间的关联关系，可能的取值有 system、const、eq_ref、 ref、 range、 index和 all。
//possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
//key行是MySQL 实际选用的索引。
//key_len行给出索引按字节计算的长度，key_len数值越小,表示越快。
//ref行给出了关联关系中另一个数据表里的数据列名。
//rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
//Extra行提供了与关联操作有关的信息。
//可以看到，possible kevs和 kev的值都为 year publication，查询时使用了索引。
  1. 创建唯一索引
mysql> CREATE TABLE t1
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> UNIQUE INDEX UniqIdx(id)
    -> );
Query OK, 0 rows affected (0.21 sec)

mysql> SHOW CREATE table t1 \g
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE id = 1234562 \g
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
  1. 创建单列索引
mysql> CREATE TABLE t2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NULL,
    -> INDEX SingleIdx(name(20))
    -> );
Query OK, 0 rows affected (0.13 sec)

// 索引名称为SingleIdx，索引列为name，长度为20
mysql> SHOW CREATE table t2 \g
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(30) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM t2 WHERE name="ysh";
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ref  | SingleIdx     | SingleIdx | 81      | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. 创建组合索引
mysql> CREATE TABLE t3
    -> (
    -> id INT(11) NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT(11) NOT NULL,
    -> info VARCHAR(255),
    -> INDEX MultiIdx(id, name, age)
    -> );
Query OK, 0 rows affected (0.15 sec)


mysql> SHOW CREATE table t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

//使用组合索引时，要注意必须要有第一个列，这里就是必须有id这个列，可以组成id = 1 AND name = 'ysh' 
//也可以 id = 1 AND age = 10 ,也可以有三个，但是不能没有id，如 name = 'ysh' AND age = 11的组合，
//这样就不能触发索引，也就是说，这样的组合是不能使用索引的。
//没有id就不满足列集的最左前缀的要求，就是要有最左的列。
mysql> EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = 'ysh' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 124
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
  1. 创建全文索引
mysql> CREATE TABLE t4
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> FULLTEXT INDEX FullTxtIdx(info)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW CREATE table t4 \G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
  1. 创建空间索引
mysql> CREATE TABLE t5
    -> (
    -> g GEOMETRY NOT NULL,
    -> SPATIAL INDEX spatIdx(g)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.03 sec)


mysql> SHOW CREATE TABLE t5 \G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
在已经存在的表上创建索引
  1. 使用ALTER TABLE 语句创建索引
语法格式：ALTER TABLE table_name [col_name data_type] ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

//在book中的bookname字段上建立名为BkNameIdx的普通索引
mysql> SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
1 row in set (0.06 sec)


//其中各个主要参数的含义为：
//(1) Table表示创建索引的表。
//(2) Non_unique 表示索引非唯一，1 代表是非唯一索引， 0代表唯一索引。
//(3) Key_name 表示索引的名称。
//(4) Seq_in_index 表示该字段在索引中的位置，单列索引该值为1, 组合索引为每个字段在索引定义中的顺序
//(5) Column_name表示定义索引的列字段。
//(6) Sub_part 表示索引的长度。
//(7) Null 表示该字段是否能为空值。
//(8) Index type 表示索引类型。
//可以看到，book 表中已经存在了一个索引， 即前面已经定义的名称为year_publication 索引，该索引为非唯一索引。


//增加索引
mysql> ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0


//查看表中的索引
mysql> SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
2 rows in set (0.01 sec)


//建立一个唯一索引
mysql> ALTER TABLE book ADD UNIQUE INDEX UniquidIdx (bookId);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: UniquidIdx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 3. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
3 rows in set (0.07 sec)

//建立单列索引
mysql> ALTER TABLE book ADD INDEX BkcmtIdx(comment(50));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| book  |          0 | UniquidIdx       |            1 | bookid           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| book  |          1 | year_publication |            1 | year_publication | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     |
| book  |          1 | BkNameIdx        |            1 | bookname         | A         |           0 |       30 |   NULL |      | BTREE      |         |               | YES     |
| book  |          1 | BkcmtIdx         |            1 | comment          | A         |           0 |       50 |   NULL | YES  | BTREE      |         |               | YES     |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.02 sec)

//组合索引
mysql> ALTER TABLE book ADD INDEX BkAuAndInfoIdx(authors(30),info(50));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

//我们看到Seq_in_index: 的值有1和2，就是在组合索引定义时列的位置。
mysql> SHOW INDEX FROM book \G
*************************** 5. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BkAuAndInfoIdx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 6. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BkAuAndInfoIdx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
6 rows in set (0.00 sec)

//添加全文索引，需要MyISAM引擎，因此重新建立了一个表
mysql> CREATE TABLE t6
    -> (
    -> id INT NOT NULL,
    -> info CHAR(255)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx(info);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM t6 \G
*************************** 1. row ***************************
        Table: t6
   Non_unique: 1
     Key_name: infoFTIdx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment:
      Visible: YES
1 row in set (0.09 sec)

//创建空间索引
mysql> CREATE TABLE t7 (g GEOMETRY NOT NULL )ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW INDEX FROM t7 \G
*************************** 1. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: spatIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null:
   Index_type: SPATIAL
      Comment:
Index_comment:
      Visible: YES
1 row in set (0.07 sec)
  1. 使用CREATE INDEX创建索引
语法结构：CREATE [UNION | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name [length],...) [ASC | DESC]

mysql> CREATE TABLE book_1
    -> (
    -> bookid INT NOT NULL,
    -> bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL,
    -> info VARCHAR(255) NULL,
    -> comment VARCHAR(255) NULL,
    -> year_publication YEAR NOT NULL
    -> );
Query OK, 0 rows affected (0.17 sec)

//创建普通索引
mysql> CREATE INDEX BkNameIdx ON book_1(bookname);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book_1 \G
*************************** 1. row ***************************
        Table: book_1
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
1 row in set (0.03 sec)

//创建唯一索引
mysql> CREATE UNIQUE INDEX BkcmtIdx ON book_1(bookId);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book_1 \G
*************************** 1. row ***************************
        Table: book_1
   Non_unique: 0
     Key_name: BkcmtIdx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: book_1
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
2 rows in set (0.04 sec)

//创建单列索引
mysql> CREATE INDEX BkcmIdx ON book_1(comment(50));
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

//创建组合索引
mysql> CREATE INDEX BkAuAndInfoIdx ON book_1(authors(20),info(50));
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM book_1 \G
*************************** 1. row ***************************
        Table: book_1
   Non_unique: 0
     Key_name: BkcmtIdx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: book_1
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 3. row ***************************
        Table: book_1
   Non_unique: 1
     Key_name: BkcmIdx
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 4. row ***************************
        Table: book_1
   Non_unique: 1
     Key_name: BkAuAndInfoIdx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 20
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
*************************** 5. row ***************************
        Table: book_1
   Non_unique: 1
     Key_name: BkAuAndInfoIdx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
5 rows in set (0.04 sec)

mysql> CREATE TABLE t8
    -> (
    -> id INT NOT NULL,
    -> info CHAR(255),
    -> g GEOMETRY NOT null
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.12 sec)

//创建全文索引
mysql> CREATE FULLTEXT INDEX infoFTIDx ON t8(info);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

//创建空间索引
mysql> CREATE SPATIAL INDEX spatIdx ON t8 (g);
Query OK, 0 rows affected, 1 warning (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW INDEX FROM t8 \g;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t8    |          1 | spatIdx   |            1 | g           | A         |        NULL |       32 |   NULL |      | SPATIAL    |         |               | YES     |
| t8    |          1 | infoFTIDx |            1 | info        | NULL      |        NULL |     NULL |   NULL | YES  | FULLTEXT   |         |               | YES     |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.03 sec)

删除索引

MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句，两者可实现相同的功能，DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。

  1. 使用ALTER TABLE 删除索引
//语法格式：ALTER TABLE table_name DROP INDEX index_name;

mysql> SHOW CREATE TABLE book \G;
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  UNIQUE KEY `UniquidIdx` (`bookid`),
  KEY `year_publication` (`year_publication`),
  KEY `BkNameIdx` (`bookname`(30)),
  KEY `BkcmtIdx` (`comment`(50)),
  KEY `BkAuAndInfoIdx` (`authors`(30),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

//删除索引
mysql> ALTER TABLE book DROP INDEX UniquidIdx;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE book DROP INDEX UniquidIdx;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW CREATE TABLE book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`),
  KEY `BkNameIdx` (`bookname`(30)),
  KEY `BkcmtIdx` (`comment`(50)),
  KEY `BkAuAndInfoIdx` (`authors`(30),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
  1. 使用DROP INDEX语句删除索引
//语法结构：DROP INDEX index_name ON table_name;


mysql> SHOW CREATE TABLE book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`),
  KEY `BkNameIdx` (`bookname`(30)),
  KEY `BkcmtIdx` (`comment`(50)),
  KEY `BkAuAndInfoIdx` (`authors`(30),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

//删除索引
mysql> DROP INDEX BkNameIdx ON book;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SHOW CREATE TABLE book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`),
  KEY `BkcmtIdx` (`comment`(50)),
  KEY `BkAuAndInfoIdx` (`authors`(30),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

MySQL8.0新特性–支持降序索引

在MySQL中默认为升序排序，我们可以定义为降序排序

//定义方法
mysql> CREATE TABLE ts1
    -> (
    -> a INT,
    -> b INT,
    -> INDEX idx_a_b(a,b DESC)
    -> );
Query OK, 0 rows affected (0.12 sec)

//查看细节
mysql> SHOW CREATE TABLE ts1 \G
*************************** 1. row ***************************
       Table: ts1
Create Table: CREATE TABLE `ts1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

//为表ts1 添加80000条数据 
mysql> DELIMITER ;;
mysql> CREATE PROCEDURE ts_insert ()
    -> BEGIN
    -> DECLARE i INT DEFAULT 1;
    -> WHILE i < 80000
    -> DO
    -> insert into ts1 select rand()*80000, rand()*80000;
    -> set i=i+i;
    -> END WHILE;
    -> commit;
    -> END;;
Query OK, 0 rows affected (0.13 sec)

mysql> DELIMITER ;
mysql> CALL ts_insert();

//查看ts1数据表的执行计划
mysql> explain select * from ts1 order by a, b desc limit 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ts1   | NULL       | index | NULL          | idx_a_b | 10      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

//修改条件，再看看执行条件
mysql> explain select * from ts1 order by a desc, b desc limit 5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | ts1   | NULL       | index | NULL          | idx_a_b | 10      | NULL |   17 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
理解MySQL——索引与优化总结
09-09
MySQL的索引与优化是数据库性能提升的关键环节。索引能显著提高查询速度，尤其是在处理大量数据时。本文将深入探讨这一主题。 首先，选择合适的数据类型至关重要。较小的数据类型通常意味着更快的处理速度和更少的...
win7 64位官方旗舰版上搭建ruby on rails的步骤
热门推荐
MasterWilliam的专栏
05-24 15万+
今天在win7上安装ruby成功，步骤记录下来了，分享给朋友们。亲测，可行。 ----------第一步：安装ruby------------ 1.安装 rubyinstaller-2.2.4-x64.exe ，记得勾选 add path...选项，安装完之后 ruby -v 查看版本号，比如 ruby 2.2.4p230 (2015-12-16 revision 53155) [x64-mi
Linux命令大全
hscoder的博客
03-05 8054
一、文件/文件夹管理 ls 列出当前目录文件（不包括隐含文件） ls -a 列出当前目录文件（包括隐含文件） ls -l 列出当前目录下文件的详细信息 cd .. 回当前目录的上一级目录 cd - 回上一次所在的目录 cd ~ 或 cd 回当前用户的宿主目录 mkdir 目录名 创建一个目录 rmdir 空目录名 删除一个空目录 rm 文件名 文件名 删除一个文件或多个文
Ubuntu常用命令大全
cxianf
07-17 2127
Ubuntu常用命令大全 查看软件xxx安装内容#dpkg -L xxx 查找软件#apt-cache search 正则表达式查找文件属于哪个包#dpkg -S filename apt-file search filename 查询软件xxx依赖哪些包#apt-cache depends xxx 查询软件xxx被哪些包依赖#apt-cache rdepends xxx 增加一个光盘...
数据库之操作MySQL
prigilm的博客
11-01 673
数据库之操作MySQL 一、数据库 1、什么是数据库 数据库是数据管理的有效技术，是由一批数据构成的有序集合，这些数据被存放在结构化的数据表里。. 数据表之间相互关联，反映客观事物间的本质联系。. 数据库能有效地帮助一个组织或企业科学地管理各类信息资源。. 数据是数据库中存储的基本对象，是按一定顺序排列组合的物理符号。. 数据有多种表现形式，可以是数字、文字、图像，甚至是音频或视频，它们都可以经过数字化后存入计算机。 2、数据库的分类 数据库本质上就是一种C/S架构 数据库的分类：关系型数据库和非关系
谷粒商城-高级篇-aiueo
qq_40780198的博客
02-19 2857
105 初步检索 105.1 _cat GET /_cat/nodes : 查看所有节点 GET /_cat/health : 查看es健康状况 GET /_cat/master : 查看主节点 GET /_cat/indices: 查看所有索引 107 乐观锁字段 _seq_no :并发控制字段 , 每次更新就会 + 1 , 用来做乐观锁 _primary_term : 同上,主分片重新分配 , 如重启 , 就会变化. 107.1 并发修改 发送两次修改同时对同一文档进行修改 , 为了控
MySQL——索引下推
m0_60500421的博客
06-05 1651
MySQL索引下推
Mysql——索引
weixin_73530513的博客
03-26 1573
当你去图书馆借书时，你是选择一个书架一个书架的寻找，还是根据图书馆提供的书籍目录来找呢？那肯定是选择目录来寻找，目录查找的时间比粗暴的寻找不知快多少。而目录就是充当索引的角色。那换到数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。在本章中，我们了解了mysql中最常用的索引——B+Tree，认识了它的逻辑结构，和它的有缺。
MySQL —— 索引
最新发布
m0_74824123的博客
12-29 947
既然红黑树不行，那我们可以考虑B树，这时一颗多路平衡树，由于是多路，所以可以降低书高，但是我们的MySQL还是不满意，觉得效率还不是不够高，于是MySQL 就使用B树的变形也就是B+树，我们在前面就知道B+树有一些特点：真实的数据都是保存在叶子节点上的，非叶子结点只是起到一个导航的作用，并且叶子结点是使用双向链表进行连接的，所以在数据库进行范围查找的时候十分方便。，这两行并不存储任何真实数据，而是最为数据行链表的头和尾【你可以理解为链表的两个空节点，一个是空的头节点，另一个是空的尾节点】
Mysql——索引下推
一蓑烟雨任平生
03-21 635
这意味着存储引擎在遍历索引的过程中，不仅可以利用索引快速定位记录，而且可以在读取索引的同时就完成一部分WHERE条件的判断，只取出那些真正满足所有条件的行数据，而不是先返回所有引用主键的记录，再由服务器层进一步筛选。例如，在一个带有多个列的索引中，即使查询条件包含了索引列和非索引列，ICP也能发挥作用，存储引擎可以直接在索引内部进行多列条件的初步筛选，减少不必要的回表操作（即减少访问实际数据页的次数），从而降低IO成本，提高查询性能。在没有启用索引下推的情况下，MySQL可能会首先使用复合索引。
MYSQL——索引设计原则
庄小焱
12-28 1926
之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。但是，对于这个查询语句来说，如果你定义的 index2 不是 email(6) 而是 email(7），也就是说取 email 字段的前 7 个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到 ID2，只扫描一行就结束了。其中，Q1 的结果还是符合预期的，rows 的值是 104620；
linux常用命令分类
yjpfj1203的专栏
12-19 4953
一：软件     查看软件xxx安装内容：#dpkg -L xxx     查找软件：#apt-cache search 正则表达式     查找文件属于哪个包：#dpkg -S filename apt-file search filename     查询软件xxx依赖哪些包：#apt-cache depends xxx     查询软件xxx被哪些包依赖：#apt-cache rd
查询数据
A_easy_learner的博客
05-06 2万+
基本查询语句 MySQL冲数据表中查询数据的基本语句是SELECT语句，SELECT语句基本格式是： SELECT {* | <字段列表>} [ FROM <表1>,<表2>,... [WHERE <表达式> [GROUP BY <group by definition>] [HAVING <expression> [{<operator> <expression>}...]] [ORDE
存储过程和函数
A_easy_learner的博客
05-17 908
1. 创建存储过程和函数 存储程序可以分为存储过程和函数，在MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE 和CREATE FUNCTION。使用CALL 调用存储过程，只能输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值，存储过程也可以调用其他存储过程。 1. 创建存储过程 创建存储过程，需要使用CREATE PROCEDURE 语句。 语法格式：CREATE PROCEDURE sp_name ( [proc_parameter] ) [characte
MySQL修改表和字段
A_easy_learner的博客
03-19 616
修改表名 语法：alter table 旧表名 rename to 新表名。to可有可无 例子： mysql> show tables; +-------------------+ | Tables_in_test_db | +-------------------+ | tb_dept1 | | tb_deptment3 | | tb_emp | | tb_emp0 | | tb_emp1 | | tb_emp
MySQL基本操作之创建数据表
A_easy_learner的博客
03-18 272
创建数据库 创建数据库 mysql> create database test_db; 查看所有数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sys
MySQL常见面试题解析——索引、事务详解及常见问题
（11）MySQL中常见的索引类型有主键索引、唯一索引、普通索引、全文索引等。 （12）覆盖索引指的是查询的结果可以直接从索引中获得，而不需要再进行回表查询。 （13）非聚簇索引不一定会回表查询，如果查询的数据...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值