MySQL的索引

索引是一种特殊的数据结构,用来快速查询数据库表中的特定记录。MySQL中所有的数据类型都可以被索引。MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引。

简介

索引由数据库表中的一列或多列组合而成,其作用是提高对表中数据的查询速度。

索引是创建在表上的,是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询的速度。通过索引,查询数据时可以不必读完记录的所有信息,而只是查询所有引列。

不同的存储引擎定义了每个表的最大索引数和最大索引长度。
索引的优点是可以提高检索数据的速度。缺点是创建和维护索引需要耗费时间和物理空间;增删查改要动态维护索引,造成数据维护速度降低。

索引可以提高查询速度,但会影响写入速度。插入大量数据的时候,最好的方式是先删除表中的索引,然后插入数据。写入完成之后,再创建索引。

分类

  1. 普通索引
    创建普通索引时,不附加任何限制条件,其值由字段本身的完整性约束条件决定。
  2. 唯一性索引
    使用unique参数设置索引为唯一性索引。主键就是一种特殊的唯一性索引。
  3. 全文索引
    使用FULLTEXT参数设置为全文索引。只能建立在char、varchar或者text类型的字段上。默认不支持区分大小写,只有使用二进制排列之后可以执行区分大小写的全文索引。
  4. 单列索引
    在单个字段上创建索引,只根据该字段进行索引。
  5. 多列索引
    在多个字段上创建索引。
  6. 空间索引
    使用spatial参数设置索引为空间索引。只有MyISAM引擎支持空间索引。

设计原则

  1. 选择唯一性索引
  2. 为经常需要排序、分组和联合操作的字段建立索引
  3. 为常作为查询条件的字段建立索引
  4. 限制索引的数目
  5. 尽量使用数据量少的索引
  6. 尽量使用前缀来索引
  7. 删除不在使用或者很少使用的索引

创建索引

\\创建表的时候创建索引
mysql> create table index1(id int, name varchar(20), sex boolean, index(id));
mysql> show create table index1\G
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `id` (`id`)   //索引被创建
) ENGINE=InnoDB DEFAULT CHARSET=latin1
\\查询索引情况
mysql> explain select * from index1 where id=1\G

**** 1. row ******
           id: 1
  select_type: SIMPLE
        table: index1
   partitions: NULL
         type: ref
possible_keys: id   //索引被使用
          key: id   //索引字段是id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
\\创建唯一性索引
mysql> create table index2(id int unique, name varchar(20), unique index index2_id(id asc));

mysql> show create table index2\G
*************************** 1. row ***************************
       Table: index2
Create Table: CREATE TABLE `index2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index2_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
\\创建全文索引,注意只有MyISAM支持
mysql> create table index3(id int unique,
    -> info varchar(20),
    -> fulltext index index3_info(info))
    -> engine=MyISAM;

mysql> show create table index3\G
**** 1. row *****
       Table: index3
Create Table: CREATE TABLE `index3` (
  `id` int(11) DEFAULT NULL,
  `info` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  FULLTEXT KEY `index3_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

\\创建单列索引
mysql> create table index4(id int, subject varchar(40), index index4_st(subject(10)) );
\\对于字符型的数据,可以不用查询全部信息,而只查询前面若干字符信息
mysql> show create table index4\G
***** 1. row ******
       Table: index4
Create Table: CREATE TABLE `index4` (
  `id` int(11) DEFAULT NULL,
  `subject` varchar(40) DEFAULT NULL,
  KEY `index4_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\创建多列索引
mysql> create table index5(id int, name varchar(20), sex char(4), index index5_ns(name,sex));
mysql> show create table index5\G
********* 1. row **********
       Table: index5
Create Table: CREATE TABLE `index5` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  KEY `index5_ns` (`name`,`sex`)    \\索引建立
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
\\查询索引情况
mysql> explain select * from index5 where name='hjh'\G
************* 1. row *****
           id: 1
  select_type: SIMPLE
        table: index5
   partitions: NULL
         type: ref
possible_keys: index5_ns
          key: index5_ns
      key_len: 23
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
\\使用多列索引要注意,只有使用了索引中的第一个字段才会触发索引。如果没有使用索引中的第一个字段,那么这个多列索引就不会起作用。
mysql> explain select * from index5 where sex='n'\G
********** 1. row **********
           id: 1
  select_type: SIMPLE
        table: index5
   partitions: NULL
         type: ALL
possible_keys: NULL     //没有起作用
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
\\在已有表上建立索引
mysql> create index index7_id on user(id);
mysql> show create table user\G
********* 1. row *******
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index7_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\创建唯一性索引
mysql> create unique index index_name8 on user(name);
mysql> show create table user\G
************** 1. row **************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name8` (`name`),    //以那么为唯一性索引
  KEY `index7_id` (`id`)    //id为普通索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\建立多列索引
mysql> create index index11_na on user(name,address);
mysql> show create table user\G
************ 1. row *************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),       //主键
  UNIQUE KEY `index_name8` (`name`),    //唯一性索引
  KEY `index7_id` (`id`),       //普通索引
  KEY `index10_addr` (`address`),   //单列索引
  KEY `index11_na` (`name`,`address`)   //多列索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\使用alter table来创建索引
mysql> alter table user add index index13_phone(phone);
mysql> show create table user\G
********** 1. row ************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name8` (`name`),
  KEY `index7_id` (`id`),
  KEY `index10_addr` (`address`),
  KEY `index11_na` (`name`,`address`),
  KEY `index13_phone` (`phone`)     //使用alter table新增索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1

\\删除索引
mysql> drop index index13_phone on user;
mysql> show create table user\G
*********** 1. row ******
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(40) NOT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name8` (`name`),
  KEY `index7_id` (`id`),
  KEY `index10_addr` (`address`),
  KEY `index11_na` (`name`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Leave a Reply

Your email address will not be published. Required fields are marked *