MySQL学习总结（Linux系统环境）

Linux下：

1.mysql的卸载

2.mysql的安装

3.查询优化

4.查询缓存设置

 

CentOS release 6.3下安装MySQL-5.5.22

一.CentOS安装MySQL

查看系统版本：# more /etc/issue

1.卸载安装的mysql

查看是否安装了mysql

[root@virt-4-contos /]# rpm -qa|grep -i mysql

mysql-libs-5.1.61-4.el6.x86_64

若有，先看看服务是否启动着：

# ps aux|grep mysql

若启动着,则停掉服务:

# service mysql stop

[root@virt-4-contos /]# yum -y remove mysql-libs-5.1.61-4.el6.x86_64

[root@virt-4-contos /]# rpm -qa|grep -i mysql

[root@virt-4-contos /]# find / -name mysql

/data/mysql

/usr/local/mysql

/usr/local/mysql/data/mysql

/usr/local/mysql/bin/mysql

/usr/local/mysql/include/mysql

/var/spool/mail/mysql

/home/mysql

/etc/rc.d/init.d/mysql

/root/hhf/mysql-5.5.22/client/mysql

/root/hhf/mysql-5.5.22/include/mysql

[root@virt-4-contos /]# rm -rf /data/mysql

[root@virt-4-contos /]# rm -rf /usr/loacl/mysql

[root@virt-4-contos /]# rm -rf /var/spool/mail/mysql

[root@virt-4-contos /]# rm -rf /home/mysql

[root@virt-4-contos /]# rm -rf /etc/my.cnf    //同时需要删除mysql的配置文件

[root@virt-4-contos /]# rpm -qa|grep -i mysql

[root@virt-4-contos /]#

 

2.安装mysql 相关依赖库

（没有的话就安装，有就不用安装了） 

通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt*   libtool*

没有的话安装：

yum安装：

#yum install bison gcc gcc-c++ autoconf automake zlib* libxml* ncurses-devel  libtool

 

查看是否安装cmake：

输入命令：cmake -help

3.安装cmake包

# wgethttp://www.cmake.org/files/v2.8/cmake-2.8.6.tar.gz

# tar zxvf cmake-2.8.6.tar.gz

# cd cmake-2.8.6/

# ./configure

# gmake

# gmake install 

 

4.创建mysql的安装目录及数据库存放目录

# mkdir -p /usr/local/mysql                 //安装mysql

# mkdir -p /usr/local/mysql/data            //存放数据库

 

5.添加用户和创建数据库目录和权限设置

#groupadd mysql

#useradd -r -g mysql mysql

 

6.MYSQL 5.5.22安装

# wget http://down1.chinaunix.net/distfiles/mysql-5.5.22.tar.gz

# tar zxvf mysql-5.5.22.tar.gz

# cd mysql-5.5.22

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1

# gmake

# gmake install

 

参数说明：

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql       //安装目录

-DINSTALL_DATADIR=/usr/local/mysql/data         //数据库存放目录

-DDEFAULT_CHARSET=utf8                     //使用utf8字符

-DDEFAULT_COLLATION=utf8_general_ci          //校验字符

-DEXTRA_CHARSETS=all                        //安装所有扩展字符集

-DENABLED_LOCAL_INFILE=1                  //允许从本地导入数据

注意事项：

重新编译时，需要清除旧的对象文件和缓存信息。

# make clean

# rm -f CMakeCache.txt

# rm -rf /etc/my.cnf

# cd /usr/local/mysql

 

7.配置

(1)设置目录权限

# cd /usr/local/mysql

# chown -R root:mysql .　//把当前目录中所有文件的所有者设为root，所属组为mysql

# chown -R mysql:mysql data

(2) 启动服务添加到系统

# cp support-files/my-medium.cnf /etc/my.cnf　//将mysql的启动服务添加到系统服务中

(3)创建系统数据库的表

# cd /usr/local/mysql

# scripts/mysql_install_db --user=mysql

(4)设置环境变量

# vi /root/.bash_profile

在PATH=$PATH:$HOME/bin添加参数为：

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/mysql/lib

#source /root/.bash_profile

(5)启动mysql

# cd /usr/local/mysql

# cp support-files/mysql.server  /etc/init.d/mysql　//将mysql的启动服务添加到系统服务中

# service mysql start

(6)修改MySQL的root用户的密码以及打开远程连接

# mysql -u root mysql    

mysql>use mysql;              //选择数据库

mysql>desc user;

//更改root用户的密码

mysql>update user set Password = password('123456') where User='root';

//更改mysql用户的密码

 mysql>update user set Password = password('123456') where User='mysql';

mysql> Grant all privileges on *.* to 'root'@'% ' identified by '123456' with grant option;　 (%表示是所有的外部机器，如果指定某一台机，就将%改为相应的机器名)

mysql> Grant all privileges on *.* to 'mysql'@'% ' identified by '123456' with grant option;  (%表示是所有的外部机器，如果指定某一台机，就将%改为相应的机器名)

mysql>select Host,User,Password  from user where User='root'; 

mysql>select Host,User,Password  from user where User='mysql';
mysql>flush privileges;
mysql>exit
新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，来使新设置生效。

重新登录：mysql -u root -p

若还不能进行远程连接，则关闭防火墙

# /etc/rc.d/init.d/iptables stop

(7)权限分配说明

mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;

权限1,权限2,…权限n代表

select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。

当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。

当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。

用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%'表示从任何地址连接。

‘连接口令’不能为空，否则创建失败。

(8)查看编码格式

默认登录mysql之后可以通过SHOW VARIABLES语句查看系统变量及其值。

mysql> show variables like '%character%';

如下：

 

若不是，则修改/etc/my.cnf文件

 1.打开my.cnf修改编码

    在[client]下增加default-character-set=utf8

    在[mysqld]下增加default-character-set=utf8

     同时加上init_connect='SET NAMES utf8' (设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行)

 

 2.重新启动mysql

　　service mysqld restart

　　再次输入show variables like '%character%';

    以下情况在安装过程中设置了编码的情况下并未出现，创建数据库的时候不设置编码，也没有关系，中文能正常显示，所以以下情况暂供出错的时候做参考

　　即使做了以上修改如果直接数据库再创建表，然后存入中文，取出来的会是问号。解决的办法是：创建数据库的时候指明默认字符集为utf8，如：

　　create database test default character set utf8;

(9)测试编码格式

   #mysql -u mysql -p

   123456

   show databases;

   use mysql;

   创建一个数据库

   CREATE DATABASE testone;

   选择你所创建的数据库 

   use testone;

   show tables;

   创建一个数据库表

   CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), birth DATE,     birthaddr VARCHAR(20));

   插入数据

   insert into mytable values ('abccs','f','1977-07-07','china');

   insert into mytable values ('张三','f','1977-07-07','china');

   查询数据，中文不乱码

   select * from mytable;

(10)配置文件参数说明

 my.cnf文件

[client]

#password       = your_password

port            = 3306         //端口号

socket          = /tmp/mysql.sock  //为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于UNIX/Linux系统; 默认设置一般是/var/lib/mysql/mysql.sock文件)

 

# The MySQL server

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 16M   //用来存放索引区块的RMA值(默认设置是8M)。

max_allowed_packet = 1M  //包或任何生产的中间字符串的最大大小

table_open_cache = 64  //mysql每打开一个表，都会读入一些数据到table_open_cache缓存中，当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取   show variables like 'table%';    show status like 'open%';

sort_buffer_size = 512K  //1.Sort_Buffer_Size 是一个connection级参数，在每个    connection第一次需要使用这个buffer的时候，一次性分配设置的内存。

2.Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。3.据说Sort_Buffer_Size 超过2KB的时候，就会使用mmap() 而不是 malloc() 来进行内存分配，导致效率降低。使用mmap 分配内存时，会带来性能上的损耗，影响大约在 30% 左右。

net_buffer_length = 8K   //用于建立连接时的连接缓冲和结果缓冲。最小值是1K，最大值是1M。但有需要时，可以动态扩大到 max_allowed_packet的大小。

read_buffer_size = 256K  //为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。

read_rnd_buffer_size = 512K  //类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。

myisam_sort_buffer_size = 8M  //当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区。

 

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin                         //把对数据进行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命 令)以二进制格式记入日志(二进制变更日志，binary update log)。这种日志的文件名是filename.n或默认的hostname.n，其中n是一个6位数字的整数(日志文件按顺序编号)。

 

# binary logging format - mixed recommended

binlog_format=mixed

 

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1  //给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。

 

 

[mysqldump]

quick

max_allowed_packet = 16M

 

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

 

[myisamchk]

key_buffer_size = 20M  //用来存放索引区块的RMA值(默认设置是8M)。

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

 

[mysqlhotcopy]

interactive-timeout   //服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。参数默认值：28800秒（8小时）

 

 

 

MySQL查询优化方法

二.查询优化

1.为查询缓存优化你的查询

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：

// 查询缓存不开启

$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// 开启查询缓存

$today = date("Y-m-d");

$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

2.建立索引

索引，它是加快查询的最重要的工具。还有其他加快查询的技术，但是最有效的莫过于恰当地使用索引了。在MySQL的邮件清单上，人们通常 询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取得最大的性能改善，然后再寻求其他可能有帮助的技 术。

3.当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。

在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。（请注意，第一条中是Select *，第二条是Select 1）

// 没有效率的：

$r = mysql_query("SELECT * FROM user WHERE country = 'China'");

if (mysql_num_rows($r) > 0) {

    // ...

}

// 有效率的：

$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");

if (mysql_num_rows($r) > 0) {

    // ...

}

4.避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

所以，你应该养成一个需要什么就取什么的好的习惯。

// 不推荐

$r = mysql_query("SELECT * FROM user WHERE user_id = 1");

$d = mysql_fetch_assoc($r);

echo "Welcome {$d['username']}";

// 推荐

$r = mysql_query("SELECT username FROM user WHERE user_id = 1");

$d = mysql_fetch_assoc($r);

echo "Welcome {$d['username']}";

 

 

MySQL查询缓存设置提高MySQL查询性能

三.查询缓存设置

从 MySQL4开始，出现了QueryCache查询缓存，如果使用了QueryCache，当查询接收到一个和之前同样的查询，服务器将会从查询缓存种检索结果，而不是再次分析和执行上次的查询。这样就大大提高了性能，节省时间，非常有用。

打开查询缓存，是通过几个步骤来设置的，例如：虽然你设置Mysql允许查询缓存，但是如果你设置的查询缓存大小为了0，这和没有允许没什么区别。所以必须是几个步骤的设置才能真正打开查询缓存这个功能。

下面我用 mysql5.2.22为演示最常用的设置查询缓存

1. query_cache_type使用查询缓存的方式
一般，我们会把 query_cache_type 设置为 ON,默认情况下应该是ON

mysql> select @@query_cache_type;

+——————–+
| @@query_cache_type |
+——————–+
| ON |
+——————–+
这样当我们执行 select id,name from tableName; 这样就会用到查询缓存。在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明select sql_no_cache id,name from tableName;当然也可以禁用查询缓存： mysql> set session uery_cache_type=off;
这里我们不讨论这个，我们演示常用的设置。

2.系统变量hava_query_cahce设置查询缓存是否可用

mysql> show variables like 'have_query_cache';

+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+
上面的显示，表示设置查询缓存是可用的。

3.系统变量 query_cache_size

表示查询缓存大小，也就是分配内存大小给查询缓存，如果你分配大小为0，
那么第一步和第二步起不到作用，还是没有任何效果。

mysql> select @@global.query_cache_size;

+—————————+
| @@global.query_cache_size |
+—————————+
| 16777216 |
+—————————+
上面是 mysql6.0设置默认的，之前的版本好像默认是0的，那么就要自己设置下。
设置 set @@global.query_cache_size=1000000;这里是设置1M左右，900多K。
再次查看下select @@global.query_cache_size;
+—————————+
| @@global.query_cache_size |
+—————————+
| 999424 |
+—————————+
显示我们设置新的大小，表示设置成功。

4.query_cache_limit 控制缓存查询结果的最大值

例如：如果查询结果很大，也缓存？这个明显是不可能的。
MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会
进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。

mysql> select @@global.query_cache_limit;

+—————————-+
| @@global.query_cache_limit |
+—————————-+
| 1048576 |
+—————————-+
这个是默认的数值，如果需要修改，就像设置缓存大小一样设置，使用set重新指定大小。
好了，通过4个步骤就可以打开了查询缓存，具体值的大小和查询的方式这个因不同的情况来指定了。

5.不被缓存的函数

如果一个查询包含下面函数中的任何一个，它不会被缓存：

BENCHMARK()	CONNECTION_ID()	CURDATE()
CURRENT_DATE()	CURRENT_TIME()	CURRENT_TIMESTAMP()
CURTIME()	DATABASE()	带一个参数的ENCRYPT()
FOUND_ROWS()	GET_LOCK()	LAST_INSERT_ID()
LOAD_FILE()	MASTER_POS_WAIT()	NOW()
RAND()	RELEASE_LOCK()	SYSDATE()
不带参数的UNIX_TIMESTAMP()	USER()

 

在下面的这些条件下，查询也不会被缓存：

引用自定义函数(UDFs)。

引用自定义变量。

引用mysql系统数据库中的表。

下面方式中的任何一种：

SELECT ...IN SHARE MODE

SELECT ...FOR UPDATE

SELECT ...INTO OUTFILE ...

SELECT ...INTO DUMPFILE ...

SELECT * FROM ...WHERE autoincrement_col IS NULL

最后一种方式不能被缓存是因为它被用作为ODBC工作区来获取最近插入的ID值。

 

 

 

评论

1 楼 hnraysir 2014-03-03  

down下来看看