MySQL索引优化总结 |
您所在的位置:网站首页 › 怎么使用自动索引方式 › MySQL索引优化总结 |
前言:相信大家都知道索引可以大大提高MySQL的检索速度,但是真正在平时工作中写SQL,真的会考虑到这条SQL如何能够用上索引提升执行效率?本篇博客详细的介绍了索引优化的20个原则,只要在工作中能够随时应用到,相信你写出的SQL能够命中索引,效率杠杠的。 一、索引的分类索引可以大大提高MySQL的检索速度。索引就像书中的目录一样为了让我们更快的寻找到自己想要的数据,下面是MySQL常用的索引简介。 (1) 普通索引 这是最基本的索引,它没有任何限制。 如何使用? //方式1 ALTER TABLE table_name ADD INDEX index_name ( column )比如:ALTER TABLE users ADD INDEX index_users( id ) //方式2 CREATE INDEX index_name ON table_name (column_name)比如:CREATE INDEX index_users ON users (id) (2) 唯一索引 与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须一。 如何使用? //方式1 ALTER TABLE table_name ADD UNIQUE [indexName] (column)比如:ALTER TABLE users ADD UNIQUE index_users( id ) //方式2 CREATE UNIQUE INDEX index_name ON table_name (column_name)比如:CREATE UNIQUE INDEX index_users ON users(id) (3) 主键索引 它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候指定了主键,就会自动创建主键索引, CREATE INDEX不能用来创建主键索引,使用 ALTER TABLE来代替。 如何使用? //方式1 ALTER TABLE table_name ADD PRIMARY KEY ( column )比如:ALTER TABLE users ADD PRIMARY KEY ( id ) 方式2:创建表的时候指定主键 1.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的速度。 画外音:非聚集索引需要回表查询,先定位主键值,再定位行记录,因为要扫描两遍索引树,它的性能较扫一遍索引树更低。 其实按照定义,除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。如果非要把非聚集索引类比成现实生活中的东西,那么非聚集索引就像新华字典的偏旁字典,他结构顺序与实际存放顺序不一定一致。 1.3、联合索引最左匹配原则联合索引又叫复合索引,对表上的多个字段同时建立的索引(有顺序,ABC,ACB是完全不同的两种联合索引。)以联合索引(a,b,c)为例,建立这样的索引相当于建立了索引a、ab、abc三个索引。 遵循最左前缀原则(必须带着最左边的列做条件才能命中索引),且从出现范围开始索引失效; 当遇到范围查询(>、1 and c =1; 这条语句只有 a,b 会用到索引,c 都不能用到索引。 create index mix_ind on 表名 (id,name,email); select * from 表名 where id = 123; # 命中索引 select * from 表名 where id = 123 and name = 'pamela'; # 命中索引 select * from 表名 where id > 123 and name = 'pamela'; # id命中,name不命中索引,因为出现范围 select * from 表名 where id = 123 and email = '[email protected]'; # 命中索引 select * from 表名 where email = '[email protected]'; # 不命中索引,因为条件中没有id select * from 表名 where name='pamela' and email = '[email protected]'; # 不命中 A:select * from student where age = 16 and name = '小张' B:select * from student where name = '小张' and sex = '男' C:select * from student where name = '小张' and sex = '男' and age = 18 D:select * from student where age > 20 and name = '小张' E:select * from student where age != 15 and name = '小张'A遵从最左匹配原则,age是在最左边,所以A走索引; B直接从name开始,没有遵从最左匹配原则,所以不走索引; C虽然从name开始,但是有索引最左边的age,mysql内部会自动转成where age = '18' and name = '小张' and sex = '男' 这种,所以还是遵从最左匹配原则; D这个是因为age>20是范围,范围字段会结束索引对范围后面索引字段的使用,所以只有走了age这个索引; E这个虽然遵循最左匹配原则,但是不走索引,因为!= 不走索引; question1:如何给下列sql语句加上联合索引? select * from test where a = 1 and b = 1 and c = 1;answer: 咱们一看,直接加索引(a,b,c)就可以了,其实不然,也不能说这个答案不对,只能说这个答案不够完整。因为mysql在执行的时候会经历优化器过程,所以会把sql语句自动优化成符合索引的顺序,所以索引(a,b,c) (a,c,b) 或者是(c,b,a)都是可以的,那我们究竟如何确定索引呢?这个就得根据实际情况来了,比如a字段是表示性别的,只有0,1和2三个值来表示 未知,男,女三种性别,那么把a字段放在联合索引的最后会比较合适,反正哪个字段的内容重复率越高,就把哪个字段往联合索引的后面放。 question2:如何给下列sql语句加上索引? SELECT * FROM table WHERE a > 1 and b = 2;answer: 如果咱们建立索引(a,b),那么a>1是可以走到索引的,但是b=2就没法走到索引了。但是如果咱们建立索引(b,a),那么sql语句会被自动优化成 where b=2 and a> 1,这样a和b都能走到索引,所以建立索引(b,a)比较合适 1.4、索引覆盖和回表使用聚集索引(主键或第一个唯一索引)就不会回表,非聚集索引就会回表。当select的数据列被所建索引覆盖时不需要回表,可以直接取得数据。 覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。覆盖索引在查询过程中不需要回表。只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表速度更快。 覆盖索引其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引,以及唯一索引都能实现覆盖索引的作用。explain的输出结果Extra字段为Using index时,能够触发索引覆盖。 create index ind_id on 表名(id); # 对id字段创建了索引 select id from 表名 where id > 100; # 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表 select max(id) from 表名 where id > 100; # 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表 select count(id) from 表名 where id > 100; # 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表 select name from 表名 where id > 100; # 相对慢(1) 如何实现索引覆盖? 常见的方法是:将被查询的字段,建立到联合索引里去。 select id,name from user where name='shenjian';能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。 Extra:Using index。 (2)哪些场景可以利用索引覆盖来优化SQL? 场景1:全表count查询优化 原表为:user(PK id, name, sex);不能利用索引覆盖 select count(name) from user;添加索引,就能够利用索引覆盖提效 alter table user add key(name);场景2:列查询回表优化 这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。 场景3:分页查询 将单列索引(name)升级为联合索引(name, sex),也可以避免回表。 1.5、前缀索引前缀索引说白了就是对文本的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引更小,所以查询更快。 ALTER TABLE table_name ADD KEY(column_name(prefix_length));MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。 1.6、索引合并深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。 为什么会有index merge? 我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说,其实就是:对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。 MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。 索引合并是指分别创建的两个索引,在某一次查询中临时合并成一条索引。 # 索引合并 create index ind_id on 表名(id); create index ind_email on 表名(email); select * from 表名 where id=100 or email = '[email protected]' # 索引合并,临时把两个索引ind_id和ind_email合并成一个索引 1.7、索引下推(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缓冲区)。 子查询条件不能下推。触发条件不能下推,调用存储过程条件不能下推。 二、索引优化规则查询的条件字段尽量用索引字段 2.0、and/or条件相连and条件相连,有一列有索引就会命中索引,加快查询速度;or条件相连,所有列都有索引才能命中索引,加快查询速度; create index mix_ind on 表名 (id); select * from 表名 where id = 123 and name = 'pamela'; # 有一列有索引,速度快 select * from 表名 where id = 123 or name = 'pamela'; # 不是所有列都有索引,速度慢 2.1、like语句的前导模糊查询不能使用索引 select * from doc where title like '%XX'; --不能使用索引 select * from doc where title like 'XX%'; --非前导模糊查询,可以使用索引因为页面搜索严禁左模糊或者全模糊,如果需要可以使用搜索引擎来解决。 2.2、union、in、or 都能够命中索引,建议使用 inunion能够命中索引,并且MySQL 耗费的 CPU 最少。 select * from doc where status=1 union all select * from doc where status=2;in能够命中索引,查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in。 select * from doc where status in (1, 2);or 新版的 MySQL(MySQL5.0后) 索引合并能够命中索引,查询优化耗费的 CPU 比 in多,不建议频繁用or。 select * from doc where status = 1 or status = 2补充:有些地方说在where条件中使用or,索引会失效,造成全表扫描,这是个误区: 要求where子句使用的所有字段,都必须建立索引; 如果数据量太少,mysql制定执行计划时发现全表扫描比索引查找更快,所以会不使用索引; 确保mysql版本5.0以上,且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。 2.3、负向条件查询不能使用索引负向条件有:!=、、not in、not exists、not like 等。 例如下面SQL语句: select * from doc where status != 1 and status != 2;可以优化为 in 查询: select * from doc where status in (0,3,4); 2.4、联合索引最左前缀原则如果在(a,b,c)三个字段上建立联合索引,那么他会自动建立 a| (a,b) | (a,b,c)组索引。联合索引遵循最左前缀原则(必须带着最左边的列做条件才能命中索引),且从出现范围开始索引失效; create index mix_ind on 表名 (id,name,email); select * from 表名 where id = 123; # 命中索引 select * from 表名 where id > 123; # 不命中索引,因为出现范围 select * from 表名 where id = 123 and name = 'pamela'; # 命中索引 select * from 表名 where id > 123 and name = 'pamela'; # 不命中索引,因为出现范围 select * from 表名 where id = 123 and email = '[email protected]'; # 命中索引 select * from 表名 where email = '[email protected]'; # 不命中索引,因为条件中没有id select * from 表名 where name='pamela' and email = '[email protected]'; # 不命中索引,因为条件中没有id登录业务需求,SQL语句如下: select uid, login_time from user where login_name=? andpasswd=?可以建立(login_name, passwd)的联合索引。因为业务上几乎没有passwd 的单条件查询需求,而有很多login_name 的单条件查询需求,所以可以建立(login_name, passwd)的联合索引,而不是(passwd, login_name)。 2.5、不能使用索引中范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效。范围条件有:=、between等。 索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。 假如有联合索引 (empno、title、fromdate),那么下面的 SQL 中 emp_no 可以用到索引,而title 和 from_date 则使用不到索引。 select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31' 2.6、不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描。例如下面的 SQL 语句,即使 date 上建立了索引,也会全表扫描: select * from doc where YEAR(create_time) |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |