技术部ubuntu安装mysql

www.
163.
com网易技术部1深入浅出深入浅出深入浅出深入浅出MySQLMySQLMySQLMySQL数据库开发、优化与管理维护数据库开发、优化与管理维护数据库开发、优化与管理维护数据库开发、优化与管理维护第一篇开发篇.
8第1章帮助的使用.
81.
1按照层次看帮助.
81.
2快速查阅帮助.
10第2章表类型(存储引擎)的选择.
112.
1Mysql存储引擎概述.
112.
2各种存储引擎的特性.
112.
3如何选择合适的存储引擎.
12第3章选择合适的数据类型.
133.
1选择数据类型的基本原则.
133.
2固定长度数据列与可变长度的数据列.
133.
2.
1char与varchar.
133.
2.
2text和blob.
153.
3浮点数与定点数.
15第4章字符集.
164.
1字符集概述.
164.
2Mysql支持的字符集简介.
174.
3Unicode简述.
174.
4怎样选择合适的字符集.
174.
5Mysql字符集的设置.
18第5章索引的设计和使用.
185.
1Mysql索引概述.
185.
2设计索引的原则.
195.
3btree索引与hash索引.
205.
4Mysql如何使用索引.
21第6章锁机制和事务控制.
216.
1如何加锁.
216.
2死锁.
226.
3事务控制.
22www.
163.
com网易技术部2第7章SQL中的安全问题.
297.
1SQL注入简述.
297.
2开发中可以采取的措施.
307.
2.
1prepareStatement+Bind-variable.
307.
2.
2使用应用程序提供的转换函数:317.
2.
3自己定义函数进行校验.
31第8章SQLMode及相关问题.
318.
1MysqlSQLMode简介.
318.
2SQLMode与可移植性.
338.
3SQLMode与数据效验.
34第9章常用SQL技巧.
349.
1检索包含最大/最小值的行.
349.
2巧用rand()/rand(n)提取随机行.
349.
3利用groupby的withrollup子句做统计.
359.
4用bitgroupfunctions做统计.
36第10章其他需注意的问题.
3910.
1数据库名、表名大小写问题.
3910.
2使用外键需注意的地方.
41第二篇优化篇.
45第11章SQL优化.
4511.
1优化SQL的一般步骤.
4511.
1.
1通过showstatus和应用特点了解各种SQL的执行频率.
4511.
1.
2定位执行效率较低的SQL语句:4611.
1.
3通过EXPLAIN分析低效SQL的执行计划:4611.
1.
4确定问题,并采取相应的优化措施:4711.
2索引问题.
4811.
2.
1索引的存储分类.
4810.
2.
2MySQL如何使用索引.
4910.
2.
3查看索引使用情况.
4911.
3两个简单实用的优化方法.
50www.
163.
com网易技术部311.
3.
1定期分析表:5011.
3.
2使用optimizetable:5011.
4常用SQL的优化.
5111.
4.
1大批量插入数据:5111.
4.
2优化insert语句:5211.
4.
3优化groupby语句:5211.
4.
4优化orderby语句:5211.
4.
5优化join语句:5311.
4.
6mysql如何优化or条件:5411.
4.
7查询优先还是更新(insert、update、delete)优先:5411.
4.
8使用SQL提示:5511.
5其他优化措施.
56第12章优化数据库对象.
5612.
1优化表的数据类型.
5612.
2通过拆分,提高表的访问效率.
5712.
3逆规范化.
5712.
4使用冗余统计表.
5712.
5选择更合适的表类型.
58第13章锁问题.
5813.
1获取锁等待情况.
5813.
2什么情况下使用表锁.
5913.
3什么情况下使用行锁.
5913.
4insert…select…带来的问题.
6013.
5next-key锁对并发插入的影响.
6113.
6隔离级别对并发插入的影响.
6113.
7如何减少锁冲突.
62第14章优化MysqlServer.
6314.
1查看Mysqlserver当前参数.
6314.
2影响Mysql性能的重要参数.
6314.
2.
1key_buffer_size的设置.
63www.
163.
com网易技术部414.
2.
2table_cache的设置.
6514.
2.
3innodb_buffer_pool_size的设置:6514.
2.
4innodb_flush_log_at_trx_commit的设置:6514.
2.
5innodb_additional_mem_pool_size:6614.
2.
6innodb_table_locks:6614.
2.
7innodb_lock_wait_timeout:6614.
2.
8innodb_support_xa:6714.
2.
9innodb_doublewrite:6714.
2.
10innodb_log_buffer_size:6714.
2.
11innodb_log_file_size:67第15章I/O问题.
6715.
1使用磁盘阵列或虚拟文件卷分布I/O.
6815.
2使用SymbolicLinks分布I/O.
68第16章应用优化.
6916.
1使用连接池.
6916.
2减少对Mysql的访问.
7016.
2.
1避免对同一数据做重复检索:7016.
2.
2使用mysqlquerycache:7016.
2.
3加cache层:7116.
3负载均衡.
7116.
3.
1利用mysql复制分流查询操作:7116.
3.
2采用分布式数据库架构:71第三篇管理维护篇.
73第17章mysql安装升级.
7317.
1安装.
7317.
1.
1安装方法比较.
7317.
1.
2rpm安装步骤.
7417.
1.
3二进制安装步骤.
7417.
2源码安装步骤.
7517.
3源码安装的性能考虑:75www.
163.
com网易技术部517.
3.
1去掉不需要的模块:7517.
3.
2只选择要使用的字符集:7617.
3.
3使用pgcc编译:7617.
3.
4使用静态编译以提高性能:7717.
4mysql升级.
7717.
5mysql降级.
78第18章Mysql日志管理.
7818.
1错误日志:7818.
2BINLOG:7918.
3查询日志.
8018.
4慢查询日志:80第19章数据备份与恢复:8119.
1备份/恢复策略:8119.
2冷备份:8119.
3逻辑备份:8119.
4单个表的备份:8219.
5使用备份工具ibbackup:8219.
6时间点恢复:8319.
7位置恢复:8419.
8MyISAM表修复:84第20章Mysql安全:8520.
1正确设置目录权限:8520.
2尽量避免以root权限运行mysql:8520.
3删除匿名帐号:8520.
4给mysqlroot帐号设置口令:8620.
5设置安全密码并定期修改:8620.
6只授予帐号必须的权限:8620.
7除root外,任何用户不应有mysql库user表的存取权限:8620.
8不要把FILE、PROCESS或SUPER权限授予管理员以外的帐号:8620.
9loaddatalocal带来的安全问题:87www.
163.
com网易技术部620.
10尽量避免通过symlinks访问表:8820.
11使用merge存储引擎潜藏的安全漏洞:8820.
12防止DNS欺骗:8820.
13droptable命令并不收回以前的相关访问授权:8820.
14使用SSL:8820.
15如果可能,给所有用户加上访问IP限制:9020.
16严格控制操作系统帐号和权限:9020.
17增加防火墙:9020.
18其他安全设置选项:9020.
18.
1allow-suspicious-udfs:9020.
18.
2old-passwords:9020.
18.
3safe-user-create:9120.
18.
4secure-auth:9120.
18.
5skip-grant-tables:9120.
18.
6skip-networking:9120.
18.
7skip-show-database:91第21章Mysql复制:9221.
1Mysql复制概述:9221.
2安装配置:9221.
3日常管理维护:9321.
3.
1经常查看slave状态.
9321.
3.
2怎样强制主服务器阻塞更新直到从服务器同步9421.
3.
3master执行的语句在slave上执行失败怎么办9421.
3.
4Slave上出现logevententryexceededmax_allowed_packet错误怎么办9421.
3.
5多主复制时,自动增长变量的冲突问题.
9521.
3.
6怎么样知道slave上现在复制到什么地方了.
9521.
4需要注意的问题:95第22章MysqlCluster:9522.
1MysqlCluster概述:95www.
163.
com网易技术部722.
2MysqlCluster架构:9622.
3安装配置:9622.
3.
1管理节点配置步骤:9622.
3.
2sql节点和数据节点的配置:9822.
4管理维护:9822.
4.
1Cluster的启动.
9822.
4.
2Cluster的关闭.
9922.
5数据备份和恢复:99第23章Oracle向Mysql数据迁移:10023.
1数据类型的差异:10023.
2利用导出文本迁移:10023.
2.
1导出为insertsql文本.
10023.
2.
2导出为固定格式文本.
10123.
3利用工具软件迁移:10223.
4使用DBA组开发的迁移工具:10223.
5数据迁移常见问题:10323.
5.
1字符集问题:10323.
5.
2特殊字符处理:10323.
5.
3日期字段的处理:10323.
5.
4如何使迁移过程不被SQL错误中断:10423.
5.
5如何查找产生warnings的原因:104第24章应急处理:10424.
1一般处理流程:10424.
2忘记root密码:10524.
3表损坏如何处理:10524.
4MyISAM表超过4G无法访问:10624.
5数据目录磁盘空间不足怎么办10624.
6如何禁止DNS反向解析107第25章Mysql管理中一些常用的命令和技巧:10725.
1参数设置方法:107www.
163.
com网易技术部825.
2mysql.
sock丢失后怎么连接数据库10725.
3同一台机器运行多个mysql:10825.
4查看用户权限:10925.
5修改用户密码:11025.
6怎样灵活的指定连接的主机:11125.
7到底匹配哪个符合条件的用户:11125.
8不进入mysql,怎样运行sql语句11225.
9客户端怎么访问内网数据库113第一篇第一篇第一篇第一篇开发篇开发篇开发篇开发篇第1111章帮助的使用在不同的mysql版本中,很多特性和语法有可能是不一样的,我们怎么样才能知道当前版本的语法是什么样呢最好的办法是学会使用mysql的帮助.
方法很简单:1.
1按照层次看帮助如果不知道帮助能够提供些什么,可以一层一层往下看:命令如下:mysql>mysql>mysql>mysql>contentscontentscontentscontentsYouaskedforhelpabouthelpcategory:"Contents"Formoreinformation,type'help',whereisoneofthefollowingcategories:AccountManagementAdministrationDataDefinitionDataManipulationDataTypeswww.
163.
com网易技术部9FunctionsFunctionsandModifiersforUsewithGROUPBYGeographicFeaturesLanguageStructurePluginsStorageEnginesStoredRoutinesTableMaintenanceTransactionsTriggers对于列出的分类,可以进行看自己感兴趣的部分,例如:mysql>mysql>mysql>mysql>datadatadatadatatypestypestypestypesYouaskedforhelpabouthelpcategory:"DataTypes"Formoreinformation,type'help',whereisoneofthefollowingtopics:AUTO_INCREMENTBIGINTBINARYBITBLOBBLOBDATATYPEBOOLEAN.
.
.
.
.
.
对于列出的具体数据类型,可以进一步看详细情况:mysql>mysql>mysql>mysql>intintintintName:'INT'Description:INT[(M)][UNSIGNED][ZEROFILL]www.
163.
com网易技术部10Anormal-sizeinteger.
Thesignedrangeis-2147483648to2147483647.
Theunsignedrangeis0to4294967295.
1.
2快速查阅帮助实际当中,如果我们需要快速查阅某项语法时,可以使用关键字进行快速查询.
例如,我想知道show命令都能看些什么东西,可以用如下命令:mysql>mysql>mysql>mysql>showshowshowshowName:'SHOW'Description:SHOWhasmanyformsthatprovideinformationaboutdatabases,tables,columns,orstatusinformationabouttheserver.
Thissectiondescribesthosefollowing:SHOWAUTHORSSHOWCHARACTERSET[LIKE'pattern']SHOWCOLLATION[LIKE'pattern']SHOW[FULL]COLUMNSFROMtbl_name[FROMdb_name][LIKE'pattern']SHOWCONTRIBUTORSSHOWCREATEDATABASEdb_nameSHOWCREATEEVENTevent_nameSHOWCREATEFUNCTIONfuncname.
.
.
.
.
.
我想知道createtable的语法,可以命令如下:mysql>mysql>mysql>mysql>createcreatecreatecreatetabletabletabletableName:'CREATETABLE'Description:Syntax:CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_namewww.
163.
com网易技术部11(create_definition,.
.
.
)[table_option.
.
.
][partition_options]Or:CREATE[TEMPORARY]TABLE[IFNOTEXISTS]tbl_name[(create_definition,.
.
.
)][table_option.
.
.
][partition_options]select_statement、.
.
.
.
.
.
第2222章表类型(存储引擎)的选择2.
1Mysql存储引擎概述mysql支持多种存储引擎,在处理不同类型的应用时,可以通过选择使用不同的存储引擎提高应用的效率,或者提供灵活的存储.
mysql的存储引擎包括:MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表.
2.
2各种存储引擎的特性下面我们重点介绍几种常用的存储引擎并对比各个存储引擎之间的区别和推荐使用方式.
特点MyisamBDBMemoryInnoDBArchive存储限制没有没有有64TB没有事务安全支持支持www.
163.
com网易技术部12最常使用的2种存储引擎:1.
Myisam是Mysql的默认存储引擎,当create创建新表时,未指定新表的存储引擎时,默认使用Myisam.
每个MyISAM在磁盘上存储成三个文件.
文件名都和表名相同,扩展名分别是.
frm(存储表定义)、.
MYD(MYData,存储数据)、.
MYI(MYIndex,存储索引).
数据文件和索引文件可以放置在不同的目录,平均分布io,获得更快的速度.
2.
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全.
但是对比Myisam的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引.
2.
3如何选择合适的存储引擎选择标准:根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合.
下面是常用存储引擎的适用环境:1.
MyISAM:默认的MySQL插件式存储引擎,它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一锁机制表锁页锁表锁行锁行锁B树索引支持支持支持支持哈希索引支持支持全文索引支持集群索引支持数据缓存支持支持索引缓存支持支持支持数据可压缩支持支持空间使用低低N/A高非常低内存使用低低中等高低批量插入的速度高高高低非常高支持外键支持www.
163.
com网易技术部132.
InnoDB:用于事务处理应用程序,具有众多特性,包括ACID事务支持.
3.
Memory:将所有数据保存在RAM中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问.
4.
Merge:允许MySQLDBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起,并作为1个对象引用它们.
对于诸如数据仓储等VLDB环境十分适合.
第3333章选择合适的数据类型3.
1选择数据类型的基本原则前提:使用适合存储引擎.
选择原则:根据选定的存储引擎,确定如何选择合适的数据类型下面的选择方法按存储引擎分类:1.
MyISAM数据存储引擎和数据列MyISAM数据表,最好使用固定长度的数据列代替可变长度的数据列.
2.
MEMORY存储引擎和数据列MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系.
两者都是作为CHAR类型处理的.
3.
InnoDB存储引擎和数据列建议使用VARCHAR类型对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),因此在本质上,使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单.
因而,主要的性能因素是数据行使用的存储总量.
由于CHAR平均占用的空间多于VARCHAR,因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的.
www.
163.
com网易技术部143.
2固定长度数据列与可变长度的数据列3.
2.
13.
2.
13.
2.
13.
2.
1charcharcharchar与varcharvarcharvarcharvarcharCHAR和VARCHAR类型类似,但它们保存和检索的方式不同.
它们的最大长度和是否尾部空格被保留等方面也不同.
在存储或检索过程中不进行大小写转换.
下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:请注意上表中最后一行的值只适用不使用严格模式时;如果MySQL运行在严格模式,超过列长度不的值不保存,并且会出现错误.
从CHAR(4)和VARCHAR(4)列检索的值并不总是相同,因为检索时从CHAR列删除了尾部的空格.
通过下面的例子说明该差别:mysql>CREATETABLEvc(vVARCHAR(4),cCHAR(4));QueryOK,0rowsaffected(0.
02sec)mysql>INSERTINTOvcVALUES('ab','ab');QueryOK,1rowaffected(0.
00sec)mysql>SELECTCONCAT(v,'+'),CONCAT(c,'+')FROMvc;|CONCAT(v,CONCAT(c,'+')|值CHAR(4)存储需求VARCHAR(4)存储需求''''4个字节''1个字节'ab''ab'4个字节'ab'3个字节'abcd''abcd'4个字节'abcd'5个字节'abcdefgh''abcd'4个字节'abcd'5个字节www.
163.
com网易技术部15|ab+|ab+|1rowinset(0.
00sec)3.
2.
23.
2.
23.
2.
23.
2.
2texttexttexttext和blobblobblobblob在使用text和blob字段类型时要注意以下几点,以便更好的发挥数据库的性能.
1.
BLOB和TEXT值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候.
删除这种值会在数据表中留下很大的"空洞",以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用OPTIMIZETABLE功能对这类表进行碎片整理.
2.
使用合成的(synthetic)索引.
合成的索引列在某些时候是有用的.
一种办法是根据其它的列的内容建立一个散列值,并把这个值存储在单独的数据列中.
接下来你就可以通过检索散列值找到数据行了.
但是,我们要注意这种技术只能用于精确匹配的查询(散列值对于类似=等范围搜索操作符是没有用处的).
我们可以使用MD5()函数生成散列值,也可以使用SHA1()或CRC32(),或者使用自己的应用程序逻辑来计算散列值.
请记住数值型散列值可以很高效率地存储.
同样,如果散列算法生成的字符串带有尾部空格,就不要把它们存储在CHAR或VARCHAR列中,它们会受到尾部空格去除的影响.
合成的散列索引对于那些BLOB或TEXT数据列特别有用.
用散列标识符值查找的速度比搜索BLOB列本身的速度快很多.
3.
在不必要的时候避免检索大型的BLOB或TEXT值.
例如,SELECT*查询就不是很好的想法,除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行.
否则,你可能毫无目的地在网络上传输大量的值.
这也是BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子.
你可以搜索索引列,决定那些需要的数据行,然后从合格的数据行中检索BLOB或TEXT值.
4.
把BLOB或TEXT列分离到单独的表中.
在某些环境中,如果把这些数据列移动到第二张数据表中,可以让你把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的.
这会减少主表中的碎片,使你得到固定长度数据行的性能优势.
它还使你在主数据表上运行SELECT*查询的时候不会通过网络传输大量的BLOB或TEXT值.
www.
163.
com网易技术部163.
3浮点数与定点数为了能够引起大家的重视,在介绍浮点数与定点数以前先让大家看一个例子:mysql>CREATETABLEtest(c1float(10,2),c2decimal(10,2));QueryOK,0rowsaffected(0.
29sec)mysql>insertintotestvalues(131072.
32,131072.
32);QueryOK,1rowaffected(0.
07sec)mysql>select*fromtest;|c1|c2||131072.
31|131072.
32|1rowinset(0.
00sec)从上面的例子中我们看到c1列的值由131072.
32变成了131072.
31,这就是浮点数的不精确性造成的.
在mysql中float、double(或real)是浮点数,decimal(或numberic)是定点数.
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;它的缺点是会引起精度问题.
在今后关于浮点数和定点数的应用中,大家要记住以下几点:1、浮点数存在误差问题;2、对货币等对精度敏感的数据,应该用定点数表示或存储;3、编程中,如果用到浮点数,要特别注意误差问题,并尽量避免做浮点数比较;4、要注意浮点数中一些特殊值的处理.
第4444章字符集4.
1字符集概述字符集是一套符号和编码的规则,不论是在oracle数据库还是在mysql数据库,都www.
163.
com网易技术部17存在字符集的选择问题,而且如果在数据库创建阶段没有正确选择字符集,那么可能在后期需要更换字符集,而字符集的更换是代价比较高的操作,也存在一定的风险,所以,我们推荐在应用开始阶段,就按照需求正确的选择合适的字符集,避免后期不必要的调整.
4.
2Mysql支持的字符集简介mysql服务器可以支持多种字符集(可以用showcharacterset命令查看所有mysql支持的字符集),在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集,相比oracle等其他数据库管理系统,在同一个数据库只能使用相同的字符集,mysql明显存在更大的灵活性.
mysql的字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念.
字符集是用来定义mysql存储字符串的方式,校对规则则是定义了比较字符串的方式.
字符集和校对规则是一对多的关系,MySQL支持30多种字符集的70多种校对规则.
每个字符集至少对应一个校对规则.
可以用SHOWCOLLATIONLIKE'utf8%';命令查看相关字符集的校对规则.
4.
3Unicode简述Unicode是一种编码规范.
我们在这里简述一下Unicode编码产生的历史.
先从ASCII码说起,ASCII码也是一种编码规范,只不过ASCII码只能最多表示256个字符,是针对英文产生的,而面对中文、阿拉伯文之类的复杂文字,256个字符显然是不够用的.
于是各个国家或组织都相继制定了符合自己语言文字的标准,比如gb2312、big5等等.
但是这种各自制定自己的标准的做法显然是有很多弊端的,于是Unicode编码规范应运而生.
Unicode也是一种字符编码方法,不过它是由国际组织设计,可以容纳全世界所有语言文字的编码方案.
Unicode的学名是"UniversalMultiple-OctetCodedCharacterSet",简称为UCS.
UCS可以看作是"UnicodeCharacterSet"的缩写.
Unicode有两套标准UCS-2和UCS-4,前者用2个字节表示一个字符,后者用4个字节表示一个字符.
以目前常用的UCS-2为例,它可以表示的字符数为2^16=65535,基本上可以容纳所有的欧美字符和绝大多数亚洲字符.
www.
163.
com网易技术部184.
4怎样选择合适的字符集我们建议在能够完全满足应用的前提下,尽量使用小的字符集.
因为更小的字符集意味着能够节省空间、减少网络传输字节数,同时由于存储空间的较小间接的提高了系统的性能.
有很多字符集可以保存汉字,比如utf8、gb2312、gbk、latin1等等,但是常用的是gb2312和gbk.
因为gb2312字库比gbk字库小,有些偏僻字(例如:洺)不能保存,因此在选择字符集的时候一定要权衡这些偏僻字在应用出现的几率以及造成的影响,不能做出肯定答复的话最好选用gbk.
4.
5Mysql字符集的设置mysql的字符集和校对规则有4个级别的默认设置:服务器级、数据库级、表级和字段级.
分别在不同的地方设置,作用也不相同.
服务器字符集和校对,在mysql服务启动的时候确定.
可以在my.
cnf中设置:[mysqld]default-character-set=utf8或者在启动选项中指定:mysqld--default-character-set=utf8或者在编译的时候指定:.
/configure--with-charset=utf8如果没有特别的指定服务器字符集,默认使用latin1作为服务器字符集.
上面三种设置的方式都只指定了字符集,没有指定校对规则,这样是使用该字符集默认的校对规则,如果要使用该字符集的非默认校对规则,则需要在指定字符集的同时指定校对规则.
可以用showvariableslike'character_set_server';命令查询当前服务器的字符集和校对规则.
www.
163.
com网易技术部19第5555章索引的设计和使用5.
1Mysql索引概述所有MySQL列类型可以被索引.
对相关列使用索引是提高SELECT操作性能的最佳途径.
根据存储引擎定义每个表的最大索引数和最大索引长度.
所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节.
大多数存储引擎有更高的限制.
在MySQL5.
1中,对于MyISAM和InnoDB表,前缀可以达到1000字节长.
请注意前缀的限制应以字节为单位进行测量,而CREATETABLE语句中的前缀长度解释为字符数.
当为使用多字节字符集的列指定前缀长度时一定要加以考虑.
还可以创建FULLTEXT索引.
该索引可以用于全文搜索.
只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列.
索引总是对整个列进行,不支持局部(前缀)索引.
也可以为空间列类型创建索引.
只有MyISAM存储引擎支持空间类型.
空间索引使用R-树.
默认情况MEMORY(HEAP)存储引擎使用hash索引,但也支持B-树索引.
5.
2设计索引的原则1.
搜索的索引列,不一定是所要选择的列.
换句话说,最适合索引的列是出现在WHERE子句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列.
2.
使用惟一索引.
考虑某列中值的分布.
对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差.
例如,存放年龄的列具有不同值,很容易区分各行.
而用来记录性别的列,只含有"M"和"F",则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)3.
使用短索引.
如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做.
例如,如果有一个CHAR(200)列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引.
对前10个或20个字符进行索引能够节省大量索引空间,也可能会使查询更快.
较小的索引涉及的磁盘I/O较少,较短的值比较起来更快.
更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值.
这增加了找到行而不用读取索引中较多块的可能性.
(当然,应该利用一些常识.
如仅用列值的第一个字符进行索引是不可能有多大好处的,www.
163.
com网易技术部20因为这个索引中不会有许多不同的值.
)4.
利用最左前缀.
在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引.
多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行.
这样的列集称为最左前缀.
(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n个字符作为索引值.
)5.
不要过度索引.
不要以为索引"越多越好",什么东西都用索引是错的.
每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过.
在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长.
如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度.
此外,MySQL在生成一个执行计划时,要考虑各个索引,这也要费时间.
创建多余的索引给查询优化带来了更多的工作.
索引太多,也可能会使MySQL选择不到所要使用的最好索引.
只保持所需的索引有利于查询优化.
如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引.
如果是,则就不要费力去增加这个索引了,因为已经有了.
6.
考虑在列上进行的比较类型.
索引可用于""和BETWEEN运算.
在模式具有一个直接量前缀时,索引也用于LIKE运算.
如果只将某个列用于其他类型的运算时(如STRCMP()),对其进行索引没有价值.
5.
3btree索引与hash索引对于BTREE和HASH索引,当使用=、、IN、ISNULL或者ISNOTNULL操作符时,关键元素与常量值的比较关系对应一个范围条件.
Hash索引还有一些其它特征:它们只用于使用=或操作符的等式比较(但很快).
优化器不能使用hash索引来加速ORDERBY操作.
(该类索引不能用来按顺序搜索下一个条目).
MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引).
如果你将一个MyISAM表改为hash-索引的MEMORY表,会影响一些查询.
只能使用整个关键字来搜索一行.
(用B-树索引,任何关键字的最左面的前缀可用来找到行).
对于BTREE索引,当使用>BETWEEN、!
=或者,或者LIKE'pattern'(其中'pattern'不以通配符开始)操作符时,关键元素与常量值的比较关系对应一个范围条件.
www.
163.
com网易技术部21"常量值"系指:查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式下面是一些WHERE子句中有范围条件的查询的例子:下列范围查询适用于btree索引和hash索引SELECT*FROMt1WHEREkey_col=1ORkey_colIN(15,18,20);下列范围查询适用于btree索引SELECT*FROMt1WHEREkey_col>1ANDkey_colmysql>select*fromtt3;Emptyset(0.
00sec)mysql>select*fromtt3;Emptyset(0.
00sec)mysql>starttransaction;QueryOK,0rowsaffected(0.
00sec)mysql>insertintott3values('1',1);QueryOK,1rowaffected(0.
03sec)mysql>select*fromtt3;Emptyset(0.
00sec)mysql>commit;QueryOK,0rowsaffected(0.
05sec)www.
163.
com网易技术部24mysql>select*fromtt3;|id|name||1|1.
00|1rowinset(0.
00sec)mysql>insertintott3values('2',2);QueryOK,1rowaffected(0.
04sec)这个事务是按照自动提交执行的mysql>select*fromtt3;|id|name||1|1.
00||2|2.
00|2rowsinset(0.
00sec)www.
163.
com网易技术部25mysql>starttransaction;QueryOK,0rowsaffected(0.
00sec)mysql>insertintott3values('3',3);QueryOK,1rowaffected(0.
00sec)mysql>commitandchain;QueryOK,0rowsaffected(0.
05sec)自动开始一个新的事务mysql>insertintott3values('4',4);QueryOK,1rowaffected(0.
00sec)mysql>select*fromtt3;|id|name||1|1.
00||2|2.
00||3|3.
00|3rowsinset(0.
00sec)mysql>commit;QueryOK,0rowsaffected(0.
06sec)www.
163.
com网易技术部26开始一个事务,会造成一个隐含的unlocktables被执行:mysql>select*fromtt3;|id|name||1|1.
00||2|2.
00||3|3.
00||4|4.
00|4rowsinset(0.
00sec)timesession_1session_2--------------------------------------------------------->mysql>select*fromtt3;Emptyset(16.
65sec)mysql>select*fromtt3;Emptyset(16.
65sec)mysql>locktablett3write;QueryOK,0rowsaffected(0.
00sec)mysql>select*fromtt3;等待mysql>insertintott3values('1',1);QueryOK,1rowaffected(0.
07sec)等待mysql>rollback;QueryOK,0rowsaffected(0.
00sec)等待www.
163.
com网易技术部27因此,在同一个事务中,最好不使用不同存储引擎的表,否则rollback时需要对非事务类型的表进行特别的处理,因为commit、rollback只能对事务类型的表进行提交和回滚.
通常情况下,只对提交的事务纪录到二进制的日志中,但是如果一个事务中包含非事务类型的表,那么回滚操作也会被记录到二进制日志中,以确保非事务类型表的更新可以被复制到从的数据库中.
和oracle的事务管理相同,所有的DDL语句是不能回滚的,并且部分的DDL语句会造成隐式的提交.
在事务中可以通过定义savepoint,指定回滚事务的一个部分,但是不能指定提交事务的一个部分.
对于复杂的应用,可以定义多个不同的savepoint,满足不同的条件时,回滚不同的savepoint.
需要注意的是,如果定义了相同名字的savepoint,则后面定义的savepoint会覆盖之前的定义.
对于不再需要使用的savepoint,可以通过releasesavepoint命令删除savepoint,删除后的savepoint,不能再执行rollbacktosavepoint命令.
下面我们例子就是模拟回滚事务的一个部分,通过定义savepoint来指定需要回滚的事务的位置.
mysql>starttransaction;QueryOK,0rowsaffected(0.
00sec)等待mysql>select*fromtt3;|id|name||1|1.
00|1rowinset(37.
71sec)开始一个事务时,表锁被释放.
对lock方式加的表锁,不能通过rollback进行回滚.
www.
163.
com网易技术部28timesession_1session_2--------------------------------------------------------->mysql>select*fromtt3;|id|name||2|2.
00||3|3.
00||4|4.
00|3rowsinset(0.
00sec)mysql>select*fromtt3;|id|name||2|2.
00||3|3.
00||4|4.
00|3rowsinset(0.
00sec)mysql>starttransaction;QueryOK,0rowsaffected(0.
00sec)mysql>deletefromtt3whereid='2';QueryOK,1rowaffected(0.
00sec)mysql>select*fromtt3;|id|name||3|3.
00||4|4.
00|3rowsinset(0.
00sec)mysql>select*fromtt3;|id|name||2|2.
00||3|3.
00||4|4.
00|3rowsinset(0.
00sec)www.
163.
com网易技术部29mysql>savepointtest;QueryOK,0rowsaffected(0.
00sec)mysql>deletefromtt3whereid='3';QueryOK,1rowaffected(0.
00sec)mysql>select*fromtt3;|id|name||4|4.
00|3rowsinset(0.
00sec)mysql>select*fromtt3;|id|name||2|2.
00||3|3.
00||4|4.
00|3rowsinset(0.
00sec)mysql>rollbacktosavepointtest;QueryOK,0rowsaffected(0.
00sec)mysql>select*fromtt3;|id|name||3|3.
00||4|4.
00|2rowsinset(0.
00sec)mysql>select*fromtt3;|id|name||2|2.
00||3|3.
00||4|4.
00|3rowsinset(0.
00sec)www.
163.
com网易技术部30第7777章SQLSQLSQLSQL中的安全问题7.
1SQL注入简述SQLInjection攻击具有很大的危害,攻击者可以利用它读取、修改或者删除数据库内的数据,获取数据库中的用户名和密码等敏感信息,甚至可以获得数据库管理员的权限.
如果能够再利用SQLServer扩展存储过程和自定义扩展存储过程来执行一些系统命令,攻击者还可以获得该系统的控制权.
而且,SQLInjection也很难防范.
网站管理员无法通过安装系统补丁或者进行简单的安全配置进行自我保护,一般的防火墙也无法拦截SQLInjection攻击.
SQLInjection原理:结构化查询语言(SQL)是一种用来和数据库交互的文本语言.
SQLInjection就是利用某些数据库的外部接口把用户数据插入到实际的数据库操作语言(SQL)当中,从而达到入侵数据库乃至操作系统的目的.
它的产生主要是由于程序对用户输入的数据没有进行严格的过滤,导致非法数据库查询语句的执行.
如下面的用户登陆验证程序:$sql="SELECT*FROMuserWHEREusername='$username'ANDpassword='$password'";mysql>commit;QueryOK,0rowsaffected(0.
05sec)mysql>select*fromtt3;|id|name||3|3.
00||4|4.
00|2rowsinset(0.
00sec)mysql>select*fromtt3;|id|name||3|3.
00||4|4.
00|2rowsinset(0.
00sec)www.
163.
com网易技术部31$result=mysql_db_query($dbname,$sql);如果我们提交如下url:http://127.
0.
0.
1/injection/user.
phpusername=angel'or'1=1那么就可以成功登陆系统,但是很显然这并不是我们预期的,同样我们也可以利用sql的注释语句实现sql注入,如下面的例子:http://127.
0.
0.
1/injection/user.
phpusername=angel'/*http://127.
0.
0.
1/injection/user.
phpusername=angel'%23这样就把后面的语句给注释掉了!
说说这两种提交的不同之处,我们提交的第一句是利用逻辑运算,第二、三句是根据mysql的特性,mysql支持/*和#两种注释格式,所以我们提交的时候是把后面的代码注释掉,值得注意的是由于编码问题,在IE地址栏里提交#会变成空的,所以我们在地址栏提交的时候,应该提交%23,才会变成#,就成功注释了,这个比逻辑运算简单得多了.
7.
2开发中可以采取的措施7.
2.
17.
2.
17.
2.
17.
2.
1prepareStatementprepareStatementprepareStatementprepareStatement++++Bind-variableBind-variableBind-variableBind-variable对Java,Jsp开发的应用,可以使用prepareStatement+Bind-variable来防止sql注入,另外从PHP5开始,也在扩展的mysqli中支持preparedstatements,所以在使用这类语言作数据库开发时,强烈建议使用prepareStatement+Bind-variable来实现,而尽量不要使用拼接的sql.
7.
2.
27.
2.
27.
2.
27.
2.
2使用应用程序提供的转换函数:很多应用程序接口都提供了对特殊的字符进行转换的函数,恰当的使用这些函数,可以防止应用程序用户输入使应用程序生成不期望的效果的语句的数值.
MySQLCAPI:使用mysql_real_escape_string()API调用.
MySQL++:使用escape和quote修饰符.
PHP:使用mysql_real_escape_string()函数(适用于PHP4.
3.
0,之前的版本请使用mysql_escape_string(),PHP4.
0.
3之前的版本请使用addslashes()).
从PHP5开始,可以使用扩展的mysqli,这是对MYSQL新特性的一个扩展支持,其中的一个优点就是支持preparedstatements.
www.
163.
com网易技术部32PerlDBI:使用placeholders或者quote()方法.
RubyDBI:使用placeholders或者quote()方法.
JavaJDBC:使用PreparedStatement和placeholders.
7.
2.
37.
2.
37.
2.
37.
2.
3自己定义函数进行校验如果现有的转换函数仍然不能满足要求,则需要自己编写函数进行输入校验.
输入验证是一个很复杂的问题.
输入验证的途径可以分为以下几种:整理数据使之变得有效;拒绝已知的非法输入;只接受已知的合法的输入.
所以如果想要获得最好的安全状态,目前最好的解决办法就是对用户提交或者可能改变的数据进行简单分类,分别应用正则表达式来对用户提供的输入数据进行严格的检测和验证.
第8888章SQLSQLSQLSQLModeModeModeMode及相关问题8.
1MysqlSQLMode简介MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式.
这样,应用程序就能对服务器操作进行量身定制以满足自己的需求.
这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查.
这样,就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL.
可以使用"--sql-mode="modes""选项,通过启动mysqld来设置默认的SQL模式.
从MySQL4.
1开始,也能在启动之后,使用SET[SESSION|GLOBAL]sql_mode='modes'语句,通过设置sql_mode变量更改模式.
通常在linux下安装完mysql后,默认的sql-mode值是空,在这种情形下mysql执行的是一种不严格的检查,例如日期字段可以插入'0000-00-0000:00:00'这样的值,还有如果要插入的字段长度超过列定义的长度,那么mysql不会终止操作,而是会自动截断后面的字符继续插入操作,如下例:www.
163.
com网易技术部33mysql>createtablet5(c1char(3));mysql>insertintot5values('abcd');mysql>select*fromt5;+------+|c1|+------+|abc|+------+1rowinset(0.
00sec)我们发现插入的字符被自动截断了,但是如果我们本意希望如果长度超过限制就报错,那么我们可以设置sql_mode为STRICT_TRANS_TABLES,如下:mysql>setsessionsql_mode='STRICT_TRANS_TABLES'这样我们再执行同样的操作,mysql就会告诉我们插入的值太长,操作被终止,如下:mysql>insertintot5values('abcd');ERROR1406(22001):Datatoolongforcolumn'c1'atrow1经常使用的sql_mode值如下表:说明:如果把sql_mode的值设置成后面的两个值(也就是我们说的严格模式),那么当在列中插入或更新不正确的值时,mysql将会给出错误,并且放弃insert/update操作.
在我们的一般应用中建议使用这两种模式,而不是使用默认的空或ANSI模式.
但是需要注意的问题是,如果数据库运行在严格模式下,并且你的存储引擎不支持事务,那么有数据不一致的风Sql_mode值描述ANSI更改语法和行为,使其更符合标准SQL.
STRICT_TRANS_TABLES如果不能将给定的值插入到事务表中,则放弃该语句.
对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句.
本节后面给出了更详细的描述.
TRADITIONALMakeMySQL的行为象"传统"SQL数据库系统.
该模式的简单描述是当在列中插入不正确的值时"给出错误而不是警告".
注释:一旦发现错误立即放弃INSERT/UPDATE.
如果你使用非事务存储引擎,这种方式不是你想要的,因为出现错误前进行的数据更改不会"滚动",结果是更新"只进行了一部分".
www.
163.
com网易技术部34险存在,比如一组sql中有两个dml语句,如果后面的一个出现了问题,但是前面的已经操作成功,那么mysql并不能回滚前面的操作.
因此说设置sql_mode需要应用人员权衡各种得失,从而得到一个合适的选择.
Sql_mode的值还有很多,这里不再累述,可以参考相关的手册.
8.
2SQLMode与可移植性如果mysql与其它异构数据库之间有数据移植的需求的话,那么下面的sql_mode的组合设置可以达到相应的效果:8.
3SQLMode与数据效验SQLMode还可以实现对数据效验和转移等功能如:·效验日期数据合法性.
·在INSERT或UPDATE过程中,如果被零除(或MOD(X,0)),则产生错误·将'"'视为识别符引号('`'引号字符)·禁用反斜线字符('\')做为字符串内的退出字符.
启用NO_BACKSLASH_ESCAPES模式,反斜线则成为普通字符.
·将||视为字符串连接操作符(+)(同CONCAT()),而不视为OR.
数据库Sql_mode值DB2PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSMAXDBPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USERMSSQLPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSORACLEPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USERPOSTGRESQLPIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONSwww.
163.
com网易技术部35第9999章常用SQLSQLSQLSQL技巧9.
1检索包含最大/最小值的行MIN([DISTINCT]expr),MAX([DISTINCT]expr)返回expr的最小值和最大值.
MIN()和MAX()的取值可以是一个字符串参数;在这些情况下,它们返回最小或最大字符串值.
DISTINCT关键词可以被用来查找expr的不同值的最小或最大值,然而,这产生的结果与省略DISTINCT的结果相同.
若找不到匹配的行,MIN()和MAX()返回NULL.
mysql>SELECTstudent_name,MIN(test_score),MAX(test_score)->FROMstudent->GROUPBYstudent_name;9.
2巧用rand()/rand(n)提取随机行可按照如下的随机顺序检索数据行,如下:mysql>SELECT*FROMtbl_nameORDERBYRAND();ORDERBYRAND()同LIMIT的结合可以从一组列中选择随机样本,如下:mysql>SELECT*FROMtable1,table2WHEREa=bANDcFROMstudent->GROUPBYstudent_name;在本小节,主要介绍groupby语句和bit_and,bit_or函数共同使用完成统计工作,先来了解一下bit_and和bit_or函数,举例如下:mysql>CREATETABLE`ta`(->`id`smallint(5)unsignedNOTNULLdefault'0',->KEY`id`(`id`)->)TYPE=MyISAM;QueryOK,0rowsaffected(0.
01sec)mysql>INSERTINTO`ta`VALUES("1"),("2"),("3"),("4");QueryOK,8rowsaffected(0.
00sec)Records:4Duplicates:0Warnings:0mysql>SELECTBIT_OR(id)fromta;|BIT_OR(id)||7|www.
163.
com网易技术部381rowinset(0.
00sec)#.
.
0001#.
.
0010#.
.
0011#.
.
0100#OR.
.
0000#.
.
0111mysql>SELECTBIT_AND(id)fromta;|BIT_AND(id)||0|1rowinset(0.
00sec)#.
.
0001#.
.
0010#.
.
0011#.
.
0100#AND.
.
1111#.
.
0000熟悉了bit_and和bit_or后我们一起来学习一下bit_and,bit_or和groupby函数共同使用进行统计工作.
针对上面的表ta我们增加字段cust_type并按这个字段的值进行分类统计altertabletaaddcolumncust_typevarchar(100);updatetasetcust_type='2'whereid>3;updatetasetcust_type='1'wherecust_typeisnull;mysql>SELECTcust_type,BIT_OR(id)fromtagroupbycust_type;www.
163.
com网易技术部39|cust_type|BIT_OR(id)||1|3||2|4|2rowsinset(0.
00sec)mysql>SELECTcust_type,BIT_and(id)fromtagroupbycust_type;|cust_type|BIT_and(id)||1|0||2|4|2rowsinset(0.
00sec)BIT_AND(expr)返回expr中所有比特的bitwiseAND.
计算执行的精确度为64比特(BIGINT).
若找不到匹配的行,则这个函数返回18446744073709551615.
(这是无符号BIGINT值,所有比特被设置为1).
BIT_OR(expr)返回expr中所有比特的bitwiseOR.
计算执行的精确度为64比特(BIGINT).
若找不到匹配的行,则函数返回0.
BIT_XOR(expr)www.
163.
com网易技术部40返回expr中所有比特的bitwiseXOR.
计算执行的精确度为64比特(BIGINT).
若找不到匹配的行,则函数返回0.
第10101010章其他需注意的问题10.
1数据库名、表名大小写问题在MySQL中,数据库对应数据目录中的目录.
数据库中的每个表至少对应数据库目录中的一个文件(也可能是多个,取决于存储引擎).
因此,所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性.
这说明在大多数Unix中数据库名和表名对大小写敏感,而在Windows中对大小写不敏感.
一个显著的例外情况是MacOSX,它基于Unix但使用默认文件系统类型(HFS+),对大小写不敏感.
然而,MacOSX也支持UFS卷,该卷对大小写敏感,就像Unix一样.
注释:尽管在某些平台中数据库名和表名对大小写不敏感,不应在同一查询中使用不同的大小写来引用给定的数据库或表.
下面的查询不会工作,因为它同时引用了表my_tables和asMY_tables:mysql>SELECT*FROMmy_tableWHEREMY_TABLE.
col=1;列、索引、存储子程序和触发器名在任何平台上对大小写不敏感,列的别名也不敏感.
默认情况,表别名在Unix中对大小写敏感,但在Windows或MacOSX中对大小写不敏感.
下面的查询在Unix中不会工作,因为它同时引用了别名a和A:mysql>SELECTcol_nameFROMtbl_nameASa->WHEREa.
col_name=1ORA.
col_name=2;然而,该查询在Windows中是可以的.
要想避免出现差别,最好采用一致的转换,例如总是用小写创建并引用数据库名和表名.
在大多数移植和使用中建议使用该转换.
www.
163.
com网易技术部41在MySQL中如何在硬盘上保存和使用表名和数据库名由lower_case_tables_name系统变量确定,可以在启动mysqld时设置.
lower_case_tables_name可以采用下面的任一值:在Windows和MacOSX中,lower_case_tables_name的默认值是1.
如果只在一个平台上使用MySQL,通常不需要更改lower_case_tables_name变量.
然而,如果你想要在对大小写敏感不同的文件系统的平台之间转移表,会遇到困难.
例如,在Unix中,my_tables和MY_tables是两个不同的表,但在Windows中,这两个表名相同.
要想避免由于数据库或表名的大小写造成的数据转移问题,可使用两个选项:在任何系统中可以使用lower_case_tables_name=1.
使用该选项的不利之处是当使用SHOWTABLES或SHOWDATABASES时,看不出名字原来是用大写还是小写.
在Unix中使用lower_case_tables_name=0,在Windows中使用lower_case_tables_name=2.
这样了可以保留数据库名和表名的大小写.
不利之处是必须确保在Windows中查询总是用正确大小写引用数据库名和表名.
如果将查询转移到Unix中,由于在Unix中大小写很重要,如果大小写不正确,它们不工作.
值含义0使用CREATETABLE或CREATEDATABASE语句指定的大写和小写在硬盘上保存表名和数据库名.
名称比较对大小写敏感.
在Unix系统中的默认设置即如此.
请注意如果在大小写不敏感的文件系统上用--lower-case-table-names=0强制设为0,并且使用不同的大小写访问MyISAM表名,会导致索引破坏.
1表名在硬盘上以小写保存,名称比较对大小写敏感.
MySQL将所有表名转换为小写以便存储和查找.
该行为也适合数据库名和表的别名.
该值为Windows和MacOSX系统中的默认值.
2表名和数据库名在硬盘上使用CREATETABLE或CREATEDATABASE语句指定的大小写进行保存,但MySQL将它们转换为小写以便查找.
名称比较对大小写敏感.
注释:只在对大小写不敏感的文件系统上适用!
InnoDB表名以小写保存,例如lower_case_tables_name=1.
www.
163.
com网易技术部42例外:如果你正使用InnoDB表,在任何平台上均应将lower_case_tables_name设置为1,以强制将名转换为小写.
请注意在Unix中将lower_case_tables_name设置为1之前,重启mysqld之前,必须先将旧的数据库名和表名转换为小写10.
2使用外键需注意的地方在MySQL中,InnoDB表支持对外部关键字约束条件的检查.
对于除InnoDB类型的表,当使用REFERENCEStbl_name(col_name)子句定义列时可以使用外部关键字,该子句没有实际的效果,只作为备忘录或注释来提醒,你目前正定义的列指向另一个表中的一个列.
执行该语句时,实现下面很重要:MySQL不执行表tbl_name中的动作,例如作为你正定义的表中的行的动作的响应而删除行;换句话说,该句法不会致使ONDELETE或ONUPDATE行为(如果你在REFERENCES子句中写入ONDELETE或ONUPDATE子句,将被忽略).
该句法可以创建一个column;但不创建任何索引或关键字.
如果用该句法定义InnoDB表,将会导致错误.
你可以使用作为联接列创建的列,如下所示:CREATETABLEperson(idSMALLINTUNSIGNEDNOTNULLAUTO_INCREMENT,nameCHAR(60)NOTNULL,PRIMARYKEY(id));CREATETABLEshirt(idSMALLINTUNSIGNEDNOTNULLAUTO_INCREMENT,styleENUM('t-shirt','polo','dress')NOTNULL,www.
163.
com网易技术部43colorENUM('red','blue','orange','white','black')NOTNULL,ownerSMALLINTUNSIGNEDNOTNULLREFERENCESperson(id),PRIMARYKEY(id));INSERTINTOpersonVALUES(NULL,'AntonioPaz');SELECT@last:=LAST_INSERT_ID();INSERTINTOshirtVALUES(NULL,'polo','blue',@last),(NULL,'dress','white',@last),(NULL,'t-shirt','blue',@last);INSERTINTOpersonVALUES(NULL,'LillianaAngelovska');SELECT@last:=LAST_INSERT_ID();INSERTINTOshirtVALUES(NULL,'dress','orange',@last),(NULL,'polo','red',@last),(NULL,'dress','blue',@last),(NULL,'t-shirt','white',@last);SELECT*FROMperson;|id|name||1|AntonioPaz||2|LillianaAngelovska|www.
163.
com网易技术部44SELECT*FROMshirt;|id|style|color|owner||1|polo|blue|1||2|dress|white|1||3|t-shirt|blue|1||4|dress|orange|2||5|polo|red|2||6|dress|blue|2||7|t-shirt|white|2|SELECTs.
*FROMpersonp,shirtsWHEREp.
nameLIKE'Lilliana%'ANDs.
owner=p.
idANDs.
color'white';|id|style|color|owner||4|dress|orange|2||5|polo|red|2||6|dress|blue|2|按照这种方式使用,REFERENCES子句不会显示在SHOWCREATETABLE或DESCRIBE的输出中:www.
163.
com网易技术部45SHOWCREATETABLEshirt\G1.
rowTable:shirtCreateTable:CREATETABLE`shirt`(`id`smallint(5)unsignedNOTNULLauto_increment,`style`enum('t-shirt','polo','dress')NOTNULL,`color`enum('red','blue','orange','white','black')NOTNULL,`owner`smallint(5)unsignedNOTNULL,PRIMARYKEY(`id`))ENGINE=MyISAMDEFAULTCHARSET=latin1在列定义中,按这种方式使用REFERENCES作为注释或"提示"适用于表MyISAM和BerkeleyDB.
www.
163.
com网易技术部46第二篇第二篇第二篇第二篇优化篇优化篇优化篇优化篇第11111111章SQLSQLSQLSQL优化11.
1优化SQL的一般步骤11.
1.
111.
1.
111.
1.
111.
1.
1通过showshowshowshowstatusstatusstatusstatus和应用特点了解各种SQLSQLSQLSQL的执行频率通过SHOWSTATUS可以提供服务器状态信息,也可以使用mysqladminextended-status命令获得.
SHOWSTATUS可以根据需要显示session级别的统计结果和global级别的统计结果.
以下几个参数对Myisam和Innodb存储引擎都计数:1.
Com_select执行select操作的次数,一次查询只累加1;2.
Com_insert执行insert操作的次数,对于批量插入的insert操作,只累加一次;3.
Com_update执行update操作的次数;4.
Com_delete执行delete操作的次数;以下几个参数是针对Innodb存储引擎计数的,累加的算法也略有不同:1.
Innodb_rows_readselect查询返回的行数;2.
Innodb_rows_inserted执行Insert操作插入的行数;3.
Innodb_rows_updated执行update操作更新的行数;4.
Innodb_rows_deleted执行delete操作删除的行数;通过以上几个参数,可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主,以及各种类型的SQL大致的执行比例是多少.
对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会累加.
对于事务型的应用,通过Com_commit和Com_rollback可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题.
此外,以下几个参数便于我们了解数据库的基本情况:www.
163.
com网易技术部471.
Connections试图连接Mysql服务器的次数2.
Uptime服务器工作时间3.
Slow_queries慢查询的次数11.
1.
211.
1.
211.
1.
211.
1.
2定位执行效率较低的SQLSQLSQLSQL语句:可以通过以下两种方式定位执行效率较低的SQL语句:1.
可以通过慢查询日志定位那些执行效率较低的sql语句,用--log-slow-queries[=file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.
可以链接到管理维护中的相关章节.
2.
慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用showprocesslist命令查看当前MySQL在进行的线程,包括线程的状态,是否锁表等等,可以实时的查看SQL执行情况,同时对一些锁表操作进行优化.
11.
1.
311.
1.
311.
1.
311.
1.
3通过EXPLAINEXPLAINEXPLAINEXPLAIN分析低效SQLSQLSQLSQL的执行计划:通过以上步骤查询到效率低的SQL后,我们可以通过explain或者desc获取MySQL如何执行SELECT语句的信息,包括select语句执行过程表如何连接和连接的次序.
explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT.
mysql>explainselectsum(moneys)fromsalesa,companysbwherea.
company_id=b.
idanda.
year=2006;|select_type|table|type|possible_keys|key|key_len|rows|Extra|www.
163.
com网易技术部48|SIMPLE|b|index|PRIMARY|PRIMARY|4|1|Usingindex||SIMPLE|a|ALL|NULL|NULL|NULL|12|Usingwhere|2rowsinset(0.
02sec)select_type:select类型table:输出结果集的表type:表示表的连接类型当表中仅有一行是type的值为system是最佳的连接类型;当select操作中使用索引进行表连接时type的值为ref;当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率.
possible_keys:表示查询时,可以使用的索引列.
key:表示使用的索引key_len:索引长度rows:扫描范围Extra:执行情况的说明和描述11.
1.
411.
1.
411.
1.
411.
1.
4确定问题,并采取相应的优化措施:经过以上步骤,基本可以确认问题出现的原因,可以根据情况采取相应的措施,进行优化提高执行的效率.
例如上面的例子,我们确认是对a表的全表扫描导致效率的不理想,我们对a表的year字段创建了索引,查询需要扫描的行数明显较少.
mysql>explainselectsum(moneys)fromsalesa,companysbwherea.
company_id=b.
idanda.
year=2006;www.
163.
com网易技术部49|select_type|table|type|possible_keys|key|key_len|rows|Extra||SIMPLE|b|index|PRIMARY|PRIMARY|4|1|Usingindex||SIMPLE|a|ref|year|year|4|3|Usingwhere|2rowsinset(0.
02sec)11.
2索引问题11.
2.
111.
2.
111.
2.
111.
2.
1索引的存储分类myisam表的数据文件和索引文件是自动分开的;innodb的数据和索引是存储在同一个表空间里面,但可以有多个文件组成.
创建索引语法如下:CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name[USINGindex_type]ONtbl_name(index_col_name,.
.
.
)index_col_name:col_name[(length)][ASC|DESC]索引的存储类型目前只有两种(btree和hash),具体和表的模式相关:myisambtreeinnodbbtreememory/heaphash,btreewww.
163.
com网易技术部50mysql目前不支持函数索引,只能对列的前一部分(length)进行索引,例:createindexind_testontable1(name(5)),对于char和varchar列,使用前缀索引将大大节省空间.
10.
2.
2MySQL如何使用索引索引用于快速找出在某个列中有一特定值的行.
对相关列使用索引是提高SELECT操作性能的最佳途径.
查询要使用索引最主要的条件是查询条件中需要使用索引关键字,如果是多列索引,那么只有查询条件使用了多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引.
下列情况下,Mysql不会使用已有的索引:1.
如果mysql估计使用索引比全表扫描更慢,则不使用索引.
例如:如果key_part1均匀分布在1和100之间,下列查询中使用索引就不是很好:SELECT*FROMtable_namewherekey_part1>1andkey_part1均不使用索引;3.
如果不是索引列的第一部分;4.
如果like是以%开始;5.
对where后边条件为字符串的一定要加引号,字符串如果为数字mysql会自动转为字符串,但是不使用索引.
10.
2.
3查看索引使用情况如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用.
Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救.
这个值的含义是在数据文件中读下一行的请求数.
如果你正进行大量的表扫描,该值较高.
通常说明表索引不正确或写入的查询没有利用索引.
www.
163.
com网易技术部51语法:mysql>showstatuslike'Handler_read%';11.
3两个简单实用的优化方法11.
3.
111.
3.
111.
3.
111.
3.
1定期分析表:ANALYZETABLE语法:ANALYZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name].
.
.
本语句用于分析和存储表的关键字分布.
在分析期间,使用一个读取锁定对表进行锁定.
这对于MyISAM,BDB和InnoDB表有作用.
对于MyISAM表,本语句与使用myisamchk-a相当.
CHECKTABLE语法:CHECKTABLEtbl_name[,tbl_name].
.
.
[option].
.
.
option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}检查一个或多个表是否有错误.
CHECKTABLE对MyISAM和InnoDB表有作用.
对于MyISAM表,关键字统计数据被更新.
CHECKTABLE也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在.
CHECKSUMTABLE语法:CHECKSUMTABLEtbl_name[,tbl_name].
.
.
[QUICK|EXTENDED]报告一个表校验和.
11.
3.
211.
3.
211.
3.
211.
3.
2使用optimizeoptimizeoptimizeoptimizetabletabletabletable:OPTIMIZETABLE语法:OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name].
.
.
如果已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有www.
163.
com网易技术部52VARCHAR,BLOB或TEXT列的表)进行了很多更改,则应使用OPTIMIZETABLE.
被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置.
您可以使用OPTIMIZETABLE来重新利用未使用的空间,并整理数据文件的碎片.
OPTIMIZETABLE只对MyISAM,BDB和InnoDB表起作用.
11.
4常用SQL的优化11.
4.
111.
4.
111.
4.
111.
4.
1大批量插入数据:1.
对于Myisam类型的表,可以通过以下方式快速的导入大量的数据.
ALTERTABLEtblnameDISABLEKEYS;loadingthedataALTERTABLEtblnameENABLEKEYS;这两个命令用来打开或者关闭Myisam表非唯一索引的更新.
在导入大量的数据到一个非空的Myisam表时,通过设置这两个命令,可以提高导入的效率.
对于导入大量数据到一个空的Myisam表,默认就是先导入数据然后才创建索引的,所以不用进行设置.
2.
而对于Innodb类型的表,这种方式并不能提高导入数据的效率.
对于Innodb类型的表,我们有以下几种方式可以提高导入的效率:a.
因为Innodb类型的表是按照主键的顺序保存的,所以将导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率.
如果Innodb表没有主键,那么系统会默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用这个优势提高导入数据的效率.
b.
在导入数据前执行SETUNIQUE_CHECKS=0,关闭唯一性校验,在导入结束后执行SETUNIQUE_CHECKS=1,恢复唯一性校验,可以提高导入的效率.
c.
如果应用使用自动提交的方式,建议在导入前执行SETAUTOCOMMIT=0,关闭自动提交,导入结束后再执行SETAUTOCOMMIT=1,打开自动提交,也可以提高导入的效率.
11.
4.
211.
4.
211.
4.
211.
4.
2优化insertinsertinsertinsert语句:3.
如果你同时从同一客户插入很多行,使用多个值表的INSERT语句.
这比使用分开www.
163.
com网易技术部53INSERT语句快(在一些情况中几倍).
Insertintotestvalues(1,2),(1,3),(1,4)…4.
如果你从不同客户插入很多行,能通过使用INSERTDELAYED语句得到更高的速度.
Delayed的含义是让insert语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘;这比每条语句分别插入要快的多;LOW_PRIORITY刚好相反,在所有其他用户对表的读写完后才进行插入;5.
将索引文件和数据文件分在不同的磁盘上存放(利用建表中的选项);6.
如果进行批量插入,可以增加bulk_insert_buffer_size变量值的方法来提高速度,但是,这只能对myisam表使用;7.
当从一个文本文件装载一个表时,使用LOADDATAINFILE.
这通常比使用很多INSERT语句快20倍;8.
根据应用情况使用replace语句代替insert;9.
根据应用情况使用ignore关键字忽略重复记录.
11.
4.
311.
4.
311.
4.
311.
4.
3优化groupgroupgroupgroupbybybyby语句:默认情况下,MySQL排序所有GROUPBYcol1,col2,.
.
.
.
.
查询的方法如同在查询中指定ORDERBYcol1,col2,.
.
.
.
如果显式包括一个包含相同的列的ORDERBY子句,MySQL可以毫不减速地对它进行优化,尽管仍然进行排序.
如果查询包括GROUPBY但你想要避免排序结果的消耗,你可以指定ORDERBYNULL禁止排序.
例如:INSERTINTOfooSELECTa,COUNT(*)FROMbarGROUPBYaORDERBYNULL;11.
4.
411.
4.
411.
4.
411.
4.
4优化orderorderorderorderbybybyby语句:在某些情况中,MySQL可以使用一个索引来满足ORDERBY子句,而不需要额外的排序.
where条件和orderby使用相同的索引,并且orderby的顺序和索引顺序相同,并且orderby的字段都是升序或者都是降序.
例如:下列sql可以使用索引.
www.
163.
com网易技术部54SELECT*FROMt1ORDERBYkey_part1,key_part2,.
.
.
;SELECT*FROMt1WHEREkey_part1=1ORDERBYkey_part1DESC,key_part2DESC;SELECT*FROMt1ORDERBYkey_part1DESC,key_part2DESC;但是以下情况不使用索引:SELECT*FROMt1ORDERBYkey_part1DESC,key_part2ASC;--orderby的字段混合ASC和DESCSELECT*FROMt1WHEREkey2=constantORDERBYkey1;--用于查询行的关键字与ORDERBY中所使用的不相同SELECT*FROMt1ORDERBYkey1,key2;--对不同的关键字使用ORDERBY:11.
4.
511.
4.
511.
4.
511.
4.
5优化joinjoinjoinjoin语句:Mysql4.
1开始支持SQL的子查询.
这个技术可以使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中.
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,并且写起来也很容易.
但是,有些情况下,子查询可以被更有效率的连接(JOIN).
.
替代.
假设我们要将所有没有订单记录的用户取出来,可以用下面这个查询完成:SELECT*FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)如果使用连接(JOIN).
.
来完成这个查询工作,速度将会快很多.
尤其是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询如下:SELECT*FROMcustomerinfoLEFTJOINsalesinfoONcustomerinfo.
CustomerID=salesinfo.
CustomerIDWHEREsalesinfo.
CustomerIDISNULL连接(JOIN).
.
之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完www.
163.
com网易技术部55成这个逻辑上的需要两个步骤的查询工作.
11.
4.
611.
4.
611.
4.
611.
4.
6mysqlmysqlmysqlmysql如何优化orororor条件:对于or子句,如果要利用索引,则or之间的每个条件列都必须用到索引;如果没有索引,则应该考虑增加索引.
11.
4.
711.
4.
711.
4.
711.
4.
7查询优先还是更新(insertinsertinsertinsert、updateupdateupdateupdate、deletedeletedeletedelete)优先:MySQL还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间.
改变优先级还可以确保特定类型的查询被处理得更快.
我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先.
下面我们提到的改变调度策略的方法主要是针对Myisam存储引擎的,对于Innodb存储引擎,语句的执行是由获得行锁的顺序决定的.
MySQL的默认的调度策略可用总结如下:1.
写入操作优先于读取操作.
2.
对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理.
3.
对某张数据表的多个读取操作可以同时地进行.
MySQL提供了几个语句调节符,允许你修改它的调度策略:1.
LOW_PRIORITY关键字应用于DELETE、INSERT、LOADDATA、REPLACE和UPDATE.
2.
HIGH_PRIORITY关键字应用于SELECT和INSERT语句.
3.
DELAYED关键字应用于INSERT和REPLACE语句.
如果写入操作是一个LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作.
在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么就允许第二个读取者插到写入者之前.
只有在没有其它的读取者的时候,才允许写入者开始www.
163.
com网易技术部56操作.
这种调度修改可能存在LOW_PRIORITY写入操作永远被阻塞的情况.
SELECT查询的HIGH_PRIORITY(高优先级)关键字也类似.
它允许SELECT插入正在等待的写入操作之前,即使在正常情况下写入操作的优先级更高.
另外一种影响是,高优先级的SELECT在正常的SELECT语句之前执行,因为这些语句会被写入操作阻塞.
如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理,那么请使用--low-priority-updates选项来启动服务器.
通过使用INSERTHIGH_PRIORITY来把INSERT语句提高到正常的写入优先级,可以消除该选项对单个INSERT语句的影响.
11.
4.
811.
4.
811.
4.
811.
4.
8使用SQLSQLSQLSQL提示:SELECTSQL_BUFFER_RESULTS.
.
.
将强制MySQL生成一个临时结果集.
只要所有临时结果集生成后,所有表上的锁定均被释放.
这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助.
当处理一个会让客户端耗费点时间才能处理的大结果集时,可以考虑使用SQL_BUFFER_RESULT提示字.
这样可以告诉MySQL将结果集保存在一个临时表中,这样可以尽早的释放各种锁.
USEINDEX在你查询语句中表名的后面,添加USEINDEX来提供你希望MySQ去参考的索引列表,就可以让MySQL不再考虑其他可用的索引.
Eg:SELECT*FROMmytableUSEINDEX(mod_time,name).
.
.
IGNOREINDEX如果你只是单纯的想让MySQL忽略一个或者多个索引,可以使用IGNOREINDEX作为Hint.
Eg:SELECT*FROMmytaleIGNOREINDEX(priority).
.
.
FORCEINDEX为强制MySQL使用一个特定的索引,可在查询中使用FORCEINDEX作为Hint.
Eg:SELECT*FROMmytableFORCEINDEX(mod_time).
.
.
11.
5其他优化措施1.
使用持久的连接数据库以避免连接开销.
www.
163.
com网易技术部572.
经常检查所有查询确实使用了必要的索引.
3.
避免在频繁更新的表上执行复杂的SELECT查询,以避免与锁定表有关的由于读、写冲突发生的问题.
4.
对于没有删除的行操作的MyISAM表,插入操作和查询操作可以并行进行,因为没有删除操作的表查询期间不会阻塞插入操作.
对于确实需要执行删除操作的表,尽量在空闲时间进行批量删除操作,避免阻塞其他操作.
5.
充分利用列有默认值的事实.
只有当插入的值不同于默认值时,才明确地插入值.
这减少MySQL需要做的语法分析从而提高插入速度.
6.
对经常访问的可以重构的数据使用内存表,可以显著提高访问的效率.
7.
通过复制可以提高某些操作的性能.
可以在复制服务器中分布客户的检索以均分负载.
为了防止备份期间对应用的影响,可以在复制服务器上执行备份操作.
8.
表的字段尽量不使用自增长变量,在高并发情况下该字段的自增可能对效率有比较大的影响,推荐通过应用来实现字段的自增长.
第12121212章优化数据库对象12.
1优化表的数据类型表需要使用何种数据类型,是需要根据应用来判断的.
虽然应用设计的时候需要考虑字段的长度留有一定的冗余,但是不推荐让很多字段都留有大量的冗余,这样即浪费存储也浪费内存.
我们可以使用PROCEDUREANALYSE()对当前已有应用的表类型的判断,该函数可以对数据表中的列的数据类型提出优化建议,可以根据应用的实际情况酌情考虑是否实施优化.
语法:SELECT*FROMtbl_namePROCEDUREANALYSE();SELECT*FROMtbl_namePROCEDUREANALYSE(16,256);输出的每一列信息都会对数据表中的列的数据类型提出优化建议.
第二个例子告诉PROCEDUREANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议.
如果没有这样的限制,输出信息可能很长;ENUM定义通常很难阅读.
www.
163.
com网易技术部58在对字段类型进行优化时,可以根据统计信息并结合应用的实际情况对其进行优化.
12.
2通过拆分,提高表的访问效率这里我们所说的拆分,主要是针对Myisam类型的表,拆分的方法可以分成两种情况:1.
纵向拆分:纵向拆分是只按照应用访问的频度,将表中经常访问的字段和不经常访问的字段拆分成两个表,经常访问的字段尽量是定长的,这样可以有效的提高表的查询和更新的效率.
2.
横向拆分:横向拆分是指按照应用的情况,有目的的将数据横向拆分成几个表或者通过分区分到多个分区中,这样可以有效的避免Myisam表的读取和更新导致的锁问题.
12.
3逆规范化数据库德规范化设计强调数据的独立性,数据应该尽可能少地冗余,因为存在过多的冗余数据,这就意味着要占用了更多的物理空间,同时也对数据的维护和一致性检查带来了问题.
但是对于查询操作很多的应用,一次查询可能需要访问多表进行,如果通过冗余纪录在相同表中,更新的代价增加不多,但是查询操作效率可以有明显提高,这种情况就可以考虑通过冗余数据来提高效率.
12.
4使用冗余统计表使用createtemporarytable语法,它是基于session的表,表的数据保存在内存里面,当session断掉后,表自然消除.
对于大表的统计分析,如果统计的数据量不大,利用insert.
.
.
select将数据移到临时表中比直接在大表上做统计要效率更高.
12.
5选择更合适的表类型1、如果应用出现比较严重的锁冲突,请考虑是否更改存储引擎到innodb,行锁机制可以有效的减少锁冲突的出现.
2、如果应用查询操作很多,且对事务完整性要求不严格,则可以考虑使用Myisamwww.
163.
com网易技术部59存储引擎.
更多存储引擎选择的原则,请参考开发篇的相关章节.
第13131313章锁问题13.
1获取锁等待情况可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:mysql>showstatuslike'Table%';|Variable_name|Value||Table_locks_immediate|105||Table_locks_waited|3|2rowsinset(0.
00sec)可以通过检查Innodb_row_lock状态变量来分析系统上的行锁的争夺情况:mysql>showstatuslike'innodb_row_lock%';|Variable_name|Value||Innodb_row_lock_current_waits|0||Innodb_row_lock_time|2001||Innodb_row_lock_time_avg|667||Innodb_row_lock_time_max|845||Innodb_row_lock_waits|3|5rowsinset(0.
00sec)www.
163.
com网易技术部60另外,针对Innodb类型的表,如果需要察看当前的锁等待情况,可以设置InnoDBMonitors,然后通过Showinnodbstatus察看,设置的方式是:CREATETABLEinnodb_monitor(aINT)ENGINE=INNODB;监视器可以通过发出下列语句来被停止:DROPTABLEinnodb_monitor;设置监视器后,在showinnodbstatus的显示内容中,会有详细的当前锁等待的信息,包括表名、锁类型、锁定记录的情况等等,便于进行进一步的分析和问题的确定.
打开监视器以后,默认情况下每15秒会向日志中记录监控的内容,如果长时间打开会导致.
err文件变得非常的巨大,所以我们在确认问题原因之后,要记得删除监控表以关闭监视器.
或者通过使用--console选项来启动服务器以关闭写日志文件.
13.
2什么情况下使用表锁表级锁在下列几种情况下比行级锁更优越:1.
很多操作都是读表.
2.
在严格条件的索引上读取和更新,当更新或者删除可以用单独的索引来读取得到时:3.
UPDATEtbl_nameSETcolumn=valueWHEREunique_key_col=key_value;4.
DELETEFROMtbl_nameWHEREunique_key_col=key_value;5.
SELECT和INSERT语句并发的执行,但是只有很少的UPDATE和DELETE语句.
6.
很多的扫描表和对全表的GROUPBY操作,但是没有任何写表.
13.
3什么情况下使用行锁行级锁定的优点:1.
当在许多线程中访问不同的行时只存在少量锁定冲突.
2.
回滚时只有少量的更改.
3.
可以长时间锁定单一的行.
行级锁定的缺点:1.
比页级或表级锁定占用更多的内存.
www.
163.
com网易技术部612.
当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁.
3.
如果你在大部分数据上经常进行GROUPBY操作或者必须经常扫描整个表,比其它锁定明显慢很多.
4.
用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定.
13.
4insert…select…带来的问题当使用insert.
.
.
select.
.
.
进行记录的插入时,如果select的表是innodb类型的,不论insert的表是什么类型的表,都会对select的表的纪录进行锁定.
对于那些从oracle迁移过来的应用,需要特别的注意,因为oracle并不存在类似的问题,所以在oracle的应用中insert.
.
.
select.
.
.
操作非常的常见.
例如:有时候会对比较多的纪录进行统计分析,然后将统计的中间结果插入到另外一个表,这样的操作因为进行的非常少,所以可能并没有设置相应的索引.
如果迁移到mysql数据库后不进行相应的调整,那么在进行这个操作期间,对需要select的表实际上是进行的全表扫描导致的所有记录的锁定,将会对应用的其他操作造成非常严重的影响.
究其主要原因,是因为mysql在实现复制的机制时和oracle是不同的,如果不进行select表的锁定,则可能造成从数据库在恢复期间插入结果集的不同,造成主从数据的不一致.
如果不采用主从复制,关闭binlog并不能避免对select纪录的锁定,某些文档中提到可以通过设置innodb_locks_unsafe_for_binlog来避免这个现象,当这个参数设置为true的时候,将不会对select的结果集加锁,但是这样的设置将可能带来非常严重的隐患.
如果使用这个binlog进行从数据库的恢复,或者进行主数据库的灾难恢复,都将可能和主数据库的执行效果不同.
因此,我们并不推荐通过设置这个参数来避免insert.
.
.
select.
.
.
导致的锁,如果需要进行可能会扫描大量数据的insert.
.
.
select操作,我们推荐使用select.
.
.
intooutfile和loaddatainfile的组合来实现,这样是不会对纪录进行锁定的.
www.
163.
com网易技术部6213.
5next-key锁对并发插入的影响在行级锁定中,InnoDB使用一个名为next-keylocking的算法.
InnoDB以这样一种方式执行行级锁定:当它搜索或扫描表的索引之时,它对遇到的索引记录设置共享或独占锁定.
因此,行级锁定事实上是索引记录锁定.
InnoDB对索引记录设置的锁定也映像索引记录之前的"间隙".
如果一个用户对一个索引上的记录R有共享或独占的锁定,另一个用户不能紧接在R之前以索引的顺序插入一个新索引记录.
这个间隙的锁定被执行来防止所谓的"幽灵问题".
可以用next-key锁定在你的应用程序上实现一个唯一性检查:如果你以共享模式读数据,并且没有看到你将要插入的行的重复,则你可以安全地插入你的行,并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复.
因此,thenext-key锁定允许你锁住在你的表中并不存在的一些东西.
13.
6隔离级别对并发插入的影响REPEATABLEREAD是InnoDB的默认隔离级别.
带唯一搜索条件使用唯一索引的SELECT.
.
.
FORUPDATE,SELECT.
.
.
LOCKINSHAREMODE,UPDATE和DELETE语句只锁定找到的索引记录,而不锁定记录前的间隙.
用其它搜索条件,这些操作采用next-key锁定,用next-key锁定或者间隙锁定锁住搜索的索引范围,并且阻止其它用户的新插入.
在持续读中,有一个与READCOMMITTED隔离级别重要的差别:在这个级别,在同一事务内所有持续读读取由第一次读所确定的同一快照.
这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句,这些SELECT语句对相互之间也是持续的.
READCOMMITTED隔离级别是一个有些象Oracle的隔离级别.
所有SELECT.
.
.
FORUPDATE和SELECT.
.
.
LOCKINSHAREMOD语句仅锁定索引记录,而不锁定记录前的间隙,因而允许随意紧挨着已锁定的记录插入新记录.
UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录,而不包括记录前的间隙.
在范围类型UPDATE和DELETE语句,InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入.
这是很必要的,因为要让MySQL复制和恢www.
163.
com网易技术部63复起作用,"幽灵行"必须被阻止掉.
如果应用是从基于ORACLE的应用迁移到MYSQL数据库的,那么建议使用该隔离级别提供数据库服务,因为该隔离级别是最接近ORACLE的默认隔离级别的,迁移可能遇到的锁问题最小.
13.
7如何减少锁冲突1.
对Myisam类型的表:1)Myisam类型的表可以考虑通过改成Innodb类型的表来减少锁冲突.
2)根据应用的情况,尝试横向拆分成多个表或者改成Myisam分区对减少锁冲突也会有一定的帮助.
2.
对Innodb类型的表:1)首先要确认,在对表获取行锁的时候,要尽量的使用索引检索纪录,如果没有使用索引访问,那么即便你只是要更新其中的一行纪录,也是全表锁定的.
要确保sql是使用索引来访问纪录的,必要的时候,请使用explain检查sql的执行计划,判断是否按照预期使用了索引.
2)由于mysql的行锁是针对索引加的锁,不是针对纪录加的锁,所以虽然是访问不同行的纪录,但是如果是相同的索引键,是会被加锁的.
应用设计的时候也要注意,这里和Oracle有比较大的不同.
3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,当表有主键或者唯一索引的时候,不是必须使用主键或者唯一索引锁定纪录,其他普通索引同样可以用来检索纪录,并只锁定符合条件的行.
4)用SHOWINNODBSTATUS来确定最后一个死锁的原因.
查询的结果中,包括死锁的事务的详细信息,包括执行的SQL语句的内容,每个线程已经获得了什么锁,在等待什么锁,以及最后是哪个线程被回滚.
详细的分析死锁产生的原因,可以通过改进程序有效的避免死锁的产生.
5)如果应用并不介意死锁的出现,那么可以在应用中对发现的死锁进行处理.
6)确定更合理的事务大小,小事务更少地倾向于冲突.
7)如果你正使用锁定读,(SELECT.
.
.
FORUPDATE或.
.
.
LOCKINSHAREMODE),试着用更低的隔离级别,比如READCOMMITTED.
8)以固定的顺序访问你的表和行.
则事务形成良好定义的查询并且没有死锁.
www.
163.
com网易技术部64第14141414章优化MysqlMysqlMysqlMysqlServerServerServerServer14.
1查看Mysqlserver当前参数1.
查看服务器参数默认值:mysqld--verbose--help2.
查看服务器参数实际值:shell>mysqladminvariables或者mysql>SHOWVARIABLES;3.
查看服务器运行状态值:shell>mysqladminextended-status或者mysql>SHOWSTATUS;14.
2影响Mysql性能的重要参数14.
2.
114.
2.
114.
2.
114.
2.
1key_buffer_sizekey_buffer_sizekey_buffer_sizekey_buffer_size的设置说明:键缓存(变量key_buffer_size)被所有线程共享;服务器使用的其它缓存则根据需要分配.
此参数只适用于myisam存储引擎.
使用方法:mysql5.
1以前只允许使用一个系统默认的key_buffermysql5.
1以后提供了多个key_buffer,可以将指定的表索引缓存入指定的key_buffer,这样可以更小的降低线程之间的竞争,相关语法如下:例如,下面的语句将表t1、t2和t3的索引分配给名为hot_cache的键高速缓冲:mysql>CACHEINDEXt1,t2,t3INhot_cache;可以用SETGLOBAL参数设置语句或使用服务器启动选项设置在CACHEINDEX语句中www.
163.
com网易技术部65引用的键高速缓冲的大小来创建键高速缓冲.
例如:mysql>SETGLOBALkeycache1.
key_buffer_size=128*1024;要想删除键高速缓冲,将其大小设置为零:mysql>SETGLOBALkeycache1.
key_buffer_size=0;请注意不能删除默认键高速缓冲.
删除默认键高速缓冲的尝试将被忽略CACHEINDEX在一个表和键高速缓冲之间建立一种联系,但每次服务器重启时该联系被丢失.
如果你想要每次服务器重启时该联系生效,一个发办法是使用选项文件:包括配置键高速缓冲的变量设定值,和一个init-file选项用来命名包含待执行的CACHEINDEX语句的一个文件.
例如:key_buffer_size=4Ghot_cache.
key_buffer_size=2Gcold_cache.
key_buffer_size=2Ginit_file=/path/to/data-directory/mysqld_init.
sql每次服务器启动时执行mysqld_init.
sql中的语句.
该文件每行应包含一个SQL语句.
下面的例子分配几个表,分别对应hot_cache和cold_cache:CACHEINDEXa.
t1,a.
t2,b.
t3INhot_cacheCACHEINDEXa.
t4,b.
t5,b.
t6INcold_cache要想将索引预装到缓存中,使用LOADINDEXINTOCACHE语句.
例如,下面的语句可以预装表t1和t2索引的非叶节点(索引块):mysql>LOADINDEXINTOCACHEt1,t2IGNORELEAVES;键高速缓冲可以通过更新其参数值随时重新构建.
例如:mysql>SETGLOBALcold_cache.
key_buffer_size=4*1024*1024;如果你很少使用MyISAM表,那么也保留低于16-32MB的key_buffer_size以适应给予磁盘的临时表索引所需.
www.
163.
com网易技术部6614.
2.
214.
2.
214.
2.
214.
2.
2table_cachetable_cachetable_cachetable_cache的设置说明:数据库中打开表的缓存数量.
table_cache与max_connections有关.
例如,对于200个并行运行的连接,应该让表的缓存至少有200*N,这里N是可以执行的查询的一个联接中表的最大数量.
还需要为临时表和文件保留一些额外的文件描述符.
设置技巧:可以通过检查mysqld的状态变量Opened_tables确定表缓存是否太小:mysql>SHOWSTATUSLIKE'Opened_tables';|Variable_name|Value||Opened_tables|2741|如果值很大,即使你没有发出许多FLUSHTABLES语句,也应增加表缓存的大小.
14.
2.
314.
2.
314.
2.
314.
2.
3innodb_buffer_pool_sizeinnodb_buffer_pool_sizeinnodb_buffer_pool_sizeinnodb_buffer_pool_size的设置:缓存InnoDB数据和索引的内存缓冲区的大小.
你把这个值设得越高,访问表中数据需要得磁盘I/O越少.
在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的80%.
尽管如此,还是不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度.
14.
2.
414.
2.
414.
2.
414.
2.
4innodb_flush_log_at_trx_commitinnodb_flush_log_at_trx_commitinnodb_flush_log_at_trx_commitinnodb_flush_log_at_trx_commit的设置:0:日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何操作.
1:在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新.
2:在每个提交,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新.
对日志文件每秒刷新一次.
www.
163.
com网易技术部67默认值是1,也是最安全的设置,即每个事务提交的时候都会从logbuffer写到日志文件,而且会实际刷新磁盘,但是这样性能有一定的损失.
如果可以容忍在数据库崩溃的时候损失一部分数据,那么设置成0或者2都会有所改善.
设置成0,则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务,最多丢失1秒钟的事务,这种方式是最不安全的,也是效率最高的.
设置成2的时候,因为只是没有刷新到磁盘,但是已经写入日志文件,所以只要操作系统没有崩溃,那么并没有丢失数据,比设置成0更安全一些.
在mysql的手册中,为了确保事务的持久性和复制设置的耐受性、一致性,都是建议将这个参数设置为1的.
14.
2.
514.
2.
514.
2.
514.
2.
5innodb_additional_mem_pool_sizeinnodb_additional_mem_pool_sizeinnodb_additional_mem_pool_sizeinnodb_additional_mem_pool_size:InnoDB用来存储数据目录信息和其它内部数据结构的内存池的大小.
默认值是1MB.
应用程序里的表越多,你需要在这里分配越多的内存.
如果InnoDB用光了这个池内的内存,InnoDB开始从操作系统分配内存,并且往MySQL错误日志写警告信息.
没有必要给这个缓冲池分配非常大的空间,在应用相对稳定的情况下,这个缓冲池的大小也相对稳定.
14.
2.
614.
2.
614.
2.
614.
2.
6innodb_table_locksinnodb_table_locksinnodb_table_locksinnodb_table_locks:InnoDB重视LOCKTABLES,直到所有其它线程已经释放他们所有对表的锁定,MySQL才从LOCKTABLE.
.
WRITE返回.
默认值是1,这意为LOCKTABLES让InnoDB内部锁定一个表.
在使用AUTOCOMMIT=1的应用里,InnoDB的内部表锁定会导致死锁.
可以通过设置innodb_table_locks=0来消除这个问题.
14.
2.
714.
2.
714.
2.
714.
2.
7innodb_lock_wait_timeoutinnodb_lock_wait_timeoutinnodb_lock_wait_timeoutinnodb_lock_wait_timeout:Mysql可以自动的监测行锁导致的死锁并进行相应的处理,但是对于表锁导致的死锁不能自动的监测,所以该参数主要被用来在出现类似情况的时候对锁定进行的后续处理.
默认值是50秒,根据应用的需要进行调整.
www.
163.
com网易技术部6814.
2.
814.
2.
814.
2.
814.
2.
8innodb_support_xainnodb_support_xainnodb_support_xainnodb_support_xa:通过该参数设置是否支持分布式事务,默认值是ON或者1,表示支持分布式事务.
如果确认应用中不需要使用分布式事务,则可以关闭这个参数,减少磁盘刷新的次数并获得更好的InnoDB性能.
14.
2.
914.
2.
914.
2.
914.
2.
9innodb_doublewriteinnodb_doublewriteinnodb_doublewriteinnodb_doublewrite:默认地,InnoDB存储所有数据两次,第一次存储到doublewrite缓冲,然后存储到确实的数据文件.
如果对性能的要求高于对数据完整性的要求,那么可以通过--skip-innodb-doublewrite关闭这个设置.
14.
2.
1014.
2.
1014.
2.
1014.
2.
10innodb_log_buffer_size:innodb_log_buffer_size:innodb_log_buffer_size:innodb_log_buffer_size:默认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可以.
如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了.
如果它的值设置太高了,可能会浪费内存--它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间.
通常8-16MB就足够了.
越小的系统它的值越小.
14.
2.
1114.
2.
1114.
2.
1114.
2.
11innodb_log_file_sizeinnodb_log_file_sizeinnodb_log_file_sizeinnodb_log_file_size:在高写入负载尤其是大数据集的情况下很重要.
这个值越大则性能相对越高,但是要注意到可能会增加恢复时间.
第15151515章I/OI/OI/OI/O问题磁盘搜索是巨大的性能瓶颈.
当数据量变得非常大以致于缓存性能变得不可能有效时,该问题变得更加明显.
对于大数据库,其中你或多或少地随机访问数据,你可以确信对读取操作需要至少一次硬盘搜索,写操作需要多次硬盘搜索.
要想使该问题最小化,应使用搜索次数较少的磁盘.
www.
163.
com网易技术部6915.
1使用磁盘阵列或虚拟文件卷分布I/O分条意味着你有许多磁盘,将第1个块放到第1个硬盘,第2个块放到第2个磁盘,并且第N块在(Nmodnumber_of_disks)磁盘上等等.
这意味着如果正常数据大小小于分条大小(或完全匹配),能够得到最佳性能.
分条完全取决于操作系统和分条大小,因此用不同的条纹大小对应用程序进行基准测试.
分条的不同速度完全依赖于参数.
依赖于怎样设置条纹参数和硬盘数量,可以根据不同数量级别得到不同的标准.
你必须进行选择以便优化随机或顺序存取.
1.
为了高可靠性你可能想使用RAID0+1(条纹加镜像),但在这种情况下,需要2*N块磁盘来保持N个磁盘的数据.
如果你肯为它花钱,这可能是最好的选项.
然而,你可能还必须投资一部分资金到卷管理软件中以便有效地管理它.
2.
一个较好的选择是根据数据类型的重要性程度改变RAID级别.
例如,保存可以在RAID0硬盘上重新生成的不太重要的数据,但保存真正重要数据(例如主机信息和日志)到RAID0+1或RAIDN硬盘.
如果你有许多写操作,RAIDN可能会存在问题,因为需要时间来更新校验位.
15.
2使用SymbolicLinks分布I/O可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换.
推荐的方法只需要将数据库通过符号链接指到不同的磁盘.
符号链接表仅作为是最后的办法.
符号链接一个数据库的方法是,首先在一些有空闲空间的硬盘上创建一个目录,然后从MySQL数据目录中创建它的一个符号链接.
shell>mkdir/dr1/databases/testshell>ln-s/dr1/databases/test/path/to/datadir注意:只有MyISAM表完全支持符号链接.
对于其它表类型,如果试图在操作系统中的文件上用前面的任何语句使用符号链接,可能会出现奇怪的问题.
对于MyISAM表的符号链接的处理如下:1.
在数据目录指,一定会有表定义文件、数据文件和索引文件.
数据文件和索引文件可以移到别处和在数据目录中符号链接替代.
表定义文件不能进行符号链接替换.
2.
可以分别通过符号链接将数据文件和索引文件指到不同的目录.
www.
163.
com网易技术部703.
如果mysqld没有运行,符号链接可以从服务器命令行使用ln-s手动完成.
同样,通过使用DATADIRECTORY和INDEXDIRECTORY选项创建表,你可以指示运行的MySQL服务器执行符号链接.
4.
myisamchk不用数据文件或索引文件替换符号链接.
它直接工作在符号链接指向的文件.
任何临时文件创建在数据文件或索引文件所处的目录中.
5.
注释:当你删掉一个表时,如果该表使用了符号链接,符号链接和该符号链接指向的文件都被删除掉.
这就是你不应以系统root用户运行mysqld或允许系统用户对MySQL数据库目录有写访问权限的原因.
6.
如果你用ALTERTABLE.
.
.
RENAME重命名一个表并且不将表移到另一个数据库,数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应地重新命名.
7.
如果你用ALTERTABLE.
.
.
RENAME移动一个表到另一个数据库,表移动到另一个数据库目录.
旧的符号链接和其所指向的文件被删除.
换句话说,新表不再被链接.
8.
如果不使用符号链接,你应对mysqld使用--skip-symbolic-links选项以确保没有人能够使用mysqld来删除或重新命名数据目录之外的文件.
表符号链接还不支持以下操作:1.
ALTERTABLE忽略DATADIRECTORY和INDEXDIRECTORY表选项.
2.
BACKUPTABLE和RESTORETABLE不考虑符号链接.
3.
.
frm文件必须绝不能是一个符号链接(如前面所述,只有数据和索引文件可以是符号链接).
如果试图这样做(例如,生成符号链接)会产生不正确的结果.
第16161616章应用优化16.
1使用连接池对于访问数据库来说,建立连接的代价比较昂贵,因此,我们有必要建立"连接池"以提高访问的性能.
我们可以把连接当作对象或者设备,池中又有许多已经建立的连接,访问本来需要与数据库的连接的地方,都改为和池相连,池临时分配连接供访问使用,结果返回后,访问将连接交还.
www.
163.
com网易技术部7116.
2减少对Mysql的访问16.
2.
116.
2.
116.
2.
116.
2.
1避免对同一数据做重复检索:应用中需要理清楚对数据库的访问逻辑,需要对相同表的访问,尽量集中在相同sql访问,一次提取结果,减少对数据库的重复访问.
16.
2.
216.
2.
216.
2.
216.
2.
2使用mysqlmysqlmysqlmysqlqueryqueryqueryquerycachecachecachecache:作用:查询缓存存储SELECT查询的文本以及发送给客户端的相应结果.
如果随后收到一个相同的查询,服务器从查询缓存中重新得到查询结果,而不再需要解析和执行查询.
适用范围:不发生数据更新的表.
当表更改(包括表结构和表数据)后,查询缓存值的相关条目被清空.
查询缓存的主要参数设置:showvariableslike'%query_cache%';have_query_cache表明服务器在安装使已经配置了高速缓存query_cache_size表明缓存区大小,单位为Mquery_cache_type的变量值从0到2,含义分别为0或者off(缓存关闭)1或者on(缓存打开,使用sql_no_cache的select除外)2或者demand(只有带sql_cache的select语句提供高速缓存)在SHOWSTATUS中,你可以监视查询缓存的性能:变量含义Qcache_queries_in_cache在缓存中已注册的查询数目Qcache_inserts被加入到缓存中的查询数目Qcache_hits缓存采样数数目Qcache_lowmem_prunes因为缺少内存而被从缓存中删除的查询数目Qcache_not_cached没有被缓存的查询数目(不能被缓存的,或由于QUERY_CACHE_TYPE)Qcache_free_memory查询缓存的空闲内存总数Qcache_free_blocks查询缓存中的空闲内存块的数目www.
163.
com网易技术部7216.
2.
316.
2.
316.
2.
316.
2.
3加cachecachecachecache层:Cache(高速缓存)、Memory(内存)、Harddisk(硬盘)都是数据存取单元,但存取速度却有很大差异,呈依次递减的顺序.
对于CPU来说,它可以从距离自己最近的Cache高速地存取数据,而不是从内存和硬盘以低几个数量级的速度来存取数据.
而Cache中所存储的数据,往往是CPU要反复存取的数据,有特定的机制(或程序)来保证Cache内数据的命中率(HitRate).
因此,CPU存取数据的速度在应用高速缓存后得到了巨大的提高.
因为将数据写入高速缓存的任务由CacheManager负责,所以对用户来说高速缓存的内容肯定是只读的.
需要你做的工作很少,程序中的SQL语句和直接访问DBMS时没有分别,返回的结果也看不出有什么差别.
而数据库厂商往往会在DBServer的配置文件中提供与Cache相关的参数,通过修改它们,可针对我们的应用优化Cache的管理.
16.
3负载均衡16.
3.
116.
3.
116.
3.
116.
3.
1利用mysqlmysqlmysqlmysql复制分流查询操作:利用mysql的主从复制可以有效的分流更新操作和查询操作,具体的实现是一个主服务器,承担更新操作,多台从服务器,承担查询操作,主从之间通过复制实现数据的同步.
多台从服务器一方面用来确保可用性,一方面可以创建不同的索引满足不同查询的需要.
对于主从之间不需要复制全部表的情况,可以通过在主的服务器上搭建一个虚拟的从服务器,将需要复制到从服务器的表设置成blackhole引擎,然后定义replicate-do-table参数只复制这些表,这样就过滤出需要复制的binlog,减少了传输binlog的带宽.
因为搭建的虚拟的从服务器只起到过滤binlog的作用,并没有实际纪录任何数据,所以对主数据库服务器的性能影响也非常的有限.
通过复制分流查询的存在的问题是主数据库上更新频繁或者网络出现问题的时候,主从之间的数据可能存在差异,造成查询结果的异议,应用在设计的时候需要有所考虑.
16.
3.
216.
3.
216.
3.
216.
3.
2采用分布式数据库架构:mysql从5.
0.
3开始支持分布式事务,当前分布式事务只对Innodb存储引擎支持.
分布式的数据库架构适合大数据量,负载高的情况,有良好的扩展性和高可用性.
通过在多Qcache_total_blocks查询缓存中的块的总数目www.
163.
com网易技术部73台服务器之间分布数据实现在多台服务器之间的负载平均,提高了访问的执行效率.
具体实现的时候,可以使用mysql的Cluster功能(NDB引擎)或者自己编写程序来实现全局事务.
www.
163.
com网易技术部74第三篇第三篇第三篇第三篇管理维护篇管理维护篇管理维护篇管理维护篇第17171717章mysqlmysqlmysqlmysql安装升级17.
1安装17.
1.
1安装方法比较rpm二进制源码优点安装简单,适合初学者学习使用1.
安装简单2.
可以地安装到任何路径下,灵活性好3.
一台服务器可以安装多个mysql1.
在实际安装的操作系统进行可根据需要定制编译,最灵活2.
性能最好3.
一台服务器可以安装多个mysql缺点1.
需要单独下载客户端和服务器2.
安装路径不灵活,默认路径不能修改,一台服务器只能安装一个mysql1)已经经过编译,性能不如源码编译的好2)不能灵活定制编译参数1.
安装过程较复杂2.
编译时间长文件布局/usr/bin客户端程序和脚本/usr/sbinmysqld服务器/var/lib/mysql日志文件,数据库bin客户端程序和mysqld服务器data日志文件,数据库docsbin客户端程序和脚本include/mysql包含(头)文件infoInfo格式的文档www.
163.
com网易技术部7517.
1.
2rpm安装步骤大多数情况下,下载MySQL-server和MySQL-client就够用了,安装方法如下:shell>rpm-ivhMySQL-server-VERSION.
i386.
rpmshell>rpm-ivhMySQL-client-VERSION.
i386.
rpm17.
1.
3二进制安装步骤root登陆,执行如下步骤:shell>groupaddmysqlshell>useradd-gmysqlmysqlshell>cd/home/mysqlshell>tar-xzvf/home/mysql/mysql-VERSION-OS.
tar.
gzshell>ln-smysql-VERSION-OS.
tar.
gzmysqlshell>cdmysqlshell>scripts/mysql_install_db--user=mysqlshell>chown-Rroot:mysql.
shell>chown-Rmysql:mysqldatashell>bin/mysqld_safe--user=mysql&/usr/share/doc/packages文档/usr/include/mysql包含(头)文件/usr/lib/mysql库/usr/share/mysql错误消息和字符集文件/usr/share/sql-bench基准程序文档,ChangeLoginclude包含(头)文件lib库scriptsmysql_install_dbshare/mysql错误消息文件sql-bench基准程序lib/mysql库libexecmysqld服务器share/mysql错误消息文件sql-bench基准程序和crash-me测试var数据库和日志文件www.
163.
com网易技术部7617.
2源码安装步骤root登陆,执行如下步骤:shell>groupaddmysqlshell>useradd-gmysqlmysqlshell>gunzipcdmysql-VERSIONshell>.
/configure--prefix=/usr/local/mysqlshell>makeshell>makeinstallshell>cpsupport-files/my-medium.
cnf/etc/my.
cnfshell>cd/usr/local/mysqlshell>bin/mysql_install_db--user=mysqlshell>chown-Rroot.
shell>chown-Rmysqlvarshell>chgrp-Rmysql.
bin/mysql_install_db--user=mysql17.
3源码安装的性能考虑:17.
3.
1去掉不需要的模块:源码安装由于可以灵活的进行数据库的定制编译,因此有更强的灵活性.
某些编译选项可以大大增强我们数据库的性能.
执行如下命令可以看到所有编译的配置选项:shell>.
/configure--help如果只安装客户端,可以执行如下命令:shell>.
/configure--without-server如果你不想要位于"/usr/local/var"目录下面的日志(log)文件和数据库,使用类似于下列configure命令的一个:www.
163.
com网易技术部77shell>.
/configure--prefix=/usr/local/mysqlshell>.
/configure--prefix=/usr/locallocalstatedir=/usr/local/mysql/data第一个命令改变安装前缀以便将所有内容安装到"/usr/local/mysql"下面而非默认的"/usr/local".
第二个命令保留默认安装前缀,但是覆盖了数据库目录默认目录(通常是"/usr/local/var")并且把它改为/usr/local/mysql/data.
编译完MySQL后,可以通过选项文件更改这些选项修改socket的默认位置:shell>.
/configure\--with-unix-socket-path=/usr/local/mysql/tmp/mysql.
sock17.
3.
2只选择要使用的字符集:改变安装后的默认字符集和排序规则:shell>.
/configure--with-charset=CHARSET.
/configure--with-collation=COLLATION选择需要安装的字符集:shell>.
/configure--with-extra-charsets=LISTlist可以是下面任何一项:空格间隔的一系列字符集名complex-,以包括不能动态装载的所有字符集all–,以将所有字符集包括进二进制17.
3.
3使用pgcc编译:pgcc2.
90.
29或更新版:CFLAGS="-O3-mpentiumpro-mstack-align-double"CXX=gcc\www.
163.
com网易技术部78CXXFLAGS="-O3-mpentiumpro-mstack-align-double\-felide-constructors-fno-exceptions-fno-rtti"17.
3.
4使用静态编译以提高性能:shell>.
/configure--with-client-ldflags=-all-static\--with-mysqld-ldflags=-all-static17.
4mysql升级方法1最简单,适合于任何存储引擎(不一定速度最快)安装新数据库将老数据库导出为文本,导入到新数据库上shell>mysqladmin-hhostname-Pport-uuser-ppasswdcreatedb_nameshell>mysqldump--optdb_name|mysql-hhostname-Pport-uuser-ppasswddb_name注:如果网络较慢,可以在导出选项中加上--compress来减少网络传输升级权限表将原库中的mysql数据库目录全部cp过来覆盖新库中mysql数据库在shell里面执行mysql_fix_privilege_tables命令升级权限表shell>mysql_fix_privilege_tables重启数据库服务方法2适合于任何存储引擎,速度较快安装新数据库旧库中创建保存输出文件的目录并备份数据库:shell>mkdirDUMPDIRshell>mysqldump--tab=DUMPDIRdb_name将DUMPDIR目录中的文件转移到目标机上相应的目录中并将文件装载到MySQL:shell>mysqladmincreatedb_name#createdatabaseshell>catDUMPDIR/*.
sql|mysqldb_name#createtablesindatabaseshell>mysqlimportdb_nameDUMPDIR/*.
txt#loaddataintotableswww.
163.
com网易技术部79(实际测试的时候,发现txt要放到data下才能执行,否则提示文件找不到)升级权限表将原库中的mysql数据库目录全部cp过来覆盖新库中mysql数据库在shell里面执行mysql_fix_privilege_tables命令升级权限表shell>mysql_fix_privilege_tables重启数据库服务方法3适合于myisam表,速度最快安装新数据库将原库中的数据目录下的所有文件(.
frm,.
MYD,MYI)cp到新库下的相应目录下升级权限表将原库中的mysql数据库目录全部cp过来覆盖新库中mysql数据库在shell里面执行mysql_fix_privilege_tables命令升级权限表shell>mysql_fix_privilege_tablesflushtables或者重启数据库服务生效17.
5mysql降级对于myisam存储引擎,直接将数据文件cp到低版本数据库上的数据目录下如果发生表格式冲突,或者是其他存储引擎的表,用mysqldump导出文本后导入低版本的数据库第18章Mysql日志管理18.
1错误日志:记录内容:包含了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息文件位置和格式:可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置.
如果没有给定file_name值,mysqld使用错误日志名host_name.
err并在数据目录中写入www.
163.
com网易技术部80日志文件18.
2BINLOG:记录内容:二进制日志包含了所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句.
语句以"事件"的形式保存,它描述数据更改文件位置和格式:当用--log-bin[=file_name]选项启动时,mysqld写入包含所有更新数据的SQL命令的日志文件.
如果未给出file_name值,默认名为-bin后面所跟的主机名.
如果给出了文件名,但没有包含路径,则文件被写入数据目录查看binlog内容:shell>mysqlbinloglog-file删除日志:RESETMASTER;//删除所有binlog日志,新日志编号从头开始PURGEMASTERLOGSTO'mysql-bin.
010';//删除mysql-bin.
010之前所有日志PURGEMASTERLOGSBEFORE'2003-04-0222:46:26';//删除2003-04-0222:46:26之前产生的所有日志相关选项:1.
--binlog-do-db=db_name告诉主服务器,如果当前的数据库(即USE选定的数据库)是db_name,应将更新记录到二进制日志中.
其它所有没有明显指定的数据库被忽略2.
--binlog-ignore-db=db_name告诉主服务器,如果当前的数据库(即USE选定的数据库)是db_name,不应将更新保存到二进制日志中要想记录或忽视多个数据库,使用多个选项,为每个数据库指定相应的选项.
www.
163.
com网易技术部813.
-innodb-safe-binlog使用此选项和sync_binlog=N(每写N次日志同步磁盘)全局变量将使得事务能够记录的更加安全4.
具有SUPER权限的客户端可以通过SETSQL_LOG_BIN=0语句禁止将自己的语句记入二进制记录18.
3查询日志记录内容:记录了客户端的所有语句,而二进制日志不包含只查询数据的语句文件位置和格式:用--log[=file_name]或-l[file_name]选项启动它.
如果没有给定file_name的值,默认名是host_name.
log.
18.
4慢查询日志:记录内容:记录包含所有执行时间超过long_query_time秒的SQL语句的日志文件.
获得初使表锁定的时间不算作执行时间.
文件位置和格式用--log-slow-queries[=file_name]选项启动它.
如果没有给出file_name值,默认为主机名,后缀为-slow.
log.
如果给出了文件名,但不是绝对路径名,文件则写入数据目录.
快速查看:使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志,例如:[zzx@bj37data]$mysqldumpslowbj37-slow.
log其他选项:在MySQL5.
1中,通过--log-slow-admin-statements服务器选项,你可以请求将慢管www.
163.
com网易技术部82理语句,例如OPTIMIZETABLE、ANALYZETABLE和ALTERTABLE写入慢查询日志第19章数据备份与恢复:19.
1备份/恢复策略:1.
要定期做mysql备份,并考虑系统可以承受的恢复时间.
2.
确保mysql打开log-bin,有了binarylog,mysql才可以在必要的时候做完整恢复,或基于时间点的恢复,或基于位置的恢复.
3.
要经常做备份恢复测试,确保备份是有效的,并且是可以恢复的.
19.
2冷备份:备份:1.
停掉mysql服务,在操作系统级别备份mysql的数据文件.
2.
重启mysql服务,备份重启以后生成的binlog.
恢复:1.
停掉mysql服务,在操作系统级别恢复mysql的数据文件.
2.
重启mysql服务,使用mysqlbinlog恢复自备份以来的binlog.
19.
3逻辑备份:备份:1.
选择在系统空闲时,比如在夜间,使用mysqldump–F(flush-logs)备份数据库.
mysqldump–uroot–p***pointcard–F>pointcard.
sql2.
并备份mysqldump开始以后生成的binlog.
恢复:1.
停掉应用,执行mysql导入备份文件.
mysql–uroot–p***pointcardselect*intooutfile'/tmp/order_tab'fields-terminated-by=','fromorder_tab;2.
方法2:mysqldump–uroot–p***–T/tmppointcardorder_tab--fields-terminated-by=',';恢复:1.
方法1:mysql>loaddata[local]infile'/tmp/order_tab'intotableorder_tabfields-terminated-by=',';2.
方法2:mysqlimport–uroot–p***[--local]pointcatdorder_tab.
txt--fields-terminated-by=',';注意:如果导入和导出是跨平台操作的(windows和linux),那么要注意设置参数line-terminated-by,windows上设置为line-terminated-by='\r\n',linux上设置为line-terminated-by='\n'.
19.
5使用备份工具ibbackup:ibbackup是innodb公司(www.
innodb.
com)的一个热备份工具,专门对innodb存储引擎进行物理热备份,此工具是收费的,不能免费使用.
现在innodb公司已经被oracle收购使用方法:编辑用于启动的配置文件my.
cnf和用于备份的配置文件my2.
cnfmy.
cnf的例子如下:[mysqld]datadir=/home/heikki/datawww.
163.
com网易技术部84innodb_data_home_dir=/home/heikki/datainnodb_data_file_path=ibdata1:10M:autoextendinnodb_log_group_home_dir=/home/heikki/dataset-variable=innodb_log_files_in_group=2set-variable=innodb_log_file_size=20M如果想备份到/home/heikki/backup,则my2.
cnf的例子如下:[mysqld]datadir=/home/heikki/backupinnodb_data_home_dir=/home/heikki/backupinnodb_data_file_path=ibdata1:10M:autoextendinnodb_log_group_home_dir=/home/heikki/backupset-variable=innodb_log_files_in_group=2set-variable=innodb_log_file_size=20M开始备份ibbackupmy.
cnfmy2.
cnf如果需要恢复,则进行日志重做ibbackup--apply-logmy2.
cnf恢复后重启数据库服务.
/bin/mysqld_saft--defaults-file=my2.
cnf&19.
6时间点恢复:1.
如果上午10点发生了误操作,可以用以下语句用备份和binglog将数据恢复到故障前:mysqlbinlog--stop-date="2005-04-209:59:59"/var/log/mysql/bin.
123456|mysql-uroot–pmypwd2.
跳过故障时的时间点,继续执行后面的binlog,完成恢复www.
163.
com网易技术部85mysqlbinlog--start-date="2005-04-2010:01:00"/var/log/mysql/bin.
123456|mysql-uroot-pmypwd\19.
7位置恢复:和时间点恢复类似,但是更精确,步骤如下:mysqlbinlog--start-date="2005-04-209:55:00"--stop-date="2005-04-2010:05:00"/var/log/mysql/bin.
123456>/tmp/mysql_restore.
sql该命令将在/tmp目录创建小的文本文件,编辑此文件,找到出错语句前后的位置号,例如前后位置号分别是368312和368315.
恢复了以前的备份文件后,你应从命令行输入下面内容:mysqlbinlog--stop-position="368312"/var/log/mysql/bin.
123456\|mysql-uroot-pmypwdmysqlbinlog--start-position="368315"/var/log/mysql/bin.
123456\|mysql-uroot-pmypwd\上面的第1行将恢复到停止位置为止的所有事务.
下一行将恢复从给定的起始位置直到二进制日志结束的所有事务.
因为mysqlbinlog的输出包括每个SQL语句记录之前的SETTIMESTAMP语句,恢复的数据和相关MySQL日志将反应事务执行的原时间.
19.
8MyISAM表修复:一张损坏的表的症状通常是查询意外中断并且能看到下述错误:"tbl_name.
frm"被锁定不能更改.
不能找到文件"tbl_name.
MYI"(Errcode:nnn).
文件意外结束.
记录文件被毁坏.
从表处理器得到错误nnn解决方法如下:www.
163.
com网易技术部86方法一:myisamchk-rtablename上面的方法几乎能解决所有问题,如果不行,则使用:myisamchk-otablename方法二:1)CHECKTABLEtbl_name[,tbl_name].
.
.
[option].
.
.
option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}2)REPAIR[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name].
.
.
[QUICK][EXTENDED][USE_FRM]第20章Mysql安全:20.
1正确设置目录权限:设置目录权限的原则是软件和数据分开,具体如下:1.
将mysql安装在单独的用户下2.
安装时,以root用户进行安装,mysql的软件默认都为root权限3.
安装完毕后,将数据目录权限设置为实际运行mysql的用户权限,比如:Chown–Rmysql:mysql/home/mysql/data20.
2尽量避免以root权限运行mysql:将4.
1的目录权限设置完毕后,启动、停止mysql以及日常的维护工作都可以在mysql用户下进行,没有必要su到root后再用—user=mysql来启动和关闭mysql,这样就没有必要授权维护人员root权限,而且最重要的一定是因为任何具有FILE权限的用户能够用root创建文件.
20.
3删除匿名帐号:安装完毕mysql后,会自动安装一个空帐号,普通用户只需要执行mysql命令即可登陆mysql,给系统造成隐患,建议删除此空帐号:www.
163.
com网易技术部87dropuser''@'localhost';dropuser''@'localhost.
localdomain';20.
4给mysqlroot帐号设置口令:Mysql安装完毕后,root默认口令为空,需要马上修改root口令:[zzx@localhostdata]$mysql–urootmysql>setpassword=password('123');QueryOK,0rowsaffected(0.
00sec)20.
5设置安全密码并定期修改:尽量使用安全密码,建议使用6位以上字母、数字、下画线和一些特殊字符组合而成的字符串20.
6只授予帐号必须的权限:只需要赋予普通用户必须的权限,比如:Grantselect,insert,update,deleteontablenameto'username'@'hostname';20.
7除root外,任何用户不应有mysql库user表的存取权限:如果拥有mysql库中user表的存取权限(select、update、insert、delete),就可以轻易的增加、修改、删除其他的用户权限,造成系统的安全隐患.
20.
8不要把FILE、PROCESS或SUPER权限授予管理员以外的帐号:FILE权限可以被滥用于将服务器主机上MySQL能读取的任何文件读入到数据库表中.
包括任何人可读的文件和服务器数据目录中的文件.
可以使用SELECT访问数据库表,然后将其内容传输到客户端上.
www.
163.
com网易技术部88不要向非管理用户授予FILE权限.
有这权限的任何用户能在拥有mysqld守护进程权限的文件系统那里写一个文件!
为了更加安全,由SELECT.
.
.
INTOOUTFILE生成的所有文件对每个人是可写的,并且你不能覆盖已经存在的文件.
file权限也可以被用来读取任何作为运行服务器的Unix用户可读取或访问的文件.
使用该权限,你可以将任何文件读入数据库表.
这可能被滥用,例如,通过使用LOADDATA装载"/etc/passwd"进一个数据库表,然后能用SELECT显示它.
PROCESS权限能被用来察看当前执行的查询的明文文本,包括设定或改变密码的查询.
SUPER权限能用来终止其它用户或更改服务器的操作方式.
比如kill进程不要将PROCESS或SUPER权限授给非管理用户.
mysqladminprocesslist的输出显示出当前执行的查询正文,如果另外的用户发出一个UPDATEuserSETpassword=PASSWORD('not_secure')查询,被允许执行那个命令的任何用户可能看得到20.
9loaddatalocal带来的安全问题:由MySQL服务器启动文件从客户端向服务器主机的传输.
理论上,打过补丁的服务器可以告诉客户端程序传输服务器选择的文件,而不是客户用LOADDATA语句指定的文件.
这样服务器可以访问客户端上客户有读访问权限的任何文件.
在Web环境中,客户从Web服务器连接,用户可以使用LOADDATALOCAL来读取Web服务器进程有读访问权限的任何文件(假定用户可以运行SQL服务器的任何命令).
在这种环境中,MySQL服务器的客户实际上是Web服务器,而不是连接Web服务器的用户运行的程序.
解决方法:可以用--local-infile=0选项启动mysqld从服务器端禁用所有LOADDATALOCAL命令.
www.
163.
com网易技术部89对于mysql命令行客户端,可以通过指定--local-infile[=1]选项启用LOADDATALOCAL,或通过--local-infile=0选项禁用.
类似地,对于mysqlimport,--localor-L选项启用本地数据文件装载.
在任何情况下,成功进行本地装载需要服务器启用相关选项.
20.
10尽量避免通过symlinks访问表:不要允许使用表的符号链接.
(可以用--skip-symbolic-links选项禁用).
如果你用root运行mysqld则特别重要,因为任何对服务器的数据目录有写访问权限的人则能够删除系统中的任何文件!
20.
11使用merge存储引擎潜藏的安全漏洞:Merge表在某些版本中可能存在以下安全漏洞:用户A赋予表T的权限给用户B用户B创建一个包含T的merge表,做各种操作用户A收回对T的权限安全隐患:用户B通过merge表仍然可以访问表A中的数据20.
12防止DNS欺骗:如果你不信任你的DNS,你应该在授权表中使用IP数字而不是主机名.
在任何情况下,你应该非常小心地使用包含通配符的主机名来创建授权表条目!
20.
13droptable命令并不收回以前的相关访问授权:drop表的时候,其他用户对此表的权限并没有被收回,这样导致重新创建同名的表时,以前其他用户对此表的权限会自动赋予,导致权限外流.
因此,要在删除表时,同时取消其他用户在此表上的相应权限.
20.
14使用SSL:下面列出了规定SSL、证书文件和密钥文件使用的选项.
它们可以位于命令行中或选项文件中.
www.
163.
com网易技术部90·--ssl对于服务器,该选项规定该服务器允许SSL连接.
对于客户端程序,它允许客户使用SSL连接服务器.
单单该选项不足以使用SSL连接.
还必须指定--ssl-ca、--ssl-cert和--ssl-key选项.
通常从反向使用该选项表示不应使用SSL.
要想实现,将选项指定为--skip-ssl或--ssl=0.
请注意使用--ssl不需要SSL连接.
例如,如果编译的服务器或客户不支持SSL,则使用普通的未加密的连接.
确保使用SSL连接的安全方式是使用含REQUIRESSL子句的GRANT语句在服务器上创建一个账户.
然后使用该账户来连接服务器,服务器和客户端均应启用SSL支持.
·--ssl-ca=file_name含可信SSLCA的清单的文件的路径.
·--ssl-capath=directory_name包含pem格式的可信SSLCA证书的目录的路径.
·--ssl-cert=file_nameSSL证书文件名,用于建立安全连接.
·--ssl-cipher=cipher_list允许的用于SSL加密的密码的清单.
cipher_list的格式与OpenSSLciphers命令相同.
示例:--ssl-cipher=ALL:-AES:-EXP·--ssl-key=file_namewww.
163.
com网易技术部91SSL密钥文件名,用于建立安全连接.
20.
15如果可能,给所有用户加上访问IP限制:给所有用户加上ip限制将拒绝所有未知的主机进行的连接,保证只有受信任的主机才可以进行连接.
例如:Grantselectondbname.
*to'username'@'ip'identifiedby'passwd';20.
16严格控制操作系统帐号和权限:在数据库服务器上要严格控制操作系统的帐号和权限,比如:锁定mysql用户其他任何用户都采取独立的帐号登陆,管理员通过普通用户管理mysql;或者通过rootsu到mysql用户下进行管理.
禁止修改mysql用户下的任何资源20.
17增加防火墙:购买防火墙.
这样可以保护你防范各种软件中至少50%的各种类型的攻击.
把MySQL放到防火墙后或隔离区(DMZ).
20.
18其他安全设置选项:20.
18.
1allow-suspicious-udfs:该选项控制是否可以载入主函数只有xxx符的用户定义函数.
默认情况下,该选项被关闭,并且只能载入至少有辅助符的UDF.
这样可以防止从未包含合法UDF的共享对象文件载入函数.
20.
18.
2old-passwords:强制服务器为新密码生成短(pre-4.
1)密码哈希.
当服务器必须支持旧版本客户端程序时,为了保证兼容性这很有用.
www.
163.
com网易技术部9220.
18.
3safe-user-create:如果启用,用户不能用GRANT语句创建新用户,除非用户有mysql.
user表的INSERT权限.
如果你想让用户具有授权权限来创建新用户,你应给用户授予下面的权限:mysql>GRANTINSERT(user)ONmysql.
userTO'user_name'@'host_name';这样确保用户不能直接更改权限列,必须使用GRANT语句给其它用户授予该权限.
20.
18.
4secure-auth:不允许鉴定有旧(pre-4.
1)密码的账户.
20.
18.
5skip-grant-tables:这个选项导致服务器根本不使用权限系统.
这给每个人以完全访问所有的数据库的权力!
(通过执行mysqladminflush-privileges或mysqladminreload命令,或执行FLUSHPRIVILEGES语句,你能告诉一个正在运行的服务器再次开始使用授权表.
)20.
18.
6skip-networking:在网络上不允许TCP/IP连接.
所有到mysqld的连接必须经由Unix套接字进行20.
18.
7skip-show-database:使用该选项,只允许有SHOWDATABASES权限的用户执行SHOWDATABASES语句,该语句显示所有数据库名.
不使用该选项,允许所有用户执行SHOWDATABASES,但只显示用户有SHOWDATABASES权限或部分数据库权限的数据库名.
请注意全局权限指数据库的权限.
www.
163.
com网易技术部93第21章Mysql复制:21.
1Mysql复制概述:复制是将主数据库的DML操作通过日志传到从服务器上,使得从服务器实现了对主服务器的远程备份,并且可以通过应用使得在主服务器繁忙的时候分担一部分负载.
mysql支持同时向多台从服务器进行复制.
缺点:不能保证主从同步,只能实现异步复制.
21.
2安装配置:1.
正常安装主从服务器.
确保主服务器开启log-bin,主从服务器的server_id取不同的数字.
2.
在主服务器上,设置一个复制账户,并授予REPLICATIONSLAVE权限:mysql>GRANTREPLICATIONrepON*.
*TO'rep'@'slave_host'IDENTIFIEDBY'repl_pass';3.
在主服务器上,设置读锁定有效:mysql>FLUSHTABLESWITHREADLOCK;然后得到主服务器上当前的二进制日志名和偏移量值:mysql>SHOWMASTERSTATUS;-------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|-------+|bj72-bin.
000013|27050310|||-------+4.
在从服务器上,做相应设置:mysql>CHANGEMASTERTO->MASTER_HOST='master_host',www.
163.
com网易技术部94->MASTER_PORT=master_port,->MASTER_USER='rep',->MASTER_PASSWORD='rep_pass',->MASTER_LOG_FILE='bj72-bin.
000013',->MASTER_LOG_POS=27050310;5.
在从服务器上,启动slave线程:mysql>STARTSLAVE;这时slave上执行showprocesslist命令将显示类似如下进程:10436|systemuser.
.
.
|Waitingformastertosendevent|NULL这表明slave已经连接上master,并开始接受并执行日志.
6.
在主服务器上,重置读锁定:mysql>UNLOCKTABLES;21.
3日常管理维护:21.
3.
1经常查看slave状态mysql>SHOWSLAVESTATUS\G;.
.
.
.
.
Slave_IO_Running:YesSlave_SQL_Running:Yes.
.
.
.
.
.
1rowinset(0.
00sec)主要检查Slave_IO_Running和Slave_SQL_Running这两个进程状态是否是yes,这两个进程的含义如下:Slave_IO_Running:此进程负责slave从master服务器上读取binlog日志,并写入slave服务器上的中继日志中Slave_SQL_Running:此进程负责读取并且执行中继日志中的binlog日志只要其中有一个进程的状态是no,则表示复制进程停止,错误原因可以从Last_Errno后面看到www.
163.
com网易技术部9521.
3.
2怎样强制主服务器阻塞更新直到从服务器同步可以使用下面的步骤:1.
在主服务器上,执行这些语句:mysql>FLUSHTABLESWITHREADLOCK;mysql>SHOWMASTERSTATUS;记录SHOW语句的输出的日志名和偏移量.
这些是复制坐标.
2.
在从服务器上,发出下面的语句,其中Master_POS_WAIT()函数的参量是前面步骤中的得到的复制坐标值:mysql>SELECTMASTER_POS_WAIT('log_name',log_offset);SELECT语句阻塞直到从服务器达到指定的日志文件和偏移量.
此时,从服务器与主服务器同步,语句返回.
3.
在主服务器上,发出下面的语句允许主服务器重新开始处理更新:mysql>UNLOCKTABLES;21.
3.
3master执行的语句在slave上执行失败怎么办1.
确定是否从服务器的表与主服务器的不同.
尽力了解发生的原因.
然后让从服务器的表与主服务器的一样并运行STARTSLAVE.
2.
如果前面的步骤不工作或不适合,尽力了解手动更新是否安全(如果需要),然后忽视来自主服务器的下一个语句.
如果你确定可以跳过来自主服务器的下一个语句,执行下面的语句:mysql>SETGLOBALSQL_slave_SKIP_COUNTER=n;mysql>STARTSLAVE;如果来自主服务器的下一个语句不使用AUTO_INCREMENT或LAST_INSERT_ID(),n值应为1.
否则,值应为2.
使用AUTO_INCREMENT或LAST_INSERT_ID()的语句使用值2的原因是它们从主服务器的二进制日志中取两个事件.
21.
3.
4Slave上出现logevententryexceededmax_allowed_packet错误怎么办在主从服务器上增加max_allowed_packet参数的大小:www.
163.
com网易技术部96mysql>SET@@global.
max_allowed_packet=16777216;同时在my.
cnf里,设置max_allowed_packet=16M,保证下次重新启动后继续有效.
21.
3.
5多主复制时,自动增长变量的冲突问题在单主复制时,系统参数auto_increment_increment和auto_increment_offset可以采用默认设置,但是多主复制时,要定制auto_increment_increment和auto_increment_offset的设置,保证多主之间不会有重复冲突.
比如两个master的情况可以如下设置:Master1上:auto_increment_increment=2,auto_increment_offset=1;(1,3,5,7…序列)Master2上:auto_increment_increment=2,auto_increment_offset=0;(0,2,4,6…序列)21.
3.
6怎么样知道slave上现在复制到什么地方了可以查看SHOWSLAVESTATUS语句的Seconds_Behind_Master列的结果21.
4需要注意的问题:第22章MysqlCluster:22.
1MysqlCluster概述:MySql自4.
1.
x开始推出MySqlCluster功能(NDB引擎),开始NDB引擎只是支持基于内存的数据表,到现在5.
1beta版时,开始支持基于磁盘的数据表.
理论上,MySqlCluster通过数据的分布式存储和可扩展的系统架构,可以满足更大规模的应用;通过冗余策略,大大提高了系统的可靠性和数据的有效性.
虽然早在5.
0时就有公司将MySqlCluster用于正式生产环境,但是更多的测试(包括我们自己的测试)表明,MySqlCluster在性能和可靠性上还有待于完善,我们期待MySql5.
1正式版发布时,MySqlCluster在性能和可靠性上能够有重大的改进.
www.
163.
com网易技术部9722.
2MysqlCluster架构:mysqlcluster由三部分构成:管理节点管理节点需要config.
ini文件,该文件通知节点有多少需要维护的副本,需要在每个数据节点上为数据和索引分配多少内存,数据节点的位置,在每个数据节点上保存数据的磁盘位置,以及SQL节点的位置.
管理节点只能有一个,要求配置不高.
sql节点前台通过sql节点来访问mysqlcluster的数据节点里面的数据.
可以有多个sql节点,通过每个sql节点查询到的数据都是一致的.
而且sql节点性能越好.
数据节点用来存放cluster里面的数据,可以有多个数据节点.
每个数据节点可以有多个镜像copy这三种节点物理上可以在不同的服务器上,也可以在同一台服务器上22.
3安装配置:22.
3.
1管理节点配置步骤:mkdir/var/lib/mysql-clustercd/var/lib/mysql-clusterviconfig.
ini对于我们的典型设置,config.
ini文件应类似于:[NDBDDEFAULT]NoOfReplicas=2#每个数据节点的镜像数量DataMemory=500M#HowmuchmemorytoallocatefordatastorageIndexMemory=300M#Howmuchmemorytoallocateforindexstorage[TCPDEFAULT]portnumber=2202#数据节点的默认连接端口[NDB_MGMD]#配置管理节点www.
163.
com网易技术部98id=1hostname=192.
168.
7.
187datadir=/home/zzx2/mysql-cluster[NDBD]id=2HostName=192.
168.
7.
187DataDir=/home/zzx2/mysql/data[NDBD]id=3hostname=192.
168.
7.
55datadir=/home/zzx/mysql/data[NDBD]id=4HostName=192.
168.
7.
187DataDir=/home/zzx3/mysql/data[NDBD]id=5hostname=192.
168.
7.
55datadir=/home1/zzx1/mysql/data[MYSQLD]hostname=192.
168.
7.
187[MYSQLD]hostname=192.
168.
7.
55[MYSQLD]#Optionsformysqldprocess:其中,每个节点都要有一个独立的id号,具体说明如下:[NDB_MGMD]表示管理节点的配置,只能有一个[NDBD]表示数据节点的配置,可以有多个[MYSQLD]表示sql节点的配置,可以有多个.
也可以是空节点www.
163.
com网易技术部9922.
3.
2sql节点和数据节点的配置:#Optionsformysqldprocess:[MYSQLD]ndbcluster#runNDBenginendb-connectstring=192.
168.
0.
10#locationofMGMnode#Optionsforndbdprocess:[MYSQL_CLUSTER]ndb-connectstring=192.
168.
0.
10#locationofMGMnode22.
4管理维护:22.
4.
1Cluster的启动在管理主机上,从系统shell发出下述命令以启动MGM节点进程:shell>ndb_mgmd-f/var/lib/mysql-cluster/config.
ini在每台数据节点主机上,对于首次启动,运行下述命令启动NDBD进程:shell>ndbd--initial--ndb-connectstring=192.
168.
7.
187:1186注意,仅应在首次启动ndbd时,或在备份/恢复或配置变化后重启ndbd时使用"--initial"参数,这很重要.
原因在于,该参数会使节点删除由早期ndbd实例创建的、用于恢复的任何文件,包括恢复用日志文件sql节点顺序启动mysql服务node1:.
/bin/mysqld_safe&.
.
.
.
.
.
noden:.
/bin/mysqld_safe&在任何一个sql节点运行mysql客户端,即可以连接到mysqlcluster数据库注意:创建表必须用engine=ndb选项来使得新创建的表是ndb表.
否则数据将不会存储在ndb数据节点中,并且其他sql节点将看不到此表www.
163.
com网易技术部10022.
4.
2Cluster的关闭shell>ndb_mgm-eshutdown22.
5数据备份和恢复:备份步骤:启动管理服务器(ndb_mgm)执行命令:startbackup管理服务器回复"备份backup_id开始",其中,backup_id是该备份的唯一ID(如果未作其他配置,该ID还将保存在簇日志中).
管理服务器发出消息"备份backup_id完成",通知备份操作已结束.
备份路径:$MYSQL_HOME/data/BACKUP/BACKUP-备份ID恢复步骤:1.
进入单用户模式(先运行ndb_mgm)NDB>ENTERSINGLEUSERMODE52.
ndb_restore-b2-n2-chost=192.
168.
7.
187:1186-m-r/home/zzx2/mysql/data/BACKUP/BACKUP-2b:备份idn:节点idm:恢复表定义r:恢复路径注意:-m参数是恢复表定义使用,只需要第一个节点带此参数就可以,否则会报错:TableorindexwithgivennamealreadyexistsRestore:Failedtorestoretable:cluster/def/NDB$BLOB_2_3.
.
.
Exiting3.
退出单用户模式NDB>EXITSINGLEUSERMODEwww.
163.
com网易技术部101第23章Oracle向Mysql数据迁移:23.
1数据类型的差异:在迁移oracle数据到mysql数据库时,首先要清楚2个数据类型的差异,并且在迁移前确定oracle中的数据类型在mysql数据库中使用什么样的数据类型来替换最为合适,在转换时对一些不确定的数据类型可以通过必要的测试来进行检测和确认.
这里给出了常用的几种数据类型的对照:另外,可以通过MysqlMigrationToolkit工具,将oracle的数据类型转换为mysql数据类型.
这个工具在mysql网站上提供下载.
但是此工具目前只有windows版本.
直接使用MysqlMigrationToolkit工具将oracle数据导入到mysql数据库,会出现很多问题,建议对存在大量clob字段的数据最好不要使用此工具进行数据的导入.
23.
2利用导出文本迁移:23.
2.
1导出为insertsql文本生成sql文件,直接使用mysql命令进行导入.
Oracle数据类型mysql数据类型DatedatetimeCharcharvarchar2varcharClobtext最大长度为65,535(216–1)字符的TEXT列.
NumberMEDIUMINT或INT或DECIMAL--针对带有浮点数的数据www.
163.
com网易技术部102例如:使用oracle的select语句生成sql文件SQL>spooltest.
sqlsetheadoffsetpagesize0setrecsepOFFsetwrapoffsetfeedbackoffsetlinesize200settermoutoffsettrimspoolonselect'insertintotestvalues('||id||');'fromtest;spooloff;传输要导入的sql文件到mysql服务器执行下列命令导入:mysql-uroottestUSEtest;mysql>LOADDATAINFILE'/home/bjguan/tt.
txt'INTOTABLEtest.
ttFIELDSTERMINATEDBY'\t';或mysql-e"LOADDATAINFILE'/home/bjguan/tt.
txt'INTOTABLEttFIELDSTERMINATEDBY'\t'"test-urootwww.
163.
com网易技术部10323.
3利用工具软件迁移:Mysql官方有自己开发的迁移工具MysqlMigrationToolkit,但是在实际迁移过程中发现,此工具对于转义字符的处理问题很多,对于含有varchar和clob的字段经常出错,因此不建议使用23.
4使用DBA组开发的迁移工具:为了解决迁移过程中出现的各种问题,dba组开发了一个迁移工具,主要功能是对oracle的数据进行导出,导出的时候,对各种转义字符都做了相应处理,将导出的文件直接loaddatainfile到目标表即可.
使用步骤:1、服务器上必须安装jre,如果用oci连接,则还要有oracle客户端2、将中的包和类加入classpathjavaMigration'连接串''目标路径''字段分隔符''记录分隔符''table1,table2.
.
.
.
.
'feedbackBigfeedbackSmall其中:Migration为类名称连接串为oracle的jdbc连接字符串,如果为thin连接,用以下格式:jdbc:oracle:thin:@ip:port:dbname,username,passwd如果为oci连接,则格式为:jdbc:oracle:oci8:@tns目标路径为要生成的文件要保存到的目标目录,例如/home/zzx/,后面的/一定要写啊,在目标目录下一个表产生一个文件字段分隔符为一个字符,例如!
记录分隔符为一个字符,李然#table1、table2表示导出多个表,多个表之间用逗号分隔开,feedbackBig/small分别指不包含/包含clob字段的表每次写入文件的记录条数,如果出现内存溢出,则把相应参数调小,对小表,则可以增大相应参数,使得导出更快!
后三个参数都可以不写,默认值分别为导出全部表,50000和3000举一个完整的例子:www.
163.
com网易技术部104javaMigration'jdbc:oracle:thin:@202.
106.
168.
88:1521:riji,user,passwd''/home/yellowpage/zzx/javaMigration'jdbc:oracle:oci8:@riji,user,passwd''/home/yellowpage/zzx/3、生成的文件大家直接在mysql服务器上运行load命令即可导入23.
5数据迁移常见问题:23.
5.
1字符集问题:从oracle导出时的客户端字符集要等于要导入的mysql数据库字符集以保证数据的一致性:Oracle:NLS_LANGMYSQL:default-character-set23.
5.
2特殊字符处理:导入过程中出错很重要的一点是对转义字符的处理不正确而出错,在dba组开发的迁移程序中,对所有字符串中含有我们定义的域分隔符、行分隔符和转义字符'\'的字符,全部加上了转义.
这样,我们就可以保证我们定义的分隔符可以正常发挥作用.
在loaddata语法中,虽然也可以手工指定各种分隔符和转义字符,但是如果导出文件的字符中含有和指定分隔符相同的字符时,会经常出错.
23.
5.
3日期字段的处理:Oracle中导出日期的格式,一定要和mysql支持的格式一致,否则导入的时候会出错,mysql支持的日期格式如下:常见格式指定DATETIME、DATE和TIMESTAMP值:'YYYY-MM-DDHH:MM:SS'或'YY-MM-DDHH:MM:SS'格式的字符串.
允许"不严格"语法:任何标点符都可以用做日期部分或时间部分之间的间割符.
例如,'98-12-3111:30:45'、'98.
12.
3111+30+45'、'98/12/3111*30*45'和'98@12@3111^30^45'是等价的.
www.
163.
com网易技术部105'YYYY-MM-DD'或'YY-MM-DD'格式的字符串.
这里也允许使用"不严格的"语法.
例如,'98-12-31'、'98.
12.
31'、'98/12/31'和'98@12@31'是等价的.
'YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式的没有间割符的字符串,假定字符串对于日期类型是有意义的.
例如,'19970523091528'和'970523091528'被解释为'1997-05-2309:15:28',但'971122129015'是不合法的(它有一个没有意义的分钟部分),将变为'0000-00-0000:00:00'.
'YYYYMMDD'或'YYMMDD'格式的没有间割符的字符串,假定字符串对于日期类型是有意义的.
例如,'19970523'和'970523'被解释为'1997-05-23',但'971332'是不合法的(它有一个没有意义的月和日部分),将变为'0000-00-00'.
YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字,假定数字对于日期类型是有意义的.
例如,19830905132800和830905132800被解释为'1983-09-0513:28:00'.
YYYYMMDD或YYMMDD格式的数字,假定数字对于日期类型是有意义的.
例如,19830905和830905被解释为'1983-09-05'.
23.
5.
4如何使迁移过程不被SQL错误中断:启动mysql客户端进行连接的时候加上-f,-f可以强制出错的时候,记录错误但不终止load过程;--show-warnings显示出错原因23.
5.
5如何查找产生warnings的原因:启动mysql客户端的时候加上-v--show-warnings参数,-v显示出错的数据.
可以用tee工具将load过程中的错误详细记录在文本中.
第24章应急处理:24.
1一般处理流程:如果数据库在运行过程中出现任何异常,一般按照如下步骤解决:查看错误日志.
错误日志一般放在数据库的data目录下www.
163.
com网易技术部106通过perror工具查看错误号,判断错误出现的原因寻找解决方案24.
2忘记root密码:如果你忘记了MySQL的root用户的口令,你可以用下列过程恢复它.
通过发送一个kill(不是kill-9)到mysqld服务器来关闭mysqld服务器.
pid被保存在一个.
pid文件中,通常在MySQL数据库目录中(你必须是一个UNIXroot用户或运行服务器的相同用户做这个):kill`cat/mysql-data-directory/hostname.
pid`使用--skip-grant-tables选项重启mysqld.
用mysql-hhostnamemysql连接mysqld服务器并且用一条GRANT命令改变口令.
你也可以用mysqladmin-hhostname-uuserpassword'newpassword'进行.
用mysqladmin-hhostnameflush-privileges或用SQL命令FLUSHPRIVILEGES来装载权限表24.
3表损坏如何处理:一张损坏的表的症状通常是查询意外中断并且能看到下述错误:"tbl_name.
frm"被锁定不能更改.
不能找到文件"tbl_name.
MYI"(Errcode:nnn).
文件意外结束.
记录文件被毁坏.
从表处理器得到错误nnn解决方法如下:方法一:myisamchk-rtablename上面的方法几乎能解决所有问题,如果不行,则使用:myisamchk-otablenamewww.
163.
com网易技术部107方法二:CHECKTABLEtbl_name[,tbl_name].
.
.
[option].
.
.
option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}REPAIR[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name].
.
.
[QUICK][EXTENDED][USE_FRM]24.
4MyISAM表超过4G无法访问:mysql5.
0以前的中的myisam存储引擎默认的表大小只支持到4G,如果大于4G,可以执行以下命令来扩大表的存储能力:altertableweblogentryMAX_ROWS=1000000000AVG_ROW_LENGTH=15000;执行以下命令可以查看更改前后的表状态:myisamchk-dvtablename其中:Maxdatafilelength:4294967294Maxkeyfilelength:4398046510079表明了本表实际支持的最大MYDsize和最大MYIsize24.
5数据目录磁盘空间不足怎么办如果建表前时候预测到data目录下的空间不足,则在建表时用如下选项指定数据目录和索引目录到磁盘充足的空间:DATADIRECTORY='absolutepathtodirectory'INDEXDIRECTORY='absolutepathtodirectory'如果表已经创建,则可以将表的数据文件和索引文件mv到磁盘充足的分区上,然后在原文件处创建符号链接即可.
当然,mv前最好停机或者将表锁定,以防止表的更改.
www.
163.
com网易技术部10824.
6如何禁止DNS反向解析showprocesslist命令出现了非常多个进程,但是这些进程很奇怪,类似于:unauthenticateduser|192.
168.
5.
71:57857|NULL|Connect|NULL|login|NULL这些并不是我们正常的进程,原来这是mysql的一个bug,是由于反复解析ip和dns造成的,启动的时候加上--skip-name-resolve选项就可以避免第25章Mysql管理中一些常用的命令和技巧:25.
1参数设置方法:1)如果对服务器参数不熟悉,建议从$MYSQL_HOME/support-files下面按照需要cp合适的配置文件为数据库配置文件,例如:cpmy-large.
cnf/etc/my.
cnf2)session级修改(只对本session有效):setpara_name=value;3)全局级修改(对所有新的连接都有效,但是数据库重启后失效)setglobalpara_name=value;4)永久修改将参数在my.
cnf中增加或者修改25.
2mysql.
sock丢失后怎么连接数据库请注意,如果你指定localhost作为一个主机名,mysqladmin默认使用Unix套接字文件连接,而不是TCP/IP.
从MySQL4.
1开始,通过--protocol=TCP|SOCKET|PIPE|MEMORY}选项,你可以显示地指定连接协议,举例如下:socket连接:[zzx@zzxmysql]$mysql-urootERROR2002(HY000):Can'tconnecttolocalMySQLserverthroughsocket'/home/zzx/mysql/mysql.
sock'(2)tcp连接:www.
163.
com网易技术部109[zzx@zzxmysql]$mysql--protocol=TCP-uroot-p-P3307-hlocalhostEnterpassword:WelcometotheMySQLmonitor.
Commandsendwith;or\g.
YourMySQLconnectionidis73toserverversion:5.
0.
15-standardType'help;'or'\h'forhelp.
Type'\c'toclearthebuffer.
mysql>25.
3同一台机器运行多个mysql:最简单的方法,将每个mysql安装在不同的用户下面,例如mysql1和mysql2,每个用户下面,分别执行如下操作:exportMYSQL_HOME=/home/mysql1/mysqlshell>groupaddmysqlshell>useradd-gmysqlmysql1shell>cd/home/mysql1shell>tar-xzvf/home/mysql1/mysql-VERSION-OS.
tar.
gzshell>ln-smysql-VERSION-OS.
tar.
gzmysqlshell>cdmysqlcpsupport-files/my-large.
cnf(根据实际情况选择).
/my.
cnfvimy.
cnf,主要修改[client]和[mysqld]下面的port和socket,并指定字符集,例如:[client]port=3307socket=/home/mysql1/mysql/data/mysql.
sock#TheMySQLserver[mysqld]default-character-set=utf8port=3307socket=/home/mysql1/mysql/data/mysql.
sock.
.
.
.
.
.
shell>scripts/mysql_install_db--user=mysql1www.
163.
com网易技术部110shell>chown-Rroot:mysql.
shell>chown-Rmysql1:mysqldatashell>bin/mysqld_safe--user=mysql&mysql2用户执行的和mysql1类似,不同的是指定不同的MYSQL_HOME,不同的port、socket即可25.
4查看用户权限:怎么样查看用户权限,最简单的方法,通过如下语句:mysql>showgrantsfor'test1'@'localhost';如果要通过权限表来查看,比较复杂:在5.
0以下,按照以下顺序来查看:user->db->tables_priv->columns_priv,权限范围依次递减.
和参数的设置不同,权限设置的原则是:全局权限覆盖局部权限首先,从user表中查看user和host对应的那些权限值,比如:select_priv="Y"说明此用户组具有对所有数据库的所有表的select权限,此时,再单独对某个数据库设置select权限已经没有意义如果user表中的select_priv="N",则接着查看db表中对应用户组的权限,如果存在一条记录如下:Host|Db|User|Select_priv|Insert_priv|Update_priv|Delete_priv|Create_priv|Drop_priv|Grant_priv|References_priv|Index_priv|Alter_priv|Create_tmp_table_priv|Lock_tables_priv||localhost|test2|test1|Y|Y|Y|Y|Y|Y|N|Y|Y|Y|Y|Y|www.
163.
com网易技术部111则表示test1@localhost用户组对test2数据库中的所有表具有所有权限(除了grant),此时单独对此数据库内的表进行权限设置已经没有意义;如果没有此记录或者对应权限不是"N",则接着查询tables_priv表,此表中的记录决定了对数据库中实际表的权限;如果tables_priv内记录的权限都是Y,则对表内的任何列单独设置权限已经没有意义,如果tables_priv没有对应表的记录或者对应权限不是"N",则接着查询columnss_priv表的记录.
一步一般类推,最后得出某个用户组的权限.
在mysql5.
0以后,多了一个数据字典库information_schema,通过这个库里面的USER_PRIVILEGES、SCHEMA_PRIVILEGES、TABLE_PRIVILEGES、COLUMN_PRIVILEGES表可以得到同样的结论.
25.
5修改用户密码:方法1:可以用mysqladmin命令在命令行指定密码:shell>mysqladmin-uuser_name-hhost_namepassword"newpwd"方法2:为账户赋予密码的另一种方法是执行SETPASSWORD语句:mysql>SETPASSWORDFOR'jeffrey'@'%'=PASSWORD('biscuit');如果是更改自己的密码,可以省略for语句:mysql>SETPASSWORD=PASSWORD('biscuit');方法3:你还可以在全局级别使用GRANTUSAGE语句(在*.
*)来指定某个账户的密码而不影响账户当前的权限:mysql>GRANTUSAGEON*.
*TO'jeffrey'@'%'IDENTIFIEDBY'biscuit';方法4:直接更改数据库的user表:shell>mysql-urootmysqlmysql>INSERTINTOuser(Host,User,Password)->VALUES('%','jeffrey',PASSWORD('biscuit'));mysql>FLUSHPRIVILEGES;www.
163.
com网易技术部112shell>mysql-urootmysqlmysql>UPDATEuserSETPassword=PASSWORD('bagel')->WHEREHost='%'ANDUser='francis';mysql>FLUSHPRIVILEGES;注意:更改密码时候一定要使用password函数(mysqladmin和grant两种方式不用写,会自动加上)25.
6怎样灵活的指定连接的主机:在user表Host值的指定方法:Host值可以是主机名或IP号,或'localhost'指出本地主机.
你可以在Host列值使用通配符字符"%"和"_".
Host值'%'匹配任何主机名,空Host值等价于'%'.
它们的含义与LIKE操作符的模式匹配操作相同.
例如,'%'的Host值与所有主机名匹配,而'%.
mysql.
com'匹配mysql.
com域的所有主机.
25.
7到底匹配哪个符合条件的用户:例如以下两个用户:'thomas.
loc.
gov''fred'fred,从thomas.
loc.
gov连接'%''fred'fred,从任何主机连接当从主机thomas.
loc.
gov进行连接的时候,上面两个用户显然都满足条件,该选择哪个呢如果有多个匹配,服务器必须选择使用哪个条目.
按照下述方法解决问题:服务器在启动时读入user表后进行排序.
然后当用户试图连接时,以排序的顺序浏览条目服务器使用与客户端和用户名匹配的第一行.
当服务器读取表时,它首先以最具体的Host值排序.
主机名和IP号是最具体的.
'%'意味着"任何主机"并且是最不特定的.
有相同Host值的条目首先以最具体的User值排序(空User值意味着"任何用户"并且是最不特定的).
例如下例是www.
163.
com网易技术部113排序前和排序后的结果:|Host|User|…|%|root|…|%|jeffrey|…|localhost|root|…|localhost||…排序前|Host|User|…|localhost|root|….
.
.
|localhost|%|jeffrey|….
.
.
|%|root|….
.
.
排序后·记住:明确指定用户名的用户不一定是被匹配的用户25.
8不进入mysql,怎样运行sql语句使用--execute(-e)选项:mysql-uroot-p-e"SELECTUser,HostFROMUser"mysql可以按这种方式传递多个SQL语句,用分号隔开:shell>mysql-uroot-p-e"SELECTNameFROMCountryWHERENameLIKE'AU%';SELECTCOUNT(*)FROMCity"worldEnterpassword:******www.
163.
com网易技术部114|Name||Australia||Austria||COUNT(*)||4079|请注意长形式(--execute)后面必须紧跟一个等号(=).
25.
9客户端怎么访问内网数据库oracle的客户端可以通过cman来访问内网中的oralce数据库,mysql能实现类似功能吗可以,假设服务器如下:中转服务器ip:202.
108.
15.
160(192.
168.
161.
38)内网服务器ip:192.
168.
161.
39,在端口3306上起着mysql服务客户端:windows,secureCRT在中转服务器上增加sshturnal,具体操作如下:1.
点击session的属性2.
点击connection->port_forwarding3.
点击add按钮:name中随便起个名字;local下的ip写上127.
0.
0.
1,port随便起一个未使用的port,例如9999;remote下面的的hostname写上192.
168.
161.
39,端口写上3306;点击ok设置成功在192.
168.
161.
39的mysql内增加一个用户test,host设置为192.
168.
161.
38grantselectondbname.
*totest@192.
168.
161.
38identifiedby'123';客户端执行mysql-h127.
0.
0.
1-P3306-utest-p123,连接成功