目录
4，减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu内存占用
5，利用更多资源：使用表分区，可以增加并行操作，最大限度利用cpu资源
一，MySQL优化的五大原则：
1，减少数据的访问：通过索引访问减少磁盘的I/O读写操作
2，返回更少的数据：只需要返回需要的字段
3，减少交互次数：批量DML操作，减少数据库连接次数
4，减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu内存占用
5，利用更多资源：使用表分区，可以增加并行操作，最大限度利用cpu资源
总结到SQL优化的三点：
- 最大化利用索引
- 尽可能避免全表扫描
- 减少无效的数据查询
二，SQL优化策略
1，有效建立索引
- 主键
- 频繁作为查询的字段要建立索引
- join的连接字段建立索引
- 经常排序和分组的字段（group by会首先进行分组操作）因为索引本身就是有序的
- 关联字段建立联合索引
- 更新频繁的字段不能创索引
2，索引失效的场景
- 在字段开头进行模糊查询，这会导致数据库引擎放弃索引进行全表扫描（最左前缀原理类似）
- 联合索引，没有使用第一列索引
- 数据类型隐式转换：（默认转换成int型，这样会导致索引失效）
- 使用or前后没有同时使用索引（因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描）。
- 索引列进行计算和函数操作，会导致引擎放弃索引，进行全表扫描（因为索引保存的是索引字段的原始值，而不是经过函数变换的值）优化：可以将表达式、函数操作移动到等号右侧。
- 单个范围条件查询（<,>,between）可以命中索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。（与最左前缀同理）
- 负向条件查询可能不能使用索引，可以优化为in查询，其中负向条件有：not in，!= ,not like
2，SELECT语句其他优化
- 避免出现select *
取出全部列，会让优化器无法完成索引覆盖扫描
- 避免出现不确定的结果
使用函数很容易导致主库与从库相应的数据不一致
- 多表查询时，小表在前，大表在后。
在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了
- 调整where字句中的连接顺序
M有SQL采用从左往右，自上而下的顺序解析where字句。
3，增删改DML语句优化
- 大批量插入数据
- 适当使用commit
4，查询条件优化
- 对于复杂的查询，可以使用中间临时表暂存数据
- 优化group by语句
默认情况下，Mysql会对group by分组的所有值进行排序，如果不想排序，可以指定ORDER BY NULL禁止
- 优化join语句
- 优化union查询
MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
- 拆分复杂SQL为多个小SQL，避免大事务
简单的sql容易用到查询缓存
- 使用truncate代替delete
当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。
使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零。
- 使用合理的分页方式以提高效率
5，建表优化
- 在表中建立索引，优先考虑where，order by使用到的字段
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，还会增加存储开销。
- 用varchar代替char
节约存储空间，并且查询效率更高
Explain分许SQL性能：
所有包含的字段：id,select type,table,type,possible keys, key,key_len, ref(显示查找索引所用到的那些列和常量), rows, Extra
id：表示嵌套语句中，各语句的执行顺序，值越大优先级越高。
最重要的：1,Type:访问类型，上图中到下依次变差。
MVCC：
是多版本控制机制，是一种并发控制的方法。通过快照读的方式来实现并发控制，也就是说事务内部看到的数据不受其他事务的影响，。避免了加锁，极大提高了并发能力。
多版本并发控制 的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。
内部原理：主要是基于记录的 隐藏字段，undo log，read view来实现。
MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。
SQL执行很慢的可能原因：
- SQL问题（没有建立索引，建立了但没用到索引）
- 同步阻塞
- 数据库在同步log文件到磁盘
扫一扫
173万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
