下面讲讲关于MySQL如何实现数据备份与恢复，文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完MySQL如何实现数据备份与恢复这篇文章你一定会有所受益。常见的MySQL管工具mysql  命令行   跨平台  MySQL官方bundle包自带MySQL-Workbench  图形  跨平台  MySQL官方提供MySQL-Front  图形  Windows  开源，轻量级客户端软件phpMyAdmin  浏览器  跨平台  开源，需LAMP平台Navicat  图形  Windows  专业、功能强大、商业版PhpMyAdmin部署思路：1.安装httpd、mysql、php-mysql及相关包2.启动httpd服务程序3.解压phpMyAdmin包，部署到网站目录4.配置config.inc.php,指定MySQL主机地址5.创建授权用户6.浏览器访问、登录使用yum  -y  install  httpd  php  php-mysqltar  -zxf  phpMyAdmin-2.11.11-all-languages.tar.gz  -C /var/www/html/cd /var/www/html/mv  phpMyAdmin-2.11.11-all-languages/  phpmyadminchown  -R  apache:apache  phpmyadmin/cp  phpmyadmin/config.sample.inc.php  phpmysdmin/config.inc.phpvim  /var/www/html/phpmyadmin/config.inc.php$cfg['Servers'][$i]['host'] = 'localhost';$cfg['blowfish_secret'] = '123456';mysql>create database  bbsdb;mysql>grant all on bbsdb.* to admin@'localhost' identified by '654321';systemclt start httpdfirefox http://localhost/phpmyadminMySQL数据备份与恢复数据备份方式：1.物理备份，冷备份：cp 、 tar 、 ....例：物理备份与恢复备份操作格式:cp  -rp  /var/lib/mysql/数据库  备份目录/文件名tar -zcvf xxx.tar.gz  /var/lib/mysql/数据库/*恢复操作格式:cp -rp  备份目录/文件名  /var/lib/mysql/（默认存储路径）tar -zxvf  xxx.tar.gz  -C  /var/lib/mysql/数据库名/2.逻辑备份，mysqldump 、mysql原理：执行备份时，根据已有的库和表生成对应SQL命令，把生成的SQL命令存储到指定的备份文件里。备份策略：完全备份：备份所有数据（一台云服务器，一个库，一张表）增量备份：备份自上一次备份（包含完全备份、差异备份、增量备份）之后有变化的数据。差异备份：备份自上一次完全备份之后有变化的数据。例：逻辑备份及恢复完全备份操作格式：mysqldump -u用户名 -p密码  源库名  >  路径/xxx.sql完全备份的恢复操作格式：mysql  -u用户名  -p密码  目标库名    <  路径/xxx.sql库名的表示方式：--all-databases      所有库库名                 指定的单个库库名.表名            指定库的指定表-B 库名1  库名2 ..   备份多个库注意事项：无论备份还是恢复，都要验证用户及权限。例：将所有的库备份为mysql-all.sql文件mysqldump  -u root  -p 123456  --all-databases > alldb.sql将userdb库备份为userdb.sql文件mysqldump  -u root  -p 123456  userdb  > userdb.sql将备份文件userdb.sql恢复到userdb3库mysql>create  databases  userdb3;mysql -u roo -p 123456 userdb3 < userdb.sql实时增量备份binlog日志类型：二进制日志，用途：记录所有更改数据的操作，配置：log_bin[=dir/name]server_id=数字max_binlog_size=数字m启用binlog日志采用binlog日志的好处1.记录除查询之外的所有SQL命令。2.用于数据恢复。3.配置mysql主从同步的必要条件。例：vim  /etc/my.cnf[mysqld]....log_bin  //启用binlog日志server_id=100   //指定id值,id号不能重复（1-255）范围。systemctl restart mysqldbinlog相关文件默认日志文件名：主机名-bin.index   //记录已有的binlog日志文件名主机名-bin.000001  //第1个二进制日志（达到500M存去下一条纪录）主机名-bin.000002  //第2份二进制日志....手动生成新的日志文件1.重启mysql服务2.执行SQL操作 mysql > flush logs;（flush logs，切换到下一个binlog日志文件）3.mysqldump  --flush-logs4.mysql -uroot -p密码 -e  'flush  logs'清理binlog日志删除早于指定版本的binlog日志：purge  master  logs  to  'binlog文件名';删除所有binlog日志，重建新日志：reset  master;例：mysql>purge  master  logs to 'mysql-bin.000003';mysql>reset master;分析binlog日志查看日志当前记录格式：mysql>show variables like 'binlog_format';修改日志记录格式：vim  /etc/my.cnf[mysqld]....binlog_format="mixed"  设置日志文件的记录格式systemctl restart  mysqld三种记录格式：1.statement: 每一条修改数据的sql命令都会记录在binlog日志中。2.row: 不记录sql语句上下文相关信息，仅保存哪条记录被修改。3.mixed: 是以上两种格式的混合使用。binlog日志文件记录sql命令的方式：1.时间点2.pos点（偏移量）查看有哪些bilog日志：mysql>show master logs;使用mysqlbinlog工具格式：mysqlbinlog  [选项]  binlog日志文件名常用选项：1.时间点：--start-datetime="yyyy-mm-dd  hh:mm:ss"--stop-datetime="yyyy-mm-dd hh:mm:ss"2.pos点：--start-position=数字--stop-position=数字例： 查看从2017年1月2日15:30开始的更改操作 mysqlbinlob  --start-datetime="2017-01-01 15:30"  /var/lib/mysql-bin.000001 .... at 318解释：server id 1        ： 数据库主机的服务号；end_log_pos 796    ： sql结束时的pos节点thread_id=11       ： 线程号binlog恢复数据方式一：基本思路：1.使用mysqlbinlog提取历史SQL操作2.通过管道交给mysql命令执行例： 恢复第1份binlog日志的部分信息 mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001 mysqlbinlog  --start-position=296  --stop-position=1073  /var/lib/mysql-bin.000001  |mysql -uroot  -p123456方式二：命令格式：mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];参数解释：IN 'log_name'    ：指定要查询的binlog文件名(不指定就是第一个binlog文件)FROM pos         ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)LIMIT [offset,]  ：偏移量(不指定就是0)row_count        ：查询总条数(不指定就是所有行)例：mysql>show binlog events in 'mysql-bin.000002'\G;MySQL备份工具物理备份缺点：1.跨平台性差2.备份时间长、冗余备份、浪费存储空间mysqldump备份缺点：1.效率较低，备份和还原速度慢。2.备份过程中，数据插入和更新操作会被挂起。XtraBackup备份工具：1.备份过程中不锁库表，适合生产环境。2.由专业组织Percona提供（改进MySQL分支）。主要含两个组件：1.xtrabackup：C程序，支持InnoDB/XtraDB2.innobackupex：以Perl脚本封装xtrabackup,还支持MyISAM支持事务和事务回滚，要求存储引擎为innodb事务日志文件：ibdataLSN  日志序列号ib_logfile0   //SQL命令ib_loggile1....安装XtraBackupyum  -y  install  perl-Digest-MD5.x86_64  rsync perl-DBD-MySQLrpm  -ivh  libev-4.15-1.el6.rf.x86_64.rpmrpm  -ivh  percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpmrpm  -ql   percona-xtrabackup-24/usr/bin/innobackupex  //备份innodb、xtrdb、myisam引擎的表/usr/bin/xbcloud..../usr/bin/xtrabackup  //备份innodb、xtrdb引擎的表....innobackupex基本选项 --host           //主机名   --user           //用户名 --port           //端口号 --password       //密码 --databases      //数据库名（单个库：databases="库名"，多个库：databases="库1 库2"，单个表：databases="库.表"） --no-timestamp   //不用日期命名备份文件存储的子目录名 --redo-only      //日志回滚合并（最后一次增量备份日志回滚不需要此选项） --apply-log      //准备还原（回滚日志） --copy-back      //恢复数据 --incremental 目录名  //增量备份 --incremental-basedir=目录名   //增量备份时，指定上一次备份数据存储的目录名 --incremental-dir=目录名        //准备恢复数据时，指定增量备份数据存储的目录名 --export         //导出表信息 import           //导出表空间XtraBackup完全备份格式：innobackupex  -user 用户名  --password  密码  --databases="系统库列表和存储数据库"  备份目录名  --no-timestamp例：将所有库完全备份到 /backup innobackupex  --user root  --password 123456 /backup  --no-timestamp  XtraBackup完全恢复完全恢复时要求空的库目录rm -rf /var/lib/mysqlmkdir  /var/lib/mysqlchown  -R  mysql:mysql  /var/lib/mysql格式：innobackupex  -user  用户名  --password  密码  --databases="系统库列表和存储数据库"   --copy-back  备份目录名例:恢复所有数据 innobackupex  --apply-log  /backup innobackupex   --copy-back  /backup XtraBackup增量备份必须先有一次完全备份格式：innobackupex  --user 用户名  --password 密码  databases="系统库列表和存储数据库"  --incremental  目录名 --incremental-basedir="完全备份目录名"  -no-timestamp例：完全备份到/allbak、第一次增量备份到/new1、第二次增量备份到/new2 cp  -rp  /var/lib/mysql/mysql  /root/mysql.bak  //备份授权库 innobackupex  --user root  --password 123456 --databases="gamedb"  /fullbak  --no-timestamp  //完全备份 innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new1  --incremental-basedir="/fullbak" --no-timestamp //第一次增量备份 innobackupex  --user root  --password 123456 --databases="gamedb"  --incremental /new2  --incremental-basedir="/new1"  --no-timestamp  //第二次增量备份XtraBackup增量恢复rm -rf /var/lib/mysqlmkdir  /var/lib/mysqlchown  -R  mysql:mysql  /var/lib/mysql格式：1.innobackupex  --user  用户名  --password  密码  --databases="系统库列表和存储数据库"  --apply-log  --redo-only 完全备份目录名  2.innobackupex  --user  用户名  --password  密码  --databases="系统库列表和存储数据库"  --apply-log  --redo-only 完全备份目录名  --incremental-dir=增量备份目录名3.innobackupex  --user  用户名  --psssword  密码  --databases="系统库列表和存储数据库"  --copy-back  完全备份目录名例：恢复第一次增量备份到/new1、第二次增量备份到/new2的数据 rm -rf /var/lib/mysql mkdir  /var/lib/mysql innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  //恢复完全备份 innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --redo-only  /fullbak  --incremental-dir="/new1"  //恢复增量 innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  /fullbak  --incremental-dir="new2"  //恢复增量 innobackupex  --user root  --password 123456  --databases="gamedb"  --copy-back  /fullbak  //拷贝文件 cp -r /root/mysql.bak  /var/lib/mysql/mysql chown  -R  mysql:mysql  /var/lib/mysql恢复完全备份文件中的单个表格式：innobackupex  innobackupex  --user  用户名  --password  密码  --databases="系统库列表和存储数据库"  --apply-log  --export 完全备份目录名例：完全备份数据库到/allbak目录 innobackupex  --user root  --password 123456  --databases="gamedb"  /allbak --no-timestamp //完全备份 mysql>drop  table gamedb.a; innobackupex  --user root  --password 123456  --databases="gamedb"  --apply-log  --export  /allbak  //导出表信息 mysql>create  table gamedb.a(id int);  //创建表 mysql>alter   table gamedb.a  discard tablespace;  //删除表空间 cp  /allbak/gamedb/a.{ibd,cfg,exp}  /var/lib/mysql/gamedb  //拷贝表信息文件 chown  mysql:mysql  /var/lib/mysql/gamedb/a.*   //修改所有者mysql>alter  table  gamedb.a  import  tablespace;  //导入表空间

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。下文主要给大家带来备份MYSQL的简单操作方法，希望这些内容能够带给大家实际用处，这也是我编辑备份MYSQL的简单操作方法这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。备份类型及特点冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；热备（hot backup）：备份的同时，业务不受影响。根据要备份的数据集合的范围完全备份：full backup，备份全部字符集。增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。差异备份：differential backup 上次完全备份以来改变了的数据。建议的恢复策略：完全+增量+二进制日志完全+差异+二进制日志数据库做完全备份：mysqldump -uroot -p --single-transaction  --master-data=2  --databases zy >D:\mysql\priv\20171206-1.sql--single-transaction: 基于此选项能实现热备InnoDB表；因此，不需要同时使用--lock-all-tables；--master-data=2  记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的--databases hellodb 指定备份的数据库从备份中查看二进制信息：   4.操作数据库变动信息，再次查看数据客的日志信息    show master status       5.测试增量备份      6.查看某个日志点的操作记录      7.热备在导入数据时需要关闭二进制日志   set sql_log_bin=0    flush logs    --刷新日志    8.导入之前备份的二进制文件对于以上关于备份MYSQL的简单操作方法，大家是不是觉得非常有帮助。原文链接：https://www.yisu.com/zixun/32596.html

limit m,n分页语句 select * from dept order by deptno desc limit 3,3; select * from dept order by deptno desc limit m,n; limit 3,3的意思扫描满足条件的3+3行，撇去前面的3行，返回最后的3行，那么问题来了，如果是limit 200000,200，需要扫描200200行，如果在一个高并发的应用里，每次查询需要扫描超过20W行，效率十分低下。 建立主键或唯一索引, 利用索引(假设每页10条)  SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M。  select * from dept where deptno >10 order by deptno asc limit n;//下一页    select * from dept where deptno <60 order by deptno desc limit n//上一页 适应场景: 适用于数据量多的情况(元组数上万)。 这种方式不管翻多少页只需要扫描n条数据。  基于索引再排序 方法2 虽然扫描的数据量少了，但是在某些需要跳转到多少也得时候就无法实现，这时还是需要用到方法1，既然不能避免，那么我们可以考虑尽量减小m的值，因此我们可以给这条语句加上一个条件限制。是的每次扫描不用从第一条开始。这样就能尽量减少扫描的数据量。  例如：每页10条数据，当前是第10页，当前条目ID的最大值是109，最小值是100. 那么跳到第9页： select * from dept where deptno<100 order by desc limit 0,10; 那么跳到第8页： select * from dept where deptno<100 order by desc limit 10,10; 那么跳到第11页： select * from dept where deptno>109 order by asc limit 0,10; 那么跳到第11页： select * from dept where deptno>109 order by asc limit 10,10; ———————————————— 原文链接：https://blog.csdn.net/qq_16570607/article/details/118410492 

一、创建拼音对照表-- 创建汉字拼音对照临时表CREATE TABLE IF NOT EXISTS `t_base_pinyin` ( `pin_yin_` varchar(255) CHARACTER SET gbk NOT NULL, `code_` int(11) NOT NULL, PRIMARY KEY (`code_`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;二、插入对照数据-- 插入数据INSERT INTO t_base_pinyin (pin_yin_,code_) VALUES ("a", 20319),("ai", 20317),("an", 20304),("ang", 20295),("ao", 20292),("ba", 20283),("bai", 20265),("ban", 20257),("bang", 20242),("bao", 20230),("bei", 20051),("ben", 20036),("beng", 20032),("bi", 20026),("bian", 20002),("biao", 19990),("bie", 19986),("bin", 19982),("bing", 19976),("bo", 19805),("bu", 19784),("ca", 19775),("cai", 19774),("can", 19763),("cang", 19756),("cao", 19751),("ce", 19746),("ceng", 19741),("cha", 19739),("chai", 19728),("chan", 19725),("chang", 19715),("chao", 19540),("che", 19531),("chen", 19525),("cheng", 19515),("chi", 19500),("chong", 19484),("chou", 19479),("chu", 19467),("chuai", 19289),("chuan", 19288),("chuang", 19281),("chui", 19275),("chun", 19270),("chuo", 19263),("ci", 19261),("cong", 19249),("cou", 19243),("cu", 19242),("cuan", 19238),("cui", 19235),("cun", 19227),("cuo", 19224),("da", 19218),("dai", 19212),("dan", 19038),("dang", 19023),("dao", 19018),("de", 19006),("deng", 19003),("di", 18996),("dian", 18977),("diao", 18961),("die", 18952),("ding", 18783),("diu", 18774),("dong", 18773),("dou", 18763),("du", 18756),("duan", 18741),("dui", 18735),("dun", 18731),("duo", 18722),("e", 18710),("en", 18697),("er", 18696),("fa", 18526),("fan", 18518),("fang", 18501),("fei", 18490),("fen", 18478),("feng", 18463),("fo", 18448),("fou", 18447),("fu", 18446),("ga", 18239),("gai", 18237),("gan", 18231),("gang", 18220),("gao", 18211),("ge", 18201),("gei", 18184),("gen", 18183),("geng", 18181),("gong", 18012),("gou", 17997),("gu", 17988),("gua", 17970),("guai", 17964),("guan", 17961),("guang", 17950),("gui", 17947),("gun", 17931),("guo", 17928),("ha", 17922),("hai", 17759),("han", 17752),("hang", 17733),("hao", 17730),("he", 17721),("hei", 17703),("hen", 17701),("heng", 17697),("hong", 17692),("hou", 17683),("hu", 17676),("hua", 17496),("huai", 17487),("huan", 17482),("huang", 17468),("hui", 17454),("hun", 17433),("huo", 17427),("ji", 17417),("jia", 17202),("jian", 17185),("jiang", 16983),("jiao", 16970),("jie", 16942),("jin", 16915),("jing", 16733),("jiong", 16708),("jiu", 16706),("ju", 16689),("juan", 16664),("jue", 16657),("jun", 16647),("ka", 16474),("kai", 16470),("kan", 16465),("kang", 16459),("kao", 16452),("ke", 16448),("ken", 16433),("keng", 16429),("kong", 16427),("kou", 16423),("ku", 16419),("kua", 16412),("kuai", 16407),("kuan", 16403),("kuang", 16401),("kui", 16393),("kun", 16220),("kuo", 16216),("la", 16212),("lai", 16205),("lan", 16202),("lang", 16187),("lao", 16180),("le", 16171),("lei", 16169),("leng", 16158),("li", 16155),("lia", 15959),("lian", 15958),("liang", 15944),("liao", 15933),("lie", 15920),("lin", 15915),("ling", 15903),("liu", 15889),("long", 15878),("lou", 15707),("lu", 15701),("lv", 15681),("luan", 15667),("lue", 15661),("lun", 15659),("luo", 15652),("ma", 15640),("mai", 15631),("man", 15625),("mang", 15454),("mao", 15448),("me", 15436),("mei", 15435),("men", 15419),("meng", 15416),("mi", 15408),("mian", 15394),("miao", 15385),("mie", 15377),("min", 15375),("ming", 15369),("miu", 15363),("mo", 15362),("mou", 15183),("mu", 15180),("na", 15165),("nai", 15158),("nan", 15153),("nang", 15150),("nao", 15149),("ne", 15144),("nei", 15143),("nen", 15141),("neng", 15140),("ni", 15139),("nian", 15128),("niang", 15121),("niao", 15119),("nie", 15117),("nin", 15110),("ning", 15109),("niu", 14941),("nong", 14937),("nu", 14933),("nv", 14930),("nuan", 14929),("nue", 14928),("nuo", 14926),("o", 14922),("ou", 14921),("pa", 14914),("pai", 14908),("pan", 14902),("pang", 14894),("pao", 14889),("pei", 14882),("pen", 14873),("peng", 14871),("pi", 14857),("pian", 14678),("piao", 14674),("pie", 14670),("pin", 14668),("ping", 14663),("po", 14654),("pu", 14645),("qi", 14630),("qia", 14594),("qian", 14429),("qiang", 14407),("qiao", 14399),("qie", 14384),("qin", 14379),("qing", 14368),("qiong", 14355),("qiu", 14353),("qu", 14345),("quan", 14170),("que", 14159),("qun", 14151),("ran", 14149),("rang", 14145),("rao", 14140),("re", 14137),("ren", 14135),("reng", 14125),("ri", 14123),("rong", 14122),("rou", 14112),("ru", 14109),("ruan", 14099),("rui", 14097),("run", 14094),("ruo", 14092),("sa", 14090),("sai", 14087),("san", 14083),("sang", 13917),("sao", 13914),("se", 13910),("sen", 13907),("seng", 13906),("sha", 13905),("shai", 13896),("shan", 13894),("shang", 13878),("shao", 13870),("she", 13859),("shen", 13847),("sheng", 13831),("shi", 13658),("shou", 13611),("shu", 13601),("shua", 13406),("shuai", 13404),("shuan", 13400),("shuang", 13398),("shui", 13395),("shun", 13391),("shuo", 13387),("si", 13383),("song", 13367),("sou", 13359),("su", 13356),("suan", 13343),("sui", 13340),("sun", 13329),("suo", 13326),("ta", 13318),("tai", 13147),("tan", 13138),("tang", 13120),("tao", 13107),("te", 13096),("teng", 13095),("ti", 13091),("tian", 13076),("tiao", 13068),("tie", 13063),("ting", 13060),("tong", 12888),("tou", 12875),("tu", 12871),("tuan", 12860) ,("tui", 12858),("tun", 12852),("tuo", 12849),("wa", 12838),("wai", 12831),("wan", 12829),("wang", 12812),("wei", 12802),("wen", 12607),("weng", 12597),("wo", 12594),("wu", 12585),("xi", 12556),("xia", 12359),("xian", 12346),("xiang", 12320),("xiao", 12300),("xie", 12120),("xin", 12099),("xing", 12089),("xiong", 12074),("xiu", 12067),("xu", 12058),("xuan", 12039),("xue", 11867),("xun", 11861),("ya", 11847),("yan", 11831),("yang", 11798),("yao", 11781),("ye", 11604),("yi", 11589),("yin", 11536),("ying", 11358),("yo", 11340),("yong", 11339),("you", 11324),("yu", 11303),("yuan", 11097),("yue", 11077),("yun", 11067),("za", 11055),("zai", 11052),("zan", 11045),("zang", 11041),("zao", 11038),("ze", 11024),("zei", 11020),("zen", 11019),("zeng", 11018),("zha", 11014),("zhai", 10838),("zhan", 10832),("zhang", 10815),("zhao", 10800),("zhe", 10790),("zhen", 10780),("zheng", 10764),("zhi", 10587),("zhong", 10544),("zhou", 10533),("zhu", 10519),("zhua", 10331),("zhuai", 10329),("zhuan", 10328),("zhuang", 10322),("zhui", 10315),("zhun", 10309),("zhuo", 10307),("zi", 10296),("zong", 10281),("zou", 10274),("zu", 10270),("zuan", 10262),("zui", 10260),("zun", 10256),("zuo", 10254);三、创建汉字转换拼音函数-- 建立汉字转换拼音函数DROP FUNCTION IF EXISTS to_pinyin;DELIMITER $CREATE FUNCTION to_pinyin(NAME VARCHAR(255) CHARSET gbk)RETURNS VARCHAR(255) CHARSET gbkBEGIN DECLARE mycode INT; DECLARE tmp_lcode VARCHAR(2) CHARSET gbk; DECLARE lcode INT; DECLARE tmp_rcode VARCHAR(2) CHARSET gbk; DECLARE rcode INT; DECLARE mypy VARCHAR(255) CHARSET gbk DEFAULT ''; DECLARE lp INT; SET mycode = 0; SET lp = 1; SET NAME = HEX(NAME); WHILE lp < LENGTH(NAME) DO SET tmp_lcode = SUBSTRING(NAME, lp, 2); SET lcode = CAST(ASCII(UNHEX(tmp_lcode)) AS UNSIGNED); SET tmp_rcode = SUBSTRING(NAME, lp + 2, 2); SET rcode = CAST(ASCII(UNHEX(tmp_rcode)) AS UNSIGNED); IF lcode > 128 THEN SET mycode =65536 - lcode * 256 - rcode ; SELECT CONCAT(mypy,pin_yin_) INTO mypy FROM t_base_pinyin WHERE CODE_ >= ABS(mycode) ORDER BY CODE_ ASC LIMIT 1; SET lp = lp + 4; ELSE SET mypy = CONCAT(mypy,CHAR(CAST(ASCII(UNHEX(SUBSTRING(NAME, lp, 2))) AS UNSIGNED))); SET lp = lp + 2; END IF; END WHILE; RETURN LOWER(mypy);END;$DELIMITER ;四、使用方法(部分生僻字无法转换，查到的都是zuo)select to_pinyin('测试')

正常安装并配置MySQL；新建想修改的数据库安装目录如D:\MySQL\data;拷贝C:\ProgramData\MySQL\MySQL Server 5.6\data下所有文件到D:\MySQL\data；找到MySQL软件的安装目录默认为C:\ProgramData\MySQL\MySQL Server 5.6中的my.ini文件找到 datadir="C:/ProgramData/MySQL/MySQL Server 5.6/data/" 修改为：datadir="D:/MySQL/data"添加D:\MySQL\data访问权限，添加NETWORK SERVICE 完全控制权限重新启动MySQL服务即可，新建一个数据库目录D:\MySQL\data下就会多出一个文件夹即数据库目录。重要提示:安装配置MySQL经常会遇到两个错误 1是1045，这个往往是重新安装或重新配置数据库要求输入原来的密码，而密码错误导致的， 解决办法是重新安装之前卸载原来的程序，并删除安装目录和C:\ProgramData\下的MySQL目录，重新安装即可。 2是1067，这是上面提到的更改数据库目录后如果设置不正确启动服务是遇到的错误，按照上面的步骤即可避

事件调度器概述在MySQL 5.1中新增了一个特色功能事件调度器（Event Scheduler），简称事件。它可以作为定时任务调度器，取代部分原来只能用操作系统的计划任务才能执行的工作。另外，更值得一提的是，MySQL的事件可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下是非常实用的。事件调度器是定时触发执行的，从这个角度上看也可以称作是“临时触发器”。但是它与触发器又有所区别，触发器只针对某个表产生的事件执行一些语句，而事件调度器则是在某一段（间隔）时间执行一些语句。1.查看事件调度器是否开启事件由一个特定的线程来管理。启用事件调度器后，拥有SUPER权限的账户执行SHOW PROCESSLIST就可以看到这个线程了。示例：查看事件是否开启。SHOW VARIABLES LIKE 'event_scheduler'; SELECT @@event_scheduler; SHOW PROCESSLIST;2.开启或关闭事件调度器通过设定全局变量event_scheduler的值即可动态的控制事件调度器是否启用。开启MySQL的事件调度器，可以通过下面两种方式实现。2.1通过设置全局参数使用SET GLOBAL命令可以开启或关闭事件。将event_scheduler参数的值设置为ON，则开启事件；如果设置为OFF，则关闭事件。示例：使用SET GLOBAL命令可以开启或关闭事件。-- 开启事件调度器 SET GLOBAL event_scheduler = ON;-- 关闭事件调度器 SET GLOBAL event_scheduler = OFF;-- 查看事件调度器状态 SHOW VARIABLES LIKE 'event_scheduler'; 注意：如果想要始终开启事件，那么在使用SET GLOBAL开启事件后，还需要在my.ini（Windows系统）/my.cnf（Linux系统）中添加event_scheduler=on。因为如果没有添加，MySQL重启事件后又会回到原来的状态。2.2通过更改配置文件在MySQL的配置文件my.ini（Windows系统）/my.cnf（Linux系统）中，找对[mysqld]，然后在下面添加以下代码开启事件。 事件调度器启动状态 event_scheduler = on 在配置文件中添加代码并保存文件后，还需要重新启动MySQL服务器才能生效。通过该方法开启事件，重启MySQL服务器后，不恢复为系统默认的状态。

数据库的加锁操作一： 什么是数据库加锁 ？数据库加锁: 简单的意思就是对于在执行一个操作（比如修改）时，对这个操作的对象加锁，放置其他操作读取到脏数据或者幽灵数据。或者术语来说就是一种排他锁，当写的时候不允许其他程序写，这样就可以保证数据一致性了二：为什么要给数据加锁？对于这点，我们需要简单的了解几个概念：（1）.什么是事务？事务： 是用户定义的数据库操作系列，这些操作作为一个完整的工作单元执行。一个事务内的所有语句作为一个整体。要么全部执行，要么全部不执行。事务的几个特点： 原子性，一致性，隔离性，持久性， 简称ACID特征一般来讲： 事务的这几个特点被遭到破坏的有下列几个情况：（1） 多事务并行运行时，不同事务的操作有交叉情况。（->_-> 如果你想不到列子：就想想多线程问题）（2） 事务在运行过程中被强迫停止。（2）什么是脏读：脏读 ：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。（3）什么是不可重复读？不可重复读 ：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。（4）什么是huan读？huan读 : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。因为在上述的情况下，数据会出现脏数据。对于一个考虑安全性的系统而言，加锁自然是十分必要.三、如何对数据加锁：对于数据加锁： 一般分为如下两种，第一类，就是数据库自己加锁，第二类，就是线程锁。第一种： 数据库自己加锁行级锁：是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。表级锁：是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。页级锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。死锁：当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。举几个列子：对于数据表的加锁：方法一： 使用SQL语句进行加锁lock tables Gxjun read;select * from Gxjun;unlock tables 方法二 ， 采用记录锁加锁：public void test() { String sql = "select * from Gxjun for update"; try { conn.setAutoCommit(false); this.pstmt = conn.prepareStatement(sql); pstmt.executeQuery(); } catch (Exception e) { System.out.println("异常" + e.getMessage()); } } 锁的使用规则：for update 与 lock in share mode 属于行级锁和页级锁 for update 排它锁,lock in share mode 共享锁 对于记录锁.必须开启事务. 行级锁定事实上是索引记录的锁定.只要是用索引扫描的行（或没索引全表扫描的行）,都将被锁住. 在不同的隔离级别下还会使用next-key locking算法.即所扫描的行之间的“间隙”也会也锁住(在Repeatable read和Serializable隔离级别下有间隙锁). 在mysql中一共享锁的含义是:在被共享锁锁住的行,即使内容被修改且并没有提交.在另一个会话中依然看到最新修改的信息.在同一会话中加上了共享锁.可以对这个表以及这个表以外的所有表进行增、删、改、查的操作.在不同的会话中.可以查到共享锁锁住行的最新消息.在mysql中排他锁的含义是:在被排它锁锁住的行,内容修改并没提交，在另一个会话中不会看到最新修改的信息。在不同的会话中.可以查到共享锁锁住行的最新消息.在同一个会话中的可以叠加多个共享锁和排他锁.在多个会话中,需要等待锁的释放. SQL中的update 与 for update是一样的原理. 等待超时的参数设置:innodb_lock_wait_timeout=50 (单位秒). 任何可以触发事务提交的命令,都可以关闭共享锁和排它锁.参考：https://blog.csdn.net/weixin_43687896/article/details/84069654

1、什么是表分区？    mysql中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。    表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。2、表分区与分表的区别分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。3、表分区有什么好处？（1）与单个磁盘或文件系统分区相比，可以存储更多的数据。（2）对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。（3）一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。（4）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。5）通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。4、分区表的限制因素（1）一个表最多只能有1024个分区。（2）MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。（3）如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。（4）分区表中无法使用外键约束。（5）MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。5、MySQL支持的分区类型有哪些？（1）RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。（2）LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。（3）HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。（4）KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。说明：在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列(BLOB，TEXT类型除外)作为分区键。参考连接：https://blog.csdn.net/weixin_62958599/article/details/127386923

       索引是对数据库表中一列或多列的值进行排序的一种结构类似与一本书的目录。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。优点索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。索引可以帮助服务器避免排序和创建临时表索引可以将随机IO变成顺序IO索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。缺点创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。对于非常小的表，大部分情况下简单的全表扫描更高效；通过以上可以发现索引的优缺点还是很明显的，那么在实际项目中应该按照什么准则建立索引呢。应该创建索引的列在经常需要搜索的列上，可以加快搜索的速度在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。不该创建索引的列对于那些在查询中很少使用或者参考的列不应该创建索引。 若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。对于那些只有很少数据值或者重复值多的列也不应该增加索引。 这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。对于那些定义为text, image和bit数据类型的列不应该增加索引。 这些列的数据量要么相当大，要么取值很少。当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）索引类型Normal：普通索引；Unique：唯一索引；FullText：全文索引，通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引，就是为这种场景设计的，通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题。索引方式BTREE，B树索引又分为B树和B+树B树特征：1.关键字集合分布在整颗树中；2.任何一个关键字出现且只出现在一个结点中；3.搜索有可能在非叶子结点结束；4.其搜索性能等价于在关键字全集内做一次二分查找；5.自动层次控制；B+树的特征：1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；2.不可能在非叶子结点命中；3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；4.每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。5.更适合文件索引系统；HASH哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。原文参考：cid:link_0

MySQL两张表取差集业务场景如下：人员表中有证件号、手机号字段，这两个字段因为涉及到个人隐私问题，因此加密存储，有另外一张解密表可以和人员表进行关联，查出解密后的证件号和手机号，现在需要统计出人员表中有多少没有加密的数据，进行再次加密处理，这个时候，考虑到的就是求两张表的差集，来找出未加密的人员。求差集 SQL 脚本1234select p.* from persons pleft join CERT_CNO_PNO_TAB c ON c.PERSON_BUSINESS_ID = p.idwhere p.`status` = 'person.status.allow.yes' and p.del_flag = '0'and c.id is null;注意看，where 语句的最后一个条件。通过 left join 连表之后，再使用右表的 c.id is null 来进行过滤，最终可以得到 person 表与 CERT_CNO_PNO_TAB 表中的差集。结果如下：我们来验证一下，看看求得的这个差集，在解密表中是否真的不存在。1select * from CERT_CNO_PNO_TAB where PERSON_BUSINESS_ID = '0f551c3e03e34e449e5b2e31b64efdc5';结果如下：可以看出，通过 left join 再加上 右表的条件过滤取的的差集，是正确的。这种求差集的效率，远比 not in 、not exist 要快的多，小伙伴们可以尝试一下！MySQL查两个表之间的数据差集需要查两个表之间的差集首先，想到的是主键直接not in1select mailbox_id from co_user where mailbox_id not in (select mailbox_id from core_mailbox);好吧！这个是可以，但是数据多了的话，想到这个查询的逻辑有点受不住于是再改为下面的这样：1234select cu.mailbox_id,cm.mailbox_id from co_user as cu 　　　　left join core_mailbox as cm 　　　　　　on cu.mailbox_id = cm.mailbox_id 　　　　　　　　where cm.mailbox_id is NULL;利用了left join的，然后进行对比，并且利用where进行筛选。后面也在网上找了这条：123SELECT mailbox_id FROM `co_user` left join (select mailbox_id as i from core_mailbox) as t1on co_user.mailbox_id= t1.i where t1.i is NULL;概念上与第二条同理。好吧！ 回顾了一下left joinSQL LEFT JOIN 关键字LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。转载自https://www.jb51.net/article/274538.htm

一般在更新时会遇到以下场景：1.所有字段全部更新；2.根据条件更新字段中的某部分内容；3.根据不同的条件更新不同的值以下是几种场景中常用的update方法。一、方法分类二、具体用法（1）根据条件更新值根据指定条件更新(多列)（全部更新）把表中 [符合条件的行的] 列名1字段中的值全部修改为值1 [，列名2字段中的值修改为值2]。123update 表名 set 列名1 = 值1 [,列名2=值2][where 条件];替换指定值(多列)（部分更新）把表中 [符合条件的行的] 列名1字段中的查找内容全部修改为替换内容 [，列名2字段中的查找内容全部修改为替换内容]。123update 表名 set 列名1 = replace(列名1, '查找内容', '替换内容') [,列名2 = replace(列名2, '查找内容', '替换内容')] [where 条件];（2）按照不同条件(批量)更新不同值使用 if把表中 [符合条件的行的] 列名1字段中符合条件1的内容修改为值1，否则修改为值2 [,列名2字段中符合条件2的内容修改为值3，否则修改为值4]。12345update tableset    列名1 = if(条件1,值1,值2),    列名2 = if(条件2,值3,值4)[where 条件];使用 case when把表中 [符合条件的行的] 列名1字段中符合条件1的内容修改为值1 [,符合条件2的修改为值2,...] [,列名2字段中符合条件21的内容修改为值21,符合条件22的修改为值22,...] 。12345678910111213141516update tableset 列名1 =    case        when 条件1 then 值1        when 条件2 then 值2        when 条件3 then 值3        ...    end,    列名2 =    case        when 条件21 then 值21        when 条件22 then 值22        when 条件23 then 值23        ...    end[where 条件];三、实例students 表 (id表示主键，name是姓名，score是平均成绩)idnamescore1李明992张三743孙华59（1）根据条件更新值把 students 表中 name 为张三的 score 字段的值全部修改为100。1234#使用whereupdate students set score = 100where name = '张三';idnamescore1李明992张三1003孙华59把 students 表中 id 大于等于2的所有行中 score 中59的部分全部修改为0，name 中三的部分全部修改为四。12345#使用replaceupdate students set score = replace(score,59,0),    name = replace(name,'三','四')where id >= 2;注意：张三替换之后是张四，并不是只有字段等于三时才能替换。idnamescore1李明992张四743孙华0（2）按照不同条件更新不同值请把students表中score小于60的score字段全部改为0，否则改为100，name字段中的名字改为不及格，否则改为及格。12345#批量更新多值 + ifupdate students set    score = if(score < 60,0,100),    name = if(score < 60,'不及格','及格');idnamescore1及格1002及格1003不及格0注意：更新的值要满足建表时的字段类型。比如score是int类型就不能更新为char类型。请把students表中score小于60的score字段全部改为0，name字段中的名字改为不及格；score大于等于90的score字段全部改为2，name字段中的名字改为优秀；score大于等于60小于90的score字段全部改为1，name字段中的名字改为良好。12345678910111213#批量更新多值 + case whenupdate students set    name = case        when score < 60 then '不及格'        when score >= 90 then '优秀'        else '良好'    end,        score = case        when score < 60 then 0        when score >= 90 then 2        else 1    end;注意：更新的时候是按照代码语句的先后顺序更新的。可以尝试先更新score后更新name，结果是不一样的。idnamescore1优秀22良好13不及格0转载自https://www.jb51.net/article/274724.htm

MySQL需要关注的参数及状态变量open_files_limit操作系统允许mysqld打开的文件数量。这个值可以设置得比较大，比如50000，最好在系统初始化安装时就设置了一个较大的值。可修改文件/etc/security/limits.conf来实现，命令如下：12vi /etc/security/limits.conf* -nofile 50000max_connect_errors此值应设置得比较大，如大于5000，以避免因为连接出错而超过出错阈值，导致MySQL阻止该主机连接，如被阻塞，则须手动执行flush-hosts进行复位。max_connections允许并行的客户端连接数目。默认值为100太小，一般会不够用。生产环境中建议设置为2000~5000.注意，对于32位的MySQL由于有内存限制，连接数不能过大(建议小于800)，否则可能会由于连接过多，造成MySQL实例崩溃。max_used_connectionsMySQL Server启动后曾经到达的最大连接数。如果该值达到max_connections，那么某个时刻存在突然的高峰连接时，可能会有性能问题。threads_connected当前打开的连接数量。这个值不能超过设置的max_connections*80%。需要注意及时调整max_connections的值。一旦连接数超过了max_connections，就会出现客户端连接不上的错误。aborted_connects试图连接到MySQL服务器而失败的连接数。正常情况下，该值不会持续增加，出现连接失败的原因主要有如下几点：1) 客户端程序在退出之前未调用mysql_close()。2) 客户端的空闲时间超过了wait_timeout或interactive_timeout秒，未向服务器发出任何请求。3) 客户端在数据传输中途突然结束。Aborted_clients由于客户端没有正确关闭连接导致客户端终止而中断的连接数。出现下述情况时，服务器将增加”Aborted_clients“(放弃客户端)的状态变量。1) 客户端不具有连接至数据库的权限。2) 客户端采用了不正确的密码。3) 连接信息包含不正确的信息。4) 获取连接信息包的时间超过了connect_timeout秒。我们可以使用如下命令发现异常：1mysqladmin -uroot -p -S /path/to/tmp//3306/mysql.sock ext | grep Abort也可以使用tcpdump来判断是什么原因导致了异常：12tcpdump -s 1500 -w tcp.out port 3306strings tcpdump.outthread_cache_size服务器应缓存多少线程以便重新使用？当客户端断开连接时，如果线程少于thread_cache_size，则客户端的线程将被放入缓存。如果有新连接请求分配线程则可以从缓存中重新利用线程，只有当缓存空了时才会创建新线程。如果新连接很多，则可以增加该变量以提高性能。如果是大量并发的短连接，则可能会因为thread_cache_size不够而导致性能问题。生产环境中一般将其设置为100~200。由于线程可以缓存，所以线程持有的内存不会被轻易释放。Threads_created创建用来处理连接的线程数。应该监视Thread_created的增量，如果较多，则需要增加thread_cache_size的值。以上对thread_cache_size的设置在高并发的时候会很有效。高并发时大量并发短连接对CPU的冲击不容忽视。treads_running指同时运行的线程数目。这个值一般不会大于逻辑CPU的个数，如果经常有过多的线程同时运行，那么可能就意味着有性能的问题。这个指标很重要往往表明一个系统繁忙程度，它在系统爆发性性能问题之前，会有一个上升的趋势，此时收集的性能信息，将有助于我们诊断复杂的性能问题。slow_launch_chreads如果这个值比较大，则意味着创建线程太慢了，可能是系统出现了性能问题，存在资源瓶颈，从而导致操作系统没有安排足够的CPU时间给新创建的线程。query_cache_size为了缓存查询结果分配的内存大小。一般设置为256MB。注意不要设置得太大。可监控查询缓存命中率：Qcache_hits / (Qcache_hits+Com_select)。更改这个值，会清空所有的缓存结果集，对于非常繁忙的系统，可能会很耗时，导致服务停顿，因为MySQL在删除所有的缓存查询时是逐个进行的。Qchache_lowmem_prunes该变量记录了由于查询缓存出现内存不足，而需要从缓存中删除的查询数量，可通过监控Qcache_lowmem_prunes的增量，来衡量是否需要增大query_cache_size。Qcache_lowmem_prunes状态变量提供的信息能够帮助你调整查询缓存的大小。它可计算为了缓存新的查询而从查询缓存区移出到自由内存中的查询数目。查询缓存区使用最近最少使用(LRU)策略来确定哪些查询需要从缓存区中移出。InnoDB_buffer_pool_wait_free一般情况下，是通过后台向InnoDB缓冲池中写入数据的。但是，如果需要读或创建页，并且没有 干净的页可用，那么它还需要先等待页面清空。如果已经适当设置了缓冲池的大小，那么该值应该会很小。Slow_queries查询时间超过long_query_time秒的查询个数。应该监控此变量的增量变化，一般1秒内不要超过5~10个，否则可能是有性能问题。Select_full_join没有 使用索引的连接数量。如果该值较大，则应该仔细检查一下表的索引。Created_tmp_tables创建内存临时表的数量，如果Created_tmp_disk_tables比较大，则应该考虑增加tmp_table_size的大小。注：应该将tmp_table_size和max_heap_table_size简单调整到大小一样。32MB一般足够了。对这两个参数的控制通常基于内存引擎的临时表可以增长的阈值，若超过了这个阈值，就会转化成 On-disk MyISAM表。Created_tmp_disk_tables服务器执行语句时在硬盘上自动创建的临时表的数量。Bytes_received 和 Bytes_sent可用来监控MySQL的流量。key_buffer_sizeMyISAM索引缓冲，实际用到多少就分配多少。不一定需要分配很大的空间，可参考实际观察到的值，不要大于实际值。如下命令可用于评估索引空间的大小。1select sum(index_length) from information_schema.tables where engine='MYISAM';Open_tables当前打开的表的数量。Opened_tables已经打开的表的数量。查看Open_tables及Opened_tables的增量时，如果后者的增量比较大，那么可能table_open_cache（或者table_cache）不够用了。如果Open_tables对比table_cache_size并不大，但Opened_tables还在持续增长，那么也可能是显式临时表被不断打开而导致的。table_open_cache（table_cache 5.1.3之前的参数名）默认的设置太小了，生产环境中应该将其设置得足够大，数千到一万是比较合理的值。检查Opened_tables status变量，如果该值比较大，而我们不经常运行 FLUSH TABLES命令，那么应该增加table_open_cache的变量值。table_definition_cache一般可以将其设置为足够高的值来缓存表定义，比如4096，这并不会耗费什么资源。默认的256太小了。转载自https://www.jb51.net/article/274699.htm

项目中有需要,使用MySQL的in子查询,查询符合in子查询集合中条件的数据,但是没想到的是,MySQL的in子查询会如此的慢,让人无法接受,下面这篇文章主要给大家介绍了关于Mysql查询优化之IN子查询优化的相关资料,需要的朋友可以参考下+目录物化表首先提出一个不相关的IN子查询12SELECT * FROM s1  WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');对于不相关的 IN 子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：结果集太多，可能内存中都放不下～对于外层查询来说，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就导致：无法有效的使用索引，只能对外层查询进行全表扫描。在对外层查询执行全表扫描时，由于 IN 子句中的参数太多，这会导致检测一条记录是否符合和 IN 子句中的参数匹配花费的时间太长。比如说 IN 子句中的参数只有两个：SELECT * FROM tbl_name WHERE column IN (a, b);这样相当于需要对 tbl_name 表中的每条记录判断一下它的 column 列是否符合 column = a OR column= b 。在 IN 子句中的参数比较少时这并不是什么问题，如果 IN 子句中的参数比较多时，比如这样：SELECT * FROM tbl_name WHERE column IN (a, b, c …, …);那么这样每条记录需要判断一下它的 column 列是否符合 column = a OR column = b OR column = cOR … ，这样性能耗费可就多了。所以提出一个解决方案：不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。临时表的特性：该临时表的列就是子查询结果集中的列。写入临时表的记录会被去重。一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size ，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+ 树索引。这个将子查询结果集中的记录保存到临时表的过程称之为 物化。物化表转连接当我们把子查询进行物化之后，假设子查询物化表的名称为 materialized_table ，该物化表存储的子查询结果集的列为 m_val ，那么这个查询其实可以从下边两种角度来看待：12SELECT * FROM s1WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a');从表 s1 的角度来看待，整个查询的意思其实是：对于 s1 表中的每条记录来说，如果该记录的 key1 列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。画个图表示一下就是这样：从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在 s1 表中找到对应的 key1 列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。也就是说其实上边的查询就相当于表 s1 和子查询物化表 materialized_table 进行内连接：1SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;如果使用 s1 表作为驱动表的话，总查询成本由下边几个部分组成：物化子查询时需要的成本扫描 s1 表时的成本s1表中的记录数量 × 通过 m_val = xxx 对 materialized_table 表进行单表访问的成本（物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。如果使用 materialized_table 表作为驱动表的话，总查询成本由下边几个部分组成：物化子查询时需要的成本扫描物化表时的成本物化表中的记录数量 × 通过 key1 = xxx 对 s1 表进行单表访问的成本

 Mysql知识点 通过下面的图片可以看出，MySQL基础语法分为四部分：连接数据库，对数据库的操作，对表中的数据操作，对表操作等等。  SQL知识点 SQL相关的知识点就多了，SQL就是对数据库表进行操作，需要掌握的技术知识点就比较多了。 比如：  如何创建表，更新表，删除表，重命名表。 什么是组合查询，什么是子查询等等。 如何过滤检索数据，分组数据，排序检索数据，快速检索数据。 如何使用函数处理数据，SQL中会用到哪些函数？ 还要知道seclect查询语句的执行顺序。 还需要知道聚集函数的使用。 联表查询的实现方法。  Mybatis知识点 包含了  快速入门 mybatis缓存：一级缓存，二级缓存 Mapper代理知识点 映射关系， 映射文件介绍  看上图知道了一级缓存就是基于sqlSession的缓存，Mybatis默认是开启一级缓存的。实现原理就是：通过一个Map来实现 同一个sqlsession再次发出相同的sql，就从缓存中取不走数据库。如果两次中间出现commit操作（修改、添加、删除），本sqlsession中的一级缓存区域全部清空，下次再去缓存中查询不到所以要从数据库查询，从数据库查询到再写入缓存。 与Spring整合之后，使用的是Mappper代理对应，一级缓存是失效的。为什么呢？因为在同一线程里面两次查询同一数据所使用的sqlsession是不相同的。 二级缓存是基于Mapper(同一个命名空间)的缓存，Mybaits的二级缓存是需要自己在配置文件中配置的。查询结果映射的pojo需要实现 java.io.serializable接口，useCache=“false”。 这就是mybatis中一级缓存和二级缓存。面试的时候也会并经常问到，一定要掌握闹牢固。 面试题分享 MySQL部分 一千万条数据的表, 如何分页查询 数据量过大的情况下, limit offset分页会由于扫描数据太多而越往后查询越慢. 可以配合当前页最后一条ID进行查询。  SELECT * FROM T WHERE id > #{ID} LIMIT #{LIMIT}。 当然, 这种情况下ID必须是有序递增的, 这也是有序ID的好处之一。  MySQL怎么恢复半个月前的数据 需要前期是有定期的备份整个数据库的数据，如果有备份可以通过binlog日志进行恢复  MySQL事务的隔离级别, 分别有什么特点 1. 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到. 2. 读提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到. 3. 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的. 4. 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行  唯一索引比普通索引快吗, 为什么？ 唯一索引不一定比普通索引快, 还可能慢.  原因是：  1. 查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微. 2. 更新时, 这个情况就比较复杂了. 普通索引将记录放到change buffer中语句就执行完毕了. 而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作. 对于写多读少的情况, 普通索引利用change buffer有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引.  订单表数据量越来越大导致查询缓慢, 如何处理 分库分表. 由于历史订单使用率并不高, 高频的可能只是近期订单,  因此, 将订单表按照时间进行拆分, 根据数据量的大小考虑按月分表或按年分表. 订单ID最好包含时间(如根据雪花算法生成), 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询.  Mybatis部分 1.Mybatis是否支持延迟加载？如果支持，它的实现原理是什么？  Mybatis仅支持association关联对象和collection关联集合对象的延迟加载，association指的就是 一对一，collection指的就是一对多查询。在Mybatis配置文件中，可以配置是否启用延迟加载 lazyLoadingEnabled=true|false。 它的原理是: 使用CGLIB创建目标对象的代理对象，当调用目标方法时，进入拦截器方法，比如调 用a.getB().getName()，拦截器invoke()方法发现a.getB()是null值，那么就会单独发送事先保存好 的查询关联B对象的sql，把B查询上来，然后调用a.setB(b)，于是a的对象b属性就有值了，接着完 成a.getB().getName()方法的调用。  这就是延迟加载的基本原理。 当然了，不光是Mybatis，几乎所有的包括Hibernate，支持延迟加载的原理都是一样的。  2.#{}和${}的区别  #{}是占位符，预编译处理；${}是拼接符，字符串替换，没有预编译处理。  Mybatis在处理#{}时，#{}传入参数是以字符串传入，会将SQL中的#{}替换为?号，调用 PreparedStatement的set方法来赋值。 #{} 可以有效的防止SQL注入提高系统安全性 。后者不能防止SQL 注入#{} 的变量替换是在DBMS 中；${} 的变量替换是在 DBMS 外  使用MyBatis的mapper接口调用时有哪些要求？ 1.Mapper接口方法名和mapper.xml中定义的每个sql的id相同。 2.Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql 的parameterType的类型相 同。 3.Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同。 4.Mapper.xml文件中的namespace即是mapper接口的类路径。  Mybatis如何执行批量操作 使用foreach标签 foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach标签的属性主 要有item，index，collection，open，separator，close。 item 表示集合中每一个元素进行迭代时的别名，随便起的变量名； index 指定一个名字，用于表示在迭代过程中，每次迭代到的位置，不常用； open 表示该语句以什么开始，常用“(”； separator 表示在每次进行迭代之间以什么符号作为分隔符，常用“,”； close 表示以什么结束，常用“)”。 在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是 在不同情况下，该属性的值是不一样的， 主要有一下3种情况：  如果传入的是单参数且参数类型是一个List的时候，collection属性值为list 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封 装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map 的， map的key就是参数名 所以这个时候collection属性值就是传入的List或array对象在自己封 装的map里面的key 具体用法如下：  <!-- 批量保存(foreach插入多条数据两种方法)int addEmpsBatch(@Param("emps") List<Employee> emps); --> <!-- MySQL下批量保存，可以foreach遍历 mysql支持values(),(),()语法 --> //推荐使用 <insert id="addEmpsBatch"> INSERT INTO emp(ename,gender,email,did) VALUES <foreach collection="emps" item="emp" separator=","> (#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id}) </foreach> </insert> <!-- 这种方式需要数据库连接属性allowMutiQueries=true的支持 如jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true --> <insert id="addEmpsBatch"> <foreach collection="emps" item="emp" separator=";"> INSERT INTO emp(ename,gender,email,did) VALUES (#{emp.eName},#{emp.gender},#{emp.email},#{emp.dept.id}) </foreach> </insert> 使用ExecutorType.BATCH Mybatis内置的ExecutorType有3种， 默认为simple,该模式下它为每个语句的执行创建一个 新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所 有更新语句，显然batch性能将更优； 但batch模式也有自己的问题，比如在Insert操作时， 在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的 具体用法如下： mapper和mapper.xml如下  //批量保存方法测试 @Test public void testBatch() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); //可以执行批量操作的sqlSession SqlSession openSession = sqlSessionFactory.openSession(ExecutorType.BATCH);  //批量保存执行前时间 long start = System.currentTimeMillis(); try { EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class); for (int i = 0; i < 1000; i++) { mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0, 5), "b", "1")); }  openSession.commit(); long end = System.currentTimeMillis(); //批量保存执行后的时间 System.out.println("执行时长" + (end - start)); //批量 预编译sql一次==》设置参数==》10000次==》执行1次 677 //非批量    （预编译=设置参数=执行 ）==》10000次 1121 } finally { openSession.close(); } mapper和mapper.xml如下  public interface EmployeeMapper { //批量保存员工 Long addEmp(Employee employee); } <mapper namespace="com.jourwon.mapper.EmployeeMapper" <!--批量保存员工 --> <insert id="addEmp"> insert into employee(lastName,email,gender) values(#{lastName},#{email},#{gender}) </insert> </mapper> 写到最后，一直在技术路上前行… ———————————————— 版权声明：本文为CSDN博主「小冷coding」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/leng_yong/article/details/128806329 

 《狂飙》电视剧你看了吗？ 咖啡干嚼不加糖，我是建工高启强。 手拿冻鱼追一路，我叫启盛你记住。 鱼摊卖鱼开箱货，杀人还得陈金默。 孟钰启兰把我亲，只玩不处叫安欣。 先亲程程后摸腿，我是莽村李宏伟。 AD钙奶来上香， 京海大佬叫徐江。 老公被埋不知情，我是大嫂陈书婷。 脚踩五菱没刹车，记住我是有田哥。 放贷不还就见红，我是刀哥唐小龙。 特产一箱接一箱，我是区长龚开疆。 金丝眼镜真斩男，我是御姐高启兰。 早饭喝粥不放糖，我是省委高玉良。 师父牺牲我撒谎，我的名字叫李响。 一路狂飙到凌晨，我是少爷高晓晨。 交警缉毒带管电，我的名字叫杨健。 做局从来不露面，冬哥安排秘书见。 具体的可以看电视剧哈，强哥这个人物张颂文演的可以的。你觉得呢？  程序“狂飙”，你担心吗？ 那回到项目程序里面，如果你负责写的项目程序一直狂飙的话，怕不怕，慌不慌。 是不是要想办法解决问题，不让程序挂掉。避免问题出现，控制住成本，不让程序崩溃。  这篇文章就写一下基于MySQL的方面的性能优化处理方法。掌握了MySQL性能调优总能解决一部分问题吧。  性能优化步骤 适合创建索引的情况 主键自动建立唯一索引； 频繁作为查询条件的字段应该创建索引; 查询中与其它表关联的字段，外键关系建立索引; 单键/组合索引的选择问题， 组合索引性价比更高; 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度; 查询中统计或者分组字段。 不适合创建索引的情况 表记录太少; 经常增删改的表或者字段; Where 条件里用不到的字段不创建索引; 过滤性不好的不适合建索引。 Explain 性能分析 使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。 分析查询语句或是表结构的性能瓶颈。 用法： Explain +SQL 语句。  执行代码如下，没有where条件 EXPLAIN SELECT * from t_im_contacts 1 Explain 执行后返回的信息  以上所有结果列说明如下：  参数    说明 id    选择标识符，id 越大优先级越高，越先被执行；select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序。 select_type    表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询 table    输出结果集的表 partitions    匹配的分区 type    表示表的连接类型,类型有很多单独说明下 possible_keys    表示查询时，可能使用的索引 key    表示实际使用的索引 key_len    索引字段的长度 ref    列与索引的比较 rows    大概估算的行数 filtered    按表条件过滤的行百分比 Extra    执行情况的描述和说明 id说明 id 相同，执行顺序由上至下 id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行 id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行衍生 = DERIVED  关注点： id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。  select_type 说明  type字段说明 最重要的就是 type 字段，type 值类型如下：  all — 扫描全表数据； index — 遍历索引； range — 索引范围查找； index_subquery — 在子查询中使用 ref； unique_subquery — 在子查询中使用 eq_ref； ref_or_null — 对 null 进行索引的优化的 ref； fulltext — 使用全文索引； ref — 使用非唯一索引查找数据； eq_ref — 在 join 查询中使用主键或唯一索引关联； const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。 Extra说明一下 出现 null 表示查询的列未被索引覆盖，且where筛选条件是索引的前导列，这意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表查询”来实现,性能不是很好。  Using index condition 表示查询条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。  Using index 使用索引，Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是 用来读取数据而非利用索引执行查找  Using where 单独出现 表示当前查询的字段不能被索引覆盖，所以可能会产生回表，效率略低  Using where,Using index 表示查询的列被索引覆盖，且where筛选条件是索引列前导列的一个范围，或者是索引列的非前导列。 效率比较高  Using filesort 这种情况是在使用 order by 关键字的时候，如果待排序的内容无法通过索引直接直接进行排序，mysql就有可能进行文件排序。但是由于查询次数过多的话，对于排序的效率还是有一定的影响的。  所以，根据自己的情况进行优化改进即可。你有好的建议欢迎评论区交流讨论。 让自己的程序平稳的运行。  写到最后，一直在技术路上前行…  昨天，删去；今天，争取；明天，努力。 ———————————————— 版权声明：本文为CSDN博主「小冷coding」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/leng_yong/article/details/128853450