MySql优化
https://blog.csdn.net/qq_35361115/article/details/105943893
一个应用吞吐量往往出现在数据库的处理速度上，随着应用程序的使用，数据库数据逐渐增多,数据库处理压力逐渐增大,关系型数据库的数据是存放在磁盘上的，读写速度较慢(与内存中的数据相比).
- 表字段的设计阶段，考量更优的存储和计算
- 数据库自身提供的优化功能，如索引
- 横向扩展，主从复制、读写分离、负载均衡和高可用
- 典型SQL语句优化(收效甚微)
- decimal
- 小数转整数
- ip inet_aton(“ip”)
- ip inet_ntoa(num)
nul数值的计算逻辑比较复杂
- 较长的数字数据可以使用decimal
- char为定长(超过长度的内容将被截掉)， varchar为非定长， text对内容长度的保存额
外保存而varchar对长度的保存占用数据空间
字段原子性(关系型数据库有列的念,默认就符合了)
消除对主键的部分依赖(因为主键可能不止一个)；使用一 个与业务无关的字段作为主键
消除对主键的传递依赖，高内聚， 如商品表可分为商品简略信息表和商品详情表两张表
Innodb支持事务、行级锁定、外键
MyISAM的数据和索引是分开存储的(.MY,.MYD) .而Innodb是存在一 起的(.frm)
可以通过移动表对应的MYI和MYD能够实现表的移动，而Innodb还有额外的关联文件
MyISAM删除数据时会产生碎片空间(占用表文件空间)。要定期通过optimize .table table-name手动优化。而Innodb不会。
Innodb插入数据时按照主键有序来插入，因此表中数据默认按主键有序(耗费写入时间，因为需要在b+ tree中查找插入点，但查找效率高)
- 新闻、博客网站
- 支持事务/外键，保证数据一致性、完整性
- 并发能力强（行锁）
从数据中提取的具有标识性的关键字,并且有到对应数据的映射关系
要求关键字唯一且不为null
符合索引仅按照第一字段有序
要求关键字唯一
show create table student
desc student
创建时指定，如：
first_name add key /unique key / primary key / fulltext key key-name
(first_name,last_name)
更改表结构：
alter table student add key/ unique key /primary key/ fulltext key key_name
(first_name,last_name)
alter table student drop key key_name
如果删除的是主键索引，并且主键索引自增长，则需要alter modify 先取消自增长再删除
分析SQL执行是否用到了索引，用到了什么索引
- where 如果查找字段都建立了索引，则会索引覆盖
- order by 如果排序字段建立了素引，而索引又是有序排列的，直接根据索引拿对应数据即可,读取查询出来的所有数据再排序相比效率很高
- join 如果join on 的条件字段建立了索引，查找会变得高效
即使建立了索引，有些场景也不一定使用
4. where id+1 = ? 建议写成 where id = ?-1 ，既保证索引字段的独立出现
5. like语句不要在关键字前模糊匹配，即使‘%keyword’不会使用索引，而‘keyword%’会使用索引
6. or 关键字两边条件字段都建立索引时才会使用索引，只有一边不是就会做全表扫描
7. 状态值。像性别这样的状态值，一个关键字对应很多数条数据，会认为使用索引比全表扫描效率还低。
- btree 搜索多叉树：结点内关键字有序排列，关键字之间有一个指针，查找效率 log(nodeSize,N)，其中nodeSize 指一个结点内关键字数量（取决于关键字长度和结点大小）
- b+tree 由btree升级而来，数据和关键字存在一块空间，省去了由关键字到数据的映射找数据存放地的时间
- 将select 查询结果缓存起来，key 为SQL语句，value 为查询结果；如果SQL功能一样，但只是多个空格略微改动都会导致key的不匹配
- 客户端开启
query_cache_type
0-不开启
1-开启，默认缓存每条select，针对某个sql不缓存： select sql_no-cache
2-开启，默认都不缓存，通过select sql-cache制定缓存哪一条
- 客户端设置缓存大小：query_cache_size
- 重置缓存：reset query_cache
- 缓存失效： 对数据表的改动会导致基于该数据表的所有缓存失效（表层面管理）
-
默认情况下一张表对应一组存储文件，但当数据量较大时(通常千万条级别)需要将数 据分到多组存储文件，保证单个文件的处理效率
-
partition by分区函数(分区字段)(分区逻辑)
hash-分区字段为整形
key-分区字段为字符串
range-基于比较，只支持 less than
list-基于状态值
- 分区管理
--创建分区
create aoleril arton by kettit prtis 10
--修改表结构
alter table article add partition(分区逻辑)
- 分区字段应该选择常用的检索字段，否则分区意义不大
- 多张结构相同的表存储同一类型数据
- 单独一张表保证id唯一性
- 分割字段到多张表，这些表记录时一一对应关系
- 首先手动将 slave和master同步一下
stop slave
master 导出数据到slave执行一遍
show master status with read lock 记录File和Position
到slave上change master to
- start slave 查看Slave_IO_Running和Slave_SQL_Running，必须为YES
- master可读可写，但slave只能读，否则主从复制会失效需要手动同步
- mysqlreplicate快速配置主从复制
- 使用原生javax.sql.Connection
WriteDatabase 提供写链接
ReadDatabase 提供读链接
- 借助Spring AOP和Aspect实现数据源动态切换
class RoutingDataSourceImpl extends AbstractRoutingDataSource{
// 重写determineDatasource,注入SqlSessionFactory
// 配置defaultTargetDatasource和targetDatasource
// （根据determineDatasource的返回值选择具体数据源 value-ref）
}
DatasourceAspect切面组件，配置切入点@Pointcut aspect()(所有DAO类的所有方法)
配置前置增强@Before（"aspect()"） before(Joinpoint point),通过
point.getSignature.getName获取方法名，与METHOD_TYPE_MAP的前缀集合对比，
将write/read设置到当前线程上(也是接下来将要执行DAO方法的线程，前世增强将其拦截下来)
DatasourceHandler,使用ThreadLocal在前置通知中将方法使用的数据源绑定到执行该方法的线程上，
执行该方法获得数据源时再根据当前线程获取。
- 算法
- 轮询
- 加权轮询
- 依据负载情况
- 为单击服务提供一个冗余机
- 心跳检测
- 虚IP
- 主从复制
为了避免长时间表级锁定
- copy 策略，逐行复制，记录复制时间表SQL日志重新执行
- mysql5.6 online ddl，大大缩短锁定时间
- 先禁用索引和约束，导入之后统一建立
- 避免逐条事务
innodb为了保证一致性， 默认为每条SQL加事务(也是要耗费时间的) . 批量导入前应
手动建立事务，导入完毕后手动提交事务。
避免较大的 offset（较大页码数）
innodb为了保证一致性， 默认为每条SQL加事务(也是要耗费时间的) . 批量导入前应
手动建立事务，导入完毕后手动提交事务。
尽量查询所需字段，减少网络传输延迟(影响不大)
会为每条数据生成一个随机数最后根据随机数进行排序，可以使用应用程序声场随机数代替
如果确定只检索一条数据，建议都加上 limit 1
- 定位查询效率较低的SQL.针对性地做优化
- 配置项
开启 slow_query_log
临界时间 long_query_time
- 慢查询日志会自己记录超过临界时间的SQL，并保存在 datadir 下的 xxx-slow.log中
- 自动记录每条SQL的执行时间和具体某个SQL的详细步骤花费的时间
- 配置项： 开启 profiling
- 查看日志信息
show profiles
- 查看具体SQL的详细步骤花费的时间：
show profiles for query Query_ID
- max_connections: 最大客户端连接数
- table_open_cache: 表文件缓存句柄数，加快表文件的读写
- key_buffer-size: 索引缓存大小
- innodb_buffer_pool_size , innodb的缓冲池大小，实现 innodb各种功能的前提
- innodb_file_per_table，每个表一个ibd文件，否则innodb共享表空间
- 自动生成sql并执行来测试性能
mysqlslap --auto-generate_sql -uroot -poot
- 并发测试
模拟100个客户端执行sql
mysqlslap --auto-generate-sql – concurrency=100 -uroot -proot
- 多轮测试，反应平均情况
模拟100个客户端执行sql，执行3轮
mysqlslap --auto-generate-sql–concurrency=100 --interations=3 -uroot -proot
- 存储引擎测试
–engine=innodb
mysqlslap --auto-generate-sql --concurrency=100 --internations=3 engine – innodb -uroot -proot ,模拟100个客户端执行sql，执行3轮，innodb的处理性能
–engine=myisam
mysqlslap --auto-generate-sql --concurrency=100 --internations=3 engine – innodb -uroot -proot ,模拟100个客户端执行sql，执行3轮，myisam的处理性能