mysql github week2_week3-MySQL 实践二

MySQL 实践二

建议：文章为丁奇的MySQL实战课程笔记，如果感兴趣建议支持正版。主要原因：付费知识，作者需要对自己说的东西负责，错误少，质量较良莠不齐的博文高不少。

MySQL Count()

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

对于不同引擎，count(*)的实现机制是不一样的。在没有过滤条件时：

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎，执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。

count(字段)，not null字段与主键类似，允许null则需要取出再判断是否为null。

count(字段)

order by 时如何工作的

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。例如有这样一个语句

select city,name,age from t where city='杭州' order by name limit 1000 ;

情况一：sort_buffer大小足够，全字段排序。我们需要获取三个字段的信息，所以InnoDB会根据会在city索引上寻找符合条件的主键ID，根据主键ID取得三个字段的信息存入sort_buffer，待所有符合条件的行存入sort_buffer后，根据name值做快速排序。

情况二：sort_buffer空间不足，排序数据量太大，需要利用磁盘临时文件辅助排序。需要使用外部排序，外部排序一般使用归并排序算法。

情况三：sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少。rowid排序。较情况一，仅获取name与主键ID存入sort_buffer，按name排序后再根据ID回表，获取city、age和name字段返回。如果sort_buffer不足，参照情况二。

优化方案

有这样一个sql语句

select city,name from t where city='杭州' order by name limit 1000;

可以建立联合索引key(city,name)，这样就可以避免排序，并利用联合索引返回结果。

当出现order by rand()这样的语句，有这样一个表，有一万条记录

CREATE TABLE `words`

( `id` int(11) NOT NULL AUTO_INCREMENT,

`word` varchar(64) DEFAULT NULL,

PRIMARY KEY (`id`)) ENGINE=InnoDB;

我们执行这样一条语句

select city from words order by rand() limit 3;

通过慢查询日志得到如下结果：

# Query_time: 0.005657 Lock_time: 0.001914 Rows_sent: 3 Rows_examined: 20003

SET timestamp=1592375094;

select word from words order by rand() limit 3;

执行流程如下：

创建临时表，这个表时memory引擎的，表里有两个字段，一个用来存储word，varchar(64)，一个Double类型用于存储rand()的值。

主键扫描所有的行，并取出word，计算rand，存入临时表。到这里这里扫描行数为10000

将rand值与rowid存入sort_buffer并排序，这里进行全表扫描再加10000，排序不增加扫描行数，此时扫描行数为20000

排序后取出前三个rowid，返回临时表，取word，加3。故此，总扫描行数为20003行。

在InnoDB引擎中，没有主键系统会自动创建主键rowid，默认大小6字节。

MEMORY引擎不是索引组织表，在这个例子中，你可以将其看作一个数组，rowid为下标。

另外在内存不够建立临时表时，排序将采用优先队列排序算法。以本例为例，以前三行构成堆，遍历表，更新堆，得到前三位。

如何正确生成随机行

设总行数为C，我们可以通过计算随机值floor(C * rand())Rand，floor为取整。

select * from xx where id=Rand //不要使用 limit Rand，1

因为前者扫描一行，后者扫描Rand+1行。

SQL语句注意事项

情况一：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。但并不是不适用该索引。

select count(*) from tradelog where month(t_modified)=7;

select count(*) from tradelog where

-> (t_modified >= '2016-7-1' and t_modified

-> (t_modified >= '2017-7-1' and t_modified

-> (t_modified >= '2018-7-1' and t_modified

这两个语句都使用了t_modified索引，但是前者因为month函数，导致优化器放弃了索引的快速定位功能，只能利用索引进行全表扫描。

隐式类型转换

有这样的语句，其中tradeid的类型为varchar。MySQL会触发类型转换将varchar转为数字。

select * from tradelog where tradeid=110717;

<=>select * from tradelog where CAST(tradid AS signed int) = 110717;//情况一

select * from tradelog where id="83126";

<=>select * from tradelog where id = CAST("83126" AS signed int);//无影响

另外，更隐蔽的类型转化还有隐式字符编码转换。