[TOC]

0x00 MySQL优化案例

案例1:优化的起因网站运行缓慢

数据库优化关注点：

• 慢查询语句：show full processlist;
• 慢查询语句设置日志文件：long_query_time = 1 、 log-slow-queries = /data/3306/slow.log

服务器优化实例：

#优化原因：服务器负载较高
$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;

weiyigeek.top-索引

总结：

• 当值有重复的列需要在重复值少的列创建索引以增加查询速度;

案例2：搜索查询通配符导致慢查询增多
如：

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

优化思路：

• 业务上实现登录才能进行查询,保证了自己的核心用户能正常访问,不好就是未注册用户体验不是很好;
• 对访问的业务日志进行流量分析/反爬虫可以采用(awstats),发现异常PV则将IP封禁;
• 配置多个主从进行读写分离,让查询语句走向从库,减轻主库读写压力;
• 在数据库前端加上memcached或者redis这样的缓存服务器;
• 通过搜索服务Sphinx实现搜索,让数据库只做简单饿查询服务
• 自己开发程序,实现每日读库计算索引,保证在服务器上提供的搜索,然后每5分钟从一个从库做一次增量。

0x01 多种优化方案

一个网站最先出现瓶颈的一定是数据库,然后是磁盘IO;
Mysql 数据库优化建议：

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 访问控制/登录权限/开发端口/

0x02 数据库安全权限控制思想

1. 制度与流程控制

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


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


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


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


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

2. 账号权限控制

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

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

3.数据库客户访问控制

• 系统层面控制(系统安全、系统运维权限管控)
• 更改mysql数据库的默认端与phpMyadmin管理端口
• 限制登录数据库的机器或者ip段建议采用堡垒机、VPN到内网机器中进行管理数据库(有记录非常重要)
• 不做公网域名解析,使用本机的host实现访问(限制任何IP直接访问)或者用内部IP访问
• phpMyAdmin站点目录独立于所有其他站点根目录外,只能由指定域名和ip地址访问
• 限制从web连接的账号管理数据库的权限并且,根据开发者角色分配指定账号访问
• 监控数据库账号登录/与web应用部署,监测到异常后进行预警,防止开发人员上传phpMyadmin等数据库管理程序

4.数据库运维管理思想核心

• 未雨绸缪(制定应急预案、应急演练)
• 亡羊补牢,举一反三(当发生安全事件时候,对于业务区域的所有的机器进行排查)
• 完备的架构设计及备份/恢复策略(容灾)
• 定期思考并实战模拟以上策略

---

0x03 Mysql索引优化基本要点

什么是索引？
答：索引就是用来提高数据库性能的重要工具或者手段,简单的说索引就像书籍的目录一样,通过索引可以快速找到需要的书的内容;

索引的原理?
答：索引是建立了针对于数据内容的排序结果的指针,根据指针快速定位所要的数据；(像书的目录一样,直接找到对应位置)

索引的设计原则：

• 索引列一般为where子句中的列或连接字句中的列;
  • select * from user where uid=’10886’;
• 尽量不对于基数小的列做索引（唯一值多的列）：如sex性别列(为什么？)
• 尽量使用短索引：如果对字符列索引尽量指定最小长度
  • create index cityname on city(city(10)); / 对city列的前10个字符 /
• 复合索引的前缀特性,索引的顺便非常重要，在创建符合索引时应将最常用作为限制条件列放在最左边，依次递减;
  • key(a,b,c) … where b=5 will not use index;
  • key(a,b,c)联合索引的组合：key(a),key(a,b),key(a,b,c),而下面的组合无法走索引key(b),key(b,c),key(a,c)
  • 注意事项：避免出现无用的索引(很少或者从未被调用的),INNODB尽量指定主键,最常用较短数据类型唯一列作为主键;尽量使用定长字符char而不使用varchar类型;
• 避免过度使用索引,索引不是越多越好;
  • 1.索引的建立对提高检索能力很有用,但是数据库维护它也很费资源;
  • 2.对性别列索引被称为过度索引;
  • 3.索引会占用磁盘空间，降低更新操作性能,
  • 4.行数比较小的行不建议创建索引;

创建索引的方式

#示例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;

如何查看索引建立后的效果?
答：使用expain语法查看索引利用情况,并且建立索引后线程查找是非常快速的;

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

weiyigeek.top-explain查看

补充：

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

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

MYSQL数据库使用索引的条件：

• MySQL(BTREE)使用索引的比较条件：除了大于小于等于还要BETWEEN,IN,!= 或者 <>, like ‘xx%’;
• 索引的列不包含NULL值如果包含该列将不会使用索引,如果在数据设计时候不要让建立索引的字段默认值为NULL;
• 列类型是字符串要在where条件中把字符串值用括号括起来
• 用or分割开的条件,or前条件又索引,后面的列无索引那么涉及的索引都不会被用到;
• 不要再列上进行运算(否则导致索引失效而全表扫描)
• 不使用NOT IN 和 <> 操作,可以使用NOT EXISTS 与 id > 3 or id < 4 来代替
• 在where和ORDER BY使用相同的索引,并且ORDER BY 的顺序和索引的顺序相同,并且ORDER BY 字段都是升序或者降低的情况才会使用索引;