Mysql优化小技巧

Dear 丶 2022-04-23 11:20 357阅读 0赞

思梦PHP 2019-02-09 07:01:00

数据量和要求

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

优化技巧

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

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

实验准备

*  mysql版本：5.7.23

![Mysql优化小技巧][Mysql]

*   
 *  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`()BEGINDECLARE 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();调用存储过程，机器上花了两分多钟。

![Mysql优化小技巧][Mysql 1]

执行存储过程

实验过程

1. 查询数据总条数

常见的查询方式是直接使用count函数，但是在数据量过大时，速度不够快。

select count(\*) from table;

通过**count(id),count(\*),count(1)**三种方式计算，速度相差不大，都不够快

![Mysql优化小技巧][Mysql 2]

通过count()函数查询

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

select max(id) from table;

![Mysql优化小技巧][Mysql 3]

通过id计算总量

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

2. 常用字段建立索引

mysql对于**主键会自动创建索引**，在建立了索引的字段上进行查询速度会变得非常快。

例如，我们对id（有索引）和phone（无索引）分别进行一次查询，比较他们的速度。

![Mysql优化小技巧][Mysql 4]

有无索引对比

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

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

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

![Mysql优化小技巧][Mysql 5]

limit对比

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

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

![Mysql优化小技巧][Mysql 6]

查询时间线性增长

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

![Mysql优化小技巧][Mysql 7]

最大id和全表检索对比

5. 使用正确的数据类型

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

![Mysql优化小技巧][Mysql 8]

使用正确的数据类型

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';

![Mysql优化小技巧][Mysql 9]

image.png

对于建立了索引的条件，mysql会自动进行优化，优先查询具有索引的字段。

例如select \* from users where id = 10000000 and phone = '10000000' and create\_time = '2018-11-05 03:22:56'这条语句，即使id=10000000写在了最左边，但查询时依旧最先进行检索，所以语句执行时间不到1ms。

![Mysql优化小技巧][Mysql 10]

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

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

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

![Mysql优化小技巧][Mysql 11]

作者：潇湘Blake

链接：https://www.jianshu.com/p/59569cd496bb

作者：慕码人8056858

链接：https://www.imooc.com/article/277634

[Mysql]: http://p1.pstatp.com/large/pgc-image/54755518c2834864a49a1bdc66286e6f
[Mysql 1]: /images/20220320/e41d87460a004b49a627a403fd96e009.png
[Mysql 2]: /images/20220320/e30da0de94a7484191c8b20b00047917.png
[Mysql 3]: http://p1.pstatp.com/large/pgc-image/316d7dbe272940da801123928c1927bb
[Mysql 4]: http://p9.pstatp.com/large/pgc-image/fb69b2ad6fd64958a50e80a255bf6299
[Mysql 5]: http://p1.pstatp.com/large/pgc-image/e853a61e38104ad2a16453a715d8299a
[Mysql 6]: http://p1.pstatp.com/large/pgc-image/bb65de5e39ba4d6abab017b20114ea73
[Mysql 7]: http://p1.pstatp.com/large/pgc-image/22be6f1ed5a743779f192b97f2bbe49f
[Mysql 8]: /images/20220320/90677b7ca5644ee29d8b246613cdeb4d.png
[Mysql 9]: http://p1.pstatp.com/large/pgc-image/277d8e9334f1497a9852087eb9aaec1c
[Mysql 10]: http://p1.pstatp.com/large/pgc-image/fb24eaababb14ab3b4069301ca8a124f
[Mysql 11]: http://p1.pstatp.com/large/pgc-image/7a023af6efba44939c2cfa119c09dcb2