MySql索引

发表于 | 分类于

MySql索引

[TOC]

神马是MySql索引

​ 数据库索引是一种数据结构，目的是提高表的操作速度。要创建的索引，应当认为哪列将用于使SQL查询，创建对这些列的一个或多个索引。实际上，索引也是表，其中保存主键或索引字段的指针并指向每个记录到实际的表的类型。

​ 索引一方面可以提高数据的检索速度，不过另一方面过多的索引会降低表的gen更新速度，（因为在更新表的同时也会更新索引。）

索引的基本用法

  1. 创建与修改普通索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE INDEX indexName ON mytable(username(length)); 
    //创建表的时候直接指定
    CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
    );  
    //修改索引
    ALTER mytable ADD INDEX [indexName] ON (username(length)) 
    //删除索引
    DROP INDEX [indexName] ON mytable;

  2. 创建与修改复合索引

    复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。

    1
    2
    3
    create index index_name on tbl_name(index_col_name,...);
    //修改索引
    alter table tbl_name add index index_name on (index_col_name,...);

  3. 创建与修改唯一索引

    唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
    //创建表的时候直接指定
    CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    UNIQUE [indexName] (username(length))  
    );  
    //修改索引
    ALTER table mytable ADD UNIQUE [indexName] (username(length))

  4. 创建与修改主键索引

    主键索引值必须是唯一的，且不能为NULL。（默认创建主键都会为该列创建索引）

    1
    2
    3
    ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
    //删除索引
    ALTER TABLE testalter_tbl DROP PRIMARY KEY;

  5. 创建与修改全文索引

    它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE article (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        content TEXT,
        FULLTEXT (title, content) --在title和content列上创建全文索引
    );
    //修改索引
    ALTER TABLE article ADD FULLTEXT INDEX fulltext_article (title, content)

    如何使用全文索引

    1
    2
    //在article表的title和content列中全文检索指定的查询字符串
    SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

    MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

使用索引的注意事项

  • 不要在列上进行运算或者函数

    在索引列上进行运算或使用函数会导致索引失效

    1
    select * from user where YEAR(birthday)<1990 and code="">

  • 不要为多个列创建索引

    为多个列创建独立的索引，大部分并不能提高MySQL的查询性能。因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

  • 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  • 范围查询对多列查询的影响

    如果查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。

    1
    where age>10 and age<90 and name="lianggzone" >

    这是因为age是范围查询，导致多列索引t_index(age,name)，无法用到name索引。

    ​