mysql实战总结

应用层面的操作数据库主要需要了解的内容有:

1. 安装启动和连接数据库，修改数据库的管理密码管理权限
2. 数据库的备份与恢复，全量备份，增量备份，物理备份，逻辑备份
3. 数据库的高可用部署：集群
4. 数据库的优化：数据库操作系统硬件层的优化，数据库软件配置层面的优化，sql语句层面的优化，数据索引层面的优化。

-

安装数据库：

可以下载官网已经编译好的安装包，环境centos
yum 安装：mysql-community
先下载npm包
安装npm包： rpm -Uvh mysql57-community-release-el7-9.noarch.rpm
安装mysql：yum install mysql-community-server
启动mysql：systemctl start mysqld
初始安装后，修改root用户密码
启动服务后，root用户会创建一个临时密码存放在/var/log/mysqld.log里面，可以通过
grep 'temporary password' /var/log/mysqld.log 进行查看
然后使用 mysql -uroot -p进行登录
登录时如果临时密码有特殊符号的，可以用单引号括起来'myPassword'
然后
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
刷新权限：flush privileges;
-

授权某个用户访问数据库
grant all privileges on 数据库.表 to '用户名'@'ip' identified by '密码'
grant all privileges on . to 'root'@'%' identified by '密码'//%百分号表示任意地址

-

备份与还原

使用mysqldump备份：
使用sql格式备份：mysqldump --all-databases > my.sql
mysqldump --databases db1 db2 > my.sql
通过sql文件重新加载数据：mysql < my.sql 或者mysql>source my.sql
使用分隔符形式备份数据：
mysqldump --tab=/tmp db1会使用两种格式保存两个文件，一种txt一种sql
参数：--fields-terminated-by=str，--fields-enclosed-by=char，--fields-optionally-enclosed-by=char，--fields-escaped-by=char，--lines-terminated-by=str
恢复分隔符形式的备份文件：
mysql db1<t1.sql
mysqlimport db1 t1.txt
或者
mysql>use db1;
mysql> load data infile 't1.txt' into table t1;

如何复制一个数据库
mysqldump db1 > dump.sql
mysql db2 < dump.sql
不要再mysqldump中使用--database，这会导致use db1的语句存在dump文件中，从而覆盖db2的数据库名。
如何复制一个服务器的数据库到另外一个服务器：
mysqldump --databases db1 > dump.sql
在服务器二：mysql>dump.sql
使用--databases是因为会包含create database语句
如何备份存储过程（存储过程，函数，触发器，事件）
加上参数 --events --routines（存储过程和函数） --triggers
如何存储定义和数据分隔：
--no-data不会复制表格数据，--no-create-info：
升级兼容性测试：
mysqldump --all-databases --no-data --routines --events > dump-defs.sql
在升级的服务器中mysql < dump-defs.sql

使用二进制日志进行时间点恢复（增量恢复）：
查看二进制日志列表：show binary logs
查看二进制日志的名字：show master status
恢复二进制日志：mysqlbinlog mylog | mysql-uroot -p
查看二进制日志信息：mysqlbinlog mylog | more
如果要处理多个二进制日志，每个文件单独一条语句时一种危险的做法，应该在一条语句中执行
mysqlbinlog log1 log2 log3 | mysql -uroot -p
或者使用追加的方式：
mysqlbinlog log1 >my.sql
mysqlbinlog log2 >>my.sql
然后使用：mysql -uroot -p -e "source my.sql"
可以使用--skip-gtids忽略全局事务
恢复时间点：
使用--start-datetime和--stop-datetime来指定恢复的时间：
mysqlbinlog --stop-datetime="2017-04-20 9:59:59" mylog | mysql -uroot -p
mysqlbinlog --start-datetime="2017-04-20 9:59:59" mylog | mysql -uroot -p
使用--start-position和--stop-position来指定恢复的位置：
mysqlbinlog --stop-position=154 mylog | mysql -uroot -p
mysqlbinlog --start-position=209 mylog | mysql -uroot -p

主从配置

原理就是使用二进制日志，一主两从，主服务器在保存数据之前会写入一个binlog记录，里面包含了数据操作的记录，这个记录会被同步到从服务器，然后从服务器会启动sql线程将这些记录同步进数据库
操作过程：
1：分别配置主从服务器的my.cnf文件，添加server-id=1,server-id=2,server-id=3....
主节点上添加log-bin=mylog，开启二进制文件
2：主节点上添加复制用的用户
grant replication slave on . to 'repl'@'%' identified by 'myPassword';
查看主节点状态：show master status;此时会显示日志文件名和position
3：给各个服务器打开3306端口：
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
4：在各个从节点上输入
change master to
master_host='主节点ip', //逗号要有
master_user='repl',
master_password='myPassword',
master_log_file='mylog.000001', //这里的文件名是从主节点上查看到的文件名
master_log_pos=154; //这里的位置是从主节点上查看到的
成功后使用start slave;进行启动
然后查看从节点信息：show slave status\G，如果
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都为是则说明正常，如果是否则可能有不对的配置，查看端口，log_pos位置等。
主从复制的具体配置my.cnf
server-id=1
log-bin=/var/log/mysql/mysql-bin //二进制日志的存放位置
binlog-do-db=test //需要同步的库
binlog-ignore-db=mysql //不记录日志的库，即不需要同步的库
log-slave-updates //开启从节点上的日志功能
sync_binlog=1 //经过1次日志写操作把日志文件写入硬盘一次，最安全但效率最低，默认是0
#auto_increment //控制自增列的行为

优化

单表优化：
字段：
1：尽量使用tinyint，smallint，medium_int作为整数类型而非INT，非负的加上UNSIGNED
2：varchar长度只分配需要的空间
3：使用枚举或整数代替字符串类型
4：使用timestamp，不使用datetime
5：单表字段建议在20以内
6：用整型来存ip
索引：
1：考虑在where和order by的列上建立索引
2：值分布很稀少的字段不适合建索引
3：字符字段不要做主键
4：不用外键，由程序保证约束
5：不用unique，由程序保证约束
优化sql语句的执行效率的方法：
1：尽量选择较小的列
2：将where中用的比较频繁的字段建立索引
3：select子句中避免使用 *
4：避免在索引列上使用计算，not， in 和<>操作
5：当只需要一行数据的时候使用limit 1
6：保证表单数据不超过200W，适时分隔表

不做列运算，任何对列的操作都导致表扫描，查询时把操作移到等号右边。sql语句尽可能简单，一条sql只能在一个cpu上运行，大语句拆小语句。or改成in，or的效率是n级别，in的效率是log(n)的级别，in的个数建议在200内

-
行子查询：可以使用多个列作为判断条件
select * from t1 where (col1,col2) in (select col3,col4 from t2)

exist：如果子查询有返回，则返回数据，如果子查询没有内容则没有返回

优化子查询：
1：使用可以影响子查询行数或顺序的子查询语句，就是让子查询语句过滤多点数据
2：使用子查询来替换join连接
3：把外部语句移入内部子查询
SELECT FROM t1
WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
替换：
SELECT FROM t1
WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
又如：
SELECT (SELECT column1 + 5 FROM t1) FROM t2;
替换
SELECT (SELECT column1 FROM t1) + 5 FROM t2;
4：使用一个行查询来替代相关子查询：
SELECT FROM t1
WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
替换：
SELECT FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
AND t2.column2=t1.column2);
5：使用not (a = any()) 比 a <> all()好
6：使用x = any (table containing (1,2)) 比x=1 or x =2好
7：使用 = any比exists好
8：如果记录只返回一行，那么使用=比in好

explain查询分析器

explain的内容：
id select_type table type possible_keys key key_len ref rows Extra

id：执行编号，如果没有嵌套或者子查询，只有唯一的select，那么每行都显示1
select_type：显示本行查询类型：
simple：简单子查询，不包含子查询和union
primary：包含union或子查询
subquery：子查询
derived：派生表
union：
union result：匿名临时表
dependent union：
subquery：
dependent subquery：
table：对应行赈灾访问的表名或别名
type：访问类型：
all：全表扫描
index：扫描表的时候按索引此埙扫描，而不是逐行
range：范围扫描，key列会显示使用了哪个索引。使用=，<>，>，》=,《,<=， BETWEEN或I时可以使用range
ref：索引访问，返回所有所有匹配的单个值的行，只有使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。
eq_ref：使用唯一索引或主键查询时才出现
const：确定最多只有一行匹配的时候，mysql优化器会在查询前读取且只读取一次
system：const连接的一种特列，表仅有一行满足条件
Null：在执行节点甚至用不到访问表或索引（高效）
possible_keys：显示使用了哪些索引
key：实际决定使用的键（索引），如果没有选择索引则为Null
key_len：使用的键长度，长度越短越好
ref：贤惠使用哪个列或常熟与key一起从表中选择行
rows：执行查询必须检查的行数，这是一个预估值
extra：重要补充信息：
using filesort：使用索引排序
using temporary：用临时表保存中间结果
not exists：一旦匹配了left join就不再搜索
using index：使用覆盖索引，不需要读取数据文件，如果同时出现了using where，表明
using index conditon：
using where：
using join buffer：
impossible where：
select tables optimized away：
distinct：