<small id='PCTgxJs8fB'></small> <noframes id='bgWEuUtV0f'>

  • <tfoot id='PLrOu'></tfoot>

      <legend id='2vRgsm'><style id='Vtz0'><dir id='JpFMQ'><q id='2Wgdn9'></q></dir></style></legend>
      <i id='xyAQw'><tr id='scVLqh49P'><dt id='XkwOK3L'><q id='UTJD'><span id='ju3KN'><b id='gd5NFRs1wl'><form id='AOyMfaV3iS'><ins id='dSmgq2p'></ins><ul id='2S7sBC'></ul><sub id='BeQnr'></sub></form><legend id='MnNlGj'></legend><bdo id='3s4HOjoQwb'><pre id='HtcGmsXe'><center id='MnbN'></center></pre></bdo></b><th id='vsKLU07'></th></span></q></dt></tr></i><div id='6VDL0'><tfoot id='teGd5'></tfoot><dl id='Ko4zGNP'><fieldset id='6AQVR'></fieldset></dl></div>

          <bdo id='Aj6aMmrIpV'></bdo><ul id='Gla7ncoQj'></ul>

          1. <li id='ljbx'></li>
            登陆

            Java架构师-面试官出的MySQL索引问题,这篇文章全给你处理

            admin 2020-02-14 150人围观 ,发现0个评论

            0 前语

            这篇文章不会解说索引的基础常识,首要是关于MySQL数据库的B+树索引的相关原理,里边的一些常识都参阅了MySQL技能内情这本书,也算关于这些常识的总结。关于B树和B+树相关的常识,能够参阅我的这篇博客:面试官问你B树和B+树,就把这篇文章丢给他

            1 索引的办理

            索引有许多中类型:一般索引、仅有索引、主键索引、组合索引、全文索引,下面咱们看看怎样创立和删去下面这些类型的索引。

            1.1 索引的创立方法

            索引的创立是能够在许多种状况下进行的。

            • 直接创立索引
            CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length)) 

            [UNIQUE|FULLLTEXT]:表明可挑选的索引类型,仅有索引仍是全文索引,不加话便是一般索引。

            table_name:表的称号,表明为哪个表添加索引。

            column_name(length):column_name是表的列名,length表明为这一列的前length行记载添加索引。

            • 修正表结构的方法添加索引
            ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length)) 
            • 创立表的时分一起创立索引
            CREATE TABLE `table` ( 
            `id` int(11) NOT NULL AUTO_INCREMENT ,
            `title` char(255) CHARACTER NOT NULL ,
            PRIMARY KEY (`id`),
            [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
            )

            1.2 主键索引和组合索引创立的方法

            前面讲的都是一般索引、仅有索引和全文索引创立的方法,但是,主键索引和组合索引创立的方法却是有点不相同的,所以独自拿出来讲一下。

            组合索引创立方法

            • 创立表的时分一起创立索引
            CREATE TABLE `table` ( 
            `id` int(11) NOT NULL AUTO_INCREMENT ,
            `title` char(255) CHARACTER Java架构师-面试官出的MySQL索引问题,这篇文章全给你处理NOT NULL ,
            PRIMARY KEY (`id`),
            INDEX index_name(id,title)
            )
            • 修正表结构的方法添加索引
            ALTER TABLE table_name ADD INDEX name_city_age (name,city,age); 

            主键索引创立方法

            主键索引是一种特别的仅有索引,一个表只能有一个主键,不答应有空值。一般是在建表的时分一起创立主键索引。

            CREATE TABLE `table` ( 
            `id` int(11) NOT NULL AUTO_INCREMENT ,
            `title` char(255) CHARACTER NOT NULL ,
            PRIMARY KEY (`id`)
            )

            1.3 删去索引

            删去索引可运用ALTER TABLE或DROP INDEX句子来删去索引。相似于CREATE INDEX句子,DROP INDEX能够在ALTER TABLE内部作为一条句子处理,语法如下。

            (1)DROP INDEX index_name ON talbe_name

            (2)ALTER TABLE table_name DROP INDEX index_name

            (3)ALTER TABLE table_name DROP PRIMARY KEY

            第3条句子只在删去PRIMARY KEY索引时运用,由于一个表只或许有一个PRIMARY KEY索引,因而不需求指定索引名。

            1.4 索引实例

            上面讲了一下根本的常识,接下来,仍是经过一个详细的比方来领会一下。

            • step1:创立表
             create table table_index( 
            id int(11) not null auto_increment,
            title char(255) not null,
            primary key(id)
            );
            • step2:添加索引

            首要,咱们运用直接添加索引的方法添加一个一般索引。

            CREATE INDEX idx_a ON table_index(title); 

            接着,咱们用修正表Java架构师-面试官出的MySQL索引问题,这篇文章全给你处理结构的时分添加索引。

            ALTER TABLE table_index ADD UNIQUE INDEX idx_b (title(100)); 

            最终,咱们再添加一个组合索引。

            ALTER TABLE table_index ADD INDEX idx_id_title (id,title); 

            这样,咱们就把前面索引的方法都用上一遍了,我信任你也了解这些操作了。

            • step3:运用SHOW INDEX指令检查索引信息

            假设想要检查表中的索引信息,能够运用指令SHOW INDEX,下面的比方,咱们检查表table_index的索引信息。

            SHOW INDEX FROM table_index\G; 



            得到上面的信息,上面的信息什么意思呢?咱们逐个介绍!

            字段解说Table索引地点的表Non_unique非仅有索引,假设是0,代表仅有的,也便是说假设该列索引中不包括重复的值则为0 否则为1Key_name索引的姓名,假设是主键的话 则为PRIMARYSeq_in_index索引中该列的方位,从1开端,假设是组合索引 那么依照字段在树立索引时的次序排列Collation列是以什么方法存储在索引中的。能够是A或许NULL,B+树索引总是A,排序的,Sub_part是否列的部分被索引,假设只是前100行索引,就显现100,假设是整列,就显现NULLPacked要害字是否被紧缩,假设没有,为NULLIndex_type索引的类型,关于InnoDB只支撑B+树索引,所以都是显现BTREE

            • step4:删去索引

            直接删去索引方法

            DROP INDEX idx_a ON table_index; 

            修正表结构时删去索引

            ALTER TABLE table_index DROP INDEX idx_b; 

            1.5 Cardinality要害字解析

            在上面介绍了那么多个要害字的意思,但是Cardinality这个要害字十分的要害,优化器会依据这个值来判别是否运用这个索引。在B+树索引中,只需高挑选性的字段才是有意义的,高挑选性便是这个字段的取值规模很广,比方姓姓名段,会有许多的姓名,可挑选性就高了。

            一般来说,判别是否需求运用索引,就能够经过Cardinality要害字来判别,假设十分挨近1,阐明有必要运用,假设十分小,那么就要考虑是否运用索引了。

            需求留意的一个问题时,这个要害字不是及时更新的,需求更新的话,需求运用ANALYZE TABLE,例如。

            analyze table table_index; 



            由于现在没有数据,所以,你会发现,这个值一向都是0,没有改变。



            InoDB存储引擎Cardinality的战略

            在InnoDB存储引擎中,这个要害字的更新发生在两个操作中:insert和update。但是,并不是每次都会更新,这样会添加负荷,所以,关于这个要害字的更新有它的战略:

            • 表中1/16的数据发生改变
            • InnoDB存储引擎的计数器stat_modified_conter>2000000000

            默许InnoDB存储引擎会对8个叶子节点进行采样,采样进程如下:

            • B+树索引中叶子节点数量,记做A
            • 随机获得B+树索引中的8个叶子节点。计算每个页不同的记载个数,分别为p1-p8
            • 依据采样信息得到Cardinality的预估值:(p1+p2+p3+...+p8)*A/8

            由于随机采样,所以,每次的Cardinality值都是不相同的,只需一种状况会相同的,便是表中的叶子节点小于或许等于8,这时分,怎样随机采样都是这8个,所以也就相同的。

            1.6 Fast Index Creation

            在MySQL 5.5之前,关于索引的添加或许删去,每次都需求创立一张暂时表,然后导入数据到暂时表,接着删去原表,假设一张大表进行这样的操作,会十分的耗时,这是一个很大的缺陷。

            InnoDB存储引擎从1.0.x版别开端加入了一种Fast Index Creation(快速索引创立)的索引创立方法。

            这种方法的战略为:每次为创立索引的表加上一个S锁(同享锁),在创立的时分,不需求重新建表,删去辅佐索引只需求更新内部视图,并将辅佐索引空间符号为可用,所以,这种功率就大大进步了。

            1.7 在线数据界说

            MySQL5.6开端支撑的在线数据界说操作便是:答应辅佐索引创立的一起,还答应其他insert、update、delete这类DM操作,这就极大进步了数据库的可用性。

            所以,咱们能够运用新的语法进行创立索引:

            ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length)) 
            [ALGORITHM = {DEFAULT|INPLACE|COPY}]
            [LOCK = {DEFAULT|NONE|SHARED|EXLUSIVE}]

            ALGORITHM指定创立或许删去索引的算法

            • COPY:创立暂时表的方法
            • INPLACE:不需求创立暂时表
            • DEFAULT:依据参数old_alter_table参数判别,假设是OFF,选用INPLACE的方法

            LOCK表明对表添加锁的状况

            • NONE:不加任何锁
            • SHARE:加一个S锁,并发读能够进行,写操作需求等候
            • EXCLUSIVE:加一个X锁,读写都不能并发进行
            • DEFAULT:先判别是否能够运用NONE,如不能,判别是否能够运用SHARE,如不能,再判别是否能够运用EXCLUSIVE形式。

            2 B+ 树索引的运用

            2.1 联合索引

            联合索引是指对表上的多个列进行索引,这一部分咱们将经过几个比方来解说联合索引的相关常识点。

            首要,咱们先创立一张表以及为这张表创立联合索引。

            create table t_index( 
            a char(2) not null default '',
            b char(2) not null default '',
            c char(2) not null default '',
            d char(2) not null default ''
            )engine myisam charset utf8;

            创立联合索引

            alter table t_index add index abcd(a,b,c,d); 

            刺进几条测试数据

            insert into t_index values('a','b','c','d'), 
            ('a2','b2','c2','d2'),
            ('a3','b3','c3','d3'),
            ('a4','b4','c4','d4'),
            ('a5','b5','c5','d5'),
            ('a6','b6','c6','d6');

            到这一步,咱们现已根本预备好了需求的数据,咱们能够进行更深一步的联合索引的讨论。

            咱们什么时分需求创立联合索引呢

            索引树立的首要意图便是为了进步查询的功率,那么联合索引的意图也是相似的,联合索引的意图便是为了进步存在多个查询条件的状况下的功率,就如上面树立的表相同,有多个字段,当咱们需求运用多个字段进行查询的时分,咱们就需求运用到联合索引了。

            什么时分联合索引才会发挥效果呢

            有时分,咱们会用联合索引,但是,咱们并不清楚其原理,不知道什么时分联合趸索引会起到效果,什么时分又是会失效的?

            带着这个问题,咱们了解一下联合索引的最左匹配准则。

            最左匹配准则:这个准则的意思便是创立组合索引,以最左面的为准,只需查询条件中带有最左面的列,那么查询就会运用到索引。

            下面,咱们用几个比方来看看这个准则。

            EXPLAIN SELECT * FROM t_index WHERE a = 'a' \G; 



            咱们看看这条句子的成果,首要,咱们看到运用了索引,由于查询条件中带有最左面的列a,那么运用了几个索引呢?这个咱们需求看key_len这个字段,咱们知道utf8编码的一个字符3个字节,而咱们运用的数据类型是char(2),占两个字节,索引便是2*3等于6个字节,所以只需一个索引起到了效果。

            EXPLAIN SELECT * FROM t_index WHERE b = 'b2' \G; 



            这个句子咱们能够看出,这个没有运用索引,由于possible_keys为空,而且,从查询的行数rows能够看出为6(咱们测试数据一共6条),阐明进行了全盘扫描的,阐明这种状况是不契合最左匹配准则,所以不会运用索引查询。

            EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY d \G; 



            这种状况又有点不相同了,咱们运用了一个排序,能够看出运用了索引,经过key_len为12能够得到运用了2个索引a、b,别的在Extra选项中能够看到运用了Using filesort,也便是文件排序,这儿运用文件排序的原因是这样的:上面的查询运用了a、b索引,但是当咱们用d字段来排序时,(a,d)或许(b,d)这两个索引是没有排序的,联合索引的运用有一个长处,便是索引的下一个字段是会主动排序的,在这儿的这种状况来说,c字段便是排序的,但是d是不会,假设咱们用c来排序就会得到不相同的成果。

            EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY c \G; 



            是不是能够看到,当咱们用c进行排序的时分,由于运用了a、b索引,所以c就主动排序了,所以也就不必filesort了。

            讲到这儿,我信任经过上面的几个比方,关于联合索引的相关常识现已十分的透彻明晰了,最终,咱们再来聊几个常见的问题。

            Q1:为什么不对表中的每一个列创立一个索引呢

            榜首,创立索引和保护索引要消耗时刻,这种时刻跟着数据量的添加而添加。

            第二,索引需求占物理空间,除了数据表占数据空间之外,每一个索引还要占必定的物理空间,假设要树立聚簇索引,那么需求的空间就会更大。

            第三,当对表中的数据进行添加、删去和修正的时分,索引也要动态的保护,这样就下降了数据的保护速度。

            Q2:为什么需求运用联合索引

            削减开支。建一个联合索引(col1,col2,col3),实践相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会添加写操作的开支和磁盘空间的开支。关于许多数据的表,运用联合索引会大大的削减开支!

            掩盖索引。对联合索引(col1,col2,col3),假设有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL能够直接经过遍历索引获得数据,而无需回表,这削减了许多的随机io操作。削减io操作,特别的随机io其实是dba首要的优化战略。所以,在真实的实践运用中,掩盖索引是首要的进步功用的优化手法之一。

            功率高。索引列越多,经过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假定假定每个条件能够筛选出10%的数据,假设只需单值索引,那么经过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到契合col2=2 and col3= 3的数据,然后再排序,再分页;假设是联合索引,经过索引筛选出1000w10% 10% *10%=1w,功率进步可想而知!

            掩盖索引

            掩盖索引是一种从辅佐索引中就能够得到查询的记载,而不需求查询调集索引中的记载,运用掩盖索引的一个长处是辅佐索引不包括整行记载的一切信息,所以巨细远小于调集索引,因而能够大大削减IO操作。掩盖索引的别的一个长处便是关于计算问题有优化,咱们看下面的一个比方。

            explain select count(*) from t_index \G; 



            假设是myisam引擎,Extra列会输出Select tables optimized away句子,myisam引擎现已保存了记载的总数,直接回来成果,就不需求掩盖索引优化了。

            假设是InnoDB引擎,Extra列会输出Using index句子,阐明InnoDB引擎优化器运用了掩盖索引操作。

            2.2 索引提示

            MySQL数据库支撑索引提示功用,索引提示功用便是咱们能够显现的告知优化器运用哪个索引,一Java架构师-面试官出的MySQL索引问题,这篇文章全给你处理般有下面两种状况或许运用到索引提示功用(INDEX HINT):

            • MySQL数据库的优化器过错的挑选了某个索引,导致SQL运转很慢
            • 某SQL句子能够挑选的索引十分的多,这时优化器挑选履行计划时刻的开支或许会大于SQL句子自身。

            这儿咱们接着上面的比方来解说,首要,咱们先为上面的t_index表添加几个索引;

            alter table t_index add index a (a); 
            alter table t_index add index b (b);
            alter table t_index add index c (c);

            接着,咱们履行下面的句子;

            EXPLAIN SELECT * FROM t_index WHERE a = 'a' AND b = 'b' AND c = 'c' \G; 



            你会发现这条句子就能够运用三个索引,这个时分,咱们能够显现的运用索引提示来运用a这个索引,如下:

            EXPLAIN SELECT * FROM t_iJava架构师-面试官出的MySQL索引问题,这篇文章全给你处理ndex USE INDEX(a) WHERE a = 'a' AND b = 'b' AND c = 'c' \G; 



            这样就显现的运用索引a了,假设这种方法有时分优化器仍是没有挑选你想要的索引,那么,咱们能够别的一种方法FORCE INDEX。

            EXPLAIN SELECT * FROM t_index FORCE INDEX(a) WHERE a = 'a' AND b = 'b' AND c = 'c' \G; 



            这种方法则必定会挑选你想要的索引。

            2.3 索引优化

            Multi-Range Read 优化

            MySQL5.6开端支撑,这种优化的意图是为了削减磁盘的随机拜访,而且将随机拜访转化为较为次序的数据拜访,这种优化适用于range、ref、eq_ref类型的查询。

            Multi-Range Read 优化的长处:

            • 让数据拜访变得较为次序。
            • 削减缓冲区中页被替换的次数。
            • 批量处理对键值的查询操作。

            咱们能够运用参数optimizer_switch中的符号来操控是否敞开Multi-Range Read 优化。下面的方法将设置为总是敞开状况:

            SET @@optimizer_switch='mrr=on,mrr_cost_based=off'; 

            Index Condition Pushdown(ICP) 优化

            这种优化方法也是从MySQL5.6开端支撑的,不支撑这种方法之前,当进行索引查询时,首要咱们先依据索引查找记载,然后再依据where条件来过滤记载。但是,当支撑ICP优化后,MySQL数据库会在取出索引的一起,判别是否能够进行where条件过滤,也便是将where过滤部分放在了存储引擎层,大大削减了上层SQL对记载的讨取。

            ICP支撑range、ref、eq_ref、ref_or_null类型的查询,当时支撑MyISAM和InnoDB存储引擎。

            咱们能够运用下面句子敞开ICP:

            set @@optimizer_switch = "index_condition_pushdown=on" 

            或许封闭:

            set @@optimizer_switch = "index_condition_pushdown=off" 

            当敞开了ICP之后,在履行计划Extra能够看到Using index condition提示。

            3 索引的特色Java架构师-面试官出的MySQL索引问题,这篇文章全给你处理、长处、缺陷及适用场景

            索引的特色

            • 能够加速数据库的检索速度
            • 下降数据库刺进、修正、删去等保护的速度
            • 只能创立在表上,不能创立在视图上
            • 既能够直接创立也能够直接创立

            索引的长处

            • 创立仅有性索引,确保数据库表中的每一行数据的仅有性
            • 大大加速数据的检索速度
            • 加速数据库表之间的衔接,特别是在完成数据的参阅完整性方面特别有意义
            • 在运用分组和排序字句进行数据检索时,相同能够明显削减查询的时刻
            • 经过运用索引,能够在查询中运用优化躲藏器,进步体系功用

            索引的缺陷

            • 榜首,创立索引和保护索引要消耗时刻,这种时刻跟着数据量的添加而添加。
            • 第二,索引需求占物理空间,除了数据表占数据空间之外,每一个索引还要占必定的物理空间,假设要树立聚簇索引,那么需求的空间就会更大。
            • 第三,当对表中的数据进行添加、删去和修正的时分,索引也要动态的保护,这样就下降了数据的保护速度。

            索引的适用场景

            • 匹配全值

            对索引中一切列都指定详细值,便是对索引中的一切列都有等值匹配的条件。

            • 匹配值的规模查询

            对索引的值能够进行规模查找。

            • 匹配最左前缀

            只是运用索引中的最左面列进行查询,比方在 col1 + col2 + col3 字段上的联合索引能够被包括 col1、(col1 + col2)、(col1 + col2 + col3)的等值查询运用到,但是不能够被 col2、(col2、col3)的等值查询运用到。

            最左匹配准则能够算是 MySQL 中 B-Tree 索引运用的首要准则。

            • 只是对索引进行查询

            当查询的列都在索引的字段中时,查询的功率更高,所以应该尽量防止运用 select *,需求哪些字段,就只查哪些字段。

            • 匹配列前缀

            只是运用索引中的榜首列,而且只包括索引榜首列的最初一部分进行查找。

            • 能够完成索引匹配部分准确而其他部分进行规模匹配
            • 假设列名是索引,那么运用 column_name is null 就会运用索引,例如下面的就会运用索引:
            explain select * from t_index where a is null \G 
            • 常常呈现在要害字order by、group by、distinct后边的字段
            • 在union等调集操作的成果集字段
            • 常Java架构师-面试官出的MySQL索引问题,这篇文章全给你处理常用作表衔接的字段
            • 考虑运用索引掩盖,对数据很少被更新,假设用户常常值查询其间你的几个字段,能够考虑在这几个字段上树立索引,从而将表的扫描变为索引的扫描

            索引失效状况

            • 以%最初的 like 查询不能运用 B-Tree 索引,履行计划中 key 的值为 null 表明没有运用索引
            • 数据类型呈现隐式转化的时分也不会运用索引,例如,where 'age'+10=30
            • 对索引列进行函数运算,原因同上
            • 正则表达式不会运用索引
            • 字符串和数据比较不会运用索引
            • 复合索引的状况下,假设查询条件不包括索引列最左面部分,即不满足最左准则 leftmost,是不会运用复合索引的
            • 假设 MySQL 估量运用索引比全表扫描更慢,则不运用索引
            • 用 or 分割开的条件,假设 or 前的条件中的列有索引,然后边的列中没有索引,那么触及的索引都不会被用到
            • 运用负向查询(not ,not in, not like ,<> ,!= ,!> ,!< ) 不会运用索引
            请关注微信公众号
            微信二维码
            不容错过
            Powered By Z-BlogPHP