关于MySQL索引的几点值得注意的事项

在数据库中，对性能影响最大的包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略。

索引策略决定数据库快速定位数据的效率，存储策略决定数据持久化的效率。

1.索引不存储null值。

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

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

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

将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。这样的话，null值实际上是不能

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

2.不适合键值较少的列（重复数据较多的列）。

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

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

如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块

少一些，肯定就不会利用索引了。

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

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

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

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

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

4.MySQL主要提供2种方式的索引：B-Tree索引，Hash索引。

B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。

哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。

显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。

如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略，本文将分析它们的异同和性能。

赞 (0)

相关推荐

  • Mysql建表与索引使用规范详解

    一. MySQL建表,字段需设置为非空,需设置字段默认值.二. MySQL建表,字段需NULL时,需设置字段默认值,默认值不为NULL.三. MySQL建表,如果字段等价于外键,应在该字段加索引.四. MySQL建表,不同表之间的相同属性值的字段,列类型,类型长度,是否非空,是否默认值,需保持一致,否则无法正确使用索引进行关联对比.五. MySQL使用时,一条SQL语句只能使用一个表的一个索引.所有的字段类型都可以索引,多列索引的属性最多15个.六. 如果可以在多个索引中进行选择,MySQL通常

  • MySQL 主键与索引的联系与区别分析

    关系数据库依赖于主键,它是数据库物理模式的基石.主键在物理层面上只有两个用途: 惟一地标识一行. 作为一个可以被外键有效引用的对象. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针.下面是主键和索引的一些区别与联系. 1. 主键一定是唯一性索引,唯一性索引并不一定就是主键. 所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引.因为主键可以唯一标识某一行记录,所以可以确保执行数据更新.删除的

  • MySQL批量插入遇上唯一索引避免方法

    MySQL批量插入遇上唯一索引避免方法

    一.背景 以前使用SQL Server进行表分区的时候就碰到很多关于唯一索引的问题:Step8:SQL Server 当表分区遇上唯一约束,没想到在MySQL的分区中一样会遇到这样的问题:MySQL表分区实战. 今天我们来了解MySQL唯一索引的一些知识:包括如何创建,如何批量插入,还有一些技巧上SQL: 这些问题的根源在什么地方?有什么共同点?MySQL中也有分区对齐的概念?唯一索引是在很多系统中都会出现的要求,有什么办法可以避免?它对性能的影响有多大? 二.过程 (一) 导入差异数据,忽略重

  • MySQL 创建索引(Create Index)的方法和语法结构及例子

    CREATE INDEX Syntax CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_type] index_col_name: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH | RTREE} 复制代码 代码如下: -- 创建无索引的表格 create t

  • Mysql索引会失效的几种情况分析

    Mysql索引会失效的几种情况分析

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因) 注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引 2.对于多列索引,不是使用的第一部分,则不会使用索引 3.like查询是以%开头 4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引 5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查看索引的使用情况show status li

  • mysql占用CPU过高的解决办法(添加索引)

    下面是MYSQL占用CPU高处理的一个例子,希望对遇到类似问题的朋友们有点启发.一般来说MYQL占用CPU高,多半是数据库查询代码问题,查询数据库过多.所以一方面要精简代码,另一方面最好对频繁使用的代码设置索引. 今天早上起来 机器报警 一查负载一直都在4以上 top了一下 发现 mysql 稳居 第一 而且相当稳定 我擦 重启一下mysql不行 mysql> show processlist;一下 发现xxx网站有两条 查询语句 一直 在列,我擦 该站 也就30多万条记录 量也不大 不可能是机

  • 基于mysql全文索引的深入理解

    基于mysql全文索引的深入理解

    前言:本文简单讲述全文索引的应用实例,MYSQL演示版本5.5.24. Q:全文索引适用于什么场合? A:全文索引是目前实现大数据搜索的关键技术. 至于更详细的介绍请自行百度,本文不再阐述. -------------------------------------------------------------------------------- 一.如何设置? 如图点击结尾处的{全文搜索}即可设置全文索引,不同MYSQL版本名字可能不同. 二.设置条件 1.表的存储引擎是MyISAM,默认

  • mysql 表索引的一些要点

    1.表的主键.外键必须有索引: 2.数据量超过300的表应该有索引: 3.经常与其他表进行连接的表,在连接字段上应该建立索引: 4.经常出现在Where子句中的字段,特别是大表的字段,应该建立索引: 5.索引应该建在选择性高的字段上: 6.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引: 7.复合索引的建立需要进行仔细分析:尽量考虑用单字段索引代替: A.正确选择复合索引中的主列字段,一般是选择性较好的字段:       B.复合索引的几个字段是否经常同时以AND方式出现在Whe

  • mysql 添加索引 mysql 如何创建索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • 关于MySQL索引的几点值得注意的事项

    在数据库中,对性能影响最大的包括数据库的锁策略.缓存策略.索引策略.存储策略.执行计划优化策略. 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率. 1.索引不存储null值. 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运

  • MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项

    以下的文章主要介绍的是MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项是值得我们大家注意的,我们大家可能不知道过多的对索引进行使用将会造成滥用.因此MySQL索引也会有它的缺点: 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT.UPDATE和DELETE.因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件. 建立索引会占用磁盘空间的索引文件.一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快. 索引只是提高效

  • 关于MySQL索引的深入解析

    前言 我们知道,索引的选择是优化器阶段的工作,但是优化器并不是万能的,它有可能选错所要使用的索引.一般优化器选择索引考虑的因素有:扫描行数,是否排序,是否使用临时表. 使用explain分析sql explain是很好的自测命令,勤于使用explain有助于我们写出更合理的sql语句以及建立更合理的索引: mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) ord

  • MySQL 索引和数据表该如何维护

    MySQL 索引和数据表该如何维护

    查找和修复数据表冲突 数据表最糟糕的事情就是发生冲突.使用MyISAM存储引擎时,通常因为崩溃导致冲突.然而,当存在硬件故障.MySQL内部Bug或操作系统Bug时,所有的存储引擎都可能遭受索引冲突. 冲突的索引可能导致查询返回错误的结果,在没有重复值时的重复索引错误增加,甚至可能导致全表扫描或崩溃.如果你遇到过偶发的事件,例如一个你认为不会发生的错误,这个时候运行CHECK TABLE命令去检测数据表是否有冲突(注意有些数据库引擎不支持这个命令,有些则支持多种选项参数去指定如何检测表).通常,

  • 一文弄懂MySQL索引创建原则

    一文弄懂MySQL索引创建原则

    目录 一.适合创建索引 1.字段的数值有唯一性限制 2.频繁作为Where查询条件的字段 3.经常Groupby和Orderby的列 4.Update.Delete的where条件列 5.Distinct字段需要创建索引 6.多表Join连接操作时,创建索引注意事项 7.使用列的类型小的创建索引 8.使用字符串前缀创建索引 9.区分度高的列适合作为索引 10.使用最频繁的列放到联合索引的左侧 11.在多个字段都要创建索引的情况下,联合索引由于单值索引 二.不适合创建索引 1.在where中使用不

  • MySQL索引与事务定义到使用详解

    目录 1.索引的本质 2.索引的使用 2.1查看索引 2.2创建索引 2.3删除索引 3.索引的数据结构 3.1B树 3.2B+树 4.事务 4.1事物的回滚(rollback) 4.2事务的四大特性(ACID) 4.2.1 原子性 4.2.2 一致性 4.2.3 持久性 4.2.4 隔离性 5.并发引起的问题 5.1 "读脏数据" 5.2 "不可重复读" 5.3 "幻读" 6.MySQL的隔离级别 6.1 read uncommitted 6.

  • 深入理解 MySQL 索引底层原理

    目录 Mysql 索引底层数据结构选型 哈希表(Hash) 二叉查找树(BST) AVL 树和红黑树 B 树 5.B+树 Innodb 引擎和 Myisam 引擎的实现 MyISAM 引擎的底层实现(非聚集索引方式) Innodb 引擎的底层实现(聚集索引方式) 一步一步推导出 Mysql 索引的底层数据结构. Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及索引的设计,决定了 Mysql 整体的数据检索性能. 我们知

  • 详解mysql索引总结----mysql索引类型以及创建

    关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车.对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢.还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引. 一个简单的对比测试 以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

  • 浅析mysql索引

    数据库索引是一种数据结构,目的是提高表的操作速度.可以使用一个或多个列,提供快速随机查找和访问记录的高效排序来创建索引. 要创建的索引,应当认为哪列将用于使SQL查询,创建对这些列的一个或多个索引. 实际上,索引也是表,其中保存主键或索引字段的指针并指向每个记录到实际的表的类型. 用户无法看到索引,它们只是用来加速查询,并将被用于数据库搜索引擎在查找记录时提高速度. INSERT和UPDATE语句需要更多的时间来创建索引,作为在SELECT语句快速在这些表上操作.其原因是,在执行插入或更新数据时

随机推荐