关于索引,需要明确点
1:索引是对数据库表中一列或多列的值进行排序的一种结构(通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。)。
2:创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
3:索引需要占物理空间。
4:表中的数据进行增、删、改的时候,索引也要动态的维护,这就降低了表的维护速度(执行增加、删除、更新操作操作的同时会对索引文件进行重新排序或更新)。
如何确认需要添加索引
1:表中记录达到一定的条数,如300以上。
2:查询性能的要求,高于增删改。
如何确认哪些字段添加索引
1:SELECT:经常需要查询的列。
2:主键(mysql的Innode默认必须主键,所以使用Innodb时,主键索引一定存在的。)
3::INNER JOIN、LEFT JOIN、RIGHT JOIN:经常作为表结合的条件的列。
4:WHERE:经常使用在WHERE子句中的列(根据范围(<,<=,=,>,>=,BETWEEN,IN)进行搜索的列,因为索引已经排序,其指定的范围是连续的,加快排序查询时间)。
5:ORDER BY:经常排序的列上创建索引。(原因:索引已经排序,这样查询可以利用索引的排序,加快排序查询时间)。
6:索引应该建在维度高或选择性高的列创建索引,就是数据列中不重复值出现的个数,个数越大,维度就越高。要为维度高的列创建索引。如性别和年龄,那年龄的维度就高于性别,性别这样的列不适合创建索引,因为维度过低。
7:索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引(原因:较小的数据,索引文件更小,内存中也可以装载更多的索引键)。
8:较长的字符串使用前缀索引(确认前缀索引的长度:select count(distinct left(col_char,5)) / count(*) from t_table,得到前几个字母对标数据的覆盖率,覆盖率超过31%黄金值就可以使用前缀索引。红色的数字自行替换,找到一个最优的长度)。前缀字符并非越多越好,需要在索引的选择性和索引IO读取量中做出衡量。
9:使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引。
如何确认不应该添加索引
1:表非常小的情况下,不要添加索引,全文扫描更加。要记得索引要是一种资源的消耗。
2:数据列包含许多重复的内容,建立索引没有太大的效果,不要建立。
3:查询中很少使用或者参考的列,不要创建索引。(若列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。)
4:对于那些定义为text, image和bit数据类型的列不应该增加索引。
5:当修改性能要求远远高于检索性能时,不应该创建索引。(修改性能和检索性能是互相矛盾的)
索引失效的场景
1:索引列有函数或表达式,将导致索引失效而进行全表扫描:SELECT id FROM t_tabel WHERE score-70>60或SELECT name FROM t_table WHERE LEFT(`name`,4) ='davi'。
3:索引列以通配符%开头(如:like ‘%abc’),则索引失效,直接全表扫描;若只是以%结尾,则不影响。
4:索引列的类型和条件值的类型不符合时:SELECT * FROM t_table WHERE type=1 (type列为varchar字符类型,用整数查找不走索引,需要使用type='1',索引才会生效)。
5:条件有OR时,部分条件列为索引列,索引也不会生效。除非所有的条件列都是索引列,索引才会生效。
7:表中数据不多,只有几十几百条,MySQL评估使用全表扫描要比使用索引快,也不使用索引。
8:>、<范围查询:mysql 会一直向右匹配直到遇到索引搜索键使用>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询,那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列,因此如果查询条件中有两个>、<范围列则无法全用到索引。
9:联合索引中,where中索引列违背最左匹配原则,一定会导致索引失效。
10:order by 对主键索引排序会用到索引,其他的索引失效
索引的优点
1:索引大大减小了服务器需要扫描的数据量,从而大大加快数据的检索速度,这也是创建索引的最主要的原因。
2:索引可以帮助服务器避免排序和临时表。
3:索引可以将随机IO变成顺序IO。
4:索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组,提高了表访问并发性。
5:InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)。
6:通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
7:可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
8:在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
9:通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
索引的缺点
1::创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
2:索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间,如果需要建立聚簇索引,那么需要占用的空间会更大。
3:对表中的数据进行增、删、改的时候,索引也要动态的维护,这就降低了整数的维护速度。
复合索引的使用
1:最左前缀匹配原则:
即最左优先(查询条件精确匹配索引的左边连续一列或几列,则构建对应列的组合索引树),在检索数据时也从联合索引的最左边开始匹配。在复合索引中,索引第一位的column很重要,只要查询语句包含了复合索引的第一个条件,基本上就会使用到该复合索引(可能会使用其他索引)。在建复合索引的时候应该按照column的重要性从左往右建。
假设A、B、C三个字段索引按A+B+C顺序创建的索引:
A --走索引
B --不走索引
C --不走索引
A + B 或 B + A -- 走索引
B + C 或 C + B -- 不走索引
A + B + C 或 B + C + A 或 C + B + A --走索引
覆盖索引与回表
1:覆盖索引:查询的字段包含在条件的索引键中(或是组合索引键中的其它字段)。覆盖索引的查询效率极高,原因在与其不用做回表查询。(查询的列数据作为索引树的键值,直接在索引树中得到反馈(存在于索引节点),不用遍历如InnoDB中的叶子节点(存放数据表各行数据)就可得到查询的数据(不用回表))。
2:回表:先定位主键值,再定位行记录(先通过普通索引定位到主键值,在通过聚集索引定位到行记录)
索引基数
在'show index from t_table'结果集中有一列为Cardinality的值,它的作用也非常的大,称之为:索引基数
索引基数:索引列的唯一值的个数,如果是复合索引就是唯一组合的个数。这个数值将会作为MySQL优化器对语句执行计划进行判定时依据。如果唯一性太小,那么优化器会认为这个索引对语句没有太大帮助,而不使用索引。cardinality值越大,就意味着,使用索引能排除越多的数据,执行也更为高效。
当有多个索引可用时,mysql会自动依据cardinality大的值来进行SQL索引选择优化。
假设数据库中有数据值100行,但是cardinality=89并不准确,是因为它不会自动更新,需要通过analyzetable来进行更新,示例如下:
mysql>analyze local table ad_article;
索引分类
按功能划分
1:主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL。
2:唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,但是一个唯一索引只能包含一列,比如身份证号码、卡号等都可以作为唯一索引。
3:普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入。
4:全文索引:它查找的是文本中的关键词,主要用于全文检索。
按列数划分
1:单例索引:一个索引只包含一个列,一个表可以有多个单例索引。:
2:组合索引:一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则,即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。:
物理分类
1:聚簇索引:聚簇是为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。聚簇索引(clustered index)不是单独的一种索引类型,而是一种数据存储方式。这种存储方式是依靠B+树来实现的,根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时,方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块,找到索引也就找到了数据。
2:非聚簇索引:数据和索引是分开的,B+树叶子节点存放的不是数据表的行记录。
虽然InnoDB和MyISAM都默认使用B+树结构存储索引,但是只有InnoDB的主键索引才是聚簇索引,InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表只能拥有一个聚簇索引。
聚簇索引优缺点(InnoDB)
优点:1:数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快:
2:聚簇索引对于主键的排序查找和范围查找速度非常快:
缺点:
1:插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键(主键列不要选没有意义的自增列,选经常查询的条件列才好,不然无法体现其主键索引性能):
2:更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。:
3:二级索引访问:需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
全文索引 FULLTEXT
全文索引:关键字的匹配来进行查询过滤,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题。
FULLTEXT VS LIKE+%
使用LIKE+%确实可以实现模糊匹配,适用于文本比较少的时候。对于大量的文本检索,LIKE+%与全文索引的检索速度相比就不是一个数量级的。
FULLTEXT的支持情况
1:MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
2:MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
3:只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引:
4:InnoDB内部并不支持中文、日文等,因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。
创建全文索引
1:建表的时候:FULLTEXT KEY keyname(colume1,colume2) // 创建联合全文索引列
2:在已存在的表上创建::create fulltext index keyname on xxtable(colume1,colume2);或者alter table xxtable add fulltext index keyname (colume1,colume2);
使用全文索引
全文索引有独特的语法格式,需要配合match 和 against 关键字使用
1:match()函数中指定的列必须是设置为全文索引的列:
2:against()函数标识需要模糊查找的关键字
3:建表文:
create table fulltext_test(
id int auto_increment primary key,
words varchar(2000) not null,a
artical text not null,
fulltext index words_artical(words,artical)
)engine=innodb default charset=utf8;insert into fulltext_test values(null,'a','a');
insert into fulltext_test values(null,'aa','aa');
insert into fulltext_test values(null,'aaa','aaa');
insert into fulltext_test values(null,'aaaa','aaaa');4:执行查询:
select * from fulltext_test where match(words,artical) against('a');
select * from fulltext_test where match(words,artical) against('aa');
select * from fulltext_test where match(words,artical) against('aaa');
select * from fulltext_test where match(words,artical) against('aaaa');5:结果
发现只有aaa和aaaa才能查到记录,为什么会这样呢?
原因就是全文索引关键词长度阈值。
可以通过show variables like '%ft%';查看。可见InnoDB的全文索引的关键词最小索引长度 为3。上文使用的是InnoDB引擎建表,同时也解释为什么只有3a以上才有搜索结果。
设置关键词长度阈值,可以有效的避免过短的关键词,得到的结果过多也没有意义。6:修改长度阈值
修改MySQL配置文件,在[mysqld]的下面追加以下内容,设置关键词最小长度为5。
[mysqld]
innodb_ft_min_token_size = 5
ft_min_word_len = 5
然后重启MySQL服务器(systemctl restart mysqld),还要修复索引(repair table 表名 quick;),不然参数不会生效
7:全文索引模式为什么上文搜索关键字为aaa的时候,有且仅有一条aaa的记录,为什么没有aaaa的记录呢?
原因就是自然语言的全文索引 IN NATURAL LANGUAGE MODE
默认情况下,或者使用 IN NATURAL LANGUAGE MODE 修饰符时,match() 函数对文本集合执行自然语言搜索,上面的例子都是自然语言的全文索引。自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里,相关度是基于匹配的关键词的个数,以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语,匹配时的相关度就越高。
MySQL在全文查询中会对每个合适的词都会先计算它们的权重,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重,MySQL默认的阀值是50%。上面‘a’在每个文档都出现,因此是100%,只有低于50%的才会出现在结果集中;‘aaa’的权重值为1/2=50%,没有低于50%,因此’aaaa’不会出现在’aaa’结果集中。
这个机制也比较好理解,比如一个数据表存储的是一篇篇的文章,文章中的常见词、语气词等等,出现的肯定比较多,搜索这些词语就没什么意义了,需要搜索的是那些文章中有特殊意义的词,这样才能把文章区分开。
select * from fulltext_test where match(words,artical) against('aaa');
等价
select * from fulltext_test where match(words,artical) against('aaa' IN NATURAL LANGUAGE MODE);8:布尔全文索引 IN BOOLEAN MODE
在布尔搜索中,我们可以在查询中自定义某个被搜索的词语的相关性,这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,来指定搜索词在结果中的包含情况。
建立如下表:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB
(1):+ (AND)全文索引列必须包含该词且全文索引列(之一)有且仅有该词(2):- (NOT)表示必须不包含,默认为误操作符。如果只有一个关键词且使用了- ,会将这个当成错误操作,相当于没有查询关键词;如果有多个关键词,关键词集合中不全是负能符(~ -),那么-则强调不能出现。
举例:查找title,body列中有且仅有apple(是apple不是applexx 也不是 xxapple)但是不含有banana的记录
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple -banana' IN BOOLEAN MODE);(3)*:> 提高该词的相关性,查询的结果靠前:
(4):< 降低该词的相关性,查询的结果靠后
举例:返回同时包含apple(是apple不是applexx 也不是 xxapple)和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);(5):~ 异或,如果包含则降低关键词整体的相关性
举例:返回的记录必须包含apple(且不能是applexx 或 xxapple),但是如果同时也包含banana会降低权重(只出现apple的记录会出现在结果集前面)。但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple ~banana' IN BOOLEAN MODE);(6):* 通配符,表示关键词后面可以跟任意字符
举例:返回的记录可以为applexx
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('apple*' IN BOOLEAN MODE);(7):空格 表示OR
举例:查找title,body列中包含apple(是apple不是applexx 也不是 xxapple)或者banana的记录,至少包含一个
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('apple banana' IN BOOLEAN MODE);(8):"" 双引号,效果类似like '%some words%'
举例:模糊匹配 “apple banana goog”会被匹配到,而“apple good banana”就不会被匹配
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"apple banana"' IN BOOLEAN MODE);9:InnoDB中FULLTEXT中文支持
InnoDB内部并不支持中文、日文等,因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。MySQL内置ngram插件便可解决该问题。
FULLTEXT (title, body) WITH PARSER ngram
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
InnoDB索引实现
InnoDB使用B+TREE存储数据,除了主键索引为聚簇索引,其它索引均为非聚簇索引。一个表中只能存在一个聚簇索引(主键索引),但可以存在多个非聚簇索引。
1:主键索引:B+树,叶子节点包含数据表中行记录就是聚簇索引(索引和数据是一块的)
主键索引结构分析:B+树单个叶子节点内的行数据按主键顺序排列,物理空间是连续的(聚簇索引的数据的物理存放顺序与索引顺序是一致的);叶子节点之间是通过指针连接,相邻叶子节点的数据在逻辑上也是连续的(根据主键值排序),实际存储时的数据页(叶子节点)可能相距甚远。
2:辅助索引:在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
3:InnoDB索引优化::InnoDB中主键不宜定义太大,因为辅助索引也会包含主键列,如果主键定义的比较大,其他索引也将很大。如果想在表上定义 、很多索引,则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。InnoDB中尽量不适用非单调字段作主键,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
Innodb主键索引图
Innodb辅助索引图
MyISAM索引实现
MyISAM也使用B+Tree作为索引结构,但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引。
1:MyISAM表的索引和数据是分开存储的,.MYD表数据文件 .MYI表索引文件
2:MyISAM辅助索引:在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。 MyISAM辅助索引也是非聚簇索引。
MyISAM主键索引图
InnoDB和MyISAM的索引检索过程
1:对于InnoDB和MyISAM而言,主键索引是根据主关键字来构建的B+树存储结构,辅助索引则是根据辅助键来构造的B+树存储结构,彼此的索引树都是相互独立的。
2:InnoDB辅助索引的访问需要两次索引查找,第一次从辅助索引树找到主键值,第二次根据主键值到主键索引树中找到对应的行数据。:
3:MyISM使用的是非聚簇索引,表数据存储在独立的地方,这两棵(主键和辅助键)B+树的叶子节点都使用一个地址指向真正的表数据。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。:
4:聚簇索引的叶子节点存放的是数据行(主键值也是行内数据),支持覆盖索引。
5:二级索引的叶子节点存放的是主键值或指向数据行的指针。
6:由于叶子节点(数据页)只能按照一棵B+树排序,故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织,所以一张表可以有多个辅助索引。
B+TREE
是N叉树,层级小,非叶子节点不再存储数据,数据只存储在同一层的叶子节点上,B+树从根到每一个节点的路径长度一样。B树的查询/插入/修改/删除的平均时间复杂度都为O(log(n));
1:叶子之间,增加了链表,获取所有节点,不再需要中序遍历,使用链表的next节点就可以快速访问到
2:范围查找方面,当定位min与max之后,中间叶子节点,就是结果集,不用中序回溯(范围查询在SQL中用得很多,这是B+树比B树最大的优势)
3:叶子节点存储实际记录行,记录行相对比较紧密的存储(稠密索引),且是有序的,适合大数据量磁盘存储;非叶子节点存储记录的PK(叶子结点的索引(稀疏索引)),用于查询加速,适合内存存储
4:非叶子节点,不存储实际记录,而只存储记录的KEY的话,那么在相同内存的情况下,B+树能够存储更多索引
可以来初步计算一下:假设key、子树节点指针均占用4B,则B树一个节点占用4 + 4 = 8B,一页页面大小4KB,则N = 4 * 1024 / 8B = 512,一个512叉的B树,1000w的数据,深度最大 log(512/2)(10^7) 约等于4。对比二叉树如AVL的深度为log(2)(10^7) 约为24,相差了5倍以上!
假如一个节点大小是4KB,一个KEY有8字节,一页可以存4000/8=500个KEY,根据N叉树特点,就算一层500叉节点,则:
第一层树:1个节点,1*500KEY , 大小4K
第二层树:500节点 500*500=25万个KEY,500*4K=2M
第三层树:500 * 500节点 500*500*500=1.2亿KEY,500*500*4K=1G
如果没算错,1G空间,只用三层树结构,可以存1.2亿行数据的KEY,B+树牛掰不?
所以B+TREE索引只用占用很少的内存空间,却大大提升了查询效率(不论是单个查询、范围查询还是有序性查询),并且还减少了磁盘读写,所以好的算法与数据结构是可以省钱的。
B-TREE
B-树就是B树,多路搜索树,树高一层意味着多一次的磁盘I/O,查询/插入/修改/删除的平均时间复杂度都为O(log(n));
B树的特征:
1:关键字集合分布在整颗树中;
2:任何一个关键字出现且只出现在一个结点中;
3:搜索有可能在非叶子结点结束;
4:其搜索性能等价于在关键字全集内做一次二分查找;
5:自动层次控制;
HASH
哈希索引:基于哈希表实现,采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。查询/插入/修改/删除的平均时间复杂度都为O(1)。只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。:
由于Hash索引比较的是进行Hash运算之后的Hash值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样。
hash索引的限制:
1:哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。:
2:哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。:
3:哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。:
4:哈希索引只支持等值比较查询,包括=、IN()、<>(注意<>和<=>是不同的操作)。也不支持任何范围查询,例如WHERE price>100。
5:访问哈希索引的数据非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行。:
如果哈希冲突很多的话,一些索引维护操作的代价也会很高。例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行时,存储引擎需要遍历对应哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。