SQL语句及索引的优化
1. 尽量避免使用子查询
例:SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = 'chackca');
其子查询在Mysql5.5版本里,内部执行计划是这样:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会非常慢。
在MariaDB10/Mysql5.6版本里,采用join关联方式对其进行了优化,这条SQL语句会自动转换为:
SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id
但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE子查询无效,固生产环境应避免使用子查询
由于MySQL的优化器对于子查询的处理能力比较弱,所以不建议使用子查询,可以改写成Inner Join,之所以 join 连接效率更高,是因为 MySQL不需要在内存中创建临时表
2. 用IN来替换OR
低效查询:SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
高效查询:SELECT * FROM t WHERE id IN (10,20,30);
另外,MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:
select id from table_name where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。
3. 读取适当的记录LIMIT M,N,而不要读多余的记录
select id,name from t limit 866613, 20
使用上述sql语句做分页的时候,可能有人会发现,随着表数据量的增加,直接使用limit分页查询会越来越慢。
对于 limit m, n 的分页查询,越往后面翻页(即m越大的情况下)SQL的耗时会越来越长,对于这种应该先取出主键id,然后通过主键id跟原表进行Join关联查询。因为MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。
优化的方法如下:可以取前一页的最大行数的id(将上次遍历到的最末尾的数据ID传给数据库,然后直接定位到该ID处,再往后面遍历数据),然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。sql可以采用如下的写法:
select id,name from table_name where id> 866612 limit 20
4. 禁止不必要的Order By排序
如果我们对结果没有排序的要求,就尽量少用排序;
如果排序字段没有用到索引,也尽量少用排序;
另外,分组统计查询时可以禁止其默认排序
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默认情况下,Mysql会对所有的GROUP BT col1,col2…的字段进行排序,也就是说上述会对 goods_id进行排序,如果想要避免排序结果的消耗,可以指定ORDER BY NULL禁止排序:
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL
5. 总和查询可以禁止排重用union all
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的CPU运算,加大资源消耗及延迟。当然,union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。
6. 避免随机取记录
SELECT * FROM t1 WHERE 1 = 1 ORDER BY RAND() LIMIT 4;
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
以上两个语句都无法用到索引
7. 将多次插入换成批量Insert插入
INSERT INTO t(id, name) VALUES(1, 'aaa');
INSERT INTO t(id, name) VALUES(2, 'bbb');
INSERT INTO t(id, name) VALUES(3, 'ccc');
—>
INSERT INTO t(id, name) VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc');
8. 只返回必要的列,用具体的字段列表代替 select * 语句
SELECT * 会增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前者也需要经常更新。所以要求直接在select后面接上字段名。
9. 区分in和exists
select * from 表A where id in (select id from 表B)
//上面的语句相当于:
select * from 表A where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
另外,in查询在某些情况下有可能会查询返回错误的结果,因此,通常是建议在确定且有限的集合时,可以使用in。如 IN (0,1,2)。
10. 优化Group By语句
1、如果对group by语句的结果没有排序要求,要在语句后面加 order by null(group 默认会排序);
2、尽量让group by过程用上表的索引,确认方法是explain结果里没有Using temporary 和 Using filesort;
3、如果group by需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大tmp_table_size参数,来避免用到磁盘临时表;
4、如果数据量实在太大,使用SQL_BIG_RESULT这个提示,来告诉优化器直接使用排序算法(直接用磁盘临时表)得到group by的结果。
5、使用where子句替换Having子句:避免使用having子句,having只会在检索出所有记录之后才会对结果集进行过滤,这个处理需要排序分组,如果能通过where子句提前过滤查询的数目,就可以减少这方面的开销。
低效: SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效: SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP by JOB
索引的类型
UNIQUE唯一索引
不可以出现相同的值,可以有NULL值。
INDEX普通索引
允许出现相同的索引内容。
PRIMARY KEY主键索引
不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引。
fulltext index 全文索引
上述三种索引都是针对列的值发挥作用,但全文索引,可以针对值中的某个单词,比如一篇文章中的某个词,然而并没有什么卵用,因为只有myisam以及英文支持,并且效率让人不敢恭维,但是可以用coreseek和xunsearch等第三方应用来完成这个需求。
索引的CURD
索引的创建
ALTER TABLE
适用于表创建完毕之后再添加。
ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)索引名
ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名,可要可不要;如果不要,当前的索引名就是该字段名。
ALTER TABLE `table_name` ADD UNIQUE (`column_list`)
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`)
ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)
CREATE INDEX
CREATE INDEX可对表增加普通索引或UNIQUE索引。
--例:只能添加这两种索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
索引的删除
DROP INDEX `index_name` ON `talbe_name`
ALTER TABLE `table_name` DROP INDEX `index_name`
-- 这两句都是等价的,都是删除掉table_name中的索引index_name;
ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引,注意主键索引只能用这种方式删除
索引的查看
show index from tablename;