高性能MySql读书笔记

以下是Xmind笔记，看着有些费劲。。。记录下

高性能Mysql
    mysql架构和基础
        mysql逻辑架构图
            客户端
            服务器层
                连接/线程处理、查询缓存、解析器、优化器
            存储引擎
        锁粒度
            表锁：服务器会为诸如 alter Table之类语句使用表锁，而忽略存储引擎的锁机制
            行级锁：只在存储引擎层，不在服务器层
        事务
            ACID
                atomicity 原子性
                consistency 一致性
                isolation 隔离性
                durability 持久性
            隔离级别
                Read uncommitted
                Read  committed
                Repeatable read  (mysql默认)
                Serializable 可串行化
            死锁
                InnoDB 处理方法：将持有最少行级排它锁的事务进行回滚
            服务器层不管理事务，是由下层的存储引擎实现的。  最好不要在事务中混合使用存储引擎（InnoDB-事务型、MyIsAM-非事务型）
        MVCC 多版本并发控制
            在每条记录后保留2条隐藏的列：行版本号、行删除版本号。每开始一个事务，系统版本号自动递增。Select：只查 【 行版本号<=当前系统版本号 && 行删除版本要么未定义，要么大于当前系统版本号--确保未被删除】。
        存储引擎
            InnoDB (mysql默认)
                事务型、高并发  间隙锁、聚簇索引、热备份
            MyIsAM
                不支持事务(没有回滚)、不支持行级锁、不支持崩溃后的安全恢复
                对整张表加锁
                对于只读数据可以考虑
        导入导出
            Create table a Like b; Insert into a  Select * from b;
    Schema与数据类型优化
        数据类型
            整数类型： -2^(N-1)到2^(N-1)-1.  Unsigned。INT(11) 指定宽度，只是规定了交互工具(mysql命令行客户端)来显示字符的个数，值的合法范围，比如INT(1)和INT(20)是相同
            实数类型：DECIMAL ，数据量大时也可以用BIGINT代替
            字符串类型
                VARCHAR 可变长字符串、CHAR 定长，括号里的是字符数
                ENUM 枚举代替字符串类型
            日期和时间类型：TIMESTAMP(1970年-2038年) 效率更高、DATATIME(1001年-9999年)
            IPv4地址：32位无符号整数，不是字符串
        范式和反范式
            第一范式：无重复的列
            第二范式：属性完全依赖于主键
            第三范式：属性不依赖于其他表的非主键
            反范式可以避免表关联，所以一般是 混用范式和反范式
        缓存表与汇总表
            缓存表：存储从其他表获取速度慢的信息，例如 逻辑上冗余的数据
            汇总表：保存 Group By语句聚合数据，例如 逻辑上不冗余的数据
        总结
            一切为了  更快地读，更慢地写
            尽量使用可以正确存储数据的最小数据类型
            尽量 NOT NULL
    索引
        索引类型
            BTREE，InnoDB使用B+Tree，通过比较节点页的值和要查找的值找到合适的指针进入下层子节点（二分查找），有序的
            哈希索引：对索引列计算一个哈希码
                InnoDB有 自定义哈希索引：当注意到某些索引列被使用得非常频繁时，它会在内存中基于BTree索引之上再创建一个哈希索引，自动的内部行为
                创建自定义哈希索引
                    新增一个被索引的url_crc 列
                    Select id From url Where url_crc=CRC32("http://myslq.com")  And url="http://myslq.com";
                无法排序、不支持部分索引列匹配查找、不支持范围查询
        索引策略
            独立的列：索引列不能是表达式的一部分，也不能是函数的参数
            前缀索引
                索引开始的部分字符，提高索引效率。对于BLOB、Text、很长的VARCHAR，必须使用前缀索引
                索引的选择性：不重复的索引值（基数）/数据表的记录总数（#T），1/#T到1之间，例如 唯一索引的选择性=1     --P154
                无法Order By 和Group By，无法覆盖扫描
            多列索引
                在多个列上建立独立的单列索引大部分情况下不能提高mysql的查询性能
                Or  -> UNION ALL
                选择合适的索引列顺序：当不需要考虑排序和分组时，将选择性最高的列放在前面通常是最好的
            聚簇索引 (InnoDB)
                数据存储方式，表示数据行和相邻的键值紧凑地存储在一起。一般 一个表只能有一个聚簇索引
                叶子页包含了行的全部数据，但是节点页只包含了索引列
                缺点： 二级索引访问需要俩次索引查找。 存储引擎需要找到二级索引的叶子节点获得对应的主键列，然后根据这个值取聚簇索引中查找到对应的行
                聚簇索引：主键索引，非聚簇索引：二级索引    --P167
            主键索引：Auto_Increment
                并发插入 可能导致间隙锁竞争  ，innodb_autonic_lock_mode配置=0：锁，保证ID连续； >=1: 性能提升，ID可能不连续
            覆盖索引
                定义：一个索引包含所有需要查询的字段的值
                由于InnoDB的聚簇索引，覆盖索引对InnoDB表非常有用：若二级主键能覆盖查询，则可以避免对主键索引的二次查询。
                延迟关联：  P174、P187
                    Select name,sex From profiles Where sex='M' Order By rating Limit 100000,10
                    转化为 Select name,sex From profiles Inner Join( Select id From profiles where x.sex='M' Order By rating Limit 100000,10) As x  Using(id);
            索引扫描来做排序
                EXPLAIN出来的type列："index"
                若索引不能覆盖查询所需的列，按索引顺序读取的速度通常要慢于顺序全表扫描，因为 每扫描一条索引都得回表查询
                OrderBy 索引最左前缀
                查询需要关联多表，则只当OrderBy子句所引用的字段全部为第一个表时，才能使索引用作排序
        总结
            BTree索引的限制
                最左前缀
                不能跳过索引的列
                若查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找
            mysql 不能在索引中执行LIKE操作
            只在索引中做最左前缀匹配的LIKE比较，若是通配符开头的LIKE查询，将无法匹配
            尽量扩展已有索引而不是创建新索引，但有时出于性能考虑需要冗余索引
            id > 45  转化为  id IN (1,4,99) ：范围条件查询，就无法使用范围列后面的其他索引列，但是对于多个等值查询就可以
    查询性能优化
        慢查询
            是否向数据库请求了不需要的数据？ LIMIT、只取需要的列
            是否在扫描额外的记录？ 
“Extra: Using Where”: Mysql将通过Where条件来筛选存储引擎返回的记录
                索引中使用where 是在存储引擎层完成
                索引覆盖扫描（Extra：Using index）直接从索引中过滤不需要的记录并返回命中结果，无须再回表查询，Mysql服务器层完成
                Using Where 需要先从数据表读出记录然后过滤，Mysql服务器层完成
        重构查询方式
            大查询分解成多个小查询
            分解关联查询
                让缓存的效率更高：关联中的某个表变化，就无法使用查询缓存，
拆分后，某个表很少变化可重复利用查询缓存结果
        查询的过程 -图P204
            mysql客户端/服务端通信协议
                半双工，要么服务器向客户端发数据，要么客户端到服务器， 俩个动作不能同时发生
                库函数实际是从缓存获取数据
                查询状态   -P207
            查询缓存
                查询优化器
                    静态优化：直接对解析树分析优化，第一次完成后一直有效，编译时优化
                        Or -> IN（）先对列表内数据排序，再二分查找，O（log N）优于O（N）
                    动态优化
                关联查询 ：嵌套循环关联
                    先在一个表循环取出单条数据，再嵌套循环到下一个表寻找匹配的行，直到找到所有表的匹配的行为止，然后回溯到上一个表
                    关联查询优化器 评估不同顺序的成本来选一个代价最小的关联顺序
                排序优化 不能用索引生成排序结果时，需自己排序（filesort）
                    俩次传输排序 ， I/O成本高
                    单次传输排序，当查询需要所有列的总长度不超过参数
max_length_for_sort_data ,Mysql使用此排序算法。
            查询执行引擎
                逐步执行计划
            返回结果给客户端
                增量、逐步返回的过程
        查询优化器的局限性
            糟糕：Where条件中包含IN（）的子查询语句
                Select * From film Where film_id IN（
Select film_id From film_actor Where actor_id = 1）
                转化为 
 Select film.* From film inner Join film_actor Using(film_id) Where actor_id = 1
            UNION
                Union的各个子句中分别使用这些 Order By、Limit
        优化特定类型查询
            Count
                count（列值）：不统计列值NULL
                最好用Count（*）
            优化关联查询
                确保Group By和Order By只涉及到一个表中的列，这样索引才可以优化
            优化子查询
                尽量用关联查询代替
            优化Limit 分页
                延迟关联 -见上一章
            优化UNION查询
                创建并填充临时表来执行Union查询； 尽量用Union All，否则会对整个临时表做唯一性检查，代价高
            使用用户自定义变量
                Set @rownum := 0; 
Select id,@rownum := @rownum + 1 AS rownum From actor
        总结
            分组查询：若可以，在应用程序中做超级聚合更好
            快速地完成事情：尽量使用Update 代替先Select For Update再Update的写法，因为事务提交的越快，持有锁的时间越短
            尽量少做事：看是否真的需要这么精确的计算，可以运用简单的方案过滤大多数数据
    高级特性
        分区表  (粗粒度)
            定义：是一个独立的逻辑表，但是底层由多个物理子表组成。底层表必须相同存储引擎
            CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    -- other columns omitted
)ENGIN=InnoDB PARTITION BY RANG(Year(order_date))(
    PARTITION p_2010 values less than (2010),
    PARTITION p_2010 values less than (2010),
    PARTITION p_2010 values less than (2010),
    PARTITION p_catchall values less than MAXVALUE );,
                表达式返回的值是一个确定的整数，且不能是常数
            策略
                全量扫描数据，不要任何索引（根据分区的规则大致定位需要的数据位置）
                索引数据，并分离热点  (数据有明显的热点，可以单独放一个分区)
            可能的问题
                Null值会使分区过滤无效
                    PARTITION BY RANGE COLUMNS(order_date)  
--直接使用列本身而不是基于列的函数，mysql5.5版本
                尽量分区列和索引列匹配
                限制分区的数量，一般最多100个左右分区
            总结
                要做Where条件带入分区列！！！
                根据粗粒度索引优势，通过分区过滤通常可以让查询扫描更少的数据。
        视图
            本身是一个虚拟表，不存放任何数据，返回的数据是从其他表中生成的
            CREATE VIEW Oceania AS 
    Select * from country where continent = 'Oceania'
    With CHECK OPTION;
            合并算法、临时表算法（包含Group By、Distinct、聚合函数、Union、子查询）
            不支持在视图上建触发器
            只有合并算法的视图 才可更新。
（update Oceania Set population = popluation * 1.1 Where Name='Australia'）
        存储过程
            适用于：   相比查询执行的成本，解析和网络开销很明显
        触发器
            每个表的每个事件，最多只能定义一个触发器
            基于行的触发
            作用： 实现更新反范式化数据、记录数据变更日志、系统维护任务、约束
        事件
            指定mysql 在某个时候执行一段代码，或者每隔一段时间间隔执行一段代码
        绑定变量
            SET @sql := 'Select id,first_name  FROM actor where first_name=？'；
            客户端向服务器发送一个 sql 语句原型，服务器解析并存储这个 sql 部分执行计划，返回客户端处理句柄。以后每次执行这类查询，客户端都指定使用这个句柄。
        字符集和校对规则
            一种从二进制编码到字符的映射；  校对： 用于字符集的排序规则
            服务器 -> 数据库 -> 表，这是一个逐级继承的默认设置，最靠底层的默认设置影响
            大小写敏感：_cs  、_ci (case sensitive;  case insensitive)；
 字符串编码的二进制：_bin
                col2 CHAR(1) CHARSET utf8,
col3 CHAR(1) COLLATE latin1_bin
            只有排序查询要求的字符集与服务器数据的字符集相同的时候，才能使用索引进行排序
        全文索引 （B-Tree）
            通过关键字匹配来进行查询过滤，基于相似度的查询
            只有MyISAM 支持全文索引，msql5.6版本后，InnoDB 也支持了
            自然语言的全文索引
                在整个索引中出现次数越少的词语，匹配时的相关度就越高
                SELECT　film_id,title, RIGHT(description,25)
From film_text
Where MATCH(title,description)   AGAINST('factory casu');
                自动按照相似度进行排序
            布尔全文索引
                SELECT　film_id,title, RIGHT(description,25)
From film_text
Where MATCH(title,description)   AGAINST('+factory  +casu'  IN  BOOLEAN MODE);
                只有MyISAM 引擎才能使用
        查询缓存
            定义：缓存完整的select 查询结果
            默认应该关闭查询缓存；
若实在查询缓存作用很大，也要配置很小的查询缓存空间（几十兆）
            若查询语句包含任何不确定的函数，那么查询缓存不能找到缓存结果。如：current_date
            命中和写入的比率，Qcache_hits/Qcache_inserts >3:1  通常查询缓存是有效的；即缓存带来的资源节约大于其本身的资源消耗；
            Flush query cache: 碎片管理。小心用，因为无法访问查询缓存，服务器会僵死一段时间。
            query_cache_size  设置成0， 来关闭查询缓存。
            总结：
                对于写密集型应用，直接禁用  可以提高系统性能