mysql中的索引

        索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

1.索引的特征

• 高效性 利用索引可以提高数据库的查询效率
• 唯一性 索引可以确保所查的数据的唯一性
• 完整性 用户可以加速表和表之间的连接，实现表与表之间的参照完整性
• 特殊能力 通过使用索引，可以在查询过程 中，使用优化隐藏器，提高系统性能。

2.索引的缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。

2.因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘 空间的索引文件。

3. 如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立 最优秀的索引，或优化查询语句。 

3.索引的分类

主键索引 ：在数据库关系图中为表定义一个主键将自动 创建主键索引。 

唯一索引  ：不允许具有索引值相同的行，从而禁止重复 的索引或键值。

常规索引 ：最基本的索引类型，没有 唯一性之类的限制。

全文索引  ：搜索引擎的关键技术，用于检索文本信息， 可以是词语或者段落。

3.1 主键索引

        语法：

CREATE TABLE `表名` (
`字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,
#或 PRIMARY KEY(`字段1`)
)

 注意：

• 某一个属性组能唯一标识一条记录；

• 最常见的索引类型；

• 确保数据记录的唯一性；

• 确定特定数据记录在数据库中的位置。

3.2 唯一索引

        语法：

CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL UNIQUE,
#或 UNIQUE KEY(`字段1`)
)

注意：

• 快速定位特定数据；

• index和key关键字都可设置常规索引；

• 应加在查找条件的字段；

• 不宜添加太多常规索引，影响数据的插入、删除和修改操作。 

3.3 常规索引

        语法：

CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL,
INDEX/KEY(`字段1`)
)

注意：

• 避免同一个表中某数据列中的值重复；

• 对比主键索引只能有一个,唯一索引可有多个。 

3.4 全文索引

        语法：

CREATE TABLE `表名` (
`字段1` VARCHAR(32) NOT NULL,,
fulltext key (字段名，字段名，字段名) with parser ngram
)ENGINE=innodb

SELECT <字段表> FROM <表名> 
WHERE MATCH(字段) 
AGAINST (‘要搜索的关键词’);

全文索引的注意：

1.Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引。

2.只有char、varchar、text类型字段能创建全文索引。

3.当大量写入数据时，建议先写入数据，后再建立全文索引，提高效率。

4.Mysql内置ngram 解析器，可以解析中日韩三国文字。有汉字的一定要启用它。

5.英文分词用空格，逗号；中文分词用 ngram_token_size 设定.

 

注意需要在配置文件中加入：

[mysqld]

ngram_token_size=2 

4.索引的管理 

 4.1查看索引 

SHOW INDEX(或KEYS) FROM 表名;

4.2 修改索引 

ALERT TABLE 表名 ADD 索引类型（数据列名）; ALTER TABLE add FULLTEXT INDEX (字段名1，字段2，，) [ WITH PARSER ngram]

4.3 删除索引

删除索引
ALTER TABLE 表名 DROP PRIMARY KEY;
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;

5.优化原则—创建索引  

1.【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值 禁止被更新 。

2.【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。

3.【建议】主键的名称以 pk 开头，唯一键以 uni 或 uk 开头，普通索引以 idx 开头，一律 使用小写格式，以字段的名称或缩写作为后缀。

4.【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。

5.【建议】单个表上的索引个数 不能超过6个 。

6.【建议】在建立索引时，多考虑建立 联合索引 ，并把区分度最高的字段放在最前面。

7.【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。

8.【建议】建表或加索引时，保证表里互相不存在 冗余索引 。 比如：如果表里已经存在key(a,b)， 则key(a)为冗余索引，需要删除 。