SQL优化

select a.MemberID,a.MemberName,a.MemberPhone
    from Member a,Member_Tmep b 
    where (a.MemberName = b.MemberName or a.MemberPhone = b.MemberPhone) and a.MemberID <> b.MemberID

使用"union all"的性能比"union"更高。使用"union"而不是"union all"，是因为“会员姓名相同但ID不同的记录”和“会员电话相同但ID不同的记录”可能有重复，使用"union"可以去掉重复的记录。

--查询出会员姓名相同但ID不同的记录
select a.MemberID,a.MemberName,a.MemberPhone
    from Member a
    inner join Member_Tmep b on a.MemberName = b.MemberName and a.MemberID <> b.MemberID

union

--再查询出会员电话相同但ID不同的记录，进行合并
select a.MemberID,a.MemberName,a.MemberPhone
    from Member a
    inner join Member_Tmep b on a.MemberPhone = b.MemberPhone and a.MemberID <> b.MemberID

查询出会员姓名为“张三”、“李四”的记录。

select * from Member where MemberName = '张三' 
union all
select * from Member where MemberName = '李四'

4. 尽量避免进行null值的判断，查询中回表数量太多导致放弃索引进行全表扫描。

优化方式：可以给字段添加默认值0，对0值进行判断。

is null、is not null、!= 、<>

查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值可以简单得记忆为20%左右。

5.尽量避免在where条件中等号的左侧进行表达式、函数操作。

可以将表达式、函数操作移动到等号右侧。

6. 当数据量大时，避免使用where 1=1的条件。

优化方式：

在mybatis中常用到if标签判断where子句后的条件，为防止首字段为空导致sql报错。

<select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(id) from t_book t where 1=1
<if test="title !=null and title !='' ">
 AND title = #{title} 
</if> 
<if test="author !=null and author !='' ">
 AND author = #{author}
</if> 
</select>

用where标签代替

 <select id="queryBookInfo" parameterType="com.ths.platform.entity.BookInfo" resultType="java.lang.Integer">
 select count(*) from t_book t
<where>
<if test="title !=null and title !='' ">
 title = #{title} 
</if>
<if test="author !=null and author !='' "> 
 AND author = #{author}
</if>
</where> 
</select>

7. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。

select col1 from table where key_part2=1 and key_part3=2

8. 隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123;

二、SELECT语句其他优化

1. 避免出现select *

SELECT DISTINCT FROM JOIN ON
WHERE GROUP BY
WITH
HAVING
ORDER BY

2.多表关联查询时，小表在前，大表在后。

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。

3. 用where字句替换HAVING字句

HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤。

where和having的区别：where后面不能使用组函数

4.调整Where字句中的连接顺序

MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

三、增删改 DML 语句优化

1. 大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句，一般情况下批量插入效率有几倍的差别。

Insert into T values(1,2),(1,3),(1,4);

2. 适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

6. 使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。

使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。

7. 使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率针对展现等分页需求，合适的分页方式能够提高分页的效率。

案例1：

select * from t where thread_id = 10000 and deleted = 0 
order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
order by gmt_create asc limit 0, 15) a, t 
where a.id = t.id;

事务占用的undo数据块；
事务在redo log中记录的数据块；
释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

3. 避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，在MySQL中可以通过变量实现。

四、查询条件优化

1. 对于复杂的查询，可以使用中间临时表暂存数据；

2. 优化group by语句

默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，如 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;
3. 优化join语句
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。有些情况下，子查询可以被更有效率的连接(JOIN)..替代。
4. 优化union查询
MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10
UNION ALL
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效：SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10
UNION
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

5.拆分复杂SQL为多个小SQL，避免大事务
简单的SQL容易使用到MySQL的QUERY CACHE；
减少锁表时间特别是使用MyISAM存储引擎的表；
可以使用多核CPU。

五、建表优化

1. 在表中建立索引，优先考虑where、order by使用到的字段。

2. 尽量使用数字型字段（如性别，男：1 女：2）

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

3. 查询数据量大的表会造成查询缓慢。

扫描行数过多，分段分页进行查询，循环遍历，将结果合并处理进行展示。要查询100000到100050的数据，如下：

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,*
FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

4. 用varchar/nvarchar 代替 char/nchar

变长字段存储空间小，可以节省存储空间，其次，在一个相对较小的字段内搜索效率显然要高些。
不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

六、索引

1、索引

索引会占用磁盘空间，会降低更新表的效率。因为在更新数据时，要额外维护索引文件。

2、索引类型

聚簇索引： 索引列的值必须是唯一的，并且不能为空，一个表只能有一个聚簇索引。

如果表设置了主键，则主键就是聚簇索引，如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引，以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引。
InnoDB的聚簇索引的叶子节点存储的是行记录（其实是页结构，一个页包含多行数据）。

唯一索引 ：索引列的值是唯一的，值可以为空。

普通索引：没有什么限制，允许在定义索引的列中插入重复值和空值。

普通索引也叫二级索引，笼统的讲，除聚簇索引外的索引，即非聚簇索引。InnoDB的普通索引叶子节点存储的是主键（聚簇索引）的值，而MyISAM的普通索引存储的是记录指针。

复合索引 ：也叫组合索引，用户可以在多个列上组合建立索引，遵循“最左匹配原则”，在条件允许的情况下使用复合索引可以替代多个单列索引的使用。

回表查询
先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

索引覆盖
只需要在一棵索引树上就能获取SQL所需的所有列数据（select 后面查询的字段都可以从这个索引的树中获取），无需回表，速度更快。

select id,age,name from user where age = 10;

explain分析：age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值。此时的Extra列的NULL表示进行了回表查询

为了实现索引覆盖，需要建组合索引idx_age_name(age,name)

drop index idx_age on user;
create index idx_age_name on user(`age`,`name`);

explain分析：此时字段age和name是组合索引idx_age_name，查询的字段id、age、name的值都在索引树上，只需扫描一次组合索引B+树即可，这就是实现了索引覆盖，此时的Extra字段为Using index表示使用了索引覆盖。

七、分析sql

1、type (从表中找出目标行的方式，访问类型)

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
至少达到range级别，最好能达到ref

1.system：表只有一行记录（等于系统表），这是const类型的特例
2.const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。
3.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
5.range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。
6.index：Full Index Scan，index与ALL区别为index类型只遍历索引树。比ALL块，因为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）。
7.ALL：Full Table Scan，遍历全表以找到匹配的行。

2、key: 实际使用的索引

3、key_len

索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得的，不是通过表内检索出的

4、Extra

Extra中的为空的情况,查询列存在未被索引覆盖&&where筛选列是索引的前导列，意味着通过索引超找并且通过“回表”来找到未被索引覆盖的字段

Xability

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL优化

当内查询的表小时（小于外查询的表），用in效率高。在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
复制链接

扫一扫