文档库

最新最全的文档下载
当前位置:文档库 > mysql专精

mysql专精

36/6<6

36<6*6

100/2<60

100<60*2

Mysq数据引擎

MYISAM 首先它不支持事物,所以对事物要求高的业务不适合,然后呢其锁定机制是表级索引,虽然锁定的实现很容易但大大降低了并发性能,,也因此当我们在对它查询的时候不能插写入,写入的时候不能查询,但是当查询数据时,不会阻碍其他的查询语句,还有myisam可以通过key_buffer缓存来大大提高访问性能减少硬盘IO,但是这个缓冲区只会缓存索引,不会缓存数据

适用场景:不需要事物支持,并发相对较低(锁定机制),数据修改相对较少(阻塞问题),以查询为主

最佳优化:尽量索引,调整读写优先级,启用延迟插入改善大批量写入性能,

Innodbl 它具有较好的事物支持,支持4个事物隔离级别,支持多版本读,它读写的时候和事物隔离相关,并且它的缓存特性非常高效,能缓存索引和数据,整个表和主键以集群方式存储,并可以通过索引来实现行级锁定

适用场景:需要事物支持,数据更新较为频繁,行家锁定对高并发有很好的适应能力,但需要确保查询是根据索引完成,它对数据一致性要求较高,通过innoDB的缓存能力来提高内存利用,达到减少硬盘IO

BDB

分布式存储引擎,可以由多个BDB存储引擎组成集群来分别存放数据,它可以和mysqld

分开存在于独立的机器上,然后通过网络进行交互,它对内存的需求量很大

适用场景:具有非常高的并发需求,对单个请求的响应要求不是很快,查询简单,过滤条件较为固定,每次请求数据量较少

Memory(heap)能像session和缓存一样方便操作和管理,充分的发挥了内存引擎的特点,高速度,低延迟,适合只读或者频繁读的操作,不适合频繁写入,

Federated 专门用于访问远程数据库,因此能够将多个分离的Mysql服务器链接起来,从多个物理服务器创建一个逻辑数据库,十分适用于分布式环境或者数据集市环境,然后通过它建的表这是在本地只有一个表定义的文件,而数据则会存在远程数据库当中

Archive 引擎为大量很少引用的历史,文档,或安全审计信息的存储和检索提供解决方案,它的优点是插入同样的数据,比myisam节约75%的空间,比innoDB节约83%,当数据量非常大的时候,插入性能会比myisam更好,不过呢它不支持索引,当我们需要移植数据时,archive可以方便的移动到新的mysql环境,我们只需要将它的底层文件复制过去就OK了把myisam的数据表移到innoDB

MERGE存储分离动态和静态的数据,它利用结构接近的数据来优化查询,查询时可以访问更少的数据,更容易维护大数据集,我们可以通过修改。Mrg文件动态的增加和减少子表,

应用的场景:如果需要把日志记录不停的录入mysql数据库,并且每天,每周,或者每个月都创建一个单一的表,而且要时常进行来自多个表的合计查询,这时候我们用它会非常简单有效。

CSV引擎简单并且很方便,说它简单是它创建和使用的时候很简单,方便是它的数据存储文件格式就是通用的CSV文件格式,这种格式很常见,很多软件都支持,因此它没有索引,主要的用途是,当我们在输出CSV报表的时候使用

查询优化

Explain select * …..

Table 显示这一行的数据是关于哪一张表的

Type 这是重要的列显示连接使用了何种类型

Possible_keys 显示可能应用在这张表的索引

Key 实际使用的索引如果为NULL,mysql有可能会选择优化不足的条件,这种情况下,可以在select语句中使用use

Ref 显示索引的那一列被使用了

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 当知道只要查询一条数据时,使用limit1

2 如果有一个字段,比如性别国家民族状态或部门我们知道这些字段的取值是有限且固定的,那么,我们可以用EMUM 而不是Varchar

3多用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_stmt

trigger_time是触发程序的动作时间。它可以是BEFORE或AFTERtrigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:

·INSERT:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。

·UPDATE:更改某一行时激活触发程序,例如,通过UPDATE 语句。

·DELETE:从表中删除某一行时激活触发程序,例如,通过DEL ETE和REPLACE语句。

3.2 可能遇到的问题

如果你在触发器里面对刚刚插入的数据进行了insert/update, 会造成循环的调用.

如:

create trigger test before update on test for each row update test set NEW.u pdateTime = NOW() where id=NEW.ID; END

应该使用set:

create trigger test before update on test for each row set NEW.update Time = NOW(); END

存储过程

CREATE PROCEDURE 名称(OUT/IN 参数类型)

BEGIN

SQL语句

END

修改存储过程

ALTER PROCEDURE 存储过程名SQL语句代码块删除存储过程

DROP PROCEDURE IF EXISTS 存储过程名

查看一个已存在的存储过程

SHOW CREATE PROCEDURE 存储过程名

列出所有的存储过程

SHOW PROCEDURE STATUS

调用存储过程

CALL 存储过程名(参数列表)