mysql专精.doc

36/66366*6100/26010060*2Mysq数据引擎MYISAM 首先它不支持事物，所以对事物要求高的业务不适合，然后呢其锁定机制是表级索引，虽然锁定的实现很容易但大大降低了并发性能，也因此当我们在对它查询的时候不能插写入，写入的时候不能查询，但是当查询数据时，不会阻碍其他的查询语句，还有myisam可以通过key_buffer缓存来大大提高访问性能减少硬盘IO，但是这个缓冲区只会缓存索引，不会缓存数据适用场景：不需要事物支持，并发相对较低（锁定机制），数据修改相对较少（阻塞问题），以查询为主最佳优化：尽量索引，调整读写优先级，启用延迟插入改善大批量写入性能，Innodbl 它具有较好的事物支持，支持4个事物隔离级别，支持多版本读，它读写的时候和事物隔离相关，并且它的缓存特性非常高效，能缓存索引和数据，整个表和主键以集群方式存储，并可以通过索引来实现行级锁定适用场景：需要事物支持，数据更新较为频繁，行家锁定对高并发有很好的适应能力，但需要确保查询是根据索引完成，它对数据一致性要求较高，通过innoDB的缓存能力来提高内存利用，达到减少硬盘IOBDB 分布式存储引擎，可以由多个BDB存储引擎组成集群来分别存放数据，它可以和mysqld分开存在于独立的机器上，然后通过网络进行交互，它对内存的需求量很大适用场景：具有非常高的并发需求，对单个请求的响应要求不是很快，查询简单，过滤条件较为固定，每次请求数据量较少Memory(heap)能像session和缓存一样方便操作和管理，充分的发挥了内存引擎的特点，高速度，低延迟，适合只读或者频繁读的操作，不适合频繁写入，Federated 专门用于访问远程数据库，因此能够将多个分离的Mysql服务器链接起来，从多个物理服务器创建一个逻辑数据库，十分适用于分布式环境或者数据集市环境，然后通过它建的表这是在本地只有一个表定义的文件，而数据则会存在远程数据库当中Archive 引擎 为大量很少引用的历史，文档，或安全审计信息的存储和检索提供解决方案，它的优点是插入同样的数据，比myisam节约75%的空间，比innoDB节约83%，当数据量非常大的时候，插入性能会比myisam更好，不过呢它不支持索引，当我们需要移植数据时，archive可以方便的移动到新的mysql环境，我们只需要将它的底层文件复制过去就OK了 把myisam的数据表移到innoDBMERGE存储分离动态和静态的数据，它利用结构接近的数据来优化查询，查询时可以访问更少的数据，更容易维护大数据集，我们可以通过修改。Mrg文件动态的增加和减少子表，应用的场景：如果需要把日志记录不停的录入mysql数据库，并且每天，每周，或者每个月都创建一个单一的表，而且要时常进行来自多个表的合计查询，这时候我们用它会非常简单有效。CSV引擎 简单并且很方便，说它简单是它创建和使用的时候很简单，方便是它的数据存储文件格式就是通用的CSV文件格式，这种格式很常见，很多软件都支持，因此它没有索引，主要的用途是，当我们在输出CSV报表的时候使用查询优化Explain select * .Table 显示这一行的数据是关于哪一张表的 Type 这是重要的列 显示连接使用了何种类型Possible_keys 显示可能应用在这张表的索引Key 实际使用的索引 如果为NULL，mysql有可能会选择优化不足的条件，这种情况下，可以在select语句中使用useRef 显示索引的那一列被使用了Rows mysql认为必须检查的用来返回请求数据的行数Extra mysql如何解析查询的额外信息。Extra 返回的描述的意义ALTER TABLE 表名 CHANGE TYPE = InnoDB如果只是想把表格从ISAM更新为MyISAM，你可以使用MySQL_convert_table_format命令，而不需要编写ALTER TABLE表达式。你可以使用SHOW TABLE命令（这是MySQL对ANSI标准的另一个扩展）来确定哪个引擎在管理着特定的表格。SHOW TABLE会返回一个带有多数据列的结果集，你可以用这个结果集来查询获得所有类型的信息：数据库引擎的名称在Type字段里。下面的示例代码说明了 SHOW TABLE的用法：SHOW TABLE STATUS FROM tblInnoDB由于MySQL能够允许你在表格这一层应用数据库引擎，所以你可以只对需要事务处理的表格来进行性能优化，而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言，灵活性才是关键。Mysql优化1 当知道只要查询一条数据时，使用limit12 如果有一个字段，比如性别 国家 民族 状态 或部门 我们知道这些字段的取值是有限且固定的，那么，我们可以用EMUM 而不是Varchar3多用explain 和 profile分析查询语句4.有时候可以1条大的SQL可以分成几个小SQL顺序执行，分了吧，速度会快很多。5.每隔一段时间用alter table table_name engine=innodb;优化表6.连接时注意:小表 jion 大表的原则7学会用explain 和 profile判断是什么原因使你的SQL慢。8.查看慢查询日志，找出执行时间长的SQL试着优化去吧9锁定表，保证数据的完整性Mysql慢查询Show variables like “%long%”; /查看一下当前慢查询的时间（默认10秒）Set global long_query_time =2 /设置时间为2秒Show variables like “%slow%”;/查看一下慢查询是不是已经开启Set global slow_query_log=on /启用慢查询Show variables like “%slow%”触发器CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmttrigger_time是触发程序的动作时间。它可以是BEFORE或AFTERtrigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一： INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。 UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。 DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。 3.2 可能遇到的问题 如果你在触发器里面对刚刚插入的数据进行了 insert/update, 会造成循环的调用. 如： create trigger test before update on test for each row update test set NEW.updateTime = NOW() where id=NEW.ID; END 应该使用set： create trigger test before update on test for each row set NEW.updateTime = NOW(); END存储过程CREATE PROCEDURE 名称(OUT/IN 参数 类型)BEGIN SQL语句END修改存储