摘抄 mysql 相关信息

　　1,Myisam 在磁盘上，将数据存储为3个文件。第一个是表结构文件，它的名字以表的名字开始，为.frm文件；第二个文件是数据文件，扩展名为.MYD；第三个是索引文件，扩展名为.MYI。
Myisam存储引擎最大的特点是表级锁、不支持事务和全文索引，适合一些cms内容管理系统作为后台数据库使用，但是在大并发，重负荷生产系统上，表锁的特性显得力不从心。
　　2，InnoDb 提供了提交，回滚，崩溃恢复能力的事务安全（ACID兼容）存储引擎。行锁。支持外键（FOREIGN KEY）. 为处理巨大数据量时拥有最大性能设计的。它的CPU效率可能是其他基于磁盘的关系型数据库引擎不能匹配的。InnoDB在一个表空间中存储它的表和索引。InnoDB表可以是任何尺寸的，即使在文件尺寸限制为2GB 的操作系统上。
　　3，Myisam 表级锁，加锁块；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
　　　  innodb 行级锁，加锁慢；会出现死锁；锁定粒度最小，发生锁的冲突概率最低，并发度最高。
　　4， 对myisam表的读操作（加读锁 lock table tablename read;），不会堵塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
　　对myisam的写操作（加写锁），会阻塞其他今天对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
　　5，innodb 存储引擎室通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才会使用行级锁，否则，使用表锁。

myisam与innodb之间的主要区别：
　　Myisam是非事务安全型的，而innodb是事务安全型的，也就是ACID事务支持；
　　myisam是表级锁，锁开销最小，而innodb支持行级锁定，锁管理开销大，支持更好的并发写操作；
　　myisam支持全文索引，而innodb不支持全文索引，但在最新的5.6版本中已提供支持；
　　myisam相对简单，管理方便，因此在效率上要优于innodb，小型应用可以考虑使用myisam；
　　myisam表式保存成文件的形式，在跨平台的数据转移中使用myisam存储会省不少的麻烦；innodb采用表空间来管理数据；
　　innodb表比myisam表更安全，可以保证在数据不丢失的情况下，切换非事务表到事务表；

　　Myisam 存储引擎的读锁跟写锁是互斥的，读写操作室串行的。试想一下，一个进程请求某个myisam表的读锁，同时另一个进程也请求同一张表的写锁，mysql该如何处理呢？答案是 写进程先获得锁；不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前；因为mysql认为写请求一般要比读请求重要。这也是Myisam表不适合有大量更新操作和查询操作应用的原因。因为大量的更新操作会造成查询操作很难获得读锁。从而可能永远堵塞。
　　Innodb 用于事务处理应用程序，具有众多特性，包括支持ACID事务、行锁等。如果应用中需要执行大量读写操作，则应该使用innodb，这样可以提高多用户并发操作的性能。对应myiam引擎，在mysql5.5 版本里，oracle公司支持的已经很少，以后内存数据库是一种趋势，所以建议优先选择innodb引擎。

开启慢日志：
　　在my.cnf配置文件里面，加入以下参数：
　　slow_query_log = 1
　　slow_query_log_file = mysql.slow
　　long_query_time = 2;//2s以上的sql被记录
　　使用 mysqldumpslow 命令取出好事最长的前10条慢sql
　　mysqldumpslow -s t -t 10 slow.log

sql语句优化：
　　1，尽量避免使用子查询，改用连表
　　2，like“%xxx%” 是无法使用索引的
　　　　a句：select count(1) from table where name like '%xxx%';
　　　　b句：select count(1) from table table_alias join
　　　　　　(select key from table where name like '%xxx%') table_alias_2 on a.key = b.key
　　　　b句比a句效率高。
　　3，limit 分页优化
　　　　a句：select * from tablename limit 99999,10;
　　　　b句：select * from tablename order by key limit 99999,10;
　　　　c句：select * from tablename where key > 999999 order by key limit 99999,10;
　　　　c > b > a;

　　　　a句：select * from tablename order by createdata asc limit 332344,10;
　　　　　　优化思路：先取出332344行后面的一条记录id，然后采用内连接的方法取出10条
　　　　b句：select a.* from tablename a
　　　　　　　　join
　　　　　　(select id from tablename order by createdata acs limit 332344,1) b
　　　　　　　　on
　　　　　　a.id >= b.id
　　　　　　limit 10;
　　4，or 条件语句优化
　　　　tmp 表 有2个字段，name，age都加入了索引
　　　　　　select * from tmp where age = 43 or name = 'xxx';
　　　　是不会使用到任何索引的，优化（采用 union all）：
　　　　select * from tmp where age = 43
　　　　　　union all
　　　　select * from tmp where name = 'xxx';
　　5，where 条件后的字段使用sql函数后，无法使用索引
　　6，using filesort 的优化方法，就是该排序字段，加入联合索引，如果只是单独的一个字段排序，那么将这个字段跟主键建立联合索引；如果有其他的条件，那么跟那个条件一起建立联合索引。（order by ;group by ;）

my.cnf 优化
　　对应 per_thread_buffers,可以将其理解为Oracal的PGA，为每个连接到mysql的用户分配的内存，其包含如下几个参数：
　　　　1，read_buffer_size

　　　　　　该参数用于表的顺序扫描，表示每个线程分配的缓冲区大小。比如，在进行全表扫描时，Mysql会按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满或者全部数据读取完毕后，再将buffer中的数据返回给上层调用者，以提高效率。默认 128kb，这个参数不要设置过大，一般在128kb-256kb

　　　　2,read_rnd_buffer_size

　　　　　　该参数用于表的随机读取，表示每个线程分配的缓冲区大小。比如，按照一个非索引字段做order by 排序操作时，就会利用这个缓冲区来暂存读取的数据，默认256kb,这个参数不要设置过大，一般在128-256kb
　　　　3,sort_buffer_size

　　　　　　在表进行order by ;group by 排序操作时，由于排序的字段没有索引，会出现using filesort,为了提高性能，可用此参数增加每个线程分配的缓冲区大小，默认2Mb，这个参数不要设置过大，一般在128-256kb即可。另外，一般出现using filesort的时候，要通过增加索引解决。
　　　　4，thread_stack
　　　　　　该参数表示每个线程的堆栈大小，默认 192kb,如果是 64位操作系统，设置为 256kb，不要过大。
　　　　5，join_buffer_size
　　　　　　表进行join连接操作时，如果关联的字段没有索引，会出现 Using join buffer，为了提高性能，可用此参数增加每个线程分配的缓冲区大小，默认128kb，不要过大，一般在128-256kb之间。一般出现 Using join buffer，要用增加索引来解决
　　　　6，binlog_cache_size
　　　　　　一般来说，如果数据库中没有什么大事务，写入也不是特别频繁，将其设置为1-2mb,是一个合适的选择。如果有很大的事务，可用适当增加这个缓冲值。
　　　　7，max_connectons
　　　　　　最大连接数，默认100，一般设置为 521-1000即可。

　　global_buffers优化：
　　　　　对应gblobal_buffers，可用理解为Oracle的SGA，用于内存中缓存从数据文件中检索出来的数据块，可用大大提高查询和更新数据的性能。主要参数如下：
　　　　1，innodb_buffer_pool_size
　　　　　　这个参数是innodb存储引擎的核心参数，默认128MB，这个参数要设置为物理内存的60%-70%；
　　　　2，innodb_additional_mem_pool_size
　　　　　　该参数用来存储数据字典信息和其他内部数据结构。表越多，需要在这里分配的内存越多。如果Innodb用光了这个池内的内存。Innodb开始从操作系统分配内存，并且王mysql错误日志中写警告信息。默认为8M，一般设置为16M即可
　　　　3，innodb_log_buffer_size
　　　　　　事务日志使用的缓冲区。Innodb再写事务日志的时候，为了提高性能，先将信息写入 innodb log buffer 中，当满足innodb_flush_log_trx_commit参数锁设置的相应条件时，再将日志写入文件中。默认为8MB,一般设置为 16-64MB。
　　　　4，key_buffer_size
　　　　　　该参数用来缓存myisam存储引擎的索引参数。mysql5.5中，默认为innodb存储引擎，所以这个参数可用设置小写，64M即可
　　　　5，query_cache_siez
　　　　　　缓存select语句和结果集大小的参数
　　注意，per_thread_buffers 内存设置 + global_buffers 设置不能大于实际物理内存，否则并发量很高时，会造成内容溢出，系统死机。