mysql的索引类型与创建_mysql索引总结----mysql 索引类型以及创建(转载)

一、索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

二、索引的类型

在MySQL中，索引分为两大类：聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。

在这两大类的索引类型下，还可以将索引分成四个小类：

1，普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

2，唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。

3，全文索引：全文索引(FULLTEXT)仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

4，组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

三、索引设计：

索引字段尽量使用数字型(简单的数据类型)

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

尽量不要让字段的默认值为NULL

在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。

所以我们在数据库设计时尽量不要让字段的默认值为NULL，应该指定列为NOT NULL，除非你想存储NULL。你应该用0、一个特殊的值或者一个空串代替空值。

前缀索引和索引选择性

对串列进行索引，如果可能应该指定一个前缀长度。

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。

例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指，不重复的索引值(基数)和数据表中的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

决窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长(以便节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。例如以下查询：

select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;

select count(*) as cnt,left(city,7) as perf from sakila.city_demo group by city order by cnt desc limit 10;

"""

直到这个前缀的选择性接近完整列的选择性。

"""

#计算合适的前缀长度的另一个方法就是计算完整列的选择性，

#并使前缀的选择性接近于完整列的选择性，如下：

select count(distinct city)/count(*) from sakila.city_demo;

select count(distinct left(city,7))/count(*) from sakila.city_demo;

使用唯一索引

考虑某列中值的分布。索引的列的基数越大，索引的效果越好。

例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M” 和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

使用组合索引代替多个列索引

一个多列索引(组合索引)与多个列索引MySQL在解析执行上是不一样的，如果在explain中看到有索引合并(即MySQL为多个列索引合并优化)，应该好好检查一下查询的表和结构是不是已经最优。

注意重复/冗余的索引、不使用的索引

MySQL允许在相同的列上创建多个索引，无论是有意还是无意的。大多数情况下不需要使用冗余索引。

对于重复/冗余、不使用的索引：可以直接删除这些索引。因为这些索引需要占用物理空间，并且也会影响更新表的性能。

四、操作索引

创建普通索引

CREATE INDEX index_name ON table_name(col_name);

创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

创建普通组合索引

CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

创建唯一组合索引

CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

删除索引

直接删除索引

DROP INDEX index_name ON table_name;

修改表结构删除索引

ALTER TABLE table_name DROP INDEX index_name;