MySQL数据库运用技巧和优化(不断更新)

一个LAMP架构的网站,经常说这慢那慢的,其实从架构到代码到数据库,优化的空间是非常非常多的(架构设计参照我另外一篇文章),就看你是否愿意花心思去一点一点儿的做起,比如mysql,在lamp网站的架构优化中,往往是很多人最容易忽略的环节,却往往成为优化空间最大的环节,一句不经意的sql语句,可能就会导致数据库执行时间过长,进而导致锁表时间过长,最后导致整站垮掉,让另外99%的努力工作和优化都失去意义,所以,Mysql的优化,需要细致而有耐心的进行,一点一点儿的挖,从slow_query日志开始,从my.cnf参数配置开始,从sql语句写法开始,从表中的索引index开始。。。

即日起,不断整理和MySQL的运用技巧与优化的内容,下面先想到一些是一些。

【索引】

  1. 一般来说,如果选择性超过 20% 那么全表扫描比使用索引性能更优,Oracle在选择性超过25%时会选择全表扫描。
  2. 组合索引比单一索引更有效,但是需要合理使用,清楚你在干什么,组合索引采用B树类型,左前缀的特性,创建组合索引的时候,要考虑到具体的使用场景和逻辑。
  3. 在需要创建索引的字段上,不能设置默认值为NULL,或者也不能插入NULL值,否则索引没有意义。(好像5.1后部分类型的表可以支持NULL的索引)
  4. 不是索引越多越好,过多索引容易导致更新和插入数据效率大大降低。
  5. 要理解最基本的索引概念,只有在where和join用到的字段上创建索引才有价值,另外Max()和Min()函数用到的字段也需要
  6. Where条件里面,有些情况下也是用不到索引的,比如<>, NOT IN, LIKE语句以 %_开头的时候,这时,可以用 id>m or id,用 NOT EXISTS代替NOT IN,可以用到索引的WHERE条件有:<,<=,=,>,>=,BETWEEN,IN,不以%_开头的LIKE。
  7. 对于CHAR和VARCHAR字段,只用字段的一部分就可创建索引。创建索引时,使用col_name(length)语法,因为多数字段的前10个字符通常不同,所以此索引不会比使用字段的全名创建的索引速度慢很多。另外,使用字段的一部分创建索引可以使索引文件大大减小,从而节省了大量的磁盘空间,有可能提高INSERT操作的速度。尤其一些md5()后结果的字段比较适用。

【SQL】

  1. 经常用EXPLAIN来检查你的SQL查询语句,找出那些想当然却事与愿违的隐藏问题。
  2. 常常检查slow query的日志,针对性的深挖慢嚼,改善你的系统性能

【架构和配置】

  1. 主辅库同步,通过搭建Master-slave的架构,可以把系统的读写分离,一方面提高了系统因为数据库单点导致的锁表代理的性能瓶颈,还能使系统的架构更加灵活,可以在某一台slave机器上进行数据统计分析,也可以在某一个slave上进行定时的cron任务或者daemon程序操作,从大型网站使用mysql来说,replication是必须的架构。
  2. memory 类型表

【新特性】

  1. 适当的使用触发器,对于web应用来说,可以在很多地方降低代码的复杂度,提高业务逻辑的可靠性,从我的经验来看,可以在大型应用中适当的使用触发器,比如删除一条评论,用户user表里面的评论数字段自动减1这样的操作就可以通过触发器来完成。

【附录】

有关EXPLAIN参数的说明
EXPLAIN列的解释:

  • table:显示这一行的数据是关于哪张表的
  • type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
  • possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
  • key:实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用
  • IGNORE INDEX(indexname)来强制MYSQL忽略索引
  • key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
  • ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
  • rows:MYSQL认为必须检查的用来返回请求数据的行数
  • Extra:关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢

extra列返回的描述的意义

  • Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
  • Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
  • Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
  • Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
  • Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
  • Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
  • Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)
  • system 表只有一行:system表。这是const连接类型的特殊情况
  • const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
  • eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
  • ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
  • range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况
  • index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)
  • ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免
Category: MySQL / DB
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
15 Responses
  1. blankyao says:

    期待更多技巧….

  2. MyGod says:

    如果选择性超过 20% 那么全表扫描比使用索引性能更优
    ——-

    什么叫选择性超过20%? 样进行全表扫描代替索引?

    Michael 可以举个例子说明吗? 呵呵 期待您的回答!

  3. 秒杀 says:

    找zend framework的内容无意中找到了这里。哈,IT老鸟啊!拜倒在BLOG主人的脚下。

    厄,那个座沙发的家伙(blankyao)。竟然这里都能碰到你。。。。你无敌了!

  4. Michael says:

    [Comment ID #27883 Will Be Quoted Here]
    比如有个字段,叫gender,就是性别,性别通常有三种,男、女、保密, 这三种情况假如各占33%,那么一个表里面如果要有这样的查询 select * from tab where gender=’男’ and userid=’123′; 这个时候,gender字段应该就不要用索引,因为选择性超过了20%,任何一个条件都需要遍历大量的结果。

  5. Michael says:

    [Comment ID #27885 Will Be Quoted Here]

    你们认识?

  6. 李海鑫 says:

    太专业了,学习中

  7. plantegg says:

    不是索引越多越好,过多索引容易导致更新和插入数据效率大大降低。
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    对于这个我又很详细的测试数据,性能下降的太让我惊讶了 🙂
    实验结果:
    没有索引 整型做Primary Key 整型做Index 字符串做Primary Key 字符串做Index
    53 61 81 216 218
    数据都是在插入100万的时候得出的,时间单位:秒
    可以看出整型当成PK的时候,性能下降了13%,字符串的时候性能下降了4倍吧,没有必要的时候别用字符串做PK,整型做Index的时候性能下架了60%吧 (性能都是值插入数据的时候的性能)

    当然如果你的查询次数远大于修改次数,这点性能的舍弃还是值得的!所以避免数据库中索引满天飞的情况吧

  8. Michael says:

    [Comment ID #27913 Will Be Quoted Here]

    说得太好了 🙂

  9. 逆雪寒 says:

    恩 。永远支持老大。优化还得谢谢老大的帮助。

  10. 逆雪寒 says:

    不过感觉用mysql 里面的函数。mysql 的性能就会变差。 所以真不敢想象用存储过程或是自己编写的函数。mysql 性能会如何哦老大你怎么看~~??

  11. 兔斯基 says:

    学习了,做个记号!

  12. headman says:

    请问博主,在LAMP中的网站,如果把调用数据库MySQL的SQL语句全部换成存储过程,会提高多大的效率。

  13. Michael says:

    [Comment ID #29421 Will Be Quoted Here]

    没有实际测试过,但是适当的运用存储过程和触发器一类的技术,可以使业务逻辑简化和数据更加可靠,估计性能不会有什么太大提高或者降低。

  14. abettor says:

    不是索引越多越好,过多索引容易导致更新和插入数据效率大大降低。
    ————————————————–
    M大虾的又一篇大作,学习ing。
    事实上,俺也曾深受index泛滥之苦。我的项目中,Oracle有一个字段是21位由数字组成的unique字符串,而且在后期要根据它update其他字段,结果发现当达到百万行级别时,一个insert居然要10s!后来我决定to_number()了。

  15. tangfl says:

    web2.0 的网站都已经开始去 SQL 化了。
    远离数据库,远离数据中心,网站才有未来

  16. When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a
    comment is added I get several emails with the same comment.
    Is there any way you can remove people from that service?
    Bless you! Maglia Diego Godín Bambino

  17. gamefly says:

    I love what you guys tend to be up too. This type of clever work and
    coverage! Keep up the superb works guys I’ve included you guys to
    our blogroll.

  18. g says:

    I love what you guys are up too. This sort of clever work and reporting!
    Keep up the amazing works guys I’ve included you guys to blogroll.

  19. constantly i used to read smaller posts that also clear
    their motive, and that is also happening with this article which I am
    reading here.

  20. Hi there, I wish for to subscribe for this website to take most up-to-date updates, so where can i do it please help.

  21. I seriously love your website.. Very nice colors & theme.

    Did you make this web site yourself? Please reply back as I’m looking to
    create my own personal website and would love to know
    where you got this from or just what the theme is called.
    Appreciate it!

Leave a Reply

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image