Mysql 自学笔记
前言
综述：
    磁盘搜索。需要花时间从磁盘上到一个数据，用在现代磁盘的平均时间通常小于10ms，因此理论上我们能够每秒大约搜索1000次。
    这个时间在新磁盘上提高不大并且很难为一个表进行优化。优化它的方法是将数据分布在多个磁盘上。
    磁盘读/写。当磁盘放入正确位置后，我们需要从中读取数据。对于现代的磁盘，一个磁盘至少传输10-20Mb/s的吞吐。这比搜索要容易优化，
    因为你能从多个磁盘并行地读。
    CPU周期。我们将数据读入内存后，需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。但是对于小表，
    速度通常不成问题。
·    内存带宽。当CPU需要的数据超出CPU缓存时，主缓存带宽就成为内存的一个瓶颈。这在大多数系统正是一个不常见的瓶颈但是你应该知道它。
一. MySQL设计局限与折衷
  1.  如果向列内插入不合适的或超出范围的值，MySQL将该列设定为“最好的可能的值”，而不是报告错误。对于数字值，为0、可能的最小值或最大值。
  对于字符串，为空字符串或列内可以保存的字符串。请注意当运行在严格模式或TRADITIONAL SQL模式时该行为不 适用。当然这仅仅是在严格的模式下，
  包括 TRADITIONAL SQL 模式，必须为not null 指定默认值。
 
二。 可移植性
    如果你使用某个数据库特定的功能(例如MySQL专用的REPLACE语句)，应该为SQL服务器编码一个方法以实现同样的功能。尽管慢些，
    但确允许其它服务器执行同样的任务。
    用MySQL，可以使用/*! */语法把MySQL特定的关键词加到查询中。在/**/中的代码将被其它大多数SQL服务器视为注释(并被忽略)。
   
三。优化SELECT 查询
    1.影响所有语句的一个因素是：你的许可设置得越复杂，所需要的开销越多。
    执行GRANT语句时使用简单的许可，当客户执行语句时，可以使MySQL降低许可检查开销。例如，如果未授予任何表级或列级权限，
    服务器不需要检查tables_priv和columns_priv表的内容。同样地，如果不对任何 账户进行限制，服务器不需要对资源进行统计。
    如果查询量很高，可以花一些时间使用简化的授权结构来降低许可检查开销。
    2.如果你的问题是与具体MySQL表达式或函数有关，可以使用mysql客户程序所带的BENCHMARK()函数执行定时测试。
    其语法为BENCHMARK(loop_count,expression)。例如：
    mysql> SELECT BENCHMARK(1000000,1+1)；
sql自学难吗    +------------------------+
    | BENCHMARK(1000000,1+1) |
    +------------------------+
    |                      0 |
    +------------------------+
    1 row in set (0.32 sec)
    3.为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。
    key
    key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，
    在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
    4.Analyze Table
    MYISAM 引擎下 用myisamchk --analyze
    MySQL 的Optimizer（优化元件）在优化SQL语句时，首先需要收集一些相关信息，其中就包括表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。
    我们可以使用SHOW INDEX语句来查看索引的散列程度：
    SHOW INDEX FROM PLAYERS;
    TABLE  KEY_NAME COLUMN_NAME CARDINALITY
    ------- -------- ----------- -----------
    PLAYERS PRIMARY PLAYERNO            14
    因为此时PLAYER表中不同的PLAYERNO数量远远多于14，索引基本失效。
    下面我们通过Analyze Table语句来修复索引：
    ANALYZE TABLE PLAYERS;
    SHOW INDEX FROM PLAYERS;
    结果是：
    TABLE  KEY_NAME COLUMN_NAME CARDINALITY
    ------- -------- ----------- -----------
    PLAYERS PRIMARY PLAYERNO          1000
    此时索引已经修复，查询效率大大提高。
    需要注意的是，如果开启了binlog，那么Analyze Table的结果也会写入binlog，我们可以在analyze和table之间添加关键字local取消写入。
    5.Optimize Table
    经常更新数据的磁盘需要整理碎片，数据库也是这样，Optimize Table语句对MyISAM和InnoDB类型的表都有效。
    如果表经常更新，就应当定期运行Optimize Table语句，保证效率。
    与Analyze Table一样，Optimize Table也可以使用local来取消写入binlog。
    6.总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。
    你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。
    7.WHERE 语句的优化
    去除不必要的括号：
·                        ((a AND b) AND c OR (((a AND b) AND (c AND d))))
·                -> (a AND b AND c) OR (a AND b AND c AND d)
·        常量重叠：
·                  (a<b AND b=c) AND a=5
·                -> b>5 AND b=c AND a=5
·        去除常量条件(由于常量重叠需要)：
·                  (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
·                -> B=5 OR B=6
·        索引使用的常数表达式仅计算一次。
    另外 where val = ‘5’要比 where val = 5，（如果5是整数的话）慢几十倍。
    尝试所有可能性便可以到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。
    如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。
    如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。
    每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。
    在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。
    输出每个记录前，跳过不匹配HAVING子句的行。
    例子：SELECT ... FROM tbl_nameORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
            仅仅使用索引树就可以完成的查询因此快
          SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2;
          下列查询使用索引按排序顺序检索行，不用另外的排序：
            SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ;
    8.group by 的优化查询
   
    在大多数情况下，DISTINCT子句可以视为GROUP BY的特殊情况。例如，下面的两个查询是等效的：
    SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;
    SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;
    如果不使用查询中命名的所有表的列，MySQL发现第1个匹配后立即停止扫描未使用的表。在下面的情况中，假定t1在t2之前使用(可以用EXPLAIN检查)
    ，发现t2中的第1行后，MySQL不再(为t1中的任何行)读t2：
    SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
    9.避免全表扫描
    使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。
    ·        对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时
    用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。   
    10.如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，
可以调节bulk_insert_buffer_size变量，使数据插入更快。
·        如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。
·        用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。