记录MySQL的一些知识点（2013.04.13）

我非DBA，知识这两天路过学习了一点MySQL的知识，并且进行了一些SQL操作，为了方便记忆以及今后查阅，简单做下记录（有点零乱的）。 SQL命令（语句）的分类：DDL, DML, DCL, TCL Data Definition Language (DDL) statements are used to define the database

我非DBA，知识这两天路过学习了一点MySQL的知识，并且进行了一些SQL操作，为了方便记忆以及今后查阅，简单做下记录（有点零乱的）。

SQL命令（语句）的分类：DDL, DML, DCL, TCL
Data Definition Language (DDL) statements are used to define the database structure or schema. DDL示例：CREATE, ALTER, DROP, TRUNCATE, RENAME
Data Manipulation Language (DML) statements are used for managing data within schema objects. DML示例：SELECT, INSERT, UPDATE, DELETE, CALL, EXPLAIN PLAN, LOCK TABLES
Data Control Language (DCL) statements. DCL示例：GRANT, REVOKE
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions. TCL示例：COMMIT, SAVEPOINT, ROLLBACK, SET TRANSACTION
来自：http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

MyISAM和Innodb存储引擎中的索引的区别：
1. MyISAM默认使用B-tree索引只把索引载入内存，数据缓存依赖于操作系统，InnoDB使用聚集索引实际上是在同样的结构中保存了B-tree索引和数据行，把索引和索引的数据都载入内存缓冲 。
2. MyISAM数据库中的数据是按照插入的顺序保存，在每个索引节点中保存对应的数据行的地址，理论上说主键索引和其他索引是一样的；InnoDB数据库中的数据和主键节点保存在一起，所有其他索引节点中保存的是主键索引的值。
3. 在InnoDB表中插入数据一定要尽可能按照主键增加的顺序，AUTO_INCREMENT最好，这样插入的速度最快，如果没有按照主键顺序插入数据,在插入后最好使用OPTIMIZE TABLE重新组织表。
4. 因为InnoDB索引节点中保存的是主键的值，所以主键的值越简单越好。
5. 对于InnoDB表，在查询的时候如果只需要查找索引列，就不要加入其它列，这样速度最快。
6. MyISAM和Innodb的B-tree索引支持前缀索引,有最左匹配原则。
参考：http://www.cyrec.org/posts/myisam-innodb-index
提高mysql插入数据的速度，可以参考这里提出的一些Tips： http://hi.baidu.com/jackbillow/item/d40d1eedb1eecb0d64db0027


MySQL使用分区表的好处：
1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
2，方便维护，通过删除分区来删除老的数据。
3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。
MySQL可以建立四种分区类型的分区：
1. RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。 （这种分区类型，很久之前在实际业务中也遇到的过的，那时是Oracle）
2. LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
3. HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
4. KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
一般用得多的是range分区和list分区。
参考：http://www.cyrec.org/posts/mysql-partition-tables

MySQL事务示例
InnoDB存储引擎支持事务，使用事务的SQL语句示例如下：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

START TRANSACTION or BEGIN start a new transaction.
COMMIT commits the current transaction, making its changes permanent.
ROLLBACK rolls back the current transaction, canceling its changes.
SET autocommit disables or enables the default autocommit mode for the current session.
来自：http://dev.mysql.com/doc/refman/5.6/en/commit.html

关于MySQL中内存的分配、使用、调优等基础知识，见：http://mysql.rjweb.org/doc.php/memory

http://dev.mysql.com/doc/refman/5.6/en/memory-use.html

MySQL的variables和status是管理维护的利器，就类似Oracle的spfile和v$表。
MySQL通过系统变量记录很多配置信息，比如最大连接数max_connections：
mysql> show variables like ‘%connect%’;
+————————–+——————-+
| Variable_name | Value |
+————————–+——————-+
| character_set_connection | latin1 |
| collation_connection | latin1_swedish_ci |
| connect_timeout | 10 |
| init_connect | |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_user_connections | 0 |
+————————–+——————-+
7 rows in set (0.00 sec)
可以在global或session范围内修改这个参数：
mysql> set global max_connections=151;
status命令查询当前MySQL数据库的状态：
mysql> status
MySQL为每个连接分配线程来处理，可以通过threads_connected参数查看当前分配的线程数量：
mysql> show status like ‘%thread%’;
参考：http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html

http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html

查看当前连接状态信息
有些时候，我们需要查看MYSQL当前有哪些连接，比如IP、用户等信息。执行如下SQL命令即可。
命令： show processlist;
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
列出当前连接和状态:
mysql> show processlist; （默认只列出前100条）
mysql> show full processlist; （列出所有连接）
mysql> show full processlist;
+—-+——+———————–+——+———+——+——-+———————–+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———————–+——+———+——+——-+———————–+
| 2 | root | localhost | test | Query | 0 | NULL | show full processlist |
| 4 | test | 192.168.199.112:44751 | test | Sleep | 827 | | NULL |
+—-+——+———————–+——+———+——+——-+———————–+
2 rows in set (0.00 sec)
对于影响系统运行的thread，可以用 kill connection|query threadid 的命令杀掉它。

explain查看执行计划：
mysql> explain select * from mytable;
+—-+————-+———+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | mytable | ALL | NULL | NULL | NULL | NULL | 2 | |
+—-+————-+———+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)
参考：http://dev.mysql.com/doc/refman/5.6/en/explain.html

MySQL用户和权限管理
mysql> CREATE USER ‘test’@'%’ IDENTIFIED BY ’123456′;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO ‘test’@'%’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
参考：http://dev.mysql.com/doc/refman/5.6/en/grant.html

MySQL的临时表
A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name.
参考：http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

binary log (binlog)
A file containing a record of all statements that attempt to change table data. These statements can be replayed to bring slave servers up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup.
redo log
A disk-based data structure used during crash recovery, to correct data written by incomplete transactions. During normal operation, it encodes requests to change InnoDB table data, which result from SQL statements or low-level API calls through NoSQL interfaces. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically.
The redo log is physically represented as a set of files, typically named ib_logfile0 and ib_logfile1. The data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo logs is represented by the ever-increasing LSN value. The original 4GB limit on maximum size for the redo log is raised to 512GB in MySQL 5.6.
MySQL术语词汇表：http://dev.mysql.com/doc/refman/5.6/en/glossary.html

Original article: 记录MySQL的一些知识点（2013.04.13）

©2013 笑遍世界. All Rights Reserved.

原文地址：记录MySQL的一些知识点（2013.04.13）, 感谢原作者分享。