MYSQL应用手册_v0.2

1、Mysql应用手册1 前言1.1 开发和维护功能，介绍以下专题： · 常用数据类型· 函数· 过程· ORACLE到MYSQL模型迁移· 常见错误· 常用工具· 应用设计规则1.2 数据库管理功能，介绍以下专题· 安装部署· 常用管理工具· 语法优化工具1.3 附录· ORACLE和MYSQL编写常用SQL及函数差异2 开发维护篇2.1 SQL语法MYSQL基本参照标准SQL，而ORACLE除了SQL语法，还有PL/SQL标准。两者在一些常用SQL语句和函数存在一些较少的差异，详见附录

2、一ORACLE和MYSQL常用SQL及函数差异，在此不再做介绍。2.2 常用数据类型常用数据类型分数值（含整数和浮点数），字符串（含大数据），时间。2.2.1 数值类型存储(字节)范围（有符号）范围（无符号）用途TINYINT1 -1281270255小整数值SMALLINT2 -32 76832 767065 535大整数值MEDIUMINT3-8 388 6088 388 607016 777 215大整数值INT或INTEGER4 -2 147 483 6482 147 483 64704 294 967 295大整数值BIGINT8 -9 233 372 036 854 775 808

3、9 223 372 036 854 775 807018 446 744 073 709 551 615极大整数值FLOAT4 (-3.402 823 466 E+38，1.175 494 351 E-38)0(1.175 494 351 E-38，3.402 823 466 351 E+38)0(1.175 494 351 E-38，3.402 823 466 E+38)单精度浮点数值DOUBLE8 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)0(2.225 073 858 507 201 4 E-308，1.79

4、7 693 134 862 315 7 E+308)0(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值说明： 定义了整数值的显示宽度（如：INT(4)）。表示可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度，它不限制列的保存范围，也不限制列中超过指定列宽度的值的显示。浮点值定义如float(7,4)，表示有3位整数，最多保存4位小数，超过4位小数会被四舍五入，而超过3位整数

5、位的值会报错。建议浮点数统一使用DECIMAL，对精度计算更精准，且以二进制方式存储。2.2.2 DATE类型存储(字节)范围格式用途DATE31000-01-019999-12-31YYYY-MM-DD日期值TIME3'-838:59:59''838:59:59'HH:MM:SS时间值或持续时间YEAR119012155YYYY年份值DATETIME81000-01-01 00:00:009999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP8UTC 1970-01-01 00:00:002038-01-

6、19 03：14：07YYYYMMDD HHMMSS混合日期和时间值，时间戳说明：TIME不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。TIMESTAMP类型支持默认自动更新时间戳：· 只指定缺省值：timestamp default current_timestamp· 定义缺省值并随记录变更而更新 timestamp default current_timestamp on update current_ttimestamp2.2.3 字符串类型存储（字节）用途CHAR1255 定长

7、字符串VARCHAR165535变长字符串TINYBLOB1255不超过 255 个字符的二进制字符串TINYTEXT1255短文本字符串BLOB165535二进制形式的长文本数据TEXT165535长文本数据MEDIUMBLOB116777215二进制形式的中等长度文本数据MEDIUMTEXT116777215中等长度文本数据LOGNGBLO进制形式的极大文本数据LONGTEX大文本数据说明： 存储大小参照字段保存值为字母，如果字符集是UTF8，CHAR存储大小最多为255*3字节。 字符串的存储依赖于字符集，一般字符集选择通用的UTF8（

8、字符占用存储最小1个字节，最高3个字节）。注意在实际保存值的在存储方面的区别。2.3 函数2.3.1 日期和时间相关获取当前系统日期和时间：now(): 在语句执行开始得到值sysdate()：在函数执行开始时得到最新值localtime()：在语句执行开始得到值获取当前日期（年月日）：curdate(),current_date()获取当前时间（时分秒）：curtime(),current_time()获取当前时间（按格式）：Hour(time)：小时minute(time):分钟month(date)：月份year(date)：年份date_format(date,format): 格式化

9、时间（%Y 年（YYYY）%m 月（MM 00.12）%d 日（DD 00.31）%H 小时（HH 00.24）%i 分钟（00.59）%s 秒（00.59）%f 微秒（000000.999999）计算日期：加减：date_add(date,INTERVAL expr type)，type指second,minute,hour,day,week,month,year等；例如：select date_add(sysdate(),interval -1 day);2.3.2 字符串函数返回大写字符串：ucase(aaa) 或者upper(aaa);返回小写字符串：lcase(AAA) 或者lowe

10、r(AAA);返回字符串长度：length(AAA)；截断空格： ltrim( asss ) ,rtrim( aaa ), trim( aaa );字符串连接：concat(s1,s2,);字符串替换：insert(s1,x,y,s2) 将s1第x个位置开始，共y个字符的子串替换成s2;返回字符串指定值：left(s1,x)返回s1最左边的x个字符；right(s1,x)返回s1最右边的x个字符。字符截取：substring(s1,m,n)取s1从第m位置开始的余下所有或长度为n的字符串；2.3.3 类型转换函数cast(s1 as type): 指定的类型如： binary,char,dat

11、e,time,datetime,signed,unsigned。convert(s1,type): 指定的类型如：binary,char,date,time,datetime,signed,unsigned。2.3.4 其它函数database():返回当前连接的数据库名user():返回当前连接串信息。2.4 过程过程保存在服务端，有很强的灵活性，执行速度较快，减少网络流量。但在实时性的并发会话中不建议使用。多用于统计，测试，监控等任务。2.4.1 示例DELIMITER $CREATE PROCEDURE proc1()  BEGIN SELECT COUNT

12、(*) FROM a;  END $ DELIMITER ; 2.4.2 语法说明· 在后台mysql客户端程序（mysql）窗口中，“；”表示语句结束符，如果是创建过程，需要使用DELIMITER以改变语句结束符，如“$”，最后再改回“；”。· 变量：定义局部变量declare v_i int default 0;赋值：set v_i=0;定义用户变量：set v_i=0; 用户变量必须以开头， 此变量可以在过程内外使用。变量作用域的范围以end结束。· 条件语句：if v_i =0 thenselect 0;e

13、lseif v_i=1 thenselect 1;elseselect null;end if;· 循环1:case v_iwhen 0 thenwhen 1 thenelse.end case;· 循环2：while v_i<6 doselect v_i;set v_i=v_i+1;end while;执行前检查条件· 循环3：repeatselect v_i;set v_i=v_i+1;until v_i>5end repeat;执行后检查条件· 循环3：LOOP_LABLE:loopselect v_i;set v_i=v_i+1;if

14、 v_i>5 thenleave LOOP_LABLE;end if;end loop;· 参数，有IN，OUT，INOUT类型：如create procedure proc1(in p_a int);· 执行过程：call proc1(); 没有参数；call proc1(2); 带有参数；call proc1(v_i); 使用用户变量；· 可以在当前屏幕直接输出结果，这一点比PL/SQL方便。· 注释： - name ; 必须以“-”加空格开始。2.4.3 动态SQL示例：DELIMITER $CREATE PROCEDURE proc2(in

15、 v_i int) BEGIN declare v_ret int ;declare cur_sel cursor for select a from a;declare continue handler for not found set v_ret=1;set v_ret=0;open cur_sel;repeatfetch cur_sel into v_i;select v_i;until v_retend repeat;close cur_sel;END;$ DELIMITER ;2.5 ORACLE到MYSQL模型迁移针对迁移方案，主要从数据模型转换（主要在字段类型，约束属性等）来说

16、明差异要点。2.5.1 数据模型转换 字段类型对照POWERDESIGN自动转换PDM后，ORACLE版本和MYSQL版本的所有数据类型对照关系如下： ORACLE模型mysql模型CHARcharNUMBERNumeric type(int,bigint)DATEdatetimeNVARCHAR2varcharVARCHAR2BLOBlongblobMYSQL版本从节约存储空间的角度，numeric类型可以分成tinyint（占1个字节），smallint（占2个字节），mediumint（占3个字节），int（占4个字节），bigint（占8个字节），而numeric是每个数

17、据占1个字节；日期字段由于业务大多数都会精确到时分秒，选择datetime类型；字符串类型选择varchar；由于BLOB 在ORACLE版本中最大能支持4GB，选择longblob类型。 约束属性Powerdesigner工具转换成MYSQL版本时，约束属性会自动按MYSQL方式创建，有少数差异，见下节说明。2.5.2 差异MYSQL版本数据模型创建需要注意以下要点：· 在19位以下 整数，需要转换成相应的整型（tinyint，smallint，mediumint，int，bigint）来节约存储空间。· 日期类型（DATE）需要指定为datetime。&#

18、183; 注释语句，POWERDESIGNER工具会自动转换成MYSQL版本语句。· PDM中的一些字段（填入序列号值），POWERDESIGNER工具会自动添加auto_increment（类序列号属性）。需要手工去除这种属性，业务采用框架推出的序列号使用方案。· datetime的缺省值是now()，不能使用sysdate()。· 创建主键所指定的数据类型所占存储不能太大，会报错。比如使用varchar(512)来创建组合主键。mysql5.6版本最多不能超过767字节（UTF8字符集3倍指定字段类型）。· 创建索引同主键一样。mysql5.6最多版

19、本不能超过767字节（UTF8字符集3倍指定字段类型）。· 字段的注释不要超过1024个字符（mysql5.6版本）。如果需要超长的注释，建议新建一张描述注释的表· UD用户下单表的较大的varchar字段（如：varchar(2000)）需要转换为text或者blob（每行包括含带有varchar，最大长度不能大于65535字节）。· 特别注意合理设计表索引和主键，这会影响DML语句运行是行锁还是表锁？MYSQL是基于索引条件检索数据时加行锁，否则加表锁（这一点与ORACLE不同）。2.6 常见错误（客户端）ERROR 1040超过最大连接数。需要增加max_c

20、onnections参数的值ERROR 1041内存不足。需要检查配置中的内存设置（特别注意：innodb_buffer_pool_size）。一般不要超过物理内存的75%ERROR 1042无效的主机名。常见的连接方式为：mysql -u test -p -h IP。注意mysql.user表中是否有对应原主机的连接。ERROR 1044数据库用户权限不足。需要管理员增加权限。ERROR 1045数据库服务器/数据库用户名/数据库名/数据库密码错误。分别仔细检查用户名和密码和数据库名是否正确ERROR 1046没有选择数据库。任何SQL操作前需要先选择数据库。客户端连接时使用（use dat

21、abase_name）ERROR 1049数据库不存在ERROR 1050表已经存在。创建的表已经存在ERROR 1051表不存在ERROR 1054字段不存在ERROR 1064不支持的SQL语法。出现此种错误，需要仔细检查语句，错误提示一般会显示错误的位置ERROR 1130没有连接数据库的权限。需要增加权限ERROR 1133数据库用户不存在，需要先创建用户ERROR 1149SQL语法错误ERROR 1205加锁超时。需要查看系统中锁。一般使用show processlist 可以看到运行中的锁表语句ERROR 1264字段溢出报错2.7 常用工具2.7.1 客户端（mysql）工具m

22、ysql可以连接数据库，下面是常用操作。· 新建一个连接： mysql -hlocalhost -uroot -p -P 3306 ;其中test表示打开的数据库。· 直接执行语句：-e “sql”;· 默认是自动提交，通过如下方式：mysql>select autocommit;mysql>set autocommit=0; #关闭自动提交· 查看进程信息:mysql>show processlist;使用help show命令可以查看很多命令信息。 2.7.2 数据库管理工具（mysqladmin）· 停止数据库；mysq

23、ladmin uroot p shutdown· 查看进程信息：mysqladmin uroot p processlist· 杀掉进程：mysqlamdin -uroot -p kill id1,id2,id32.7.3 数据库日志管理(mysqlbinlog)· 查看test数据库的操作日志：mysqlbinlog -d test -s master-bin.000001.· 结果输出到文件：-r out_test.txt· 客户端导入数据: mysql uroot p test <out_test.txt2.7.4 数据导入工具（m

24、ysqlimport） · 导入数据:mysqlimport -uroot -p test -fields-enclosed-by=' -fields-terminated-by=, t_test；-fields-enclosed-by 表示数据的引用符-fields-terminated-by 表示分隔符，默认的分隔符是跳格符（Tab）。t_test的记录如下：1,'1'2,'2'3,32.7.5 数据导出工具(mysqldump)· 导出指定表: mysqldump -n -t -uroot -p -B test -tables

25、t_test (-n 不生成建库语句 -t不生成建表语句 B指定数据库名 tables指定表) · 生成mysqlimport可导入文件：mysqldump -h -P 3306 -uroot -p1 -add-drop-table -opt -q -T ./ test a自动生成文件：a.sql 建表语句 。a.txt文本格式，mysqlimport可以直接使用。2.7.6 错误原因说明（perror）· perror 1045:查看1045的错误信息。2.7.7 文本替换工具(replace)mysql自带对文本中一个或多个字符串进行替换的工具。&#

26、183; 直接替换并覆盖原文件。replace s1 n_s1 s2 n_s2 - file1 file2 #对file1,file中的s1替换成n_s1，s2替换成n_s2。· 替换并显示在标准输出上replace s1 n_s1 s2 n_s2 < file 2.8 应用设计规则以下针对实时性要求高的业务场景。2.8.1 表设计建议· 表必须定义主键，建议取整形。· 不使用外键。· 事务要求高时使用INNODB存储引擎，字符集为utf8。· 表中增加记录创建时间和修改时间字段，方便数据的维护。· 单表数据多，考虑分表。小表比

27、大表在数据处理上有较快的速度。2.8.2 字段设计建议· 定义非空，避免NULL数据，利于数据库的查询优化。· 主键必须非空，并且定义缺省值。· 选择优先级：小整形大整形日期固定长度字符串变长字符串注：参见了“MYSQL常用数据类型”· 浮点选择decimal类型。· 尽量避免使用BLOB和TEXT，选择拆分表（以主键加大数据类型方式）或其它方式存储。2.8.3 索引设计建议· 极小数据的表不添加索引。· 索引字段 不能为空（含复合索引）。· 索引字段的值尽量是重复率尽量低。· 索引字段为常出现在wher

28、e条件的字段名。· 单表索引不易过多，以免增加查询优化时间。· 并发环境高的环境不要使用外键，容易带来死锁。2.8.4 综合开发设计建议以下只对实时性和并发性高的场景。· 禁止在索引列使用函数或数学运算，会导致查询时不读取索引。· 禁止使用“%”作为前缀模糊条件查询数据。· 禁止在条件语句中使用比较不同的数据类型（如数字与字符比较，编写成数字对数字，字符对字符）。· 避免使用字符类型存储数字。· 避免大SQL，拆分成单一SQL。· 避免使用select *。· 避免使用子查询。· 复合索引使用条

29、件语句中必须增加索引的第一个字段。· 索引列尽量避免更新操作。· 避免在服务端进行复杂计算，迁移到客户端处理。· 尽量少使用存储过程和触发器。· count(*)是统计整行的记录，count(col_a)是统计整列非空值的记录。3 数据库管理篇3.1 安装部署3.1.1 单数据库安装 安装程序包服务端： rpm -ivh MySQL-server-5.6.13-1.rhel5.x86_64.rpm客户端： rpm -ivh MySQL-client-5.6.13-1.rhel5.x86_64.rpm 验证mysql -help

30、 启动数据库· 创建主用户groupadd mysqluseradd -g mysql mysql· 创建f文件（注意mysql默认查找的f路径，以避免读取错误配置文件。mysqlduser = mysqlport = 3306basedir = /usrdatadir = /data1/mysql1/3306/datasocket = /data1/mysql1/3306/proc/mysql_3306.sockpid-file= /data1/mysql1/3306/proc/mysql_3306.pidtmpdir = /data1/mysql1/330

31、6/tmp# replserver-id = 1# Logginglog_bin = /data1/mysql1/3306/log/binlog/master_3306_binlog-error = /data1/mysql1/3306/log/error/error_3306.logslow_query_log_file = /data1/mysql1/3306/log/slow/slow_3306.loggeneral_log_file = /data1/mysql1/3306/log/general/general_3306.logrelay_log = /data1/mysql1/33

32、06/log/relay/relay_3306.logrelay_log_info_file = /data1/mysql1/3306/log/relay/relay_log_3306.infoexpire_logs_days=3explicit_defaults_for_timestamp=truecharacter-set-server = utf8init_connect = 'SET NAMES utf8'open_files_limit = 30000max_connections = 5000max_user_connections = 5020autocommit

33、 = 1skip-name-resolvemax_allowed_packet = 64M# InnoDBinnodb_buffer_pool_size = 2Ginnodb_flush_method = O_DIRECTinnodb_thread_concurrency = 64innodb_io_capacity = 8000innodb_read_io_threads = 16innodb_write_io_threads = 16#innodb_change_buffering = insertsinnodb_log_buffer_size = 16Minnodb_log_group_

34、home_dir = /data1/mysql1/3306/innodb/loginnodb_log_files_in_group = 3innodb_log_file_size = 512Minnodb_data_home_dir = /data1/mysql1/3306/innodb/datainnodb_data_file_path = ibdata1:1024M;ibdata2:1024M:autoextendinnodb_open_files = 30000# MyISAMkey_buffer_size = 32M# Otherquery_cache_size = 128Mtmp_t

35、able_size = 256Mmax_heap_table_size = 256Mthread_cache_size = 64bulk_insert_buffer_size = 64Mmax_binlog_cache_size = 256Mmax_binlog_size = 500Mtransaction_isolation = read-committedbinlog_format = rowlower_case_table_names = 1sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLEStable_open_cache = 30

36、262table_definition_cache = 65535connect_timeout =28800net_write_timeout = 300net_read_timeout = 300# Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values

37、.join_buffer_size = 64Ksort_buffer_size = 64Kread_buffer_size = 64Kread_rnd_buffer_size = 64K· 安装数据库：mysql_install_db -basedir=/usr -datadir=/data01/mysql/data -user=mysql -defaults-file=/home/mysql/f · 启动数据库mysqld_safe -defaults-file=/home/mysql/f &· 停止数据库mysqladmin -h -

38、P 3306 -uroot -p shutdown (安装后的初始密码为空)下载服务： http:/ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/3.1.2 主从部署 安装程序包同单数据库安装 主服务安装· 创建f配置文件 （注意替换其中的路径）mysqlddatadir = /data01/mysql/datapid-file=/home/mysql/my_etc/master.pidsocket=/home/mysql/my_etc/master.socklog-error=/data01/mysql/lo

39、g/mysql_error.loglog-bin=/data01/mysql/log/master-binlog-bin-index=/data01/mysql/log/master-bin.indextmpdir=/data01/mysql/tmpdirinnodb_data_home_dir=/data01/mysql/datainnodb_log_group_home_dir=/data01/mysql/logcharacter-set-server=utf8user=mysqlserver_id=1port=3400# master db portbinlog-format=ROWbi

40、nlog-rows-query-log-events=1sync_binlog=1log-slave-updates=truemax_binlog_size=1Gmax_relay_log_size=1Gexpire_logs_days=3binlog_cache_size=1Mgtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1master-verify-checksum=1slave-sql-verify-c

41、hecksum=1lower_case_table_names=1transaction_isolation =read-committedinnodb_file_per_table=oninnodb_buffer_pool_size =256M # buffer poolinnodb_buffer_pool_instances=8innodb_flush_log_at_trx_commit=1innodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextendinnodb_file_io_threads=4innodb_thread_concu

42、rrency=12innodb_additional_mem_pool_size=64Minnodb_log_buffer_size = 8Minnodb_log_file_size=256Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=80innodb_flush_method=O_DIRECTinnodb_autoextend_increment = 128Minnodb_read_io_threads=16innodb_write_io_threads=16innodb_io_capacity=1000innodb_io_ca

43、pacity_max=4000query_cache_type=0max_connect_errors=10000max_connections=2000character-set-server=utf8sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION· 安装数据库：mysql_install_db -basedir=/usr -datadir=/data01/mysql/data -user=mysql -defaults-file=/home/mysql/f·

44、 启动数据库mysqld_safe -defaults-file=/home/mysql/f &· 复制数据库文件打包datadir目录下的文件，复制到从数据库的datadir目录下面。· 登录数据库，创建复制用户mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl_user''%' IDENTIFIED BY 'repl_user'mysql>flush privileges; 从服务安装· 创建f配置文件 （注意替换其中的路径）mysqldd

45、atadir=/data01/mysql/datapid-file=/home/mysql/my_etc/slave.pidsocket=/home/mysql/my_etc/slave.socklog-error=/data01/mysql/log/slave_error.logtmpdir=/data01/mysql/tmpdirlog-bin=/data01/mysql/log/slave-binlog-bin-index=/data01/mysql/log/slave-bin.indexinnodb_data_home_dir=/data01/mysql/datainnodb_log_

46、group_home_dir=/data01/mysql/loguser=mysqlserver_id=1001port=4400report-port=4400binlog-format=ROWbinlog-rows-query-log-events=1sync_binlog=1log-slave-updates=truemax_binlog_size=1Gmax_relay_log_size=1Gexpire_logs_days=3binlog_cache_size=1Mgtid-mode=onenforce-gtid-consistency=truemaster-info-reposit

47、ory=TABLErelay-log-info-repository=TABLEsync-master-info=1master-verify-checksum=1slave-sql-verify-checksum=1read-only=1lower_case_table_names=1transaction_isolation =read-committedinnodb_file_per_table=oninnodb_buffer_pool_size =1024M #innodb_buffer_pool_instances=8innodb_flush_log_at_trx_commit=1i

48、nnodb_data_file_path=ibdata1:100M;ibdata2:100M:autoextendinnodb_file_io_threads=4innodb_thread_concurrency=12innodb_additional_mem_pool_size=64Minnodb_log_buffer_size = 8Minnodb_log_file_size=256Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=80innodb_flush_method=O_DIRECTinnodb_autoextend_in

49、crement = 128Minnodb_read_io_threads=16innodb_write_io_threads=16innodb_io_capacity=1000innodb_io_capacity_max=4000query_cache_type=0max_connect_errors=10000max_connections=2000character-set-server=utf8sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION· 安装数据库：mysql

50、_install_db -basedir=/usr -datadir=/data01/mysql/data -user=mysql -defaults-file=/home/mysql/f· 启动数据库mysqld_safe -defaults-file=/home/mysql/f &· 登录数据库，启动复制功能mysql>CHANGE MASTER TO MASTER_HOST='43', MASTER_PORT=3400, MASTER_USER='repl_user',MASTER_PASSWORD=

51、'repl_user', master_auto_position=1;mysql>start slave;mysql>show slave statusG 出现如下信息表示主从复制成功： Slave_IO_Running: Yes Slave_SQL_Running: Yes3.2 管理工具(WINDOWS )3.2.1 MySQL WorkbenchMYSQL官方工具，可以操作MYSQL，同时可以设计的ER/数据库建模工具。使用它设计和创建新的数据库图示，建立数据库文档，以及进行复杂的MySQL 迁移（最新版本不支持oracle到mysql）。3.2.2 SQL

52、YOG第三方工具（非开源）易于使用的、快速而简洁的图形化管理MYSQL数据库的工具。3.2.3 SQL DEVELOPERORACLE官方工具，支持同时操作 oracle,mysql数据库。3.3 语句优化工具3.3.1 Explain语法：explain select .Select_type:查询类型，主要以下几种值SIMPLE:除子查询或者 union之外的简单查询。UNION： 第二个查询开始的所有select，第一个是PRIMARY；PRIMARY:子查询中的最外层查询，不是主键查询。TYPE：查询方式（以下说明顺序表示性能由好到差）CONST:表中最多会匹配一条记录（如primar

53、y key 或者unique index）。eq_ref:多表查询中使用primary key 或者unique index。ref:多表查询中使用普通索引ref_no_null: 条件中包含对空值的查询。index_merge: 索引合并优化unique_subquery: IN后面是一个查询主键字段的子查询。index_subquery: 后面是一个查询非唯一索引的子查询。rang:查表的范围查询index:索引查询ALL：全表扫描Possible_keys:可以使用的索引，如果是NULL，表示没有使用到索引（但以Key为准）。KEY:实际执行选择的索引，从Possible_keys中选择

54、索引，如果没有是NULL。key_len:索引字段的长度。ROWS:扫描行的数量。EXTRA：执行情况的说明和描述。3.3.2 PROFILEProfiling:查询诊断分析工具，可以知道CPU，IO，SWAP等信息。操作如下：mysql>set profiling=1;mysql>select count(*) from test;mysql>show profile cpu,block io for query 1;3.4 SQL性能检查数据库运行中，定位当前SQL性能分查看进程信息得到运行的SQL，通过慢日志查询得到SQL运行，然后通过前面介绍的explain工具和pr

55、ofile工具查看SQL运行是否高效。3.4.1 查看进程信息show processlist得到当前会话的实时信息，其关键字段说明：ID：连接ID，kill操作使用此值；USER：登录用户；HOST：客户连接主机和端口DB:连接的缺省数据库。COMMAND：当前执行命令：常用如sleep，Query，Connect。TIME：持续的时间。STATE：当前SQL语句执行的状态（常见状态后面介绍） INFO：显示SQL语句（长SQL可能不全）STATE常见状态 列表：State说明Sleep等待客户端发送新请求，当前空闲Updating搜索记录并修改。Creating sort index创建临时表，一般带有order by操作Sending data查询数据，并发送给客户端User sleep客户端发起的sle