MySQL优化

索引优点：

        降低需要扫描的数据量，减少IO次数；
        可以帮助避免排序操作，避免使用临时表； 
        帮助将随机IO转为顺序IO；
        
高性能索引策略：
        (1) 在WHERE中独立使用列，尽量避免其参与运算；
            WHERE age+2 > 32 ; 
        (2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
            索引选择性：不重复的索引值和数据表的记录总数的比值；
        (3) 多列索引：
            AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
                WHERE gender='F' AND age>18;
                    index(gender), index(age)
                    index(gender,age)
        (4) 选择合适的索引列次序：选择性最高的放左侧；
        
EXPLAIN来分析索引有效性：

    EXPLAIN [explain_type] SELECT select_options
        
        explain_type:
            EXTENDED
            | PARTITIONS    
    
    输出结果：
                id: 1
        select_type: SIMPLE
            table: students
            type: const
        possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: const
            rows: 1
            Extra: 
            
        id：当前查询语句中，第个SELECT语句的编号；
                
                复杂的查询的类型主要三种：
                    简单子查询
                    用于FROM中的子查询
                    联合查询
                    
                注意：联合查询的分析结果会出现一个额外的匿名临时表；
                
        select_type：查询类型：
                简单查询：SIMPLE
                复杂查询：
                    简单子查询：SUBQUERY
                    用于FROM中的子查询：DERIVED
                    联合查询中的第一个查询：PRIMARY
                    联合查询中的第一个查询之后的其它查询：UNION
                    联合查询生成的临时表：UNION RESULT
                    
        table：查询针对的表；
            
        type：关联类型，或称为访问类型，即MySQL如何去查询表中的行
             ALL：全表扫描；
            index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；
            range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；
            ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；
            eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；
            const，system：与某个常数比较，且只返回一行；
            possiable_keys：查询中可能会用到的索引；
                key：查询中使用的索引；
            key_len：查询中用到的索引长度；
            ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值； 
            rows：MySQL估计出的为找到所有的目标项而需要读取的行数；
            
            Extra：额外信息
                Using index：使用了覆盖索引进行的查询；
                Using where：拿到数据后还要再次进行过滤； 
                Using temporary：使用了临时表以完成查询；
                Using filesort：对结果使用了一个外部索引排序；  
查询缓存：
    Query cache MySQL內建的缓存，把查询语句做hash运算，大小写hash值不一样
    缓存：k/v 
        key：查询语句的hash值
        value：查询语句的执行结果
        
    如何判断缓存是否命中：
        通过查询语句的哈希值判断：哈希值考虑的因素包括
            查询本身、要查询数据库、客户端使用的协议版本、...
                SELECT Name FROM students WHERE StuID=3;
                Select Name From students where StuID=3;
            
    哪些查询可能不会被缓存？
        查询语句中包含UDF(User-Defined Functions 用户定义函数)
        存储函数
        用户自定义变量
        临时表
        mysql系统表或者是包含列级别权限的查询
        有着不确定结果值的函数(now())；
        
    查询缓存相关的服务器变量
        query_cache_limit：能够缓存的最大查询结果；（单语句结果集大小上限）
            有着较大结果集的语句，显式使用SQL_NO_CACHE，以避免先缓存再移出； 
        query_cache_min_res_unit：内存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；
            较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作； 
            较大值的会带来空间浪费；
        query_cache_size：查询缓存空间的总共可用的大小；单位是字节，必须是1024的整数倍；
        query_cache_strip_comments
        query_cache_type：缓存功能启用与否；
            ON：启用；
            OFF：禁用；
            DEMAND：按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；
        query_cache_wlock_invalidate：如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为OFF，表示可以；ON则表示不可以；
    
    状态变量：
        mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
         +-------------------------+----------+
        | Variable_name           | Value    |
        +-------------------------+----------+
        | Qcache_free_blocks      | 1        |
        | Qcache_free_memory      | 16759688 |
        | Qcache_hits             | 0        |
        | Qcache_inserts          | 0        |
        | Qcache_lowmem_prunes    | 0        |
        | Qcache_not_cached       | 0        |
        | Qcache_queries_in_cache | 0        |
        | Qcache_total_blocks     | 1        |
        +-------------------------+----------+      
        
        命中率：
            Qcache_hits/Com_select 

InnoDB存储引擎相关的参数：

innodb_buffer_pool_size：
    索引、数据、插入数据时的缓冲区
    
    专用服务器70-80%；
        
    如果数据集本身较小，可根据数据变化幅度及规划的时长也设定合理值，比预估的目标值略大；
    
innodb_buffer_pool_instances：
    buffer_pool的区段（实例）数量；
    
    

事务日志：
    innodb_log_files_in_group：一组的日志文件数量，至少2个；
    innodb_log_file_size：日志文件大小，默认为5M；建议调大此值；
    innodb_flush_logs_at_trx_commit：
        0：log buffer（内存）每秒一次同步到log file中，且同时会进行log file到data file的同步操作；
        1：每次提交时，log buffer同步到log file，同时进行log file到data file的同步操作；
        2：每次提交时，log buffer同步到log file，但不会同时进行log file到data file的同步操作；
        
    建议：关闭autocommit，而后将此值设置为1或2；
    
    
    
innodb_file_per_table：innodb的诸多高级特性都依赖于此参数；
innodb_read_io_threads: 
innodb_write_io_threads
    文件读写的io线程数；可根据并发量和CPU核心数适当调整；
innodb_open_files：innodb可打开的文件数量上限；
    

innodb_flush_method:
    
innodb_thread_concurrency=


skip_name_resolve：
max_connections：

表分区：

PARTITION BY 根据什么划分
根据范围划分：
CREATE TABLE students (id INT, name VARCHAR(100), age TINYINT UNSIGNED NOT NULL, gender ENUM('F','M')) PARTITION BY range(age)(partition youngman values less than (40), partition middleman values less than (70), partition oldman values less than maxvalue);
根据hash划分：
CREATE TABLE students (id INT, name CHAR(100) NOT NULL, age TINYINT UNSIGNED, gender ENUM('F','M')) PARTITION BY hash(id) PARTITIONS 5;

    指明分区的数量； 
    
根据列表划分：
    CREATE TABLE students (id INT, name CHAR(100) NOT NULL, age TINYINT UNSIGNED, gender ENUM('F','M'), majorid TINYINT UNSIGNED NOT NULL) PARTITION BY list(majorid) (PARTITION p0 VALUES IN (1,4,7), PARTITION p1 VALUES IN (2,5,8), PARTITION p2 VALUES IN (3,6,9)); 

以下为百度出来的其他优化方法：

1、选取最合适的字段属性

MySQL可以很好的支持大数据量的存取，但是一般来说，数据库中的表越小，他在上面所执行的查询也就会越快。因此在创建表的时候，为了获取更好的性能，我们可以将表中的字段的宽度设的尽可能小。
例如：例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来查询的时候，数据库不用去比较NULL值
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：
DELETEFROMcustomerinfo
WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECTFROMcustomerinfo
WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)
如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECTFROMcustomerinfo
LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID
WHEREsalesinfo.CustomerIDISNULL
连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。
SELECTName,PhoneFROMclientUNION
SELECTName,BirthDateFROMauthorUNION
SELECTName,SupplierFROMproduct

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';
...
UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES
这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。
CREATETABLEcustomerinfo( CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
CREATETABLEsalesinfo( SalesIDINTNOTNULL,CustomerIDINTNOTNULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB;
注意例子中的参数“ONDELETECASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
那该对哪些字段建立索引呢？
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况
例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。
下面是应该注意的几个方面。
● 首先，最好是在相同类型的字段间进行比较的操作。
在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。
● 其次，在建有索引的字段上尽量不要使用函数进行操作。
例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
● 第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
例如下面的查询将会比较表中的每一条记录。
SELECTFROMbooks
WHEREnamelike"MySQL%"
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
SELECTFROMbooks
WHEREname＞="MySQL"andname＜"MySQM"
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。