3-MYSQL优化学习基础

#优化原因：服务器负载较高
$uptime  #top(查看负载性能) 能看见mysqld占用资源比较多

#登录数据库查看当前慢语句
> show processlist #如果看到大量线程等待，就需要对其优化;
> show full processlist;
> show variables like '%profiling%' # 设置当前配置项为ON ，SQL语句将会在打开 - 连接 - 解析 - 返回结果的时间进行展示；

#带优化语句 （等号走索引效率最高）
select id from userinfo where addr='china' and dataline='2018-12-17' and count=1024;

#查看利用索引情况：
explain select id from userinfo where addr='china' and dataline='2018-12-17' and count=1024;

#不给缓存查询 （发现没有走索引）
select SQL_NO_CACHE id from userinfo where addr='china' and dataline='2018-12-17' and count=1024;

#查看userinfo表结构
show create userinfo\G;  #只有一个id主键有索引;

#查看条件字段的唯一性：（重点：唯一值或者在重复值少的列创建索引）
select count(distinct addr) from userinfo; #150 个不重复
select count(distinct dataline) from userinfo; #450 个不重复
select count(distinct count) from userinfo; #410 个不重复

#解决方法(与研发经理商量进行改进)
use user;
#在三个列上都创建索引
create index-adc on userinfo(dataline,addr(20),count(20)); #创建索引语句
#在生产环境中如果访问频繁的大表，创建索引花费的时间较多，最好在流量低谷的时候建立索引;
#再次查看利用索引情况 (一条语句执行 12次查到)
explain select id from userinfo where addr='china' and dataline='2018-12-17' and count=1024;

#这样的语句特别多，导致数据库负载很高，但是对于这样的SQL查询语句MySQL数据库没有太大的优化余地(需要采用其他来支撑);
LIKE '%阿里%'  #注意两个% 通配符,是不走索引的;但是单个通配符还是可能走索引的;

1) 硬件优化
  a. CPU选型：64位、8~16核
  b. 内存大小：32~128GB
  c. 磁盘大小：性能与分区格式
      性能：ssd(高并发) > sas(普通业务) > SATA(线下)
      Raid4块盘：RAID0 > RAID10 > RAID5 > RAID1
  d. 网卡 多块网卡bound,以及buffer,tcp优化


2) 软件优化
  a. OS: x86_64 位系统
  b. Mysql 编译优化

补充：x86的操作系统由于单进程内存最大4G,所以不管您加多大的内存空间它只使用那么多;


3) 配置文件优化
  注意：my.cnf参数优化幅度较少,大部分以架构和SQL语句优化。
    #建议不要开启访问的日志，占用系统资源较大
    innodb_buffer_pool_size=2048MB  #一般内存3/4分之一
    sort_buffer_size=2M   #排序缓存，一个线程占用一个，不能太大
 监控：show global status\G;
 工具：mysqlreport 性能调优工具


4) SQL语句优化
  a. 索引优化
    1.白名单机制-项目开发DBA参与减少业务上线后慢SQL数量
      long_query_time=2   #慢查询最大2s
      log-slow-queries=/data/3306/slow-log.log  #慢查询记录到认证文件
    2.慢查询日志分析工具:mysqldumpslow,mysqlsla(推荐),myprofi,mysql-explain-slow-log,mysqllogfilter;
    3.每天0点定时分析慢查询
  b.大的复杂的SQL语句拆分成多个小的SQL语句;
  c.数据库是存储数据的地方,但不是技术数据的地方;
    对于数据计算应用类处理,都有拿到前端应用解决，禁止在数据库上处理;
  d.搜索功能like '%老男孩%',一般不要用MySQL数据库使用搜索应用sphinx;


5) 架构优化
  1. 负载均衡,业务拆分,微服务,读写分离,主从同步;
  2. 数据库前端必须加cache例如：memecached,用户登录,商品查询
  3. 业务应用使用nosql持久化存储,例如memcached,redis
  4. 集群的读写分离(dbproxy)
  5. 单表超过2000万,拆库拆表(人工分类：业务来区分);


6) 流程制度安全优化(参考下面)
  1. 开发人员流程开发保证程序的健壮性,稳定性,安全性,运维人员保证环境服务的优化
  2. 任何一次人为的数据库记录更新都要走一个流程
    a. 人的流程：开发->核心开发->运维或者DBA
    b. 测试流程：内网测试(安全、性能)->IDC测试->线上执行
    c. 客户端管理:PHPMyAdmin 访问控制/登录权限/开发端口/

#1.1 项目开发制度流程
- 开发人员开发环境
- 办公测试环境
- IDC测试环境
- IDC正式环境
通过这种较完善的项目开发制度及流程控制,尽可能的防止潜在的问题隐患发生;


#1.2 数据库更新流程
- 开发人员提交需求
- 开发主管审核
- 部门领导审核
- DBA(运维)审核
- DBA(运维)执行项目开发制度及流程控制的数据库更新步骤(每个步骤都要测试),最后IDC正式环境执行;
通过完善的数据库更新流程控制,可以防止很多潜在的数据丢失、破坏等问题发生;


#1.3 DBA参与项目数据库设计
- 开发环节上DBA或者运维人员最好参与数据库设计与审核工作;
- 从源头上降低不良的数据库设计(权限控制)与不良SQL语句发生(慢查询),减少发生问题的机率;
- 需要评估工作量是否允许,一般互联网公司实施全审核比较困难


#1.4 操作流程申请
- 开发等人员权限申请流程( 账户/库权限 )
- 数据库更新执行流程
- 烂SQL语句计入KPI考核


#1.5 定期对内部人员培训
- 定期给开发及相关人员培训 (从源头上降低不良数据设计及不良SQL语句的发生)
- 通过培训让大家知晓大家数据库性能的重要性,提升开发时照顾数据库性能的意识;
- 数据库设计规范及制度,SQL语句执行优化、性能优化技巧等
- 数据库架构设计等内容

#2.1 内部人员权限分配
- 权限申请流程要设置规范/合理/让需求不明确者知难而退
- 办公开发与测试环境可以开放权限,但是在IDC测试与正式环境要严格控制数据库读写权限
- 开发人员正式数据库权限分配规则,给不对外的从服务器的只读权限,不能分配线上正式主库写权限;
- 上级领导的账号密码一定要严格,以及提醒操作注意事项
- 特殊账号(all privliges)有DBA或者高级运行人员管理

#2.2 web业务账号权限分配
- 写账号默认权限为select/insert/update/delete,不要给建表改表(create,alter)等的权限,更不能all权限
- 读库账号默认权限为select(配合mysql参数read-only使用),确保从库对所有非super权限是只读的
- 设立专库专账号，非root权限
- 站库一定要分离(一定、一定),且连接数据库按照IP或者网段来授权
- 安全和业务有时是有一定的矛盾的,需要达到一个较好的平衡状态

#示例1.
create index index_uid on student(uid);

#示例2.
alter table student add index index_sage(sage);

#示例3.
create table `Suser` (
  `Sname` varchar(16) NOT NULL COMMENT '学生姓名',
  KEY `ind_sname` (Sname),
  KEY `ind_mutil` (Sname,Saddr(4)), #创建的复合索引及Saddr前4个字符
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#查看索引
show index from student;
show create table cdb_threads\G;
#删除索引
DROP INDEX index_name on student;

explain select id from test where addr="CQ" and dateline='20190503';
#查看KEY列进行查看是不是走了索引;

#问题1:查看不是suthorid列内容不同的行,越大建立索引效果越好
select count(distinct authorld) from cdb_threads;  #查看列唯一值的格式

#问题2：SQL优化后测试,不使用缓存测试：
select SQL_NO_CACHE * from uc_memeber where email='le1345';