mysql 笔记

1.记录mysql运行时的sql语句     
配置my.ini文件  在安装目录,linux下文件名为my.cnf
查找到[mysqld]区段，增加日志的配置，如下示例：
[mysqld]
log="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
long_query_time=1
log指示日志文件存放目录；
log_slow_queries指示记录执行时间长的sql日志目录；
long_query_time指示多长时间算是执行时间长，单位s。
设置完成后重启服务。

 

2.查看mysql版本

mysql --version

或者登陆mysql 后status命令或者使用 select version();

 

关于数据库

   数据库是文件的集合；是文件的集合。
   数据库实例是应用程序，位于用户与操作系统之间的一层数据管理软件。

Mysql 由以下几块组成
连接池组件
管理服务和工具组件
SQL接口组件
查询分析器组件
优化器组件
缓冲组件
插件式存储引擎
    存储引擎是基于表的而非库。引擎是其核心。
    InnoDB支持事务，面向OLTP，行锁设计，支持外键，支持非锁定读。ibd文件中。提供插入缓冲，二次写，自适应哈希索引，预读等高可用等。InnoDB会为每行生成一个RowID.
    MyISAM,官方提供的引擎，不支持事务，表锁和全文索引，OLAP操作快，非WIN系统的默认引擎。由MYD和MYI组成，MYD放数据，MYI放索引。可使用采用Huffman编码压缩的myisampack来压缩与解压文件，5后支持256T的单表数据，5前为4G。
    NDB，类似Oracle RAC的集群存储引擎，数据全部放在内存，5.1后可将非索引数据放在磁盘上，所以PK查找非常快。但是复杂的连接会很慢。
    Memory存储引擎，在内存中，适合临时存储数据，默认使用哈希索引。并发小，不支持TEXT，BLOB字段。Mysql使用该引擎存放中间结果段，如果中间结果集，将转换到myisam引擎表而存放到磁盘。
    Archive存储引擎，只支持Insert,select，使用zlib算法可达1：10的压缩比，适合存储归档数据，如日志系统。提高高速的插入和压缩功能。
    Federated存储引擎，不存放数据，指向远程Mysql的表，类似于SqlServer的链接服务器和Oracle的透明网关。
    Maria存储引擎，目标是取代MyIsam。缓存数据和索引文件，行锁设——支持事务与非事务
物理文件

关于引擎更详细内容：http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html

 

Mysql连接

一般的进程通信方式有：管道，命名管道，命名字，TCP/IP套接字，Unix域名套接字
1.TCP/IP式
    mysql -h127.0.0.1 --port=3306 -uroot -p
2.命名管道
    针对Win系统的同一机器上的二个通信的进程通讯。SqlServer本地连接默认为命名管道。
    Mysql中需要启用--enable-named-pipe选项来。
    Mysql有--shared-memory使用内存的方式，连接时客户端使用-protocol=memory选项

 

命令行中直接输入mysql也可以进入 mysql,可以看到information_schema和test库

 

mysql 文件

1.参数文件    告诉mysql实例启动时在哪里可以找到数据库文件,并指定初始化参数.
    orale找不到参数文件(二进制文件spfile和文本文件init.ora)是无法装载的,而mysql将会默认值来装载.
        mysql> USE INFORMATION_SCHEMA
        Database changed
        mysql> select * from GLOBAL_VARIABLES;\G
    一些参数是动态的可以在运行时修改如autocommit 可用set 来修改,而一些是只读的.
    set read_buffer_size=131073   仅对当前会话有效,同autocommit
    select @@session.read_buffer_size;\G     131072
    select @@global.read_buffer_size;\G     131072
    但是
    set @@global.read_buffer_size=31074;
    可以直接来修改,但是,它并不是对参数文件的修改,重启实例后将会失效.   
   
2.日志文件    记录实例对某种条件做出响应时写入的文件.如错误,二进制日志文件,满查询日志文件,查询日志文件
 错误文件
    show variables like 'log_error';  查看错误日志文件,一般为计算机名+err      
 慢查询文件
    show variables like 'long_query_time';  查看设置的慢查询的秒数,默认10秒钟   
    mysql> show variables like '%slow%';
    +---------------------+-------------------------------------------+
    | Variable_name       | Value                                     |
    +---------------------+-------------------------------------------+
    | slow_launch_time    | 2                                         |
    | slow_query_log      | OFF                                       |
    | slow_query_log_file | C:\mysql-5.6.10-win32\data\clark-slow.log |
    +---------------------+-------------------------------------------+
    一条记录查询使用了0.5秒与0.05秒意义是不一样的,前者可能进行了表扫,后面可能走了索引.
    mysql>  show variables like 'log_queries_not_using_indexes%';\G   OFF      这样将SQL语句运行的帐户和IP、运行时间、锁定的时间、返回行等。然后对之优化。
    慢日志文件内容多时可使用mysqldumpslow         mysqldumpslow -s -al -n 10 xxx.log 锁定用时最长的10条
    也可以将慢查询放在mysql库中的slow_log.   show create table mysql.slow_log;
    show variables like 'log_output';\G  默认为FILE,如果改为TABLE，则就可在mysql.slow_log中查询到结果了
    mysql.slow_log的引擎为CSV引擎，可以修改为myisam
 查询日志
    mysql.general_log   中，用于与slow_log同   
 二进制日志
    二进制包含更改操作，但是不包括select,show这样的语句，二进制日志还包含执行时间等信息，有二种作用：恢复 与复制。
    show variables like 'datadir';\G
    show variables like '%binlog%';\G
    mysql> show variables like '%binlog%';\G
        +-----------------------------------------+----------------------+
        | Variable_name                           | Value                |
        +-----------------------------------------+----------------------+
        | binlog_cache_size                       | 32768                |   未提交的二进制日志缓存，默认32KB，该值基于会话
        | binlog_checksum                         | CRC32                |
        | binlog_direct_non_transactional_updates | OFF                  |
        | binlog_format                           | STATEMENT            |   5.1后引入，默认值与之前mysql版本一样，二进制日志文件记录的是日志的逻辑SQL。如果是ROW，则不是简单的SQL，而是表的行更改。如果是MIXED则会对这二种综合使用。
        | binlog_max_flush_queue_time             | 0                    |
        | binlog_order_commits                    | ON                   |
        | binlog_row_image                        | FULL                 |
        | binlog_rows_query_log_events            | OFF                  |
        | binlog_stmt_cache_size                  | 32768                |
        | innodb_api_enable_binlog                | OFF                  |
        | innodb_locks_unsafe_for_binlog          | OFF                  |
        | max_binlog_cache_size                   | 18446744073709547520 |
        | max_binlog_size                         | 1073741824           |
        | max_binlog_stmt_cache_size              | 18446744073709547520 |
        | sync_binlog                             | 0                    |   表示每写缓冲多少次就同步到磁盘。＝1为同步方式写，不使用OS的缓冲。默认为0。
        +-----------------------------------------+----------------------+
        在分析binlog文件时使用   mysqlbinglog -vv --start-position=1011 xxx.0000002,可以清楚地看到语句的执行。
   
3.socket文件    当使用unix域套接字方式进行连接时需要的文件
        Unix下可用。 show variables like 'socket';\G
4.pid文件    mysql实例的进程PID文件
        同Apache,会在自己的目录下的机器名.pid文件中记录自己的PID
5.mysql表结构文件    存放mysql表结构定义文件
        如.frm文件
6.存储引擎文件    每个存储引擎都会有自己的文件来保存各种数据.这些引擎真正存储了数据和索引等数据.
        这些文件包括重做日志文件、表空间文件。
        InnoDB引擎在存储设计上模仿了Oracle，将存储的数据按表空间存放，默认配置下有一个10MB的ibadata1的文件，即默认表空间。如果有多个文件组成可用innodb_data_file_path来指定，位于不同文件磁盘上时有利于提升性能。
        show variables like 'innodb_file_per_table';\G 默认ON，将每个表放在单独的表空间中.
    重做日志文件默认有二个ib_logfile0与ib_logfile1,它存储了事务日志。重做日志文件用于恢复到宕机前的状态。
    show variables like 'innodb%log%';\G
        +----------------------------------+-----------+
        | Variable_name                    | Value     |
        +----------------------------------+-----------+
        | innodb_api_enable_binlog         | OFF       |
        | innodb_flush_log_at_timeout      | 1         |
        | innodb_flush_log_at_trx_commit   | 1         |
        | innodb_locks_unsafe_for_binlog   | OFF       |
        | innodb_log_buffer_size           | 8388608   |
        | innodb_log_file_size             | 50331648  |  指定文件大小
        | innodb_log_files_in_group        | 2         | 
        | innodb_log_group_home_dir        | .\        |  路径
        | innodb_mirrored_log_groups       | 1         |  指定日志镜像文件组的数量，默认为1
        | innodb_online_alter_log_max_size | 134217728 |
        | innodb_undo_logs                 | 128       |
        +----------------------------------+-----------+

 

InnoDB表空间
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：ibdata1，这样就不很，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。
通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。
在MySQL的配置文件[mysqld]部分，增加innodb_file_per_table参数。可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。
独立表空间：
优点：
1． 每个表都有自已独立的表空间。
2． 每个表的数据和索引都会存在自已的表空间中。
3． 可以实现单表在不同的数据库中移动。
4． 空间可以回收（除drop table操作处，表空不能自已回收）
    a) Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
    b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
    c) 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
缺点：
单表增加过大，如超过100个G。
结论：
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整一 下：innodb_open_files 。
InnoDB Hot Backup（冷备）的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。
1.innodb_file_per_table设置.开启方法：
在my.cnf中[mysqld]下设置
innodb_file_per_table=1
2.查看是否开启：
mysql> show variables like '%per_table%';
3.关闭独享表空间
innodb_file_per_table=0关闭独立的表空间

 

 

Master 线程每10秒做一次full purge，把一些你回滚的事务中的UNDO空间(在共享表空间中)空间释放，但是其ibdata1文件大小仍为原来的大小,后续再UNDO日志时可使用这块释放的区域。
 
InnoDB表
表中均有主键，如果没显式地创建表则将会按以下二种方式自动创建一个：1.是否有非空的唯一索引，2如无则自动创建一个6字节大小的指针。
InnoDB引擎的逻辑存储结构和Oracle大致相同，所有数据被逻辑地存放在一个表空间中，表空间由段(segment) 区(extent) 页(page) 组成，页也称块(block);
表空间中存放的只是数据、索引和插入缓冲，其它如撤销信息、系统事务信息、二次写缓冲等还是存放在原来的共享表空间中.
段：组成表空间，段有数据段(B+树的页节点)，索引段(InnoDB是索引组织的，故数据即索引，索引即数据，B+树的非页节点)，回滚段。
    并不是每个对象都有段，故，表空间是由分散的页和段组成。
区：由连续64个页组成的，每个页为16KB，即每个区为1MB。对于大数据段，InnoDB最多可申请4个区，来保证数据的顺序性能。
    在启用innodb_file_per_table后，创建表的默认大小是96KB，区是至少1MB。这是因为每个段开始时，先有32个页大小的碎片页(fragment page)来存放数据，
    当这些页用完后才是64个连续页的申请。
    create table extentsite(col int not null auto_increment,col2 varchar(7000),primary key(col))engine=InnoDB;
    这时的文件extentsite.ibd只有96KB。varchar(7000)保证一页中可存放2条记录。
    insert into extentsite select NULL,repeat('a',7000);  大小96KB
    insert into extentsite select NULL,repeat('b',7000);  大小96KB
    同样的SQL，一共插入63条时，大小为576KB。共63条记录，32个页.
   
页：页是InnoDB磁盘管理的最小单位，SQLSERVER+ORACLE默认页大小为8KB，而InnoDB为16KB。且不可以更改。常见的页有：
    数据页
    UNDO页
    系统页
    事务数据页
    插入缓冲位图页
    插入缓冲空闲列表页
    未压缩的二进制大对象页
    压缩的二进制大对象页
  行：数据按行存放，每页放的行的记录也有硬性定义，最多存放16KB/ 2~200行记录，即7992行记录。  MySQL infobright存储引擎是面向列的。面向列的对数据分析类特别有用，如
     Sybase IQ,Google Big Table.
     
  Row_format指定存放行记录的格式，Redundant为兼容之前版本保留的。默认为Compact行格式。
  Compact 是5.1后引入的，设计目标是能高效存放数据。即，一个页中存放的行数据越多，性能越高。
    变长字段长度列表+NULL标识位+记录头信息+列1数据+列2数据+…………
    变长字段长度最长不可以超过2字节，即16位，即65535(实际为65532,官方指出这个65535是所有varchar字段的总长度)，即varchar长度最长为65535。 NULL标识位，存放该行是否有NULL值用1表示。占字节为bytes。头信息 字节即40位。
    每列除用用户定义的列外，还有二个隐藏列，即事务ID列和回滚指针列分别为6个字节和7个字节。如果未定义primaryKey 还会再增加一个6字节的RowID列.
  Redundant是5.1之前的记录格式
    字段长度偏移列表+记录头信息+列1+列2.……
    create table rowTest(col int not null auto_increment,col2 varchar(7000),primary key(col))engine=InnoDB row_format=compact; row_format=redundant;
 
   行溢出
        将一些数据存储在真正的数据页面之外，如BLOB,LOB。
   
    char的存储，在不同的字符集下，其长度是不定的，即，char(N)中的N是字符的长度而不是字节的长度。char_length(a),length(a)
    查询十六进制select hex(a) from ...
   
    约束：主键，外键，唯一键
    set sql_mode='STRICT_TRANS_TABLES'
   
分区表
    分区即是在将一个表或者索引分解成多个更小，更可管理的部分，就访问数据库的应用而言，从逻辑上讲，只有一个表或者一个索引，但是物理上这个表或者索引可能由数十个物理分区组成。
    每个分区即是一个独立的对象，可以独自处理。支持水平分区，即将不同的行分布到不同的物理文件中。有RANGE分区，LIST分区，HASH分区，KEY分区。
    是否启用分区
     show variables like '%partition%';\G  ??
        create table t1(col int not null,xxxx)partition by range(col)(partition p0 values less than (10),partion p1 values less than(20));
        当id小于10时，数据插入p0分区，当在10与20间时插入p1分区。  觉得用于日期分区较好。
       
    100W的数据与1000W的数据本身构成的B+树的层次都是2，所以分区不能带来多少性能提高。即使100W层次为2，1000W层次为3，分区可以避免一次IO，但是扫描多个分区的IO……。
    一般的B+树需要2~3次 IO；少有4层。
    1000W并不是个大表。

 

set @@foreign_key_checks=0 忽略外键
show full processlist;\G  查看mysql数据库线程列表。