MySQL数据库-优化-详细优化-必看

1. MySQL数据库 优化

MySQL数据库硬件的性能瓶颈：
1）CPU的瓶颈
2）I/O的瓶颈

对于MySQL系统本身性能优化：
1）使用索引
2）使用EXPLAIN分析查询
3）调整MySQL的内部配置

2. 索引

Ø 普通索引：这是最基本的索引类型，没唯一性之类的限制。
Ø 唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。
Ø 主键：主键是一种唯一索引，但必须指定为”PRIMARY KEY”。
Ø 全文索引：MYSQL从3.23.23开始支持全文索引和全文检索。
在MYSQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。
大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。
空间列类型的索引使用R-树，MEMORY表支持hash索引。

3. 使用索引注意事项

在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，
尽量在多个条件的时候，把会提取尽量少数据量的条件放在前面，减少后一个where条件的查询时间。
应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
有些where条件会导致索引无效：
1.WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引
2.类似地，如果WHERE字句的查询条件里使用了函数（如：WHERE DAY(column)=…），MYSQL将无法使用索引
3.在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，
否则即使建立了索引也不会使用
4.如果WHERE子句的查询条件里使用了比较操作符LIKE，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能
使用索引。比如说，如果查询条件是LIKE 'abc%',MYSQL将使用索引；
如果条件是LIKE '%abc'，MYSQL将不使用索引。
5.在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。
尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。
6.如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。
比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
7.如果条件中有or(并且其中有or的条件是不带索引的)，
即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
8.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

4. 索引优化建议

合理的建立索引的建议：
(1)  越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。 
(2)  简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。
在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)  尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，
含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。
你应该用0、一个特殊的值或者一个空串代替空值
 
这部分是关于索引和写SQL语句时应当注意的一些琐碎建议和注意点。
1. 当结果集只有一行数据时使用LIMIT 1
2. 避免SELECT *，始终指定你需要的列从表中读取越多的数据，查询会变得更慢。
他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。
你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
3. 使用连接（JOIN）来代替子查询(Sub-Queries)（理论上）
   连接（JOIN）.. 之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤
   的查询工作。
4. 使用ENUM、CHAR 而不是VARCHAR，使用合理的字段属性长度
5. 尽可能的使用NOT NULL
6. 固定长度的表会更快
7. 拆分大的DELETE 或INSERT 语句
8. 查询的列越小越快

5. explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。
这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
1）表的读取顺序
2）数据读取操作的操作类型
3）哪些索引可以使用
4）表之间的引用
5）每张表有多少行被优化器查询

6. 案例分析。

1）EXPLAIN
SELECT * FROM (
                  SELECT go_shoplist.title,
                         go_member.username
                  FROM go_shoplist
                    LEFT JOIN go_member ON go_shoplist.q_uid = go_member.uid
                  WHERE go_shoplist.sid IN (196,175,7064,1370,185,195,199,203)
                    AND go_shoplist.q_uid IS NOT NULL
                    ORDER BY id
                ) a
                GROUP BY a.title ;
2）EXPLAIN
SELECT go_shoplist.title,
                     go_member.username
              FROM go_shoplist
                LEFT JOIN go_member ON go_shoplist.q_uid = go_member.uid
              WHERE go_shoplist.id IN (
                          SELECT MAX(id) FROM go_shoplist
                          WHERE go_shoplist.sid IN (196,175,7064,1370,185,195,199,203)
                            AND go_shoplist.q_uid IS NOT NULL
                            GROUP BY sid
                          )
              ORDER BY go_shoplist.id DESC;
为什么第二个更慢？
答： mysql 在处理子查询时，会改写子查询。
    通常情况下，我们希望由内到外，先完成子查询的结果，然后再用子查询来驱动外查询的表，完成查询。
    上一页的sql语句，通常我们会感性地认为该 sql 的执行顺序是：
    go_shoplist 表中根据 sid in (…)取得 max(id)记录，
    然后再到 go_shoplist中，带入 id in (…)取得查询数据。
    但是实际mysql的处理方式为：
    select * from go_shoplist … where exists (
    select * from go_shoplist where sid in (…)
    )
    mysql 将会扫描 go_shoplist 中所有数据，每条数据都将会传到子查询中与 go_shoplist 关联，
    子查询不会先被执行，所以如果 go_shoplist表很大的话，那么性能上将会出现问题。
3）还能继续优化吗？
答：   SELECT a.id,b.title,d.username
      FROM go_shoplist b
      RIGHT JOIN (SELECT MAX(id) AS id FROM go_shoplist c
                  WHERE c.sid IN (196,175,7064,1370,185,195,199,203)
                    AND c.q_uid IS NOT NULL
                    GROUP BY c.sid ) a ON a.id = b.id
      LEFT JOIN go_member d ON b.q_uid = d.uid
4)用explain看看好在哪了。

7. 配置优化

连接请求的变量：
1）max_connections： max_used_connections / max_connections * 100% （理想值≈ 85%）
2）back_log：默认数值是50，可调优为128，对于Linux系统设置范围为小于512的整数
3）interactive_timeout：默认数值是28800，可调优为7200

8. 其他优化建议

1）in 和 not in 要慎用(前面提到了)
2）应尽量避免在 where 子句中对字段进行表达式操作，
如：select id from t where num/2=100 
应改为: select id from t where num=100*2
3）在使用索引字段作为条件时，如果该索引是复合索引，
那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，
否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致
4）索引并不是越多越好，索引固然可以提高相应的 select 的效率，
但同时也降低了 insert 及 update 的效率
5）不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的字段
6）尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
7）尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
8）能用UNION ALL就不要用UNION