介绍

  索引减少存储引擎扫描数据的数量,索引文件大小通常小于数据文件大小.因为b-tree是按健值顺序存放,所以在进行排序避免使用了临时表来进行排序的磁盘消耗,提高了mysql的处理能力.

  索引并不是越多越好.随着索引的增加数据库也会增加写的操作,在对数据更新的额同时也会对索引进行更新维护(即修改数据时间变长).在Innodb中使用了插入缓存,即多次插入会合并成一次执行.太多的索引也会增加查询优化器的选择时间,；例如在一条查询中有多个索引可以使用,则会增加查询优化器分析查询的时间.总结太多不合适的索引对读写都不是很友好.

  一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此.因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引.

使用原则

  搜索的索引列,不一定是所要选择的列.换句话说,最适合索引的列是出现在WHERE子句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列.

  使用唯一索引.考虑某列中值的分布.索引的列的基数越大,索引的效果越好.例如:存放出生日期的列具有不同值,很容易区分各行.而用来记录性别的列,只含有1和0,则对此列进行索引没有多大用处.因为不管搜索哪个值,都会得出大约一半的行.

  使用短索引.如果对字符串列进行索引,应该指定一个前缀长度,例如:有一个CHAR(200),如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引.对前10个或20个字符进行索引能够节省大量索引空间,也可能会使查询更快.较小的索引涉及的磁盘 IO 较少,较短的值比较起来更快.更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值.因此,MySQL也可以在内存中容纳更多的值.这样就增加了找到行而不用读取索引中较多块的可能性.

  利用最左前缀.在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引.多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行.这样的列集称为最左前缀.

  不过度使用索引.不要以为索引'越多越好',什么东西都用索引是错误的.每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能.在修改表的内容时,索引必须进行更新,有时可能需要重构.因此,索引越多,所花的时间越长.如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度.此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要花费时间.创建多余的索引给查询优化带来了更多的工作.索引太多,也可能会使MySQL选择不到所要使用的最好索引.只保持所需的索引有利于查询优化.

使用注意事项

  1. 索引不会包含有NULL值的列.只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效.
  2. 使用短索引.短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
  3. 索引列排序.查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的.因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个列的排序.如果需要最好给这些列创建复合索引.
  4. like语句.不推荐使用like操作,like '%aaa%' 不会使用索引而like 'aaa%'可以使用索引.
  5. 不在列上进行运算.例如select * from users where YEAR(adddate)<2007;会在每个行上进行运算,将导致索引失效而进行全盘扫描,可以改成select * from users where adddate<‘2007-01-01';.
  6. 不使用NOT和<>.

索引类型

普通索引

  最基本的索引,没有任何限制.如果是CHAR,VARCHAR类型,length可以小于字段实际长度.如果是BLOB和TEXT类型,必须指定length相同.

-- 直接创建索引
CREATE INDEX indexName ON myTable(`myField`(length));
-- 修改表结构创建索引
ALTER myTable ADD INDEX indexName ON (`myField`(length));
-- 创建表的时候指定
CREATE TABLE myTable(
  `id` int not null,
  `user` varchar(16) not null,
  INDEX indexname(`user`(10))
);
-- 删除索引
DROP INDEX indexname ON myTable;

唯一索引

  与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值.如果是组合索引,则列值的组合必须唯一.

-- 直接创建索引
CREATE UNIQUE INDEX ON myTable(`myField`(length)) 
-- 修改表结构
ALTER myTable ADD UNIQUE INDEX (`myField`(length)) 
-- 创建表结构时候指定
CREATE TABLE myTable(   
  `id` INT NOT NULL,    
  `username` VARCHAR(16) NOT NULL,   
  UNIQUE indexName(`username`(length))   
);

主键索引

  特殊的唯一索引,不允许有空值.一般是在建表的时候同时创建主键索引.

-- 直接创建索引
ALTER TABLE myTable ADD PRIMARY KEY(`myField`);
-- 创建表结构指定方式1
CREATE TABLE mytable(   
  `id` INT NOT NULL,    
  `user` VARCHAR(16) NOT NULL,   
  PRIMARY KEY(id)   
);
-- 创建表结构指定方式2
CREATE TABLE mytable(  
    `id` int not null auto_increment primary key,
    `user` VARCHAR(16) NOT NULL,
);

组合索引

  组合索引拥有开销少,效率高,覆盖索引的特点.可以代替多个单一索引.假设创建了三个字段的联合索引(Field1,Field2,Field3)相当于创建了索引(Field1),(Field1,Field2),(Field1,Field2,Field3)三个索引.

  还需要注意组合索引的最左匹配原则:即查询条件中包含最左边索引的列就可以使用到联合索引.演示如下:

-- 创建用户表
create table blog_user(
  `id` int not null auto_increment primary key,
  `user` varchar(32) not null default 'angel',
  `study` varchar(32) not null default 'html',
  `city` varchar(32) not null default 'sjz',
  key `user_study_city_index` (`user`,`study`,`city`)
);
-- 拆入数据
insert into blog_user (id,user,study,city) values
(1,'qvbilam','php','hz'),
(2,'erhua','php','sjz'),
(3,'zhaocaixia','html','bj'),
(4,'wanglongwei','mysql','nj'),
(5,'quanguoxing','go','sh')

最左列查询

-- 查询条件为最左列user
-- 使用到索引user_study_city_index
-- 索引长度130
explain select * from blog_user where user='qvbilam'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_user
   partitions: NULL
         type: ref
possible_keys: user_study_city_index
          key: user_study_city_index
      key_len: 130
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

非最左列查询

-- 查询条件为第二列或第三列
-- 有使用索引,可能使用的索引为空.相当于对全索引进行遍历
-- 索引长度390
explain select * from blog_user where study='php'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_user
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_study_city_index
      key_len: 390
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

包含最左列组合查询

-- 1(user),2(study)和2(study),1(user)结果一样
-- 索引长度260
explain select * from blog_user where user='qvbilam' and study='php'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_user
   partitions: NULL
         type: ref
possible_keys: user_study_city_index
          key: user_study_city_index
      key_len: 260
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
-- 1(user),3(city)和3(city),1(user)结果一样
-- 索引长度130
explain select * from blog_user where city='bj' and user='qvbilam' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_user
   partitions: NULL
         type: ref
possible_keys: user_study_city_index
          key: user_study_city_index
      key_len: 130
          ref: const
         rows: 1
     filtered: 20.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
-- 全部查询条件
-- 索引长度390
explain select * from blog_user where user='qvbilam' and city='bj' and study='php' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_user
   partitions: NULL
         type: ref
possible_keys: user_study_city_index
          key: user_study_city_index
      key_len: 390
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

不包含最左列查询

-- 2(study),3(city)和3(city),2(study)结果一样
-- 索引长度390
explain select * from blog_user where study='php' and city='bj'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: blog_user
   partitions: NULL
         type: index
possible_keys: NULL
          key: user_study_city_index
      key_len: 390
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
Last modification：June 2nd, 2020 at 10:27 am
© 允许规范转载