精华内容
下载资源
问答
  • 定位低效率sql对于mysql性能优化,除了宏观层面网络、设备等优化,sql语句优化是极为重要一环,需要想办法找到对应执行效率低sql语句进行优化。慢查询日志慢查询日志是定位低效率sql手段之一,通过以下...

    定位低效率sql

    对于mysql性能优化,除了宏观层面的网络、设备等优化,sql语句的优化是极为重要的一环,需要想办法找到对应的执行效率低的sql语句进行优化。

    慢查询日志

    慢查询日志是定位低效率sql的手段之一,通过以下命令,设置开启慢查询日志。

    #显示是否开启了慢查询日志

    show variables like 'slow_query_log';

    #开启慢查询日志

    set global slow_query_log = on

    #慢查询日志输出位置

    set global slow_query_log_file = '/var/lib/mysql/gupaoedu-slow.log'

    set global log_queries_not_using_indexes = on

    #设置耗时查询时间阈值

    set global long_query_time = 0.1

    有了慢查询日志之后,可以通过mysqldumpslow分析哪一条sql语句需要进行优化,建立合适的索引。

    Druid数据源的监控

    使用阿里的Druid数据源时可以对接自带的监控平台,非常方便有各种统计数据,可以监控慢sql

    业务定位

    这个就非常粗暴了,只管堆数据,然后看接口的效率,可以粗略定位sql语句

    explain输出格式含义分析

    explain输出格式含义,这一个在mysql官网有完整的解释,奈何英语水平有限,看的难受,幸好发现这位博主的一篇文章,解释的非常全面,易懂,强烈推荐EXPLAIN用法和结果分析,了解的explain的输出含义,才能做以下分析。

    深入了解explain结果分析,了解每种情况会出现什么样的explain结果输出,以此能够更加清晰明了、正确地使用索引。

    根据explain分析索引有效性

    假设有如下表结构,创建主键为id,联合索引为(biz_no,cus_code),单列唯一索引uni_no

    -- auto-generated definition

    create table index_test

    (

    id int auto_increment

    primary key,

    biz_no varchar(11) default '' not null,

    cus_code varchar(11) default '' not null,

    uni_no varchar(11) default '' not null,

    constraint idx_biz_no_cus_code

    unique (biz_no, cus_code),

    constraint uk_uni_no

    unique (uni_no)

    );

    执行以下存储过程,插入数据。

    #定义存储过程

    delimiter //

    DROP PROCEDURE IF EXISTS insert_test_val;

    ##num_limit 要插入数据的数量,begin_num 起始数字

    CREATE PROCEDURE insert_test_val(in num_limit int,in begin_num int)

    BEGIN

    DECLARE i int default 1;

    DECLARE a varchar(11) default '';

    DECLARE b varchar(11) default '';

    DECLARE c varchar(11) default '';

    WHILE i<=num_limit do

    set a = concat('bizno',begin_num);

    set b = concat('cus',begin_num);

    set c =concat('uni',begin_num);

    INSERT into index_test(biz_no, cus_code, uni_no) values (a,b,c);

    set i = i + 1;

    set begin_num = begin_num + 1;

    END WHILE;

    END

    #调用存储过程

    call insert_test_val(200000,1);

    单列索引

    在where条件中不同的使用方式可能会对索引的有效性有不同的影响,这里简单分析几种情况。

    情况1:key=?情况

    对于单列索引,最常见的是key=?的情况,例如针对上面的表结构,执行以下sql,分析explain结果。

    explain select * from index_test where uni_no='uni500';

    执行结果如下

    7b6a327c5d97a0bfb7b7d22dcf7820f8.png

    可以看出,type是const,表示是一次索引就能得到数据,索引有效。

    如果我们了解innodb索引树的结构,应该可以知道,这里的索引过程应该是,先从辅助索引树uni_no的索引树检索得到主键id,然后还需要从主键索引树检索目标记录,因此除了能够使用索引之外,还可以进一步优化,就是根据业务需求,看能否使用上覆盖索引。

    覆盖索引下

    例如以下sql。只选择id的话,在辅助索引树就能完成,不需要再去主键索引树检索,大大提高了性能。

    explain select id from index_test where uni_no='uni500';

    1ef1d85e29b4512f20e099361c6649b2.png

    情况2:key > ? | key < ?情况。

    对于范围查询,会影响查询效率,这个是必然的,但是在范围条件中建立索引,能否够提升查询效率?

    索引生效

    执行如下sql,使用索引进行范围查询

    select * from index_test where uni_no < 'uni10000';

    结果是下图,只有四条记录。

    582bce726d6298d848541702290ffd6e.png

    执行explain sql分析执行计划,结果如下图,可以看出,这里的type使用的是range类型,也就是从指定的范围开始检索记录,uni_no索引生效。

    372da11256d597fbddb67a5a384ba2c2.png

    反转,索引不生效

    但是假如我们执行以下sql,把条件改为大于号,得到的explain输出确实不一样。

    explain select * from index_test where uni_no > 'uni10000';

    输出,此时执行的是全表扫描,索引不生效,这是为什么?

    096bbef28317c6cb5491243faa0b86c7.png

    思考,为何范围查询索引时而生效时而不生效

    我在数据库表中插入总共20万数据,其中条件为uni_no < 'uni10000'的数据只有四条。而反过来的数据则有199996条,约等于全表数据。我们可以知道在不是覆盖索引的情况下,使用辅助索引树检索数据需要检索两遍,假如mysql在uni_no索引树范围内逐条检索到199996条主键记录符合,然后每条主键记录都去主键索引树检索记录数据的话,效率还不如全表扫描。

    因此执行计划对此有相应优化,如果查询优化器预计辅助索引树检索到的符合的记录量特别大的情况下,就不会使用辅助索引树查询,反而使用全表扫描来完成检索,例如uni_no > 'uni10000'的情况。反之,如果预计辅助索引树检索到的记录不多的情况下,还是会使用辅助索引来检索,例如uni_no < 'uni10000'的情况.

    但是需要注意,非唯一索引树精确值等值检索,不管匹配多少记录,都是走索引树,不会因为匹配多了就不走索引树检索,猜测是因为非唯一索引树精确检索效率很高,type属于ref类型,因此先检索辅助索引树在检索主键索引树的效率还是比all全表要高。

    再次思考,在单列覆盖索引的情况下,是否应该一直生效

    根据上面的思考再次推论,在覆盖索引的情况下,检索数据不需要在辅助索引树和主键索引树分别检索两遍,只需要在辅助索引树检索就能得到对应数据,那么直接在辅助索引树上进行查询应该是最优的策略,推测不管是uni_no < 'uni10000'还是uni_no > 'uni10000',索引都应该生效。

    执行以下sql验证。

    explain select id from index_test where uni_no > 'uni10000';

    explain select id from index_test where uni_no < 'uni10000';

    结果查询的type都是range,索引生效,推测正确。

    db5839977758792f26831af09694e78b.png

    4ca24a3aecc4d6ea7615a7c363e95d2a.png

    类比,其他索引时而生效时而不生效的情况

    在key > ? | key < ?情况下,索引是否生效并不是绝对的。因为是范围查询,导致辅助索引树可能匹配到多条记录,然后到逐渐索引树检索,这种情况索引就不会生效,反而会使用all查询。其实类似的情形可以类比到一些辅助索引树匹配到记录比较多的情况。

    order by或者group by索引时而生效时而不生效

    例如本例子中,对比以下两个sql的输出,你会发现也是索引不一定生效的,type不一定是range,一切有查询优化器决定。

    explain select * from index_test where biz_no > 'bizno10000' order by biz_no,cus_code;

    #辅助索引匹配记录预计趋近全表,不使用索引

    #输出,使用全表扫描,filesort外部排序

    1SIMPLEindex_testALLidx_biz_no_cus_code199586Using where; Using filesort

    #但是假如是覆盖索引,辅助树匹配记录预计趋近全表,则还是会使用索引排序,如下sql

    explain select biz_no from index_test where biz_no > 'bizno10000' order by biz_no,cus_code;

    #输出,使用range,索引排序

    1SIMPLEindex_testrangeidx_biz_no_cus_codeidx_biz_no_cus_code1399793Using where; Using index

    explain select * from index_test where biz_no < 'bizno10000' order by biz_no,cus_code;

    #辅助索引匹配记录预计较少,

    #输出,使用range,索引排序

    1SIMPLEindex_testrangeidx_biz_no_cus_codeidx_biz_no_cus_code134Using where

    order by或者group byPS分析总结:当order by出现filesort时考虑以下问题

    order by条件是否已经建立索引?多个order by列时,还需要建立联合索引来提升,避免出现filrsort。

    如果确认order by已经建立索引,那么再看看是否加了where条件和where条件是否走order by列的索引?如果不是就会出现filesort。如果where条件与order by使用的是同一个索引,还是出现filesort,继续往下看

    如果where条件与order by使用的是同一个索引,还是出现filesort,考虑是不是where条件在辅助索引树匹配的记录太多,导致查询优化器直接不走索引树查询,如果是,请考虑控制where条件或者考虑配合业务手段,使用覆盖索引方式操作来达到走索引的目的。

    当然,出现filesort并不是说就一定是大问题,如果select的explain 输出type是range以上,使用filesort也是可以接受的,只是如果对性能有很高要求时,可以考虑这样子去优化。

    ##这是一个查询类型是range,但是使用filesort的sql,效率也不差

    explain select * from index_test where uni_no < 'uni10000' order by biz_no,cus_code;

    #输出

    1SIMPLEindex_testrangeuk_uni_nouk_uni_no134Using where; Using filesort

    情况3:<> |!=

    这些情况下不走索引,explain 输出type都是all

    总结

    单列索引有效性分析总结以下几点:

    精确匹配索引生效

    范围查找情况,索引生效判断依据以下规则:

    覆盖索引下,范围查找索引一律生效

    非覆盖索引查询,如果预计辅助索引树检索范围内匹配记录数据量较大,则索引不生效,做All扫描;

    如果预计索引树检索范围内匹配记录数据量较小,则索引生效,做range类型查询。

    order by、group by等没有使用索引排序出现filesort,参考PS分析总结

    like的情况按照最左匹配原则生效

    没使用索引精确或者范围查询,索引不可能生效。非等查询,索引也不能生效。

    联合索引

    分析联合索引的有效性时,首先需要知道的点是联合索引的匹配原则是最左匹配。

    情况1:key1=? and key2=?

    假如有联合索引(key1,key2),那么在完全精确匹配的情况下,索引是有效的,查询类型type是const,表示一次索引就能得到结果。例如以下例子。

    explain select * from index_test where biz_no='bizno5000' and cus_code='cus5000';

    输出如图所示

    f1f7b38206b534ef9687fdccaa84475b.png

    类似地,两列以上的联合索引精确匹配也是const类型查询。

    情况二:key1=?

    假如有联合索引(key1,key2),在没有精确匹配的情况下只使用key1=?作为索引条件,遵循最左匹配原则,只用最部分最左条件进行索引查询,索引生效情况如何?以下例子。

    explain select * from index_test where biz_no='bizno5000';

    结果输出如图下所示,可以看出查询类型下降一个级别,变成ref,索引可以匹配多条记录,但是效率依然很高,索引依然生效。

    7bedf642d1332b958ae57adff62e62bd.png

    类似地,两列以上的联合索引,依据最左前缀原则匹配查询,查询type也是ref。例如联合索引(key1,key2,key3),使用where条件是key1=‘a’ and key2=‘b’,查询type也是ref。

    精确查询与范围查询结合

    类似地,假如最左列使用精确检索,右边列使用范围检索,精确查询效果如何?

    explain select * from index_test where biz_no = 'bizno10000' and cus_code >= 'cus10000';

    输出查询类型为ref,索引生效.

    情况3:联合索引仅范围查询

    跟单列索引一样,涉及到仅使用范围查询分析索引生效情况,必须分为是否是覆盖索引的查询来分析。要想仅使用范围查询,并且联合索引生效,还是需要按照最左前缀匹配,所以只有在最左列作为范围条件时才可能生效。因此最终的分析方式其实跟单列的索引范围查询一样分析。

    非覆盖索引

    辅助索引树匹配范围记录较少的情况?

    使用最左列进行范围查询, biz_no < 'bizno10000’记录较少,只有四条。

    explain select * from index_test where biz_no < 'bizno10000' ;

    输出结果如下图所示,虽然使用了范围查询,但是辅助索引树预计范围内匹配记录数量较少,那么再次扫描主键索引树获取全部数据的消耗时间不会很高,因此还是使用查询类型type是range的查询。此时索引生效。

    6c612b0186aa0ee171178a3e6a498721.png

    辅助索引树匹配范围记录较多的情况?

    使用最左列进行范围查询, biz_no > 'bizno10000’记录趋近全表。

    explain select * from index_test where biz_no > 'bizno10000' ;

    输出结果如下图所示,联合索引的索引树预计范围内匹配的数据量比较大,再次扫描主键索引树获取全部数据的消耗时间可能较高,所以查询优化器优化决定使用全表扫描代替range查询。此时索引不生效。

    a23e095caa3ad1887de0b228fdc70228.png

    覆盖索引

    覆盖索引下,减少了再次扫描主键索引树的过程,那么其实不管联合索引的索引树匹配多少条记录都是无所谓的,因此都是使用range查询,索引都会生效。

    例如匹配记录多的情况。

    explain select biz_no from index_test where biz_no > 'bizno10000' ;

    输出如下图所示,虽然预计扫描行数较多,但是还是使用range查询,索引生效,因为不需要再次扫描主键索引树,range查询联合索引树效率最高。Extra中的Using Index使用覆盖索引查询。

    0d4a4b1afa2e199c1197dab51a50acb2.png

    不用说,匹配记录少的情况同理也是一样。

    总结

    联合索引的索引生效原则如下:

    所有列精确匹配索引一定生效

    最左列精确匹配,右边列使用范围查询、非等查询、不使用右边列,索引一样生效。

    最左列使用范围查询时:

    如果是覆盖查询,索引一律生效

    如果不是覆盖查询,当查询优化器预计范围匹配记录较大的时候,索引不生效;当查询优化器预计范围匹配记录较小的时候,索引生效。

    如果最左列没使用精确或者范围查询,联合索引不可能生效。

    like 查询跟单列索引类似,也是最左匹配原则。

    展开全文
  • 索引

    2019-09-24 01:24:04
    一般来说以下规则是正确的:  ● 用聚集索引比用非聚集索引的主键速度快。  ● 用聚集索引比用一般的主键做order by时速度快,特别是在小数据量情况下。  ● 使用聚集索引内的时间段,搜索...

    对于索引有一些错误观点,具体如下。

      ● 主键就是聚集索引。

      ● 只要建立索引就能显著提高查询速度。

      ● 把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度。

    一般来说以下规则是正确的:

      ● 用聚集索引比用非聚集索引的主键速度快。

      ● 用聚集索引比用一般的主键做order by时速度快,特别是在小数据量情况下。

      ● 使用聚集索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚集索引使用了多少个。

      ● “水可载舟,亦可覆舟”,索引也一样,不要过度使用索引。

    转载于:https://www.cnblogs.com/dushuai/articles/2555930.html

    展开全文
  • 880. 索引解码字符串 给定一个编码字符串 S。为了找出解码字符串并将其写入磁带,从编码字符...现在,对于给定编码字符串 S 和索引 K,查找并返回解码字符串中第 K 个字母。 示例 1: 输入:S = ...

    880. 索引处的解码字符串

    给定一个编码字符串 S。为了找出解码字符串并将其写入磁带,从编码字符串中每次读取一个字符,并采取以下步骤:

    如果所读的字符是字母,则将该字母写在磁带上。
    如果所读的字符是数字(例如 d),则整个当前磁带总共会被重复写 d-1 次。
    现在,对于给定的编码字符串 S 和索引 K,查找并返回解码字符串中的第 K 个字母。

    示例 1:

    输入:S = “leet2code3”, K = 10
    输出:”o”
    解释:
    解码后的字符串为 “leetleetcodeleetleetcodeleetleetcode”。
    字符串中的第 10 个字母是 “o”。

    示例 2:

    输入:S = “ha22”, K = 5
    输出:”h”
    解释:
    解码后的字符串为 “hahahaha”。第 5 个字母是 “h”。

    示例 3:

    输入:S = “a2345678999999999999999”, K = 1
    输出:”a”
    解释:
    解码后的字符串为 “a” 重复 8301530446056247680 次。第 1 个字母是 “a”。

    提示:

    • 2 <= S.length <= 100
    • S 只包含小写字母与数字 2 到 9 。
    • S 以字母开头。
    • 1 <= K <= 10^9
    • 解码后的字符串保证少于 2^63 个字母。**

    思路

    首先阅读题目要求

    • 每次读取一个字符
    • 读取字符为字母,记录该字母
    • 读取为数字d,前面记录的字符串会复制d-1次,并加上末端

      读了上述要求后,自然而然地想到直接的算法:按照输入字符串进行遍历,遍历到字母, 则存储; 遍历到数字,则将前面储存的字符串以d-1次循环加在原字符串后方。代码如下:

    string decodeAtIndex(string S, int K) {
       const char * s = S.data();
       int length = S.length();
       vector<char> output;
       int i  = 0;
       while (s[i] != '\0')
       {
           if (s[i] - 'a' >= 0 && s[i] - 'z' <= 0)            //字母则存储
            {
                output.push_back(s[i]);
                i++;
           }
           else                                              //如果是数字
           {
               int count = s[i] - '0'- 1;                    //将char数字转化为int并-1
               int length0 = output.size();
               while (count > 0)                           
               {
                   for (int i = 0; i < length0; i++)         //将原字符串的内容加在末尾
                   {                
                         output.push_back(output[i]);
                   }
                   count--;
               }
               i++;
           }
       }
       string res = {output[K-1]};
       return res;
    }

    这样的代码符合一般思维,可以轻松地过示例一与示例二。

    但是,上面的代码实际上是错误的,没有考虑到出题人意图的!

    这样是过不了示例三的, “a” 重复 8301530446056247680 次无疑会造成存储的字符串对象的溢出,leetcode会提示超出内存限制。那么该怎么优化我们的代码呢,
    其实题目已经给出提示了,就是索引Index,我们只需要输出下标对应的字母,也就是说我们将K值还原成对应S字符串中某个下标i,再输出S[i]就是我们的输出值了

    我们用curIndex记录当前decode字符串长度,i记录对应S中的字符位置,过程如下:
    首先对S[i]一个个遍历,如果是字母,就将curIndex+1,如果碰到数字,就将curIndex乘以这个数字,当然同时要判断,索引与K的关系。如果索引小于K,就一直遍历下去,如果大于等于K,这时可以停止遍历,因为K对应的下标已经在我们的decode字符串之前了,后面的工作是没必要的,笔者看见有些解法是遍历到结束,实际上这部分工作是可以省略的。
    确定了我们已经decode了足够的长度之后,将curIndex往回退,如果是数字,就要除以这个数字,变成单倍数的字符串,相应的,第K个也要变成单倍数的,最后找到对应的s[i],并输出。
    代码如下:

        string decodeAtIndex(string S, int K) {
    
        const char * s = S.data();
        long curIndex = 0;                        //注意这里是long,不然有测试案例会长度溢出
        int i = 0;
        for (; curIndex < K; i++)
        {
            if (s[i]>='a' && s[i]<='z')              
                curIndex++;
            else
                curIndex = curIndex*(s[i]-'0');  
        }
        while (i--)                             
        {
            if (s[i] >= '2' && s[i] <= '9')
            {
                curIndex = curIndex / (s[i] - '0');
                K = K % curIndex;
            }
            else if (K % (curIndex--)==0)
            {
                string res = {s[i]};
                return res;           
            }
        }
    }
    展开全文
  • 对于mysql性能优化,除了宏观层面网络、设备等优化,sql语句优化是极为重要一环,需要想办法找到对应执行效率低sql语句进行优化。 慢查询日志 慢查询日志是定位低效率sql手段之一,通过以下命令,设置...

    定位低效率sql

    对于mysql性能优化,除了宏观层面的网络、设备等优化,sql语句的优化是极为重要的一环,需要想办法找到对应的执行效率低的sql语句进行优化。

    慢查询日志

    慢查询日志是定位低效率sql的手段之一,通过以下命令,设置开启慢查询日志。

    #显示是否开启了慢查询日志
    show variables like 'slow_query_log'; 
    #开启慢查询日志
    set global slow_query_log = on 
    #慢查询日志输出位置
    set global slow_query_log_file = '/var/lib/mysql/gupaoedu-slow.log' 
    set global log_queries_not_using_indexes = on 
    #设置耗时查询时间阈值
    set global long_query_time = 0.1 
    

    有了慢查询日志之后,可以通过mysqldumpslow分析哪一条sql语句需要进行优化,建立合适的索引。

    Druid数据源的监控

    使用阿里的Druid数据源时可以对接自带的监控平台,非常方便有各种统计数据,可以监控慢sql

    业务定位

    这个就非常粗暴了,只管堆数据,然后看接口的效率,可以粗略定位sql语句

    explain输出格式含义分析

    explain输出格式含义,这一个在mysql官网有完整的解释,奈何英语水平有限,看的难受,幸好发现这位博主的一篇文章,解释的非常全面,易懂,强烈推荐EXPLAIN用法和结果分析,了解的explain的输出含义,才能做以下分析。

    深入了解explain结果分析,了解每种情况会出现什么样的explain结果输出,以此能够更加清晰明了、正确地使用索引。

    根据explain分析索引有效性

    假设有如下表结构,创建主键为id,联合索引为(biz_no,cus_code),单列唯一索引uni_no

    -- auto-generated definition
    create table index_test
    (
      id       int auto_increment
        primary key,
      biz_no   varchar(11) default '' not null,
      cus_code varchar(11) default '' not null,
      uni_no   varchar(11) default '' not null,
      constraint idx_biz_no_cus_code
        unique (biz_no, cus_code),
      constraint uk_uni_no
        unique (uni_no)
    );
    
    

    执行以下存储过程,插入数据。

    #定义存储过程
    delimiter //
    DROP PROCEDURE IF EXISTS insert_test_val;
    ##num_limit 要插入数据的数量,begin_num 起始数字
    CREATE PROCEDURE insert_test_val(in num_limit int,in begin_num int)
    BEGIN
      DECLARE i int default 1;
      DECLARE a varchar(11) default '';
      DECLARE b varchar(11) default '';
      DECLARE c varchar(11) default '';
      WHILE i<=num_limit do
      set a = concat('bizno',begin_num);
      set b = concat('cus',begin_num);
      set c =concat('uni',begin_num);
      INSERT into index_test(biz_no, cus_code, uni_no) values (a,b,c);
      set i = i + 1;
      set begin_num = begin_num + 1;
      END WHILE;
    END
    
    
    #调用存储过程
    call insert_test_val(200000,1);
    
    

    单列索引

    在where条件中不同的使用方式可能会对索引的有效性有不同的影响,这里简单分析几种情况。

    情况1:key=?情况

    对于单列索引,最常见的是key=?的情况,例如针对上面的表结构,执行以下sql,分析explain结果。

    explain select * from index_test where uni_no='uni500';
    

    执行结果如下
    在这里插入图片描述

    可以看出,type是const,表示是一次索引就能得到数据,索引有效。

    如果我们了解innodb索引树的结构,应该可以知道,这里的索引过程应该是,先从辅助索引树uni_no的索引树检索得到主键id,然后还需要从主键索引树检索目标记录,因此除了能够使用索引之外,还可以进一步优化,就是根据业务需求,看能否使用上覆盖索引

    覆盖索引

    例如以下sql。只选择id的话,在辅助索引树就能完成,不需要再去主键索引树检索,大大提高了性能。

    explain select id from index_test where uni_no='uni500';
    


    在这里插入图片描述

    情况2:key > ? | key < ?情况。

    对于范围查询,会影响查询效率,这个是必然的,但是在范围条件中建立索引,能否够提升查询效率?

    索引生效

    执行如下sql,使用索引进行范围查询

    select * from index_test where uni_no < 'uni10000';
    

    结果是下图,只有四条记录。


    在这里插入图片描述
    执行explain sql分析执行计划,结果如下图,可以看出,这里的type使用的是range类型,也就是从指定的范围开始检索记录,uni_no索引生效。


    在这里插入图片描述

    反转,索引不生效

    但是假如我们执行以下sql,把条件改为大于号,得到的explain输出确实不一样。

    explain select * from index_test where uni_no > 'uni10000';
    

    输出,此时执行的是全表扫描,索引不生效,这是为什么?


    在这里插入图片描述

    思考,为何范围查询索引时而生效时而不生效

    我在数据库表中插入总共20万数据,其中条件为uni_no < 'uni10000'的数据只有四条。而反过来的数据则有199996条,约等于全表数据。我们可以知道在不是覆盖索引的情况下,使用辅助索引树检索数据需要检索两遍,假如mysql在uni_no索引树范围内逐条检索到199996条主键记录符合,然后每条主键记录都去主键索引树检索记录数据的话,效率还不如全表扫描。

    因此执行计划对此有相应优化,如果查询优化器预计辅助索引树检索到的符合的记录量特别大的情况下,就不会使用辅助索引树查询,反而使用全表扫描来完成检索,例如uni_no > 'uni10000'的情况。反之,如果预计辅助索引树检索到的记录不多的情况下,还是会使用辅助索引来检索,例如uni_no < 'uni10000'的情况.

    但是需要注意,非唯一索引树精确值等值检索,不管匹配多少记录,都是走索引树,不会因为匹配多了就不走索引树检索,猜测是因为非唯一索引树精确检索效率很高,type属于ref类型,因此先检索辅助索引树在检索主键索引树的效率还是比all全表要高。

    再次思考,在单列覆盖索引的情况下,是否应该一直生效

    根据上面的思考再次推论,在覆盖索引的情况下,检索数据不需要在辅助索引树和主键索引树分别检索两遍,只需要在辅助索引树检索就能得到对应数据,那么直接在辅助索引树上进行查询应该是最优的策略,推测不管是uni_no < 'uni10000'还是uni_no > 'uni10000',索引都应该生效。

    执行以下sql验证。

    explain select id from index_test where uni_no > 'uni10000';
    
    explain select id from index_test where uni_no < 'uni10000';
    

    结果查询的type都是range,索引生效,推测正确。


    在这里插入图片描述

    在这里插入图片描述

    类比,其他索引时而生效时而不生效的情况

    在key > ? | key < ?情况下,索引是否生效并不是绝对的。因为是范围查询,导致辅助索引树可能匹配到多条记录,然后到逐渐索引树检索,这种情况索引就不会生效,反而会使用all查询。其实类似的情形可以类比到一些辅助索引树匹配到记录比较多的情况。

    order by或者group by索引时而生效时而不生效

    例如本例子中,对比以下两个sql的输出,你会发现也是索引不一定生效的,type不一定是range,一切有查询优化器决定。

    explain select * from index_test where biz_no > 'bizno10000' order by biz_no,cus_code;
    #辅助索引匹配记录预计趋近全表,不使用索引
    #输出,使用全表扫描,filesort外部排序
    1	SIMPLE	index_test	ALL	idx_biz_no_cus_code				199586	Using where; Using filesort
    
    #但是假如是覆盖索引,辅助树匹配记录预计趋近全表,则还是会使用索引排序,如下sql
    explain select biz_no from index_test where biz_no > 'bizno10000' order by biz_no,cus_code;
    #输出,使用range,索引排序
    1	SIMPLE	index_test	range	idx_biz_no_cus_code	idx_biz_no_cus_code	13		99793	Using where; Using index
    
    
    explain select * from index_test where biz_no < 'bizno10000' order by biz_no,cus_code;
    #辅助索引匹配记录预计较少,
    #输出,使用range,索引排序
    1	SIMPLE	index_test	range	idx_biz_no_cus_code	idx_biz_no_cus_code	13		4	Using where
    
    

    order by或者group byPS分析总结:当order by出现filesort时考虑以下问题

    1. order by条件是否已经建立索引?多个order by列时,还需要建立联合索引来提升,避免出现filrsort。
    2. 如果确认order by已经建立索引,那么再看看是否加了where条件和where条件是否走order by列的索引?如果不是就会出现filesort。如果where条件与order by使用的是同一个索引,还是出现filesort,继续往下看
    3. 如果where条件与order by使用的是同一个索引,还是出现filesort,考虑是不是where条件在辅助索引树匹配的记录太多,导致查询优化器直接不走索引树查询,如果是,请考虑控制where条件或者考虑配合业务手段,使用覆盖索引方式操作来达到走索引的目的。

    当然,出现filesort并不是说就一定是大问题,如果select的explain 输出type是range以上,使用filesort也是可以接受的,只是如果对性能有很高要求时,可以考虑这样子去优化。

    ##这是一个查询类型是range,但是使用filesort的sql,效率也不差
    explain select * from index_test where uni_no < 'uni10000'  order by biz_no,cus_code;
    #输出
    1	SIMPLE	index_test	range	uk_uni_no	uk_uni_no	13		4	Using where; Using filesort
    

    情况3:<> |!=

    这些情况下不走索引,explain 输出type都是all

    总结

    单列索引有效性分析总结以下几点:

    1. 精确匹配索引生效

    2. 范围查找情况,索引生效判断依据以下规则:

      • 覆盖索引下,范围查找索引一律生效

      • 非覆盖索引查询,如果预计辅助索引树检索范围内匹配记录数据量较大,则索引不生效,做All扫描;

        如果预计索引树检索范围内匹配记录数据量较小,则索引生效,做range类型查询。

    3. order by、group by等没有使用索引排序出现filesort,参考PS分析总结

    4. like的情况按照最左匹配原则生效

    5. 没使用索引精确或者范围查询,索引不可能生效。非等查询,索引也不能生效。

    联合索引

    分析联合索引的有效性时,首先需要知道的点是联合索引的匹配原则是最左匹配。

    情况1:key1=? and key2=?

    假如有联合索引(key1,key2),那么在完全精确匹配的情况下,索引是有效的,查询类型type是const,表示一次索引就能得到结果。例如以下例子。

    explain select * from index_test where biz_no='bizno5000' and cus_code='cus5000';
    

    输出如图所示


    在这里插入图片描述

    类似地,两列以上的联合索引精确匹配也是const类型查询。

    情况二:key1=?

    假如有联合索引(key1,key2),在没有精确匹配的情况下只使用key1=?作为索引条件,遵循最左匹配原则,只用最部分最左条件进行索引查询,索引生效情况如何?以下例子。

    explain select * from index_test where biz_no='bizno5000';
    

    结果输出如图下所示,可以看出查询类型下降一个级别,变成ref,索引可以匹配多条记录,但是效率依然很高,索引依然生效。


    在这里插入图片描述

    类似地,两列以上的联合索引,依据最左前缀原则匹配查询,查询type也是ref。例如联合索引(key1,key2,key3),使用where条件是key1=‘a’ and key2=‘b’,查询type也是ref。

    精确查询与范围查询结合

    类似地,假如最左列使用精确检索,右边列使用范围检索,精确查询效果如何?

    explain select * from index_test where biz_no = 'bizno10000' and cus_code >= 'cus10000';
    

    输出查询类型为ref,索引生效.

    情况3:联合索引仅范围查询

    跟单列索引一样,涉及到仅使用范围查询分析索引生效情况,必须分为是否是覆盖索引的查询来分析。要想仅使用范围查询,并且联合索引生效,还是需要按照最左前缀匹配,所以只有在最左列作为范围条件时才可能生效。因此最终的分析方式其实跟单列的索引范围查询一样分析。

    非覆盖索引

    辅助索引树匹配范围记录较少的情况?

    使用最左列进行范围查询, biz_no < 'bizno10000’记录较少,只有四条。

    explain select * from index_test where biz_no < 'bizno10000' ;
    

    输出结果如下图所示,虽然使用了范围查询,但是辅助索引树预计范围内匹配记录数量较少,那么再次扫描主键索引树获取全部数据的消耗时间不会很高,因此还是使用查询类型type是range的查询。此时索引生效。


    在这里插入图片描述

    辅助索引树匹配范围记录较多的情况?

    使用最左列进行范围查询, biz_no > 'bizno10000’记录趋近全表。

    explain select * from index_test where biz_no > 'bizno10000' ;
    

    输出结果如下图所示,联合索引的索引树预计范围内匹配的数据量比较大,再次扫描主键索引树获取全部数据的消耗时间可能较高,所以查询优化器优化决定使用全表扫描代替range查询。此时索引不生效。


    在这里插入图片描述

    覆盖索引

    覆盖索引下,减少了再次扫描主键索引树的过程,那么其实不管联合索引的索引树匹配多少条记录都是无所谓的,因此都是使用range查询,索引都会生效。

    例如匹配记录多的情况。

    explain select biz_no from index_test where biz_no > 'bizno10000' ;
    

    输出如下图所示,虽然预计扫描行数较多,但是还是使用range查询,索引生效,因为不需要再次扫描主键索引树,range查询联合索引树效率最高。Extra中的Using Index使用覆盖索引查询。


    在这里插入图片描述

    不用说,匹配记录少的情况同理也是一样。

    总结

    联合索引的索引生效原则如下:

    1. 所有列精确匹配索引一定生效
    2. 最左列精确匹配,右边列使用范围查询、非等查询、不使用右边列,索引一样生效。
    3. 最左列使用范围查询时:
      • 如果是覆盖查询,索引一律生效
      • 如果不是覆盖查询,当查询优化器预计范围匹配记录较大的时候,索引不生效;当查询优化器预计范围匹配记录较小的时候,索引生效。
    4. 如果最左列没使用精确或者范围查询,联合索引不可能生效。
    5. like 查询跟单列索引类似,也是最左匹配原则。
    展开全文
  • 在MySQL数据库中,对于索引的使用并是一直都采用正确的决定。简单表的示例:以下是代码片段: CREATE TABLE `r2` (ID` int(11) DEFAULT NULL,ID1` int(11) DEFAULT NULL,CNAME` varchar(32) DEFAUL...
  • MySQL 索引创建与优化

    2018-07-01 03:45:57
    我们将从索引基础开始,介绍什么是索引以及索引的几种类型,然后学习如何正确的创建并使用索引,让索引发挥它真正的作用,如何根据具体的应用场景选择合适的索引等。本场可能不会有太多的理论基础的介绍,而是会有...
  • 数据库索引

    2018-09-15 10:43:00
    创建并使用正确的索引【减少数据访问】 优点:加快检索速度、唯一索引保证每条数据唯一性等等,对生产系统的性能有质的提升; 缺点:索引会大大增加表记录的DML开销; 拓展:索引对DML(INSERT,UPDATE,DELETE)附加...
  • Oracle Index 索引介绍

    2011-10-27 10:45:05
    Oracle Index 索引介绍 索引在各种关系型数据库系统中都是举足轻重的组成部分,其对于提高检索数据的速度起至关重要的作用。在Oracle中,索引基本分为...而如果做出正确的选择,则可以合理使用资源,使那些已经...
  • Oracle Index 索引介绍 及 优化策略

    千次阅读 2010-11-15 15:20:00
    Oracle Index 索引介绍  索引在各种关系型...而如果做出正确的选择,则可以合理使用资源,使那些已经运行了几个小时甚至几天的进程在几分钟得以完成,这样会使您立刻成为一位英雄。这篇文章就将简单的
  • 【多选题】关于Python 程序格式框架的描述,正确的是( )【多选题】关于while循环的描述,正确的是()【判断题】对于大量列表的连接,extend( )方法比运算符+具有更高的效率。【单选题】以下代码输出的结果是?( ) i = sum...
  • 对于这样情况应当创建基于函数的索引错误的例子:正确的例子:4. 以下使用会使索引失效,应避免使用5.不要将空的变量值直接与比较运算符(符号)比较6.不要在 SQL 代码中使用双引号。7.将索引
  • 对于solr一些基础知识我这边就不做于百度重复事儿,该文档更倾向如何去处理实际工作问题思路,大致分为两块,1如何合理创建索引,2如何查询索引 先说说第一个问题:  Solr创建索引的方式有两种: 1使用...
  • 数组的索引从0开始。虽然我在这里没有说明,但是你一样可以轻易使用多维数组。 // 一个包含两个元素数组 $a[0] = "first"; $a[1] = "second"; $a[] = "third"; // 添加数组元素简单...
  • 对于excel表格操作,有以下几种常用操作:选择指定元素选择满足特定条件元素排序和数据运算行列删减和合并这些需求基于可以用标签或者坐标方式,正确指向相应行、列或者是某个区域。这种能力称为索引...
  • DTD是一种保证XML文档格式正确的有效方法,可以比较XML文档和DTD文件来看文档是否符合规范,元素和标签使用是否正确。一个DTD文档包含:元素的定义规则,元素间关系的定义规则,元素可使用的属性,可使用的实体或...
  • 使用百度语音进行用户语音识别,返回的字符串调用歌词搜索,而歌词搜索使用的是分词的方式进行的索引建立,因此字符串识别的准确率直接影响最后返回歌曲的正确性。 而百度语音是基于非特定样本
  • 什么是NoSQL数据库?

    2014-07-21 23:24:20
    然而NoSQL数据库则可省去这些麻烦(通常程序都是正确的),确实是方便快捷。 可以使用复杂的查询条件 跟key-value存储不同的是,面向文档的数据库可以通过复杂的查询条件来获取数据。虽然不具备事务处理和JOIN这些...
  • 对于百度SEO,要查询,获取和分析大量数据,包括关键词扩展和排名,页面索引,网站访问量等等。可以帮助我们获取和跟踪此数据。而且工具越准确,洞察力和信息就越有效,这将帮助您更好地为您的业务制定正确的百度SEO...
  • 它为MySQL指明了正确的方向。唯一键还用于提高搜索速度,但是它具有以下约束:不能有重复项(不存在两个x和y,其中x不是y且x == y)。该手册对它的解释如下:UNIQUE索引会创建约束,以使索引中的所有值都必须不同。...
  • 以下内容总结自丁奇老师的MySQL45讲】MyISAM引擎直接记录了表的行数,所以会很快。但是InnoDB不同。不存在这个字段,InnoDB 是索引组织表,主键索引树的叶子节点是数据,而普通索引树的...在保证逻辑正确的前提...
  • 物化视图有很多方面和索引很相似:使用物化视图目的是为了提高查询性能;物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化...
  • 书中涉及内容非常广泛,包括DBMS概念、术语和体系结构,ER模型和ER图,数据抽象和语义数据建模,UML类图表示法,基本关系模型,关系代数和关系演算,SQL,规范化,磁盘上组织记录文件主要方法,文件的索引技术...
  • LINGO软件学习

    2009-08-08 22:36:50
    因此,派生集的索引个数是最终原始父集个数,索引的取值是从原始父集到当前派生集所作限制总和。 总来说,LINGO可识别集只有两种类型:原始集和派生集。 在一个模型中,原始集是基本对象,不能再被拆分成...
  • 长期以来,传统GIS软件很难将人们积累对地理要素研究成果直接在计算机中表达出来,而采用面向对象数据模型则可以对地理空间进行多层次分解,选择更加基本操作单元,从而更全面、正确地描述现实世界。...
  • 如果该值为false,说明程序已经处于不正确的状态下,系统将给出警告或退出。一般来说,assertion用于保证程序最基本、关键的正确性。assertion检查通常在开发和测试时开启。为了提高性能,在软件发布后,assertion...
  • 1、在Linux上,对于多进程,子进程继承了父进程的下列哪些?(共享内存、信号掩码、已打开的文件描述符) 2、java语言中,在同一包下,以下说法正确的有() ...3、对数据库,关于索引的理解正确的是(...
  • 1.3 开发数据库应用的正确(和不正确)方法 10 1.3.1 了解Oracle体系结构 11 1.3.2 理解并发控制 19 1.3.3 多版本控制 22 1.3.4 数据库独立性 28 1.3.5 怎么能让应用运行得更快 42 1.3.6 DBA与开发人员关系...
  • 遗憾的是,由于C/C++ 语言的编译特性,Visual C++中并没有提供类似的可以替代的现成的数据类型,于是我们只能自己想办法去构造这样一个虚类(或者结构体),这里我们使用标识 Matrix 命名其类型。这个自己构造的 ...
  • 知道怎么样是正确的打patch,先计划打哪个patch,然后取得patch,接着打patch,测试,最后文档记录。 6. 要知道任何时刻数据库都可能会有一些object 是invalid的,你的一些操作也会增加invalid objects,定期检查...

空空如也

空空如也

1 2 3 4 5
收藏数 90
精华内容 36
关键字:

对于索引以下正确的是