mysql 高级第四天:
    回顾:

    1. 索引的使用
      a. 全值匹配我最爱
      b. 最佳左前缀原则
      c. 计算、函数导致索引失效
      d. 范围条件右边的列索引失效
      e. 不等于(!= 或者<>)索引失效
      f. is not null无法使用索引,is null可使用索引
      g. like以通配符%开头索引失效
      h. 类型转换导致索引失效
    1. 2. 关联优化:
    2. left join
    3. 在从表{被驱动表}建立索引!
    4. inner join
    5. mysql 优化器会自动识别谁是被驱动表!
    6. 3. 排序、分组优化
    7. 总结:
    8. my.cnf 的配置文件中 mysql5.7 使用的是单路
    9. 提高order by 的排序速度:
    10. 增大sort_buffer_size参数的设置 1M-8M
    11. 增大max_length_for_sort_data参数的设置 1024-8192字节
    12. 减少select 后面的查询的字段。 禁止使用select *
    13. 4. 如果有子查询和left Join
    14. 优先选择 left Join
    15. NOT IN --> LEFT JOIN xxx ON xx WHERE xx IS NULL
    16. 5. 索引覆盖
    17. 见图:
    18. 6. 如何优化sql 语句:
    19. 步骤:
    20. where 后面的条件; 尽量添加索引
    21. on 条件 在被驱动表建立索引,尽量少使用子查询尽量使用左关联
    22. group by order by 尽量使用索引! 细节问题:如果在where出现范围的话,与排序字段有冲突的情况下!
    23. 参考索引使用进行修改!
    24. 7. 慢查询了解

    重点:

    1. 完成mysql 高级讲解
    1. 2. mycat
    2. 1. 视图;
    3. a. sql 语句封装的虚拟表
    4. b. 提高复用性
    5. c. CREATE [OR REPLACE] VIEW <view_name>
    6. AS
    7. <SELECT 语句>;
    8. OR REPLACE --表示替换以有的视图
    9. 2. 主从复制;
    10. 基本原理:
    11. io线程从主服务器读取二进制日志文件,sql 线程读取中继日志文件,并执行。保证主服务器与从服务器的数据一致性!
    12. 步骤:
    13. 1. 需要一个主机,一个从机!
    14. 我拷贝的!
    15. 修改ip 地址!
    16. LINUX 有自己的uuid 唯一标识! 如果我们是拷贝的!那么这个uuid 是不会发生变化的!
    17. 一台:166
    18. 一台:168
    19. 2. 在主机添加配置数据
    20. 3. 从机添加配置数据
    21. 4. 重启服务器并关闭防火墙
    22. 5. 在主机上建立帐户并授权slave并记录当前文件名并且记录位置!
    23. 6. Linux从机上配置需要复制的主机
    24. CHANGE MASTER TO MASTER_HOST='主机ip地址',
    25. MASTER_USER='X',MASTER_PASSWORD='X',
    26. MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;
    27. 修改之后:
    28. CHANGE MASTER TO MASTER_HOST='192.168.200.168',
    29. MASTER_USER='slave',MASTER_PASSWORD='123456',
    30. MASTER_LOG_FILE='mysqlbin.000001',MASTER_LOG_POS=590;
    31. Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
    32. these UUIDs must be different for replication to work.
    33. 如果出现这个错误,需要改一下uuid 值即可!
    34. 因为修改了uuid,所以我们mysql 主机的文件名称可能发生变化!
    35. 注意文件名称:课件有坑,大家下载最新的课件!
    36. start slave; 开启主从复制!
    37. show slave status \G;
    38. 主要看IO,SQL 线程是否是yes
    39. stop slave; 停止主从复制!
    40. 7. 测试
    41. 3. mycat
    42. 数据库中间件
    43. mycat:读写分离,数据分片,整合多个数据源
    44. 原理:拦截sql 语句,进行分析!
    45. 4. mycat 安装:
    46. 168:主 mycat
    47. 主要配置文件:
    48. schema.xml:定义逻辑库,表、分片节点等内容
    49. rule.xml:定义分片规则
    50. server.xml:定义用户以及系统相关变量,如端口等
    51. 配置:
    52. server.xml
    53. schema.xml
    54. 启动mycat
    55. ./mycat console : 推荐这种方式启动
    56. 登录mycat
    57. 管理者:mysql -umycat -p123456 -P 9066 -h 192.168.200.168
    58. 服务器:mysql -umycat -p123456 -P 8066 -h 192.168.200.168
    59. <property name="serverPort">8066</property> <property name="managerPort">9066</property>
    60. 5. mycat 进行读写分离
    61. a. 搭建好主从!
    62. b. 修改配置文件!
    63. 6. mycat 垂直拆分:
    64. 垂直 分库 ,水平 分表!
    65. a. 先停止主从复制,停止mycat
    66. b. 修改配置文件!
    67. schema.xml
    68. c. 在两个节点上创建两个数据库
    69. dn1 168
    70. dn2 : 166
    71. CREATE DATABASE orders;
    72. dn1 上创建:
    73. #订单表 rows:600万
    74. CREATE TABLE orders(
    75. id INT AUTO_INCREMENT,
    76. order_type INT,
    77. customer_id INT,
    78. amount DECIMAL(10,2),
    79. PRIMARY KEY(id)
    80. );
    81. #订单详细表 rows:600万
    82. CREATE TABLE orders_detail(
    83. id INT AUTO_INCREMENT,
    84. detail VARCHAR(2000),
    85. order_id INT,
    86. PRIMARY KEY(id)
    87. );
    88. #订单状态字典表 rows:20条
    89. CREATE TABLE dict_order_type(
    90. id INT AUTO_INCREMENT,
    91. order_type VARCHAR(200),
    92. PRIMARY KEY(id)
    93. );
    94. dn2 上创建:
    95. CREATE TABLE customer(
    96. id INT AUTO_INCREMENT,
    97. NAME VARCHAR(200),
    98. PRIMARY KEY(id)
    99. );
    100. d. 启动mycat
    101. dn1,dn2 都在mycat中配置好!
    102. 7. 水平分表:
    103. 分表:按照某个字段的某种规则来分散到多个库之中!
    104. 讨论,研究一下分表的规则!
    105. 修改配置文件:
    106. schema.xml:定义逻辑库,表、分片节点等内容
    107. rule.xml:定义分片规则
    108. 保证两个节点中都有表{orders}
    109. 重启:mycat
    110. 使用mycat 进行链接 mycat 的客户端中进行插入数据!
    111. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100);
    112. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
    113. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
    114. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
    115. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
    116. INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
    117. 总结:
    118. 垂直分库:
    119. 1. 在配置文件中进行配置:
    120. 2. 在两个节点上创建两个数据库
    121. 3. 相关的表在一个数据库中。没用相关的表在另一个库中!
    122. 水平分表:
    123. 1. 找到分表的规则! 客户Id
    124. 2. 在每个节点上 创建对应的表
    125. 3. 修改配置文件 schema.xml rule.xml
    126. E-R 表操作:
    127. 可以使用join 关联!
    128. 1. 配置schema.xml
    129. 2. 每个节点上都应该有对应的表
    130. 3. 重启mycat,插入数据,测试!
    131. 全局表:
    132. 字典表: 有可能跟客户表有关系,也有可能跟订单表有关系! 称之为全局表!
    133. 总结:
    134. 1. 在每个节点上都应该有这个表的存储!
    135. 2. 每个节点中的数据都应该保持一致!
    136. mycat 要想做关联查询: 要么E-R 表操作!要么就做全局表! 通过全局表+基于 E-R 关系的分片策略
    137. 全局序列:
    138. 建议使用数据库!

    1. 总结:
    2. 1. mysql 视图;
    3. 2. mysql 主从复制
    4. 3. 会使用mycat 做读写分离
    5. 4. mycat 垂直分库,水平分表:
    6. 5. E-R表,全局表
    7. 6. 使用数据方式生成全局序列!