MySQL-索引

MySQL-索引内容整理

根据看《MySQL技术内幕》时的笔记，扩展了一些总结了一下。

加快对数据表内容的访问速度的基本手段，尤其是涉及多个数据表关联查询中。

---

优点

• 没有索引的数据表相当于无序的数据行的集合，要查找数据需要去表遍历。

• 索引相当于将数据表中的固定列的数据按顺序排列

索引快的原因：

1. 知道在何处停止(因为索引根据值来分类，比如说查找13，当查到14时就知道该停止了)
2. 算法优势(因为按一定顺序固定排列，所以可以用二进制搜索)
3. 在多表查询中尤其明显
4. MIN()和MAX()函数的出现，如果有索引不用逐行检查
5. MySQL经常使用使用完成ORODER BY 和GROUP BY的分类和分组
6. 在仅查询有索引的列的值时，可以不读取数据表文件只读取索引文件

为什么不直接全行分类

• 全行分类需要将数据按照每一个列排一次序
• 因为索引中的数据行普遍比数据表中的短，所以插入删除操作比较快

---

缺点

如果MySQl知道如何通过索引来快速处理查询，他就会使用索引

1. 索引会降低插入删除和更新速度
   因为不单要修改数据文件还要修改索引文件
   对一些写操作多读操作少的表会增加负担
2. 索引占据了更多的磁盘空间
3. 使用InnoDB引擎的数据表和索引保存在同一个文件里，添加索引会使文件更快的逼近最大文件长度
4. MySQL在为查询生成一个执行方案是都会仔细对索引进行推敲，建立多余的索引对查询优化程序加上了更多的工作。
5. 索引太多可能使MySQL无法选出最优的索引

结论

如果不需要某个特定的索引来加快查询速度，就不要创建它。

---

挑选索引

1. 尽量为用来搜索、分类或分组的数据列编制索引，不要为作为输出显示的数据列标准索引。
   在WHERE后出现的值。
2. 综合考虑数据列的维度势。
   数据列的”维度”就是容纳非重复值的个数，维度越高索引的效果越好。
   当查询优化程序确定某一数据在数据行中出现的程序超过30%时会跳过索引进行全表扫描。(现在这不是唯一依据)(现在我们都是士兵了～～)
3. 对短小的值进行索引。
   这里的小指的是数据的类型MEDIUMINT和BIGINT之类的字节数要精简。
   短小的值可以让比较操作更快完成。
   短小的值可以让索引”体积”减小减少磁盘I/O活动。
   短小键值意味着键缓存里的索引块可以容纳更多的键值。
   4.为字符串的前缀编辑索引。
   比如一个CHAR(200)的字符串，前10～20为位大多是唯一的，所以如果需要给这一列加索引，可以只添加前缀。
4. 充分利用最左边的前缀。
   见关于复合索引。
5. 适可而止，不要建立过多索引
6. 让索引的类型与你打算进行比操作的类型保持匹配
   每种索引都会选择自己对应的索引类型(B树索引，R树索引，散列索引)
   散列索引：用一个散列函数来依次处理每个数据列值。
   在”=、<、>从、”操作方面精确快速，但是查找一个范围的比较表现不佳。
   “B树”索引:都好，但是精确速度比不上散列
7. 利用”慢查询”日志章程性能低劣的查询
   这个日志可以帮助我们找出可能受益于索引的查询命令。

---

一些小tips

1. 关于LIKE操作，”%aaa%”是不会使用索引的，但是”aaa%”可以使用索引。
2. 不要在列上进行运算
   WHERE YEAR(adddate)<2017，将在每个行上进行运算，将导致索引失效可以改成：WHERE adddate<'2017-01-01'。
3. MySQL一条查询只使用一个索引,排序和查询一共只能用一个。
   所以如果需要排序多个列尽量使用复合索引
   原因：如果执行N条索引，查询优化器需要进行N次主二叉树查找，效率低于一个索引加全表(也不一定，但是贪婪算法不同意)
   N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。
4. MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。

---

相关操作

创建索引

#创建简单索引
CREATE INDEX index_name ON table_name (colum_name(length));
ALTER TABLE table_name ADD INDEX index_name (column_name(length));

#创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name(length));
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name(length));

#创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY index_name (column_name);

#创建复合索引
 CREATE INDEX index_name ON table_name (column_name1(length), column_name2(length), column_name3(length));

删除索引

#删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

查询索引

#查询索引
SHOW INDEX FROM table_name;

---

关于复合索引

 CREATE INDEX test_index ON test_table (state, city, zip);

建立一个n个数据列的索引，相当于创建了n个索引，
其中最左边的数据列能够匹配数据行，被称为”最左边的前缀”

假设一个数据表有一个复合索引：state(州)、city(城市)、zip(邮政编码)。
索引中的数据行是根据州/城市/邮政编码的顺序存储的，
所以这样一个索引可以用来搜索:

州+城市+邮政编码
州+城市
州

但是不能用来搜索

城市+邮政编码
邮政编码

同时对于”州+邮政编码”索引可以用来搜索但是不能进行值的组合(分类)

1. 不按索引最左列开始查询(多列索引)不能使用索引

例如

SELECT zip FROM test_table WHERE city='aaa' AND zip='123';

1. 某一列有范围查询则右边列无法使用索引

例如

SELECT zip FROM test_table WHERE state='aaa' AND city LIKE 'bb%' AND zip='123';

只有state、city可以使用索引，zip无法使用。

---

关于聚集索引

这个和使用的引擎有关，MyISAM引擎中索引和数据分离，InnoDB中数据文件本身就按照主键排序。

在InnoDB引擎中主键就是聚集索引，其他的为非聚集索引。

MyISAM引擎中没有聚集索引的概念。