mysql备忘录

一///MySQL
MySQL的复制原理以及流程
基本原理流程，3个线程以及之间的关联；
1. 主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
2. 从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进 自己的relay log中；
3. 从：sql执行线程——执行relay log中的语句；

MySQL存储引擎－－MyISAM与InnoDB区别
MyISAM: 不支持事务,适合OLAP应用（大量查询）。
存储结构：数据文件（.MYD),索引文件(.MYI),结构文件(.frm),特点：可以在不同的服务器上拷贝数据文件和索引文件。
加锁和并发：加锁：对整张表进行加锁，而不是行。并发：在读数据的时候，所有的表上都可以获得共享锁（读锁），每个连接都不互相干扰。在
写数据的时候，获得排他锁，会把整个表进行加锁，而其他的连接请求（读，写请求）都处于等待中。
延迟更新索引。MYISAM 默认把DELAY_KEY_WRITE开启,(show global variables like '%delay_key%';)整个选项是MYISAM引擎独有的。 在查询结束后，不会将索引的改变数据写入磁盘，而是改变内存中的索引数据。只有在清理缓冲区或关闭表时才将索引块转储到磁盘。
InnoDB:支持事务和外键。
innodb引擎的4大特性 插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
区别：
MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。
MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。即 MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以 MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
InnoDB支持外键，MyISAM不支持。
InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、 varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空 格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。（SHOW VARIABLES LIKE 'ft_min_word_len' 可修改使用几个汉字做分词。）
MyISAM支持GIS数据（空间地图数据），InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
没有where的count(*)使用MyISAM要比InnoDB快得多。因 为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般 要伴随where，且where中要包含主键以外的索引列。为什么这里特别强调“主键以外”？因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary key。所以只是count(*)的话使用secondary index扫描更快，而primary key则主要在扫描索引同时要返回raw data时的作用较大。
InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，但是从我目前运维的数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是我的首选。
原因如下：
1、首先我目前平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。
2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
3、从我接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。
4、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

如果需要把日志纪录不停的录入MySQL数据库，并且每天、每周或者每个月都创建一个单一的表，而且要时常进行来自多个表的合计查询，MERGE（引擎）表这时会非常简单有效。
Merge表的优点：
A：分离静态的和动态的数据
B：利用结构接近的的数据来优化查询
C：查询时可以访问更少的数据
D：更容易维护大数据集
E: 可以通过修改.mrg文件来修改Merge表，当然也可以用alter进行修改，修改后要通过FLUSH TABLES刷新表缓存，此法可以动态增加减少子表

外键约束：
create table city( city_id int not null auto_increment,city varchar(50),country_id int not null,primary key(city_id),constraint `fk_city_country` foreign key(country_id) references country(country_id) on delete restrict on update cascade) engine = innodb,ddefault charset=utf8;

restrict+No action: 子表有关联记录的时候，不准许更新父表；
cascade : 对父表的更新或者删除，对子表进行更新或者删除；
set null：父表更新或者删除数据的时候，字表相关字段set null ；

key
是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）
index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，索引只是索引，它不会去约束索引的字段的行为。
key的用途：主要是用来加快查询速度的。
UNIQUE KEY的用途：主要是用来防止数据插入的时候重复的。

各种引擎对比：
MyISAM：读和插入为主，很少更新和删除；并对事务的完整性，并发性要求不高 ；
InnoDB：事务处理提交和回滚，支持外键。插入查询，更新删除。类似的计费系统财务系统；
MEMORY：快速定位记录；
MERGE：突破对单个MyISAM表大小的限制，使用VLＤＢ；

临时表的表结构和数据都是储存在内存中的，session开始到结束是它的生命周期。
内存表的表结构是存储到数据库上的，数据放在内存中，网上推荐使用内存表，但我觉得我的应用场景更合适临时表。

现在设置mysql数据库也是手动提交事务。方法有两种：①临时生效（只对当前客户端有效），②永久生效
①临时生效（只对当前客户端有效）
set @@autocommit=0 （0为关闭状态，1为开启状态）
②永久生效（通过修改配置文件参数设置）
通过修改配置文件my.cnf文件，通过vim编辑my.cnf文件，在[mysqld]（服务器选项下）添加:
autocommit=0
保存，然后重启mysql服务即可生效。

针对InnoDB来说（插入数据），影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同 样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。innodb_buffer_pool_size 提高也有助于提高性能。innodb_buffer_pool_size=2G innodb_buffer_pool_instances=1

Mysql 中 CHAR的长度是固定的，而VARCHAR2的长度是可以变化的。
VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。

对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间；
对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利.

1）varchar与char的区别
Varchar存储可变长字符串，小于255字节时需要1个额外字节(大于255需要2个额外字节)存储长度，最大长度为65532字节(所有列总和)；
char存储定长(right padding)，读取时会截断末尾空格，长度最大为255字符；

mysql 4.0版本以下，varchar(50), 指的是50字节，如果存放utf8汉字时，只能存放16个（每个汉字3字节）
mysql 5.0版本以上，varchar(50), 指的是50字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放50个。
int(M) M表示显示宽度，最大255. 占4个字节。

通过explain可以知道mysql是如何处理语句，分析出查询或是表结构的性能瓶颈。通过expalin可以得到：
1. 表的读取顺序
2.表的读取操作的操作类型
3.哪些索引可以使用
4. 哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

MYSQL的事务配置项
innodb_flush_log_at_trx_commit=1
表示事务提交时立即把事务日志写入磁盘，同时数据和索引也更新。
innodb_flush_log_at_trx_commit=0
事务提交时，不立即把事务日志写入磁盘，每隔1秒写一次
innodb_flush_log_at_trx_commit=2
事务提交时，立即写入磁盘文件（这里只是写入到内核缓冲区，但不立即刷新到磁盘，而是每隔1秒刷新到盘，同时更新数据和索引

mysql使用profile分析语句性能消耗
--查看profile是否开启 show variables like '%profil%';
set profiling = 1; --关闭则用set profiling = off
--显示缓存的profile show profiles;
具体的消耗，进行详细的列出 show profile for query 1; --1是query_id (由show profiles产生。)
查看cpu的消耗情况 show profile cpu for query 1;
查看内存消耗 show profile memory for query 1;
查看io及cpu的消耗 show profile block io,cpu for query 1;
查看sql1相关的所有分析【主要看i/o与cpu,下边分析中有各项意义介绍】 show profile ALL for query 1;

(1)、explain出来的各种item的意义；
select_type
表示查询中每个select子句的类型
type
表示MySQL在表中找到所需行的方式，又称“访问类型”
possible_keys
指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
key
显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
Extra
包含不适合在其他列中显示但十分重要的额外信息

(2)、profile的意义以及使用场景；
查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等