sql优化学习 主章

什么影响服务器性能？

sql 查询速度

网卡流量

硬件性能

磁盘IO

QPS过高

 百分之八十是因为慢查询 要对sql语句进行优化

术语说明：
QPS = req/sec = 请求数/秒   每秒钟处理SQL的查询量

【QPS计算PV和机器的方式】

QPS统计方式 [一般使用 http_load 进行统计]
QPS = 总请求数 / ( 进程总数 * 请求时间 )
QPS: 单个进程每秒请求服务器的成功次数

单台服务器每天PV计算
公式1：每天总PV = QPS * 3600 * 6
公式2：每天总PV = QPS * 3600 * 8

服务器计算
服务器数量 = ceil( 每天总PV / 单台服务器每天总PV )

【峰值QPS和机器计算公式】

原理：每天80%的访问集中在20%的时间里，这20%时间叫做峰值时间
公式：( 总PV数 * 80% ) / ( 每天秒数 * 20% ) = 峰值时间每秒请求数(QPS)
机器：峰值时间每秒QPS / 单台机器的QPS = 需要的机器

问：每天300w PV 的在单台机器上，这台机器需要多少QPS？
答：( 3000000 * 0.8 ) / (86400 * 0.2 ) = 139 (QPS)

问：如果一台机器的QPS是58，需要几台机器来支持？
答：139 / 58 = 3

大量的并发超高的CPU使用率

大量并发:数据库连接数被占满（max_connections  连接数默认100   如果超过这个限制 其他未连接上的会出现500错误）

高CPU使用率：cpu资源耗尽导致宕机

 磁盘IO ：使用过快的磁盘设备

网卡流量: 1 网卡IO被占满 （从服务器的数量越多需要靠缓存越多，网卡流量越大）

                 2 进行分级缓存

                 3  避免select *查询  尽量少查用不到的数据 减少流量

  4 分离业务网路和服务器网络 避免主从同步时影响网络性能

大表影响 ：定义：记录行数超过千万行 如果是日志之类的表 只有插入 没有查询和修改可例外         或者10G

                慢查询  很难在一定时间内过滤出所需要的数据  

在大量数据中查询极少量数据 区分度低->大量磁盘IO->降低磁盘效率->大量慢查询

DDL：建立索引需要很长时间  mysql<5.5 建立索引锁表   mysql>=5.5  虽不会锁表 但会引起主从延迟

               修改表结构需要长时间锁表  许多插入阻塞导致数据库连接数被占满 造成长时间主从延迟   mysql>=5.6  会有多线程复制

大表处理：分库分表 （竖切）               大表数据历史归档 （横切） 好处：尽量减少对前后端业务影响                   

大事务影响: 原子性（全成功或全失败） 一致性 持久性（一旦提交 就是永久保存的）

隔离性 （事务对数据库数据修改 在未完成前对于其他事务不可见）1 未提交读 :未提交的数据对于事物是可见的 脏读     2  已提交读  3可重复度 4可串行化:加锁

极少使用 （并发性 由高到低  隔离性 由低到高）

                        大事务:运行时间长 操作数据多   风险:1 锁定太多数据 造成大量阻塞和锁超时 2 回滚时间长 3 执行时间长 造成主从延迟

大事务处理:避免一次处理太多数据 移出不必要在事务中的SELECT操作