`
ponlya
  • 浏览: 164454 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

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数据库线程列表。

分享到:
评论

相关推荐

    狂神MySQL笔记.rar

    【狂神MySQL笔记】是一份全面且深入的MySQL学习资源,涵盖了从基础到高级的各种MySQL技术知识点。作为数据库领域的核心工具,MySQL因其高效、稳定、开源等特性,被广泛应用于各种规模的企业和项目中。这份笔记旨在...

    尚硅谷mysql笔记.zip

    尚硅谷的MySQL笔记可能是为了帮助学习者深入理解和掌握MySQL的相关知识而编写的。这份笔记可能包含了从基础概念到高级特性的全面讲解,包括但不限于SQL语言、数据类型、数据库设计、索引、事务处理、视图、存储过程...

    MySQL笔记,比较全,知识点都有,而且有例子

    这份"MySQL笔记"涵盖了其主要知识点,并且每个概念都配有实例,使得学习更为直观易懂。以下是对这些笔记的详细解析: 一、基础概念 MySQL的基础部分包括数据库、表、字段、记录等基本元素的创建、修改和删除。理解...

    MySQL笔记——笔记.md

    MySQL笔记

    MySQL笔记(狂神说java)

    "狂神说Java"的MySQL笔记可能深入探讨了如何在Java编程环境中与MySQL进行交互,这对于我们理解数据库管理和开发至关重要。以下是对这个主题的详细阐述: 1. **MySQL基础**: - 数据库概念:MySQL是一个用于存储和...

    mysql笔记mysql笔记

    mysql笔记

    MYSQL笔记.zip

    总的来说,这个MySQL笔记涵盖了数据库管理的各个方面,通过一天的学习,你可以建立起对MySQL的初步理解,并具备基本的数据库操作能力。然而,要想成为真正的MySQL专家,还需要不断实践和深入学习更高级的主题,如...

    mysql笔记 mysql笔记

    mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记

    mysql笔记及考试答案.zip

    这份"mysql笔记及考试答案.zip"压缩包包含了深入学习MySQL的重要资源,包括一个PDF格式的MySQL笔记和一个包含考试试题及答案的文件,这对于复习MySQL知识或者准备相关考试非常有帮助。 MySQL笔记可能涵盖了以下几个...

    MySQL笔记.md

    MySQL笔记.md

    老杜MySQL笔记总结.md(全)

    老杜MySQL笔记总结.md(全)

    Mysql笔记资料

    这份"MySQL笔记资料"涵盖了全面的MySQL学习内容,包括基础理论、实战技巧以及优化策略,旨在帮助用户深入理解和掌握这个强大的数据存储与管理工具。 一、MySQL基础知识 在MySQL的学习之初,你需要了解其基本概念,...

    MySQL笔记.pdf

    这只是MySQL笔记的部分内容,实际上MySQL的功能远不止这些,还包括视图、索引、事务处理、存储过程、触发器、函数等多个高级特性。了解并掌握这些内容,将有助于在实际项目中更高效地管理和操作数据。

    MySQL笔记合集.pdf

    "MySQL笔记合集.pdf" MySQL 是一款开源的关系数据库管理系统(RDBMS),是一种relational database management system(RDBMS),是当下最流行的数据库管理系统之一。MySQL 的主要特点是开源、免费、跨平台、支持...

    mysql笔记.rar

    本笔记将深入探讨MySQL的基础知识,帮助你全面掌握这个强大的数据存储和管理工具。 首先,我们要理解数据库的基本概念。数据库是一个组织和存储数据的系统,允许用户通过结构化查询语言(SQL)进行数据的检索、更新...

    自整理! 最全mysql笔记

    - 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果 - 如果只有INSERT和SELECT操作,可以选择Archive,...

    MySQL笔记,老杜,初级MySQL笔记

    MySQL笔记,老杜,初级MySQL笔记

Global site tag (gtag.js) - Google Analytics