精华内容
下载资源
问答
  • MySQL索引失效的几种情况汇总

    千次阅读 2021-01-18 20:19:54
    索引不能存储Null,所以对这采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描。为什么索引列不能存Null值?将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于...

    一.索引不存储null值

    更准确的说,单列索引不存储null值,复合索引不存储全为null的值。索引不能存储Null,所以对这列采用is null条件时,因为索引上根本

    没Null值,不能利用到索引,只能全表扫描。

    为什么索引列不能存Null值?

    将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。

    这样的话,null值实际上是不能参与进建索引的过程。也就是说,null值不会像其他取值一样出现在索引树的叶子节点上。

    二.不适合键值较少的列(重复数据较多的列)

    假如索引列TYPE有5个键值,如果有1万条数据,那么 WHERE TYPE = 1将访问表中的2000个数据块。

    再加上访问索引块,一共要访问大于200个的数据块。

    如果全表扫描,假设10条数据一个数据块,那么只需访问1000个数据块,既然全表扫描访问的数据块少一些,肯定就不会利用索引了。

    三.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

    假如有这样一列code的值为'AAA','AAB','BAA','BAB' ,如果where code like '%AB'条件,由于前面是

    模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫

    描。如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的

    数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。

    四.索引失效的几种情况

    1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

    要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

    select * from tb1 where nid = 1 or name = 'seven@live.com';

    --特别的:当or条件中有未建立索引的列才失效,以下会走索引

    select * from tb1 where nid = 1 or name = 'seven';

    select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

    2.对于多列索引,不是使用的第一部分,则不会使用索引

    3.like查询以%开头

    select * from tb1 where email like '%cn';

    4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

    elect * from tb1 where email = 999;

    5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

    6.普通索引的不等于不会走索引

    - !=

    select * from tb1 where email != 'alex'

    --特别的:如果是主键,则还是会走索引

    select * from tb1 where nid != 123

    - >

    select * from tb1 where email > 'alex'

    --特别的:如果是主键或索引是整数类型,则还是会走索引

    select * from tb1 where nid > 123

    select * from tb1 where num > 123

    7.组合索引最左前缀

    如果组合索引为:(name,email)

    name and email -- 使用索引

    name -- 使用索引

    email -- 不使用索引

    以上就是MySQL索引失效的几种情况汇总的详细内容,更多关于MySQL索引失效的资料请关注脚本之家其它相关文章!

    展开全文
  • 首先建议无论索引列还是其他,都设置成非 null,通过默认值解决 null 值问题。 如果把索引列一些值设置为 null,是允许的,但是写sql时候就要用到 is null 和 is not null来进行筛选数据,但这个时候是否也走索引...

    首先建议无论索引列还是其他列,都设置成非 null,通过默认值解决 null 值问题。

    如果把索引列一些值设置为 null,是允许的,但是写sql时候就要用到 is null 和 is not null来进行筛选数据,但这个时候是否也走索引呢? 让我们来验证下

    创建表

    CREATE TABLE `a` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) DEFAULT NULL,
      `dd` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`)
    ) 

    插入数据

    insert into a(name) select null
    多次执行下面语句,将数据量达到十几万级别(数据太少不走索引)
    insert into a(name)
    select  uuid_short()  from a

    执行sql1

    SELECT * FROM a
    where name='99293305182879751';

    执行sql2

    SELECT * FROM a
    where name is null;

     

    通过两个执行计划我们看到都是可以正常走索引的,在mysql5.7 innodb引擎下,索引字段有null值也不会影响索引的正常使用

     

     

     

     

     

    展开全文
  • # 索引不会包含有NULL值的只要中包含有NULL值都将不会被包含在索引中,复合索引中只要含有NULL值,那么这一对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。在很多库表设计...

    # 索引不会包含有NULL值的列

    只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

    在很多库表设计规范、某某军规的文章中,是不是经常会看到类似这样的内容。小编也经常看到这样的内容,并且在编写规范的时候,准备也把这一条加进去。但在按部就班之余,小编抽空验证了一下,发现事实却并非如此!

    小编使用的MySQL版本是社区版 5.7.21

    新建测试表 t1,插入不含NULL值得100行数据,然后插入1行带NULL的数据 insert into t1(id) values(101); 表中有主键id,索引a

    CREATE TABLE`t1` (

    `id`int(11) NOT NULL,

    `a`int(11) DEFAULT NULL,

    `b`int(11) DEFAULT NULL,PRIMARY KEY(`id`),KEY`a` (`a`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    测试1,包含NULL单列索引的查询,可以看到即使是查找 IS NULL的行,也是可以用上索引的

    测试1:

    desc select * from t1 where a > 82;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 18 | 100.00 | Using index condition |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+

    desc select * from t1 where a is NULL;+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

    | 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | const | 1 | 100.00 | Using index condition |

    +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

    desc select * from t1 where a = 20 or a is null;+----+-------------+-------+------------+-------------+---------------+--------+---------+-------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------------+---------------+--------+---------+-------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref_or_null | idx_ab | idx_ab | 5 | const | 2 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+-------------+---------------+--------+---------+-------+------+----------+--------------------------+

    注意对 NULL 值的检索只能使用 is null / is not null / <=>,不能使用=,这样的运算符(mysql中可以用a <=> NULL 表示查找 a is NULL'的行)

    测试2,包含NULL复合索引的查询,首先加一个复合索引 alter table t1 drop index a,add index idx_ab(a,b); 可以看到不管是指定 a is null ,或者指定 b is null ,都可以利用上索引 idx_ab(key_len 可以看出)

    测试2:

    desc select * from t1 where a=50 and b>20;+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | range | idx_ab | idx_ab | 10 | NULL | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    desc select * from t1 where a=50 and b is null;+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_ab | idx_ab | 10 | const,const | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    desc select * from t1 where a is null and b>20;+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | range | idx_ab | idx_ab | 10 | NULL | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+

    desc select * from t1 where a is null and b is null;+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    | 1 | SIMPLE | t1 | NULL | ref | idx_ab | idx_ab | 10 | const,const | 1 | 100.00 | Using where; Using index |

    +----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+--------------------------+

    由此,只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。这句的前半句是不对的(可参考官网说明: https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html),但是后半句的结论确是可以采纳的。

    虽然MySQL可以在含有null的列上使用索引,但不代表null和其他数据在索引中是一样的。不建议列上允许为空,最好限制 not null ,并设置一个默认值,比如0和''空字符串等,如果是datetime类型,可以设置成'1970-01-01 00:00:00'这样的值。对MySQL来说,null 是一个特殊的值,Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。 对null做算术运算的结果都是null,count时不会包括null行,null 比空字符串需要更多的存储空间等。

    附:上面说到可用通过 key_len 看出使用了索引列的个数,a,b 都是 int 类型,4 byte,为什么 key_len 是 5 byte 和 10 byte 呢?是因为如果索引列定义时允许NULL,其key_len还需要再加 1 bytes.参考好友王的文章,可以移步我们的站点查看详情: http://www.fordba.com/spend-10-min-to-understand-how-mysql-use-index.html

    展开全文
  • 一.首先看一个我在某公众号看到的一个关于数据库优化的举措二....先出结论:where子句中使用上述对null的判断,如果判断的设置了索引,那就可以使用到索引三.测试:1.建表1 CREATE TABLE`test_null_index` (2 `i...

    一.首先看一个我在某公众号看到的一个关于数据库优化的举措

    b3079a31d889b3c20f1ddbb92c2c8947.png

    二.如果where子句中查询的列执行了 “is null” 或者 “is not null” 或者 “<=> null” 会不会使用索引呢?

    先列出结论:where子句中使用上述对null的判断,如果判断的列设置了索引,那就可以使用到索引

    三.测试:

    1.建表

    1 CREATE TABLE`test_null_index` (2 `id` int(11) DEFAULT NULL,3 `mark` varchar(20) DEFAULT NULL,4 `name` varchar(11) DEFAULT NULL

    5 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    2.插数据

    create procedure test_null_index(in num int)BEGIN

    DECLARE i int;set i=1;while (i

    DOif mod(i,10)!=0 then

    insert into test_null_index values (i,concat('aaa',i),null);else

    insert into test_null_index values (null,concat('aaa',i),'bbb');end if;set i=i+1;END while;END;

    call test_null_index(10000);

    3.没加索引时,type为All,全表扫描

    mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

    | 1 | SIMPLE | test_null_index | ALL | NULL | NULL | NULL | NULL | 10589 | Using where |

    +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+

    1 row in set (0.00 sec)

    4.加上索引,可以看到,索引起作用了

    mysql> create index idx_test_null ontest_null_index(id);

    Query OK,0 rows affected (0.12sec)

    Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE id is null;+----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

    | 1 | SIMPLE | test_null_index | ref | idx_test_null | idx_test_null | 5 | const | 998 | Using where |

    +----+-------------+-----------------+------+---------------+---------------+---------+-------+------+-------------+

    1 row in set (0.00 sec)

    四.注意,只能使用针对一个字段的关于null的判断,如果同时在两个字段对null进行判断,还是会走全表扫描

    1.测试,可以看到,在name字段加上索引,并查询name为空的语句,同样会走索引

    mysql> create index idx_test_null2 ontest_null_index(name);

    Query OK,0 rows affected (0.13sec)

    Records:0 Duplicates: 0 Warnings: 0mysql> explain SELECT * from test_null_index WHERE name is null;+----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

    | 1 | SIMPLE | test_null_index | ref | idx_test_null2 | idx_test_null2 | 36 | const | 8994 | Using where |

    +----+-------------+-----------------+------+----------------+----------------+---------+-------+------+-------------+

    1 row in set (0.00 sec)

    2.同时针对id和name查询为空的语句,走的是全表扫描

    mysql> explain SELECT * from test_null_index WHERE id is null or name is null;+----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

    +----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

    | 1 | SIMPLE | test_null_index | ALL | idx_test_null,idx_test_null2 | NULL | NULL | NULL | 10589 | Using where |

    +----+-------------+-----------------+------+------------------------------+------+---------+------+-------+-------------+

    1 row in set (0.01 sec)

    展开全文
  • mysql 唯一索引null.md

    2021-02-01 21:16:35
    mysql 的唯一索引要求所有参与的列都不能够为 null 值,如果唯一索引中的任何一个元素含有 null 值,则唯一约束将不起作用。示例代码create table tb (a int,b int,c int,unique index (a,b,c));insert into tb(a,b,...
  • 但是字段不是每条数据都值,所以要设置默认值为null。 字段设置为null索引就无效了(百度都这么说) 实际用explain测试,也确实没提示有索引 但是实际检索发现,肯定是有索引,一个百万级数据的表,用这个唯一...
  • 这种说法是错的:”MySql 中是不能用 null索引的,任何包含null值的列都将不会被包含在索引中“我们做个实验如下,可以看到b字段建有索引,b 字段中 null,但是select count(*) from a group by b用了b上的覆盖...
  • 除非真的要保存 NULL,否则就把定义为 NOT NULLMySQL难以优化引用了可空的查询,它会使索引索引统计和值更加复杂。可空需要更多的储存空间,还需要在MySQL内部进行特殊处理。当可空索引的时候,每条记录...
  • MySQLNULL和空值的区别,索引列是否可空值或null发布时间:2018-07-25 18:02,浏览次数:298, 标签:MySQLNULL在创建表的时候尽量把字段的默认值设置成 notnull,除非你想存储null;因为在mysql中为null的的字段...
  • mysql唯一索引null

    2021-06-09 13:54:30
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=6 ...
  • 今天在测试过程中发现数据重复的现象,想设计一个唯一索引达到幂等的效果,但是发现设置了唯一索引以后还是可以插入相同的数据,经过排查,发现联合唯一索引中含有null值会导致索引失效。 二.问题复现 现在我们来...
  • 索引不能存储Null,所以对这采用is null条件时,因为索引上根本没Null值,不能利用到索引,只能全表扫描。为什么索引列不能存Null值?将索引列值进行建树,其中必然涉及到诸多的比较操作。Null值的特殊性就在于...
  • 创建一个使用列索引的表:create table index_test1(id int not null auto_increment,first_name varchar(30) not null,last_name varchar(30) not null,primary key(id),index index_first(first_name),index index...
  • 关于mysql优化部分,很多网友说尽量避免使用is null, is not null,select * 等,会导致索引失效,性能降低?那是否一定收到影响呢?真的就不会使用索引了吗?本文的测试数据库版本为5.7.18,不同版本得出的结果...
  • 组合索引(concatenated index):由多个构成的索引...在组合索引一个重要的概念:引导(leading column),在上面的例子中,col1列为引导。当我们进行查询时可以使用”where col1 = ? ”,也可以使用”where ...
  • MySQL 多字段组合唯一索引中,字段可能会出现空值业务场景:**用户行为记录表,**防止用户数据重复插入表中。也防止尴尬。。。直接上SQL,已优化CREATE TABLE `user_behavior` (`behavior_id` bigint(20) NOT ...
  • MySQL的多列索引

    2021-01-18 21:54:16
    索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的...
  • 测试环境:数据库:MySQL5.7.25数据库引擎:InnoDB连接工具:Navicat Premium首先,创建数据库,并创建测试表 test:DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (`id` int(11) NOT NULL COMMENT 'ID',`name` ...
  • 转自:http://blog.chinaunix.net/uid-29305839-id-4257512.html创建一个多列索引:CREATE TABLE test (id INT NOT NULL,last_name CHAR(30) NOT NULL,first_name CHAR(30) NOT NULL,PRIMARY KEY (id),INDEX name ...
  • create table TEST(col_1 INT,col_2 INT,col_3 VARCHAR(128) not null,col_4 VARCHAR(128) not null,UNIQUE KEY (col_1, col_2, col_3),CONSTRAINT FOREIGN KEY (col_1) REFERENCES T2(ID) ON DELETE CASCAD...
  • mysql索引可以分为单列索引和多列索引,单列索引,请参考:mysql可以为多个创建一个索引,最多可以16,多列索引可以视为包含通过连接索引列的值而创建值的排序数组。mysql列索引适用场合:当你在where子句...
  • 我已经做了一堆与多列索引相关的阅读,没有找到我的问题的答案.我很好奇,如果我做一个多列索引,如果它们的指定是非常重要的.我的猜测是,它不会,引擎会将他们视为一个团体,在那里订购并不重要.但我想验证.CREATE TABLE...
  • 〇、MySQL索引哪些分类?按数据结构分类可分为:B+tree索引、Hash索引、Full-text索引。 按物理存储分类可分为:聚簇索引、二级索引(辅助索引)。 按字段特性分类可分为:主键索引、普通索引、前缀索引。 按字段...
  • Mysql索引

    2021-01-27 19:34:08
    索引优点索引看着挺高大上的一个名字,说白了就是我们书最新面的目录。假如你用新华字典来查找“张”这个汉字,不使用目录的话,你可能要从新华字典的第一页找到...索引用于快速找出在某个一特定值的行。不...
  • 好多情况下数据库默认值都有null,但是经过程序处理很多时候会出现,数据库值为空而不是null的情况。此时创建唯一索引时要注意了,此时数据库会把空作为多个重复值,而创建索引失败,示例如下:步骤1:mysql> ...
  • 看面试题的时候,总能看到MySQL在什么状况下用不上索引,以下:mysqlMySQL的WHERE子句中包含 IS NULL、IS NOT NULL、!= 这些条件时便不能使用索引查询,只能使用全表扫描。面试不耽误你们时间,告诉你们结论:MySQL...
  • MySQL 5.6.30问题背景由于爬虫抓取的数据不断增多,这两天在不断对数据库以及查询语句进行优化,其中一个表结构如下:CREATE TABLE `newspaper_article` (`id` varchar(50) NOT NULL COMMENT '编号',`title` varchar...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 217,380
精华内容 86,952
关键字:

mysql索引列有null

mysql 订阅