• 首页
• 分类首页

MySQL_5.5

• mysql的sql_mode合理设置
• MySQL事务隔离级别

MySQL

• Mariadb Galera Cluster
  • 官方网站: https://mariadb.org/
  • Galera/Mariadb数据库集群配置笔记:http://www.server110.com/mysql/201312/4085.html
  • MySQL Galera Cluster数据库集群配置教程:http://www.server110.com/mysql/201312/4087.html
• 用SchemaSpy生成数据库结构文档
• MySQL自带的性能压力测试工具mysqlslap
• 日志分析工具mysqlsla
  • MySQLslowlog
• MySQL衍生版 Percona Server:Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。
  • http://www.oschina.net/p/percona+server
• MySQL Utilities:
  • http://dev.mysql.com/downloads/utilities/
  • MySQL管理利器MySQL Utilities教程:http://www.ttlsa.com/mysql/mysql-manager-tools-mysql-utilities-tutorial/
• HAProxy + Pacemaker实现MySQL的高可用负载均衡: http://blog.itpub.net/14431099/viewspace-1316655/
• Linux上MySQL优化三板斧:http://www.eit.name/blog/read.php?558
  • CPU方面
    • 在BIOS中关闭电源保护模式，保证MySQL能够充分利用CPU的资源。
  • 内存：
    • vm.swappiness = 0
      • echo "vm.swappiness = 0" >>/etc/sysctl.conf;sysctl -p
      • vm.swappiness设置为0表示尽量少swap，100表示尽量将inactive的内存页交换出去。设置为0让InnoDB自己管理缓存。
      • 观察incative内存：
        • vmstat -an 1的inact列
        • cat /proc/meminfo
    • 关闭numa
      • 在BIOS中关闭
      • 在操作系统中关闭:可以直接在/etc/grub.conf的kernel行最后添加numa=off。另外可以设置vm.zone_reclaim_mode=0尽量回收内存。
      • 启动MySQL的时候，关闭NUMA特性：numactl --interleave=all mysqld &
  • 文件系统：
    • 用noatime，nobarrier挂载系统
      • 对于ext3, ext4和 reiserfs文件系统可以在mount时指定barrier=0；对于xfs可以指定nobarrier选项。
    • IO调度策略修改为deadline。
      • deadline兼顾到一个请求不会在队列中等待太久导致饿死，对数据库这种应用来说更加适用。
      • 将sda的调度策略设置为deadline:echo deadline >/sys/block/sda/queue/scheduler
      • 也可以直接在/etc/grub.conf的kernel行最后添加elevator=deadline来永久生效。

MySQL常用操作

• 修改mysql密码、建库、备份

  UPDATE mysql.user SET Password = PASSWORD('mysqlrootpasswd') WHERE User = 'root';
  FLUSH PRIVILEGES;
  
  CREATE DATABASE `Jwikitestdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  GRANT ALL ON `Jwikitestdb`.* TO 'Jwikitest'@'127.0.0.1' IDENTIFIED BY 'dbpassword';
  
  dbname="Jwikitestdb" && mysqldump -uroot -p --default-character-set=utf8 --opt \
  --extended-insert=false --triggers -R --hex-blob --single-transaction \
  "$dbname" | gzip > /bak/backups/$dbname.bak.`date +'%Y%m%d'`_1.dump.gz
  
  dbnames="ccgw_jboss count_jboss dc dc_big_data dc_jboss dc_log fgw_jboss mgw_jboss portal_jboss vtgw_jboss test";
  date;mysqldump -uroot -p --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob \
   --single-transaction --flush-logs --master-data=2 --databases $dbnames| gzip > DBs.bak.`date +'%Y%m%d'`.dump.gz;date
  
  # 转换IP地址为数值形式
  SELECT INET_ATON('192.168.0.1');
  

• replace、substring_index使用

SUBSTRING_INDEX(str,delim,num)，其返回在str中第num个delim之前或之后的字符串，若num为正则为向前取，为负则为向后取。

select SUBSTRING_INDEX('abcdefg','d',1);
-- 向前取，返回字符串abc
select SUBSTRING_INDEX('abcdefg','d',-1);
-- 向后取，返回efg
select SUBSTRING_INDEX(SUBSTRING_INDEX('abcdefg','d',-1),'g',1);
-- 取中间，返回ef

-- replace替换范例
select id,
REPLACE(paramObj,CONCAT(',"header":{',SUBSTRING_INDEX(SUBSTRING_INDEX(paramObj,'{',-1),'}',2)),'') as opt
from RemoteOperationCmdHistory limit 1;

MySQL复制

MySQL设置参数

MySQL性能分析

• 日志分析工具
• 监测工具
• 运行状态分析
• 分布式数据库代理Amoeba:http://amoeba.meidusa.com http://dbanotes.net/arch/amoeba_interview.html
• explain:https://www.cnblogs.com/yycc/p/7338894.html
• mysql_like

其它

• 计算百分比

  -- set @totalcount:=(select count(1) from DTC);
  select @totalcount:=count(1) from DTC;
  select ecu_name,count(1) as num,concat(round(count(1)/@totalcount*100,2),'%') as percent 
  from DTC group by ecu_name;
  

MySQLslowlog

Other

• 测试字符串索引和整数索引速度脚本：

  --字符串索引和整数索引，从查询效率来看，基本无差别
  --
  
  create table lish_test_index
  (
  int_var  integer,
  str_var  varchar2(36)
  )
  
  drop table lish_test_index;
  
  declare
    int_temp integer;
    str_temp varchar2(36);
  
  begin
  
    for i in 1..10000000 loop
      int_temp := 100000000000000000000000000000 + i;
      str_temp := '' || (100000000000000000000000000000 + i);
      insert into lish_test_index values(int_temp,str_temp);
    end loop;
    commit;
  
  end;
  
  create index IDX_INT_VAR on LISH_TEST_INDEX (INT_VAR);
  create index IDX_STR_VAR on LISH_TEST_INDEX (STR_VAR);
  
  --整数操作
  declare
    int_temp integer;
    str_temp varchar2(36);
    int_con_temp integer;
    int_random integer;
  
  begin
  
    dbms_output.put_line('Int begin:' || CURRENT_TIMESTAMP);
    for i in 1..1000000 loop
  
      int_random := dbms_random.value(1,10000000);
  
      int_con_temp := 100000000000000000000000000000 + int_random;
  
      select str_var,int_var
        into str_temp, int_temp
        from lish_test_index
       where int_var= int_con_temp;
  
    end loop;
    dbms_output.put_line('Int end:' || CURRENT_TIMESTAMP);
  
  end;
  
  --字符串操作
  declare
    int_temp integer;
    str_temp varchar2(36);
    str_con_temp varchar2(36);
    int_random integer;
  
  begin
  
    dbms_output.put_line('Str begin:' || CURRENT_TIMESTAMP);
    for i in 1..1000000 loop
  
      int_random := dbms_random.value(1,10000000);
  
      str_con_temp := '' || (100000000000000000000000000000 + int_random);
  
      select str_var,int_var
        into str_temp, int_temp
        from lish_test_index
       where str_var= str_con_temp;
  
    end loop;
  
    dbms_output.put_line('Str end:' || CURRENT_TIMESTAMP);
  
  end;
  

DELIMITER $$ 
CREATE PROCEDURE cursordemo(INOUT average_goals FLOAT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE matches int DEFAULT(0);
DECLARE goals int DEFAULT(0);
DECLARE half_time_goals INT;

DECLARE team_cursor CURSOR FOR SELECT HTHG FROM epl.football WHERE (FTR = "H");    
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN team_cursor;
  teams_loop: LOOP
    FETCH team_cursor INTO half_time_goals;
    IF done THEN LEAVE teams_loop;
    END IF;
    SET goals = goals + half_time_goals;
    SET matches = matches + 1;
  END LOOP teams_loop;
  SET average_goals = goals / matches;
CLOSE team_cursor;

END

$$ DELIMITER;

• 检查表是否存在

  DELIMITER //
  CREATE PROCEDURE check_table_exists(IN dbName VARCHAR(255), IN tblName VARCHAR(255))
  BEGIN
    DECLARE tableCount INT;
    SELECT COUNT(*) INTO tableCount FROM information_schema.tables WHERE table_schema = dbName AND table_name = tblName;
    IF tableCount > 0 THEN
      SELECT 'Table exists';
    ELSE
      SELECT 'Table does not exist';
    END IF;
  END//
  DELIMITER ;
  

读写分离

• proxysql:https://my.oschina.net/cart/blog/2907232
• https://proxysql.com/compare
• https://github.com/sysown/proxysql/wiki

• 类似产品
  • MySQL Route：是现在MySQL官方Oracle公司发布出来的一个中间件。
  • Atlas：是由奇虎360公发的基于MySQL协议的数据库中间件产品，它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了若干Bug，并增加了很多功能特性。目前该产品在360内部得到了广泛应用。
  • DBProxy：是由美团点评公司技术工程部DBA团队（北京）开发维护的一个基于MySQL协议的数据中间层。它在奇虎360公司开源的Atlas基础上，修改了部分bug，并且添加了很多特性。
  • Cobar：是阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。 在阿里经受住了考验，后面由于作者的走开的原因cobar没有人维护 了，阿里也开发了tddl替代cobar。
  • MyCAT：是社区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存 在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比较高，也会一直维护下去。

mysql时间类型

© 2012 - 2021 XStar  | Powerby:Vimwiki  | Style:丘迟  | 首页  | 分类首页  | 站点地图