0%

《Mysql》Mysql最佳实践

最佳实践

  1. 在更小的列上建索引,索引就会占用更少的资源
  2. 使用正确合适的类型,不要将数字存储为字符串
  3. 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型
  4. 尽可能使用较小的整数类型使表更小。例如,MEDIUMINT经常比INT好一些,因为MEDIUMINT列使用的空间要少25%
  5. 如果可能,声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL,应该毫无疑问使用它,只是避免默认地在所有列上有它,null值会占用更多的字节,并且null有很多坑的。
  6. 使用sample character set,例如latin1。尽量少使用utf-8,因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1,例如mail,url等。
  7. 要选择性的使用索引。在变化很少的列上使用索引并不是很好,例如性别列。因为这样的话还不如全表扫描(非聚簇索引需要先查询到主键再查询到数据,列变化很少的话,通过非聚簇索引B-tree树找到的主键很多,还要通过主键去找记录,得不偿失。可以通过 show index 的 Cardinality 值(不是实时更新的,可通过analyze table灯芯)得到索引中唯一值的数目的估计值,这个值越大越好,性别列的索引,这个值就是2)
  8. 在Unique列上定义Unique index。
  9. 避免建立使用不到的索引。
  10. 数据量小于1000的表不要建立索引
  11. 在需要排序或者搜索的列上建立索引。
  12. 更新比较频繁的也不适合加索引(因为索引树也要不断更新)
  13. 索引字段,越是短的键值越好,最好使用integer(因为键值越短,需要的存储空间就越少,非叶子结点就可以装更多的键,树就越矮胖)
  14. 当只要一行数据时使用 LIMIT 1。MySQL数据库引擎会在找到一条数据后停止搜索
  15. 在Join表的时候使用一样类型的列,并为他们各自建立索引。这样,MySQL内部会启动为你优化Join的SQL语句的机制
  16. 不要使用 ORDER BY RAND() 打乱返回的数据行,会带来严重的性能问题
  17. 不要使用 select * ,从数据库里读出越多的数据,那么查询就会变得越慢
  18. 为每张表设置一个ID,并设置为主键,并设置上自动增加的AUTO_INCREMENT标志。
  19. 固定长度的表(每个字段都不是VARCHAR,TEXT,BLOB类型)会更快。
  20. 垂直分割表。表字段太多,将某些字段放到另一个表中,小一点的表总是会有好的性能,前提是你不会经常join这两个表,不然性能更差
  21. 列的类型越小越好。使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然也要考虑扩展空间,不然以后要改的话会很惨
  22. 优先考虑扩展索引,而不是新建索引,避免不必要的索引
  23. 固定长度的字符串,应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率
  24. 在设计单表的索引时,首先把查询中所有的等值谓词全部取出以任意顺序放在索引最前面,在这时,如果索引中同时存在范围索引和 ORDER BY 就需要权衡利弊了,希望最小化扫描的索引片厚度时,应该将过滤因子最小的范围索引列加入索引,如果希望避免排序就选择 ORDER BY 中的全部列,在这之后就只需要将查询中剩余的全部列加入索引了

分页查询优化

当一张表记录非常多

使用 ** select * from test limit 100 offset 1000000 ** 会发现很慢,offset越大就越慢

这是因为需要把offset 1000000之前的记录都要读出来,大量的磁盘读取,虽然是顺序读取,但offset太大

那如果是 ** select * from test where status = 3 limit 100 offset 1000000 **, status是辅助索引,而且查出来的数据超过1000100条呢

虽然status是索引,但是通过status索引读出很多主键id后,需要根据主键去读记录,注意这里是随机读取,而且一样要读取1000000条记录,这就比上面更慢了(其实Mysql不允许这种事情发生的,优化器会计算出走索引还不如直接全表扫描,最终不会走索引)

那么该怎么优化呢?

如果表具有自增id的话,可以改成下面语句:** select * from orders where status = 3 and id >= (select id from orders where status = 3 limit 1 offset 1000000) limit 100 **,这样会有大幅提升

首先会查询 ** select id from orders where status = 3 limit 1 offset 1000000 ** 找到1000000位置处的id,这个查询使用了覆盖索引,没有回表查询,速度很快

接着** select * from orders where status = 3 and id >= 上面查到的id limit 100 **查询100条记录,这样的话,因为有id>=的查询条件,不需要读取1000000前面所有数据,直接可以取出100条记录,速度也很快




微信关注我,及时接收最新技术文章