一、索引概述
1、定义:
索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
2、优缺点:
优势:
提高数据检索的效率,降低数据库的IO成本
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
劣势:
索引列也是要占用空间的。
索引大大提高了查询效率,同时却也降低了更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。
二、索引的结构
1、定义及分类:
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包概括一下几种:
①B+Tree索引
最常见的索引类型,大部分引擎都支持B+树索引。
②Hash索引
底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询。
③R-tree(空间索引)
空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少。
④Full-text(全文索引)
是通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES。
注意:平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引。
2、B+Tree索引和Hash索引
B+Tree索引
MySQL索引数据结构对经典的B+Tree进行了优化。在原来B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高了区间访问的性能。
数据块(页):一个页的大小默认是16k。
Hash索引
采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
特点:
①Hash索引只能用于对等比较(=,in),不支持范围查询(between,> , < , .....)
②无法利用索引完成排序操作
③查询效率高,通常只需要一次检索就可以,效率通常要高于B+tree索引。
在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
三、索引的分类
1、主键索引
定义:针对于表中主键创建的索引
索引数量特点:默认自动创建,只能有一个。
关键字:PRIMARY
2、唯一索引
定义:避免同一个表中某数据列中的值重复。
索引数量特点:可以有多个。
关键字:UNIQUE
3、常规索引
定义:快速定位特定数据。
索引数量特点:可以有多个。
关键字:无
4、全文索引
定义:群问索引查找的是文本中的关键词,而不是比较索引中的值
索引数量特点:可以有多个
关键字:FULLTEXT
5、在InnoDB存储引擎中,根据索引的存储结构,又可以分为一下两种:
聚集索引:将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据
特点:必须有,而且只有一个。
聚集索引选取规则:
①如果存在主键,主键索引就是聚集索引。
②如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
③如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
二级索引:将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键
特点:可以存在多个。
四、索引语法
1、创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,.....);
注意:( )内是可以关联多个字段的,即一个索引关联多个字段。
2、查看索引
SHOW INDEX FROM table_name;
3、删除索引
DROP INDEX index_name ON table_name;
五、索引的优劣势
1、优势
①提高数据检索效率,降低磁盘IO成本。
②通过对数据的排序,降低排序成本。
2、劣势
①索引虽提高了查询效率,但同时降低了更新、修改、删除的效率,因为MySQL不仅要保存数据,还要维护数据和索引的关系。
②需要成本去维护索引。一个性能良好的索引需要不断的去尝试,以找到最优解。
六、索引的使用
1、索引的使用原则:
①最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
②范围查询
联合索引中,出现返回查询(< , >),范围查询右侧的列索引失效。如果解决这个问题,尽量使用<=,>= 。
③索引列上进行运算
不要在索引列上进行运算操作,索引将失效。可以使用函数运算就不会失效。
④字符串不加引号
字符串类型字段使用时,不加引号,索引将失效。
⑤模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
⑥or连接的条件
用or分割的条件,如果or前的条件中的列由索引,而后面的列中没有索引,那么涉及的索引都不会被用到。如果需要让这个语句索引有用,那么就or两边都创建索引即可。
⑦数据分布影响
如果MySQL评估使用索引比全表扫描更慢,则不使用索引。当查询条件的范围包括大部分数据,则会使用全表扫描。反之则会
⑧SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化的操作目的。
use index:建议数据库用那个索引。
格式:explain select * from 表名 use index(索引名) where 筛选条件;
ignore index:告知数据库不能用那个索引。
格式:explain select * from 表名 ignore index(索引名) where 筛选条件;
force index:告知数据库必须用这个索引。
格式:explain select * from 表名 force index(索引名) where 筛选条件;
⑨覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *。
减少使用select*。是因为创建select*后很容易出现回表查询,会降低性能。 除非创建一个全部字段的联合索引。
⑩前缀索引
当字段类型为字符串时,有时会需要很长的字符串,这就会让索引变得很大,查询时,很浪费磁盘IO,影响效率。这时就需要创建前缀索引,从而提高效率。
语法:create index 索引名 on 表名(字段(n));
(n): 选取字符串前n个字符。
前缀长度: 可以根据索引的选择来决定,而选择性是指不重复的索引(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
求选择性:select count( distinct 字段名) / count(*) from 表名;
select count( distinct substring(字段名,1,5)) / count(*) from 表shiyi
单列索引与联合索引
单列索引:一个索引只包含单个列。
联合索引:一个索引包含了多个列。
在特务场景中,如果存在多个查询条件,考虑针对查询字段建立索引时,建议建立联合索引,而非单列索引。
主要是为了减少回表查询。