Mysql优化

这里先列出几条比较有效的建议：

1. 创建表的时候：应尽量建立主键；大数据表删除，用truncate table代替delete。
2. 合理使用索引：在应用中一张表的索引不要太多。对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。
3. 查询尽量用确定的列名，少用*号。select * from tab;
4. 尽量少嵌套子查询，这种查询会消耗大量的CPU资源；
5. 对于有比较多or运算的查询，建议分成多个查询，用union all联结起来；
6. 多表查询的查询语句中，选择最有效率的表名顺序。数据库解析器对表解析从右到左，所以记录少的表放在右边。

适合创建索引的情况：

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

不适合创建索引的情况：

• 表记录太少
• 经常增删改的表
• Where条件里用不到的字段不创建索引
• 数据重复且分布平均的表字段，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。记住，索引是为了唯一定位或者范围定位！这才能快！

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，但允许有空值

复合索引 

即一个索引包含多个列'

基本语法创建

CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length))

删除索引

DROP INDEX [indexName] ON mytable;

查看索引

SHOW INDEX FROM table_name

EXPLAIN性能分析:

1：id：id相同从上到下执行，否则id越大越先执行

2：select_type :有以下几个结果

SIMPLE: 简单的 SELECT查询,查询中不包含子查询或者UNION。

 PRIMARY：指示在有子查询的语句中最外面的SELECT，主查询

SUBQUERY：在SELECT或WHERE列表中包含了子查询

DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。

UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

UNION RESULT：从UNION表获取结果的SELECT

3：table：显示这一行的数据是关于哪张表的

4：type:显示查询使用了何种类型，较为重要的指标

从最好到最差依次是：     system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

system:表仅有一行(=系统表)。这是const联接类型的一个特例。

const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。

index_merge:该联接类型表示使用了索引合并优化方法。

unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高

index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

range:只检索给定范围的行,使用一个索引来选择行。只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引

index：这通常比ALL快,因为索引文件通常比数据文件小。Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。 （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

ALL:Full Table Scan，将遍历全表以找到匹配的行

5：possible_keys :显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

6：key : 实际使用的索引。如果为NULL，则没有使用索引 查询中若使用了覆盖索引，则该索引和查询的select字段重叠

7：key_len :表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好. key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

8：ref :显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

9 ：rows :根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

10 Extra：包含不适合在其他列中显示但十分重要的信息:有以下几个值

Using filesort :说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

Using temporary :使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

Using index :表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找; 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where:表明使用了where过滤

Using join buffer:使用了连接缓存

impossible where: where子句值总是false，不能获取任何元素

select tables optimized away:在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct:优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

调优的4个方面：

1.不要让数据库做得太多；

避免复杂的多表关联

避免使用 ‘* ’：只提取你所要使用的列； 使用别名能够加快解析速度；例如 SELECT id,name from ...
2.给优化器更明确的命令；

 用EXISTS替换DISTINCT:

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换.

低效:    

SELECT DISTINCT D.DEPT_NO,DEPT_NAME     FROM DEPT D,EMP E     WHERE D.DEPT_NO = E.DEPT_NO

高效:     SELECT DEPT_NO,DEPT_NAME     FROM DEPT D     WHERE EXISTS ( SELECT ‘X’                     FROM EMP E                     WHERE E.DEPT_NO = D.DEPT_NO);

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

连接列上创建索引:

一般会在表的连接列上建立索引，尤其是该表频繁参与连接操作。对于一个比较大的连接操作，如果被驱动表的连接列上没有索引的话，会造成多次扫描被驱动表，对数据库造成的压力和开销是巨大的。

创建联合索引的选择：

例如：要从存储着学籍信息的表中查找来自中国，大连的女性学生，使用的SQL的where子句如下： where country = ‘china’and city = ‘dalian’and gender = female； country和city的联合（country，city）的选择性会比country和city各自的选择性高，同时因为gender本身的选择性低，将其加入对于提高总体选择性贡献不大，所以在此情境下适合建立（country，city）的联合索引。

至少要包含组合索引的第一列

避免在索引列上使用函数

避免使用前置通配符：

SELECT USER_NO,USER_NAME,ADDRESS      FROM USER_FILES      WHERE USER_NO LIKE ‘%109204421';

在这种情况下，MySQL将使用全表扫描.

避免在索引列上使用NOT：

低效: (这里,不使用索引)    SELECT …    FROM DEPT    WHERE DEPT_CODE != “IT”;

高效: (这里,使用了索引)    SELECT …    FROM DEPT    WHERE DEPT_CODE = “IT”;

避免在索引列上使用 IS NULL和IS NOT NULL

使用表的别名：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属.

3.减少访问次数；

当执行每条SQL语句时, MySQL在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 MySQL的工作量.

例如, 以下有三种方法可以检索出雇员号等于0342或0291的职员.

方法1 (低效) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;

方法2 (高效) SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;

在含有子查询的SQL语句中,要特别注意减少对表的查询.

例如:

低效:

高效: (多列子查询)

4.细节上的影响；

选择最有效率的表名顺序:

MySQL的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 

在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当MySQL处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 

 

WHERE子句中的连接顺序:

同样因为MySQL采用自右到左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 同时那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

WHERE子句中的连接顺序:

设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：select * from emp e,dept d     where d.deptno >10 and e.deptno =30 ; 

如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。

select * from emp e,dept d     where e.deptno =30 and d.deptno >10 ;

WHERE子句 —函数、表达式使用:

最好不要在WHERE子句中使用函数或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。

ORDER BY语句决定了MySQL如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（像联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

用Where子句替换HAVING子句：

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

 

顺序       WHERE > GROUP > HAVING

注：HAVING 中的条件一般用于对组函数的操作,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中。

用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).  为了避免使用NOT IN,我们可以把它改成外联接(OUTER JOINS)或NOT EXISTS.使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查询效率更高。

 

因为1中对emp进行了full table scan,这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。

 

用表连接替换EXISTS

 避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

表连接可以替代效率十分低下的 not in 运算，大大提高运行速度

 

用TRUNCATE替代DELETE 

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的 信息.当命令运行后,数据不能被恢复.因此很少的资源被调用, 执行时间也会很短. TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML 

 

MySQL中SQL语句解析步骤：

1. 查询缓存。在解析命令之前，根据SQL 的hash值，检查查询缓存中是否有该sql的结果集缓存。

2. 解析-语法检测。判断一条SQL语句的语法是否符合SQL的规范，比如执行：SQL> selet * from emp；我们就可以看出由于select关键字少了一个“c”，这条语句就无法通过语法检验的步骤了。

3. 语义检查。语法正确的SQL语句在解析的第二个步骤就是判断该SQL语句所访问的表及列是否准确？比如如下语句：   SQL> select * from emp; ERROR at line 1: ORA-00942: table or view does not exist 由于查询用户没有可供访问的 emp对象，因此该SQL语句无法通过语义检查。

4. MySQL将SQL解析之后，根据SQL类型不同，调用不同的模块进行处理，如查询优化，重写，复制，统计等

5. 最后根据提供的接口，调用不同表对应的不同存储引擎的具体实现。

对索引的总结：

索引是表的一个概念部分,用来提高检索数据的效率，MySQL使用了一个复杂的自平衡B+tree结构. 通常,通过索引查询数据比全表扫描要快. 当MySQL找出执行查询和Update语句的最佳路径时, MySQL优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的。