MySQL分析和架构优化

MySQL分析

    访问最多的数据
    执行最多的查询
    停留时间最长的状态
    执行查询使用最频繁的子系统
    查询过程中访问的数据种类
    执行了多少种不同类型的活动

记录查询

    普通日志：记录服务器接收到每一个查询，包含没有被执行的查询。
    慢速日志：记录执行时间超过特定长度的查询
    log-slow-queries = [file-name]
    long_query_time = 2
    日志分析：长查询    影响很大的查询      新查询

分析MySQL服务器

    使用SHOW STATUS
    值得监控的变量：Bytes_received/Bytes_sent 和服务器之间来往流量
            Com_*         服务器正在执行命令
            Created_*   在查询期间创建的临时表和文件
            Handler_*   存储引擎操作
            Select_*      不同类型的连接执行计划
            Sort_*          排序信息
    使用方法：SHOW SESSION STATUS LIKE 'Select%' / SHOW STATUS LIKE '...'
    SHOW PROFILE 默认关闭。让服务器收集用于执行查询的资源的信息
            SET profiling = 1
            SHOW PROFILES\G
    SHOW PROCESSLIST 显示正在执行的查询和连接状态

架构优化

    优化数据类型    使用能正确存储和表示数据最小类型
            简单的数据类型
            尽可能把字段定义为NOT NULL
    整数
        TINYINT(8位) SMALLINT(16位) MEDIUMINT(24位) INT(32位) BIGINT(64位)
    实数
        FLOAT(4个字节) DOUBLE(8个字节) DECIMAL(保存精确的小数)
    字符串
        VARCHAR 分配自己所需要的内存，额外1、2个字节保存长度
        CHAR    固定长度，去掉末尾的空格。适合存储很短字符串或者长度近似相同的字符串
        BLOB    保存二进制数据，没有字符集和排序规则
        TEXT    字符形式，有字符集和排序规则
    日期时间
        DATETIME
        TIMESTAMP 4字节存储，范围比DATETIME小

索引

    索引类型
        B-Tree 存储引擎使用不同方式把索引保存到磁盘上。（MyISAM使用前缀压缩，InnoDB不会压缩索引）可以支持匹配全名，匹配最左前缀，匹配列前缀，匹配范围值，精确匹配部分并且匹配某个范围中另一部分，只访问索引的查询
        哈希索引
        空间索引
        全文索引 MyISAM特有
    索引策略
        查询中没有隔离索引的列，MySQL不会使用索引。隔离列：不是表达式的一部分，也没位于函数中
        前缀索引  使索引需要空间变小，但会降低选择性。如果索引BLOB和TEXT列，或者很长的VARCHAR列，必须定义前缀索引。确定索引前缀长度，1）找到最常见的值和常见前缀对比 2）计算全列选择性，使前缀的选择性接近它
        聚集索引  存储数据方式。数据行保存在索引的叶子，实际的数据行和相关键值保存在一起。（InnoDB支持）InnoDB按照主键聚集,没有主键使用唯一非空索引，没有索引就使用InnoDB定义隐藏的主键。
                          优点：相关数据保存在一起，数据访问快
                          缺点：插入速度依赖插入顺序，按主键顺序插入最快。
                                      更新索引列代价大。
                                      插入新行或者行主键被更新，该行必须被移动时候会进行分页。导致更多磁盘空间。
                                      第二索引访问需要两次索引查找（叶子节点保存了行的主键值）
     覆盖索引  包含所有满足查询需要的数据的索引。保存包含的列的数据
                          优点：如果只读取索引，减少数据访问量
                                      按照索引值排序，I/O密集型访问会比随机访问快
                                      可以避免InnoDB第二索引的第二次索引查询
     排序使用索引扫描
              两种排序方式：文件和扫描有序的索引。EXPLAIN中type列为index，说明会扫描索引
              按照索引对结果进行排序，只有当索引顺序和ORDER BY子句中的顺序完全一致，排序方向也要一样。查询中的ORDER BY子句，要使用索引的最左前缀。
    压缩索引
              MyISAM使用前缀压缩减少索引大小
    索引和表维护
        查找并修复表  CHECK TABLE 、REPAIR TABLE
        更新索引统计  ANALYZE TABLE
        减少索引和碎片  OPTIMIZE TABLE或者转储并重新加载数据

存储引擎说明

        MyISAM  表锁  不支持自动数据恢复  不支持事务  紧密存储  只有索引保存在内存中
        InnoDB  事务性   外键   行级锁   多版本  按住键聚集  所有索引包含主键列  优化缓存  未压缩索引  数据装载缓慢   阻塞AUTO_INCREMENT  没有缓存的COUNT(*)值