mysql索引的创建,测试,使用

1、分类

    MySQL索引分为普通索引唯一索引主键索引组合索引全文索引

  索引不会包含有null值的列,索引项可以为null(唯一索引、组合索引等),但是只要列中有null值就不会被包含在索引中。

(0)创建表:

create table test(
id1 int ,
id2 int,
id3 int,
id4 int
);

(1)创建普通索引:

  create index index_name on table(column);

    或者创建表时指定,create table(..., index index_name column);
  eg:
    CREATE index index_id1 on test(id1); -- 普通索引

(2)创建唯一索引:

  类似普通索引,索引列的值必须唯一(可以为空,这点和主键索引不同)
    create unique index index_name on table(column);
  或者创建表时指定unique index_name column
  eg:
    CREATE unique index index_id2 on test(id2); --唯一索引

(3)创建主键索引:

  特殊的唯一索引,不允许为空,只能有一个,一般是在建表时指定primary key(column)

(4)创建组合索引:

  在多个字段上创建索引,遵循最左前缀原则。

  alter table t add index index_name(a,b,c);
  eg:
    alter table test add index index_id34(id3,id4); --组合索引

  测试组合索引的使用情况:https://www.cnblogs.com/lzghyh/p/13229170.html

(5)全文索引:

  主要用来查找文本中的关键字,不是直接与索引中的值相比较,像是一个搜索引擎,配合match against使用,现在只有char,varchar,text上可以创建全文索引。在数据量较大时,先将数据放在一张没有全文索引的表里,然后再利用create index创建全文索引,比先生成全文索引再插入数据快很多。

效果图:

2、何时使用索引

    MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”,倒不如说,和全表扫描比起来,去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用(A,B)的组合索引最佳,B+树根据(A,B)来排序。

    (1)主键,unique字段;

    (2)和其他表做连接的字段需要加索引;

    (3)在where里使用>,≥,=,<,≤,is null和between等字段;

    (4)使用不以通配符开始的like,where A like 'China%';

    (5)聚集函数MIN(),MAX()中的字段;

    (6)order by和group by字段;

3、何时不使用索引

    (1)表记录太少;

    (2)数据重复且分布平均的字段(只有很少数据值的列);

    (3)经常插入、删除、修改的表要减少索引,因为修改也要修改索引的数据,耗费性能;

    (4)text,image等类型不应该建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引),因为索引占用的物理空间,数据量太大浪费硬盘;

    (5)MySQL能估计出全表扫描比使用索引更快时,不使用索引;

4、索引何时失效

    (1)组合索引未使用最左前缀,例如组合索引(A,B),where B=b不会使用索引;

    (2)like未使用最左前缀,where A like '%China';

    (3)搜索一个索引而在另一个索引上做order by,where A=a order by B,只使用A上的索引,因为查询只使用一个索引 ;

    (4)or会使索引失效。如果查询字段相同,也可以使用索引。例如where A=a1 or A=a2(生效),where A=a or B=b(失效)

    (5)如果列类型是字符串,要使用引号。例如where A='China',否则索引失效(会进行隐式类型转换);

    (6)在索引列上的操作,函数(upper()等)、or、!=(<>)、not in等;

5、explain语句

      type列字段为All,未使用索引;为ref,使用索引

      ALL                  全表扫描

      index                索引全扫描

      range               索引范围扫描,常用语<,<=,>=,between等操作

      ref                    使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中

      eq_ref               类似ref,区别在于使用的是唯一索引,使用主键的关联查询

      const/system    单条记录,系统会把匹配行中的其他列作为常数处理,如主键或唯一索引查询

      null                   MySQL不访问任何表或索引,直接返回结果

   还有key字段表示用到的索引,没有用到为null

  更多解释参考:

    https://www.cnblogs.com/lzghyh/p/13229286.html

all:

 ref:



原文地址:https://www.cnblogs.com/lzghyh/p/13229302.html