MySQL高级
1.应用优化
1.1使用连接池
建立一个连接池,使用后归还 连接池JDBC,德鲁伊、ac开头的连接池
1.2减少对mysql访问
redis、elasticsearch ES,ehcach,mamcache,spring cache
减少无效的访问,能一次查询的事情不要分为两次
增加cache层
可以从数据库中抽出部分数据在应用端中以文本方式请求,也可以使用(mybatis)
1.3负载均衡(不是nginx ribbon,是主从复制,读写分离)
可以通过多台服务器来降低单台服务器压力
通过主从复制实现读写分离,使增删改操作走主节点,查询操作走从节点,从而可以降低单台服务器的读写压力(主要服务器接收增删改数据,从服务器接收查(读)数据语句)
2.mysql中查询缓存优化
执行完全相同的查询语句时,服务器会直接从缓存中读取结果,当数据修改时,之前的缓存会失效,修改比较频繁的表不适合使用查询缓存
配置
是否支持查询缓存
show variables like 'have_query_cache'
查看是否开区查询缓存
show variables like 'query_cache_type'
查询大小(建议按1024倍数增)
show variables like 'query_cache_size'
查看查询缓存状态
show status like 'Qcache%';
1.客户端发送一条查询给服务器
2.服务器先会检查查询缓存,如果命中缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;
3.服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划;
开启查询缓存(vi和vim编辑文件)
query_cache_type手动配置
off或0查询缓存关闭
on或1 查询缓存开启
demand或2查询缓存按需进
在usr/my.cnf配置中,增加以下配置
- query_cache_type设为on或1为开启(开启mysql查询缓存)
查询缓存select选项
- sql_cache:查询结果可缓存,并且query_cache_type系统变量值为on或demand
- sql_on_cache:服务器不使用查询缓存。他既不检查查询缓存,也不 在select后面添加sql_on_cache
查询缓存失效的情况
1.sql语句不一致(精确到字母(一个也不可以更改))
2.当查询语句中有一些不确定的,也会失效
3.不使用任何表查询的语句
4.查询mysql,之类的系统数据库时不走查询
5.在存储的函数,触发器或事件的主体内执行的查询
6.如果表更改,使用该表所有缓存变为无效并删除
3.MySQL内存管理及优化
1.内存优化的原则
在给mysql分配更多的内存,但要给系统留下做够内存
2.mysam存储引擎的数据文件读取以来以操作系统自身的IO缓存,因此如果有MySAM表,预留更多内存给系统做IO缓存
3.排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽
2.mysqlsam内存优化
在usr/my.cnf配置中添加以下配置
key_buffer_size=512M
read_buffer_size
3.innoDB内存优化
innoDB用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。
innodb_buffer_pool_size
决定了innodb存储引擎和索引数据的最大缓存区的大小。在足够内存下值越大,缓存命中率越高。
innodb_log_buffer_size
决定了innodb重做日志的大小,对于可能产生大量更新记录的大食物,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交签就执行不必要的日志写入磁盘操作
4.mysql并发参数调整
1.max_connections(调整mysql最大连接数,默认151)
2.back_log(达到一定程度后,之后的十个都会存放在堆栈中,超过后停止)
3.table_open_cache(打开表的缓存,具体多大根据back_log来判断)
4.thread_cache_size()
5.innodb_lock_wait_timeont(超时连接)
(具体设置等待时间,tmysql的并发能力)
5.常用SQL技巧
sql语句执行顺序
编写顺序
select from json where group by having order by limit
执行顺序
from on json where group by having select distinct order by limit
正则表达式使用
正则表达式的like: regexp
‘S$’s结尾
'[匹配括号内任意字符]'
mysql常用函数
数字函数
字符串函数
日期函数
聚合函数