一、如何优化

1. 表、字段的设计阶段,考量更优的存储和计算
2. 数据库自身提供的优化功能，如索引
3. 横向扩展，主从复制、读写分离、负载均衡和高可用
4. 典型SQL语句优化(收效甚微)

二、操作方式

字段设计

1. 对精度有要求decimal、小数转整数
2. 尽量使用整数表示字符串（如IP转换成整数）
   • inet_ aton("ip' )
   • inet_ ntoa(num)
3. 尽可能使用not null null数值的计算逻辑比较复杂
4. 定长和非定长的选择
   • 较长的数字数据可以使用
   • decimal char为定长(超过长度的内容将被截掉)， varchar为非定长，text对内容 长度的保存额外保存而varchar对长度的保存占用数据空间
5. 字段数不要过多 部分字段注释是必要的、大多字段命名许哟做到见名思意、可以预留字段以备扩展

范式设计

• 第一范式：段原子性(关系型数据库有列的念，默认就符合了)
• 第二范式：消除对主键的部分依赖(因为主键可能不止一个)；使用一个与业务无关的字段作为主键
• 第三范式：消除对主键的传递依赖；高内聚, 如商品表可分为商品简略信息表和商品详情表两张表

存储引擎（MyISAM和Innodb）

要根据自定的需求选择引擎

1. 功能差异
   • Innodb支持事务、 行级锁定、外健
2. 存储差异
   • 存储方式：MyISAM的数据和索弓 |是分开存储的(.MYI.MYD) ， 而Innodb是存在一起的(.frm)
   • 表可移动性：可以通过移动表对应的MYI和MYD能够实现表的移动，而Innodb还有 额外的关联文件
   • 碎片空间：MyISAM删除数据时会产生碎片空间(占用表文件空间)，需要定期通过optimizetable table-name手动优化。而Innodb不会。
   • 有序存储：Innodb插入数据时按照主键有序来插入。因此表中数据默认按主键有序(耗费写入时间，因为需要在b+ tree中查找插入点，但查找效率高)
3. 选择差异
   • 读多写少用MyISAM：新闻、博客网站
   • 读多写也多用Innodb：支持事务/外键，保证数据-致性、完整性、并发能力强(行锁)

索引

索引类型

• 主键索引primary key：要求关键字唯一且不为null
• 普通索引key：符合索引仅按照第一字段有序
• 唯一索引unique key：要求关键字唯一
• 全文索引fulltext key (5.7.6前不支持中文)

管理语法

• 查看索引

  show create table student
  desc student

• 建立索引

  创建时指定，如first. name varchar(1 6),last name(1 6) , key name(first_ name,last_ name)
  更改表结构：alter table student add key/unique key/primary key/ultext key key. name(first_ name,last_ name)

• 删除索引

  alter table student drop key key_ name
  如果删除的是主键索引，并且主键自增长，则需要alter modify先取消自增长再删除

分析SQL执行是否用到了索引，用到了什么索引，分析使用的场景

• where：如果查找字段都建立了索引，则会索引覆盖
• order by：如果排序字段建立了索引，而索引又是有序排列的，直接根据索引拿对应数据即可，与读取查询出来的所有数据再排序相比效率很高
• join：如果join on的条件字段建立了索引，查找会变得高效
• 索引覆盖：直接对索引做查找，而不去读取数据

语法细节，即使建立了索引，有些场景也不一定使用

• where id+1 = ?建议写成where id = ?-1，即保证索弓|字段的独立出现
• like语句不要在关键字前模糊匹配，即"%keyword不会使用索引，而"keyword% 会使用索引
• or关键两边条件字段都建立索引时才会使用索引，只要有一边不是就会做全表扫描
• 状态值。像性别这样的状态值，-个关键字对应很多条数据，会认为使用索引比全表扫描效率还低

查询缓存

可以配置mysql的配置项得到，一般都在代码中通过文件或者Redis缓存，mysql用的少

分区

默认情况下一张表对应一组存储文件，但当数据量较大时(通常千万条级别)需要将数据分到多组存储文件，保证单个文件的处理效率
分区字段应选择常用的检素字段，否则分区意义不大

partition by分区函数(分区字段)(分区逻辑)

• hash-分区字段为整型
• key-分区字段为字符串
• range-基于比较，只支持less than
• list-基于状态值

管理语句

创建时分区：create table article0 partition by key(title) partitions 10
修改表结构：alter table article add partition(分区逻辑)

水平分割和垂直分割

水平

• 多张结构相同的表存储同一类型数据
• 单独一张表保证id唯一性
  垂直
• 分割字段到多张表，这些表记录是一对应关系

集群

主从复制（读写分离）

• 首先手动将slave和master同步一下

• start slave查看Slave IO Running和Slave SQL _Running,必须都为YES

• master可读可写，但slave只能读，否则主从复制会失效需要重新手动同步

• mysqlreplicate快速配置主从复制

• 使用原stcConecton

• WriteDatabase提供写连接

• ReadDatabase提供读连接

SQL优化

• 批量写入
• limit offset,rows 分页
• select * 尽量查询所需字段,减少网络传输延时(影响不大)
• order by rand（）会为每条数据生成一个随机数最后根据随机数排序，可以使用应用程序生成随机主键代替
• limit 1 如果确定了仅仅检索一条数据，建议都加上limit 1

慢查询日志

定位查询效率较低的SQL,针对性地做优化
配置项

• 开启slow_ query. log
• 临界时间long_ query. time
  慢查询日志会自己记录超过临界时间的SQL，并保存在datadir下的xxx-slow.log中

服务器配置优化

1. max_ connections, 最大客户端连接数

2. table open cache, 表文件缓存句柄数，加快表文件的读写

3. key_ buffer. _size, 索引缓存大小

4. innodb_ buffer. pool size, innodb的缓冲池大小，实现innodb各种功能的前提

5. innodb file per_ table,每个表一个ibd文件， 否则innodb共享 表空间

压测工具MySQLSlap

自动生成sq|并执行来测试性能
myqslap -a-to-generate sql -root -root
并发测试
mysqlslap --auto-generate-sql --concurrency= 100 -uroot -proot,模拟100个客户端执行sql
多轮测试，反应平均情况
mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -uroot -proot,模拟100个客户端执行sql.执行3轮
存储引擎测试
--engine=innodb：mysqlslap --auto-generate-sql --concurrency= 100 --interations=3 -- engine-innodb -uroot -proot,模拟100个客户端执行sql.执行3轮，innodb的处理性能
-- engine= myisam：mysqlslap -- auto-generate-sql --concurrency= 100 --interations=3 --engine-innodb -uroot -proot,模拟100个客户端执行sql.执行3轮，myisam的处理性能