MySQL个人复习总结

最近想把MySQL的知识点再过一遍，带着自己的理解使用简短的话把一些问题总结一下，尤其是开发中和面试中的高频问题，基础知识点可以参考之前写的如下几篇博客，这篇不再赘述，阅读顺序由浅入深依次递进。

一、MySQL 概述 数据库&表操作 数据增删改；
二、MySQL 单表查询 多表设计；
三、MySQL 多表查询 事务 索引；
四、Mybatis入门；
五、Mybatis—基础操作；
六、Mybatis—XML配置文件、动态SQL；
七、MySQL窗口函数入门指南

SQL的优化方式有哪些？

慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。

• 避免查询不必要的列。SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像select *这种写法应该尽量避免，浪费CPU资源，也可能导致索引失效。PS：能走索引覆盖就走索引覆盖，避免回表,下面也会提到。

• 分页优化。在数据量比较大，分页比较深的情况：limit 100000,10(此时会读到第100000条数据然后读取10条返回，抛弃前面的100000条)，要考虑深分页的优化。有两种解决思路
• • 1.延迟关联。延迟关联优化的核心思想是通过主键提取数据行，避免在原始表上进行扫描和跳过行的操作。先通过where 条件提取出主键，这里只查id可以走覆盖索引，再将该表与原数据表关联，通过主键 id提取数据行，直接走了主键索引而不是通过原来的二级索引提取数据行。比如原来是select * from table where type = 2 and level = 9 order by id asc limit 100000,10;
    那么现在是select a.* from table a,
    (select id from table where type = 2 and level = 9 order by id asc limit 100000,10 ) b where a.id = b.id;
• • id偏移量。偏移量就是找到limit第一个参数对应的主键值，根据这个主键值再去过滤并limit，比如：
    select * from table where id >
    (select * from table where type = 2 and level = 9 order by id asc limit 190);

• 索引优化
• • 利用索引覆盖，减少回表
• • 避免使用or查询。MySQL 5.0之前的版本要尽量避免使用or查询，可以使用union或者子查询来替代，早期的版本使用or查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题，不过建议在实际使用中还是规范写法，能不用就少用。
• • 避免使用!=或者<>操作符。SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引
    解决方法:通过把不等于操作符改成or，可以使用索引，避免全表扫描。id <> ‘aaa’ ===> id > ‘aaa’ or id < ‘aaa’
• • 适当使用前缀索引，可以降低索引空间方用，提高索引的查询效率。
    比如，邮箱的后缀都是固定的“@x.com”，那么类似这种后面几位为固定值的字段，前面那一部分数据就非常适合定义为前缀索引，因为后面都是冗余数据，截取后做索引的话单个节点就能存放更多的索引值了。
    需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和 group by 操作，也无法作为覆盖索引。
• • 避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率。
• • 正确使用联合索引。使用联合索引的时候，注意最左匹配原则。
• • 对于WHERE条件，GROUP BY，ORDER BY里用不到的字段；字段中存在大量重复数据(性别男、女);表数据太少的时候；经常更新的字段；这些都避免去创建索引;
• • 主键索引最好是自增，且主键字段长度应尽量小。InnoDB主键索引默认是聚簇索引，数据存放在B+Tree叶子节点上，保证了数据按主键顺序存放。使用自增主键可以高效插入数据，避免数据移动和页分裂，保持索引紧凑。非自增主键可能导致插入时数据移动和页分裂，影响性能和空间利用