MyISAM是默认存储引擎,它基于更老的ISAM代码,但有很多有用的扩展。(注意MySQL 5.1不支持ISAM)。每个MyISAM在磁盘上存储成三个文件。每一个文件的名字均以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为·MYD (MYData)。
简单来说,InnoDB是由一系列后台线程和一大块内存组成。 InnoDB是事务安全的存储引擎,设计上借鉴了很多Oracle的架构思想,一般而言,在OLTP应用中,InnoDB应该作为核心应用表的首先存储引擎。InnoDB是由第三方的Innobase Oy公司开发,现已被Oracle收购,创始人是Heikki Tuuri,芬兰赫尔辛基人,和著名的Linux创始人Linus是校友。
内存
InnoDB的主要工作都是在一个单独的Master线程里完成的。Master线程的优先级最高,它主要分为以下几个循环:主循环(loop)、后台循环(background loop)、刷新循环(flush loop)、暂停循环(suspend loop)。
数据库优化
[mysqld]
skip-name-resolve,server-id = 1,bind-address = 0.0.0.0 port = 3306,datadir = /home/mysql,tmpdir = /tmp,default_storage_engine = InnoDB
character_set_server = utf8,innodb_file_per_table = 1,innodb_log_file_size = 512M,innodb_log_files_in_group = 4,innodb_rollback_on_timeout = 1,slow_query_log = 1,slow_query_log_file =/var/log/mysql/mysql-slow.log,long_query_time = 1
#log-queries-not-using-indexes#这个参数不安全,说是记录没有用到索引的语句,其实记录的全部的日志,占用大量的IO,建议不要打开
#relay_log_recovery=1#这个参数在丛库上一定要加上, query_cache_type = off,query_cache_size = 0
#这两项是禁用缓存,这个使服务器用途而定:写比较多的数据库最好禁用,因为没写一次他要修改缓存中的数据,给数据库带来额外的开销,读比较的可以开启,可以提高查询效率 #一下4个参数是mysql5.6上的新特性
innodb_buffer_pool_dump_at_shutdown = 1 #解释:在关闭时把热数据dump到本地磁盘。
innodb_buffer_pool_dump_now = 1 #解释:采用手工方式把热数据dump到本地磁盘。
innodb_buffer_pool_load_at_startup = 1 #解释:在启动时把热数据加载到内存。
innodb_buffer_pool_load_now = 1 #解释:采用手工方式把热数据加载到内存。
read_buffer_size = 2M,sort_buffer_size = 2M,join_buffer_size = 1M,key_buffer_size = 2G ,thread_cache_size = 2048,open_files_limit=65535
innodb_open_files = 8192
max_allowed_packet = 64M
thread_stack = 512k,max_length_for_sort_data = 16k,tmp_table_size = 256M,max_heap_table_size = 256M,max_connections = 4000 ,max_connect_errors = 30000,innodb_read_io_threads = 8,innodb_write_io_threads = 16,innodb_flush_method = O_DIRECT
innodb_io_capacity =20000#根据硬盘的情况修改,stat的用100,sas的200,sas做riad10的为400fision-io的可以设置为20000
innodb_buffer_pool_size = 72G#内存的80%
innodb_buffer_pool_instances=18,thread_concurrency=0,innodb_thread_conc,rrency = 0,innodb_log_buffer_size = 16M,innodb_flush_log_at_trx_commit = 2,innodb_lock_wait_timeout = 60,innodb_old_blocks_time=1000,innodb_use_native_aio = 1,innodb_purge_threads=1,innodb_change_buffering=inserts
建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引
SQL语句的优化主要包括三个问题,即如何发现有问题的SQL、如何分析SQL的执行计划、以及如何优化SQL
SQL优化顺序
15.SQL语句的优化。
<br /> 1、UNION 查询,它可以把需要使用临时表的两条或更多的 SELECT 查询合并的一个查询中<br /> 2、在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效,<br /> 3、使用 UNION 来创建查询的时候,我们只需要用UNION作为关键字把多个SELECT语句连接起来就可以了,要注意的是所>有 SELECT 语句中的字段数目要想同
SELECT Name, Phone FROM client UNION SELECT Name, BirthDate FROM author UNION SELECT Name, Supplier FROM product
select id from t where name like ‘%c%’ //相当于精确查找
比如:SELECT * FROM EMPLOYEE WHERE 1=2
in 和 not in 也要慎用,很多时候用 exists 代替 in,not exists代表not in。
select id from t where num in(1,2,3)
-----------------------------------------------------------
对于连续的数值,能用 between 就不要用 in 了
select id from t where num between 1 and 3
---------------------------------------------------------
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
替换为:
select num from a where exists(select 1 from b where num=a.num)
对字段进行null值判断;
select id from t where num is null
------------------------------------------------------------
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
select id from t where num=10 or num=20
------------------------------------------------------------
可以这样查询:
select id from t where num=10 union all select id from t where num=20
如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,
但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。
然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫:
select id from t where num=@num
------------------------------------------------------------
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num=@num
应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描
select id from t where num/2=100
----------------------------------------------------------
应改为:
select id from t where num=100*2
应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id
-------------------------------------------------------------------
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′