mysql 索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是batch_name='test111',如果要拿这条数据的话需要些的sql是 SELECT * FROM test WHERE batch_name = 'test111';

一般情况下,在没有建立索引的时候,mysql需要扫描全表及扫描10W条数据找这条数据,如果我在batch_name上建立索引,那么mysql只需要扫描一行数据及为我们找到这条batch_name='test111'的数据,是不是感觉性能提升了好多.

下面是几种常见的单列索引

一：普通索引

这个是最基本的索引,创建普通索引的格式为：

ALTER TABLE <表名> ADD INDEX (<字段>);

比如：

alter table `test` add INDEX (`batch_name`);

或者：

CREATE INDEX batch_name ON `test`(`batch_name`);

二：唯一索引

ALTER TABLE  `test` ADD UNIQUE (`aty_id`);

三：主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column ` );

四：全文索引

ALTER TABLE `table_name` ADD FULLTEXT ( `column`);

解释：一般的数据库搜索都是用的SQL的 like 语句，like 语句是不能利用索引的，每次查询都是从第一条遍历至最后一条，查询效率极其低下。一般数据超过10万或者在线人数过多,like查询都会导致数据库崩溃。 这也就是为什么很多程序都只提供标题搜索的原因了，因为如果搜索内容，那就更慢了，几万数据就跑不动了。

Mysql 全文索引是专门为了解决模糊查询提供的，可以对整篇文章预先按照词进行索引，搜索效率高，能够支持百万级的数据检索。

查看索引：

show keys from tablename;

或者：

show index from tablename;

删除索引：

DROP INDEX index_name ON talbe_name;

例子：drop INDEX is_recycle ON test;

那么，怎样确定和挑选索引列？什么样的字段适合做索引呢？

1：最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列，例如：

SELECT

col_a                           ←不适合作索引列

FROM

Tbl1 LEFT JOIN tbl2

ON tbl1.col_b = tbl2.col_c      ←适合作索引列

WHERE

col_d = expr                    ←适合作索引列

2：唯一索引：考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。

3、使用短索引：如 果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。

较小的索引涉及的磁盘 I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。

4、利用最左前缀：在创建一个 n 列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的，因此，索引中的行也会自动按 state/city 的顺序和 state 的顺序存放

5、不要过度索引：不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。