【MYSQL的SQL优化】

MYSQL语句的优化

工作多年，对MYSQL的SQL语句优化的一些心得。持续更新中。

添加操作的优化

针对于INSERT INTO 语句的优化
1.优化批量插入写法（减少SQL语句的分析次数、减小redo log、undo log、bin log等日志从而减少网络IO开销和减少日志刷盘）
eg：
insert into table (A, B) values (‘a0’, ‘b0’);
insert into table (A, B) values (‘a1’, ‘b1’);
insert into table (A, B) values (‘a2’, ‘b2’);
优化为：
insert into table (A, B) values (‘a0’, ‘b0’),(‘a1’, ‘b1’),(‘a2’, ‘b2’);
2.有序插入（数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本）
插入记录在主键上是有序排列
eg:
insert into table (id, B) values (‘3’, ‘b0’);
insert into table (id, B) values (‘2’, ‘b1’);
insert into table (id, B) values (‘1’, ‘b2’);
优化为：
insert into table (id, B) values (‘1’, ‘b0’);
insert into table (id, B) values (‘2’, ‘b1’);
insert into table (id, B) values (‘3’, ‘b2’);
3.使用on duplicate … update 避免主键冲突
eg：
insert into table (A, B) values (‘a0’, ‘b0’);
优化为：
insert into table (A, B) values (‘a0’, ‘b0’) on duplicate key update B=values(‘b0’);
4.使用insert ignore into 忽略脏数据（需要业务支持）
eg:
insert into table (A, B) values (‘a0’, ‘b0’);
优化为：
insert ignore into table (A, B) values (‘a0’, ‘b0’);
5.破坏可扩展性，使用存储过程进行优化
待续

删除操作的优化

1.delete
delete from table;
delete from table where A = ‘a’; （多表时，建议使用关联删除）
注：
1.1 属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项。
1.2 在事务中可以被回滚。
1.3 只删除表中数据而不删除表的结构(定义)，同时也不释放空间。
因此删除大数据量的表速度会很慢。
2.truncate
truncate table table_name;
注：
2.1 truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
2.2 删除内容、释放空间但不删除表的结构(定义)。
因此清空表数据，建议使用改关键字
3.drop
drop table table_name;
注：
3.1 删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
3.2 drop也属于DDL语言，立即执行，执行速度最快
3.3 删除内容和定义，释放空间。
待续

修改操作的优化

1.使用replace into 进行批量更新
eg:
insert into table (A, B) values (‘a0’, ‘b0’);
insert into table (A, B) values (‘a1’, ‘b1’);
insert into table (A, B) values (‘a2’, ‘b2’);
优化为：
replace into table (A, B) values (‘a0’, ‘b0’),(‘a1’, ‘b1’),(‘a2’, ‘b2’);
2.借助临时表优化更新
2.1 create temporary table new_table_name(ID int(4) primary key,B varchar(50));
2.2 insert into new_table_name values (0,‘a’), (1,‘b’),(2,‘c’);
2.3 update old_table_name, table_name set old_table_name.B=table_name.B where old_table_name.ID=table_name.ID;
注：需要拥有Create权限。
待续

查询的优化

1.使用索引进行优化
索引有关概念以及原理等待续。
1.1单表查询
1.1.1聚簇索引（聚集索引）
针对非空、唯一且不做频繁增删的字段，可以根据业务建立聚簇索引。
优化点：B+树中的所有是按照一定顺序排列的，所以在范围查询查询、分组和排序的时候会提高查询效率。
1.1.2覆盖索引
针对查询字段不多（eg：3个字段）、且经常用作查询条件的字段，可以根据业务建立合适的覆盖索引。
优化点：根据全职匹配，尽量避免回表查询，从而提高查询效率。
1.1.3联合索引
针对那些被用作查询条件并且被用于分组或者排序的字段，可以根据业务建立合适的联合索引。
优化点：根据最佳左前缀法则，尽量避免回表查询，从而提高查询效率。
1.1.4前缀索引
针对字符串字段，当需要作为查询条件时，可以建立合适的前缀索引。
优化点：虽然前缀索引是一定会做回表操作的（因此无法使用覆盖索引对前缀索引进行优化）。但是针对与具体业务，例如邮箱的检索，是有非常棒的优化效果的，因为邮箱信息，@之前的数据一般不会有太多的相似之处，因此回表的次数并不多。
1.1.5全文索引
全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。通过倒排索引来实现。倒排索引也是一种数据结构，是通过利用关联数组实现的。（目前使用的少，大部分是使用ES替代。）
match … as 自然的全文索引
match … in 布尔全文索引
match … with 查询扩展收索
优化点：用于like '%xxx%'进行模糊查询时。
1.2多表查询
1.2.1 合理使用内连接、外连接（左关联、右关联）
优化点：根据业务需求使用合理的连接，并为连接字段建立联合索引。
1.2.2 合理使用UNION 和 UNION ALL
优化点：因为union存在一个中间的临时表，所以，从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。
1.2.3 做子查询优化
优化点：以小表驱动大表查询，做好子查询的优化，从而优化整个多表查询的效率。

1.3索引容易失效的注意点
1.3.1索引列上做计算：索引列上做任何操作（计算、函数、(自动 or 手动)类型转换「尤其注意 varchar 和 int」），会导致索引失效而转向全表扫描。
优化点：
表达式应对结果操作而非查询字段，
eg：select id from t where num/2=100
优化为： select id from t where num=100*2
使用关联替代or，
eg：select id from t where num=100 or num = 0
优化为： （select id from t where num=100）union (select id from t where num=0)
避免隐式转换
1.3.2索引列上有范围查询：有可能会破坏最左匹配原则。
优化点：联合索引中，将可能做范围查询的字段，放在联社索引的联合字段的在最后边。
1.3.3使用不等于（!=以及 <>）
优化点：查询数量不多的情况下使用between（优先考虑） 或者 in 替代。
1.3.4使用is not null区分数据是否为null
优化点：使用 is null 替代 is not null。
1.3.5使用in
优化点：使用 exists 代替 in 。
eg：
select num from a where num in(select num from b)
优化为：
select num from a where exists(select 1 from b where num=a.num)
1.3.6表查询 rows 超过总行数 30% 时索引失效
优化点：分页查询、重新设定索引。

2.读写分离
基本的原理是让主数据库处理事务性增、删、改操作（INSERT、DELETE、UPDATE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
主机：bin log - statment、row、mixed
从机：raplay log（中继日志-回放日志）
待续。
3.分表分库
3.1垂直分片
按照业务来对数据进行分片，又称为纵向分片。他的核心理念就是专库专用。在拆分之前，一个数据库由多个数据表组成，每个表对应不同的业务。而拆分之后，则是按照业务将表进行归类，分布到不同的数据库或表中，从而将压力分散至不同的数据库或表。（无法根治性能瓶颈）
3.2水平分片
通过某个字段(或某几个字段)，根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。
3.3总结
常用的分片策略有：
取余\取模 ： 优点 均匀存放数据，缺点 扩容非常麻烦
按照范围分片 ： 比较好扩容， 数据分布不够均匀
按照时间分片 ： 比较容易将热点数据区分出来。
按照枚举值分片 ： 例如按地区分片
按照目标字段前缀指定进行分区：自定义业务规则分片
水平分片从理论上突破了单机数据量处理的瓶颈，并且扩展相对自由，是分库分表的标准解决方案。
3.4分库分表的缺点
虽然数据分片解决了性能、可用性以及单点备份恢复等问题，但是分布式的架构在获得收益的同时，也引入了非常多新的问题。
事务一致性问题
原本单机数据库有很好的事务机制能够帮我们保证数据一致性。但是分库分表后，由于数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题。跨节点关联查询问题
在没有分库时，我们可以进行很容易的进行跨表的关联查询。但是在分库后，表被分散到了不同的数据库，就无法进行关联查询了。这时就需要将关联查询拆分成多次查询，然后将获得的结果进行拼装。
跨节点分页、排序函数
跨节点多库进行查询时，limit分页、order by排序等问题，就变得比较复杂了。需要先在不同的分片节点中将数据
进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序。这时非常容易出现内存崩溃的问题。
主键避重问题
在分库分表环境中，由于表中数据同时存在不同数据库中，主键值平时使用的自增长将无用武之地，某个分区数据库生成的ID无法保证全局唯一。因此需要单独设计全局主键，以避免跨库主键重复问题。
公共表处理
实际的应用场景中，参数表、数据字典表等都是数据量较小，变动少，而且属于高频联合查询的依赖表。这一类表一般就需要在每个数据库中都保存一份，并且所有对公共表的操作都要分发到所有的分库去执行。
运维工作量
面对散乱的分库分表之后的数据，应用开发工程师和数据库管理员对数据库的操作都变得非常繁重。对于每一次数据读写操作，他们都需要知道要往哪个具体的数据库的分表去操作，这也是其中重要的挑战之一。
待续。