Mysql优化之软件优化
未十一

I.选择存储引擎

MyISAM和InnoDB是最常用的两个存储引擎。两者主要区别如下:

  • MyISAM支持表锁,InnoDB支持行锁;
  • MyISAM是非事务安全型,InnoDB是事务安全型;
  • MyISAM不支持外键,InnoDB支持外键;

MyISAM基于ISAM(索引顺序访问方法),支持全文索引;InnoDB的行锁也不是绝对的,如果它在执行一个更新的语句时没法确定更新的范围,也会锁表,比如like更新;InnoDB支持回滚、崩溃恢复、ACID事务控制,InnoDB存储它的表和索引在一张表空间,表空间可以包含多个文件。

II.正确使用索引

关于MySQL的优化,一般情况下,第一想到的就是建立索引,但是索引并不是越多越好,使用索引要恰到好处,才能更好地优化性能。
下面是使用索引的一些原则:

  • 给合适的列建立索引。在where子句中经常需要给检索的列建立索引,或者给连接子句中指定的列建立索引,而不是给select选择列表中的列建立索引。
  • 索引列的值尽可能不同。对于有唯一性的值,索引的效果最好;如果有大量的重复值,索引的效率很差。
  • 使用短索引。对字符类型的列建立索引,只要有可能,都应该指定前缀长度。例如,有一个char(50)的列,如果前20或30个字符内,多数值是唯一的,那么就不要对整个列进行索引。较小的索引,索引缓存中能容纳更多,消耗的磁盘IO更小,能提高查找的速度。
  • 利用最左前缀。创建一个n列索引,本质上是MySQL创建了n个索引。多个索引可以起n个索引的作用,可以用索引中最左列的值来匹配,这样的列值叫做最左前缀。
  • 使用like查询时索引会失效。因此尽量少使用like查询,对于百万、千万级的数据,如果真的要使用like查询,请用专业的搜索工具来实现,推荐Elasticsearch。
  • 不能滥用索引。过度使用索引会有下面这些问题:
    索引会占用额外的磁盘空间,降低性能;
    当更新数据时索引必须更新,索引越多,花费在更新的时间上更长;
    SQL在执行一个查询语句前会对这个查询语句进行优化,确定使用哪些索引,滥用索引可能导致MySQL选择到不是最优的索引;

III.避免使用select *

当需要放回某个查询结果的列时,为了编写代码的方便,一般情况下都会使用 select *,但是如果我们需要的只是几列数据,那么从数据库返回的结果无疑会更多,这样会降低查询的速度;另外返回给APP端的数据量也明显增大,就会降低数据传输的速度。

IV.字段尽可能设置为 NOT NULL

如果产品需求上规定必须严格区分一个字段的未填和为空两种状态,那么 NULL 是必须的;如果没有这个需求,建议数据库所有字段都设置为 NOT NULL,给默认值。
我们都知道 NULL 是一种数据类型,假设你给一个类型为string的字段设置了允许 NULL, 那么你在程序中就要额外处理这种数据类型,增加了开发成本。
NULL值还是需要占用额外的空间的,并且在查询比较的时候,程序会更复杂。

发布于 2019-02-24 13:12:08

{{ comment.username }}

{{ comment.created_format }}

{{ reply.username }}

回复

{{ reply.to_username }}:

{{ reply.created_format }}