mysql优化
表的分区
- 数据量过大的时候,这个时候需要将一张表的数据划分几张存储,这样查找就不用查找其他剩余的分区了.
- mysql是否支持分区.
- show variables like '%partition%'是否是yes
- range分区
- List分区
- hash分区
- keys分区
- show create table emply \G 可以查看当前对于emply进行的分区
- alter table empy drop partition pNorth; 删除分区也会导致数据被删掉.
- alter table empy add partition(partition pNorth values in(3,4,6,8,)) 添加分区
- range分区
create table emolt(
id int not null,
store_id int not null
)
partition by range(store_id)(
partition p0 values LESS THEN(6),
partition p1 values LESS THEN(11),
partition p2 values LESS THEN(13)
partition p3 values LESS THEN MAXVALUE
);
- list分区
地区 商店id号
北区 3,5,6,7,17
东区 1,23,10,20
西区 2,3,4,26,39
离散的值分区用list创建
partition by list( store_id)(
PARTITION PNOR VALUES IN(3,5,7,19),
PARTITION PEWA VALUES IN(1,2,3,56,19,5),
PARTRION PAD VALUES IN(7,89,13)
)
- 如果插入的值不在分区里,会报错
- hash分区
partition by HASH(store_id)
partitions 4;
算出hash值,分4个区,不写的话放到一个区里
mysql优化
- myisam优化
- optimize TABLE bbs_info\G
- my.ini下key_buffer_size myisam索引的缓存大小
- innodb内存优化
- innodb_buffer_pool_size 存储缓存大小
- innodb_log_buffer_size事务日志缓存大小
- 调整mysql并发相关参数
- max_connection=100 同时响应100个请求
- 调整thread_cache_size加快连接数据库的速度.控制缓存客户服务线程的数量
- innodb_lock_wait_timeout控制事务等待行锁的时间
- 应用程序优化
- mysql把连接对象放在连接池里.
- sql优化
- query_cache_type是否打开缓存 show variables like "query_cache_type";
- 负载均衡(读写分离)
- 主服务器,增删改操作
- 从服务器1,从服务器2,从服务器3, 查询操作
权限管理
-
grant select on *.* to zy@localhost indentified by '123456' with grant option;
- 查询权限show grants for 'zy'@'localhost'\G
- 删除账户drop user 'zy'@'localhost';
- 修改密码set password for 'ty'@'localhost'=password('123');
mysql监控
- 自己写程序脚本控制(数据库比较小)
- mysqladmin -uroot -p123456 -h127.0.0.1 ping
- mysqladmin -uroot -p123456 -h127.0.0.1 status
- mysqladmin -uroot -p123456 -h127.0.0.1 processlist连接数
- mysql -uroot -p123456 -BNe "select host,count(host) from processlist group by host;" information_schema统计连接的客户端
-
mysqlcheck -uroot -p123456 --all-databases;检测分析优化
-
show status like 'innodb_log%' innodb_log空间不足造成的等待次数
-
show status like "%lock%"表锁行锁的等待次数
- show status like 'created_tmp%' 是否有临时表过大而从内存中换出到磁盘上
- 商业解决方案
- 监控开源软件
mysql定时维护
||5.1版本开始支持定时器.
- show variables like '%event_scheduler%' event_scheduler 定时器开关.
- 开启定时器 set global event_scheduler=1
- 关闭定时器 set global event_scheduler=0
drop event if exists test_event;
create event test_event
on schedule every 1 second 每隔一秒钟执行
on completion preserve disable 当停掉定时器,他还是存在的
do call p_event_insert(); 调用存储过程
alter event test_event on completion preserve enable; 开启定时器