MySQL索引优化知识点有哪些

这篇文章主要介绍“MySQL索引优化知识点有哪些”，在日常操作中，相信很多人在MySQL索引优化知识点有哪些问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL索引优化知识点有哪些”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！索引可以大大提高MySQL的检索速度。索引就像书中的目录一样为了让我们更快的寻找到自己想要的数据，下面是MySQL常用的索引简介。(1) 普通索引这是最基本的索引，它没有任何限制。如何使用？比如：ALTER TABLE users ADD INDEX index_users( id )比如：CREATE INDEX index_users ON users (id)(2) 唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。如何使用？比如：ALTER TABLE users ADD UNIQUE index_users( id )比如：CREATE UNIQUE INDEX index_users ON users(id)(3) 主键索引它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会自动创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。如何使用？比如：ALTER TABLE users ADD PRIMARY KEY ( id )方式2：创建表的时候指定主键（1）聚集索引聚集索引，又叫聚簇索引，所有的数据都存在聚集索引上，叶子节点直接对应数据，从中间级的索引页的索引行直接对应数据页。InnoDB存储引擎中的主键默认就会创建一个聚集索引，每张表只能创建一个聚集索引。记录的索引顺序与物理顺序相同，更适合between and和order by操作。InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：（1）如果表定义了PK，则PK就是聚集索引；（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引画外音：所以PK查询非常快，直接定位行记录。打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面。（2）非聚集索引非聚集索引，又叫非聚簇索引、辅助索引，所有的数据和索引的目录是分开存的，叶子节点不存放具体的整行数据（叶子结点不直接指向数据页），而是存储的这一行的主键的值。记录的索引顺序与物理顺序无关。每张表可以有多个非聚集索引，需要更多磁盘和内存，多个索引会影响insert和update的速度。画外音：非聚集索引需要回表查询，先定位主键值，再定位行记录，因为要扫描两遍索引树，它的性能较扫一遍索引树更低。其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。联合索引又叫复合索引，对表上的多个字段同时建立的索引(有顺序，ABC，ACB是完全不同的两种联合索引。)以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。遵循最左前缀原则（必须带着最左边的列做条件才能命中索引），且从出现范围开始索引失效；当遇到范围查询(>、
这条语句只有 a,b 会用到索引，c 都不能用到索引。A遵从最左匹配原则，age是在最左边，所以A走索引；B直接从name开始，没有遵从最左匹配原则，所以不走索引；C虽然从name开始，但是有索引最左边的age，mysql内部会自动转成where age = ’18’ and name = ‘小张’ and sex = ‘男’ 这种，所以还是遵从最左匹配原则；D这个是因为age>20是范围，范围字段会结束索引对范围后面索引字段的使用，所以只有走了age这个索引；E这个虽然遵循最左匹配原则，但是不走索引，因为!= 不走索引；question1:如何给下列sql语句加上联合索引？answer：咱们一看，直接加索引(a,b,c)就可以了，其实不然，也不能说这个答案不对，只能说这个答案不够完整。因为mysql在执行的时候会经历优化器过程，所以会把sql语句自动优化成符合索引的顺序，所以索引(a,b,c) (a,c,b) 或者是(c,b,a)都是可以的，那我们究竟如何确定索引呢？这个就得根据实际情况来了，比如a字段是表示性别的，只有0，1和2三个值来表示 未知，男，女三种性别，那么把a字段放在联合索引的最后会比较合适，反正哪个字段的内容重复率越高，就把哪个字段往联合索引的后面放。question2:如何给下列sql语句加上索引？answer：如果咱们建立索引(a,b),那么a>1是可以走到索引的，但是b=2就没法走到索引了。但是如果咱们建立索引(b,a),那么sql语句会被自动优化成 where b=2 and a> 1，这样a和b都能走到索引，所以建立索引(b,a)比较合适使用聚集索引（主键或第一个唯一索引）就不会回表，非聚集索引就会回表。当select的数据列被所建索引覆盖时不需要回表，可以直接取得数据。覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。覆盖索引在查询过程中不需要回表。只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表速度更快。覆盖索引其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引，以及唯一索引都能实现覆盖索引的作用。explain的输出结果Extra字段为Using index时，能够触发索引覆盖。(1) 如何实现索引覆盖？常见的方法是：将被查询的字段，建立到联合索引里去。能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。Extra：Using index。(2)哪些场景可以利用索引覆盖来优化SQL？ 场景1：全表count查询优化原表为：user(PK id, name, sex)；不能利用索引覆盖添加索引，就能够利用索引覆盖提效场景2：列查询回表优化这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。场景3：分页查询将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。前缀索引说白了就是对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快。MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术，我们才知道应该如何在表上建立索引。为什么会有index merge？我们的 where 中可能有多个条件(或者join)涉及到多个字段，它们之间进行 AND 或者 OR，那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。MySQL5.0之前，一个表一次只能使用一个索引，无法同时使用多个索引分别进行条件扫描。但是从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。索引合并是指分别创建的两个索引，在某一次查询中临时合并成一条索引。(1)索引下推简介索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加，用于优化数据的查询。 通过索引下推对于非主键索引进行优化，可有效减少回表次数，从而提高效率。如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。当你不使用ICP,通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。使用ICP，当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。(2)适用场景当需要整表扫描，e.g.:range,ref,eq_ref….适用InnoDB引擎和MyISAM引擎查询（5.6版本不适用分区查询，5.7版本可以用于分区表查询）。InnoDB引擎仅仅适用二级索引。（原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区）。子查询条件不能下推。触发条件不能下推，调用存储过程条件不能下推。查询的条件字段尽量用索引字段and条件相连，有一列有索引就会命中索引，加快查询速度；or条件相连，所有列都有索引才能命中索引，加快查询速度；因为页面搜索严禁左模糊或者全模糊，如果需要可以使用搜索引擎来解决。union能够命中索引，并且MySQL 耗费的 CPU 最少。in能够命中索引，查询优化耗费的 CPU 比 union all 多，但可以忽略不计，一般情况下建议使用 in。or 新版的 MySQL（MySQL5.0后） 索引合并能够命中索引，查询优化耗费的 CPU 比 in多，不建议频繁用or。补充：有些地方说在where条件中使用or，索引会失效，造成全表扫描，这是个误区：要求where子句使用的所有字段，都必须建立索引；如果数据量太少，mysql制定执行计划时发现全表扫描比索引查找更快，所以会不使用索引；确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。负向条件有：!=、、not in、not exists、not like 等。例如下面SQL语句：可以优化为 in 查询：如果在(a,b,c)三个字段上建立联合索引，那么他会自动建立 a| (a,b) | (a,b,c)组索引。联合索引遵循最左前缀原则（必须带着最左边的列做条件才能命中索引），且从出现范围开始索引失效；登录业务需求，SQL语句如下：可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求，而有很多login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。范围条件有：、>=、between等。索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。例如下面的 SQL 语句，即使 date 上建立了索引，也会全表扫描：可优化为值计算，如下：比如下面的 SQL 语句：可以优化为：字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引。可以优化为：更新会变更 B+ 树，更新云主机域名免费试用频繁的字段建立索引会大大降低数据库性能。“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算。覆盖索引：查询的列和所建立的索引的列个数相同，字段相同。被查询的列，数据能从索引中取得，而不用通过行定位符 row-locator 再到 row 上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。例如登录业务需求，SQL语句如下。可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时，尽量使用not null 约束以及默认值。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort 的情况，影响查询性能。例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。对列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果该列在前10个或20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖索引。不过很多时候没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。示例如下，先快速定位需要获取的id段，然后再关联:比如如下 SQL 语句：可以优化为：自己明确知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。例如：left join是由左边决定的，左边的数据一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用right join。consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。ref：使用普通的索引（Normal Index）。range：对索引进行范围检索。当 type=index 时，索引物理文件全扫，速度非常慢。不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。索引越多越好，认为需要一个查询就建一个索引。宁缺勿滥，因为索引会消耗空间、严重拖慢更新和新增速度。抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。过早优化，在不了解系统的情况下就开始优化。主键自动建立唯一索引。经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引。查询中与其他表关联的字段，外键关系建立索引。经常用于聚合函数的列要建立索引，如min()，max()等的聚合函数。经常增删改的列不要建立索引。有大量重复的列不建立索引。表记录太少不要建立索引，因为数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果 。到此，关于“MySQL索引优化知识点有哪些”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注开发云网站，小编会继续努力为大家带来更多实用的文章！

相关推荐: mysql查询区分大小写吗

本篇内容主要讲解“mysql查询区分大小写吗”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql查询区分大小写吗”吧! mysql的默认查询是不区分大小写的；如果需要区分大小写，可以利用Binary关键字在查询…