MySQL索引操作

jopen 10年前

创建索引

1.主键索引

    主键也是一种索引(其实索引也是一种key)

2.创建索引语法

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name      [USING index_type]      ON tbl_name (index_col_name,...);     # 其中 index_col_name:  #    col_name [(length)] [ASC | DESC]

        例子

create index book_name_key on book (name); # 普通索引

3.查看表的索引

show index from book;  # 或者  show key from book;

4.使用索引后的性能分析(explain)

explain select * from book where name="The Ruby Programming"  # 会发现key列多了索引名,表示使用了索引

5.删除索引

drop index index_name on table_name;  alter table table_name drop primary key;

 

使用场景

  1. 索引通常被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。所以我们选择索引的列也通常选择出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。

  2. 索引较短的值，所选择的数据类型也尽可能的要小一些。譬如：如果TEXT能满足需求的话，我们就不需要用MEDIUTEXT。

  3. 如果建立联合索引的话，譬如对：t1,t2,t3建立联合索引的话，同时也是对t1和t1,t2建立了索引。但是如果单独指定t2、t3、t1t3、t2t3的值的话，都会用不到索引。

  4. 尽量比较数据类型相同的数据列。例如，INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10)，但是与CHAR(12)或VARCHAR(12)不同。

  5. 尽量不要在where子句中对索引列使用表达式或者函数。如果你在索引列使用函数调用或者更复杂的算术表达式，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。

    WHERE mycol < 4 / 2 使用了索引

    WHERE mycol * 2 < 4 没有使用索引

  6. 在使用LIKE时，尽量不要在开头使用通配符。

    WHERE col_name LIKE '%string%'   -- ng

    WHERE col_name LIKE 'abc%'         --ok

  7. 大多数数据库都索引自动创建的主键字段,但是可别忘了索引外键,它们也是经常使用的键,比如运行查询显示主表和所有关联表的某条记录就用得上。大多数数据库都索引自动创建的主键字段,但是可别忘了索引外键,它们也是经常使用的键,比如运行查询显示主表和所有关联表的某条记录就用得上。

  8. 不要索引blob/text等字段,不要索引大型字段(有很多字符),这样作会让索引占用太多的存储空间。