索引
每当后端技术人员讲起SQL
的调优时,第一个想到的方案往往是索引。先举个最简单的例子,这里在tb
表中给字段tb_name
加上普通的索引,由此根据该字段进行SELECT
查询时就无需进行全表遍历,以加快查询速度
CREATE INDEX tb_tb_a ON tb (tb_a);
SELECT tb_id FROM tb WHERE tb_a='Sherry';
以下几种情况将无法使用索引:
-
使用
OR
时
带有OR
操作的语句即使其中部分带了索引字段的也不会使用,以下SELECT
操作是使用不了索引的,所以SQL
要尽量少用OR
操作CREATE INDEX tb_tb_a ON tb (tb_a); SELECT tb_id FROM tb WHERE tb_a='Sherry' OR tb_b='Billy';(索引失效)
如果要想使用
OR
并且让索引生效,只能将OR
条件里每个相关列都加上索引!!! -
使用多列索引时
CREATE INDEX tb_tb_a_tb_b ON tb (tb_a,tb_b);
这个多列索引本质上是创建了两个索引,分别是
tb_a
和tb_a_tb_b
(可理解为字段最左部的某连续部分),则对以下SELECT
语句产生不同的结果SELECT tb_id FROM tb WHERE tb_a='Sherry';(索引生效) SELECT tb_id FROM tb WHERE tb_b='Sherry';(索引失效) SELECT tb_id FROM tb WHERE tb_a='Sherry' AND tb_b='Billy';(索引生效)
多列索引
tb_tb_a_tb_b
和分开对tb_a
、tb_b
字段创建两个独立的索引的区别是多列索引能顺序地利用所包含的字段索引,而分开创建的索引则会选择最严格(可以理解为所选出结果集最小的索引)的索引来进行检索,其他相关的索引也不会被使用,故效果不如多列索引。另外建立多列索引时,需要注意索引所用字段的顺序,应将最严格的索引放在最前面使索引产生更好的效果 -
使用
LIKE
接以%
开头的字符串时CREATE INDEX tb_tb_a ON tb (tb_a); SELECT tb_id FROM tb WHERE tb_a LIKE 'She%';(索引生效) SELECT tb_id FROM tb WHERE tb_a LIKE '%rry';(索引失效)
上面这种失效的情况下,可以使用另一种方式
SELECT tb_id FROM tb WHERE REVERSE(tb_a) LIKE 'yrr%';(索引生效)
即对所需查询的字段做一次翻转然后再进行
LIKE
操作,就可以达到利用索引的目的,不过这里又涉及到在SQL
中使用函数的问题可能影响效率,因此最好对实际情况进行测试而决定使用方式,但这种方式不适用于LIKE '%xxx%'
之类的SQL
调优 -
列是字符串类型时
假设字段tb_a
是string
类型CREATE INDEX tb_tb_a ON tb (tb_a); SELECT tb_id FROM tb WHERE tb_a='123456';(索引生效) SELECT tb_id FROM tb WHERE tb_a=123456;(索引失效)
字符串字段的查询参数不加引号时虽然在某些情况下能查询成功,但并不能利用到已创建的索引
字段
-
SELECT
语句中所提取的字段尽量少,一般只取出需要的字段,千万不要为了方便编写SQL
语句而使用以下类似做法SELECT * FROM tb WHERE tb_gender=0;
当你读取出来的记录量很大时更要禁止这种做法,这就是为什么我在本篇文章中写的
SQL
都是SELECT tb_id
之类来作为例子,而不是SELECT *
,这个读者可以亲测,即使你数据库的数据量不是很多,你也能发现当你SELECT *
和SELECT tb_id
时的耗时差别有多大(PS:某次项目经历中我就因为这个问题导致两个SQL
的耗时分别是 1100ms 和 200ms) VARCHAR
类型的字段长度在尽量合理范围内分配,无需分配过多尽量使用
TINYINT
、SMALLINT
、MEDIUM_INT
作为整数字段类型而不是INT
设计允许的情况下,尽量将字段能否为
NULL
属性设置为NOT NULL
,否则将可能导致引擎放弃使用索引而进行全表扫描
连接表
有时候为了取到多个表的字段,编写SQL
时会使用一次甚至多次JOIN
操作,在进行多表连接时应使各个表的数据集尽量少,举个例子,比如现在tb1
表数据量很大
SELECT tb1.tb1_name FROM tb1 LEFT JOIN tb2 ON tb2.tb2_otherid=tb1.tb1_id WHERE tb1.tb1_gender=0;
上面语句JOIN
操作时会进行tb1
、tb2
两个表所有数据集连接操作,为了减小连接操作的数据集,可将其改为
SELECT tb1.tb1_name FROM (SELECT tb1.tb1_id, tb1.tb1_name FROM tb1 WHERE tb1.tb1_gender=0) AS tb1 LEFT JOIN tb2 ON tb2.tb2_otherid=tb1.tb1_id;
这样一来,JOIN
左边的数据集就仅仅是tb1_gender=0
筛选出来的数据集而不是tb1
所有数据集,从而提高了JOIN
操作的执行速度。要注意一点是,JOIN
操作的查询效率要比子查询高得多,所以可以使用JOIN
操作的情况下尽量减少或杜绝子查询操作
计算操作
尽量避免在SQL
的JOIN
和WHERE
部分使用计算操作,因为大多数涉及到在SQL
中计算操作的情况往往会使索引失效而进行了全表遍历操作或者加大了数据库的负担,而这些本来是可以放到业务服务器上进行处理的,如
CREATE INDEX tb_tb_time ON tb (tb_time);
SELECT tb_id FROM tb WHERE YEAR(tb_time)='2012';(调用YEAR函数本质上也是计算操作)
这种情况不仅不能利用索引,还会给数据库带来更大的计算负担,而这种情况几乎不需要给业务服务器带来更大负担就可以进行优化,只需要将SQL
修改为
SELECT tb_id FROM tb WHERE tb_time BETWEEN '2012-01-01 00:00:00' AND '2012-12-31 23:59:59'
即可
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。