review

MySQL核心技术与最佳实践

最新推荐文章于 2024-07-12 14:53:38 发布

原创最新推荐文章于 2024-07-12 14:53:38 发布 · 668 阅读

0 ·

CC 4.0 BY-SA版权

note 专栏收录该内容

2 篇文章

订阅专栏

&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
                               Mysql 一些常见的问题
#@主从复制@ 的原理以及实现的过程 及如何处理 @延时@ 问题
#  1.搭建与主数据库完全一样的数据库环境，称为从数据库。
#  2.主数据库一般是实时的业务从数据库
#  3.从数据库一般用于数据的备份，当主数据库发生故障时切换到从数据库继续工作
#  4.可在从数据库作备份、数据统计等工作，这样不影响主数据库的性能
#
#流程： 主数据库数据发生更新-->触发一个mysql 的服务线程 -->产生一个二进制日志-->
#   触发IO线程[打开一个连接，]-->产生一个中继日志-->触发sql线程-->更新从库
#延时解决方案：[1]mysql_proly中间件  [2]提高服务配置  [3]修改mysql.ini binlog_checksum=none
#
#三大线程：IO线程，sql 线程，服务线程
#
#数据如何同步：日志@@@@@@@@ ，sql 语句，两者之间
#
#请求方式： 插件 4.url hash 5.ip hash [12位]
#
#
#
#

#  @读写分离@的搭建
# 首先要有两台数据库服务，master数据库主要用于数据的更新操作[update ,delete,insert ]，slave 数据库主要用于[select]
# 代码实现截取sql前6个字符 if($sql_sub=='select'){slave}else{master}

#mysql 动静分离
#
#
#
#

#Mysql 的缓存，存储过程，触发器，事务的实现和原理
#Mysql 缓存
#1. 服务器接收SQL,以SQL和一些其他条件为key查找缓存表(额外性能消耗)
#2. 如果找到了缓存,则直接返回缓存(性能提升)
#3. 如果没有找到缓存,则执行SQL查询,包括原来的SQL解析,优化等.
#4. 执行完SQL查询结果以后,将SQL查询结果存入缓存表(额外性能消耗)
#
#Mysql的储存过程
# 储存过程是一个可编程的函数，它可以有sql 语句和一些特殊的字符串组成，其实就像
# 我们opp中封装的方法，允许控制数据访问
# 优势：1.操作一个函数可执行大量的Transaction-SQL代码，批量处理执行速度很快
#       2.比较灵活，可以完成复杂的逻判断，运算处理
#       3系统管理员可以执行某一储存过程的权限进行限制，实现数据的访问权限控制，避免非授权的限制    
#
<!-- http://www.cnblogs.com/ivictor/p/5045378.html
DELIMIT $   #声明结束字符
CREATE PROCEDURE pro_test2(OUT user_id INT) #创建储存过程的名字
BEGIN
select * from f_user_1 limit 100;#sql 语句+流程控制
end $
#执行储存过程
call pro_test2(1);
#查看
show create procedure pro_test1 
-- 删除存储过程
DROP PROCEDURE pro_test;

IN：   表示输入参数，可以携带数据带存储过程中
OUT： 表示输出参数，可以从存储过程中返回结果
INOUT： 表示输入输出参数，既可以输入功能，也可以输出功能
-->

#
#Mysql的触发器
#
#在MySQL Server里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），
#从而自动执行的一段程序。从这种意义上讲触发器是一个特殊的存储过程
<!-- 

    DELIMITER $$

    USE `test`$$
    --判断数据库中是否存在tr_a_update触发器
    DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_update`$$
    --不存在tr_a_update触发器，开始创建触发器
    --Trigger触发条件为update成功后进行触发
    CREATE
        TRIGGER `tr_a_update` AFTER UPDATE ON `t_a` 
        FOR EACH ROW BEGIN 
        --Trigger触发后，当t_a表groupid,username数据有更改时，对t_b表同步一条更新后的数据
          IF new.groupid != old.groupid OR old.username != new.username THEN
            UPDATE `t_b` SET groupid=NEW.groupid,username=NEW.username WHEREusername=OLD.username AND groupid=OLD.groupid;
          END IF;

        END;
    $$

    DELIMITER ;
 -->

#Mysql的事务处理
#事务 ACID
#原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
#一致性: 确保数据库正确地改变状态后，成功提交的事务。
#隔离性: 使事务操作彼此独立的和透明的。
#持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在
#MYSQL的事务处理主要有两种方法
#1.用begin,rollback,commit来实现
    begin开始一个事务
    rollback事务回滚
    commit 事务确认
#2.直接用set来改变mysql的自动提交模式
    mysql默认是自动提交的，也就是你提交一个query，就直接执行！可以通过
    set autocommit = 0 禁止自动提交
    set autocommit = 1 开启自动提交

#Mysql的优化
#1.避免子查询[select * from test1 where id in (select id from test2 where xx=1)]
#2.有顺序的读取 3.避免重复的读写  4.设置自增id  5.避免使用select *  6. 合理的设置字段的类型
#7.数据读写分离  8.分区、分表  9.建立合适的索引  10.避免使用耗费资源的操作 null 
#11.不要使用rand() 12. 尽量不用or 查询 13.查询一条数据的使用加上limit 1 14.开启查询缓存
#15.前期对数据进行合理的评估，进行分区、分表  16.避免使用%前缀的模糊查询  17. 避免使用mysql 自带函数
#18.开启慢日志 19.选择合适的数据库引擎 20.尽量不要在sql 中进行一些逻辑操作 21. 小心“永久链接” mysql_pconnect

#Mysaim 和 Innodb 的区别
#  innodb 支持事务 mysaim 不支持
#  mysaim 支持全文索引 innodb 不支持
#  mysaim 在支持查询操作效率较高，innodb 执行写入操作效率较高
#  mysaim 支持表锁 innodb 支持行锁
#
#  存储结构：
#   myisam --> 储存成三个文件。第一个文件以表的名字开始,扩展名指出文件的类型。
#  .frm 文件存储表定义 。数据文件扩展名为.MYD[MYData]。索引文件扩展MYI[MYINDEX]
#   innodb -->所有的表都保存在同一数据文件，　innodb 的受操作系统的限制，一般为2GB
#  存储空间：
#   myisam -->可被压缩，储存空间较小。三种储存格式：静态表，动态表，压缩表。
#   innodb -->需要更多的内存和储存，它会在内存中建立高速的缓冲索引。
#  事务的支持：
#   mysiam --> 强调的是性能，每次查询具有原子性，执行速度比innodb 类型更快，但不提供事务支持
#   Innodb --> 提供事务支持，外部建等高级数据功能。
#  CURD操作：
#   mysaim --> 如果执行select 操作，MyISAM 时更好的选择，在增删的时候需要锁定整张表，效率会低一些。
#   Innodb --> 支持行级锁，删除插入数据的时候只需要锁定该行就行，效率较高。
#  外键：
#   MyISAM -->不支持
#   Innodb -->支持

#Sql 语句的执行速度
#mysql> set profiling=1;
#mysql> show profiles;

# mysql 的分表、分区操作
#横向分表[可以自增id去模，订单生成的时间] [用union 和union all(去重) 联查][select username from  table1 
#union select username from table2]
#纵向分表 [用户表，用户信息表，地区表]
#分区：
#分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，
#每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上

#分区：分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，
#而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。
#http://www.cnblogs.com/shengdimaya/p/5384884.html
#

#Mysql 索引的建立 / 索引的优化

#mysql> create index name_primary_index on test(name(255));  #创建索引

#mysql> alter table test add FULLTEXT index `title_fulltext_index` (`title`); #通过创建全文索引修改表的结构

#mysql> drop index addtime_unique_index on test;             #删除索引

#mysql> show index from test\G;                              #查看表中的索引

#索引方法hast 和 btree 的区别
# 1.hast 索引能快速定位数据，在数据精确查找方面hast 索引的效率高于btree
# 2.对于like 查询，hast 方法无效，hast 算法基于等值运算
# 3.hast 不支持索引排序，
#
#
#



#索引的优化：
#
#
#
#
#
#

#mysql 的数据备份的方式 
# @@全量备份     @@ 增量备份  
#1.sql 语句备份
#H:\phpStudy2\MySQL\bin>mysqldump -uroot -p ddhong > /phpstudy2/www/ddhong.sql
#
#全量备份：msyqldown /   mysql < asql / data 目录  /保存biglog
#增量备份：重启服务器后产生的binlog日志，将biglog 进行备份

#mysql 字段选择的类型及参数说明
#
#

#Mysql 的行锁和表锁
#http://www.phpddt.com/db/mysql-locking.html
#mysql 的锁机制主要是针对不懂储存引擎支持不同锁机制。
#例如：MYSAIM 和MEMORY 支持表锁，BDB 储存引擎支持页面锁，INNODB支持表锁也支持行锁，默认情况下采用行级索
#锁的特性归纳：
#·表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
#·行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
#·页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
#
#@@表锁分为：【读锁 | 写锁】
#
#共享读锁：当我们对表进行读操作，不会堵塞其他进程对表进行读操作，但会堵塞表的写请求，只有当读锁释放后，才执行其他写操作
#表独占写锁：堵塞其他进程的读写操作
#
#病发操作
#锁调度
#
#@@行锁
#共享锁：允许事务去读一行，阻止事务取得相同的数据                      LOCK IN SHARE MODE  #共享锁
#排他锁：允许事务去更新数据，阻止其他事务取得相同的数据或更新其他数据  FOR UPDATE  #排他锁

#mysql> show status like 'innodb_row_lock%';   查看锁的使用期情况


#非关系型数据库和关系型数据库的区别
#关系型数据库：Oracle,Sql server ,mysql 
#非关系型：Nosql cloudant
#nosql 和关系型数据库的比较：
#优点：1.容易部署，基本属于开源软件，不需花费大量成本
#      2.查询速度快,储存于缓存中。关系型数据库储存在硬盘中
#      3.以key /value 的形式储存，文档形式，图片形式    
#缺点：1.新型的数据库，维护工具和资料有限
#       2.不支持事务的处理
#

#Mysql 的日志有哪些
#
#错误日志
#慢日志
#查询日志
#二进制日志
#查询日志

/***************Mysql 常用命令********************/
#mysql 缓存的命令
#mysql> show full processlist;                              查看mysql 的连接数

#mysql> select @@query_cache_type;                          查看查询缓存的状态

#mysql> set session query_cache_type=on;                    打开查询缓存

#mysql> set session query_cache_type=off;                   关闭查询缓存

#mysql> show variables like 'have_query_cache';             查询缓存是否可用 

#mysql> select @@global.query_cache_size;                   查询缓存大小  

#mysql> show status like 'Qcache';                          查询缓存状态

#mysql> show status like 'Com_select';                      查询缓存的使用情况




#mysql表操作
#mysql> show create table test;                             查看表的结构 

#mysql> show engines\G;                                     查看但前系统支持的引擎

#mysql> alter table test engine=MYISAM;                      修改表的引擎



#mysql> alter table test1 rename test11;                     修改表的名称
#H:\phpStudy2\MySQL\bin>mysql -h localhost -P 3306           链接mysql 终端

 PHP 的操作

#
#cookie 和 session 的工作原理
#
#setcookie(name,value,expire,path,domain,secure)
#setcookie('key','value',time,服务器路径，作用域，规定的https 安全协议);
#
#session:客户端发送请求-->服务器端开启session-->浏览器的消息头set-cookie就存入PHPSESSID
#-->当用户发送请求时将PHPSESSID 带到请求头中-->通过请求头就session_id 带到服务端
#
#两者的区别:
#cookie 存在客户端，session 存在服务器端
#cookie不是很安全，别人可以分析本地的cookie 进行cookie 进行cookie欺骗
#session 存在服务器端，当访问量增多会给服务器带来压力
#单个cookie 保存的数据不超过4k ,很多浏览器最多可以保存20cookie 
#建议:将登陆信息重要的存在session中
#     其他信息存在cookie  

#session 共享
#1、db[会造成数据库压力]、
#2、memcache/redis[一般选择|抗病发能力强|读写数度快]
#3、cookie[关闭浏览器就没了
#4、nfs【】）文件共享/文件同步rsync [每台服务上都生成session文件 浪费资源]
#5、ip哈希 [ 例：学校用ip公网 所有人都用一个ip | vpn也用一个ip 早服务器压力 ]
#
#如何实现：
#1.配置php.ini
#session.save_handler =redis;
#session.save_path="tcp://";
#2.在php 脚本
#ini_set("session.save_handler","redis");
#ini_set("session.save_path","tcp://");
#3.修改.htaccess(超文本入库)
#php_value.session_save_handler='redis';
#php_value session.save_path  "tcp://IP:11211"
#http://www.cnblogs.com/wangxusummer/p/6382151.html

#系统面对 高并发 问题怎么解决【处理方案】
#HTML 静态化
#资源分离[img,mp4]
#数据库集群 ，读写分离
#数据缓存【memcache | reids】
#负载均衡

#单点登录的实现原理
#
#用户请求网站[没登录]--->访问sso 站点[判断有没有token,没有token]-->返回登录也
#--->输入用户信息--->sso 站点验证用户信息--->验证通过后将用户信息生成token 存入cookie 
#
#

#第三方登录原理
#优势：不需要单独的注册账号，提高用户的体验，有利于推广网站
#流程：用户点击登录[根据appid生成临时的code[我们的网站作为第三方的应用，告诉第三方的请求和发和认可]]
#--->引导用户到第三方登录的界面[引导用户授权]--->用户授权后返回code--->再次请求第三方服务器[带上appid,appkey]
#--->验证通过后返回access_token-->网站通过access_token获取有户的唯一标识open_id ;
#

#
#关于电商的购物车流程，及下订单和支付流程【商家入驻模块】
#
#
#
#
#
#
#

#Api 接口与数据量有关吗？Api 接口实现的步骤
#整个过程:
#1.写接口文档//说明，url,请求方式，地址，返回值，请求实例，更新日志
#2.定义数据类型
#3.开始封装类：【1防刷新 2认证 3防改 4签名[接口加密]】
#4.接口测试：postman , phpstrom ,swagger
#5.接口调用:file_get_contents ,curl,snoopy,jsonp,fopen

#
#Memcache 和redis 的应用及区别 [redis做一个队列]
#memcahce add,set,get ,delete,flush,replace
#
#memcache 是将数据存储在内存中           
#memcache 主要是以简单的key-value 的形式储存
#memcache 挂机后数据就没了
#memcache 使用多核而redis是单核
#memcache 通讯协议比较简单、使用高效的内存管理、互不通讯的服务器集群
# redis是将数据存入磁盘
# redis 不仅仅支持简单的key-value类型的数据 还可以存储set list hast 等结构的数据结构
# redis 可以持久缓存，数据备份 即master-slave模式的数据备份。
# redis 在储存比较小的数据时比memcache 性能更高，100k 以上memcache性能高于redis
# redis 操作：open,connect,set,add,sadd,setnx,iset,delete,remove,exists,incr,decr,lpush,rpush,lpop,rpop,
#             lsize,Iget,lGetRange[获取某个区间的值]，lReMove,smove[将某个值移动到因一个键上]
#应用场景
#数据交互比较频繁 
#高读写比（热新闻）
#秒杀
#不经常变化
#
#提高缓存的命中率：提前做好缓存预热，增加存储容量，调整缓存颗粒，更新缓存
#

#如何实现网站全静态化
#优势：
#一、减轻服务器负担，浏览网页无需调用系统数据库。
#二、有利于搜索引擎优化SEO，Baidu、Google都会优先收录静态页面，不仅被收录的快还收录的全；
#三、加快页面打开速度，静态页面无需连接数据库打开速度较动态页面有明显提高；
#四、网站更安全，HTML页面不会受php程序相关漏洞的影响；观看一下大一点的网站基本全是静态页面，
#     而且可以减少攻击，防sql注入。数据库出错时，不影响网站正常访问。
#五、数据库出错时，不影响网站的正常访问。
#最主要是可以增加访问速度,减轻服务器负担,当数据量有几万，几十万或是更多的时候你知道哪个更快了
#1.file_get_content();
#2.fopen();
#3.ob_start(); ob_get_contents();
#
#

#非对称加密和对称加密
#对称加密： 加密速度快、最简单的一种加密方式。加密和解密用的是同一把钥匙
#           但是秘钥不好管理和分配，很有可能在传输过程中被拦截，一般采用将对称加密的秘钥进行非对称加密
#非对称加密：相对而言比较安全
#           它使用了一对公钥和私钥，私钥由一方安全保管，公钥可以分发给任何请求的人
#           
#
#
#
#

#php 的代码优化
#1.设置一些静态成员属性，提高数据的
#2.使用循环时，最好设置可控的长度
#3.经历使用单引号
#4.尽量避免一些自调用的魔术方法，__Get,__Set
#5.在使用的include 和 require 的时候，尽量使用绝对路径
#6.使用多个判断时，用switch case
#7.使用数据连接尽量不要用长连接
#8.尽量使用静态页面，apache 解析php 脚本比html慢2-10 倍
#9.尽量使用缓存，php的内置函数
#10.声明一个变量之后，不用就销毁掉

#Tcp 和udp还有ip的区别 
#ip协议：传输层协议，主要解决数据如何在网络中传播
#tcp和udp 使用传输控制协议从一个网络传送数据包到另个个网络
#可以ip想象成一种高速公路，那么tcp和udp就可以看成高速高速公路的卡车
#HTTP:应用层协议，主要解决如何包装数据
#
#三次握手
#
#在TCP/IP协议中，TCP协议提供可靠的连接服务，采用三次握手建立一个连接。 
#第一次握手：建立连接时，客户端发送syn包(syn=j)到服务器，并进入SYN_SEND状态，等待服务器确认； 
#第二次握手：服务器收到syn包，必须确认客户的SYN（ack=j+1），同时自己也发送一个SYN包
#（syn=k），即SYN+ACK包，此时服务器进入SYN_RECV状态； 
#第三次握手：客户端收到服务器的SYN＋ACK包，向服务器发送确认包ACK(ack=k+1)，此包发送完毕，客户端和服务器进入ESTABLISHED状态，完成三次握手。 完成三次握手，客户端与服务器开始传送数据.
#客户端和服务端简历链接--》等待服务端确认--》服务端确认用户,同事发送一个包---》客户端收到服务端的包后发送确认包


#Sql注入，csrf 攻击，xss 攻击怎么解决，web 常见的攻击和解决方法
#sql:1.过滤特殊字符串，
#    2.pdo 的预处理
#csrf:1.验证码
#     2.token 验证
#
#xss:1.强制过滤字符串
#

#浏览器消息头，响应头参数详情
#消息头：客户端浏览器的信息
#响应头：服务器返回客户端的信息