关于MySQL的一些小见解

1.麻雀虽小五脏俱全，MySQL虽然以简单著称，但其内部结构并不简单。相比于之前的版本，您觉得MySQL 5.5引入了哪些重要的新特性？如何做好SQL语句的优化？如何合理利用索引？ MySQL 5.5引入了哪些重要的新特性？ (1) 1. 默认存储引擎更改为InnoDB。 (2) 多核性能提升 Metadata Locking (MDL) Framework替换LOCK_open mutex (lock)，使得MySQL5.1及过去版本在多核心处理器上的性能瓶颈得到解决，官方表示将继续增强对MySQL多处理器支持，直至MySQL性能 “不受处理器数量的限制” (3) 复制功能(Replication)加强 MySQL复制特性是互联网公司应用非常广泛的特性，作为MySQL最实用最简单的扩展方式，过去的异步复制方式已经有些不上形势，对某些用户 来说“异步复制”意味着极端情况下的数据风险，MySQL5.5将首次支持半同步(semi-sync replication)在MySQL的高可用方案中将产生更多更加可靠的方案。另外Slave fsync tunning;Relay log corruption recovery和Replication Heartbeat也将实现 (4) 增强表分区功能 MySQL 5.5的分区对用户绝对是个好消息，更易于使用的增强功能，以及TRUNCATE PARTITION命令都可以为DBA节省大量的时间，有时对最终用户亦如此:         1) 非整数列分区：任何使用过MySQL分区的人应该都遇到过不少问题，特别是面对非整数列分区时，MySQL 5.1只能处理整数列分区，如果你想在日期或字符串列上进行分区，你不得不使用函数对其进行转换。很麻烦，而MySQL 5.5中新增了两类分区方法，RANG和LIST分区法，同时在新的函数中增加了一个COLUMNS关键词。在MySQL 5.1中使用分区另一个让人头痛的问题是date类型(即日期列)，你不能直接使用它们，必须使用YEAR或TO_DAYS转换这些列，但在MySQL 5.5中情况发生了很大的变化，现在在日期列上可以直接分区，并且方法也很简单; 　　2) 多列分区：COLUMNS关键字现在允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区; 　　3) 可用性增强：truncate分区。分区最吸引人的一个功能是瞬间移除大量记录的能力，DBA都喜欢将历史记录存储到按日期分区的分区表中，这样可以定期 删除过时的历史数据。 但当你需要移除分区中的部分数据时，事情就不是那么简单了，删除分区没有问题，但如果是清空分区，就很头痛了，要移除分区中的所有 数据，但需要保留分区本身，你可以：使用DELETE语句，但我们知道DELETE语句的性能都很差。使用DROP PARTITION语句，紧跟着一个EORGANIZE PARTITIONS语句重新创建分区，但这样做比前一个方法的成本要高出许多。MySQL 5.5引入了TRUNCATE PARTITION，它和DROP PARTITION语句有些类似，但它保留了分区本身，也就是说分区还可以重复利用。TRUNCATE PARTITION应该是DBA工具箱中的必备工具; 　　4) 更多微调功能：TO_SECONDS：分区增强包有一个新的函数处理DATE和DATETIME列，使用TO_SECONDS函数，你可以将日期/时间列转换成自0年以来的秒数，如果你想使用小于1天的间隔进行分区，那么这个函数就可以帮到你。 (5)Insert Buffering 如果在buffer pool中没找到数据，那么直接buffer起来，避免额外的IO;Delete & Purge Buffering 跟插入一样，如果buffer pool中没有命中，先buffer起来，避免额外的IO。 (6) Support for Native AIO on Linux 如何做好SQL语句的优化？ (1)字段类型尽量用int或者tinyint类型，另外varchar字段尽量用''取代null。 (2)select * 尽量少用，你想要什么字段 就select 什么字段出来 不要老是用* 号！同理，只要一行数据时尽量使用 LIMIT 1 (3)尽量避免使用order by rand()，因为这个可能会导致mysql的灾难。 (4)每个表应该设置一个ID主键，最好是int类型 (5)对于写，尽量用简单的sql，严禁批量insert，update，以及delete操作。 (6)分页的时候，先查询第一条的主键id比如是78654，然后再where之后用id>78654。 (7)不要用永久链接 mysql_pconnect()；除非你真的非常肯定你的程序不会发生意外，不然很可能也会导致你的mysql死掉。 (8)尽量用 union all 代替 union。 (9)尽量用exists取代in，用join取代子查询。 (10)尽量优先优化高并发的 SQL，而不是执行频率低某些“大”SQL。 (11)还可以通过慢查询日志分析，sql执行监控等手段去进一步筛选可优化的sql。 如何合理利用索引？ (1)不设置外键，在经常进行join的字段上建立索引 (2)在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。 (3)在值唯一性越高的列上建立索引，比如唯一性最高的主键。 (4)如果group以及sort的列有多个，那么就建立组合索引。 (5)使用percona的工具来check索引的有效性，如果失效就可以rebiuld它 (6)在字段建立索引最后都可以自动加上主键id字段(如果id是自增字段) 2.淘宝为什么要放弃Oracle，而选择MySQL呢？大家平常都用MySQL来干些什么事情？ 根据taobao内部DBA的消息，主要是阿里巴巴想建立一套属于自己的系统，不再依赖于外部服务提供商，mysql免费只是其中一部分原因并不是主要原因。 我们公司的Mysql用来做用户帐号交易数据存储还有cache和token缓存。 3.在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。MySQL常见的备份有四种，备份策略一：直接拷贝数据库文件；备份策略二：使用mysqlhotcopy备份数据库；备份策略三：使用mysqldump备份数据库；备份策略四：使用主从复制机制(replication)，请从数据库规模以及应用的角度，谈谈您对这四种备份的认识。 首先解释一个误区，主从机制不适备份，这是数据冗余的一种。 策略一和策略二适用于myisam存储引擎。 策略三适用于所有存储引擎，不过mysqldump会全锁表，而且增量备份只能结合binlog来做。 策略四其实不是备份的一种，因为前3种都可以给予策略四上的从库来操作。 个人觉得策略四应该说实时在线备份机制比如mydumper以及xtrabackup等 4.InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。而MyISAM 是MySQL中默认的存储引擎，它基于更老的ISAM代码，但有很多有用的扩展。您觉得InnoDB和MyISAM有哪些区别？在选择存储引擎上需要考虑哪些问题？ 他们的主要区别在于， (1)innodb支持事务而myisam不支持事务 (2)还有一个行锁一个表锁 (3)InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行, select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含   where条件时，两种表的操作是一样的 (4) 对AUTO_INCREMENT的操作， MyISAM为INSERT和UPDATE操作自动更新这一列。对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。 (5) 构成上区别，每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型，基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB(据说在64位上不一样)。 选择上：根据业务来，一般对数据一致性有要求的电子商务以及游戏行业都用innodb，而移动互联网，小规模金融业务等对一致性要求不高的可以采用myisam。 5.谈谈试读《MySQL管理之道：性能调优、高可用与监控》章节后您的感想。 innodb_io_capacity，这个参数可以对IO产生效果，回去尝试一下。 200    单盘SAS/SATA 2000   SAS*12 RAID 10 5000   SSD 500000 FUSION-IO 这一章，可关闭自适应哈希索引，这个写的比较好，还有半同步这个比较不错，里面有几个性能图，根据性能图来分析，详细生动。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26230597/viewspace-1107312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26230597/viewspace-1107312/