Mysql优化小技巧

问题

假设现在有一个千万条记录的表，要针对不同的需求，进行相应的查询，修改，删除操作。要求单条sql执行时间不能超过一秒，请问如何解决？

构造假数据

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

CREATE DEFINER=`homestead`@`%` PROCEDURE `create_feedback`()
BEGIN
DECLARE i INT;
START TRANSACTION;
SET i=1;
WHILE i<10000000 DO
    INSERT INTO `tax`.`feedback`(`user_name`, `content`) VALUES ('测试用户', concat('测试反馈', i));
    SET i=i+1;
END WHILE;
COMMIT;
END

查询数据总条数

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

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

查询符合某条件的数据

工作中常常需要通过某些条件检索匹配的数据，例如根据手机号查询用户，或是根据某字段查询。通常提高速度的方法有以下几种：

1. 建立索引

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

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

2. 巧用limit，避免全表查询

除了建立索引以外，还有一个提高速度的小技巧是尽量避免全表查询。有的时候我们明知道记录可能只有一条，那么就通过limit 1进行限制。mysql在执行时，一旦找到符合条件的记录，达到了limit就将停止检索，立即返回。

limit对比
可以看出增加了limit之后，mysql查询到符合条件的行就立即返回了。

无索引下的全表遍历方式

在前面的尝试过程中，我们似乎发现，id越小的行，总能越快查询到，而id较大的行，速度更慢。由此我们猜测，mysql在无索引的字段上进行查询时，是根据id由小到大进行依次匹配的。于是我们改变数字看一看

查询时间随id变化
可以发现，时间跟随id变化，越来越久，而在id达到最大值时，和全表检索的时间几乎一致。
最大id和全表检索对比

总结

最后总结一下，mysql优化的小技巧：

1. 使用max(id)而不是count(*)进行总量计数。
   当然，前提是id是从1开始自增长，并且没有行被删除过。
2. 对于常用的查询字段建立索引。
   索引的速度优势显而易见。未建立索引时，全表查询是线性的。
3. 使用limit避免全表检索。
   有的查询明知道结果只会有一条，使用limit 1。如果查询结果需要分页显示，那么不妨使用limit，多次查询。