MySQL 基础知识
http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/ MYSQL手册mysql>? show //所有命令的说明帮助信息
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS FROM tbl_name
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW ENGINES
SHOW ERRORS row_count]
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name
SHOW INNODB STATUS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS
SHOW LOGS
SHOW MUTEX STATUS
SHOW OPEN TABLES
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW PROFILE
SHOW PROFILES
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
SHOW WARNINGS row_count]
1.用yum安装mysql时如何选择安装路径，默认安装在哪个路径下？
先用rpm -qa *mysql*看看你安装了哪些包，然后用 rpm -ql xxxxxxx 去查看具体的包安装位置 主要安装涉及到的目录有 /usr/bin;/usr/share;/var/log 2.Mysql数据库的主要配置文件有哪些？
主要的配置文件是/etc/my.cnf
此文件中指定了数据库的存放位置 # datadir=/var/lib/mysql
3.数据库文件存放在哪个路径下？
默认存放在 datadir=/var/lib/mysql
默认在:/var/lib/mysql/ 按目录存放
1.关闭mysql
2.移动/var/lib/mysql/目录下的数据库目录到自定义目录,如:/home/mysql_datas
原数据库目录结构
比如/var/lib/mysql/mysql
/var/lib/mysql/business
/var/lib/mysql/upper等
移动后结构
比如/home/mysql_datas/mysql
/home/mysql_datas/business
/home/mysql_datas/upper等
3.修改/etc/my.cnf中的如下两项到新目录的位置
datadir=/home/mysql_datas
4.修改mysql启动脚本中上面两项的位置
#vi /etc/rc.d/init.d/mysqld
get_mysql_option mysqld datadir "/var/lib/mysql"改为
get_mysql_option mysqld datadir "/home/mysql_datas/mysql"
5.启动mysql
mysql>\. //source (\.) Execute an SQL script file. 执行SQL脚本
自动递增： id int primary key auto_increment;
windows 系统下mysql的配置：
mysql 的默认端口3306
mysql客户端连接成功后，如果要执行系统命令，可以加system+系统命令
4.安装完成后配置环境变量:
找到安装目录中D:\xxxxxxxx\bin添加到系统的环境变量中
5.启动 和关闭:
net stop mysql（服务名） ； net start mysql (服务名)；系统服务进程
6.连接mysql：
mysql -uroot -p123456 -h hostname/IP
7.查看mysql的基本信息
mysql>show databases; //查看所有数据库
mysql>show status; //查看运行状态
mysql>show tables; //查看某个数据库中的表
mysql>use mysql; //使用某个数据库
mysql>select version(); //查看数据库版本
mysql>select current_date; //查看当前日期
mysql>select now(); //查看当前时间点
mysql>select user(); //查看当前登录的用户
mysql>select database();select schema();//查看当前数据库
mysql> desc user; //显示出表user的具体字段内容
mysql> flush privileges; //更新表权限后，执行使命令生效
mysql>show processlist; //查看当前用户的连接信息
mysql>select user();select current_user();select system_user();select session_user(); //查看当前用户
mysql>select password(str);select md5(str); //加密不可逆
mysql>select encode(str,pswd_str); //加密可逆
mysql>select decode(crypt_str,pswd_str); //解密函数
mysql>show grants; // 查看当前用户的权限信息
mysql>show grants for user@'hostname'; //查看指定用户的权限信息
mysql>set password for root@localhost=password('在这里填入root密码'); ← 设置root密码
mysql>update user set password=md5('wangdong') where user='wangdong';
//为某个用户设定加密的
注：在向数据库中user表添加账号，用于管理数据库时，使用insert 语句添加用户时，指定的密码被系统用password加密方式加密。如果insert语句中，password='1234'，实际的是1234是加密后的密码，当登录输入1234密码时，提示密码错误。故建议不要通过insert语句向user 表中添加用户，如果使用insert添加，请先使用select password('wangdong');把明文密钥'wangdong',经过加密后产生的字符串，填写到insert语句中的password字段。
建议使用 create table user 'wangdong'@'127.0.0.1'=password('wangdong'); //明文就是wangdong,保存到user表中的加密后的字符串。
mysql数据库默认是通过password加密方式对用户的明文密码进行加密
数据库默认的三个数据库作用：
第一个个数据库INFORMATION_SCHEMA:是信息数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息
第二个数据库mysql：这个是mysql的核心数据库，类似于sql server中的master表，主要负责存储数据库的用户、 权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除， 如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。
第三个数据库test:这个是安装时候创建的一个测试数据库，和它的名字一样，是一个完全的空数据库，没有任何表，可以删除。
8.mysql的Notifier 是对mysql服务器的监控工具
9.mysql 管理工具:
mysql;Navicat;MySQL-Front;phpmyadmin;MySQL Workbench；mysql-administrator;
10.mysql 存储引擎
mysql> show engines; //查看数据所有的存储引擎
mysql>show variables like '%storage%'; //查看当前的存储引擎
mysql>show create table user\G; //查看表的创建过程，以及创建表所用的存储引擎
mysql> create table test02 (id int) engine=INNODB; //创建表时指定存储引擎
web、数据仓库应用环境下最常使用的存储引擎是myisam，较高的插入、查询、但不支持事务。archive 引擎支持insert ,select 不支持delete，update ,replace 常用于日志文件的存储文件格式 *.ARZ
my.ini是window下mysql的配置文件，my.cnf是linux下mysql的配置文件
myisam 和innodb的区别：
MYISAM不支持外键和事务；innodb 支持外键和事务；数据存储方式不同，myisam建一张表对于三个文件，innodb建一张表对于一个文件.frm
myisam存储引擎：
不支持外键和事务，mysql的默认存储引擎；myisam建一张表对于三个文件，*.frm ,*.myd,*myi；数据文件和索引文件可以放置在不同的文件，在建表的时候通过data directory和index directory定义存储位置；
myisam引擎提供对表的修复工具，check table name 检测表；repair table name 修复表
myisam 表支持3种不同的存储格式：动态表，静态表，压缩表
静态表示默认的存储格式，占据定长的列空间；动态表中包含变长字段，占有的空间少，但是易产生碎片，optimize优化压缩表有myisampack工具创建，占据的空间很小
innodb 存储引擎：
支持事务，外键；数据存储一个文件中*.frm；写效率没有myisam引擎好，占据的空间会大用于存放索引和数据；innodb 支持外键，在创建外键的时候，父表必须有对应的索引，子表在创建外键的时候才会自动创建索引创建索引的时候，可以指定在删除，更新父表时，对子表进行的操作有restrict,cascade,set null, no action；restrict和no action相同是子表有相应变化时，父表不变化；cascade 表示父表的更新和删除时，子表也做相应的更新和删除；set null表示父表更新或者删除时，子表中对于的字段被设为null；
例：constraint 'fk_city_country' foreign key (country_id) references country (country_id) on delete restict on update cascade
当某个表被其他表创建了外键参考，该表对应的索引和主键禁止修改。
关闭外键约束 set foreign_key_checks=0 （=1 开启）
查看外键信息 show table status //show table status like 'teacher'\G
innodb 存储方式：共享表空间存储；多表空间存储
memory 存储引擎：
表存放的内存中，速度很快，但是关机后数据会丢失，默认使用hash 算法；每个memory表对应一
个磁盘文件*.frm；为memory表创建索引的时候可以指定使用hash 索引，还是使用btree索引；
在启动mysql是可以使用--init-file选项，把insert..into和load data infile 这样的语句放在
这个文件中，就可以在系统启动的时候从持久固定的源装载表；
每个memory 表中可以存放的数据量的大小受 max_heap_table_size的限制，默认16M，可以在
建表时用max_rows指定最大行数
merge存储引擎：
merge 是一组myisam表的组合，merge表本身没有数据，对merge类型的表的操作实际内部是对myisam表的操作；
对merge类型表的drop，只是删除了merge的定义对内部的数据并没有变动；
一个merge表有*.frm和*.mrg两个文件;*.mrg文件存放组合表的信息
对merge类型的表的插入操作，是通过使用insert_method=first|last|no (第一个表，第二个表，不能插入)
例：engine=merge union=(表1，表2.....) insert_method=last;
11.mysql的数据类型
数值类型
整数： tinyint 1字节 tinyint(M) unsigned zerofill M:宽度unsigned：无符号类型 zerofill:0填充 smallint 2字节；mediumint3字节；int 4字节；bigint 8字节
小数：float 4字节；double 8字节；decimal 浮点和定点类型都可以用（M,N）表示，M表示总的位
数，N表示小数的位数
日期/时间类型
year YYYY 1字节；1901-2155
输入2位,'00-69'表示2000-2069; '70-99' 表示 1970-1999；
time HH:MM:SS 3字节；date YYYY-MM-DD 3字节；
datetime YYYY-MM-DD HH：MM：SS 4字节 ；
timestamp YYYY-MM-DD-HH-MM-SS 8字节； 实际开发中使用时间戳来精确到秒的列
timestamp支持的范围小1970010108001到2038年的某个时刻；datetime 范围1000-01-01
00：00：00到9999-12-31范围大
timestamp可以根据时区的影响，能准确反映出当地的时间；datetime不会根据时区的影响，时
区不同，时间不同，有误差；
字符串类型
char 固定长度 ; char(M) 存储内容不足M个时，后面用空格填补。
varchar可变长度;binary;varbinary;blob;text;enum枚举类型;set
char和varchar型的选择原则：1.空间利用率2.速度
text 文本类型，大文本，没有默认值
检索时char 删除了尾部的空格，varvhar保留了尾部的空格
enum('M','F')枚举类型
set是和enum很类似，也是一个字符串对象，可以存储0-64个成员，1-8个字节；set 类型可以一次选择多个成员，而enum只能选择一个成员；对于输入的值在set范围内的，都可以正确地注入到set类型的列中，超出范围的，不写入，重复的只取一次
运算符
算术 + - * / （除，返回商）%（模，返回余数）；如果除数为0，返回null；
比较 > < = >=
is null 判定是否为空
is not null 判定是否不为空
isnull
12.mysql 常用函数
/**************字符串函数*********************/
select insert('ring',2,0,'ok'); /*从2的地方插入ok字符，插入时替换0个字符*/
select repeat('ring',2); /*重复输出字符串2次*/
select REVERSE('ring'); /*反写*/
select mid('ring',2,2);/*从某个位置取某个长度的字符*/
select instr('ring','i');/*查找字符*/
select replace('very good','o','O'); /*替换所有的字符*/
select LENGTH('very good'); /*返回字符串长度*/
select ascii('abc');/*返回最左边的字符ascii码*/
select space(3);/*产生3次空格*/
select CONCAT('ri','n','g',' wang'); /*组合成字符串*/
select lcase('RING'); /*小写*/
select ucase('ring'); /*转换成大写*/
select substring_index(user(),'@',1); /*提取当前的用户名,表示提取@左边第一出现的字符，如果后面的为负数则提取右边的字符*/
/**********************数学函数******************/
select conv('ee',16,10); /*将'ee'从16转换成10，16和10可以是任意进制*/
select bin(10); /*2进制*/
select hex(10); /*16进制*/
select oct(10); /*10进制*/
select round(12.5678,2); /*将小数保留两位，四舍五入*/
select round(123.8767); /*转换为整形，因为没有小数位*/
select char(65,66,99); /*将ascill码组合成字符串*/
select least(3,6,5,1); /*最小数字*/
select greatest(6,3,2,8,3,1);/*最大数字*/
select power(2,3); /*2的3次幂*/
select sqrt(9); /*开方*/
select pi(); /*圆周率 */
select RAND(11); /*返回0-1的随机数，指定种子，每次的数都一样*/
select rand();/*没有指定种子，默认为时间为种子*/
/********日期时间函数*****************/
select now();
select to_days('2008-07-6'); /*转换成天数*/
select to_days('2008-8-8')-to_days(now());/*相差的天数*/
select day(now());/*该日期中的时，分，秒，天，月，年 hour(),minute(),second(),month(),year()*/
select dayofyear('2008-2-2'); /*返回该年已过的天数*/
select monthname(now()); /*返回月的英文名*/
select dayname(now());/*星期几的英文名*/
select week(now(),0); /*表示当前的日期是第几周，后面的数字0表示从周日开始算，1表示从周一开始算*/
select from_days(734215); /*返回该天数表示的日期*/
select DATEDIFF(now(),'2008-8-8'); /*前面的日期减去后面的日期所得的天数*/
select date_add(now(),interval 2 day); /*表示给当前的日期加2天*/
select date_add(now(),interval 2 month); /*表示给当前的日期加2个月*/
select CURRENT_DATE(); /*当前日期，短格式*/
select CURRENT_TIME();/*当前的时间*/
select current_user();/*当前的用户*/
select date_format(now(),'%Y-%m-%d %H:%i:%s');/*日期的格式，Y表示4位，H表示24小时制*/
select date_format(now(),'%T'); /*只显示时间，没有日期，T表示24小时制,r表示12小时制*/
select date_format('2008-08-08 14:08:08','%Y-%m-%d'); /*显示日期部分，时间不要*/
select date_format(now(),'%Y-%m-%d %H:%i:%s %W'); /*带日期的*/
select date_format('2008-8-8','%W');/*该日期的星期几*/
/***********其他函数****************/
select database();/*返回当前的数据库名字*/
select substring_index(CURRENT_USER(),'@',1);/*提取当前的用户名*/
select password('123'); /*mysql的加密方式*/
select MD5('123');/*32位的md5加密*/
select LAST_INSERT_ID();/*返回最后一次自动产生的ID值*/
select format(123456789.565378,4);/*输出123,456,789.5654有四舍五入*/
/***************控制流函数*************/
select if(1,2,3);/*如果第一参数为空则输入第三参数，否则输入第二个参数,后面的参数可以是字符,第一为整形*/
select ifnull(null,2); /*如果第一参数为空则输出第二个参数，否则输出第一参数*/
select
name,
case
when age revoke select on mysql.* from 'mysqladmin'@'127.0.0.1'; // 回收权限给用户
mysql> grant select on mysql.* to 'mysqladmin'@'127.0.0.1'; //分配权限给用户
15.mysql的访问控制
连接控制是根据mysql数据中的user表中的user,host,password三个参数去判断的；
通过yum 安装完mysql后，默认是没启动的，需要执行下列命令进行启动
chkconfig --list | grep -i mysqld //检测mysql服务是否启动
chkconfig mysqld on //开启mysql服务
service mysqld start //启动mysqld服务
解决1045,1044问题
1.停用mysql服务：# /etc/rc.d/init.d/mysqld stop
2.输入命令：# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & //跳过用户权限检查表，可以用于破解用户密码
3.登入数据库：# mysql -u root
4.mysql> use mysql;
5.修改mysql数据库下的user表信息
select user,host from mysql.user; //查询表中的信息
update 相应的用户信息
6.flush privileges
16.基本知识
mysql> show variables like "character%"; //查看默认编码格式
mysql>rename table oldname to newname //更改表名
mysql>desc user; //查看表结构
create table msg(
>
> name varchar(10),
content varchar(1000)
);
create table goods(
name varchar(10) not null default '',
price float(5,2) not null default 0.00
)
增
insert into msg
values(3,'初来','张三','测试使用')；
更新
update msg set>
update msg set>
update msg set> 删
delete from msg where> 查询
select * from msg;
增加一列
alter table msg add address varchar(10);
列的默认值
Not null default 0;
create table info
(
id int primary key auto_increment,
name char(4) not null,
sex enum('男','女') default '男',
age tinyint,
email varchar(30),
telephone char(11),
note varchar(100),
salary double,
goschool date
)
insert into info values('','王东','男','25','111@qq.com','123456','河南人','1000.123','2014-05-08')
insert into info values('王东冬','男','25','111@qq.com','123456','河南人','1000.123','2014-05-08')
insert into info (name,sex) values('王五','男');
insert into info (name,sex) values('李海','女');
查询的5种子句：where,group,having,order by,limit
select语句
消除重复的行，使用关键字
where
in 表示在某个范围内，任意一个都可以
between......and在两个值区间
not in 取出不再某个范围内的
模糊查询 like% 统配任意字符，_ 统配单个字符
group by 分组 主要用于统计数据，要结合统计函数使用才体现出具体意义；把具有相同属性的归为一组，然后再统计比较这个组中特定选项
avg() 平均值;sum() 总和;count() 数量;max() 最大值;min() 最小值;
select count(*) from goods;//统计表中有多少行
select name,count(*) as count from goods group by name; // as 取别名
having 是对查询结果的列进行筛选，where查询是对数据表中具体的列进行数据查询
order by排序,可以升序排asc，也可以降序排desc，默认升序排列
order by 可以按照多个列进行比较，order by 列1，列2
limit 在语句最后，起到限制条目的作用 limit ,N //偏移量
select * from goods order by price limit 3,2；
//排除前3，取从第三开始的第四，第五两行
当多个子句一起使用时，书写的顺序要按照where ,group by ,having,order by ,limit
子查询：
where型
把内层查询的结果作为外层查询的条件
from型
把内层查询的结果当作临时表，供外层sql再次查询，内层的查询结果看出临时表，加"as 临时表名"
exists型
把外层查询结果拿到内层，然后查看内存查询是否成立
union的用法：合并查询的结果；
要求：两次查询的列数一致，可以来自多张表；查询的列中，如果多个表中的列数据相同，默认情况下去重复，union all 显示重复
子句中有order by ,limit,须加()；子句中order by 须结合limit使用，单独使用没有意义
左连接、右连接、内连接
左连接语法格式：select 列1，列2,..... from tableAleft join tableB on A.列=B.列 (此时就把A，B两个表整合成一个大表)
右连接语法格式：select 列1，列2,..... from tableAright join tableB on A.列=B.列 (此时就把A，B两个表整合成一个大表)
内连接语法格式：select 列1，列2,..... from tableAinner join tableB on A.列=B.列 (此时就把A，B两个表整合成一个大表)
注释：左连接以左表为准，去右表匹配数据，找不到匹配，用null补齐
左右连接可以相互转换，推荐以左连接代替右连接
内连接是筛选出左右表中都有的数据，既左连接和右连接的交集数据
同一张表多次被连接时，可以通过as取别名进行区别 A left join B as B01 on A.ID=B.ID lift jion B as B02 on ......
表的列之修改
增列
>
>
> 修列
>
> change 和modify都能修改列的属性，不同的是change可以修改列的名字，而modify仅用与修改列的类型
> 删列
> 修改表名
>oldname rename newname //alter table info rename infomation;
视图
视图是由查询结果形成的一张虚拟表，仅是为了提高查询速度而已，把某些查询结果保存起来，方便以后的查询
create view 视图名 as select 语句
删除视图： drop 视图名
视图优点：1.简化查询2.权限管理，可以隐藏某些列，不让别人查看3.拆分大数据表结合模%
修改视图：> 注：视图是表的查询结果，自然表的数据改变了，定影响视图的数据；视图的数据改变了会影响到表的数据，但是并不总能影响表的数据，只有在 视图的数据和表数据一一对应时，视图中数据的改变才会影响到表中的数据
视图的algorithm
merge:默认是该算法，是把创建视图时的select 语句where 条件和使用视图查询时的where条件进行合并组成一个条件查询相应的原始表
temptable: 是在创建视图时瞬间创建一个临时表，查询视图中的数据就是去查询这张临时表中的数据
//create algorithm=temptable view 视图名 as select 语句
字符集与校对集
MySQL字符集的设定：服务器字符集；数据库字符集；表字符集；某一个没有设定字符集，就继承上一级的字符集；
字符集转换过程:client---->字符集转换器----->服务器
1.client 告诉服务器我传送给你的数据是什么编码？character_set_client
2.告诉转换器转换成什么编码？character_set_connection
3.服务器把查询结果反馈给client时，查询结果用什么编码？character_set_results
注：如果1，2，3，采用相同的编码N，可简写为set names 编码N
总结：
create view viewname as select *......
merge:
视图
查视图
形成sql:where> temptable：
视图
查视图 sql2---->[临时表]服务器
客户端监视事件(insert/update/delete)-->触发时间(after/before)-->触发事件(insert/update/delete)
create trigger triggername
after/before insert/update/delete on 表名
for each row//此句话被数据库系统写死
begin
SQL触发语句；
end; //创建触发器时，由于MYSQL数据库默认的SQL语句是遇到分号(；)表示语句执行完毕，而end后也有分号，所有在创建触发器时需要更改默认的语句结束符 delimite @
如何在触发器中引用行的值？
对于insert而已，新增的行用 new 表示；应用新行种的列用New.列名 表示 delete old.列名
删除触发器drop trigger triggername;
create table pro(
pid int not null primary key auto_increment,
name varchar(5),
number int);
insert into pro(name,number) values('a',10),('b',20),('c',15);
create table sal(
sid int not null primary key auto_increment,
name varchar(5),
number int);
create trigger test
after insert on sal
for each row
begin
update pro set number=number-new.number where name=new.name@
end;
存储引擎和事务
数据库对同样的数据，有不同的存储方式和管理方式，在mysql中被称作存储引擎
数据库优化
1.数据库的设计（表的设计）
要符合三范式 3NF：
1NF：原子性，不可分割
2NF：满足1NF的基础上，满足2NF。表中记录要唯一，不能出现完全相同的记录。即实体唯一性
3NF：字段冗余性约束，任何字段不能由其他字段派生出来，字段没有冗余。
注：生产环境中，逻辑设计满足范式，物理设计可以降低范式要求（增加字段的部分冗余）；逆范式1-n,最好把冗余归于1（少）的一侧；
2.SQL查询语句优化
1. 通过show status命令查看个SQL语句的执行频率；
show status 命令可以显示你的mysql数据库的当前状态，主要查看的是“com”开头的指令
show status like 'com%'= show session status like 'com%'//显示当前控制台的情况
show global status like 'com%';//显示数据库从启动到当前的情况
show global status like "com_select";
show status like "uptime"; //数据库从启动到当前的运行时间
show status like "connections"; //数据库从启动到当前的并发连接数
show status like "slow_queries"; //显示查询速度慢的语句（默认小于10秒）
2.定位执行效率低的SQL语句（重点select）
存储引擎是myisam的，当创建一个表后，生产的三个文件 *.frm 表结构；*.myd 数据；*.myi 这个是索引；找到慢查询的selec,mysql数据库支持把慢查询的语句记录到日志中，程序员分析。（默认情况下不启动）
# service mysqld start --slow-query-log//开启慢查询日记记录
mysql> set long_query_time=1; //设置服务器的慢查询限定时间 1秒，超出1秒定位慢查询，并记
录到日志中
mysql> show status like "slow%"; //查询服务器中出现的慢查询语句
通过日志定位出慢查询的语句后，可以通过建立索引来加快查询速度。但是索引的缺点是，查询速度增加了，增，删、改速度相当慢了，*.myi文件占据的空间有大了
3.通过explain分析低效率的SQL语句的执行情况
在执行某条SQL语句前通过explain分析改语句的执行效率情况；根据返回的信息可以看出SQL语句是否使用索引，是从多少条记录中把数据取出的，以及排序的方式；
explain 语法中的字段：type 类型，all,system,const 一条记录//type类型的不同可以判断出语句的执行扫描范围
possible_keys: PRIMARY//可能用到的索引
row： 1//表示从多少数据中查询
extra: //查询详细信息
explain select * from goods where> 4.确定问题并采取相应的优化措施
SQL语句有几类：
ddl（数据定义语言）
dtl（数据事务语句）
dcl(数据控制语言)
3.数据库的配置
4.硬件环境配置
索引
索引建立的适合情况：1.较为频繁地作为查询条件的列 2.经常改动的列不适合做索引 3.不作为where查询条件的列
索引的类型：查询某个表中有多少索引(show index from 表名)
主键索引：把某列设为主键，即就是主键索引 （primary key）
唯一索引：unique
普通索引：index
全文索引：fulltext（仅MYISAM存储引擎支持）
复合索引：多列合在一起构成复合索引
索引的操作：
索引的创建：
1.如果创建unique /fulltext /普通索引有两种方式
1.create index 索引名 on 表名 （列1，列2，.......）
2.alter table 表名 add index 索引名 （列名，......）
2.如果是创建主键索引：> 删除索引：
1.普通索引的删除
drop index 索引名 on 表名
> 2.删除主键
> 查询索引
show index from 表名 ； show keys from 表名
索引的使用
1.下列情况将不使用索引
1.条件中有or；对于队列索引，不是使用第一部分则不会使用索引；like查询是以%开头；
2.列类型是字符型，条件中要将数据引起来，否则不使用索引
查询索引的使用情况
1.show status like 'Handler_read%'; //显示索引的使用情况
handle_read_key 越大越好，说明索引使用率比较高
handle_read_rnd_next 越小越好
对数据库中表空间的优化命令：
optimize table 表名 // 该命令主要用于当删除了某个大表中的数据内容后，表所占
的空间并没有释放，使用该命令可以释放出空间
大批量插入数据
对于MYISAM：
> loading data;
> 对于innodb:
1.将要导入的数据按照主键排序
2.set unique_checks=0,关闭唯一性校验；
3.set autocommit=0,关闭自动提交
常用的优化方法：
1.order by null //当使用group by 分组时，系统默认是排序，为了提高速度可以清除默认排序
2.建议多使用连接查询join，少用子查询，因为子查询会在内存中建立临时表
3.在精度要求高的应用中，最好使用decimal(m,n)类型；建议多使用这种类型
4.时间类型要根据实际的需求选择满足需求的类型
5.对于超大的表，可以采用表分割技术：水平分割，垂直分割
6.调整数据库参数的连接数大小，缓存大小
7.读写分离的实现，一主多辅
基础篇
/usr/share/mysql目录下，提供了my.cnf主配置文件的参考范本
linux启动MYSQL的方式：
cd /usr/bin ./mysqld_safe & ; mysqladmin -uroot shutdown
如果是通过RPM包安装的：service mysqld start/stop/restart
DDL基本语句
create database name; drop database name; desc tablename;show create table tablename\G;
删除表: drop table tablename;
修改表：alter
管理维护
mysql的3种安装方式：
1.rpm -ivh 安装 //最简易的安装
2.二进制安装
1.用root登录，增加mysql 用户和组，数据库将安装在此用户下 groupadd mysql
;useradd -g mysql mysql
2.解压二进制安装包，进入压缩包所在目录 tar -xzvf mysql-xxxx.tar.gz
并对解压后的目录增加一个链接ln -s mysql-xxx mysql
3.在数据目录下创建系统数据库和系统表，--user 表示数据库和表的owner用户
cd mysql scripts/mysql_install_db --user=mysql
4.设置目录权限，将data目录owner 改为mysql，其他目录和文件为root
chown -R root:mysql . chown -R mysql:mysql data
5.启动MYSQL
bin/mysqld_safe --user=mysql &
3.源码安装
1.用root登录，增加mysql 用户和组，数据库将安装在此用户下 groupadd mysql
;useradd -g mysql mysql
2.解压源码，并进入解压目录
tar -xzvf mysql-xxxx.tar.gz
3.用configure工具来编译源码，可以有很多参数，参考configure --help 。假定安装
/usr/local/mysql
./configure --prefix=/usr/local/mysql
make && make install
4.复制配置文件到/etc目录下
cp support-files/my-medium.cnf/etc/my.cnf
5.在数据目录下创建系统数据库和系统表，--user 表示这些数据库和表的owner是此用户
cd /usr/local/mysql bin/mysql_install_db --user=mysql
6.设置目录权限，将var 目录owner改为mysql （默认的数据目录）其他目录和文件为root
chown -R root . chown -R mysql var chgrp -R mysql .
7.启动MYSQL
bin/mysql_safe --user=mysql &
mysql升级的3种方法：
A.方法一：
1.在目标服务器上安装新版本的MYSQL
2.在新版本的mysql上创建和老版本同名的数据库
mysqladmin -h hostname -p port -u user -p passwd create db_name
3.把老版本mysql上的数据库通过管道导入到新版本数据库中 --opt 采用优化optimize
--compress 压缩
mysqldump -h 127.0.0.1 -uroot -proot --opt test01 | mysql -h 10.69.160.5 -uroot
-proot test01
4.将旧MYSQL中mysql数据库目录全部cp到新mysql中的数据库目录下
cp -R /home/mysql_old/data/mysql/home/mysql_new/data
5.在新版本服务的shell里面执行mysql_fix_privilege_tables 命令升级权限表，然后重启服
务，完成升级
B.方法二：
1.在目标服务器上安装新版本的MYSQL
2.在旧版本MYSQL中，创建用来保存输出文件的目录并用mysqldump备份数据库
mkdir dumpdir mysqldump --tab=dumpdir db_name
--tab 不会生成sql文本，而是在备份目录下对每个表分别生成.sql和.txt文件，.sql保存
建表语句.txt保存数据
3.在新MYSQL服务器上建立新的数据，把旧mysql的备份数据装载到新的服务器上
cat dumpdir/*.sql | mysql db_name （创建数据库表） mysqlimport db_name
dumpdir/*.txt （加载数据）
eg：mysqlimport -uroot -p test01 sal.txt --local//local指定从本地文件去加载数
据而不是数据库中
4.参照方法一中的步骤4，5
C.方法三：适合MYISAM存储引擎的表，速度最快
1.安装新数据库
2.把旧数据中的数据目录下的所有文件（.frm .myd .myi）cp到新版本的相应目录下
3.参照方法一中的步骤4，5
总结：数据的升级主要是把旧版本中的数据cp到新版本中，升级前后要保证数据库的字符集一致，防止出现乱码。以上升级方法是假定旧版本的数据没有更新，否则迁移过去的数据将不能保持一致
数据库管理工具
1.mysql 客户端工具
-u-p -h -P端口
如果系统中的空用户删除了会检测my.cnf中client下的用户和密码。如果没有就用root@localhost登录
#mysql -uroot -proot test01 -e "select * from goods"
//-e 参数在没有登录数据情况下，查询信息
当批量导入sql语句是，使用参数-f(强制) -v(详细信息) --show-warnings(告警信息) 参数，帮助查询错误信息
#mysql -uroot -proot test01 -f -v --show-wariningsreset master;
2.purge master logs to 'mysql_bin.00000x' //删除x编号前的所有二进制日志
3.purge master logs before 'y-m-d h:m:s'//删除指定日期前的所有日志
4.expire_logs_days=number //修改my.cnf文件，设置日志的过期天数，自动删除
日志log-bin
日志的配置都可以通过修改/etc/my.cnf文件进行详细的配置
MYSQL的备份和恢复
mysql的备份主要分为两种:逻辑备份和物理备份
逻辑备份对各种存储引擎都能适用；而物理备份因不同的存储引擎存在不同的差异；
逻辑备份是将数据库中数据备份为一个文本文件，备份的文件可以查看和编辑。
使用mysqldump工具实现逻辑备份
备份例子：
mysqldump -uroot -p --all-database > all.sql // 备份所有数据库中
mysqldump -uroot -p test01> test01.sql // 备份test01数据库
mysqldump -uroot -p test01 sal > sal.sql // 备份test01数据库中sal 表
完全恢复：
mysqldump -uroot -p dbname < bakfile //把备份文件恢复
mysqlbinlog binlog_file | mysql -uroot -p test //由于恢复备份的数据并不完整，还需要
将备份后执行的二进制日志进行重做
不完全恢复：基于时间的恢复和基于位置的恢复
基于时间的恢复：（假如上午10点服务器发生了故障，可以跳过该该时间点进行恢复）
mysqlbinlog --stop-date='2012-06-12 9:59:59' /var/log/mysql/bin.log1 | mysql -uroot -p
mysqlbinlog --start-date='2012-06-12 10:01:00' /var/log/mysql/bin.log1 | mysql -uroot -p
基于位置的恢复：先通过二进制日志定位到特定时间内的错误语句
mysqlbinlog --start-date='2012-06-12 9:59:00' --stop-dat='2012-06-12 10:01:00' /var/log/mysql/bin.log1 > /tmp/myql/bin.log
而后编辑/tmp/mysql/bin.log的内容定位到错误语句的前后的位置号，假如'111','120'， 恢复了以前的文件后，执行如下命令
mysqlbinlog --stop-position='111' /var/log/mysql/bin.log1 \ | mysql -uroot -p
mysqlbinlog --start-posttion='120' /var/log/mysql/bin.log1 \ | mysql -uroot -p
物理备份：物理备份和恢复的速度比更快，是基于文件的cp；分为冷备份和热备份
冷备是停掉数据库，备份数据的数据文件和日志文件；然后恢复数据文件，使用mysqlbinlog工具恢复自备份来的所有binlog
这种方法对myisam和innodb存储引擎使用，但很少使用冷备。
热备对不同的存储引擎有不同的方法，现介绍myisam和innodb两种数据的热备方法：
myisam存储引擎：该存储引擎的热备，实质是将备份的表加读锁，然后cp数据到备份目录
1.mysqlhotcopy myisam自带的热备份工具 mysqlhotcopy dbnmae /path
2.手动锁表 flush tables for read; 然后再执行cp命令
innodb存储引擎：该存储引擎可以使用ibbackup工具进行热备份，属于收费软件
表的导出和导入
导出
1.select * from tablename into outfile 'targe-file'
//targe-file mysql用户要有该目录有写权限
options 参数
fields terminated by 'string' //字段分割符，默认指表符 '\t'
fields enclosed by 'char' //字段引用符，如果加optionally只在
char,varchar,text等类型上加，默认不使用
fileds escaped by 'char' //转义字符，默认\
lines starting by 'string'//每行前加字符 ''
lines terminated by 'string'//行未加字符串默认\n
2.使用mysqldump -T 命令 导出数据，生成一个txt数据，文件一个表创建脚本的sql文件
mysql -uroot -p -T target_dir dbname tablename
//mysql用户要对target_dir目录有写权限
option 参数
--fields-terminated-by 'string' //字段分割符，默认指表符 '\t'
--fields-optionally-enclosed-by 'char' //字段引用符，如果加optionally只在
char,varchar,text等类型上加，默认不使用
--fileds-escaped-by 'char' //转义字符，默认\
--lines-starting-by 'string'//每行前加字符 ''
--lines-terminated-by 'string'//行未加字符串默认\n
导入
1.load data infile 'filename' into table tablename
option 参数
fields terminated by 'string' //字段分割符，默认指表符 '\t'
fields enclosed by 'char' //字段引用符，如果加optionally只在
char,varchar,text等类型上加，默认不使用
fileds escaped by 'char' //转义字符，默认\
lines starting by 'string'//每行前加字符 ''
lines terminated by 'string'//行未加字符串默认\n
ignore number lines //number 代表数字，忽略导入数据的前几行
set 列名=exp //将某个列转换后再加载
例：load data infile '/source/mysqlbackup/backup01' into table goodsbackup ignore 8 lines set> 2.mysqlimport
mysqlimport -uroot -p dbname data.txt
--fields-terminated-by 'string' //字段分割符，默认指表符 '\t'
--fields-optionally-enclosed-by 'char' //字段引用符，如果加optionally只在
char,varchar,text等类型上加，默认不使用
--fileds-escaped-by 'char' //转义字符，默认\
--lines-starting-by 'string'//每行前加字符 ''
--lines-terminated-by 'string'//行未加字符串默认\n
--igone-lines=number//忽略前多少行数据
总结：导出和导入应掌握 select * from into file 和load data infile ，而mysqldump和mysqlimport其实是调用相应的接口而已。同时mysqldump和mysqlimport 是在外部执行的而已，select * from into file 和load data infile 需要登录到数据库内部使用而已。
MYSQL的权限与安全
用户连接数据库时，权限认证过程分为两部分：
1.通过user表中user,host,password盘点是否允许用户登录数据库系统
2.登录成功后，通过下列权限表的顺序得到数据库的权限user--db--tables_priv--columns_priv，一旦匹配就停止向下搜索。
user表中的每个权限都代表了对所有数据库都有权限；当授予部分数据库的权限是，user表中的权限会设置为no,db表的权限会相应改变；
用户的创建：grant select on *.* to username@'hostname/login_IP'> usage 权限只能登录数据库，不能做任何操作
修改密码：
1.直接修改user表，密码要加password进行加密
2.用mysqladmin -u user_name -h host_name password 'new_password'
3.set password for 'name'@'hostname'=password('xxx')
4.如果是修改自己的密码：set password=password('xxxx')
删除用户：
1.直接修改user表
2.drop user test@127.0.0.1
数据库安全：从两个方面进行讨论，操作系统层面和数据库层面
操作系统层面：
1.严格控制系统的账号和权限，对mysql用户进行锁定。
2.尽量避免使用root权限启动数据库，对数据库的相应目录进行权限控制
3.防止DNS欺骗，尽量使用ip来管理用户的登录
数据库层面：
1.删除匿名用户，系统安装完成后会自动一些默认用户，需要进行管理
2.严格控制user表的权限，不要把file,process,super权限授权给除管理员之外的其他用户
process权限可以查看进程信息，super可以有kill权限
3.删除表命令并不会回收以前的相关访问权限；删除表时，其他用户对此表的权限并没有回收，会导致如果建立同名的表其他用户会自动授权相关的访问权限，进而产生权限外流；删除表前，一点要手动回收用户权限
4.启用SSL
5.old-passwords，4.1之前的版本密码使用16位，4.1之后的秘密使用41位的。为了保证兼容性，可以用oldpassword设置4.1后的用户密码；也可以修改my.cnf配置文件，但是对数据库的安全性有所降低。
6.safe-user-create 在my.cnf中启用该参数，表示不能使用grant语句创建新用户，除非用户有mysql数据user表的insert权限
7.secure-auth 使用该参数的作用是让4.1前的客户端无法进行用户认证，及时使用old-passwords也不行。
8.skip-grant-tables 该参数使数据库系统根本不需要使用权限系统，通过使用mysqladmin flush-privileges,mysqladmin> 9.skip-network 使用该参数会禁止用户通过tcp/ip协议连接数据库
10.skip-show-database 使用该参数会使只有拥有show databases权限的用户才可以查看数据库列表。默认是所有用户都可以查看
MYSQL的复制（主从复制，数据不能保证实施同步）
复制是将主服务的DDL和DML操作通过二进制日志复制到从服务器上，然后在从服务器上对这些日志重新执行，从而使得主从服务器的数据保持一致。
SQL复制的优点：
1.如果主服务器故障，可以快速切换到从服务器。
2.可以在从服务器上执行查询，降低主服务器的性能
3.可以在从服务器上执行备份，以免影响主服务器的性能
主从复制过程：
1.至少搭建2台MYSQL数据库，尽可能地使用相同的数据库版本
2.在主数据库上建立一个复制账号，并授予replication权限。
3.修改主数据库的my.cnf文件，开启binlog，并设置server-id的值
4.在主数据库上执行读锁有效，保证没有数据操作建立统一快照// flush tables with lock;
5.得到主数据库上的二进制日志名称和位置 //show master status;
6.备份主数据库上的数据文件，解除表锁定；把主数据库的备份在从数据库上进行恢复//unlock tables;
7.在从服务器上登录数据库先停止slave stop ,运行下列的命令然后再开启slave start。
change master to
master_host='10.69.160.4',
master_user='slave',
master_password='root',
master_log_file='/var/lib/mysql/logmes/mysql-bin.1',
master_log_pos=98;
最后在主服务器上通过命令show slave status \G 查看主从配置是否成功。如果失败查询相应的错误日志主从复制的my.cnf配置文件的重要参数：
log-slave-updates//从服务器是否开启二进制日志更新，默认关闭。在进行配置链式复制时最好打开
master-connect-retry=10//从服务器重试连接主服务器的时间
read-only 使用该参数可以使从数据库拒绝普通用户的更新操作，只支持超级用户的更新操作
replicate-do-db=test01 //指定主从复制的数据库
replicate-do-table=test001 //指定主从复制的表
replicate-ignore-db=mysql //指定主从复制时忽略的数据库
replicate-ignore-table=user //指定主从复制时忽略的表
slave-skip-errors=[.....] //忽略复制过程的相应错误
总结：主从配置的要点是对my.cnf文件的配置，主的二进制日志，主从的server-id 配置；5.1版本后再配置文件中不在支持master命令可以通过登录数据库使用change master to相应的命令来配置
主从服务器切换：主服务器M，从服务器S1,S2。主M坏，把S1切换为主；
1.首先保证所有的从服务器都执行完relay log ;然后在每个从服务器是执行stop slave IO_thread;
2.在从服务器S1上执行 slave stop；然后执行reset master配置为主服务器
3.在S2上执行slave stop ,然后执行change master to master_host='s1_ip' ，最后执行slave start
4.删除新的主服务器中的master.info和relay-log.info文件，否则下次重启时还是按照从服务器启动。
5.测试中S1开启了bin-log功能，这样在切为主数据库后二进制日志会传到从服务器上，同时S1要关闭
log-slave-updates 功能，避免将执行过的二进制日志重复传给S2。
MYSQL的集群
在很多情况下，术语“节点”用于指计算机，但在讨论MySQL Cluster时，它表示的是进程。
MySQL Cluster 是一种技术，该技术允许在无共享的系统中部署“内存中”数据库的 Cluster 。
集群分为同步集群和异步集群：
同步集群：(data+mysql+mgm)
特点：
1.内存级别，对系统硬件要求低，对内存要求高
2.数据同时放在几台服务器上，冗余性好
3.可以实现高可用性和负载均衡
4.建表时必须声明 engine=ndbcluster等特点
异步集群：（master+slave）
1.主从数据库异步数据
2.数据放在多个服务器上，冗余一般
3.只有实现读写分离时，才能真正实现分担主服务器的压力，高可用性和负载均衡能力不强等
cluster 主要分三种：
负载 Load Balancing ;高可用性 High Availability; 高性能 Hight Performance
cluster 按照节点的类型可以分为3种：
1.管理节点
管理节点是对其他节点的管理，一个集群中只需要一个管理节点就可以了。在数据的访问过程中，该节点不参与数据的访问，仅对SQL节点和数据节点进行配置管理。管理服务器(MGM节点)负责管理 Cluster配置文件和 Cluster日志。 Cluster中的每个节点从管理服务器检索配置数据，并请求确定管理服务器所在位置的方式。当数据节点内出现新的事件时，节点将关于这类事件的信息传输 到管理服务器，然后，将这类信息写入 Cluster日志。这类节点的作用是管理MySQL Cluster内的其他节点，如提供配置数据、启动并停止节点、运行备份等。由于这类节点负责管理其他节点的配置，应在启动其他节点之前首先启动这类节点。
MGM节点是用命令“ndb_mgmd”启动的
2.SQL节点
SQL节点可以理解为应用和数据节点之间的桥梁，应用不直接访问数据节点，而是由SQL节点访问数据节点然后再由SQL节点去访问数据节点而后返回给应用。一个集群中可以有多个SQL节点，通过每个SQL节点访问的数据都是相同的，SQL节点越多，分配给每个节点的负载就越小，性能就越高。
3.数据节点：
这类节点用于保存 Cluster的数据。数据节点的数目与副本的数目相关，是片段的倍数。
例如，对于两个副本，每个副本有两个片段，那么就有4个数据节点。不过没有必要设置多个副本。
数据节点是用命令“ndbd”启动的。
数据节点主要用于存放集群中的数据，可以有多个数据节点，每个数据节点可以有多个镜像节点。任何一个数据节点出现故障，只有其镜像节点正常，cluster都可以正常使用。
SQL节点：这是用来访问 Cluster数据的节点。对于MySQL Cluster，客户端节点是使用NDB Cluster存储引擎的传统MySQL服务器。通常，SQL节点是使用命令“mysqld –ndbcluster”启动的，或将“ndbcluster”添加到“my.cnf”后使用“mysqld”启动。
集群的管理节点的配置文件config.ini
# MySQL NDB Cluster Medium Sample Configuration File
#
# This files assumes that you are using at least 6
# hosts for running the cluster. Hostnames and paths
# listed below should be changed to match your setup
#
NoOfReplicas: 1
#DataDir: /add/path/here
#FileSystemPath: /add/path/here
# Data Memory, Index Memory, and String Memory
DataMemory: 300M
IndexMemory: 80M
BackupMemory: 64M
# Transaction Parameters
MaxNoOfConcurrentOperations: 100000
MaxNoOfLocalOperations: 100000
# Buffering and Logging
RedoBuffer: 16M
# Logging and Checkpointing
NoOfFragmentLogFiles: 200
# Metadata Objects
MaxNoOfAttributes: 500
MaxNoOfTables: 100
# Scans and Buffering
MaxNoOfConcurrentScans: 100
PortNumber: 1186
#DataDir: /add/path/here
#
# Change HOST1 to the name of the NDB_MGMD host
# Change HOST2 to the name of the NDB_MGMD host
# Change HOST3 to the name of the NDBD host
# Change HOST4 to the name of the NDBD host
# Change HOST5 to the name of the NDBD host
# Change HOST6 to the name of the NDBD host
#
NodeId: 1
HostName: 10.69.160.3
datadir=/var/lib/mysql-cluster/
#ArbitrationRank: 1
NodeId: 2
HostName: 10.69.160.4
datadir=/usr/local/mysql/data
NodeId: 3
HostName: 10.69.160.5
datadir=/usr/local/mysql/data
NodeId: 4
HostName: 10.69.160.6
datadir=/usr/local/mysql/data
#
#Id: 6
#HostName: HOST6
#
# Note: The following can be MySQLD connections or
# NDB API application connecting to the cluster
#
NodeId: 5
HostName: 10.69.160.4
#ArbitrationRank: 2
NodeId: 6
HostName: 10.69.160.5
#ArbitrationRank: 2
NodeId: 7
HostName: 10.69.160.6
#ArbitrationRank: 2
NodeId: 8
NodeId: 9
NodeId: 10
总结：最后一点要加一个的空节点
数据节点和mysql节点的主要配置：
ndbcluster #运行NDB存储引擎
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
port=3306
ndb-connectstring=10.32.34.116 #声明管理节点
启动的顺序是，管理节点----数据节点-----mysql节点
总结：
mysql的高可用性方案：双master + keepalived 方案，两台mysql服务器互为主，通过keepalived进行故障切换。
1. #mkdir /etc/keepalived
. #vi etckeepalivedkeepalived.conf
. ! Configurationkeepalived
. global_defs {
. notification_email {
. luwenju.cn
. }
. notification_email_from luwenju.cn
. smtp_server .
. smtp_connect_timeout
. router_id MySQLha
. }
.
. vrrp_instance VI_1 {
. state #两台配置此处均是BACKUP
. interface eth0
. virtual_router_id
. priority #优先级，另一台改为90
. advert_int
. nopreempt#不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
. authentication {
. auth_type PASS
. auth_pass
. }
. virtual_ipaddress {
. .
. }
. }
.
. virtual_server . {
. delay_loop #每个2秒检查一次real_server状态
. lb_algo wrr #LVS算法
. lb_kind DR #LVS模式
. persistence_timeout #会话保持时间
. protocol TCP
. real_server . {
. weight
. notify_down usrlocalMySQLbinMySQL.sh#检测到服务down后执行的脚本
. TCP_CHECK {
. connect_timeout #连接超时时间
. nb_get_retry #重连次数
. delay_before_retry #重连间隔时间
. connect_port #健康检查端口
. }
. }
编写检测服务down后所要执行的脚本#vi usrlocalMySQLbinMySQL.sh
#!binsh
pkill keepalived
#chmod x usrlocalMySQLbinMySQL.sh
注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服 务状态，当发现real_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作， 因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP。后话：世间万事万物，都不具备绝对的完美，就像上面的MySQL-HA一样，keepalived只能做到对3306的健康检查，但是做不到比如像 MySQL复制中的slave-SQL、slave-IO进程的检查。所以要想做到一些细致的健康检查，还得需要借助额外的监控工具，比如nagios， 然后用nagios实现短信、邮件报警，从而能够有效地解决问题。
页:
[1]