其实只看到了1/3，先记录一下，等以后有空了再补上。
将查询结果导出到文件
mysql > SQL QUERY INTO OUTFILE '/path/to/sql/file'
将每次操作都导出到文件
mysql > \T /path/to/file
mysql > ... //这些操作，及操作的结果都会输出到对应的文件
mysql > \t
文件内容大概会是这样
(root@localhost) [(none)]> use noah;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
(root@localhost) [noah]> show tables;
+----------------+
| Tables_in_noah |
+----------------+
| blog_category |
| blog_comment |
| blog_post |
| blog_res |
| blog_role |
| blog_role_res |
| blog_role_user |
| blog_tag |
| blog_user |
| test |
+----------------+
10 rows in set (0.00 sec)
(root@localhost) [noah]> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | foo |
| 2 | bar |
+----+------+
2 rows in set (0.02 sec)
显示MySQL当前状态及其他信息
SHOW STATUS; //显示MySQL当前状态
SHOW VARIABLES; //显示MySQL的变量信息，如version/data_dir等等
SHOW VARIABLES LIKE '%home%'; //获取包含home的变量
SHOW TABLE STATUS\G //显示当前表的状态，注意后面的\G，垂直显示结果
DESCRIBE tbl; //获取表结构
SHOW FULL COLUMNS FROM tbl; //类似上面
显示MySQL当前的连接状况
# 使用mysqladmin
mysqladmin processlist
# 或进入到mysql cli后执行
mysql > show processlist;
# 结果大概是这样
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 409 | root | localhost | noah | Query | 0 | NULL | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
关于TIMESTAMP
- 第一个TIMESTAMP字段会随着表其他字段的更新而自动更新，之后的TIMESTAMP字段则不会。
- TIMESTAMP的范围是：1970-2037；而DATETIME的范围是：1000-9999
移除重复的行
# 注意这个IGNORE参数，如果没加的话会报错，且执行失败
# 假设要去除a,b项重复的行
ALTER IGNORE TABLE tbl ADD UNIQUE INDEX(a,b);
查看当前在操作的数据库
也可以在mysql的提示符上动点手脚
# edit /etc/mysql/my.cnf
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
prompt=(\\u@\\h) [\\d]> \\
复制一个表
CREATE TABLE tbl1 LIKE tbl;
INSERT INTO tbl1 SELECT * FROM tbl;
# 也可以先用mysqladmin导出数据，再导入
定长表与变长表
包含任何varchar、text等变长字段的数据表，即为变长表，反之则为定长表。所以CHAR和VARCHAR不共存
CHAR，最多可以容纳30个字符，但如果字符数不到30个的话，也会占用这些空间，只不过会在后面补上空格，但我们查询时又会发现尾部没有空格，这是因为空格已经被CHAR处理掉了。
VARCHAR，也是最多可以容纳30个字符，但如果不足30个的话，有多少字符占多少空间，不会浪费。
变长表的优势在于有效利用空间，但由于记录大小不同，在其上进行许多删除或更新操作会使表中的碎片增多，需要定期OPTIMIZE TABLE以保持性能。
定长表的查询，检索和更新速度都比变长表快，但占用的空间也大。
PS:MySQL 5.0.3之后VARCHAR的最大字符数为65535
转换编码
SET NAMES utf8
# CHARSET utf8
聚合函数
COUNT [GROUP BY]
COUNT + HAVING + GROUP BY (HAVING可以看作后置WHERE语句)
MIN/MAX [GROUP BY]
SUM/AVG [GROUP BY]
DISTINCT [GROUP BY]
所有的这些聚合函数加上GROUP BY之后，都只对GROUP BY部分有效。(不好理解，忽略)
并发控制
读锁(共享锁)/写锁(排他锁)
当某一用户修改一部分数据时，MySQL会禁止其他用户读取同一数据。大多数时，MySQL都是以透明的方式实现锁的内部管理
锁粒度
只锁定部分修改的数据，而不是所有的资源，或者只对要修改的数据片精确加锁。任何时间，在给定的资源上，被加锁的数据量越小，就可以允许更多的并发修改，只要相互之间互不冲突即可
这么做的问题是加锁也会消耗系统资源。如获得锁，检查锁是否已解除，以及释放锁等，都会增加系统开销。如果系统花费大量时间来管理锁，而不是读/写数据，那么系统整体性能都可能会受到影响
所谓的锁策略，就是在锁开销和数据安全之间寻求一种平衡。
表锁(MyISAM)
开销最小，但不适合频繁写操作
行锁(InnoDb)
可以支持最大的并发处理，但同时也会增加开销（InnoDb），由存储引擎实现，而不是MySQL服务器
事务
一组原子性的SQL语句。要么全部执行(commit)，要么全部不执行(rollback)
正像锁粒度的增加会导致锁开销的增加一样，这种事务处理中的额外安全措施，也会导致数据库服务器要完成更多的额外工作
MySQL默认操作是AutoCommit，这意味着除非显示地开始一个事务，否则将把每个SQL操作视为一个单独事务自动执行
死锁
两个或多个事务在同一资源上相互占用，并请求加锁时，导致的恶性循环现象
解决办法：死锁检测/死锁超时机制。InnoDb处理死锁的方法是，回滚拥有最少排他行级锁的事务。
隐式和显式锁定
InnoDb: 一个事务在执行过程中的任何时候，都可以获得锁，但只有在执行COMMIT或ROLLBACK语句后，才可以释放这些锁。
InnoDb也支持显式锁定，如：
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
多版本并发控制
MySQL事务性存储引擎，如InnoDb，不是简单使用行加锁机制，而是MVCC和行加锁机制关联使用。
MVCC不是MySQL独有的技术，其他如Oracle, Postgresql等都在使用
可以将MVCC设想成一种行级加锁的变形，它避免了很多情况下的锁操作，大大降低了系统的开销
MVCC是通过及时保存在某些时刻的数据快照，而得以实现的。
所谓"版本号"，其实是InnoDb维护的一个计数器，每启动一个事务，计数器随着递增，并将该号作为事务的版本号
[INSERT]
InnoDb将系统当前的版本号设为新增行的版本号
[DELETE]
InnoDb将系统当前的版本号设为被删除行的删除号，该行并未立即被物理删除
[UPDATE]
INSERT+DELETE
[SELECT]
1. 行版本号不大于事务版本号。这确保了该行在事务开始时已存在，或者由当前事务创建、更新
2. 行删除号不存在，或者删除号大于事务版本号。这确保事务开始前行未被删除
对于被标记为删除的行，InnoDb有专门的线程负责物理删除，当行满足如下条件时认为可以将其物理删除：当前不存在版本号小于该行删除号的事务，这样可以确保不会有事务再引用到该行
保存这些额外记录的好处，是使大多数读操作都不必申请加锁
关于MyISAM
- 表加锁。并发低/开销少
- 将每个表存储成两个文件：数据文件(.MYD)和索引文件(.MYI)
- 使用CHECK TABLE mytable 和 REPAIR TABLE mytable来修复表，也可以使用myisamchk命令
- 索引长度不能超过1000(注意，如果是utf8的话，长度x3)，InnoDb没有此限制
- 可以延迟索引。使用表创建选项DELAY_KEY_WRITE创建的MyISAM表，在SQL结束之后，不会将索引的改变数据写入磁盘，而是在内存的键缓冲区中缓存索引改变数据，只有在清理缓冲区或关闭表时才将索引块转到磁盘。对于数据经常改变，并且频繁使用的表，这种模式大大提高了表的处理性能。不过，如果服务器或系统崩溃，索引将肯定损坏，并需要修复
关于InnoDb
- 行级锁。并发高/开销相对高
- 高性能
- 崩溃后自动恢复
- 主键聚簇索引，辅助索引非聚簇索引(单独索引树)，辅助索引也会包含主键列，所以如果主键列较大，则它的辅助索引也会较大
- 任何改变InnoDb表结构的操作会导致整个表的重建，包括重建所有索引
- 外键约束
- 自动提交性能差？
- 可以显示锁定
- 不要对InnoDb使用不带WHERE语句的count(*)，这会导致InnoDb执行全表扫描或索引扫描，而MyISAM只需要从相关记录中读取该值即可。
性能检测
mysql > SET PROFILING = 1;
mysql > ...
mysql > SHOW PROFILES;
会把执行的语句和执行时间都打印出来，如下
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00011700 | select count(*) from user |
| 2 | 0.00033500 | select count(*) from user_copy |
| 3 | 0.59868300 | select count(created) from user |
| 4 | 0.51746400 | select count(name) from user_copy |
| 5 | 0.00846700 | show table status like 'user' |
+----------+------------+-----------------------------------+
还可以针对某个query进行更细致的分析
也可以使用FLUSH STATUS + SHOW SESSION STATUS
mysql > SHOW PROFILE FOR QUERY 1;
查询缓存
MySQL在第二次执行相同的SQL查询语句时，默认会使用查询缓存。加上"SQL_NO_CACHE"不使用查询缓存
SELECT SQL_NO_CACHE username, ...
关于NULL
- 尽量避免NULL
- MySQL难以优化引用了可空列的查询，它会使索引，索引统计和值更加复杂
- 即使要在表中存储"没有值"的字段，还是有可能不使用NULL的，考虑使用0或空字符来代替它。
索引
索引是性能问题的首要原因，先搞定索引，再去搞查询优化
B-Tree索引
- 根节点保存了指向子节点的指针，存储引擎根据指针寻找数据
- 当一个数据块不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，所以树的深度和广度是由数据量决定的
- 每个节点包含了下层节点的链接，(没有相邻节点链接，上层链接可有可没有)
假设建立了一个(last_name, first_name, birth)的索引，此索引对于以下类型可用
匹配全名 (例如可以找到一个叫Cuba Allen，并且出生于1960-01-01的人)
匹配最左前缀 (例如可以找到姓为Allen的人，仅适用于索引中的第一列)
由于树的节点是排好序的，它们可以用于查找和ORDER BY查询
B-Tree的局限在于如果查询不是从索引列的最左边开始，就无法使用索引。所以索引列的顺序至关重要。
高性能索引策略
隔离列
如果在查询中没有隔离索引的列，MySQL通常不会是使用索引。"隔离"列意味着它不是表达式的一部分，也没有位于函数中。
前缀索引
找到合适的前缀长度(计算全列的选择性，并使前缀的选择性接近于它)
SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo
SELECT COUNT(DISTINCE LEFT(city, 4))/COUNT(*) FROM city_demo
也要注意如果数据分布非常不均匀，可能就会有问题
前缀索引能很好的减少索引的大小及提高速度，但MySQL不能在ORDER BY和GROUP BY中使用索引
聚集索引 (InnoDb)
当表有聚集索引时，它的数据行实际保存在索引的叶子页(而不是指针)，所谓"聚集"就是指实际的数据行和相关的键值都保存在一起，每个表只能由一个聚集索引(主索引)，因为不能以此把行保存在两个地方
优点：
- 可以把相关数据保存在一起。如果没有使用聚集，读取每个邮件都会访问磁盘
- 数据访问快。聚集索引把索引和数据都保存到了同一棵B-Tree中，因此从聚集索引中取得的数据通常比在非聚集索引进行查找要快
- 聚集索引能最大限度地提升I/O密集负载的性能。
缺点：
- 更新索引列代价是庞大的，因为它强制InnoDb把每个更新的行移到新位置
- 辅助索引会比较大，因为它们的叶子包含了被引用行的主键列
- 辅助索引访问需要两次索引查找
覆盖索引
所有满足查询需要的数据的索引(只需要读取索引，不需要再读取行数据)，比如这条SQL语句
SELECT state_id, city, address FROM userinfo WHERE state_id = 5
如果只在state_id上建索引，则city,address都要从表里读取行数据
如果建立index (state_id, city, address)，既能使用state_id索引，同时又可以使用覆盖索引，速度就快多了
多余和重复索引
- MySQL允许你在统一列上创建多个索引，所以MySQL不得不单独维护每一个索引
- 如果列(A,B)上有索引，那么另外一个列(A)上的索引就是多余的(B-Tree)
- 大多数情况下，多余索引都是不好的，为了避免它，应该扩展已有索引，而不是添加新索引
- 索引越多，更新索引的开销越大，尤其是在数据很多的情况下
--EOF--
若无特别说明，本站文章均为原创，转载请保留链接，谢谢
