5.MySQL索引(一)

索引设计准则:

  • 1.索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间, 而且会影响INSERT DELETE UPDATE等语句的性能,因为当表中的数据更改的同时, 索引也会进行调整和更新.
  • 2.避免对经常更新的表进行过多的索引,并且索引中的列尽可能少, 而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段
  • 3.数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可以比遍历索引的时间还要短, 索引可能不会产生优化效果
  • 4.在条件表达式中经常用到不同值较多的列上建立索引,在不同值少的列上不要建立索引. 比如学生表的"性别" 字段上只有"男"与"女"两个不同值,因此无须建立索引.如果建立索引不但不会提高 查询效率,反而会严重降低更新速度.
  • 5.当唯一性是某种数据本身的特征时,指定唯一索引,使用唯一索引需能确保定义的列的数据完整性, 以提高查询速度.
  • 6.在频繁进行排序或分组(即进行group by 或 order by操作)的列上建立索引, 如果街参与序的列有多个 可以在这些列上建立组合索引.

创建表的时候创建索引

    CREATE TABLE table_name [col_name data_type]
    [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length]) [ASC|DESC]

UNIQUE FULLTEXT 和SPATIAL为可选参数,分别表示唯一索引 全文索引 和空间索引

  • INDEX与 KEY为同义词,两者作用相同,用来指定创建索引
  • index_name 指定索引的名称
  • col_name 为需要创建索引的字段列
  • length 可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
  • ASC DESC 指定升序或者降序的索引值存储

1.创建普通索引

最基本的索引类型, 没有唯一性之类的限制,其作用只是加快对数据库的访问速度.

在book表中的year_publication字段上建立普通索引,SQL语句如下:

CREATE TABLE book
(
    bookid        INT NOT NULL,
    bookname    VARCHAR(255) NOT NULL,
    authors        VARCHAR(255) NOT NULL,
    info        VARCHAR(255) NOT NULL,
    comment        VARCHAR(255) NULL,
    year_publication VARCHAR(255) NOT NULL,
    INDEX(year_publication)

)

使用 SHOW CREATE TABLE table_name \G 查看表结构.

2.创建唯一索引

例:创建一个表t1,在表中的id字段关键字创建唯一索引.

CREATE TABLE t1
(
    id INT NOT NULL,
    ame char(30) NOT NUL,
    UNUE INDEX  uniqidx(id)
)

3.创建单列索引

CREATE TABLE t2
(
    id INT NOT NULL,
    name CHAR(50) NULL,
    INDEX SingleIdx(name(20))
)

4.创建组合索引

组合索引是在多个字段上创建一个索引

例:创建表t3,在表中的id name 和 age字段上建立组合索引

CREATE TABLE t3
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(255),
    INDEX MultiIdx(id,name,age(100))    
)

组合索引可起几个索引的作用,但是使用时并不是随便查询哪个字段都可以使用索引,而是遵从 "最左前缀",利用索引中最左边的列集来匹配行,这样的列集称为最左前缀. 例如这里由id,name,age 3个字段构成的索引,索引行中按 id/name/age的顺序存放,索引可以搜索下面的字段组合: (id,name,age) (id,name) 或者 id ,如果列不构成索引最左面的前缀,MySQL不能使用局部 索引,如(age)或者(name,age) 组合则不能使用索引查询.

5.创建全文索引

FULLTEXT 全文索引 可以用于全文搜索, 只有MyISAM存储引擎支持 FULLTEXT索引,并且 只为char varchar 和 text列, 索引总是对整个列进行,不支持局部(前缀) 索引.

例: 创建表 t4, 在表中的info字段上建立全文索引,SQL语句如下

CREAE TABLE t4
(
    id  INT NOT NULL,
    name CHAR(30) NOT NULL,
    age  INT NOT NULL,
    info VARCHAR(255),
    FULLTEXT INDEX FullTextIdx(info)
)ENGINE=MyISAM;

因为MySQL5.5中默认存储引擎为InnoDB, 在这里创建表时需要修改表的存储引擎为MyISAM.

6.创建空间索引

空间索引必须在MyISAM类型的表中创建,且空间类型字段必须为非空.

例:创建表t5, 在空间类型为GEOMETRY的字段上创建空间索引,SQL语句如下

CREATE TABLE t5
(
    g GEOMETRY NOT NULL,
    SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM

注意:空间类型字段值的非空约束,并且表的存储引擎为MyISAM

在已经存在的表上创建索引

1.使用ALTER TABLE语句创建索引

ALTER TABLE table_name ADD[UNIQE|FULLTEXT|SPATIAL] [INDEX|KEY] 
[index_name](col_name[length])[ASC|DESC]

2.使用CREATE INDEX创建索引

CREATE [UNIQUE|FULLTEXT|SPTIAL] INDEX index_name
ON table_name(col_name[length],...) [ASC|DESC]

删除索引

1.使用ALTER TABLE删除索引 ,基本语法如下

ALTER TABLE table_name DROP INDEX index_name

查看是否删除

        SHOW CREATE table table_name \G;
        SHOW INDEX FROM table_name \G;

2.使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

查看是否删除

SHOW CREATE table table_name \G;
SHOW INDEX FROM table_name \G;

查看查询语句是否使用索引:

explain select * from table_name where conditions \G ;