关系型数据库优化-CSDN博客

本文链接：https://blog.csdn.net/W18223596054/article/details/143058150

1.课程介绍

关系型数据库优化概述(了解)
定位慢SQL(掌握)发现问题
分析慢SQL(掌握)分析问题
常规优化(掌握)解决问题

2.关系型数据优化概述

2.1 为什么需要优化-关系型数据库瓶颈

2.1.1 高并发读写需求

网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。例如：双十一购物

双十一购物节，号称优惠比较多。所以很多人都在那天购物，特别是11.11凌晨的时候，成千上万或上亿，一起查询、浏览商品，下单购物。实质上，商品订单等信息是保存到数据库中的，那是不是很多人同时获取数据库连接，同时操作数据（读写），操作完成后关闭连接。那，是不是要同时支持很多连接，并且读写。那，现状我们一台数据库最大连接数是不是有限的。不能满足很多人同时读写，是有瓶颈的。

2.1.2 海量数据的高效率读写

网站产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率是非常低的.例如：商品表

像天猫、京东等购物商城，某些数据是巨大的，比如说商品，用户等。存放到数据库中会有怎么样的效果呢？是不是一张包含很多数据（上亿条记录）的表，那我们查询、插入起来是不是速度会很慢。也就是说海量数据表的的读写速率比较低。

2.1.3 高扩展性和可用性

在基于web的结构（即浏览器/服务器）当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。对于很多需要提供24小时不间断服务的网站来说，对数据库系统进行升级和扩展是非常痛苦的事情，往往需要停机维护和数据迁移.

动态添加服务器一台数据库服务器扩充到多台时，不断电情况是很难做到的。

单点故障：一台数据库服务器挂了。业务就中断，期望去找还好的数据库继续提供服务。

2.2对数据库性能影响的因素

2.2.1商业需求对性能的影响

不合理的需求：一个论坛帖子的总数量，附加要求：实时更新

问：非要实时更新吗？

2.2.2数据库设计对性能的影响

某些数据不适合基于数据库查询：比如大量数据的全文检索

解决方案：基于ES全文检索

热点数据高频查询

解决方案：合理的缓存，提高响应速度

多余的数据库交互，重复的SQL

减少数据库交互次数，减少重复相同SQL的重复执行

错误的表设计：比如几十个字段全部仍在一张表

冗余字段；大表拆小标(垂直分表，水平分表)；常用属性分离成小表；

不合理的索引设计

2.2.3 SQL编写的不合理对性能的影响

太多表JOIN

使用冗余字段减少JOIN 最多三个联表

SQL太复杂

用一个非常复杂的SQL且进行了大量的计算，有时候还不如发多条简单SQL去查询数据，在代码中去处理结果。

不合理的查询结果

SELECT * ，明明不需要那些字段，非要全部查询出来

2.2.4 其他方面

硬件环境

网路环境

2.3 关系型数据库的优化技术整体介绍

Mysql优化是一个复杂的过程，我们可以分为单机优化和多机优化、最终我们对数据库的所有操作都会转换为一条sql的执行.对关系型数据库的优化是一个综合性的技术，主要包括：

找出执行效率低Sql（定位慢查询）- 发现问题

分析慢Sql expain sql - 分析问题

优化 - 解决问题

常见优化手段

*单机*	*多机*	*其他方案*
合适表结构 3ＮＦ和反3NF	集群（读写分离，让多台服务器提供服务）	Redis缓存
合适引擎	分布式（把不同的业务分给不同的集群处理）	ElasticSearch全文检索
索引	分库	页面静态化
分表(垂直分表和水平分表)
Sql优化技巧

注意：

1、优化方案不仅可以适用在mysql,还可以使用于oracle等关系型数据库,只是命令不同罢了。

2、优化时先考虑单台数据库服务器，如果单台优化完成后,实在满足不了需求再考虑多机集群和分布式。(有的公司不会使用多台数据库)

2.4 准备数据

见“准备数据.doc”

3.定位效率低的SQL

3.1 mysql的执行原理

mysql主要分为Server层和存储引擎层

Where name like “%zs%” and id =1

Where id =1 and name like “%zs%”

Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。

存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。InnoDB 5.5.5版本作为默认引擎。

连接器

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

查询缓存

连接建立后，执行查询语句的时候，会先查询缓存，Mysql会先校验这个sql是否执行过，以Key-Value的形式缓存在内存中，Key是查询预计，Value是结果集。如果缓存key被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

Mysql 查询不建议使用缓存，因为对于经常更新的数据来说，缓存的有效时间太短了，往往带来的效果并不好，对于不经常更新的数据来说，使用缓存还是可以的，Mysql 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

分析器

mysql 没有命中缓存，那么就会进入分析器，分析器主要是用来分析SQL语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条SQL语句有多个字符串组成，首先要提取关键字，比如select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的sql是否正确，是否符合mysql的语法。

完成这2步之后，mysql就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

优化器

优化器的作用就是它认为的最优的执行方案去执行（虽然有时候也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

执行器

当选择了执行方案后，mysql就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用存储引擎的接口，返回接口执行的结果。

3.2 定位慢查询-找出执行慢sql

3.2.1原理

我们使用数据库，实质上就是连接数据库，发送SQL、服务器执行SQL并返回结果、关闭连接。也就是所有的SQL语句MySQL服务器都能感知到，比如执行了那些SQL，都执行了多少时间等。我们做优化就是找出执行时间长的Sql进行优化。问题是：如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)

所有sql都是mysql执行，当它发现客户端执行sql慢的时候，会把结果记录下来。方便用户查找定定位。

3.2.2如果用的是Druid数据库连接池，可以使用它提供的监控功能

SpringBoot + Druid监控

3.2.3查询数据库(mysql)基本状态

首先我们了解mysql数据库的一些运行状态(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接/最大连接)

Mysql运行多久

show status like 'uptime';

CRUD执行次数

show status like '%Com_%'

show status like '%Com_select%'

show status like '%Com_insert%'

show status like '%Com_update%'

show status like '%Com_delete%'

完整语法：Show session/global status like '%Com_select%'

show [session|global] status like .... 如果你不写[session|global] 默认是session 会话(指取出当前窗口的执行)，如果你想看所有(从mysql 启动到现在)，则应该 global。

查看INNODB引擎的CRUD行数

show status like '%Innodb_rows%';
查询所有连接数

show status like 'connections'
查看服务器响应的最大使用连接数

show status like 'Max_used_connections'

通过该连接数可以知道应用的DB并发，从而设置Mysql的最大连接数

通过查询（工作环境）最大并发连接数可以配置作为我们配置mysql最大连接数的依据。通常，mysql的最大连接数默认是100, 最大可以达到16384（理论上）。可以通过my.ini中的max_connections=100配置项目进行修改

3.2.4 定位慢查询

查看执行的线程

show processlist

可以用来查看当前数据库SQL正在执行的情况，定位SQL的状态

可以通过state查看SQL的状态，比如Locked 就是被锁住了

Checking table
　正在检查数据表（这是自动的）。
Closing tables
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
　复制从服务器正在连接主服务器。
Copying to tmp table on disk
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
　正在创建临时表以存放部分查询结果。
deleting from main table
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
　被其他查询锁住了。
Sending data
　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group
　正在为GROUP BY做排序。
　Sorting for order
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
　修复指令正在排序以创建索引。
Repair with keycache
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update
　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
　正在等待客户端发送新请求.
System lock
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
Waiting for tables
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

查询慢查询次数

show status like 'slow_queries' 慢查询，通常指花了2S以上的查询(默认10s)

查看和修改慢查询时间阈值

show variables like 'long_query_time';//可以显示当前慢查询时间，默认阈值 10s

set long_query_time=1 ; //可以修改慢查询时间，可以通过my.ini永久修改

注意：直接修改global 的long_query_time 之后在当前的的窗口中是没有效果的，在新打开的窗口中才会有效果。如果想让本窗口也有效果的话，不用加 global关键字。

查看慢查询的日志路径

show variables like '%slow_query_log%';

5.6以上版本使用： show variables like '%slow-query-log-file%'

可以使用 select * from mysql.slow_log ，查看日志内容

开启全局日志慢查询日志

SET GLOBAL slow_query_log = 1 //开启慢查询日志

SET GLOBAL slow_query_log_file='路径\Data\LAPTOP-20VLGCRC-slow.log' //指定慢查询日志文件

上面的修改是临时性的，要永久修改还是要改my.ini文件

把查询日志记录到table

通过执行： set global log_output='TABLE'; 或者修改my.ini ：log_output="FILE,TABLE"

通过执行：select * from mysql.slow_log; 查看日志内容

记录未使用索引的SQL

set global log_queries_not_using_indexes=ON

配置总结（在测试环境做压测调优使用）：

[mysqld]
#慢SQL阈值
long_query_time=0.00001
#开启慢sql日志
slow_query_log=1
#慢SQL日志
slow_query_log_file="D:/opensource/MySQL/MySQL Server 5.5/slow.log"
#日志记录到文件和表中
log_output="FILE,TABLE"

3.2.5步骤总结

1）关闭原有mysql服务

2）以支持记录慢sql的方式来启动mysql

3）设置慢查询时间阀值

4）构造慢sql

5）到日志文件中找慢sql（data.dir）

3.2.6 什么时候开启慢查询

系统中所有sql都执行一遍，才能判断是否有慢sql。什么时候开启能覆盖所有sql执行？

开发者自验：

开发完成后，需要统一打包，统一部署，统一验证，开发者在集成测试的时候可以开启慢查询

测试人员测试：

测试人员需要测试所有功能。

项目上线：开一段时间,把它关了.或者不开

用户用了所有功能。

开启日志记录是会影响性能的，线上环境不能开启慢查询记录

3.3 explain(分析sql语句)

-- 通过 explain 语句可以分析，mysql如何执行你的sql语句
explain select * from t_course

解释：该SQL是一个简单的select语句，查询的是t_couse表，使用的是全表扫描，没有用到索引，找到所需的记录所需要读取11行数据

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序两种情况 id相同，执行顺序从上往下； id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

3.4 Profiling(基本上不使用放弃)

Explan只能看到Sql的执行情况，是否使用索引，profiling命令可以详细的看到SQL的IO，CPU的使用情况 ,以及底层执行SQL的每一个细节步骤
1. 开启Profiling
使用 cmd去执行mysql，不用navcat工具
Mysql> set profiling = 1;    //开启SQL的profiling统计
Mysql> select * from uer   //执行sql
Mysql> set profiling = 0;   //关闭profiling
2.查看记录的Query
Mysql> show profiles;
3.查看cpu,io等
Mysql> show profile cpu,block io for query 6;
Mysql> show profile for query 6;

4.优化1-单机优化

4.1 范式&存储引擎

4.1.1 范式（规范）-常见有三种规范3NF

表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

1NF: 即表的列的具有原子性,不可再分解，即列的信息，不能分解.只要数据库是关系型数据库(mysql/oracle/db2/sysbase/sql server)，就自动的满足1NF.关系型数据库中是不允许分割列的即：列唯一

2NF :表中的记录是唯一的.通常我们设计一个主键来实现，即：行唯一

3NF：即表中不要有冗余数据，就是说，表的信息如果能够被推导出来，就不应该单独的设计一个字段来存放。可以理解为：A表不能出现B表中非主键字段。

反3NF ：3NF指的是A表不能出现B表中非主键字段，如果出现了，那这个字段就是冗余字段，违反了三范式。

举例：使用冗余字段减少JOIN连表提高查询效率

课程表中出现了字典表中的非主键字典，grade_name ，这违反了三范式，这种字段叫冗余字段。

冗余字段的好处是减少表的JOIN ,提高SQL的执行效率。坏处是原始值发生变动，冗余的值也需要跟着变

举例说明：课程中的销量，浏览量，评论数

课程浏览记录表：

要统计每个课程的浏览量，从课程浏览记录表中可以通过 JOIN 表和COUNT 函数统计出每个课程的浏览量，但是这个表中的数据量比较大的时候，查询会比较慢，可以使用冗余字段来提高效率如下：

有的时候也可以专门抽一张表作为冗余表，比如要对于上面三个数字，sale_count,view_count,comment_count,我们可以专门搞一个数据汇总表来统计这三个值，查询的时候从这个数据汇总中查询即可：

t_course_summary

Course_id	Sale_count	View_count	Comment_couent
11	100	100	200

只不过需要注意的是，每次有课程销售，课程有评论，课程有浏览，这里的汇总表的数量也要变动，我们可以在代码层面去控制，也可以使用数据库触发器来实现：

数据库触发器：mysql触发器trigger 实例详解 - 花生福 - 博客园

4.1.2 存储引擎-创建表时要选择存储引擎

分类：mysql:myisam,innodb,memory

优缺点：问 MyISAM 和 INNODB的区别(主要)

事务安全：MyISAM不支持事务，INNODB支持

查询和添加速度：MyISAM速度快，INNODB速度慢

支持全文索引：MyIsam支持，innodb不支持

锁机制 MyIsam表锁 innodb行锁

外键 MyISAM 不支持外键约束， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)

使用场景

MyISAM存储引擎：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的发帖表，回复表.

INNODB存储引擎: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

Memory 存储：比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.

操作

创建表时指定存储引擎

create table 表名(字段列表) engine 存储引擎名称

CREATE TABLE t_temp(id BIGINT PRIMARY key,name VARCHAR(20))  ENGINE myisam;

注意：如果不指定则使用默认的存储引擎，这个默认实在my.ini配置

My.ini增加配置： default-storage-engine=INNODB

修改存储引擎：alter table table_name engine=innodb;

4.2 Mysql的索引

4.2.1 引入

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的‘create index’，查询速度就可能提高百倍千倍。查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。以空间换时间

4.2.2 什么是索引

索引（Index）是帮助DBMS（数据库）高效获取数据的数据结构，索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。如同：字典的目录。

比如：根据id查找数据，如果没有使用索引，就会从第一行记录开始扫表，直到找到想要的ID为止，当数据量比较大，恰好想要的数据在最后一条，那么查询速度就会非常慢：如： where id = 9

使用索引：Id列使用索引后，Mysql会为id列维护一个索引结构，数据检索的时候就从索引中进行检索，能大大提高检索速度。举例：

4.2.3 索引分类

mysql中索引的分类

普通索引Normal：允许重复的值出现，可以在任何字段上面添加
唯一索引 Unique：除了不能有重复的记录外，其它和普通索引一样，可以在值是唯一的字段添加（用户名、手机号码、身份证、email，QQ），可以为null,并且可以有多个null
主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值,
全文索引：用来对表中的文本域(char，varchar，text)进行索引，全文索引针对MyISAM有用InnoDB不支持全文索引，所以一般不用,默认只支持英文. -使用ES,Lucene代替就ok

4.2.4 索引的方式

索引方式分为：BTREE 和 Hash

Hash方式底层使用的是Hash表算法，时间复杂度是n(1) ,一次IO就能查询到结果，打算Hash的场次是无序的，有如下缺点：

Hash结构的，InnoDB,MyIsam都不支持Hash

只能进行等值查询( = , in)，不能使用范围查询（ > ，< ，Between ）

列的重复值过多会出现大量Hash冲突问题

InnoDB不支持Hash方式， Memory存储引擎可以用到

Mysql常用引擎允许的索引类型

储存引擎	允许的索引类型
Myisam	BTREE
InoDB	BTREE
Memory	Hash,BTREE

FullText全文索引算法，myisam,只能能在char vachar text
hash通过Hash表算法，如同一个Map,通过一个key直接就能找到value
B+tree算法

4.2.5 索引的原理

4.2.5.1 InnoDB索引结构

InnoDB索引物理结构

MySQL如果使用InnoDB存储引擎，数据库文件类型就包括.frm、ibdata1，默认存储到“C:\ProgramData\MySQL\MySQL Server 5.5\data”目录下

表结构文件：xxx.frm

索引数据文件：ibdata1

InnoDB使用了b+tree作为索引结构，在InnoDB中索引和数据在同一个文件ibdata1，所以数据会存储在索引结构中。

l InnoDB索引B+Tree

下图是InnoDB的B+树索引结构，它的优势体现在：

B+Tree属于多路树，每次查询都要走到叶子节点，查询效率稳定

非叶子节点不存储完整数据，而是存储键值 KEY ，和子树节点的应用，可以存储更多的KEY，充分利用每个节点的存储空间 16KB，减少了节点数，树高变矮，IO次数变少，性能更高。

叶子节点存储完整数据，叶子节点是有序的，每个叶子节点指向下一个节点的应用，形成一个链表，适合范围查询。

InnoDB主键索引和辅助索引

对于主键默认会创建主键索引，其他列创建的索引就叫辅助索引，也叫二级索引，辅助索引的叶子节点存储的是主键索引的键，这就意味着辅助索引需要查询两个B+Tree.

这里有2个概念

回表：辅助索引扫描完之后还会扫描主键索引，这叫回表

覆盖索引：如果Select name 查询的列正好包含在辅助索引的节点的键值中，它就不需要在扫描主键索引了，这个叫覆盖索引。所以不要写Select *

问：如果表没有主键怎么办？

1.如果没有主键，Mysql会选择第一个不包含null的唯一索引作为主键索引，

2.如果不满足条件一，那么会选择一个隐藏的行RowID作为主键索引

查看隐藏rowId：Select _rowid from t_user

为什么用B+Tree

为什么不使用其他数据结构，比如数组？链表？AVL-Tree，B-Tree 而要使用B+Tree

有序数组：在查询的时候性能很高，可以二分查找，但是修改删除数据的时候会移动数组下标，性能比较差，这种结构只适合静态数据

链表：链表的删除，修改性能高，打算查询性能极差

综合上面的优势，有没有支持二分查找的链表结构呢？有：就是二叉查找树。

二叉查找树：它的查询性能和树高有关系，二叉树树高越高，查询越慢，而且在子树极端不平衡的情况下二叉树可能会变成链表，性能比较差。

AVL-Tree 平衡二叉树结构：平衡二叉查找树，如果每个节点放一个键值，数据地址，子树的引用，InnoDB节点默认最大存储为16KB，那AVL-Tree平衡二叉树的节点存储数据是远远达不到16K的，浪费了大量的存储空间。如果数据量大，就意味着树高非常高，查找一个KEY就需要遍历很多的节点，时间复杂度大，I/O次数非常高,所以查询是比较慢的

B-Tree : B-Tree：多路平衡查找树：多叉树意味着每个节点可以存储更多的KEY，多叉树可以解决每个节点空间浪费问题，也可以解决树高问题从而达到较少IO次数提高效率

但是Btree性能已经不错了，但是还不够优化，InnoDB没有使用BTree，而是使用一个更优化的树：B+树

B+Tree : 子节点存储数据，而且是有些了，相比BTree来说有如下优势：

查询效率稳定：B+Tree属于多路树，每次查询都要走到叶子节点，查询效率稳定

磁盘读写能力强：非叶子节点不存储完整数据，而是存储键值 KEY ，和子树节点的指针，

这样每个节点就可以存储更多的KEY，充分利用每个节点的存储空间 16KB，减少了节点数，

树高变矮，IO次数变少，性能更高。

排序能力强：叶子节点存储完整数据，叶子节点是有序的，每个叶子节点指向下一个节点的应用，形成一个链表，适合范围查询，和对排序支持友好

4.2.5.2 Myisam索引结构

MyISAM主键索引

myisam的数据和索引是分开的，所以树的节点指向的是数据的地址。数据存储在 course.MYD文件，索引存储在course.MYI文件中，coursr.frm是表结构定义文件，所以Myiasm的索引不存储数据，而是存储数据的磁盘地址。

下面是MyIsam索引结构，由于数据和索引是分开存放的，所以叶子节点存储的是数据的磁盘地址，而不是数据。

MyISAM辅助索引

注意：MyIsam的辅助索引的叶子节点没有指向主键索引的键值，而是直接指向的数据的磁盘地址

4.2.5 索引的操作

普通索引

索引分类：普通索引/唯一索引/主键索引/全文索引，普通索引:允许重复的值出现，一般来说，普通索引的创建，是先创建表，然后在创建普通索引

创建表时创建索引：
create index 索引名 on 表 (列1,列名2,...);
修改表添加索引
alter table 表名 add index 索引名(列1,列名2,..);

案例：
create table aaa(id int unsigned,name varchar(32));
create index nameIndex on aaa(name);
alter table aaa add index index1(name);

唯一索引

-- 建表时创建索引例如
create table bbb(id int primary key auto_increment , name varchar(32) unique);
-- 这时, name 列就是一个唯一索引，也可以在创建表后，再去创建唯一索引
create unique index 索引名 on 表名 (列1,列2,..);
alter table 表名add unique index 索引名 (列1,列2,..);
-- 为表添加索引例如
create table ccc(id int primary key auto_increment, name varchar(32));
-- 注意：unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.主键字段，不能为NULL,也不能重复

主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值

-- 创建表时指定主键 例如
create table ddd(id int unsigned primary key auto_increment ,name varchar(32) not null  defaul ‘’);
-- 这时id 列就是主键索引. 如果你创建表时，没有指定主键，
-- 在创建表后，再添加主键
alter table 表名 add primary key (列名);
-- 举例
create table eee(id int , name varchar(32) not null default ‘’);
alter table eee add primary key (id);

全文索引:用来对表中的文本域(char，varchar，text)进行索引，全文索引针对MyISAM有用

-- 创建表时定义
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;
-- 为表添加索引例如
create fulltext index 索引名 on 表名(列1,列2);
alter table 表名add fulltext index 索引名 (列1,列2);
-- 比如:
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
     )engine=myisam charset utf8;

Alter table article add fulltext index title_body_fulltext_index(title,body);

查询索引
```
show index from 表名
```

删除索引

alter table 表名 drop index 索引名; 
-- 删除主键索引
alter table 表名 drop primary key 删除主键。      [主键定义为auto_increment时不能删除]

修改索引
```
先删除后添加=修改
```

4.2.6 索引小技巧

根据索引列的多少分为复合索引(联合索引/组合索引)和普通索引

普通索引（单列索引）：该索引只在一个列上面创建

复合索引（多列索引）：该索引只在多个列上面创建

对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引(向左匹配原则)。

alter table dept add index my_indx (dname,loc);

dname 左边的列,loc就是右边的列

测试案例：

explain select * from dept where dname='aaa'       会使用到索引

测试案例：

explain select * from dept where loc='aaa'    就不会使用到索引

对于使用like的查询，查询如果是%aaa’不会使用到索引而‘aaa%’会使用到索引。

explain select * from dept where dname like '%aaa'    不能使用索引

explain select * from dept where dname like 'aaa%'   使用索引.

所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用全文索引->lucene或Es

如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引. 全部能够单独使用才能使用

explain select * from dept where dname = 'aaa'; //如果使用索引
explain select * from dept where loc = 'aaa';       //如果不能使用索引
select * from dept where dname=’xxx’ or loc=’xx’;   //不能使用索引，它没办法从两个索引树种去检索

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引

expain select * from dept where dname=’111’;

expain select * from dept where dname=111;（数值自动转字符串）

expain select * from dept where dname=qqq; 报错

也就是，如果列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来.

4.2.7 注意事项

索引的代价:

占用磁盘空间。
对增删改操作有影响,因为要维护索引，变慢。

在哪些列上适合添加索引?

不会出现在WHERE子句中字段不该创建索引
较频繁的作为查询条件字段应该创建索引

select * from emp where empno = 1
唯一性太差(离散度)的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex = '男’
更新非常频繁的字段不适合创建索引

select * from emp where logincount = 1

索引失效因素

模糊查询 like “%keyword%” 不会使用到索引,like “keyword%”可以
列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来，否则索引失效
注意：not in 、not exist、!=、< >、like "%_"，以及in(select子句) 会导致索引失效
查询的条件列进行过运算或处理，不会走索引，因为不确定计算后的值是什么

如： where DATE_FORMART(start_time,’%y-%m-%d’) = “21-2-23” 不会走索引
查询null值如： where name is null 不会走索引，可以去null设定为 0 来代替。
or会导致索引失效
如果mysql估计使用全表扫描要比使用索引快,则不使用索引，比如表里面只有一条记录

强制使用索引 select xx,oo from table force index(索引名) where xxx = xxxx;(强制使用主键)

4.2.8 面试题

是不是索引越全越好
性别字段适不适合建索引
模糊匹配like“%abc”,“%abc%”,“abc%”谁能用到索引，谁不能用到，为什么
SQL写成： Select * 有什么问题
InnoDB的索引树和MyIsam的索引树有什么区别

4.3 SQL优化小技巧

java 下执行mysql 批量插入的几种方法及用时_java_脚本之家

SQL：

DDL(数据库定义语言建库，建表，视图，索引) ，
DML(DML 数据库操作语言对表中数据增删改)
DQL(数据库查询语言对表中的记录查询)
DCL数据库控制语言对用户权限的设置，控制事务

4.3.1 DDL优化批量插入

1 、通过禁用索引来提供导入数据性能 。 这个操作主要针对有数据库的表，追加数据
//去除键
alter table test3 DISABLE keys;
//批量插入数据
insert into test3 select * from test;
//恢复键
alter table test3 ENABLE keys;
变多次索引维护为一次索引维护

2、关闭唯一校验
变多次唯一校验为一次唯一校验
set unique_checks=0  关闭
//批量插入数据
insert into test3 select * from test;
set unique_checks=1  开启

3、修改事务提交方式(导入)
变多次事务提交为一次事务提交
set autocommit=0   关闭
//批量插入
set autocommit=1   开启

4.3.2 DML优化批量插入

insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);
//合并多条为一条
insert into test values(1,2),(1,3),(1,4)

变多次事务提交为一次事务提交

4.3.3 DQL优化

1、order by优化

1、多用索引排序
2、普通结果排序（非索引排序）Filesort
索引本身就是排序的，所以多使用索引。  如果某字段要排序,可以创建索引来提高效率

30种sql语句优化：必须掌握的30种SQL语句优化 - 天下尽好 - 博客园

sql语句的执行顺序：from where having group by select order by limit

2、小表驱动大表

作业理解并抄一遍.

1.’对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
若要提高效率，可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num = @num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30.尽量避免大事务操作，提高系统并发能力。

5.优化2-多机优化-分布式系统理论介绍

5.1 什么是分布式系统

一般认为：“一个分布式系统是一些独立的网络计算机集合，但是对这个系统的用户来说，系统就像一台网络计算机一样。”这个定义有两方面的含义：第一，从硬件角度来讲，每台网络计算机都是自主的；第二，从软件角度来讲，用户将整个系统看做是一台网络计算机。这两者都是必需的，缺一不可。

微服务架构 , 数据库集群 , 主从同步-也是分布式集群

5.2 CAP理论-多台服务器

5.2.1 CAP是什么

1、Consistency(一致性):

一致性是指写操作后的读操作可以读取到最新的数据状态,当数据分布在多个节点上,从任意结点读取到的据都是最新的状态。

如何实现一致性?

写入主节点后要将数据同步到从节点。
写入主节点,要将从节点同步期间要将从节点锁定,待同步完成后再释放锁,以免在新数据写入功后,向从从节点查询到旧的数据。

分布式系统一致性的特点:

由于存在节点同步的过程,写操作的响应会有一定的延迟。
为了保证数据一致性会对资源暂时锁定 ,待数据同步完成释放锁定资源。
如果请求数据同步失败的结点则会返回错误信息, -定不会返回旧数据。

2、Availability(可用性)：

可用性是指任何操作都可以得到响应结果,且不会出现响应超时或响应错误。

如何实现可用性?

写入主节点后要将数据同步到从节点。
由于要保证从节点的可用性,不可将从节点中的资源进行锁定。
即时数据还没有同步过来,从节点也要返回要查询的数据,哪怕是旧数据,如果连旧数据也没有则可以约定返回一个默认信息,但不能返回错误或响应超时。

分布式系统可用性的特点:

所有请求都有响应,且不会出现响应超时或响应错误。

Mysql主从就是返回一个老数据

3.、Partition tolerance(分区容忍性) 可靠性

通常分布式系统的各结点部署在不同的子网,这就是网络分区,不可避免的会出现由于网络问题而导致结点之间通信失败,此时仍可对外提供服务,这叫分区容忍性。

如何实现分区容忍性?

尽量使用异步取代同步操作,例如使用异步方式将数据从主节点同步到从数据,这样结点之间能有效的实现松耦合。
添加从节点结点,其中一个从结点挂掉其它从结点提供服务。

分布式分区容忍性的特点:

分区容忍性分是布式系统具备的基本能力。

5.2.2 常见组合

定理：任何分布式系统只可同时满足二点，没法三者兼顾。

忠告：架构师不要将精力浪费在如何设计能满足三者的完美分布式系统，而是应该进行取舍

C: 一致性

A:可用性

P:可靠性

1、满足CA舍弃P，也就是满足一致性和可用性，舍弃容错性。但是这也就意味着你的系统不是分布式的了，因为涉及分布式的想法就是把功能分开，部署到不同的机器上。单个mysql服务器

2、满足CP舍弃A，也就是满足一致性和容错性，舍弃可用性。如果你的系统允许有段时间的访问失效等问题，这个是可以满足的。就好比多个人并发买票，后台网络出现故障，你买的时候系统就崩溃了。 redis.set(key,value) 当我在进行操作的时候其他的要进行等待或者超时异常：如 Redis zk consol nacas

3、满足AP舍弃C，也就是满足可用性和容错性，舍弃一致性。这也就是意味着你的系统在并发访问的时候可能会出现数据不一致的情况。 Mysql主从同步 eureaka

一般来说，现在的分布式系统都要支持p，只能选择c或者a中的一个，如果数据要求立即生效，选择c，否则运行一定的延迟a。

5.3 BASE理论

5.3.1强一致性和最终一致性

CAP理论告诉我们一个分布式系统最多只能同时满足一致性( Consistency)、可用性( Availability )和分区容忍性( Partition tolerance )这三项中的两项,其中AP在实际应用中较多, AP即舍弃一致性,保证可用性和分区容忍性,但是在实际生产中很多场景都要实现最终一致性,比如前边我们举的例子主节点向从节点同步数据,即使不要一致性,但是最终也要将数据同步成功来保证数据一致,这种一致性和CAP中的一致性不同, CAP中的一致性要求在任何时间查询每个结点数据都必须一致,它强调的是强一致性,但是最终一致性是允许可以在一段时间内每个结点的数据不一致,但是经过一段时间每个结点的数据必须一致,它强调的是最终一致性。

1、强一致性

对于关系型数据库，要求更新过的数据能被后续的访问都能看到，这是强一致性。比如小明更新V0到V1，那么小华读取的时候也应该是V1。

2、弱一致性

如果能容忍后续的部分或者全部访问不到，则是弱一致性。比如小明更新VO到V1，可以容忍那么小华读取的时候是V0。

3、最终一致性

如果经过一段时间后要求能访问到更新后的数据，则是最终一致性。比如小明更新VO到V1，可以使得小华在一段时间之后读取的时候是V1。

分布式系统通常为了保证系统的可用性，牺牲强一致性，采用最终一致性。

5.3.2 Base理论

BASE是Basically Available(基本可用)、Soft state(软状态)和Eventually consistent (最终一致性)三个短语的缩写。BASE理论是对CAP中AP的一个扩展,通过牺牲强一致性来获得可用性,当出现故障允许部分不可用但要保证核心功能可用,允许数据在一段时间内是不一致的,但最终达到一致状态。满足BASE理论的事务,我们称之为"柔性事务"。

1、基本可用

分布式系统在出现故障时,允许损失部分可用功能,保证核心功能可用。如,电商网站交易付款出现问题了,商品依然可以正常浏览。

2、软状态

由于不要求强一致性,所以BASE允许系统中存在中间状态(也叫软状态) ,这个状态不影响系统可用性,如订单的"支付中"、“数据同步中”等状态,待数据最终一致后状态改为“成功”状态。

3、最终一致

最终一致是指经过一段时间后,所有节点数据都将会达到一致。如订单的"支付中"状态,最终会变为“支付成功”或者"支付失败" ,使订单状态与实际交易结果达成一致,但需要一定时间的延迟等待。

操作我时其他可用，但是有中间状态，但是要最终一致性。

p一定要满足，c和a二选1，ap用得最多，但是我们还要使用base理论来完成最终一致性

6.优化2-多机优化-集群

分布式: 不同的服务分散到不同节点. 第一种:web,db 第二种:课程服务,用户服务

集群: 同一个服务通过多态服务器来处理. 第一种:db集群 web集群第二种:课程服务集群用户服务集群

6.1 DB集群

6.1.1 为什么要集群

1、提高并发能力

2、提高可用性，防止单点故障

6.1.2 集群方案-主从

集群：多个服务器一起对外提供一个服务。是一种特殊分布式。

MySql集群方案： mysql5.7集群方案对比_mysql 集群对比-CSDN博客；

MySQL高可用集群方案 - 静水楼台/Java部落阁 - 博客园

Mysql集群有N种方式，我们这里选择主从模式，什么是Mysql主从？

一个主(Master)多从(Slave)，主负责写操作，从负责读操作，从库的数据从主库同步复制，这样的集群模式就主从同步。主从复制MySql自己就能完成，我们需要做一些配置即可。

主从同步的优点是减轻读的压力，如果主库的写并发比较高或者为了解决主库单点故障，可用做成多个主库，多个主库相互复制，这样即提高了主库写的并发能力，也解决了单节点故障问题。

做了主从同步，我们的应用就需要做读写分离，写请求访问主库，读请求访问主/从库，可以借助一些框架来实现读写分离，如：msyqlproxy , mycat ,ShardingJdbc ，如下图：

6.2 集群

6.2.1 主从同步原理-mysql自己提供

主从复制是通过重放binlog实现主库数据的异步复制。即当主库执行了一条sql命令，那么在从库同样的执行一遍，从而达到主从复制的效果

主从复制步骤：

将Master的binary-log日志文件打开，mysql会把所有的DDL,DML,TCL写入BinaryLog日志文件中
Master会生成一个 log dump 线程，用来给从库的 i/o线程传binlog
从库的i/o线程去请求主库的binlog，并将得到的binlog日志写到中继日志（relaylog）中
从库的sql线程，会读取relaylog文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致

6.2.1.1 准备环境

至少需要两台服务器,以后肯定是独立两台电脑,当然也可以使用虚拟机. 教学的时候,可以安装多个服务(使用不同的端口)来代替服务器.

1、分别构造主、从数据库并输出日志（方便定位问题）

关闭MySql服务
拷贝mysql安装目录，重命名：master
修改新的Mysql的my.ini配置：端口，日志目录，安装目录，数据目录

拷贝数据：去老的MySql的数据目录拷贝 data\mysql 和 data\performance_schema 到新的MySql的数据目录，这个是MySql的初始化数据。在my.ini中datadir就是数据目录第位置

2、安装及启动：

修改master中的my.ini 路径改为master的路径
执行命令安装新的Mysql的服务，进入bin目录，cmd执行
mysqld --install master --defaults-file="C:\Program Files\MySQL\master\my.ini"

mysqld --install master --defaults-file="D:/Program Files (x86)/MySQL/Master/my.ini"

（install/remove of the service denied 权限不足以管理身份运行cmd.exe）

6.2.1.2 master服务器配置

1.修改master方的my.ini，在[mysqld]下：

log-bin=mysql-bin
server-id=1

innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog_ignore_db=mysql
binlog_checksum=none

2.重启master服务，登录

3.授权savle服务器的使用的账号及权限

Mysql> GRANT REPLICATION SLAVE ON *.* to 'slaveuser'@'127.0.0.1'identified by '123456';

参数说明：

lvtest：slave连接master使用的账号
IDENTIFIED BY 'admin' ：slave连接master使用的密码
192.168.77.128：slave IP

4.查询主数据库状态

Mysql> show master status;

记录 File 和 Position的值，File对应的是binlog文件， position指的是当前slave同步数据的最新行。在slave端会使用这两个信息。

6.2.1.3 slave配置

1、修改slave服务器的配置文件my.ini将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。

log-bin=mysql-bin
server-id=2

2、启动slave服务器，登录

3、在slave端，配置master链接信息 (执行语句)

 Mysql>  change master to
                   master_host='172.16.6.254',   #master IP
                   master_user='slaveuser',          #master数据库通过GRANT授权的账号
                   master_password='123456',         #master数据库通过GRANT授权的密码
                   master_port=3307,              #master数据库的端口
                   master_log_file='mysql-bin.000001', 
                              #master数据库中通过show master status显示的File名称
                   master_log_pos=269         
                               #master数据库的通过show master status显示的Position的值

4、启动同步