mysql技术内幕笔记

1.innodb对于独立子查询，具体执行时会转换成关联子查询然后执行，这个性能是很差的，比如

select * from upload_files where file_time in (select max(file_time) file_time from upload_files group by from_unixtime(file_time,‘%Y%m‘)); 针对2000多条数据要执行14秒才能完成，显然太慢；

直接写成内关联查询 select *,from_unixtime(a.file_time,‘%Y%m‘) tt from upload_files a inner join   (select max(file_time) file_time from upload_files group by from_unixtime(file_time,‘%Y%m‘)) b on a.file_time =b.file_time 就好多了，执行时间只有0.34秒；

直接改成关联查询也可以（多一层）select *,from_unixtime(file_time,‘%Y%m‘) tt from upload_files a  where exists(select * from  (select max(file_time) file_time from upload_files group by from_unixtime(file_time,‘%Y%m‘)) b where a.file_time = b.file_time);这个性能和内关联是一样的。
2.查询每个员工最大订单日期的订单（员工号和订单日期主查询和子查询都必须匹配）：

虽然可以得到正确结果，但性能太差；使用派生表可以避免子查询和外查询过多的比较操作，派生表的方法为：

此方法性能提升很多。如果每个人只想显示一条数据的话则sql语句（需要找出最大的订单号）变为

派生表比子查询的性能要好

但这个不会对执行有任何的影响

3.

因此对于条件列含有null值时，in必须对null值进行过滤才能得出和exists相同的结果，比如select * from site_article_channel where article_id not in (10,12,null)

article_id 不为10,12的数据存在，但not in (10,12,null)的值为false或unknown，故此得不到任何结果

4.

5.natural join可以理解为将两个表相同名称的列进行关联，不需要指明；例如

6.straight join功能上等同于join，它为用户提供了一种方法可以用来控制优化器，

这个情况只适用于多表关联查询时，优化器无法提供正确的优化方案的情况，用户可以强制优化器按用户指定的顺序去关联表

7.self join就是表自己连接自己，self并不会出现

8.unoin（集合操作，垂直操作），mysql支持unoin all和unoin distinct两种集合操作方式，进行集合操作的两张表列数必须相同，如果数据类型不同，数据库会进行隐式转换，同时结果列的名称由第一个表决定；unoin distinct一般写为unoin ，distinct会省略掉；所以对于unoin all中的all则必须带着，用以区分。

9.pivoting：技术是指一种可以把行转换为列的技术，Unpivoting可以看作是pivoting的反向操作，即将列旋转成为行

10.rollup是根据维度在数据结果集中进行聚合操作，其优点是一次可以取得N次group by的结果；对单列执行rollup的情况
此时结果为

比较不添加rollup的情况，最后多了一个汇总行，这在需要汇总数据的时候还是有必要的。

多列执行rollup的情况，
得到结果如下


 

其结果等同于

但性能上要好很多，只需要一次表扫描即可，而后者需要做四次表的扫描；不过需要注意的是rollup不能与order by一起使用，那么与limit一起使用语法上可以但一般没有意义，另外就是对null的处理，如果聚合的列含有null值则可能出错，这时最好进行ifnull（列明，替换值）处理。

11.事务（扁平事务，带保存点的扁平事务，链事务，嵌套事务，其中扁平事务最常用）：

事务 

采用事务对于插入或更新，删除等操作，操作完毕一次提交，比不适用事务的情况性能要好，因为采用事务只写了一次日志，而不不使用的情况则会有多次写日志操作

innodb存储引擎会对操作自动提交，有时这不是个好事情，对于事务开启后这个自动功能是会禁用的，等事务结束系统会自动打开；对于“事务要短小，不能有长时间运行的大事务”这条在mysql 的innodb上不适用。

12.辅助索引一般体积比聚集索引小很多（它不包含所有的列），如果查询条件可以命中辅助索引性能一般会更好，此时不需要使用聚集索引，称为覆盖索引。

在某些特殊的情况下可以通过查询语句后面跟force  index（索引名）强制查询采用某个索引，例如
；
例如
force则不同，他是强制的

13.分区：innodb，myisam以及ndb三种引擎都支持分区，csv等就不支持分区

局部分区：数据和相关索引存放在一个分区内

全局分区：数据存放在各个分区内，索引单独存放

水平分区：按行进行分区，同一个行肯定在一个分区内

垂直分区：按列分区，同一个列肯定在一个分区内

目前mysql仅支持局部分区，并且仅支持水平分区不支持垂直分区，mysql的分区分为四种类型，

• range（属于某个条件连续区间的数据放入一个分区），
• list（面向离散的值），
• hash（根据用户自定义的表达式的值来进行分区，返回值不能为负数），
• key（根据数据库的散列函数来进行分区）

不论那种类型的分区，如果表存在主键或唯一索引，那么分区列必须是他们的一部分；如果没有主键或唯一索引，那么任何列都可以作为分区列。

注意：对于range类型分区，如果按年分区的话，查询条件是日期，那么依然会扫描所有分区，得不到应有的效果，如果采用
这种分区才是合适的，因此分区要考虑实际的使用场景。最好查询条件和分区标准一致。

list类型分区与让个类似，只是采用了values in（1,2,3等类似的散列值）；

对于插入不符合分区的值时，range和list分区类型都一样，会报错，无法插入；如果一次插入多条数据，对于不同的引擎又有区别：

innodb会将多个插入是为一个事务，遇到错误，整体回滚；MyIsam则不同，错误前的插入，错误后的无法插入，其实还是本质上对事务是否支持的区别。

mysql5.5以前的版本，分区的条件必选是整型值（如果不是必选通过函数转换成整型值）；5.5以后则可以用整型值，时间以及字符串等字段作为条件。

子分区：如果要创建子分区，则所有分区上的子分区个数必须相同并且名称不能重复。

Null值：mysql将null值视为比任何非null值都小，分区时也一样；

B+树可以很好的完成大数据表的操作，不需要分区的帮助，相反分区如果不合理，则还会降低性能；相对而言，分区的技术更适合于olap的业务，而非oltp的业务。

一般来讲，对于符合分区条件的查询才会很快，对于不符合条件的则查询成本反而会成倍增长。