mysql学习笔记

 

Mysql的安装

 

1、rpm安装步骤:

 

大多数情况下，下载Mysql-server和Mysql-client就够用了，安装方法如下：

 

shell> rpm –ivh Mysql-server-VERSION.i386.rpm

 

shell> rpm –ivh Mysql-client-VERSION.i386.rpm

 

2、二进制安装步骤：

 

root登陆，执行如下步骤：

 

shell> groupadd mysql

 

shell> useradd –g mysql mysql

 

shell> cd /home/mysql

 

shell> tar –xzvf /home/mysql/mysql-VERSION-OS.tar.gz

 

shell> ln –s mysql-VERSION-OS /usr/local/mysql

 

shell> cd /usr/local/mysql

 

shell> scripts/mysql_install_db —user=mysql

 

shell> chown –R root:mysql .

 

shell> chown –R mysql:mysql data

 

shell> bin/mysql_safe —user=mysql &

 

3、源码安装步骤：

 

shell> groupadd mysql

 

shell> useradd –g mysql mysql

 

shell> gunzip < mysql-VERSON.tar.gz | tar –xvf -

 

shell> cd mysql-VERSION

 

shell> ./configure —prefix=/usr/local/mysql

 

shell> make

 

shell> make install

 

shell> cp support-files/my-medium.cnf /etc/my.conf

 

shell> cd /usr/local/mysql

 

shell> bin/mysql_install_db —user=mysql

 

shell> chown –R root .

 

shell> chown –R mysql var

 

shell> chgrp –R mysql .

 

shell> bin/mysql_install_db —user=mysql

 

mysql启动与停止：

 

1、启动：

 

shell>sudo ./mysqld --user=mysql & 后台运行；

 

2、停止：

 

shell>sudo ./mysqladmin -uroot -p shutdown

 

数据备份与恢复:

 

1、冷备份：

 

备份：

 

(1) 停掉mysql服务，在操作系统级别备份mysql的数据文件。

 

(2) 重启mysql服务，备份重启以后生成的binlog。

 

恢复：

 

(1) 停掉mysql服务，在操作系统级别恢复mysql的数据文件。

 

(2) 重启mysql服务，使用mysqlbinlog恢复自备份以来的binlog。

 

2、逻辑备份：

 

备份：

 

(1) 选择在系统空闲时，比如在夜间，使用mysqldump –F(flush-logs)备份数据库。

 

mysqldump –u root –p *** pointcard –F > pointcard.sql

 

(2) 并备份mysqldump开始以后生成的binlog。

 

恢复：

 

(1) 停掉应用，执行mysql导入备份文件

 

mysql –u root –p *** pointcard < pointcard.sql

 

(2) 使用mysqlbinlog恢复自mysqldump备份以来的binlog

 

Mysqlbinlog $HOME/data/mysql-bin.123456 | mysql –u root –p ***

 

3、单个表的备份：

 

备份：

 

(1) 方法一：

 

mysql > select * into out file '/tmp/order_tab' fields-terminated-by=',' from order_tab;

 

(2) 方法二：

 

mysqldump –u root –p *** -T /tmp pointcard order_tab —fields-terminated-by=',';

 

恢复：

 

(1) 方法一：

 

mysql > load data[local] infile '/tmp/order_tab' into table order_tab fields-terminated-by=',';

 

(2) 方法二:

 

mysqlimport –u root –p *** [--local] pointcard order_tab.txt —fields-terminated-by=',';

 

注意：如果导入和导出是跨平台操作的(windows和linux)，那么要注意设置参数line-terminated-by，windows上设置为

 

line-terminated-by='\r\n'，linux上设置为line-terminated-by='\n'。

 

 

mysql连接: mysql –h[address] -u[username] -p –P [port]

 

给mysql root账号设置口令：

 

mysql安装完毕后，root默认口令为空，需要马上修改root口令：

 

1、[zzx@localhost data]$ mysql –uroot

 

mysql> set password=password('[New Password]');

 

2、修改密码:linux命令下：set password for 'root'@'localhost'=PASSWORD('[New Password]');

 

 

应急处理：

 

忘记root密码：

 

如果你忘记了Mysql的root用户的口令，你可以用下列过程恢复它。

 

通过发送一个kill(不是kill -9)到mysqld服务器来关闭mysqld服务器，pid被保存再一个.pid文件中，通常在Mysql数据库目录中(你必须是一个Unix

 

root用户或运行服务器的相同用户做这个)：

 

kill 'cat /mysql-data-directory/hostname.pid'

 

使用—skip-grant-tables选项重启mysqld。

 

用mysql –h hostname mysql连接mysqld服务器并且用一条Grant命令改变口令，你也可以用mysqladmin –h hostname –u user password 'new password'

 

进行。

 

用mysqladmin –h hostname flush-privileges或用SQL命令FLUSH PRIVILEGES来装在权限表。

 

只授予账号必须的权限：

 

Grant select, insert, update, delete on tablename to 'username'@'hostname';

 

 

以下是mysql的存储引擎所持有的特点:

 

 

1、Myisam是Mysql的默认存储引擎，当create创建新表时，未指定新表的存储引擎时，默认试用Myisam。

每个Myisam在磁盘上存储成三个文件。文件名都和表明相同，扩展名分别是.frm(存储表定义)、.MYD(MYData，存储数据)、.MYI(MYIndex，存储索引)。

数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。

 

2、InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以及保留

   数据和索引。

 

 

如何选择合适的存储引擎

 

选择标准：根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

 

下面是常用存储引擎的适用环境：

 

1、Myisam：默认的MySQL插件式存储引擎，它是再Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

 

2、InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

 

3、Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可以提供极快的访问。

(个人认为，此种存储引擎很少会用到，针对目前的缓存数据库，一般都会选择redis作为二级缓存)

 

4、Merge：允许MySQL DBA或开发人员将一系列等同的Myisam表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

 

CHAR和VARCHAR之间的差别：

 

 

从图中可以看出，CHAR属于固定长度的，固定多少就是多少长度，然后VARCHAR的是如果真是长度为1，那么存储会自然+1。

 

浮点数与定点数

 

在今后关于浮点数和定点数的应用中，大家要记住一下几点：

 

1、浮点数存在误差问题；

 

2、对货币等对精度敏感的数据，应该用定点数表示或存储；

 

3、编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

 

4、要注意浮点数中一些特殊值的处理。

 

索引的创建与删除

 

例如：目前存在表person（id, name）,创建一个属于name字段的索引，如下：

 

Create index name_index on person(name(5));

 

语句中的5是索引的长度。

 

如果运行一个select语句时，可以查看执行计划，这样可以看出此次查询有没有用到索引：

 

 

从上图可以看出，运用到了索引。

 

如果想删除刚刚创建的name_index索引，如下：

 

Drop index name_index on person;

 

如果运行一个select语句时，可以查看执行计划，这样可以看出此次查询有没有用到索引：

 

从上图可以看出，由于索引已经删除，所以此次查询无索引可用。

 

批量数据导入的优化

 

1、对于InnoDB类型的表是按照主键的顺序进行保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会

默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。

 

2、在导入数据前执行set unique_checks=0，关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高导入的效率。

 

3、如果应用使用自动提交的方式，建议在导入前执行set autocommit=0，关闭自动提交，导入结束后再执行set aotocommit=1，打开自动提交，也可以提高导入的效率。

 

4、对于Myisam类型的表，可以通过以下方式快速的导入大量的数据；

 

Alter table [tablename] disable keys;

 

Loading the data

 

Alter table [tablename] enable keys;

 

   这两个命令用来打开或者关闭Myisam表非唯一索引的更新。在导入大量的数据到一个非空的Myisam表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据

 

   到一个空的Myisam表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

 

通过拆分，提高表的访问效率

 

这里我们所说的拆分，主要是针对Myisam类型的表，拆分的方法可以分为两种情况：

 

1、纵向拆分：

 

纵向拆分是只按照应用访问的频度，将表中经常访问的字段和不经常访问的字段拆分成两个表，经常访问的字段尽量是定长的，这样可以有效的提高表的查询和更新的效率。

(可以理解成，如果一个表中有三个字段，其中只有一个字段A经常访问，这样就可以将字段A提出，建立一张只有A的表，剩下的两个字段建立领一张表。)

2、横向拆分：

 

横向拆分是指按照应用的情况，有目的的将数据横向拆分成几个表或者通过分区分到多个分区中，这样可以有效的避免Myisam表的读取和更新导致的锁问题。

 

(可以理解成，如果在某种应用场景下，为了分解数据库的压力，可以将数据库中的表数据进行拆分，例如：数据库中有表A，A中有1000W条数据，然后进行横向拆分

 

时，假如拆分成两张表，那就可以创建一个和A表一样表结构的表A'，然后再将A表中500W条数据迁移到A'表中，这样就达到了横向拆分的效果。)

什么情况下使用表锁

 

表级锁再下列集中情况下比行级锁更优越：

 

1、很多操作都是读表；

 

2、再严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时；

 

3、update [table_name] set [column]=[value] where [unique_key_col]=[key_value]；

 

4、delete from [table_name] where [unique_key_col]=[key_value]；

 

5、select和insert语句并发的执行，但是只有很少的update和delete语句；

 

6、很多的扫描表和对全表的group by操作，但是没有任何写表；

 

什么情况下使用行锁

 

行级锁定的有点:

 

1、当在许多线程中访问不同的行时只存在少量锁定冲突；

 

2、回滚时只有少量的更改；

 

3、可以长时间锁定单一的行。

 

行级锁定的缺点：

 

1、比页级或表级锁定占用更多的内存；

 

2、当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁；

 

3、如果你在大部分数据上经常进行group by操作或者必须经常扫描整个表，比其它锁定明显慢很多；

 

4、用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

 

如何减少锁冲突

 

1、对Myisam类型的表：

 

(1) Myisam类型的表可以考虑通过改成Innodb类型的表来减少锁冲突；

 

(2) 根据应用的情况，尝试横向拆分成多个表或者改成Myisam分区对减少锁冲突也会有一定的帮助。

 

2、对Innodb类型的表：

 

(1) 首先要确认，再对表获取行锁的时候，要尽量的使用索引检索记录，如果没有使用索引访问，那么即使你只是要更新其中的一行纪录，也是全表锁定的。

 

要确保sql是使用索引来访问纪录的，必要的时候，请使用explain检查sql的执行计划，判断是否按照预期试用了索引。

 

(2) 由于mysql的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然是访问不同行的纪录，但是如果是相同的索引键，是会被加锁的。这里和Oracle

 

有比较大的不同。

 

(3) 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，当表有主键或者唯一索引的时候，不是必须使用主键或者唯一索引锁定纪录，其

 

他普通索引可以用来检索纪录，并只锁定符合条件的行。

 

(4) 用show innodb status来确定最后一个死锁的原因。查询的结果中，包括死锁的事务的详细信息，包括执行的SQL语句的内容，每个线程已经获得了什

 

么锁，在等待什么锁，以及最后是哪个线程被回滚。详细的分析死锁产生的原因，可以通过改进程序有效的避免死锁的产生。

 

(5) 如果应用并不介意死锁的出现，那么可以在应用中对发现的死锁进行处理。

 

(6) 确定更合理的事务大小，小事务更少地倾向于冲突。

 

(7) 如果你正使用锁定读，(select … for update 或… lock in share mode)，试着用更低的隔离级别，比如read committed。

 

(8) 以固定的顺序访问你的表和行，则事务形成良好定义的查询并且没有死锁。

 

查看Mysql server当前参数

 

1、查看服务器参数默认值：

 

Mysqld --verbose —help

 

2、查看服务器参数实际值：

 

在linux命令行中：mysqladmin variables

 

在Mysql命令行中：show variables;

 

3、查看服务器运行状态值：

 

在linux命令行中：mysqladmin extended-status

在Mysql命令行中：show status;

 

负载均衡

 

1、利用mysql赋值分流查询操作：

 

    利用mysql的主从复制可以有效地分流更新操作和查询操作，具体的实现是一个主服务器，承担更新操作，多台从服务器，承担查询操作，主从之间通过复制实现

 

数据的同步。多台从服务器一方面用来确保可用性，一方面可以创建不同的索引满足不同查询的需求。

 

    对于主从之间不需要复制全部表的情况，可以通过在主的服务器上搭建一个虚拟的从服务器，将需要复制到从服务器的表设置成blackhole引擎，然后定义

 

replicate-do-table参数只复制这些表，这样就过滤出需要复制的binlog，减少了传输binlog的带宽。因为搭建的虚拟的从服务器只起到过滤binlog的作用，

 

并没有实际纪录任何数据，所以对主数据库服务器的性能影响也非常的有限。

 

    通过复制分流查询的存在的问题是主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在差异，造成查询结果的异议，应用在设计的时候需

 

有所考虑。

 

2、采用分布式数据库架构：

 

    mysql从5.0.3开始支持分布式事务，当前分布式事务只对Innodb存储引擎支持。分布式的数据库架构适合大数据量，负载高的情况，有良好的扩展性和高可用性。

 

通过在多台服务器之间分布数据实现在多台服务器之间的负载平均，提高了访问的执行效率。具体实现的时候，可以使用mysql的Cluster功能(NDB引擎)或者自己编写

 

程序来实现全局事务。