[大数据]mysql

优化原则：

1）sql优化和索引>数据表结构>系统配置>硬件

2）sql执行顺序

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

1.按照执行顺序调整表连接顺序及字段结构

1）多表关联时小表在前，大表在后（from后的表关联查询是从左到右执行的，第一张表会进行全表扫描）

2）where替换having,havaing中一般用组函数作为过滤条件，where后不能放组函数

3）where字句连接顺序，where自左向右自上而下的解析where子句，因此将条件数据多的放在前边

2.按照索引进行优化

1）了解索引结构

mysql

1.1哈希索引

1.2b+tree索引

聚簇索引? innodb

非聚簇索引? myisam

2）适合建立索引的字段

where 以及group by后连接的字段

离散度大的字段

我们先来看一个重要的属性列的离散度，公式如下：

count(distinct(column_name)) : count(*) 

-- 列的全部不同值个数：所有数据行行数

数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高
?

3）避免索引失效

3.1大量数据下避免模糊查找，尽量后模糊查找

如果必须头模糊则可以选择INSTR（str,substr）进行匹配；

3.2避免使用in,not in范围查找，避免全表的一个扫描

如果时连续数值，可以采用between and 来代替

如果不是连续数据，可以选择exist来进行代替；

3.3避免使用or尽量使用union代替or；

3.4避免对null值字段进行判断，可以将null字段设置为0，对数值进行判断；

3.5避免在条件语句where 后的等号左侧使用运算符表达式、函数操作；

3.6避免where 1=1操作

3.7查询条件不能用不等号，如果必须使用则考虑其他字段；

3.8联合索引，依照最做匹配原则，当语句中存在第一个条件不是联合索引时，会失效；

3.其他场景的sql优化

1）增删改dml进行优化，语句短减少网络传输io

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

2)查询时复杂查询可以使用临时表暂存数据

3）join语句代替子查询，因为不会创建中间表

4）如果没有去重要求尽量使用union all ,因为union all会加上dinstinct做数据唯一性校验，消耗高

5）拆分复杂sql为多个小sql,避免大事务

6）分页查找优化

1.索引（覆盖索引优化）：只需要索引字段就可以查到对应数据

select id from table limit 10000,1;

2.条件语句查询：

select * from table where id>=(select id from table limit 10000,1) limit 100;

聚簇索引和非聚簇索引(通俗,z易懂 言简意赅) - 创天创世纪 - 博客园