Mysql优化小技巧

不定时更新，记录一些mysql优化的技巧以及验证的实验。

数据量和要求

数据量：
单表一千万条记录以上
要求：
单条sql查询时间不超过1秒

优化技巧

先把每一条心得记录在这里，后面会进行实验对其一一验证。

查询数据总条数时，使用max(id)而不是count(*)进行总量计数。
当然，前提是id是从1开始自增长，并且没有行被删除过。
对于常用的查询字段建立索引。
索引的速度优势显而易见。未建立索引时，全表查询是线性的。
使用limit避免全表检索。
有的查询明知道结果只会有一条，使用limit 1。如果查询结果需要分页显示，那么不妨使用limit，多次查询。
limit的偏移量较大时，先用索引进行限制
当limit较大时，例如select * from users limit 5000000,1;，在搜索之前会先进行500万的偏移，相当于进行了一半的遍历，需要根据实际情况进行优化。
使用正确的数据类型
比如phone我们常常可能会存储为char(11)，那么在查询时需要使用字符串类型，而非数字。（尽管mysql会对其转义，但这依旧会增加查询时间）
对于无索引的查询条件，将能够过滤最多记录的where条件放在最后。
如果phone = '10000000'和create_time = '2018-11-05 03:22:56'都是查询条件，而phone = '10000000'能够过滤更多记录，就将其写在最右边。
select * from users where create_time = '2018-11-05 03:22:56' and phone = '10000000';
同一字段的where条件，使用in而不是or
or的效率是接近于O(n)，而in的效率是O(Log n)

实验准备

mysql版本：5.7.23

mysql版本

建表

建立一个很常见的users表

CREATE TABLE `homestead`.`users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL DEFAULT '用户名',
  `phone` CHAR(11) NOT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT '0',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`));

插入测试数据

为了直观感受速度，先写一个存储过程，插入10000000（一千万）条记录到表中。

CREATE DEFINER=`homestead`@`%` PROCEDURE `create_user`()
BEGIN
DECLARE i INT;
START TRANSACTION;
SET i=1;
WHILE i<=10000000 DO
    INSERT INTO `homestead`.`users`(`name`, `phone`) VALUES ('测试用户',  i);
    SET i=i+1;
END WHILE;
COMMIT;
END

通过call create_user();调用存储过程，机器上花了两分多钟。

执行存储过程

实验过程

1. 查询数据总条数

常见的查询方式是直接使用count函数，但是在数据量过大时，速度不够快。
select count(*) from table;
通过count(id),count(*),count(1)三种方式计算，速度相差不大，都不够快

通过count()函数查询

通常在表设计之初，自增量id通常从1开始增长，并且每一行数据都不应该被直接delete，所以id的最大值就是总条数，因此也可以直接查询id的最大值。
select max(id) from table;

通过id计算总量

比较之下，速度得到了极大的提高。

2. 常用字段建立索引

mysql对于主键会自动创建索引，在建立了索引的字段上进行查询速度会变得非常快。
例如，我们对id（有索引）和phone（无索引）分别进行一次查询，比较他们的速度。

有无索引对比

id建立了索引，甚至不需要0.01秒就能查询出来。而phone因为没有建立索引，花费了3秒的时间。由此可见索引对于查询速度的影响极大。

3. 使用limit，避免全表索引

避免全表查询能够大幅提高查询速度。有的时候我们明知道记录可能只有一条，那么就通过limit 1进行限制。mysql在执行时，一旦找到符合条件的记录，达到了limit就将停止检索，立即返回。

limit对比

4. 小插曲：无索引下的全表遍历方式

在前面的尝试过程中，我们似乎发现，id越小的行，总能越快查询到，而id较大的行，速度更慢。由此我们猜测，mysql在无索引的字段上进行查询时，是根据主键顺序遍历的。例如下面的时间比较：

查询时间线性增长

可以发现，时间跟随id变化，越来越久，而在id达到最大值时，和全表检索的时间相差无几。

最大id和全表检索对比

5. 使用正确的数据类型

对于数字的字符串匹配，mysql会自动进行转换而不会报错，但这依旧会增加查询时间。数据表users中的phone字段，我们是以char(11)存储的，那么在查询时应该严格使用字符串。下面这个对比可以看出查询的时间：不当的数据类型导致查询时间变长。

使用正确的数据类型

6. 将过滤更多字段的where条件写在语句的最后

对于没有建立索引的多个where条件，mysql的执行顺序是从右到左执行。
满足phone = '10000000'的记录只有一条，而满足create_time = '2018-11-05 03:22:56'的却有很多，因此phone = '10000000'能够过滤更多记录，应该将其写在最右边。
select * from users where create_time = '2018-11-05 03:22:56' and phone = '10000000';

image.png

对于建立了索引的条件，mysql会自动进行优化，优先查询具有索引的字段。
例如select * from users where id = 10000000 and phone = '10000000' and create_time = '2018-11-05 03:22:56'这条语句，即使id=10000000写在了最左边，但查询时依旧最先进行检索，所以语句执行时间不到1ms。

建立了索引的字段不论顺序先后，都优先查询

7. 同一字段的where条件，使用in而不是or

例如要依据同一字段查询多条记录，应当使用in而不是or。or的复杂度更高，耗时更长。