MySQL-索引内容整理
根据看《MySQL技术内幕》时的笔记,扩展了一些总结了一下。
加快对数据表内容的访问速度的基本手段,尤其是涉及多个数据表关联查询中。
优点
没有索引的数据表相当于无序的数据行的集合,要查找数据需要去表遍历。
索引相当于将数据表中的固定列的数据按顺序排列
索引快的原因:
- 知道在何处停止(因为索引根据值来分类,比如说查找13,当查到14时就知道该停止了)
- 算法优势(因为按一定顺序固定排列,所以可以用二进制搜索)
- 在多表查询中尤其明显
- MIN()和MAX()函数的出现,如果有索引不用逐行检查
- MySQL经常使用使用完成ORODER BY 和GROUP BY的分类和分组
- 在仅查询有索引的列的值时,可以不读取数据表文件只读取索引文件
为什么不直接全行分类
- 全行分类需要将数据按照每一个列排一次序
- 因为索引中的数据行普遍比数据表中的短,所以插入删除操作比较快
缺点
如果MySQl知道如何通过索引来快速处理查询,他就会使用索引
- 索引会降低插入删除和更新速度
因为不单要修改数据文件还要修改索引文件
对一些写操作多读操作少的表会增加负担 - 索引占据了更多的磁盘空间
- 使用InnoDB引擎的数据表和索引保存在同一个文件里,添加索引会使文件更快的逼近最大文件长度
- MySQL在为查询生成一个执行方案是都会仔细对索引进行推敲,建立多余的索引对查询优化程序加上了更多的工作。
- 索引太多可能使MySQL无法选出最优的索引
结论
如果不需要某个特定的索引来加快查询速度,就不要创建它。
挑选索引
- 尽量为用来搜索、分类或分组的数据列编制索引,不要为作为输出显示的数据列标准索引。
在WHERE后出现的值。 - 综合考虑数据列的维度势。
数据列的”维度”就是容纳非重复值的个数,维度越高索引的效果越好。
当查询优化程序确定某一数据在数据行中出现的程序超过30%时会跳过索引进行全表扫描。(现在这不是唯一依据)(现在我们都是士兵了~~) - 对短小的值进行索引。
这里的小指的是数据的类型MEDIUMINT和BIGINT之类的字节数要精简。
短小的值可以让比较操作更快完成。
短小的值可以让索引”体积”减小减少磁盘I/O活动。
短小键值意味着键缓存里的索引块可以容纳更多的键值。
4.为字符串的前缀编辑索引。
比如一个CHAR(200)的字符串,前10~20为位大多是唯一的,所以如果需要给这一列加索引,可以只添加前缀。 - 充分利用最左边的前缀。
见关于复合索引。 - 适可而止,不要建立过多索引
- 让索引的类型与你打算进行比操作的类型保持匹配
每种索引都会选择自己对应的索引类型(B树索引,R树索引,散列索引)
散列索引:用一个散列函数来依次处理每个数据列值。
在”=、<、>从、”操作方面精确快速,但是查找一个范围的比较表现不佳。
“B树”索引:都好,但是精确速度比不上散列 - 利用”慢查询”日志章程性能低劣的查询
这个日志可以帮助我们找出可能受益于索引的查询命令。
一些小tips
- 关于LIKE操作,”%aaa%”是不会使用索引的,但是”aaa%”可以使用索引。
- 不要在列上进行运算
WHERE YEAR(adddate)<2017
,将在每个行上进行运算,将导致索引失效可以改成:WHERE adddate<'2017-01-01'
。 - MySQL一条查询只使用一个索引,排序和查询一共只能用一个。
所以如果需要排序多个列尽量使用复合索引
原因:如果执行N条索引,查询优化器需要进行N次主二叉树查找,效率低于一个索引加全表(也不一定,但是贪婪算法不同意)
N条独立索引同时在一条语句使用的消耗比只使用一个索引还要慢。 - 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(邮政编码)。
索引中的数据行是根据州/城市/邮政编码的顺序存储的,
所以这样一个索引可以用来搜索:
州+城市+邮政编码
州+城市
州
但是不能用来搜索
城市+邮政编码
邮政编码
同时对于”州+邮政编码”索引可以用来搜索但是不能进行值的组合(分类)
- 不按索引最左列开始查询(多列索引)不能使用索引
例如
SELECT zip FROM test_table WHERE city='aaa' AND zip='123';
- 某一列有范围查询则右边列无法使用索引
例如
SELECT zip FROM test_table WHERE state='aaa' AND city LIKE 'bb%' AND zip='123';
只有state、city可以使用索引,zip无法使用。
关于聚集索引
这个和使用的引擎有关,MyISAM引擎中索引和数据分离,InnoDB中数据文件本身就按照主键排序。
在InnoDB引擎中主键就是聚集索引,其他的为非聚集索引。
MyISAM引擎中没有聚集索引的概念。