mysql index mysql index_MYSQL的索引类型：PRIMARY, INDEX,UNIQUE,FULLTEXT,SPAIAL 有什么区别？各适用于什么场合？

1.MySQL索引类型

MySql常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

KEY（主键索引）

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

（唯一索引）

ALTER TABLE `table_name` ADD UNIQUE (`column`)

INDEX（普通索引）

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

（全文索引）

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

复合索引（包含多列的索引，与单行索引相对）

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

注意：关于主键索引，给表定义主键就会自动创建主键索引，而创建的主键索引在数据库管理工具的“设计表”中的“索引”项中是看不到的。

主键索引只能通过SQL语句查看：

SHOW INDEX FROM table_name

2. MySQL 各种索引的区别

唯一索引()：和“普通索引”类似，不同之处在于索引列的值必须唯一，但是允许为空值。

主键索引()：是一种特殊的唯一索引，不允许空值。

普通索引（INDEX）：最基本的索引，没有任何限制

全文索引（）：只能用于表，用于检索文章中的文本信息。对于大数据量，生成全文索引既费时又费空间。

组合索引：为了进一步提高MySQL的效率，可以创建组合索引，遵循“最左前缀”原则。

例如你正在为一个商场制作一个会员卡系统，该系统有一个会员表，其中包含以下字段：

会员编号 INT，用作主键。可以使用索引，唯一且不为 NULL

会员名称（10）。如果要为会员名称创建索引，则为普通索引。

会员ID号（18），如果要对会员ID号建立索引，可以选择（唯一，不允许重复）

会员电话号码(10)

会员地址(50)

会员备注信息TEXT，会员备注信息，如果需要建立索引可以选择全文搜索

但是，它最适合用于搜索很长的文章。对于较短的文本，例如只有一两行，普通的 INDEX 也可以使用。

创建索引的另一种方法：

CREATE UNIQUE INDEX index_name ON table_name(column_name列表)

删除索引的语法是：

DROP INDEX index_name ON tableName

3.单列索引与组合索引

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

复合索引：一个索引包含多个列。

为了直观地比较两者，创建另一个表格：

CREATE TABLE myIndex (
i_testID INT NOT NULL AUTO_INCREMENT,
vc_Name VARCHAR(50) NOT NULL,
vc_City VARCHAR(50) NOT NULL,
i_Age INT NOT NULL,
i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID)
);

这10000条记录中分散有5条带“ ”的记录，但是城市、年龄的组合不同。

我们来看看这个T-SQL：

SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;

首先考虑创建单列索引：

在该列上建立索引，MYSQL在执行T-SQL时，快速定位到带=的5条记录，取出来放入一个中间结果集，在这个结果集中，先排除不等于“郑州”的记录，再排除i_Age不等于25的记录，最后筛选出唯一满足条件的记录。

虽然在 上建立索引，MYSQL在查询时不需要扫描全表，效率有所提高，但距离我们的要求还有一定距离。同样，在 和 i_Age 上建立单列索引，效率也差不多。

为了进一步压榨MySQL的效率，我们应该考虑建立复合索引。也就是将i_Age建成索引：

ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);

--注意，创建表的时候，长度是50，这里为什么用的是10呢？因为一般一个name的长度不会超过10，这样会加快索引查询速度，减少索引文件的大​​小，提高更新速度。

执行T-SQL的时候，MySQL不需要扫描任何记录就可以找到唯一的记录！！

肯定有人要问了，如果我们在,,i_Age上分别建立单列索引，这样该表就有了三个单列索引，那查询效率会不会和上面说的组合索引一样呢？呵呵，差别很大，比我们的组合索引低多了~~虽然此时有三个索引，但是MySQL只能使用它认为好像效率最高的单列索引。

建立这样的组合指数实际上相当于建立

,,i_年龄

，

像这样的组合索引有三个！为什么没有像 i_Age 这样的组合索引呢？这是因为 MySQL 组合索引是“最左前缀”的结果。简单来说，它只从最左边开始。并不是所有包含这三列的查询都会使用这个组合索引。下面的 T-SQL 会使用它：

    SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="郑州"
    SELECT * FROM myIndex WHREE vc_Name="erquan"

以下将不会被使用：

    SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="郑州"
    SELECT * FROM myIndex WHREE vc_City="郑州"

4. 触发指数

现在你应该知道如何创建和使用索引了吧？但是什么时候需要创建索引呢？一般来说，出现在 WHERE 和 JOIN 中的列需要建立索引，但情况并非总是如此，因为 MySQL 只对 =、IN 触发索引，有时也会对 LIKE 触发索引。

我们来看看这个 SQL：

SELECT t.vc_Name 
FROM testIndex t 
LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name 
WHERE m.i_Age=20 AND m.vc_City='郑州' 

上述SQL语句中，需要为表和i_Age创建索引，由于该表出现在JOIN子句中，因此也需要为其创建索引。

如上所述，只有某些 LIKE 查询才需要创建索引。这是因为当查询以通配符 % 和 _ 开头时，MySQL 不会触发索引。例如：

SELECT * FROM myIndex WHERE vc_Name like'erquan%'

将触发索引，而：

SELECT * FROM myIndex WHEREt vc_Name like'%erquan'

该索引将不被使用。

5. 索引还不够

以上就是使用索引提高查询效率的优点，但是索引也有众所周知的缺点，切记不要滥用索引。

1.索引虽然大大提高了查询速度，但是也会降低更新表的速度，比如执行、和。因为在更新表的时候，MySQL不仅需要保存数据，还需要保存一个索引文件。

2、创建索引会占用索引文件的磁盘空间，一般这个问题不会太严重，但如果在一个大表上创建多个组合索引，索引文件会迅速膨胀。

6. 要点总结

索引类型可以分为：主键索引、唯一索引、普通索引、全文索引

根据索引列数可分为：单列索引、组合索引

创建索引有两种方法：ALTER TABLE ... 和 INDEX ...

显示已创建的索引：SHOW INDEX FROM

删除索引：DROP INDEX ...

几种索引的区别及使用场景：

区别：主键索引，列值唯一，且不为空（特殊唯一索引）；唯一索引，列值唯一，可以为空；普通索引：列值不受限制；全文索引，适合大文本。

使用方式：id一般使用主键索引；身份证一般使用唯一索引；姓名一般使用普通索引；备注信息一般使用全文索引。

单列索引与复合索引：

当有多个条件约束时，使用组合索引比单列索引要好，组合索引包含多个列，按照“最左前缀”原则建立多个索引组合。

触发索引的情况：

在 WHERE 和 JOIN 子句中，<、、>=、、IN 和 LIKE 如果不以 % 或 _ 开头，将触发索引。

索引的缺点：

因为需要同时维护索引文件，会降低数据更新的速度，如果在一个大表上建立多个组合索引，索引文件会迅速膨胀，占用过多的内存。