学习MySQL笔记

参考:http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/
database

1 MySQL用户账户管理

1.1 创建新用户的方式

• 使用GRANT语句

GRANT SELECT, INSERT ON *.* TO 'lixin'@'%';
GRANT ALL ON *.* TO 'lixin'@'%'; 
<font color='red'>注意:</font>用以上命令授权的用户没有给其它用户授权的权限,如果想让该用户可以授权,用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' **WITH GRANT OPTION**; 

• 直接操作MySQL授权表

mysql> INSERT INTO user
    -> VALUES('localhost','monty',PASSWORD('some_pass'),
    -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
    -> VALUES('%','monty',PASSWORD('some_pass'),
    -> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
    -> Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

区别:
1. 当你用INSERT创建账户时使用FLUSH PRIVILEGES的原因是告诉服务器重读授权表。否则，只有重启服务器后更改方会被注意到。使用 GRANT，则不需要使用FLUSH PRIVILEGES。
2. 用INSERT使用PASSWORD()函数是为了加密密码。GRANT语句为你加密密码，因此不需要PASSWORD()。

最好的方法是使用GRANT语句，因为这样更精确，错误少。

1.2 限制账户连接资源

限制MySQL服务器资源使用的一个方法是将max_user_connections系统变量设置为非零值。但是，该方法严格限于全局，不允许管理具体账户。并且，它只限制使用单一账户同时连接的数量，而不是客户端连接后的操作。

在MySQL 5中,你可以为具体账户限制下面的服务器资源：
+ 账户每小时可以发出的查询数
+ 账户每小时可以发出的更新数
+ 账户每小时可以连接服务器的次数

资源限制保存在max_questions、max_updates、max_connections和max_user_connections列内。如果user表没有这些列，必须对它进行升级；
例如

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    -> IDENTIFIED BY 'frank'
    -> WITH MAX_QUERIES_PER_HOUR 20 /*每小时最大查询次数*/
    -> MAX_UPDATES_PER_HOUR 10      /*每小时最大更新次数*/
    -> MAX_CONNECTIONS_PER_HOUR 5   /*账号每小时最大连接次数*/
    -> MAX_USER_CONNECTIONS 2;      /*账户一次可以同时连接的最大连接数*/
要想取消已有限制，将该值设置为零

2 MySQL数据库备份

导入：
1)mysql> USE 数据库名;            [数据库要事先存在]
mysql> SOURCE d:/test.sql; 
2) [数据库要事先存在]
mysqldump --default-character-set=utf8 -u用户名 -p 数据库名 < 目标文件(如d:\test.sql)
导出：
mysqldump --default-character-set=utf8 -u用户名 -p 数据库名 > 目标文件(如d:\test.sql)
例mysqldump -u root -p tpweb > d:\test.sql

参考 http://xxlixin1993.blog.51cto.com/9498220/1664432
补充:

指定恢复时间
mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 | mysql -u root -pmypwd
指定恢复位置
mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -pmypwd 

3 编码

查看当前编码:

show variables like 'character%';
1.改配置文件并重启 /etc/my.ini

[mysqld]
default-character-set = utf8
character_set_server = utf8
[client]
default-character-set=utf8
[mysql]
default-character-set = utf8
[mysqld_safe]
default-character-set = utf8
[mysql.server]
default-character-set = utf8
<div class="md-section-divider"></div>

2.立即生效 但重启服务会失效

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
<div class="md-section-divider"></div>

4 MySQL中的复制

4.1 主从复制原理

复制过程中一个服务器充当主服务器，一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

主从复制的优点：
1. 主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。
2. 通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。读操作调度给slave，写操作调度给master
3. 使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。

4.1 主从复制实施

参考 http://xxlixin1993.blog.51cto.com/9498220/1664432
MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

细节
MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。

5 优化

优化的前提是知道瓶颈的所在。所有做基准测试是非常有必要的。推荐工具sysbench。
最常见的系统瓶颈是：磁盘搜索，磁盘读/写，CPU周期（我们将数据读入内存后，需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。当CPU需要的数据超出CPU缓存时，主缓存带宽就成为内存的一个瓶颈。）

5.1 优化SELECT语句和其它查询

mysql> desc select id from tmp;
<div class="md-section-divider"></div>

重点要看的:
type : 联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：

ref:ref列显示使用哪个列或常数与key一起从表中选择行。
rows:显示MySQL认为它执行查询时必须检查的行数。

注意：这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。

5.2 MySQL怎样优化WHERE子句

• 所有常数的表在查询中比其它表先读出。常数表为：空表或只有1行的表或与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。
  下列的所有表用作常数表：
  mysql> SELECT * FROM t WHERE primary_key=1;
  mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

• 尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。

• 每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。
• 在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。

5.3 INSERT语句的速度

插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：

连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1x记录大小）
插入索引：（1x索引）
关闭：(1)

这不考虑打开表的初始开销，每个并发运行的查询打开。
表的大小以logN (B树)的速度减慢索引的插入。

加快插入的一些方法：

• 如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节bulk_insert_buffer_size变量，使数据插入更快。
• 如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度
• 锁定表可以加速用多个语句执行的INSERT操作：

    LOCK TABLES a WRITE;
    INSERT INTO a VALUES (1,23),(2,34),(4,33);
    INSERT INTO a VALUES (8,26),(6,29);
    UNLOCK TABLES;
<div class="md-section-divider"></div>

• 可以通过增加key_buffer_size系统变量来扩大 键高速缓冲区

5.4 DELETE语句的速度

删除一个记录的时间与索引数量确切成正比。为了更快速地删除记录，可以增加键高速缓冲的大小。如果想要删除一个表的所有行，使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name。

5.5 其它优化技巧

• 使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改thread_cache_size变量的值。
• 尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。
• 对于频繁更改的表，应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式。
• 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。

5.6 锁

对WRITE，MySQL使用的表锁定方法原理如下：

如果在表上没有锁，在它上面放一个写锁。
否则，把锁定请求放在写锁定队列中。

对READ，MySQL使用的锁定方法原理如下：

如果在表上没有写锁定，把一个读锁定放在它上面。
否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。
这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+--------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+--------+

行级锁定的优点：
+ 当在许多线程中访问不同的行时只存在少量锁定冲突。
+ 回滚时只有少量的更改。
+ 可以长时间锁定单一的行。
行级锁定的缺点：
+ 比页级或表级锁定占用更多的内存。
+ 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
+ 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
+ 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

5.7 MySQL如何使用索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树。

例如：
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果col1和col2上存在一个多列索引，可以直接取出相应行。如果col1和col2上存在单列索引，优化器试图通过决定哪个索引将找到更少的行来找出更具限制性的索引并且使用该索引取行。
如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

如果列不构成索引最左面的前缀，MySQL不能使用局部索引。假定有下面显示的SELECT语句。

6 语言结构

Tips

使用--safe-updates选项

通常情况，这样的语句从表中删除所有行。用--safe-updates，可以通过指定可以识别它们的键值只删除某些行。
设置方法：
shell>mysql --safe-updates --select_limit=500 --max_join_size=10000
所有大的SELECT结果自动限制到1,000行，除非语句包括一个LIMIT子句。
所有insert最多10000行
或
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000

字符串 单引号与双引号的区别

如果SQL服务器模式启用了NSI_QUOTES，可以只用单引号引用字符串。用双引号引用的字符串被解释为一个识别符。

列类型存储

数值类型存储

日期和时间类型的存储

字符串类型的存储

注释：VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示)，而不是该类型的最大可能的大小。例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度，加上一个记录字符串长度的字节。对于字符串'abcd'，L是4，

使用INFORMATION_SCHEMA表去查看相关信息

例如：select table_name FROM information_schema.tables ORDER BY table_name DESC;

使用SQL_NO_CACHE禁用缓存查询

select SQL_NO_CACHE count(*) from users where email = 'hello';