MySQL索引的相关介绍

最新推荐文章于 2021-01-27 15:20:50 发布

现实如此呀

最新推荐文章于 2021-01-27 15:20:50 发布

阅读量177

点赞数

分类专栏： MySQL 文章标签：数据库 mysql sql 索引

本文链接：https://blog.csdn.net/weixin_47019016/article/details/111025238

版权

MySQL 专栏收录该内容

24 篇文章 4 订阅

订阅专栏

MySQL索引

一、索引介绍
- 二、创建表的同时创建索引
- - 三、使用alter在已经存在的表上创建索引
  - - 四、使用create创建索引
    - - 五、删除索引
      - 六、使用SQLAdvisor工具优化索引

一、索引介绍

索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。MyISAM，InnoDB支持btree索引，Memory支持btree和hash索引。
在MySQL的设计中，当查询结果达到了原表中的一定比例(大概30%左右)，MySQL优化器会认为没有必要再使用索引，而直接采用全表扫描，这个比例与数据库的预读能力与参数有关。
1.索引的优势：

(1)加快查询速度;

(2)创建唯一索引来保证数据表中数据的唯一性;

(3)实现数据的完整性，加速表和表之间的连接;

(4)减少分组和排序的时间;

2.增加索引也有许多不利：

(1)创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

(2)索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

(3)当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

3.索引的分类

主键索引
也称聚簇索引(clustered index)，是一种特殊的唯一索引，它要求字段的值不能重复、不能为空，并且一张表只能有一个主键索引。
主键索引最好建立在跟业务不相关、很少修改和删除并且最好是自增的字段上，比如ID字段。对于聚簇索引来说索引即数据，用主键查询数据至少需要一次索引查找。
自增主键的插入模式符合递增插入的场景。每次插入一条新记录都是追加操作，不涉及变动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键往往不容易保证有序插入，这样写数据成本相对较高。

非主键索引(secondary index)
非主键索引也称二级索引(secondary index)，对于二级索引来说，存储的是主键信息，还需要根据主键再进行一次查询才能获取最终数据，这称为回表。
非主键索引又可以分为以下类型：

INDEX：普通索引，也叫辅助索引。允许字段内容相同，是最常用的索引类型;

UNIQUE INDEX：唯一索引，字段不允许重复，只可以有一个值为空;

FULLTEXT INDEX：全文索引;

组合索引：一个索引中包含了多列;

(1)唯一索引和普通索引
普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
(2)单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
(3)全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。
全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL 5.6中只有MyISAM存储引擎支持全文索引，MySQL 5.7中MyISAM和InnoDB都支持全文索引。
(4)空间索引
对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是：geometry、point、linstring和polygon。
MySQL使用SPATIAL关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为NOT NULL，MySQL 5.6中空间索引只能在存储引擎为MyISAM的表中创建，但是MySQL 5.7中MyISAM和InnoDB都支持空间索引。
4.主键索引和非主键索引的查询区别

#假设id列为主键索引，该语句则使用主键查询，只需要搜索id列这棵B+树
select * from a where id=100

#假设name列为普通索引，该语句需要先搜索name索引树，得到id后再到id索引树搜索，这个过程称为回表;
select * from a where name='zj'

覆盖索引
当一个索引包含了需要查询的所有字段时就称为覆盖索引，无需回表。
假设worker表有索引(type,salary)，以下查询就会使用到覆盖索引特性。

select type,salary from worker where type='a'

5.创建索引的规则

(1)创建索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响insert、delete、update等语句的性能。
因为当表中的数据更改时，索引也会进行调整和更新。
通常单表不应该超过5个，否则就应该考虑表设计的合理性。

(2)数据量小的表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要长。

(3)避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引。

(4)在条件表达式中经常用到的不同值较多的列创建索引。

(5)当唯一性是某种数据本身的特征时，创建唯一性索引。

(6)在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建组合索引。

6.索引失效的原因
避免在索引字段上使用计算、NOT(!=、<>)、IS NULL、IS NOT NULL、%模糊查询、varchar与int隐式转换(比如数据类型是字符串，使用select * from table where id = '123’会走索引，而select * from table where id=123就不会)等情况，否则会导致索引失效。
慎用IN、NOT IN。IN和NOT IN所定义的值是不确定的，所以MySQL不会对这两种查询使用索引。
对于连续的数值使用BETWEEN来代替IN。BETWEEN定义的是一个连续的区间，可以使用到索引。

select id,name,salary from worker where salary in (1,2,3);  #不走索引

select id,name,salary from worker where salary between 1 and 3;  #走索引

7.索引变慢的可能性
很少变动的表可以多创建索引，经常变动的表有索引也有可能出现索引丢失，所以一旦出现本来很快的查询语句突然变慢了，就要考虑这个问题，进行索引重建。

二、创建表的同时创建索引

语法格式:

Create index 创建索引
alter table 添加索引
Create table 表名[字段名 数据类型] [unique 唯一索引|fulltext 全文索引|spatial 空间索引] [index|key] [索引名] (col_name [length]) [asc |desc]

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...
key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

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.01 sec)

查看创建的索引

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

explain判断索引是否正在被使用

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

TYPE的取值范围:System const ref eq_ref index all range
2.创建唯一索引
主要原因是减少查询索引列操作的执行时间。尤其是对比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

mysql> create table t1
    -> (
    -> id int not null,
    -> name char(30) not null,
    -> unique index uniqidx(id)
    -> );
Query OK, 0 rows affected (0.00 sec)

查看创建的索引

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

3.创建单列索引
是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。

mysql> create table t2
    -> (
    -> id int not null,
    -> name char(50) null,
    -> index singleidx(name)
    -> );
Query OK, 0 rows affected (0.00 sec)

查看创建的索引

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

4.创建组合索引
是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀指索引最左边的列来匹配行。

mysql> create table t3
    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> index multiidx(id,name,age)
    -> );
Query OK, 0 rows affected (0.01 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=utf8
1 row in set (0.00 sec)

组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。
遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5.创建全文索引
FULLTEXT，可以用于全文搜索，只能为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部索引，适合大型数据的表创建。

mysql> create table t4
    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> fulltext index fullidx(info(100))
    -> );
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 `fullidx` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

6.创建空间索引
空间类型的字段必须为非空。

mysql> create table t5
    -> (
    -> g geometry not null,
    -> spatial index spaidx(g)
    -> );
Query OK, 0 rows affected (0.01 sec)

查看创建的索引

mysql> show create table t5\G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spaidx` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

7.查看索引的方式
(1)查询指定库中的所有索引

mysql> select * from information_schema.statistics where table_schema='test'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: t1
   NON_UNIQUE: 0
 INDEX_SCHEMA: test
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: id
    COLLATION: A
  CARDINALITY: 3
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
1 row in set (0.00 sec)

(2)查询表中存在的索引

mysql> show index from test.t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 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)

(3)查询创建表时添加的索引

mysql> show create table test.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

三、使用alter在已经存在的表上创建索引

语法格式:

Alter table 表名 add [unique唯一索引|fulltext全文索引|spatial空间索引] [index|key] [默认索引名] (定义索引名[length]) [asc|desc]

1.添加索引

mysql> alter table book add index bknameidx(bookname(30));
Query OK, 0 rows affected (0.03 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))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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: 
*************************** 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: 
2 rows in set (0.00 sec)

2.添加唯一性索引

mysql> alter table book add unique index uniqidx(bookid);
Query OK, 0 rows affected (0.01 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,
  UNIQUE KEY `uniqidx` (`bookid`),
  KEY `year_publication` (`year_publication`),
  KEY `bknameidx` (`bookname`(30))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: uniqidx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 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: 
*************************** 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: 
3 rows in set (0.00 sec)

3.添加单列索引

mysql> alter table book add index bkidex(comment(50));
Query OK, 0 rows affected (0.00 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,
  UNIQUE KEY `uniqidx` (`bookid`),
  KEY `year_publication` (`year_publication`),
  KEY `bknameidx` (`bookname`(30)),
  KEY `bkidex` (`comment`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: uniqidx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 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: 
*************************** 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: 
*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkidex
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
4 rows in set (0.00 sec)

4.添加全文索引

mysql> create table t6
    -> (
    -> id int not null,
    -> info char(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t6 add fulltext index infofulidx(info);
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

查看添加的索引

mysql> show create table t6\G
*************************** 1. row ***************************
       Table: t6
Create Table: CREATE TABLE `t6` (
  `id` int(11) NOT NULL,
  `info` char(255) DEFAULT NULL,
  FULLTEXT KEY `infofulidx` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from t6\G
*************************** 1. row ***************************
        Table: t6
   Non_unique: 1
     Key_name: infofulidx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

5.添加组合索引

mysql> alter table book add index bkauthandinfoidx(authors(20),info(50));
Query OK, 0 rows affected (0.01 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,
  UNIQUE KEY `uniqidx` (`bookid`),
  KEY `year_publication` (`year_publication`),
  KEY `bknameidx` (`bookname`(30)),
  KEY `bkidex` (`comment`(50)),
  KEY `bkauthandinfoidx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 0
     Key_name: uniqidx
 Seq_in_index: 1
  Column_name: bookid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 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: 
*************************** 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: 
*************************** 4. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkidex
 Seq_in_index: 1
  Column_name: comment
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkauthandinfoidx
 Seq_in_index: 1
  Column_name: authors
    Collation: A
  Cardinality: 0
     Sub_part: 20
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 6. row ***************************
        Table: book
   Non_unique: 1
     Key_name: bkauthandinfoidx
 Seq_in_index: 2
  Column_name: info
    Collation: A
  Cardinality: 0
     Sub_part: 50
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
6 rows in set (0.00 sec)

6.添加空间索引

mysql> create table t7
    -> (
    -> g geometry not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t7 add spatial index spatidx(g);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看添加的索引

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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: 0
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

四、使用create创建索引

语法格式:

Create index 已存在的表上创建索引 Create [unique唯一性索引|fulltext全文索引|spatial空间索引] index 索引名
On 表名（col_name[lenth]..）[asc|desc]

注意:用create table创建的索引可以不添加索引名（不添加索引名默认为字段名）但是用create index添加索引名必须加索引名。
创建一个book1的表

mysql> create table book1
    -> (
    -> 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.10 sec)

1.创建普通索引

mysql> create index bknameidex on book1(bookname);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table book1\G
*************************** 1. row ***************************
       Table: book1
Create Table: CREATE TABLE `book1` (
  `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 `bknameidex` (`bookname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.创建单列索引

mysql> create index bkcmtidex on book1(comment(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table book1\G
*************************** 1. row ***************************
       Table: book1
Create Table: CREATE TABLE `book1` (
  `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 `bknameidex` (`bookname`),
  KEY `bkcmtidex` (`comment`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.创建组合索引

mysql> create index bkauthandinfoidex on book1(authors(30),info(50));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table book1\G
*************************** 1. row ***************************
       Table: book1
Create Table: CREATE TABLE `book1` (
  `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 `bknameidex` (`bookname`),
  KEY `bkcmtidex` (`comment`(50)),
  KEY `bkauthandinfoidex` (`authors`(30),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.创建全文索引

mysql> create table t8
    -> (
    -> id int not null,
    -> info char(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> create fulltext index fullidex on t8(info);
Query OK, 0 rows affected, 1 warning (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `id` int(11) NOT NULL,
  `info` char(255) DEFAULT NULL,
  FULLTEXT KEY `fullidex` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5.创建唯一性索引

mysql> create unique index uniqidex on book1(bookid);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table book1\G
*************************** 1. row ***************************
       Table: book1
Create Table: CREATE TABLE `book1` (
  `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 `uniqidex` (`bookid`),
  KEY `bknameidex` (`bookname`),
  KEY `bkcmtidex` (`comment`(50)),
  KEY `bkauthandinfoidex` (`authors`(30),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

6.创建空间索引

mysql> create table t9
    -> (
    -> g geometry not null
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create spatial index spaidx on t9(g);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t9\G
*************************** 1. row ***************************
       Table: t9
Create Table: CREATE TABLE `t9` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spaidx` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

五、删除索引

语法格式:

Alter table 表名 drop index 索引名；

1.查看book中创建的索引

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 `uniqidx` (`bookid`),
  KEY `year_publication` (`year_publication`),
  KEY `bknameidx` (`bookname`(30)),
  KEY `bkidex` (`comment`(50)),
  KEY `bkauthandinfoidx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.用ALTER TABLE删除

mysql> alter table book drop index uniqidx;
Query OK, 0 rows affected (0.01 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 `bkidex` (`comment`(50)),
  KEY `bkauthandinfoidx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.添加AUTO_INCREMENT的约束字段的唯一索引不能删除，用DROP INDEX删除。

mysql> show create table t7\G
*************************** 1. row ***************************
       Table: t7
Create Table: CREATE TABLE `t7` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> drop index spatidx on t7;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。

六、使用SQLAdvisor工具优化索引

SQLAdvisor是美团开源的一款索引优化建议工具，根据分析SQL中的where条件、聚合条件、多表join等给出优化建议。
1.下载SQLAdvisor
下载地址
2.安装依赖包

#安装依赖包
yum -y install cmake libaio-devel libffi-devel glib2 glib2-devel

#安装percona yum仓库
yum -y install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

#编译安装SQLAdvisor会依赖perconaserverclient_r，由percona-server-shared-56提供
yum -y install Percona-Server-shared-56 --nogpgcheck

# ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so  #做软连接，已存在的话可以忽略

3.编译依赖项

cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./

make && make install

4.安装SQLadvisor

cd SQLAdvisor/sqladvisor/
cmake -DCMAKE_BUILD_TYPE=debug ./
make  #make后生成一个sqladvisor可执行文件，这就是需要用到的工具，拷贝到/usr/bin下

5.SQLadvisor常用选项与示例

-f, --defaults-file     sqls file

-u, --username          username

-p, --password          password

-P, --port              port

-h, --host              host

-d, --dbname            database name

-q, --sqls              sqls

-v, --verbose           1:output logs 0:output nothing

6.命令运行后，对于一些不是非常复杂的SQL都会给出一个索引优化建议

sqladvisor -h 10.3.0.230 -P 3307 -u dba -pHzdba666#@888 -d wp_ark_test -q "SELECT * FROM \`ark_attach\` ORDER BY create_time DESC" -v 1

现实如此呀

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引的相关介绍

MySQL索引一、索引介绍一、索引介绍索引是对数据库表中一列或者多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值得行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。
复制链接

扫一扫