mysql实战总结

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

创新互联建站长期为1000+客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为突泉企业提供专业的成都网站设计、成都网站建设，突泉网站改版等技术服务。拥有10多年丰富建站经验和众多成功案例,为您定制开发。

安装启动和连接数据库，修改数据库的管理密码管理权限
数据库的备份与恢复，全量备份，增量备份，物理备份，逻辑备份
数据库的高可用部署：集群
数据库的优化：数据库操作系统硬件层的优化，数据库软件配置层面的优化，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 db1mysqlimport 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：

本文题目：mysql实战总结
URL分享：http://ybzwz.com/article/igdpcj.html

mysql实战总结

安装数据库：

备份与还原

主从配置

优化

explain查询分析器

其他资讯