索引
简介
索引是一个单独的、存储 在磁盘上的数据库结构,他们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列有一特定值的行,所有MySQL列类型都可以被索引,对相关列使用索引是提高查询操作速度的最佳途径。
索引的优点
- 可以大大加快数据的查询速度。
- 在实现数据的参考完整性方面,可以加速表和表之间的连接。
- 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。
- 主键索引的叶子结点存储了整一行的内容(聚簇索引),使用主键可以快速获取到整行的数据。
索引的缺点
- 创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
- 索引需要占磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
索引的设计原则
- 索引并非越多越好。过多的索引会占用大量内存空间,而且会影响增删改的性能。
- 避免对经常更新的表建立过多的索引,并且索引中的列要尽可能少。
- 数据量的表最好不要建立索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要短。
- 在条件表达式中经常用到的不同值较多的列上建立索引。如学生表中的‘性别’列,只有“男”,“女”两个值,就无需建立索引。
- 在频繁进行排序和分组的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。
- 对于字符串类型的字段进行索引,如果可能应该指定一个前缀长度。如果有一个char(255)的列 在前10个或前30个字符内,多数值是唯一的,则不需要对整个列建立索引。短索引不仅可以提高查询速度而且可以节省磁盘空间,减少I/O操作。
索引优化
慢查询日志:会记录所有执行时间超过long_query_time的所有查询或不使用索引的查询,所以可以根据慢查询日志进行对应sql语句优化。
使用覆盖索引:覆盖索引即查询的所有列都有索引,使用覆盖索引可以减少大量回表操作,也会可以进行范围查找减少磁盘IO。
(回表操作:先索引扫描,再通过主键去取索引中未能提供的数据,即为回表)
回表操作解释
索引顺序:将选择性高的索引放在前面,可以过滤大多数索引。如果已有(a,b)索引 就不需要再维护一个a索引。
定期清除过时索引:维护索引需要耗费系统大量资源,尤其是当表中数据比较多得时候。
尽量拓展索引而不是新建索引。
不会使用索引的情况
- 使用LIKE 关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。只有“%”不在第一个位置,索引才会起作用。
- 使用多列索引时,只有在查询条件中使用了这些字段的第一个字段时,索引才会被使用。最左匹配原则。
ex:CREATE INDEX index_id_price ON fruits(f_id,f_price);
SELECT * FROM fruits WHERE f_id=‘12’;
此时会使用索引
SELECT * FROM fruits WHERE f_price=5.2;
此时不会使用索引 - 索引列不能是表达式的一部分,也不能是函数的参数。
ex: select * from table where id-1>4;
此时id是表达式的一部分 就不会使用索引。