Mysql常见优化技巧总结(值得收藏)

DusNoob 22天前 39

数据库优化维度有四个:硬件、系统配置、数据库表结构、SQL及索引。

优化选择

  • 优化成本:硬件>系统配置>数据库表结构>SQL及索引

  • 优化效果:硬件<系统配置<数据库表结构<SQL及索引

    优化工具

  • show [SESSION | GLOBAL] variables 查看数据库参数信息

  • SHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息

  • information_schema 获取元数据的方法

  • SHOW ENGINE INNODB STATUS Innodb引擎的所有状态

  • SHOW PROCESSLIST 查看当前所有连接session状态

  • explain 获取查询语句的执行计划

  • show index 查看表的索引信息

  • slow-log 记录慢查询语句

  • mysqldumpslow 分析slowlog文件的

    优化经验

    1.SQL及索引优化
  • 当只要一行数据时使用 LIMIT 1,这是为了使EXPLAIN中type列达到const类型;

  • 如果排序字段没有用到索引,就尽量少排序;

  • 在Join表的时候使用相当类型的例,并将其索引,如果你的应用程序有很多 JOIN 查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。

  • 避免SELECT *从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载;

  • 尽可能的使用NOT NULL , 对于null的判断会导致引擎放弃使用索引而进行全表扫描;

  • 把IP地址存成 UNSIGNED INT ,很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2;

  • 使用合理的分页方式以提高分页的效率,可以取前一页的最大行数的id,然后根据这个最大的id来限制下一页的起点。比如此列中,上一页最大的id是866612。SQL可以采用如下的写法:

  • 不建议使用%前缀模糊查询,例如LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”;

  • 分解关联查询, 将一个大的查询分解为多个小查询;

  • 避免在where子句中对字段进行表达式操作,比如

    select user_id,user_project from table_name where age*2=36;
  • 对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成

    select user_id,user_project from table_name where age=36/2;
  • 对于联合索引来说,要遵守最左前缀法则, 举列来说索引含有字段id,name,school,可以直接用id字段,也可以id,name这样的顺序,但是name,school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序,常用的查询字段放在最前面;

  • 利用小表去驱动大表,减少嵌套循环中的循环次数,以减少 IO总量及CPU运算的次数;

  • 尽量使用inner join,避免left join参与联合查询的表至少为2张表,一般都存在大小之分。如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表;

    2.数据库表结构的优化
  • 选择正确的存储引擎

  • 永远为每张表设置一个ID,应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的 AUTO_INCREMENT标志

  • 将字段很多的表分解成多个表 ,于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

  • 增加中间表, 对于需要经常联合查询的表,可以建立中间表以提高查询效率。通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。

    3.架构的优化
  • 业务的持久化层的实现采用分库架构,mysql服务可平行扩展,分散压力。

  • 单个库读写分离,一主多从,主写从读,分散压力。这样从库压力比主库高,保护主库。

  • 服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

  • 不同业务的mysql物理上放在不同机器,分散压力。

  • 使用比主库更好的硬件设备作为slave总结,mysql压力小,延迟自然会变小。

    4.硬件的优化
  • 采用好服务器,比如4u比2u性能明显好,2u比1u性能明显好。

  • 存储用ssd或者盘阵,提升随机写的性能。

  • 主从间保证处在同一个交换机下面,并且是万兆环境。

本内容来自网络,希望对你有帮助

最新回复 (0)
    • 都市菜鸟网
      2
        立即登录 立即注册 
返回