mysql学习笔记(2)-创建MySQL索引

1. 单列索引

创建语法：

ALTER TABLE <table> ADD PARIMARY KEY [index-name] (<column>);

ALTER TABLE <table> ADD [UNIQUE]  KEY|INDEX [index-name] (<column>);

删除语法：

DROP INDEX [index-name] ON <table>;

2.  索引的作用：

1）限制查询读取的行数

上述查询中type=ALL、key=NULL，可以判断进行了全表扫描

添加索引后再次进行查询

发现rows的数量有明显改善。

2）提高关系表连接的操作性能

显示id=2的表会执行全表查询，可以通过添加索引来解决这个问题。

3. 关于索引选择性

索引选择性是指索引列中不同值的数量与总行数的比。例如100条记录，索引列的不同值是98，则索引选择性为0.98。理想情况下索引选择值为1，且每一个值都是一个非空唯一值。索引的选择性越接近于1，这个索引的效率就越高。

4.  使用索引进行模式匹配

1）如果查找条件以通配符开头，则MySQL不会使用索引

例如 like ’word%‘可以使用索引，而like ‘%word’ 和 like ’%word%‘则无法使用索引

技巧：如果经常需要以通配符开头查询，常用的是在数据库中保存需要查询的值的反序值。例如搜索email like ’%.com‘无法使用索引，而搜索reverse_email like reverse('%.com')则可以使用

2）MySQL不支持基于索引的函数。如果想创建带有列函数的索引会导致语法错误。查询时索引列如果被函数使用则MySQL不会使用该列上的索引。

5.  唯一索引

如果每个列上不会出现重复的值，可以创建唯一索引。

唯一索引有两个目的：

1. 提供数据完整性以保证在列中任何值都只出现一次

2. 告知优化器对给定的记录最多只可能有一行返回值，避免额外的索引扫描。当使用唯一索引时MySQL知道最多只可能返回一行数据，找到一个匹配结果之后就不需要继续扫描了。

6.  结果排序

如果没有索引，MySQL会使用内部文件排序算法对结果行进行排序。也就是在执行计划的Extra列中会出现Using filesort

通过基于索引的数据排序算法，就可以免去分类的过程。

7. 多列索引

索引可以创建在两列或多列上。多列索引也被称为混合索引或者连接索引。

多列索引的创建语法和之前的相同，唯一不同的是要指定该索引时跨越多列的：

ALTER TABLE <table> ADD PARIMARY KEY [index-name] (<column1>，<column2> ...);

ALTER TABLE <table> ADD [UNIQUE]  KEY|INDEX [index-name] (<column1>，<column2> ...);

如下示例：

从结果中看到ref是三个常量。

虽然索引可以包含多列，但实际上对索引的效率会有所限制。索引的行的宽度应该尽可能的短，这样就可以在一个索引数据页面中包含更多的索引记录，好处是可以读取尽量少的数据，从而尽可能快地遍历索引。explain命令中的key_len和ref两个属性值可以用来判断选中的索引的利用率。

对上面的例子稍作修改，从where条件中删除最后两个。

可以看到多余的列没有被用到查询中。key_len减少到16，ref中仅剩一个const。如果没有其他查询用到后面两列，那这就是一个可优化的点以减少索引行的宽度。

8.  合并WHERE和ORDER BY语句

MySQL可以通过多列索引同时优化数据行的限制条件以及优化排序结果

如下示例： 当使用多列索引后，可以消除执行计划extra列中的Using filesort

添加索引：

8.  查询提示

1）总查询提示

所有总查询提示会在SELECT关键字之后立刻产生。包括SQL_CACHE、SQL_NO_CACHE、SQL_SMALL_RESULT、SQL_BIG_RESULT、SQL_BUFFER_RESULT、SQL_cALC_FOUND_ROWS、HIGH_PRIORITY。

而只有STRAIGHT_JOIN查询提示会对查询中索引的使用有影响。这个提示会包塑优化器按照查询中指定表的顺序执行查询执行计划。

2)索引提示

查询时可以通过USE INDEX和FORCE INDEX使用指定索引。

区别是USE INDEX只是推荐优化器使用此索引，如果优化器觉得使用表扫描速度更快则会选择表扫描。而FORCE INDEX则是强制使用该索引

也可以使用IGNORE INDEX不使用指定索引。

语法：

USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)