MySQL之索引、主键索引、唯一索引、常规索引、全文索引、索引使用场景、索引失效场景、sql优化的规则等

1.什么是索引

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

四个特性：

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

2.索引的缺点

1. 虽然索引大大提高了查询速度，同时却会降低表更新的速度，如对表进行INSERT、UPDATE和DELETE。
2. 因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引回占用磁盘空间的索引文件。
3. 如果在一个大表上创建了多种组合索引，索引文件的膨胀很快。

索引只是提高效率的一个因素，如果MySQL有大量数据的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

3.索引分类

索引的分类：

1. 主键索引
   在数据库关系图中为表定义一个主键将自动创建主键索引。
2. 唯一索引
   不允许具有索引值相同的行，从而禁止重复的索引或键值。
3. 常规索引
   最基本的索引类型，没有唯一性之类的限制。
4. 全文索引
   搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。
   聚簇索引（主键） / 非聚簇索引（唯一，常规，全文）

1、主键索引

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

说明：

? 1.某一个属性组能唯一标识一条记录；

? 2.最常见的索引类型；

? 3.确保数据记录的唯一性；

? 4.确定特定数据记录在数据库中的位置。

2、唯一索引

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

说明：

? 1.快速定位特定数据；

? 2.index和key关键字都可设置常规索引；

? 3.应加在查找条件的字段；

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

3、常规索引

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

说明：

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

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

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 设定.

注意需要在配置文件中加入（默认检索子的个数，优先检索2个子的，如果存储全是成语的话，可以设置成4）：

[mysqld]

ngram_token_size=2

4.索引的管理

1、查看索引

SHOW INDEX(或KEYS) FROM 表名;

2、删除索引

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

3、修改索引

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

5.索引的使用规则

1、最左前缀匹配原则

对于联合索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。

比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。

mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。

比如a = 1 and b = 2 and c > 3 and d = 4，

如果建立（a,b,c,d）顺序的索引，d是用不到索引的，

如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)

表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，

使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。（比如，我们会选择学号做索引，而不会选择性别来做索引。）

3、=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4、索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致

索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<‘2007-01-01’。

比如：Flistid+1>‘2000000608201108010831508721‘。原因很简单，假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。

5、尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

7、使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

8、索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

9、like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，过多的创建索引反而不能提升速度。

6.索引使用的场景

1、匹配全值：对索引中所有列都指定具体值，即对索引中的所有列都有等值匹配的条件。
2、匹配值的范围查询：对索引的值能够进行范围查找
3、匹配最左前缀：仅仅使用索引中的最左边列进行查询。比如组合索引（col1,col2,col3）能够被col1，col1+col2，col1+col2+col3的等值查询利用到的。
4、仅对索引进行查询：当查询列都在索引字段中。即select中的列都在索引中。
5、匹配列前缀：仅仅使用索引的第一列，并且只包含索引第1列的开头部分进行查找。例如：WHERE title LIKE ‘xxx%’
6、索引部分等值匹配，部分范围匹配
7、若列名是索引，则使用column_name is null就会使用索引

7.索引失效的场景

1、以%开头的like查询
2、数据类型出现隐式转化，不会使用索引
3、组合索引，不满足最左原则，不使用符合索引
4、估计使用索引比全表扫描还慢，则不要使用索引
5、用or分割条件，若or前后只要有一个列没有索引，就都不会用索引
6、使用 != 或 <> 操作符时 ：尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。
7、对字段进行null值判断：应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
8、避免select： 在解析的过程中，会将’*’ 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

8.SQL优化原则

1. 建议使?预编译语句进?数据库操作
2. 避免数据类型的隐式转换
3. 充分利?表上已经存在的索引
4. 禁?使? SELECT * 必须使? SELECT 查询
5. 禁?使?不含字段列表的 INSERT 语句
6. 避免使??查询，可以把?查询优化为 join 操作
7. 避免使? JOIN 关联太多的表
8. 对应同?列进? or 判断时，使? in 代替 or
9. WHERE 从句中禁?对列进?函数转换和计算
10. 在明显不会有重复值时使? UNION ALL ?不是 UNION
11. 拆分复杂的? SQL 为多个? SQL