您的位置:首页 - 教程 - MYSQL - 正文
mysql索引种类和操作

部分来源:

  1. mysql索引总结----mysql 索引类型以及创建 该来源,部分命令运行失败,故在本篇文章中进行了修改。所有命令,都已经进行过运行测试。
  2. mysql 添加索引 mysql 如何创建索引

索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
假设要从10万条查询中查询某个数据,在没有索引的情况下,数据库会遍历全部10万条数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。

索引分类及命令

以数据库data_index,表格test_index,字段name为例

  1. 普通索引
    最基本的索引,它没有任何限制,索引类型为Normal,索引方法为BTREE。

    直接创建普通索引
    CREATE INDEX index_name ON test_index(name);
    修改表结构的方式添加普通索引
    ALTER TABLE test_index ADD INDEX index_name(name);
    创建表的时候同时创建普通索引
    CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title)
    );
    删除索引
    DROP INDEX index_name ON table_name;
  2. 唯一索引
    与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。
    如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似,只是添加关键字UNIQUE。
    索引类型为Unique,索引方法为BTREE。

    直接创建唯一索引 
    CREATE UNIQUE INDEX index_name ON test_index(name);
    修改表结构的方式添加唯一索引 
    ALTER TABLE test_index ADD UNIQUE INDEX index_name(name);
    创建表的时候同时创建唯一索引
    CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE INDEX index_name (title)
    );
    删除索引
    DROP INDEX index_name ON table_name;
  3. 全文索引(FULLTEXT)
    MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;
    他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。
    对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。
    不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
    索引类型为Full Text,索引方法为空。

    直接创建全文索引
    CREATE FULLTEXT INDEX index_name ON table_name_full(title);
    修改表结构的方式添加全文索引 
    ALTER TABLE table_name_full ADD FULLTEXT INDEX index_name(title);
    创建表的时候同时创建全文索引
    CREATE TABLE `table_name_full` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT INDEX index_name (title)
    );
    删除索引
    DROP INDEX index_name ON table_name;
  4. 单列索引、多列索引
    多个单列索引与单个多列索引的查询效果不同
    因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

  5. 组合索引(最左前缀)
    平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。
    现在对表格test_index删除所有索引,并且添加组合索引

    ALTER TABLE test_index ADD INDEX index_name_desp(name,desp);

    建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:
    name,desp
    name
    为什么没有desp这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引。
    示例如下:

    mysql语句:
    select * from test_index where name = "cuiyf1465";
    分析结果:
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  test_index      ref index_name_desp index_name_desp 768 const   1   100 
    其中,key值为index_name_desp 表示使用到了索引
    
    mysql语句:
    select * from test_index where desp = "handsome092";
    分析结果:
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  test_index      ALL                 96903   10  Using where
    其中,key值为空 表示未使用到索引
    
    mysql语句:
    select * from test_index where name = "cuiyf034" and desp = "handsome092";
    分析结果:
    id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
    1   SIMPLE  test_index      const   index_name_desp index_name_desp 1536    const,const 1   100 
    其中,key值为index_name_desp 表示使用到了索引    

注意事项:

  1. 索引不会包含有NULL值的列
    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
  2. like语句操作
    一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
  3. 不要在列上进行运算
    例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,
    因此我们可以改成:select * from users where adddate<’2007-01-01′。
    

评论: