mysql 写入 细节_MySQL 细节

1、MySQL 的联接执行策略 -- “嵌套循环联接”，下面是一个简单的例子：

mysql > SELECT tbl1.col1, tbl2.col2

-> FROM tbl1 INNER JOIN(LEFT OUTER JOIN)tbl2 USING (col3)

-> WHERE tbl1.col1 IN (5, 6)

假设 MySQL 不会改变查询中的顺序，那么 MySQL 执行查询的伪代码大致如下：

outer_iter = iterator over tbl1 where col1 IN (5, 6)

outer_row = outer_iter.next

while outer_row

inner_iter = iterator over tbl2 where col3 = outer_row.col3

inner_row = inner_iter.next

if inner_row

while inner_row

output [ outer_row.col1, inner_row.col2 ]

inner_row = inner_iter.next

end

else

output [ outer_row.col1, NULL ]

end

outer_row = outer_iter.next

end

(注：黄色部分为外联接时的伪代码)

2、在处理 FROM 子句中的子查询时，它会先执行子查询，并且把结果当到临时表里面，然后把临时表当成普通表进行下一步处理。

需要注意的是，临时表没有索引，在决定 FROM 子句中到底是用复杂的联接还是用子查询时要牢记这一点。

MySQL 也使用临时表来处理联合(UNION)。

3、GROUP_CONCAT

mysql> select group_concat(t_id) as t_ids from t_title_url_8 where t_title like '%北京现代%';

+-----------------------------------------------------------------------------------------------------------+

| t_ids                                                                                        |

+-----------------------------------------------------------------------------------------------------------+

| 1052,1102,1192,1193,1194,1247,8973,9041,13807,15831,16748,16822,16845,17881,22314,24367,31020,35751,38707 |

+-----------------------------------------------------------------------------------------------------------+

用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。用如下命令查看默认大小

mysql> show variables like '%group_concat_max%';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| group_concat_max_len | 1024  |

+----------------------+-------+

4、优化 LIMIT 和 OFFSET

分页系统中使用 LIMIT 和 OFFSET 是很常见的，它们通常也会和ORDER BY 一起使用，索引对排序较有帮助，如果没有索引就需要大量文件排序。

一个常见的问题是偏移量很大，例如查询使用了 LIMIT 10000,20，它就会产生10020行数据，并且丢掉前10000行。

一个提高效率的简单技巧就是在覆盖索引上进行偏移，而不是对全行数据进行偏移。

例子: mysql > SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50,5

如果表非常大，这个查询最好写成下面这个样子：

mysql >

SELECT film.film_id, film.discription from sakila.film  INNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50,5) as tn_film USING (film_id)

sakila.film表中film_id 是主键，title字段有索引。

5、优化联合

重要的是始终使用 UNION ALL，除非需要服务器消除重复的行。如果忽略了ALL关键字，MYSQL就会向临时表添加distinct选项，它会利用所有的行来

决定数据的唯一性，这种操作开销很大。但是要知道ALL不会删除临时表，MYSQL总是把结果放在临时表中，然后再把它们读出来，即使在没必要这

么做时，也会如此。(比如可以直接把数据返回客户端，但是也需要建立临时表，然后读取临时表的数据)

6、查询缓存何时有帮助

检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率。它是缓存提供的查询结果的数量，而不是服务器执行的数量。当有select查询时，Qcche_hits 和 Com_select 这两个变量会根据查询缓存的情况进行递增，查询缓存命中率的计算公式是：Qcache_hits/(Qcache_hits+Com_select)。命中率多少才好？这视情况而定，如果缓存命中代表了开销很大的查询，那么即使命中率很低(30%)也有很大的好处。

缓存未命中的原因：

查询不可缓存。原因可能是含有不确定函数，比如 CURRENT_DATE，也有可能是缓存结果太大，无法缓存。这两种无法缓存的查询会使状态变量Qcache_not_cache增加

服务器从来没见过这个缓存

缓存的结果以前被缓存过但是后来被移除了。移除的原因可能是内存不足人为移除，也可能是缓存失效。

缓存可能会因为碎片、内存不足或数据改变(该查询的结果数据量增加或对数据进行了修改)而失效。

可以通过检查 Com_*(Com_update，Com_delete等)的值知道有多少查询修改了数据。也可以通过检查Qcache_lowmem_prunes的值了解有多少查询因为内存不足而失效。

SQL_NO_CACHE 即不会检查查询是否已经存在于查询缓存也不会缓存查询结果

SQL_CACHE 如果查询结果是可缓存的，那么会缓存查询结果

7、复制表

create table `new_tablename` like `org_tablename`

create table `new_tablename` select * from `org_tablename` 会将原表中的数据完整复制一份，但表结构中的索引会丢失。

create table `new_tablename` like `org_tablename` 只会完整复制原表的建表语句，但不会复制数据

8、数据碎片

DELETE删除数据和更改表的存储引擎都会产生留空的空间，虽然在插入新数据时会尝试利用这些留空的空间，但是也不能充分

如何查看哪些表存在数据碎片：

SHOW TABLE STATUS;查看数据库中所有表的详细信息(存储引擎:Engine，现有数据行数:Rows，数据长度:Data_length，

碎片长度:Date_free，索引长度:Index_length，创建时间:Create_time，更新时间:Update_time)

SELECT table_name, data_length, data_free, create_time FROM INFORMATION_SCHEMA.TABLES WHERE table_name like '自己的表名' ORDER BY data_free DESC;

9、SQL_NO_CACHE