MySQL数据库基本操作指南[摘选]

MySQL查询优化之索引问题

创建时间: 2010-09-17

摘自：网易DBA

一、使用索引问题

1、对于创建的多列索引（也即组合索引），只要查询的条件中用到了最左边的列，索引一般就会被使用。

2、对于使用like的查询，后面如果是常量并且只有％号不再第一个字符，索引才会被使用。

3、如果对大的文本进行索引，使用全文索引而不使用like '%....%';

二、查看索引使用情况

如果索引正在工作，Handler_read_key的值就会很高，这个值代表了一个行被索引值读的次数，很低表示增加索引得到的性能改善不高，因为索引并不能经常使用。Handler_read_rnd_next的值高则表示查询运行低效，并且应该建立索引补救。

MySQL查询优化之SQL语句

创建时间: 2010-09-17

摘自：网易DBA

优化SQL语句的一般步骤

1、通过show status命令了解各种SQL的执行频率
      MySQL客户端连接成功后，可以show [ session | global ] 命令可以提供服务器状态信息，也可以在操作系统上使         用mysqladmin extended-status命令获得这些消息，session表示当前连接，而global表示数据库上次启动至今的           统计结果

   

      Com_xxx表示每个xxx语句执行的次数，通常我们之关心select,update,insert,delete的统计参数

      Connections表示试图连接MySQL服务器的次数。

      Uptime表示服务器工作时间

      Slow_queries表示慢查询的次数

2、定位查询执行效率低的SQL语句

      通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[=filename]选项启动，mysqld写一个包含       所有执行时间超过long_query_time秒的SQL日志文件。

      慢查询是在查询结束后才记录，所以在应用反应执行效率出现问题的时候查询慢日志并不能定位问题，可以使用           show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否索表等，可以实时查看SQL执行情况

3、通过explain分析低效的SQL执行计划

4、确定问题并采取相应的优化措施

MySQL两个简单使用的优化方法

创建时间: 2010-09-17

摘自：网易DBA

1、定期分析表和检查表

分析表语句：

analyze table  table_name1,table_name2 ... 

检查表语句：

check table table_name1  ,table_name2 .....

2、定期优化表

如果已经删除了表的一大部分，或者包含可变长度行的表（含有VARCHAR、BLOB、TEXT）进行了很多更改，则应使用optimize table table_name进行表优化，该命令可以对表进行碎片整理和合并。注意，该命令只对BDB、MyISAM、InnoDB表起作用。

用BIT GROUP FUNCTIONS做统计

创建时间: 2010-09-16

关键词：Group By ; BIT_AND ; BIT_OR ;

假设有这样一个任务：一个超市要统计每个客户每次购买了哪些物品。为了简单化问题，我们假设该超市共有面包、牛奶、饼干、啤酒四种商品。

通常我们的做法是建立一个购物单表，记录购物发生的时间、顾客信息，再建一个购物表，记录数量、单价等等详细信息。这样设计表结构的优点是顾客所购买的商品的详细信息都可以记录下来。但是如果仅仅是需要统计用户购买的商品种类和每次购物总价等信息的话，那么这种数据库结构设计就显得太复杂了。

当然，我们也可以以一个字符串型的VARCHAR字段来表示客户的购买信息，但是客户一次购买很多商品的话，需要很大的存储空间，而且最头疼的是做各种统计的时候更会显得捉襟现肘。

下面给出一种新的解决方案，仍然用一个字段表示顾客的购买商品信息，该字段存储一个十进制数字，当然转换成二进制的时候，那么每一位代表一个商品，而且如果所在位是1，表示顾客购买了该种商品，例如二进制从低位到高位（从右到左）一次代表面包、牛奶、饼干、啤酒。那么用户购物单记录为5，寄0101，那么就知道用户购买了面包和饼干。把这些购物单按用户分组做BIT_OR()运算就可以知道用户都购买过什么商品，BIT_AND()运算就知道每次用户都会买的商品，再按GROUP BY就可以按用户统计购买情况了。

这种数据库结构设计的好处就是能用很简洁的数据表示很丰富的信息，这种方法能够大大节省空间，而且能够提高部分统计计算的速度。

不过需要注意的是，这种设计其实损失了顾客购买商品的详细信息，比如购买商品的数量、当时单价、折扣信息、是否有促销等，因此，还要根据具体的实际情况有选择性的考虑数据库结构设计。

MySQL随机读一条数据

创建时间: 2010-09-16

其实，有两个方法：

第一：用group + rand + limit完成

select * from table_name group by rand() limit 1;

第二：自定义查询

select * from products where id = ( ceil( rand() * (select max(id) from products))) limit 1

方法二有个缺点，如果id不是连续的值，就有可能找不到随机出来的id行的记录。

使用视图的优势

创建时间: 2010-09-14

视图（VIEW）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的，视图并不在数据库中实际存在，行和列来自自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

一、视图相对于普通的表的优势主要体现在以下几个方面：

• 简单：使用视图的用户完全不需要关心后面对应的表的结构，关联条件和筛选条件，对用户来说已经是过滤好的符合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行或某个列，但是通过视图就可以实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

二、视图的操作

• 创建视图：
  create or replace view view_name as 
  ->select 列名1,列名2,列名3
  ->from 表名1,表名2
  ->where 从句条件
  注意：视图的定义有用一些限制，例如，在FROM关键字后面不能包含子查询。
• 删除视图：
  用户可以一次删除一个或者多个视图，前提是必须有该视图的DROP权限。
  drop view [ if exists ] view_name [,view_name]
• 查看视图：
  show tables    MySQL 5.1版本开始可以使用该命令查询表和视图。
  show table status [ from db_name ] [ like pattern ] 不仅可以显示表的信息，也可显示视图的信息。
  show create view view_name \G 查看视图的定义。
  select * from views where table_name = 'view_name'  \G 通过查看information_schema.views也可以查看视图的相关信息。

MySQL索引之HASH和BTREE比较

创建时间: 2010-09-14

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

MySQL大文本存储方式TEXT和BLOB

创建时间: 2010-09-13

一般在保存少量字符串的时候，我们会选择CHAR或者VARCHAR；而在保存较大文本时，通常会选择使用TEXT或者BLOB，二者之间的主要差别是BLOB能用来保存二进制数据，例如，图片；而TEXT只能保存字符数据，比如一篇文章或日记。

TEXT类型可分为：

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

BLOB也可以分为：

TINYBLOB

BLOB

MEDIUMBLOB

LONGBLOB

使用TEXT和BLOB值会引起一些性能问题，以下提供了一些解决方法。

1、特别是在执行了大量的删除操作时，删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理，避免“空洞”引起性能问题。

2、在不必要的时候避免检索大型的BLOB或TEXT值。

3、把BLOB或TEXT列分离到单独的表中去。

4、可以使用合成的（Synthetic）索引来提高大文本字段的查询性能。合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存放在单独的数据列中，接下来就可以通过散列值找到数据行了，用散列标志符值查询的速度比搜索BLOB列本身的速度快很多。注意：这种方法只能用于精确匹配的查询。

eg：

1.create table t(id varchar(1),context blob,hash_value varchar(40));

2.insert into t values(1,repeat('beijing',2),md5(context));

3.insert into t values(2,repeat('beijing',2),md5(context));

4.insert into t values(3,repeat('beijing 2008',2),md5(context));

5.select * from t where hash_value=md5(repeat('beijing 2008',2));

Ubuntu下更改Mysql默认字符集

创建时间: 2010-09-13

在ubuntu安装Mysql Server比较简单,一条命令就可以搞定,如下:

sudo apt-get install mysql-server

但是用apt-get安装mysql不能设置默认字符集,可能是我不明白怎么弄,安装完默认字符集是latin1,可以登录mysql后用status命令查看,显示内容如下:

root@ubuntu-vm:/home/kuuyee#　mysql　-u　root　-p
Enter　password:
Welcome　to　the　MySQL　monitor.　　Commands　end　with　;　or　\g.
Your　MySQL　connection　id　is　4
Server　version:　5.0.51a-3ubuntu5.4　(Ubuntu)

Type　'help;'　or　'\h'　for　help.　Type　'\c'　to　clear　the　buffer.

mysql>　status
--------------
mysql　　Ver　14.12　Distrib　5.0.51a,　for　debian-linux-gnu　(i486)　using　readline　5.2

Connection　id:　　　　　　　　　　4
Current　database:
Current　user:　　　　　　　　　　　root@localhost
SSL:　　　　　　　　　　　　　　　　　　　　Not　in　use
Current　pager:　　　　　　　　　　stdout
Using　outfile:　　　　　　　　　　''
Using　delimiter:　　　　　　　　;
Server　version:　　　　　　　　　5.0.51a-3ubuntu5.4　(Ubuntu)
Protocol　version:　　　　　　　10
Connection:　　　　　　　　　　　　　Localhost　via　UNIX　socket
Server　characterset:　　　　latin1
Db　　　　　characterset:　　　　latin1
Client　characterset:　　　　latin1
Conn.　　characterset:　　　　latin1
UNIX　socket:　　　　　　　　　　　　/var/run/mysqld/mysqld.sock
Uptime:　　　　　　　　　　　　　　　　　16　hours　33　min　45　sec

Threads:　1　　Questions:　10　　Slow　queries:　0　　Opens:　12　　Flush　tables:　1　　Open　tables:　6　　Queries　per　second　avg:　0.000

--------------

解决办法就是更改/etc/mysql/my.cnf文件,在[mysqld]和[mysql]下添加如下内容:

[mysqld]
default-character-set=utf8
default-storage-engine=INNODB

[mysql]
default-character-set=utf8

然后重启mysql server即可,命令如下:

sudo /etc/init.d/mysql　restart

然后再查看一下status,呵呵!字符集变为utf-8了!

MySQL浮点数与定点数

创建时间: 2010-09-13

浮点数和定点数都是用来保存含有小数的数值，如果一个字段被定义为浮点或定点类型后，插入数据的精度超过该列定义的实际精度，则会自动四舍五入的插入原值。

定点数不同于浮点数，它实际是以字符串的形式存储的，所以定点数可以更精确的保存数据。

注意：在今后关于浮点数和定点数的应用中，用户需要考虑以下几个原则：

• 浮点数存在误差问题
• 对货币等对精确度敏感的数据，应该使用定点数表示
• 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免作浮点数比较
• 要主要浮点数中一些特数值的处理。

数据库的ACID特性

创建时间: 2010-09-13

对软件开发人员来说，数据库也有这么贵重。如果使用数据库的话，需要确保它安全存储数据并允许您将来检索数据。您还希望数据库允许多个程序使用数据库而不会相互干扰。为了演示，假设您拥有一个银行。银行的数据库必须具备下列功能： 

　　● 安全存储合适的数据 
　　●快速检索合适的数据 
　　●支持多个并行的用户会话 

　　这些任务可以合称为 ACID 测试；ACID 是 Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和 Durability（持久性）的缩略词。 

　　原子性 指数据库操作可以组合到一起，当作单个单元处理。 

　　一致性 保证此单个单元（或事务）中的所有操作要么都成功执行，要么都不执行。换句话说，数据库不能处于未完成状态。要了解这些特征为何如此重要，可考虑这样一个 银行事务：即钱从储蓄帐户转到支票帐户。如果在将钱从储蓄帐户减去之后、加到支票帐户之前，转帐处理失败，那么您就会变穷，而银行将会有一个愤怒的（前） 客户！原子性使得这两个操作 —— 从储蓄帐户减钱和向支票帐户加钱 —— 被当作单个事务进行处理。一致性保证事务中的这两个操作同时成功或同时失败。这样，您的钱就不会丢失了。 

　　隔离性 指独立的数据库事务集合以不相互冲突的方式执行。仍使用这个银行类比，考虑两个客户同时在帐户之间转移资金。数据库必须分别跟踪两个转帐；否则，资金可能进入错误的帐户，而银行可能得多两个愤怒的（前）客户。 

　　持久性 保证数据库是安全的，不会异常终止。当电源断电时，如果电视或计算机不工作，这可能是小麻烦，但同样的事情对于数据库来说就不一样了。如果银行计算机在转 移资金时掉电，导致交易丢失，您就不会是个快乐的客户了。持久性保证如果数据库在资金转移期间异常终止，则当数据库重新启动时，它将能够恢复交易并继续正 常的操作。

MySQL字符串操作函数归总

创建时间: 2010-09-12

CONCAT(S1,S2,...,SN)   连接几个字符串，只要有一个为NULL，则返回NULL

INSERT(str,x,y,instr)  将字符串str从x位置开始y长度的字符串替换为instr

LOWER(str) 小写

UPPER(str) 大写

LFET(str,x)、RIGHT(str,x) 取str左（右）边x个字符

LPAD(str,n,pad)，RPAD(str,n,pad) 用pad对字符串从左（右）边进行填充直到长度为n

REPLACE(str,a,b) 将str中的a用b进行替代

REPEAT(str,x) 重复str字符串x次

STRCMP(s1,s2) 比较两个字符串

LTRIM(str) RTRIM(str) TRIM(str) 去除空格

SUBSTRING(str,x,y) 在str中从x位置开始截取y个字段长度

MySQL表结构的操作

创建时间: 2010-09-12

一、增加表字段

ALTER TABLE table_name ADD [COLUMN] column_definition [FIRST | AFTER col_name]

二、修改表类型

ALTER TABLE table_name MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

三、字段改名

ALTER TABLE table_name CHANGE [COLUMN] old_col_name column_definitions 

 [FIRST | AFTER col_name]

四、删除字段

ALTER TABLE table_name DROP [COLUMN] column_name

五、修改字段排列顺序

在ADD，CHANGE，MODIFY语法中，都有一个可选项first | after col_name，这个选项可以用来修改字段在表中的位置，默认ADD增加的字段在表的最后，而CHANGE和MODIFY默认都不会改变字段的位置。

六、更改表名

ALTER TABLE table_name RENAME [TO] new_tablename

七、更改表的存储引擎

ALTER TABLE table_name engine=innodb，改成了innodb类型

八、检查表是否损坏

CHECK TABLE table_name

九、修复表

REPAIR TABLE table_name

十、查看mysql的状态报告 

show status; 
show innodb status; 
mysqlreport --第三方插件; 

十一、解释查询 

explain select * from tableA where field1= 'a'; 

十二、添加索引 

alter table TableA add key name(name); 
alter table TableA drop key name(name); 
alter table TableA add com_key(field1,field2,field3); 

十三、显示数据库的引擎

show engines;

MySQL中information_schema数据库介绍

创建时间: 2010-09-12

mysql中information_schema数据库介绍

［更多详细内容见mysql官方文档：http://dev.mysql.com/doc/refman/5.1/zh/information-schema.html#schemata-table］

INFORMATION_SCHEMA提供了访问数据库元数据的方式。

 

元数据是关于数据的数据，例如数据库名或表名、列的数据类型或访问权限等。有时，用于表述该信息的其他术语包括“数据字典”和“系统目录”。

 

下面举几个例子来说明用法，详细用法请参考手册。

 

1、查看数据库服务器上的数据库

 

 

Sql代码 

1. SELECT SCHEMA_NAME AS  'database'   
2. FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0, 30  

 

2、查看某个数据库里的数据表

 

 

Sql代码 

1. SELECT table_name   
2. FROM INFORMATION_SCHEMA.TABLES   
3. WHERE table_schema = 'test' LIMIT 0, 30  

 

3、查看某个数据表里的字段

 

 

Sql代码 

1. SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT  
2. FROM INFORMATION_SCHEMA.COLUMNS  
3. WHERE table_name = 'entries' AND table_schema = 'test' LIMIT 0, 30  

 

4、查看某个表的索引信息

 

 

Sql代码 

1. SELECT *   
2. FROM INFORMATION_SCHEMA.STATISTICS  
3. WHERE table_name = 'authors' AND table_schema = 'test' LIMIT 0, 30  

 

5、查看某个数据库里数据和索引的大小(M)

 

 

Sql代码 

1. SELECT SUM( DATA_LENGTH ) /1024 /1024 AS DATA_SIZE, SUM( INDEX_LENGTH ) /1024 /1024 AS INDEX_SIZE  
2. FROM INFORMATION_SCHEMA.TABLES  
3. WHERE table_schema = 'test'

MySQL中导入大容量数据库

创建时间: 2010-09-12

use db_name;

source 文件路径名称

例子：

mysql -uroot -p;

use emb;

source /var/www/examples.sql

MySQL中的大文件存储－BLOB

创建时间: 2010-09-12

MySQL中，BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据。BLOB类型实际是个类型系列（TinyBlob、Blob、MediumBlob、LongBlob），除了在存储的最大信息量上不同外，他们是等同的。

MySQL的四种BLOB类型
类型 大小(单位：字节) 
TinyBlob 最大 255 
Blob 最大 65K 
MediumBlob 最大 16M 
LongBlob 最大 4G

实际使用中根据需要存入的数据大小定义不同的BLOB类型。
需要注意的是：如果你存储的文件过大，数据库的性能会下降很多。

MySQL中VARCHAR和CHAR类型

创建时间: 2010-09-12

两者都是用来保存mysql中较短的字符串，二者的主要区别在于存储的方式不同：

（1）CHAR列的长度为创建表时所声明的长度，长度范围可以是0～255中的任何一个值。

（2）VARCHAR列中的值为可变长字符串，长度可以指定为0～65535之间的值。

（3）在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

MySQL中的内连接和外连接的解释

创建时间: 2010-09-12

弄个例题，直观一点。两个表： 
--表stu 
id name 
1, Jack 
2, Tom 
3, Kity 
4, nono 
--表exam 
id grade 
1, 56 
2, 76 
11, 89 

内连接 （显示两表id匹配的） 
select stu.id,exam.id,stu.name, exam.grade from stu inner join exam on stu.id=exam.id 

-------------------------------- 
1 1 Jack 56 
2 2 Tom 76 


左连接（显示join 左边的表的所有数据，exam只有两条记录，所以stu.id,grade 都用NULL 显示） 
select stu.id,exam.id,stu.name, exam.grade from stu left join exam on stu.id=exam.id 
1 1 Jack 56 
2 2 Tom 76 
3 NULL Kity NULL 
4 NULL nono NULL 

右连接（与作连接相反，显示join右边表的所有数据） 
select stu.id,exam.id,stu.name, exam.grade from stu right join exam on stu.id=exam.id 
1 1 Jack 56 
2 2 Tom 76 
NULL 11 NULL 89

总结来说：
内连接取两个表的交集，外连接分左和右，左连接取左边的全部，右连接取右边的全部。
内连接:进行连接的两个表对应的相匹配的字段完全相同的连接。
左连接：LEFT OUTER JOIN， 两个表左连接时会返回左边表中的所有行和右边表中与之相匹配的列值，如果没有则用空值代替。
右连接：RIGHT OUTER JOIN，两个表进行右连接时会返回右边表中的所有的行和左边表中与之相匹配的列值没有相匹配的用控制代替。