在线客服
扫描二维码
下载博学谷APP扫描二维码
关注博学谷微信公众号
优化SQL是一个老生常谈的问题,我们可以从多方面入手对SQL进行优化。今天本文主要从应用层来看,如何通过建立索引优化SQL。索引的数据结构B+Tree有着较高的查询性能,因此建立索引主要是对SQL 的查询性能进行优化。下面我们一起来看看建立普通索引、建立复合索引、最左前缀匹配原则、索引下推、覆盖索引、普通索引等等内容吧~
1、建立普通索引:对经常出现在 where 关键字后面的表字段建立对应的索引。。
2、建立复合索引:如果 where 关键字后面常出现的有几个字段,可以建立对应的 复合索引。要注意可以优化的一点是,将单独出现最多的字段放在前面。例如现在我们有两个字段 a 和 b 经常会同时出现在 where 关键字后面:
select * from t where a = 1 and b = 2; \* Q1 *\
也有很多 SQL 会单独使用字段 a 作为查询条件:
select * from t where a = 2; \* Q2 *\
此时,我们可以建立复合索引 index(a,b)。因为不但 Q1 可以利用复合索引,Q2 也可以利用复合索引。
3、最左前缀匹配原则
如果我们使用的是复合索引,应该尽量遵循 最左前缀匹配原则。MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。假如此时我们有一条SQL:
select * from t where a = 1 and b = 2 and c > 3 and d = 4;
那么我们应该建立的复合索引是:index(a,b,d,c) 而不是 index(a,b,c,d)。因为字段 c 是范围查询,当 MySQL 遇到范围查询就停止索引的匹配了。大家也注意到了,其实 a,b,d 在 SQL 的位置是可以任意调整的,优化器会找到对应的复合索引。还要注意一点的是,最左前缀匹配原则不但是复合索引的最左 N 个字段;也可以是单列(字符串类型)索引的最左 M 个字符。例如我们常说的 like 关键字,尽量不要使用全模糊查询,因为这样用不到索引;所以建议是使用右模糊查询:select * from t where name like '李%'(查询所有姓李的同学的信息)。
4、索引下推:很多时候,我们还可以复合索引的 索引下推 来优化 SQL 。例如此时我们有一个复合索引:index(name,age) ,然后有一条 SQL 如下:
select * from user where name like '张%' and age = 10 and sex = 'm';
根据复合索引的最左前缀匹配原则,MySQL 匹配到复合索引 index(name,age) 的 name 时,就停止匹配了;然后接下来的流程就是根据主键回表,判断 age 和 sex 的条件是否同时满足,满足则返回给客户端。
但是由于有索引下推的优化,匹配到 name 时,不会立刻回表;而是先判断复合索引 index(name,age) 中的 age 是否符合条件;符合条件才进行回表接着判断 sex 是否满足,否则会被过滤掉。那么借着 MySQL 5.6 引入的索引下推优化 ,可以做到减少回表的次数。
5、覆盖索引:很多时候,我们还可以覆盖索引来优化SQL。
情况一:SQL 只查询主键作为返回值。主键索引(聚簇索引)的叶子节点是整行数据,而普通索引(二级索引)的叶子节点是主键的值。所以当我们的 SQL 只查询主键值,可以直接获取对应叶子节点的内容,而避免回表。
情况二:SQL 的查询字段就在索引里。复合索引:假如此时我们有一个复合索引 index(name,age) ,有一条 SQL 如下:
select name,age from t where name like '张%';
由于是字段 name 是右模糊查询所以可以走复合索引,然后匹配到 name 时,不需要回表,因为 SQL 只是查询字段 name 和 age,所以直接返回索引值就 ok 了。
6、普通索引
尽量 使用普通索引 而不是唯一索引。首先,普通索引和唯一索引的查询性能其实不会相差很多;当然了,前提是要查询的记录都在同一个数据页中,否则普通索引的性能会慢很多。但是,普通索引的更新操作性能比唯一索引更好;其实很简单,因为普通索引能利用 change buffer 来做更新操作;而唯一索引因为要判断更新的值是否是唯一的,所以每次都需要将磁盘中的数据读取到 buffer pool 中。
7、前缀索引
我们要学会巧妙的使用 前缀索引,避免索引值过大。例如有一个字段是 addr varchar(255),但是如果一整个建立索引 [ index(addr) ],会很浪费磁盘空间,所以会选择建立前缀索引 [ index(addr(64)) ]。建立前缀索引,一定要关注字段的区分度。例如像身份证号码这种字段的区分度很低,只要出生地一样,前面好多个字符都是一样的;这样的话,最不理想时,可能会扫描全表。前缀索引避免不了回表,即无法使用覆盖索引这个优化点,因为索引值只是字段的前 n 个字符,需要回表才能判断查询值是否和字段值是一致的。
怎么解决?倒序存储:像身份证这种,后面的几位区分度就非常的高了;我们可以这么查询:
select field_list from t where id_card = reverse('input_id_card_string'
增加 hash 字段并为 hash 字段添加索引。
8、干净的索引列:索引列不能参与计算,要保持索引列“干净”。假设我们给表 student 的字段 birthday 建立了普通索引。下面的 SQL 语句不能利用到索引来提升执行效率:
select * from student where DATE_FORMAT(birthday,'%Y-%m-%d') = '2020-02-02';
我们应该改成下面这样:
select * from student where birthday = STR_TO_DATE('2020-02-02', '%Y-%m-%d');
9、扩展索引
我们应该尽量扩展索引,而不是新增索引,一个表最好不要超过5个索引;一个表的索引越多,会导致更新操作更加耗费性能。
关于建立索引优化SQL的方案就说到这里了,更多精彩内容尽在博学谷资讯栏目!
— 申请免费试学名额 —
在职想转行提升,担心学不会?根据个人情况规划学习路线,闯关式自适应学习模式保证学习效果
讲师一对一辅导,在线答疑解惑,指导就业!
相关推荐 更多
推荐几款实用的Google Chrome插件
大名鼎鼎的Chrome,是全世界最优秀的浏览器之一。大家喜爱Chrome的一大原因,肯定离不开丰富的扩展插件,今天小编就为大家推荐几款实用的Google Chrome插件,全方位提升大家的上网体验。
5959
2019-07-11 11:35:06
九款好用的Linux命令行终端工具推荐
作为一个专业的计算机人士,熟练操作Linux命令行是最低的要求。相信许多人每天要在Linux命令行的使用时花大量时间,为了解决大家的问题,本文将向大家推荐可以替代系统自带鸡肋终端软件的工具,它们分别是Tilda、Guake、Yakuake、Terminator、Stjerm、Terminology、TermKit、Wterm、Aterm和Rxvt。
7523
2019-11-08 12:32:04
好用的MySQL管理工具推荐盘点
众所周知,MySQL作为小型关系型数据库管理系统,目前备受喜爱和青睐,因此在世界范围内的中小型网站中被广泛地应用。它的优点有体积不大、快速且安全,开放源码,更重要的是成本不高,因此从降低网站成本的角度考虑,许多中小型企业会使用MySQL存储数据。因此今天本文讲盘点几款好用的MySQL管理工具,比如PhpMyadmin、Navicat、SQLyog等等,下面一一推荐给大家。
5768
2020-02-06 10:06:46
迎复工潮 办公室内口罩戴多长时间?多久更换一次?
口罩佩戴的时间要适宜。医用无纺布口罩持续应用6~8小时,遇紧急情况应及时更换:呼吸困难,口罩有破损或毁坏,口罩与面部无法密合,口罩受污染,染有血渍或飞沫等异物时,曾使用于隔离病房或病患接触,有异味需要及时更换
8111
2020-02-24 15:32:34
培训机构种类繁多该如何选择?
培训机构种类繁多该如何选择?当下社会各类培训机构多如牛毛,就算是想学一个编程或其他,培训机构也非常多,广告打的响当当,但是不知道实质是否良好,更不知道自己的能力到底如何,是否找到一个培训机构,就一定适合自己?更何况我们作为消费者,也想钱花在刀刃上,尽量寻求性价比高的培训机构。
3957
2020-06-02 14:45:56