c mysql创建索引_MySQL 创建索引

1、索引创建原则

(1)搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。

(2)使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。

(3)使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。

(4)利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

(5)不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。

(6)对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

2、常见索引描述

(1)普通索引：ALTER TABLE table_name ADD INDEX index_name(column_name)

最基本的索引，没有任何限制。

(2)唯一索引：ALTER TABLE table_name ADD UNIQUE INDEX index_name(column_name)

与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

(3)主键索引：ALTER TABLE table_name ADD PRIMARY KEY(column_name)

一种特殊的唯一索引，不允许有空值，且每张表只能有一个。

(4)全文索引：ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name)

char，varchar，text类型的列才能创建全文索引，主要用于查找文本中的关键字，并不是直接与索引中的值进行比较。配合match、against关键字才能使用。

(5)前缀索引：ALTER TABLE table_name ADD INDEX index_name(column_name(number))

有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

(6)组合索引：ALTER TABLE table_name ADD INDEX index_name(column_name1, column_name2, column_name3)

为了更多的提高MySQL效率可建立组合索引，遵循“最左前缀”原则。创建组合索引应该将最常用做限制条件的列放在最左边，依次递减。组合索引最左字段用in是可以用到索引的。相当于建立了a，a,b，a,c，a,b,c四个索引。

最左前缀匹配规则：MySQL会一直向右匹配直到遇到范围查询(>，3 and d=4，如果建立(a,b,c,d)顺序索引，d是用不到索引的，如果建立(a,b,d,c)顺序索引则都可以用到。

in和=可以乱序，比如 a = 1 and b =2 and c=3建立(a,b,c)索引可以任意顺序，MySQL查询优化器可以帮你生成执行计划，使得索引可以被识别的形式。

3、索引使用注意事项

(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。

(2)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

(3)组合索引中，第一个索引列没有被匹配，那么在查询中这个索引也不会被MySQL采用。

(4)如果like是以％开始，也不会用到索引。

(5)如果列类型是字符串，那么一定记得在where条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL也不会用到的。

4、两种索引结构

(一)Hash索引

MySQL中，只有Memory(Memory表只存在内存中，断电会消失，适用于临时表)存储引擎显示支持Hash索引，是Memory表的默认索引，尽管Memory表也可以使用B+Tree索引。Hash索引把数据以Hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为Hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

(二)B+Tree索引

B+Tree是MySQL使用最频繁的一个索引数据结构，是Innodb和MyISAM存储引擎默认的索引。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。