1.索引概述索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)它们包含着对数据表里所有记录的引用指针。索引是一种数据结构。数据库索引是数据库管理系统中一个排序的数据结构以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B树。更通俗的说索引就相当于目录。为了方便查找书中的内容通过对内容建立索引形成目录。索引是一个文件它是要占据物理空间的。、、例如数据库里面有20000条记录现在要执行这么一个查询SELECT * FROM table where num 10000。如果没有索引必须遍历整个表直到num等于10000的这一行被找到为止如果在num列上创建索引MySQL不需要任何扫描直接在索引中找10000就可以得知值这一行的位置。可见索引的建立可以提高数据库的查询速度。2.优缺点2.1 优点通过创建唯一索引可以保证数据库表中每一行数据的唯一性可以大大加快数据的查询速度这也是创建索引最主要的原因在实现数据的参考完整性方面可以加速表和表之间的连接在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间2.2 缺点时间上创建索引和维护索引要耗费时间具体地当对表中的数据进行增加、删除和修改的时候索引也要动态的维护会降低增/改/删的执行效率空间上索引需要占物理空间3.索引分类可分为单列索引普通索引唯一索引主键索引组合索引全文索引空间索引主键索引: 数据列不允许重复不允许为NULL一个表只能有一个主键。唯一索引: 数据列不允许重复允许为NULL值允许多个null值一个表允许多个列创建唯一索引。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引普通索引: 基本的索引类型没有唯一性的限制允许为NULL值。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引全文索引 是目前搜索引擎使用的一种关键技术。全文索引类型为FULLTEXT在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。在5.6之前MySQL中只有MyISAM存储引擎支持全文索引5.6之后MyISAM和InnoDB支持可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引4.索引的设计原则索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍高效的索引对于获得良好的性能非常重要设计索引时应该考虑一下索引并非越多越好一个表中如有大量的索引不仅占用磁盘空间而且会影响INSERT、DELETE、UPDATE等语句的性能因为当表中的数据更改的同时索引也会进行调整和更新Mysql8中原话// 二级索引非聚集索引// 一个表最多可以包含64个二级索引。//多列索引最多允许有16列。// 超过限制将返回错误。//错误1070(42000):指定的关键部件太多;最多允许16个零件Atable can contain a maximum of64secondaryindexes.Amaximum of16columns is permittedformulticolumnindexes.Exceedingthe limit returns anerror.ERROR1070(42000):Toomany key parts specified;max16parts allowed避免对经常更新的表设计过多的索引并且索引中的列尽可能要少而对经常用于查询的字段应该创建索引但要避免添加不必要的字段频繁更新的列不要设计索引数据量小的表最好不要使用索引由于数据较少查询花费的时间可能比遍历索引时间还要短索引可能不会产生优化效果若是不能有效区分数据的列不适合做索引列(如性别男女未知最多也就三种区分度实在太低)当唯一性是某种数据本身的特征时指定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度在频繁排序或分组即group by或order by操作的列上建立索引如果待排序的列有多个可以在这些列上建立组合索引定义有外键的数据列一定要建立索引最左前缀匹配原则组合索引非常重要的原则mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。对于定义为text、image和bit的数据类型的列不要建立索引。查询的内容尽量匹配索引避免回表问题当没有给表创建索引的时候如果有主键会创建聚簇索引如果没有主键会生成rowid作为隐式主键5.索引的数据结构b树hash索引的数据结构和具体存储引擎的实现有关在MySQL中使用较多的索引有Hash索引B树索引等而我们经常使用的InnoDB存储引擎的默认索引实现为B树索引。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择BTree索引。5.1 索引实现原理为什么使用B树作为索引的存储结构5.1.1 目录到索引的演变过程假设有一个表index_demo表中有2个INT类型的列1个CHAR(1)类型的列c1列为主键CREATETABLEindex_demo(c1INT,c2INT,c3CHAR(1),PRIMARYKEY(c1));index_demo表的简化的行格式示意图如下我们只在示意图里展示记录的这几个部分record_type表示记录的类型 0是普通记录、 2是最小记录、 3 是最大记录、1是B树非叶子节点记录。next_record表示下一条记录的相对位置我们用箭头来表明下一条记录。各个列的值这里只记录在 index_demo 表中的三个列分别是 c1 、 c2 和 c3 。其他信息除了上述3种信息以外的所有信息包括其他隐藏列的值以及记录的额外信息。将其他信息项暂时去掉并把它竖起来的效果就是这样把一些记录放到页里的示意图就是这里一页就是一个磁盘块代表一次IOMySQL InnoDB的默认的页大小是16KB因此数据存储在磁盘中可能会占用多个数据页。如果各个页中的记录没有规律我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中我们可以这样做 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值给所有的页建立目录项以页28为例它对应目录项2这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储比如数组就可以实现根据主键值快速查找某条记录的功能了。比如查找主键值为 20 的记录具体查找过程分两步先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中因为 12 ≤ 20 209 对应页9。再到页9中根据二分法快速定位到主键值为 20 的用户记录。至此针对数据页做的简易目录就搞定了。这个目录有一个别名称为索引。5.1.2 目录结构到B树结构的演变过程我们新分配一个编号为30的页来专门存储目录项记录页10、28、9、20专门存储用户记录目录项记录和普通的用户记录的不同点目录项记录 的 record_type 值是1而 普通用户记录 的 record_type 值是0。目录项记录只有主键值和页的编号两个列而普通的用户记录的列是用户自己定义的包含很多列另外还有InnoDB自己添加的隐藏列。现在查找主键值为 20 的记录具体查找过程分两步先到页30中通过二分法快速定位到对应目录项因为 12 ≤ 20 209 就是页9。再到页9中根据二分法快速定位到主键值为 20 的用户记录。更复杂的情况如下我们生成了一个存储更高级目录项的 页33 这个页中的两条记录分别代表页30和页32如果用户记录的主键值在[1, 320)之间则到页30中查找更详细的目录项记录如果主键值 不小于320 的话就到页32中查找更详细的目录项记录。这个数据结构它的名称是 B树 。5.2 B树实现索引关于B树和B树详细可以看数据结构-B树和B树非叶节点只存放索引不存放数据n棵子tree的节点包含n个关键字所有的叶子节点通过一个有序链表构成了全部数据及指向这些数据的指针可以按照关键码排序的次序遍历全部数据5.3 Hash表实现索引简要说下类似于数据结构中简单实现的HASH表散列表一样当我们在mysql中用哈希索引时主要就是通过Hash算法常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法将数据库字段数据转换成定长的Hash值与这条数据的行指针一并存入Hash表的对应位置如果发生Hash碰撞两个不同关键字的Hash值相同则在对应Hash键下以链表形式存储。当然这只是简略模拟图。5.4 Hash表相对于B树的优劣hash索引底层就是hash表进行查找时调用一次hash函数就可以获取到相应的键值之后进行回表查询获得实际数据。B树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据。5.3.1 Hash表的优点正常情况下Hash表的等值查询更快。如果存在大量重复键值就存在Hash碰撞的问题。就要先找到键所在位置然后再根据链表往后扫描直到找到相应的数据5.3.2 缺点因为在hash索引中经过hash函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询。而B树的的所有节点皆遵循(左节点小于父节点右节点大于父节点多叉树也类似)天然支持范围。hash索引不支持使用索引进行排序原理同上。hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。hash索引任何时候都避免不了回表查询数据而B树在符合某些条件(聚簇索引覆盖索引等)的时候可以只通过索引完成查询。hash索引虽然在等值查询上较快但是不稳定。性能不可预测当某个键值存在大量重复的时候发生hash碰撞此时效率可能极差。而B树的查询效率比较稳定对于所有的查询都是从根节点到叶子节点且树的高度较低。5.4 为什么使用B树而不用B树B和B树的区别在于B树的非叶子结点只包含导航信息不包含实际的值所有的叶子结点和相连的节点使用链表相连便于区间查找和遍历。5.4.1 B 树的优点在于读写代价低和磁盘IO效率有关一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。这样的话索引查找过程中就要产生磁盘I/O消耗。B树的内部结点并没有指向关键字具体信息的指针只是作为索引使用其内部结点比B树小盘块能容纳的结点中关键字数量更多一次性读入内存中可以查找的关键字也就越多相对的IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素查询效率稳定由于B树在内部节点上不包含数据信息因此在内存页中能够存放更多的key。 数据存放的更加紧密具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。B树支持顺序查询B树的叶子结点都是相链的因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历相邻的元素可能在内存中不相邻所以缓存命中性没有B树好。增删效率好5.4.2 B树优点由于B树的每一个节点都包含key和value因此经常访问的元素可能离根节点更近因此访问也更迅速。下面是B 树和B树的区别图5.5 B树的存储量级真实环境中一个页存放的记录数量是非常大的默认16KB假设指针与键值忽略不计或看做10个字节数据占 1 kb 的空间如果B树只有1层也就是只有1个用于存放用户记录的节点最多能存放 16 条记录。如果B树有2层最多能存放1600×1625600条记录。如果B树有3层最多能存放1600×1600×1640960000条记录。如果存储千万级别的数据只需要三层就够了B树的非叶子节点不存储用户记录只存储目录记录相对B树每个节点可以存储更多的记录树的高度会更矮胖IO次数也会更少。6. 聚簇索引和非聚簇索引6.1 聚簇索引innodb可以把主键索引理解成聚簇索引。一张表中如果没有指定某列是聚簇索引那么该表的第一个唯一非空索引被作为聚集索引如果也没有索引那么系统会自动创建一个隐含列作为表的聚集索引 这个字段长度为6个字节类型为长整型。一个表只能有一个聚集索引因为聚集索引把表的数据格式转换成索引树平衡树的格式放置。所以一个表只能有一个主键。树中的节点除底部节点的数据是由主键及数据地址构成。时间复杂度Olog n(n代表记录数)页内的记录是按照主键的大小顺序排成一个单向链表。页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表。非叶子节点存储的是记录的主键页号。叶子节点存储的是完整的用户记录。查找的时候先通过主键找到所在的叶节点而这个叶节点叶包含了此行的所有数据信息innodb直接存储数据信息myisam存储着数据的指向来源通过DBCC PAGE查看页信息验证聚集索引和非聚集索引节点信息6.1.1 优点数据访问更快 因为索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快。聚簇索引对于主键的排序查找和范围查找速度非常快。按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库可以从更少的数据块中提取数据节省了大量的IO操作。6.1.2 缺点插入速度严重依赖于插入顺序 按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键。更新主键的代价很高 因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。6.1.3 限制只有InnoDB引擎支持聚簇索引MyISAM不支持聚簇索引。由于数据的物理存储排序方式只能有一种所以每个MySQL的表只能有一个聚簇索引。如果没有为表定义主键InnoDB会选择非空的唯一索引列代替。如果没有这样的列InnoDB会隐式的定义一个主键作为聚簇索引。为了充分利用聚簇索引的聚簇特性InnoDB中表的主键应选择有序的id不建议使用无序的id比如UUID、MD5、HASH、字符串作为主键无法保证数据的顺序增长。6.2 非聚集索引二级索引、辅助索引聚簇索引只能在搜索条件是主键值时才发挥作用因为B树中的数据都是按照主键进行排序的如果我们想以别的列作为搜索条件那么需要创建非聚簇索引。例如以c2列作为搜索条件那么需要使用c2列创建一棵B树如下所示其中蓝色的是非聚簇索引值即C2列黄色是聚簇索引值即C1列粉色是页号。6.2.1 这个B树与聚簇索引有几处不同页内的记录是按照从c2列的大小顺序排成一个单向链表。页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表。非叶子节点存储的是记录的c2列页号。叶子节点存储的并不是完整的用户记录而只是c2列主键这两个列的值。一张表可以有多个非聚簇索引每次给字段建一个新索引 字段中的数据就会被复制一份出来 用于生成索引。 因此 给表添加索引会增加表的体积 占用磁盘存储空间。6.2.2 非聚簇索引的查找逻辑根据c2列的值查找c24的记录查找过程如下根据根页面44定位到页42因为2 ≤ 4 9由于c2列没有唯一性约束所以c24的记录可能分布在多个数据页中又因为2 ≤ 4 ≤ 4所以确定实际存储用户记录的页在页34和页35中。在页34和35中定位到具体的记录。但是这个B树的叶子节点只存储了c2和c1主键两个列所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。6.2.3 非聚簇索引存在的问题非聚集索引叶节点仍然是索引节点包括普通索引列和主键索引列只是有一个指针指向对应的数据块此如果使用非聚集索引查询而查询列中包含了其他该索引没有覆盖的列那么他还要进行第二次的查询查询节点上对应的数据行的数据。我们可以通过使用覆盖联合索引来避免回表问题。6.3 覆盖联合索引避免回表如果为一个索引指定两个字段 那么这个两个字段的内容都会被同步至索引之中。比如对abc三个字段创建了索引那么就相当于不是真的创建了三个索引aababc所以能不用select *能用到覆盖索引的时候就用覆盖索引查询结果例//查询生日在1991年11月1日出生用户的用户名select user_name from user_info where birthday 1991-11-1非聚集索引create index index_birthday on user_info(birthday);查询过程先通过索引找主键再通过主键找到数据回表覆盖索引create index index_birthday_and_user_name on user_info(birthday, user_name);查询过程通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容然而 叶节点中除了有user_name表主键ID的值以外 user_name字段的值也在里面 因此不需要通过主键ID值的查找数据行的真实所在 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式 可以省略不使用覆盖索引查找的后面两个步骤 大大的提高了查询性能7. InnoDB索引与MyISAM索引实现的区别MyISAM的索引方式都是非聚簇的InnoDB只包含1个聚簇索引在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在MyISAM中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是二级索引 。InnoDB的索引文件本身就是数据文件而MyISAM索引文件和数据文件是分离的 索引文件仅保存数据记录的地址。MyISAM的表在磁盘上存储在以下文件中*.sdi描述表结构、*.MYD数据*.MYI索引InnoDB的表在磁盘上存储在以下文件中.ibd表结构、索引和数据都存在一起InnoDB的非聚簇索引data域存储相应记录主键的值 而MyISAM索引记录的是地址。换句话说InnoDB的所有非聚簇索引都引用主键作为data域。MyISAM的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通过获取主键之后再去聚簇索引里找记录虽然说也不慢但还是比不上直接用地址去访问。InnoDB要求表必须有主键 MyISAM可以没有 。如果没有显式指定则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型7.1 innodb的B树存储结构叶子节点直接存储着数据聚簇索引7.2 Myisam的B树存储结构叶子节点存储的是数据的地址8. 索引下推Index Condition PushdownMySQL 5.6引入了索引下推优化默认开启使用SET optimizer_switch index_condition_pushdownoff;可以将其关闭。官方文档中给的例子和解释如下people表中zipcodelastnamefirstname构成一个索引SELECT*FROMpeopleWHEREzipcode95054ANDlastnameLIKE%etrunia%ANDaddressLIKE%Main Street%;如果没有使用索引下推技术则MySQL会通过zipcode95054从存储引擎中查询对应的数据返回到MySQL服务端然后MySQL服务端基于lastname LIKE %etrunia%和address LIKE %Main Street%来判断数据是否符合条件。如果使用了索引下推技术则MYSQL首先会返回符合zipcode95054的索引然后根据lastname LIKE %etrunia%和address LIKE %Main Street%来判断索引是否符合条件。如果符合条件则根据该索引来定位对应的数据如果不符合则直接reject掉。有了索引下推优化可以在有like条件查询的情况下减少回表次数。总结未开启索引下推根据筛选条件在索引树中筛选第一个条件获得结果集后回表操作进行其他条件筛选再次回表查询开启索引下推在条件查询时当前索引树如果满足全部筛选条件可以在当前树中完成全部筛选过滤得到比较小的结果集再进行回表操作9. 最左匹配原则顾名思义就是最左优先在创建多列索引时要根据业务需求where子句中使用最频繁的一列放在最左边。最左前缀匹配原则非常重要的原则mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4如果建立(a,b,c,d)顺序的索引只有abc可以用到索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。和in可以乱序比如a 1 and b 2 and c 3 建立(a,b,c)索引可以任意顺序mysql的查询优化器会帮你优化成索引可以识别的形式两个字段name,age建立联合索引如果where age12这样的话是没有利用到索引的这里我们可以简单的理解为先是对name字段的值排序然后对age的数据排序如果直接查age的话这时就没有利用到索引了查询条件where name’xxx’ and agexx这时的话就利用到索引了再来思考下where agexx and name’xxx‘这个sql会利用索引吗按照正常的原则来讲是不会利用到的但是查询优化器会进行优化把位置交换下。这个sql也能利用到索引了三个字段加上联合索引abc如果查询a 1 and c 2这种情况只有a会用到索引c不会如果是数字类型字符串没有加单引号也可以查询结果但是会造成索引失效9.1 查询优化器优化联合索引实测表字段中有exam_idpaper_iduser_id三个字段对他们创建联合索引查询测试正常排序abcexplainselect*frome_user_examwhereexam_id372andpaper_id372anduser_id184524124736049152;2. 正常排序abexplainselect*frome_user_examwhereexam_id372andpaper_id3723. 正常排序bexplainselect*frome_user_examwherepaper_id372异常排序baexplainselect*frome_user_examwherepaper_id372andexam_id372;异常排序bacexplainselect*frome_user_examwherepaper_id372andexam_id372anduser_id184524124736049152;异常排序acexplainselect*frome_user_examwhereexam_id372anduser_id184524124736049152;7. 异常排序caexplainselect*frome_user_examwhereuser_id184524124736049152andexam_id3728. 异常排序bcexplainselect*frome_user_examwherepaper_id372anduser_id184524124736049152;异常排序cbaexplainselect*frome_user_examwhereuser_id184524124736049152andpaper_id372andexam_id372;9.2 结果分析创建abc联合索引约等于创建了abc、ab、a三个索引通过结果分析只要用到了这三个索引索引前后顺序并不会对索引是否使用造成影响只有没有用到这三个索引中的任何一个才会造成索引失效比如ac只会用到索引aba用到了索引ab所以可以使用索引10. 索引失效原因如果条件中有or即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)即where a 1 or b 1如果a加了索引b没有加索引这里a的索引还是不会生效的注意要想使用or又想让索引生效只能将or条件中的每个列都加上索引就是说如果要索引生效必须要单独使用like查询是以%开头。以%结尾还是会生效的如wl%解决使用覆盖索引--id,name,age;对name 创建索引select*fromuserwherenamelike%明--typeallselectname,idfromuserwherenamelike%明--typeindex-- 没有高效使用索引是因为字符串索引会逐个转换成accii码-- 生成b树时按首个字符串顺序排序类似复合索引未用左列字段失效一样-- 跳过开始部分也就无法使用生成的b树了对于多列索引不是使用的第一部分则不会使用索引如果列类型是字符串那一定要在条件中将数据使用引号引用起来,否则不使用索引EXPLAINSELECT*FROMempWHEREname123;EXPLAINSELECT*FROMempWHEREname123;--索引失效普通使用not in不会使用索引in可以使用索引如果是主键索引无论是in还是not in都会走索引不符合最左匹配原则数据类型出现隐式转化如varchar不加单引号的话可能会自动转换为int型mysql判断全表扫描比使用索引更快的时候比如重复情况较多的字段并且正标数据不多的时候在order by时select的字段出现了非索引字段‘’号两边的字符集或者排序规则不一致重要我就是因为这个问题找了好久失效原因不光数据库的字符集要一致、表的和字段的字符集也要一致计算、函数导致索引失效-- 显示查询分析EXPLAINSELECT*FROMempWHEREemp.nameLIKEabc%;EXPLAINSELECT*FROMempWHERELEFT(emp.name,3)abc;--索引失效不等于(! 或者)索引失效EXPLAINSELECTSQL_NO_CACHE*FROMempWHEREemp.nameabc;EXPLAINSELECTSQL_NO_CACHE*FROMempWHEREemp.nameabc;--索引失效IS NOT NULL 失效 和 IS NULLEXPLAINSELECT*FROMempWHEREemp.nameISNULL;EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL;--索引失效注意当数据库中的数据的索引列的NULL值达到比较高的比例的时候即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引此时type的值是range范围查询-- 将 id20000 的数据的 name 值改为 NULLUPDATEempSETnameNULLWHEREid20000;-- 执行查询分析可以发现 IS NOT NULL 使用了索引-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效由查询优化器的算法决定EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL11. 索引字段包含null值问题11.1 如果表中有字段为null又被经常查询该不该给这个字段创建索引应该创建索引使用的时候尽量使用is null判断。IS NOT NULL 失效 和 IS NULLEXPLAINSELECT*FROMempWHEREemp.nameISNULL;EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL;--索引失效注意当数据库中的数据的索引列的NULL值达到比较高的比例的时候即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引此时type的值是range范围查询-- 将 id20000 的数据的 name 值改为 NULLUPDATEempSETnameNULLWHEREid20000;-- 执行查询分析可以发现 IS NOT NULL 使用了索引-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效由查询优化器的算法决定EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL11.2 有字段为null索引是否会失效不一定会失效每一条sql具体有没有使用索引 可以通过trace追踪一下最好还是给上默认值数字类型的给0字符串给个空串“”12. group by 分组和order by在索引使用上有什么区别group by 使用索引的原则几乎跟order by一致 唯一区别group by 先排序再分组遵照索引建的最佳左前缀法则group by没有过滤条件也可以用上索引。Order By 必须有过滤条件才能使用上索引。
Mysql学习（一）-- 索引
1.索引概述索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)它们包含着对数据表里所有记录的引用指针。索引是一种数据结构。数据库索引是数据库管理系统中一个排序的数据结构以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B树。更通俗的说索引就相当于目录。为了方便查找书中的内容通过对内容建立索引形成目录。索引是一个文件它是要占据物理空间的。、、例如数据库里面有20000条记录现在要执行这么一个查询SELECT * FROM table where num 10000。如果没有索引必须遍历整个表直到num等于10000的这一行被找到为止如果在num列上创建索引MySQL不需要任何扫描直接在索引中找10000就可以得知值这一行的位置。可见索引的建立可以提高数据库的查询速度。2.优缺点2.1 优点通过创建唯一索引可以保证数据库表中每一行数据的唯一性可以大大加快数据的查询速度这也是创建索引最主要的原因在实现数据的参考完整性方面可以加速表和表之间的连接在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间2.2 缺点时间上创建索引和维护索引要耗费时间具体地当对表中的数据进行增加、删除和修改的时候索引也要动态的维护会降低增/改/删的执行效率空间上索引需要占物理空间3.索引分类可分为单列索引普通索引唯一索引主键索引组合索引全文索引空间索引主键索引: 数据列不允许重复不允许为NULL一个表只能有一个主键。唯一索引: 数据列不允许重复允许为NULL值允许多个null值一个表允许多个列创建唯一索引。可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引普通索引: 基本的索引类型没有唯一性的限制允许为NULL值。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引全文索引 是目前搜索引擎使用的一种关键技术。全文索引类型为FULLTEXT在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。在5.6之前MySQL中只有MyISAM存储引擎支持全文索引5.6之后MyISAM和InnoDB支持可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引4.索引的设计原则索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍高效的索引对于获得良好的性能非常重要设计索引时应该考虑一下索引并非越多越好一个表中如有大量的索引不仅占用磁盘空间而且会影响INSERT、DELETE、UPDATE等语句的性能因为当表中的数据更改的同时索引也会进行调整和更新Mysql8中原话// 二级索引非聚集索引// 一个表最多可以包含64个二级索引。//多列索引最多允许有16列。// 超过限制将返回错误。//错误1070(42000):指定的关键部件太多;最多允许16个零件Atable can contain a maximum of64secondaryindexes.Amaximum of16columns is permittedformulticolumnindexes.Exceedingthe limit returns anerror.ERROR1070(42000):Toomany key parts specified;max16parts allowed避免对经常更新的表设计过多的索引并且索引中的列尽可能要少而对经常用于查询的字段应该创建索引但要避免添加不必要的字段频繁更新的列不要设计索引数据量小的表最好不要使用索引由于数据较少查询花费的时间可能比遍历索引时间还要短索引可能不会产生优化效果若是不能有效区分数据的列不适合做索引列(如性别男女未知最多也就三种区分度实在太低)当唯一性是某种数据本身的特征时指定唯一索引。使用唯一索引需能确保定义的列的数据完整性以提高查询速度在频繁排序或分组即group by或order by操作的列上建立索引如果待排序的列有多个可以在这些列上建立组合索引定义有外键的数据列一定要建立索引最左前缀匹配原则组合索引非常重要的原则mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。对于定义为text、image和bit的数据类型的列不要建立索引。查询的内容尽量匹配索引避免回表问题当没有给表创建索引的时候如果有主键会创建聚簇索引如果没有主键会生成rowid作为隐式主键5.索引的数据结构b树hash索引的数据结构和具体存储引擎的实现有关在MySQL中使用较多的索引有Hash索引B树索引等而我们经常使用的InnoDB存储引擎的默认索引实现为B树索引。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择BTree索引。5.1 索引实现原理为什么使用B树作为索引的存储结构5.1.1 目录到索引的演变过程假设有一个表index_demo表中有2个INT类型的列1个CHAR(1)类型的列c1列为主键CREATETABLEindex_demo(c1INT,c2INT,c3CHAR(1),PRIMARYKEY(c1));index_demo表的简化的行格式示意图如下我们只在示意图里展示记录的这几个部分record_type表示记录的类型 0是普通记录、 2是最小记录、 3 是最大记录、1是B树非叶子节点记录。next_record表示下一条记录的相对位置我们用箭头来表明下一条记录。各个列的值这里只记录在 index_demo 表中的三个列分别是 c1 、 c2 和 c3 。其他信息除了上述3种信息以外的所有信息包括其他隐藏列的值以及记录的额外信息。将其他信息项暂时去掉并把它竖起来的效果就是这样把一些记录放到页里的示意图就是这里一页就是一个磁盘块代表一次IOMySQL InnoDB的默认的页大小是16KB因此数据存储在磁盘中可能会占用多个数据页。如果各个页中的记录没有规律我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中我们可以这样做 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值给所有的页建立目录项以页28为例它对应目录项2这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储比如数组就可以实现根据主键值快速查找某条记录的功能了。比如查找主键值为 20 的记录具体查找过程分两步先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中因为 12 ≤ 20 209 对应页9。再到页9中根据二分法快速定位到主键值为 20 的用户记录。至此针对数据页做的简易目录就搞定了。这个目录有一个别名称为索引。5.1.2 目录结构到B树结构的演变过程我们新分配一个编号为30的页来专门存储目录项记录页10、28、9、20专门存储用户记录目录项记录和普通的用户记录的不同点目录项记录 的 record_type 值是1而 普通用户记录 的 record_type 值是0。目录项记录只有主键值和页的编号两个列而普通的用户记录的列是用户自己定义的包含很多列另外还有InnoDB自己添加的隐藏列。现在查找主键值为 20 的记录具体查找过程分两步先到页30中通过二分法快速定位到对应目录项因为 12 ≤ 20 209 就是页9。再到页9中根据二分法快速定位到主键值为 20 的用户记录。更复杂的情况如下我们生成了一个存储更高级目录项的 页33 这个页中的两条记录分别代表页30和页32如果用户记录的主键值在[1, 320)之间则到页30中查找更详细的目录项记录如果主键值 不小于320 的话就到页32中查找更详细的目录项记录。这个数据结构它的名称是 B树 。5.2 B树实现索引关于B树和B树详细可以看数据结构-B树和B树非叶节点只存放索引不存放数据n棵子tree的节点包含n个关键字所有的叶子节点通过一个有序链表构成了全部数据及指向这些数据的指针可以按照关键码排序的次序遍历全部数据5.3 Hash表实现索引简要说下类似于数据结构中简单实现的HASH表散列表一样当我们在mysql中用哈希索引时主要就是通过Hash算法常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法将数据库字段数据转换成定长的Hash值与这条数据的行指针一并存入Hash表的对应位置如果发生Hash碰撞两个不同关键字的Hash值相同则在对应Hash键下以链表形式存储。当然这只是简略模拟图。5.4 Hash表相对于B树的优劣hash索引底层就是hash表进行查找时调用一次hash函数就可以获取到相应的键值之后进行回表查询获得实际数据。B树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据。5.3.1 Hash表的优点正常情况下Hash表的等值查询更快。如果存在大量重复键值就存在Hash碰撞的问题。就要先找到键所在位置然后再根据链表往后扫描直到找到相应的数据5.3.2 缺点因为在hash索引中经过hash函数建立索引之后索引的顺序与原顺序无法保持一致不能支持范围查询。而B树的的所有节点皆遵循(左节点小于父节点右节点大于父节点多叉树也类似)天然支持范围。hash索引不支持使用索引进行排序原理同上。hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。hash索引任何时候都避免不了回表查询数据而B树在符合某些条件(聚簇索引覆盖索引等)的时候可以只通过索引完成查询。hash索引虽然在等值查询上较快但是不稳定。性能不可预测当某个键值存在大量重复的时候发生hash碰撞此时效率可能极差。而B树的查询效率比较稳定对于所有的查询都是从根节点到叶子节点且树的高度较低。5.4 为什么使用B树而不用B树B和B树的区别在于B树的非叶子结点只包含导航信息不包含实际的值所有的叶子结点和相连的节点使用链表相连便于区间查找和遍历。5.4.1 B 树的优点在于读写代价低和磁盘IO效率有关一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。这样的话索引查找过程中就要产生磁盘I/O消耗。B树的内部结点并没有指向关键字具体信息的指针只是作为索引使用其内部结点比B树小盘块能容纳的结点中关键字数量更多一次性读入内存中可以查找的关键字也就越多相对的IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素查询效率稳定由于B树在内部节点上不包含数据信息因此在内存页中能够存放更多的key。 数据存放的更加紧密具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。B树支持顺序查询B树的叶子结点都是相链的因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历相邻的元素可能在内存中不相邻所以缓存命中性没有B树好。增删效率好5.4.2 B树优点由于B树的每一个节点都包含key和value因此经常访问的元素可能离根节点更近因此访问也更迅速。下面是B 树和B树的区别图5.5 B树的存储量级真实环境中一个页存放的记录数量是非常大的默认16KB假设指针与键值忽略不计或看做10个字节数据占 1 kb 的空间如果B树只有1层也就是只有1个用于存放用户记录的节点最多能存放 16 条记录。如果B树有2层最多能存放1600×1625600条记录。如果B树有3层最多能存放1600×1600×1640960000条记录。如果存储千万级别的数据只需要三层就够了B树的非叶子节点不存储用户记录只存储目录记录相对B树每个节点可以存储更多的记录树的高度会更矮胖IO次数也会更少。6. 聚簇索引和非聚簇索引6.1 聚簇索引innodb可以把主键索引理解成聚簇索引。一张表中如果没有指定某列是聚簇索引那么该表的第一个唯一非空索引被作为聚集索引如果也没有索引那么系统会自动创建一个隐含列作为表的聚集索引 这个字段长度为6个字节类型为长整型。一个表只能有一个聚集索引因为聚集索引把表的数据格式转换成索引树平衡树的格式放置。所以一个表只能有一个主键。树中的节点除底部节点的数据是由主键及数据地址构成。时间复杂度Olog n(n代表记录数)页内的记录是按照主键的大小顺序排成一个单向链表。页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表。非叶子节点存储的是记录的主键页号。叶子节点存储的是完整的用户记录。查找的时候先通过主键找到所在的叶节点而这个叶节点叶包含了此行的所有数据信息innodb直接存储数据信息myisam存储着数据的指向来源通过DBCC PAGE查看页信息验证聚集索引和非聚集索引节点信息6.1.1 优点数据访问更快 因为索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快。聚簇索引对于主键的排序查找和范围查找速度非常快。按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库可以从更少的数据块中提取数据节省了大量的IO操作。6.1.2 缺点插入速度严重依赖于插入顺序 按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键。更新主键的代价很高 因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。6.1.3 限制只有InnoDB引擎支持聚簇索引MyISAM不支持聚簇索引。由于数据的物理存储排序方式只能有一种所以每个MySQL的表只能有一个聚簇索引。如果没有为表定义主键InnoDB会选择非空的唯一索引列代替。如果没有这样的列InnoDB会隐式的定义一个主键作为聚簇索引。为了充分利用聚簇索引的聚簇特性InnoDB中表的主键应选择有序的id不建议使用无序的id比如UUID、MD5、HASH、字符串作为主键无法保证数据的顺序增长。6.2 非聚集索引二级索引、辅助索引聚簇索引只能在搜索条件是主键值时才发挥作用因为B树中的数据都是按照主键进行排序的如果我们想以别的列作为搜索条件那么需要创建非聚簇索引。例如以c2列作为搜索条件那么需要使用c2列创建一棵B树如下所示其中蓝色的是非聚簇索引值即C2列黄色是聚簇索引值即C1列粉色是页号。6.2.1 这个B树与聚簇索引有几处不同页内的记录是按照从c2列的大小顺序排成一个单向链表。页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表。非叶子节点存储的是记录的c2列页号。叶子节点存储的并不是完整的用户记录而只是c2列主键这两个列的值。一张表可以有多个非聚簇索引每次给字段建一个新索引 字段中的数据就会被复制一份出来 用于生成索引。 因此 给表添加索引会增加表的体积 占用磁盘存储空间。6.2.2 非聚簇索引的查找逻辑根据c2列的值查找c24的记录查找过程如下根据根页面44定位到页42因为2 ≤ 4 9由于c2列没有唯一性约束所以c24的记录可能分布在多个数据页中又因为2 ≤ 4 ≤ 4所以确定实际存储用户记录的页在页34和页35中。在页34和35中定位到具体的记录。但是这个B树的叶子节点只存储了c2和c1主键两个列所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。6.2.3 非聚簇索引存在的问题非聚集索引叶节点仍然是索引节点包括普通索引列和主键索引列只是有一个指针指向对应的数据块此如果使用非聚集索引查询而查询列中包含了其他该索引没有覆盖的列那么他还要进行第二次的查询查询节点上对应的数据行的数据。我们可以通过使用覆盖联合索引来避免回表问题。6.3 覆盖联合索引避免回表如果为一个索引指定两个字段 那么这个两个字段的内容都会被同步至索引之中。比如对abc三个字段创建了索引那么就相当于不是真的创建了三个索引aababc所以能不用select *能用到覆盖索引的时候就用覆盖索引查询结果例//查询生日在1991年11月1日出生用户的用户名select user_name from user_info where birthday 1991-11-1非聚集索引create index index_birthday on user_info(birthday);查询过程先通过索引找主键再通过主键找到数据回表覆盖索引create index index_birthday_and_user_name on user_info(birthday, user_name);查询过程通过非聚集索引index_birthday_and_user_name查找birthday等于1991-11-1的叶节点的内容然而 叶节点中除了有user_name表主键ID的值以外 user_name字段的值也在里面 因此不需要通过主键ID值的查找数据行的真实所在 直接取得叶节点中user_name的值返回即可。 通过这种覆盖索引直接查找的方式 可以省略不使用覆盖索引查找的后面两个步骤 大大的提高了查询性能7. InnoDB索引与MyISAM索引实现的区别MyISAM的索引方式都是非聚簇的InnoDB只包含1个聚簇索引在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在MyISAM中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是二级索引 。InnoDB的索引文件本身就是数据文件而MyISAM索引文件和数据文件是分离的 索引文件仅保存数据记录的地址。MyISAM的表在磁盘上存储在以下文件中*.sdi描述表结构、*.MYD数据*.MYI索引InnoDB的表在磁盘上存储在以下文件中.ibd表结构、索引和数据都存在一起InnoDB的非聚簇索引data域存储相应记录主键的值 而MyISAM索引记录的是地址。换句话说InnoDB的所有非聚簇索引都引用主键作为data域。MyISAM的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通过获取主键之后再去聚簇索引里找记录虽然说也不慢但还是比不上直接用地址去访问。InnoDB要求表必须有主键 MyISAM可以没有 。如果没有显式指定则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型7.1 innodb的B树存储结构叶子节点直接存储着数据聚簇索引7.2 Myisam的B树存储结构叶子节点存储的是数据的地址8. 索引下推Index Condition PushdownMySQL 5.6引入了索引下推优化默认开启使用SET optimizer_switch index_condition_pushdownoff;可以将其关闭。官方文档中给的例子和解释如下people表中zipcodelastnamefirstname构成一个索引SELECT*FROMpeopleWHEREzipcode95054ANDlastnameLIKE%etrunia%ANDaddressLIKE%Main Street%;如果没有使用索引下推技术则MySQL会通过zipcode95054从存储引擎中查询对应的数据返回到MySQL服务端然后MySQL服务端基于lastname LIKE %etrunia%和address LIKE %Main Street%来判断数据是否符合条件。如果使用了索引下推技术则MYSQL首先会返回符合zipcode95054的索引然后根据lastname LIKE %etrunia%和address LIKE %Main Street%来判断索引是否符合条件。如果符合条件则根据该索引来定位对应的数据如果不符合则直接reject掉。有了索引下推优化可以在有like条件查询的情况下减少回表次数。总结未开启索引下推根据筛选条件在索引树中筛选第一个条件获得结果集后回表操作进行其他条件筛选再次回表查询开启索引下推在条件查询时当前索引树如果满足全部筛选条件可以在当前树中完成全部筛选过滤得到比较小的结果集再进行回表操作9. 最左匹配原则顾名思义就是最左优先在创建多列索引时要根据业务需求where子句中使用最频繁的一列放在最左边。最左前缀匹配原则非常重要的原则mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4如果建立(a,b,c,d)顺序的索引只有abc可以用到索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。和in可以乱序比如a 1 and b 2 and c 3 建立(a,b,c)索引可以任意顺序mysql的查询优化器会帮你优化成索引可以识别的形式两个字段name,age建立联合索引如果where age12这样的话是没有利用到索引的这里我们可以简单的理解为先是对name字段的值排序然后对age的数据排序如果直接查age的话这时就没有利用到索引了查询条件where name’xxx’ and agexx这时的话就利用到索引了再来思考下where agexx and name’xxx‘这个sql会利用索引吗按照正常的原则来讲是不会利用到的但是查询优化器会进行优化把位置交换下。这个sql也能利用到索引了三个字段加上联合索引abc如果查询a 1 and c 2这种情况只有a会用到索引c不会如果是数字类型字符串没有加单引号也可以查询结果但是会造成索引失效9.1 查询优化器优化联合索引实测表字段中有exam_idpaper_iduser_id三个字段对他们创建联合索引查询测试正常排序abcexplainselect*frome_user_examwhereexam_id372andpaper_id372anduser_id184524124736049152;2. 正常排序abexplainselect*frome_user_examwhereexam_id372andpaper_id3723. 正常排序bexplainselect*frome_user_examwherepaper_id372异常排序baexplainselect*frome_user_examwherepaper_id372andexam_id372;异常排序bacexplainselect*frome_user_examwherepaper_id372andexam_id372anduser_id184524124736049152;异常排序acexplainselect*frome_user_examwhereexam_id372anduser_id184524124736049152;7. 异常排序caexplainselect*frome_user_examwhereuser_id184524124736049152andexam_id3728. 异常排序bcexplainselect*frome_user_examwherepaper_id372anduser_id184524124736049152;异常排序cbaexplainselect*frome_user_examwhereuser_id184524124736049152andpaper_id372andexam_id372;9.2 结果分析创建abc联合索引约等于创建了abc、ab、a三个索引通过结果分析只要用到了这三个索引索引前后顺序并不会对索引是否使用造成影响只有没有用到这三个索引中的任何一个才会造成索引失效比如ac只会用到索引aba用到了索引ab所以可以使用索引10. 索引失效原因如果条件中有or即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)即where a 1 or b 1如果a加了索引b没有加索引这里a的索引还是不会生效的注意要想使用or又想让索引生效只能将or条件中的每个列都加上索引就是说如果要索引生效必须要单独使用like查询是以%开头。以%结尾还是会生效的如wl%解决使用覆盖索引--id,name,age;对name 创建索引select*fromuserwherenamelike%明--typeallselectname,idfromuserwherenamelike%明--typeindex-- 没有高效使用索引是因为字符串索引会逐个转换成accii码-- 生成b树时按首个字符串顺序排序类似复合索引未用左列字段失效一样-- 跳过开始部分也就无法使用生成的b树了对于多列索引不是使用的第一部分则不会使用索引如果列类型是字符串那一定要在条件中将数据使用引号引用起来,否则不使用索引EXPLAINSELECT*FROMempWHEREname123;EXPLAINSELECT*FROMempWHEREname123;--索引失效普通使用not in不会使用索引in可以使用索引如果是主键索引无论是in还是not in都会走索引不符合最左匹配原则数据类型出现隐式转化如varchar不加单引号的话可能会自动转换为int型mysql判断全表扫描比使用索引更快的时候比如重复情况较多的字段并且正标数据不多的时候在order by时select的字段出现了非索引字段‘’号两边的字符集或者排序规则不一致重要我就是因为这个问题找了好久失效原因不光数据库的字符集要一致、表的和字段的字符集也要一致计算、函数导致索引失效-- 显示查询分析EXPLAINSELECT*FROMempWHEREemp.nameLIKEabc%;EXPLAINSELECT*FROMempWHERELEFT(emp.name,3)abc;--索引失效不等于(! 或者)索引失效EXPLAINSELECTSQL_NO_CACHE*FROMempWHEREemp.nameabc;EXPLAINSELECTSQL_NO_CACHE*FROMempWHEREemp.nameabc;--索引失效IS NOT NULL 失效 和 IS NULLEXPLAINSELECT*FROMempWHEREemp.nameISNULL;EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL;--索引失效注意当数据库中的数据的索引列的NULL值达到比较高的比例的时候即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引此时type的值是range范围查询-- 将 id20000 的数据的 name 值改为 NULLUPDATEempSETnameNULLWHEREid20000;-- 执行查询分析可以发现 IS NOT NULL 使用了索引-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效由查询优化器的算法决定EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL11. 索引字段包含null值问题11.1 如果表中有字段为null又被经常查询该不该给这个字段创建索引应该创建索引使用的时候尽量使用is null判断。IS NOT NULL 失效 和 IS NULLEXPLAINSELECT*FROMempWHEREemp.nameISNULL;EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL;--索引失效注意当数据库中的数据的索引列的NULL值达到比较高的比例的时候即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引此时type的值是range范围查询-- 将 id20000 的数据的 name 值改为 NULLUPDATEempSETnameNULLWHEREid20000;-- 执行查询分析可以发现 IS NOT NULL 使用了索引-- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效由查询优化器的算法决定EXPLAINSELECT*FROMempWHEREemp.nameISNOTNULL11.2 有字段为null索引是否会失效不一定会失效每一条sql具体有没有使用索引 可以通过trace追踪一下最好还是给上默认值数字类型的给0字符串给个空串“”12. group by 分组和order by在索引使用上有什么区别group by 使用索引的原则几乎跟order by一致 唯一区别group by 先排序再分组遵照索引建的最佳左前缀法则group by没有过滤条件也可以用上索引。Order By 必须有过滤条件才能使用上索引。