MySQL个人复习总结

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

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

SQL的优化方式有哪些？
PostgreSQL和MySQL之间的一些对比
Mysql中MyISAM和innoDB引擎的区别
执行一条 select 语句,期间发生了什么？
InnoDB如何存储数据？为什么选择B+树作为索引数据结构？
使用 like “%x“，索引一定会失效吗？
Count(\*)、Count(1)、Count(主键)、Count(字段)性能?如何优化Count(\*)？
简单介绍一下MVCC机制和Read View。MySQL 可重复读隔离级别，完全解决幻读了吗？如何避免？
一条update 语句的执行过程？
为什么需要二阶段提交？它的原理是什么？
三种日志的作用？redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？
持续更新ing

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叶子节点上，保证了数据按主键顺序存放。使用自增主键可以高效插入数据，避免数据移动和页分裂，保持索引紧凑。非自增主键可能导致插入时数据移动和页分裂，影响性能和空间利用