MySQL学习笔记

re2212

1、文件
        数据的冗余和不一致性（数据的冗余指的是数据的重复，不一致指的是修改数据的时候就要修改保存在不同文件中的相同数据）
        数据的访问困难（数据量很大）
        数据孤立（孤立导致关联性很低，造成访问困难）
        完整性问题（很难解决事务问题）
        原子性问题（很难解决事务问题）
        并发访问异常（并发时有可能操作相同的数据，此时就得做出相应的策略）
        安全性问题（如何保护隐私数据）

表示层
        文件
逻辑层
        文件系统（存储引擎）
物理层
        元数据
        数据：数据块

RDBMS
        关系模型（一张二维表）（结构化数据模型，每一行保存的数据属性一样
        E-R:实体-关系型模型（把二维表拆开，对应一个实体，实体通过关系联系）
        对象关系模型：基于对象的数据模型（在表中用一个指针指向大的数据，大的数据保存在文件中）
        半结构化数据模型 XML扩展标记语言（每一行保存的数据属性不一样，为了存储，就在每一行分别存储数据的属性和值）
       
SQL：structure query language 结构化查询语言

        ANSI:ansi-sql 美国国家标准
        DML 数据操作语言
                insert delete select update
        DDL 数据定义语言
                create drop alter
                操作对象的：RDB
                库 表 索引 视图 用户 存储过程 存储函数 触发器 事件调度（相当于crontab）
        DCL 数据控制语言
                grant revoke 访问权限的语句
               
约束：
        域约束：数据类型约束
        外键约束：引用完整性约束
        主键约束：某字段能唯一标识此字段所属的实体，并且不允许为空(一个表只能有一个主键，可以用不同字段组合形成主键）
        唯一性约束：每一行的某字段都不允许出现相同值，可以为空，一张表中可以有多个唯一键
        检查性约束：限定值的范围


关系型数据
        表示层：表
        逻辑层：存储引擎
        物理层：数据文件

数据查询和存储
        存储管理器：
                权限及完整性管理器
                事务管理器
                文件管理器
                缓冲区管理器
        查询管理器
                DML解释器
                DDL解释器
                查询执行引擎

mysql单进程多线程
        守护线程
        应用线程
        thread reuse 线程重用

关系用运算
        投影：只输出指定属性
        选择：只输出符合条件的行
        自然连接：具有相同名字的属性上所有取值相同的行
        笛卡尔积：
                (a+b)*(c+d)=ac+ac+bc+bd
        并：并集


使用程序设计语言如何跟RDBMS交互：
        嵌入式SQL：
        动态SQL：
       
MySQL插件式存储引擎
        5.5.8：MyISAM （适合查询比较多，修改比较少）
        5.5.8后：InnoDB（指出事务）


MySQL用户改密
        1、mysqladmin -u username -h nostname password 'newpassword' -p
        2、mysql>set password for 'user'@'host'=password('newpassword');
        3、mysql>update mysql.user set password=password('newpassword') where condition;
       
MySQL编译安装5.5
        1、安装cmake
        tar -zxf cmake-2.8.8.tar.gz
        cd cmake-2.8.8.tar.gz
        ./configure
        make && make install

       
1、使用cmake编译mysql5.5

指定安装文件的安装路径是常用的选项
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/data/mysql
-DSYSCONFIDIR=/etc

默认编译的存储引擎包括：csv myisam myisammrig 和heap,若需要安装其他存储引擎，可以使用类似如下编译选项
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1

若要明确指定不编译摸存储引擎，可以使用类似如下的选项
-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1
例如
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1

若要编译进其它功能，如SSL等，可以使用类似如下选项来实现编译时使用
-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0

其他常用的选项
-DMYSQL_TCP_PORT=3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DENABLED_LOCAL_INFILE=1
-DEXTAR_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATTION=utf8_general_ci 字符排序
-DWITH_DEBUG=0
-DENABLE_PROFILING=1 性能分析功能

如果想清理此前的编译所生成的文件
make clean
rm CMAKECACHE.txt

2、编译安装
groupadd -r mysql
useradd -g mysql -r -d /data/mydata  -s /sbin/nologin mysql
tar -zxf mysql-5.5.25
cd mysql-5.5.25
cmake  . -LH

make && make install
       
       
       
       
       
       
MySQL客户端工具
[client]
        mysql
        mysqldump  备份
        mysqladmin 管理
        mysqlcheck 检查
        mysqlimport
        --protocol --port --shared-memory-base-name  
       
非客户端工具
        myisamchk
        myisampack
       
       
mysqld --help --verbose 查看mysql支持的各种指令的，可以直接加载配置文件中
       
       
datadir=/mydata/data 里面的 hostname.err 的错误日志可以查看启动过程中的错误，如果没有这个日志，代表指定的数据目录位置错误

mysqld启动不了
1、此前服务没关闭
2、数据初始化失败
3、数据目录位置错误
4、数据目录权限       
       
       
show character set;查看当前服务器所支持的所有字符集

show collation;显示字符集的排序规则

select last insert_id; 查询上次执行的结果



MySQL的服务器变量
        作用域：分类
                全局变量
                        和用户没有关系，每次启动mysql服务器就生效
                        show global variables
                会话变量
                        登录的用户不同，使用的变量也不同
                        show [session] variables
        生效时间：分类
                动态
                        立即调整，不用重启服务器
                静态
                        必须写在配置文件中，才能调整
                        通过参数传递给mysqld
        动态调整参数的生效方式：
                        全局：对当前会话无效，只对新建立的会话有效
                        会话：对当前会话有效，及时生效
                               
                               
                调用变量
                        显示：
                                select @@global.sql_mode
                                select @@session.sql_mode
                        设定：set  global|session 变量名='value'
                       
               
数据库的创建
        create database|schema(方案，项目)  db_name [character set=] [collate=]
       
数据库用户和权限管理
        1、user ：用户账户、和全局权限
        2、db：库级别权限
        3、tables_priv:表级别权限
        4、columns_priv：列级别权限
        5、procs_priv:存储过程和存储函数的相关权限
        6、proxies_priv：代理用户权限
       
用户账户：
        用户名@主机
        用户名：16个字符以类
        主机：
                主机名www.baidu.com
                ip:
                网络地址172.16.0.0/255.255.0.0
                通配符：%
               
权限级别：
        全局级别，库级别，表级别，列级别，存储过程和存储函数
       
               
--skip-name-resolve 提高用户连接的速度，跳过用户名的解析
--skip-grant-tables
       
       
grant all privileges on [object_type] *.* to 'user'@'hostname' ;
object_type
        function
        procedure
       
       
#############################################################
##                                数据库的安装，进入                                                   ##
#############################################################
www.mysql.com/download
percona
mariadb

软件包格式
        rpm(通用，特有） 源码包 通用二进制
        rpm list all mysql
        MySQL.i386 客户端
        mysql-server 服务端
       
        mysql 客户端程序
        mysqld 服务端程序
       
        yum -y install mysql-server
        初始化
                建立源数据库mysql，和数据数据库
        service mysqld start 完成初始化
        mysql 连接数据库
       
        用户: user@允许的客户端主机 某个用户在某个主机可以登录服务器
       
        mysql基于TCP进行客户端和服务端通信的
        如果客户端和服务器在同一台机器上则是通过套接字通信的
        /var/lib/mysql/mysql.sock 本地进程间通信，不再是网络上的套接字通信
               
        MySQL客户端
                交互式
                批处理模式 执行MySQL脚本
               
        交互式模式中的命令类型
                客户端命令
                服务器端命令
                        都必须使用语句结束符，默认为分号
                       
        关系数据库对象
        库        表 索引 视图 约束 存储过程 存储函数 触发器 游标 事务 用户 权限
       
       
        字段名称 数据类型 类型修饰（约束）
        修饰符 notnull
        字符
                不区分大小写
                                        char(n)大小固定
                                        varchar(n)大小不固定
                区分大小写                       
                                        binary（n)
                                        varbinary(n)
                                        text(n)不区分大小写
                                        blob(n)区分大小写
        int
                精确数值
                        tinyint        1
                        smallint 2
                        mediumint 3
                        int                        4
                        bigint                8
                        修饰符 unsigned
                近似数值
                        float
                        double
        日期时间
                date time datetime stamp
        布尔类型
                true flase
               
        DDL
                create alter drop
        DML
                insert update delete
        DCL
                grant revoke
               
        create user ‘user'@'host' identified by 'password';
        drop user 'user'@'host';
                host
                        ip hostname network 通配符% _
               
        grant update/delete...  on db_name.tb_name to 'user'@'host' identified by 'password';
        revoke update/delete... on db_name.tb_name from 'user'@'host'
        show grants for 'user'@'host';
        all privileges 所有权限
               
        mysql 程序开发者是通过驱动来和数据库通信的，而驱动是通过调用API，通常是capi来和底层通信的
        普通用户是通过，c/s模式来和MySQL通信，客户端把SQL发给server端，server负责调用解释器解释SQL
        然后把结果返回给客户端
       
        DBMS database management system       
        RDBMS relational database management system
       
       
/usr/local/mysql/bin/mysqld_safe --user=mysql & 启动MySQL进程
/usr/local/mysql/bin/mysql -u root -h localhost -p123

mysql>set password for 'username'@'localhost'=password('password');
flush privileges;

#mysqladmin -uroot -h127.0.0.1 -p password '123456'

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

grant all privileges on *.* to 'root'@'%' identified by 'redhat';可以远程登录
flush privileges;

图形客户端
1、phpmyadmin
2、workbench
3、MySQL front
4、navicat for mysql

#############################################################
##                                MySQL的数据库的相关操作                                           ##
#############################################################
create database hailang;         创建数据库
show databases;                                查看所有数据库
select database();                        显示当前的数据库
use databbase hailang;                选择数据库
show create database hailang 显示创建的数据库详细信息
drop database hailang;                 删除数据库
show engines;                                显示数据库支持的存储引擎
show variables like 'storage_engine' 显示默认的存储引擎
show tables;                                 显示所有的表



#############################################################
##                                MySQL的数据表的相关操作                                           ##
#############################################################
create table 表名(
id int not null primary key auto_increment,
name varchar(10) not null default 'hailang' unique ,
);

insert into 表名(字段） values(数据类型）

[constraint 约束名 ] primary key [][] 可以单字段也可以多字段，设置主键索引
[constraint 外键名] foreign key [字段名][字段名] references 主表名(主键名)
外键约束
        使一个表中的一个或者多个字段，依赖于另一个表中的主键约束
        前提是他们字段的类型必须相同，而且删除外键约束的表必须先删除外键约束
        外键约束必须有相同的存储引擎
[constraint 约束名] unique 字段名 设置唯一约束，唯一索引
字段名 数据类型 default 默认值 默认约束
字段名 数据类型 auto_increment 自动增加，前提是primary key
desc                                         查看表结构
show create table 表名\G; 查看表的详细创建信息，\G 以列显示
alter table 旧表名 rename [to] 新表名;重命名表名
alter table 表名 modify 字段名 数据类型; 修改表结构中的字段类型
alter table 表名 change 旧字段 新字段 数据类型 ;修改表结构中的字段类型
alter table 表名 add 新字段 类型 约束条件 [first][after];在表结构中添加新的字段
alter table 表名 drop 字段名; 删除字段
alter table 表名 modify 字段 数据类型 first|after 字段2 ；更改字段的的位置
alter table 表名 engine=存储引擎;更改表的存储引擎
alter table 表名 drop foreign key 外键约束名;删除表的外键约束
drop table if exists 表名1、表2; 删除表




#############################################################
##                                MySQL的数据表select的相关操作                           ##
#############################################################

select [*/字段名] from 表/视图 where[查询条件]表达式 group by having order by limit

select [单字段] from 表/视图 单字段查询
select [多字段] from 表/视图 多字段查询


###################################
where 条件查询
####################################

where 字段 条件判断符
        = 等于 <>/!= 不等于 < 小于 <= 小于等于 > 大于 >=大于等于 between

where id in (101,02);查询所有Id等于101或者02的

where id not in (11,88) ;查询所有id 不等于11和88的

where price between 2 and 4; 查询所有价格在2到4之间的

where fname like "%b"; 查询包含b的字段

%匹配多个字符
_匹配单个字符

where fname is null ;查询为空的字段；

where id='101 and price >=5; 查询某个字段等于101，而且价格大约等于5的

where id='100' or price='2'; 查询某个字段等于100或者等于2

and 的优先级高于 or

select distinct 字段名 from 表名 查询表中的所有不重复的字段


#########################################
order by 排序查询
#########################################

可以单个字段排序，也可以进行多个字段排序

order by 默认是升序排序
order by 字段 desc ;降序排序


#######################################
group by having 分组查询
#######################################
group by 字段 having 条件表达式

分组查询是对数据按照某个或者多个字段进行分组，MySQL使用group by
关键字进行分组

select s_id,count(*) as total from fruits group by s_id;
根据s_id对fruits表中的数据进行分组
group_concat()可以显示每个分组中的各个字段的值
select s_id,group_concat(f_name) as names from fruits group by s_id;


having 过滤分组
        select s_id,group_concat(f_name) as names from fruits group by s_id having count(f_name)>1;

with rollup 在所有查询出的的分组记录之后增加一条记录
rollup 和 order by 互斥


#######################################
limit  条件查询
#######################################

limit 4; 查询前四行结果
limit 4,3; 查询从第五条开始连续3个的结果



#############################################################
##                                MySQL的数据表函数的相关操作                           ##
#############################################################


count
        count(*)统计null
        count(字段)不包括null       
        与group by 一起使用时，先对某个字段进行分组，然后在分别统计每个组的数量
       
sum
        和group by 一起使用时，先对某个字段进行分组，然后再统计每组的总和

       
avg               
        和group by 一起使用时，先对某个字段进行分组，然后再统计每组的平均值
       
max
        和group by 一起使用时，先对某个字段进行分组，然后再统计每组的最大值

min
        和group by 一起使用时，先对某个字段进行分组，然后再统计每组的最小值
       

#############################################################
##                                MySQL的数据表连接查询                              ##
#############################################################

####################
内连接查询
        查询多个表中相关联的行
suppliers和fruits有相同的字段s_id

select suppliers.s_id,s_name,f_name,f_price
        from fruits,suppliers
        where fruits.s_id=suppliers.s_id ;
       
select suppliers.s_id,s_name,f_name,f_price
        from fruits inner join suppliers
        on fruits.s_id=suppliers.s_id;
       
##########################
外连接查询
        左连接
                left join返回包括左表中的所有记录和右表中的连接字段相等的记录
       
        右连接
                right join返回包括右表中的所有记录和右表中的连接字段相等的记录
               
select customers.c_id,orders.0_num
        from customers left outer join orders
        on customers.c_id=orders.c_id;
       
select customers.c_id,orders.o_num
        from customers right outer jion orders
        on customers.c_id=orders.c_id;
       
##########################
复合条件连接查询

内连接加and的条件
select customers.c_id,orders.o_num
        from customers inner join orders
        on customers.c_id=orders.c_id and customers.c_id=1001;
内连接加order by的条件
select suppliers.s_id,s_name,f_name,f_prce
        from fruits inner jion suppliers
        on fruits.s_id=suppliers.s_id
        order by fruits.s_id;

       
###########################
子查询

#######
带any和some关键字的子查询
any 和some 关键字是同义词，表示满足其中任意一条条件，就做为外层查询的条件

分别创建2个表，并且向这两个表中插入数据
create table tb1 (num 1 int not null)
create taable tb2 (num2 int not null)
insert into tb1 values(1),(13),(27);
insert into tb2 values(6),(14),(11);

select num1 from tb1 where num1 >any(select num2 from tb2);


#######
带all 的子查询
        all要满足内层查询的所有条件
       
select num1 from tb1 where num1 >all(select num2 from tb2);

############
带exists关键字的子查询
        判断子查询是否返回行，如果返回行就为真，如果没有返回行则为假
       
        select * from fruits
        where exists
        (select s_name from suppliers where s_id=107);

        select * from fruits
        where f_price>10.20 and exists
        (select s_name from suppliiers where s_id=107);
       
#########
带in关键字的子查询
        内层查询得到一个数据列，这个数据列的值将提供给外层查询语句进行比较操作

select c_id from orders where o_num in
(select o_num from orderitems where f_id='c0');

#############################################################
##                                MySQL的数据合并查询结果                             ##
#############################################################

利用union关键字，可以给出多条select语句，并且将他们的结果合并成单个结果
2个表对应的列数和数据类型必须相同。
union不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的
使用关键字ALL的作用是不删除重复行也不对结果进行排序
select s_id,f_name,f_prce from fruits where f_prce<9
union all
select s_id,f_name,f_prce from fruits where s_id in(101,101);
       
#############################################################
##                                为表和字段取别名                                   ##
#############################################################
       
select * from orders as o  where0.0_num=3001;

select f_name as fruits_name from fruits ;

#############################################################
##                                REGEXP                                              ##
#############################################################
select * from aa where f_name REGEXP "^.*";

#############################################################
注意
###########################################################

distinct 是应用于所有列，所有列上的值都不重复，不能针对部分使用

order by 在from 子句后面，如果使用limit 必须位于order by 之后



#########################################################################
#########################################################################
插入更新与删除，insert int /update /delete
########################################################################
########################################################################

insert into tablename(column_list) values(value_list);

同时插入多条记录
insert into tablename(column_list) values(value_list1),(value_list2);

将查询结果插入到表中
insert into tablename(column_list) select(column_list2) from table_name2 where(conditon);

update person set age=15,name='liming' where id=10;

delete from table_name where condition;

######################################################################
                        ###################################################
                                索引
                        ##################################################
#######################################################################

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。
使用索引用于快速找出在某个或者多个列中有一特定值的行，所有MySQL列类型都可以被索引，
对相关列使用索引是提高查询操作速度的最佳途径
索引是在存储引擎中实现的，因此每种存储引擎的索引都不一定完全相同，不且每种存储引擎也不一定支持所有索引类型
根据存储引擎定义每个表的最大索引和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256
。大多数存储引擎有更高的限制。MySQL中索引的存储类型有2中；btree和hash，具体和表的存储引擎相关myisam
和innodb存储引擎只支持btree,memory和heap存储引擎可以支持hash和btree索引

索引的分类
        普通索引和唯一索引
        单例索引和组合索引
        全文索引
        空间索引

创建普通索引       
create table book(
bookid int not null,
bookname varchar(255） not null,
index(bookname))
);

show create table book\G;

explain select * from book where bookname="ww"\G;

explain语句输出结果的各个行解释如下：
select_type行指定所使用的select查询类型，这里值为simple,表示简单的select,
不使用union或子查询，其他可能的取值有：priamary union subquery等

table行指定数据库读取的数据表的名字，它们按照被读取的先后顺序排列

type行指定了本数据表与其他数据表直接的关联关系，可能的取值有system const eq_ref ref range index all

possible_keys行给出了mysql 在搜索数据记录是可选用的各个索引

key 行是mysql 实际选用的索引

key_len 行给出索引按字节计算的长度，key_len数值越小，表示越快

ref行给出了关联关系中另一个数据表里的数据列的名字

rows行是mysql在执行这个查询时预计会从这个数据表里读出的数据行的个数

extra行提供了与关联操作有关的信息


创建唯一索引
create table t1(
id int not null,
name char(30) not null,
unique index uniqidx(id)
);

创建单例索引
create table t2(
id int not null,
name char(50) null,
idex singledx(name(20))
);

创建组合索引
create table t3(
id int not null,
age int not null,
info varchar(255),
iddex multildx(id,name,age(100))
);

创建全文索引
create table t4(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
fulltext index fulltxtidx(info)
)engine=myisam;
只有myisam支持全文索引，且只为char varchar 和text列



######################################
在已存在的表上创建索引
######################################

alter table tableanme add [unique fulltext spatial] [index key] [index_name](col_name)[asc desc]

alter table book add index bknameidx(bookname（30））;
查看指定表中的索引
show index from book\G;

######################################################################
使用create index 创建索引

create [unique fulltext spatial] index index_name on table_name(col_name)[asc desc]

create index bknameidx on book(bookname);
create unique index uniqidldx on book(bookid);



##################################################
删除索引

########################
使用alter table 删除索引
alter table table_name drop index index_name;
添加auto_increment约束字段的唯一索引不能被删除，必须去除auto_increment之后才可以删除；

########################
使用drop index 删除索引
drop index bkauandinfo on book;



#######################################################################
创建存储过程和函数
########################################################################
创建存储过程
############
create procedure sp_name ()
begin
select avg(f_price) as avgprice from fruits;
end;

delimiter //  更改结束符 或者 \d //
create procedure proc()
gegin
select * from fruits;
end//
delimiter ;

create procedure p3()
begin
set @i=1 ;
while @i<10000 do
insert into t3 values(@i);
set @i=@i+1;
end while ;
end //
show procedure status;
call p3;

############
创建存储函数
############
delimiter //
create function name ()
returns char(50)
return (select s_name from suppliers where s_call='48075');
//
delimiter;

###########
定义变量
############
declare myparam int default 100;

##############
为变量赋值
###########
set myparam=200;

###############
定义条件和处理程序
################













##########################################################################
MySQL的主从复制
########################################################################

前提2台服务器的MySQL已经安装，且可以启动
2台服务器的网络已经连通
通过phpinfo();查询lamp环境的配置
bin-log日志已经开启，show variables like "%log%";确认bin-log是否开启

首先得要在主服务器上对从服务器进行用户授权
必须保证从服务器可以通过授权的用户进行远程登录

slave上的配置
更改server-id 2 保证主从服务器的server-id不同

进入slave 的MySQL里面
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rep'@'%' IDENTIFIED BY 'rep123';
FLUSH PRIVILEGES; 创建用户账户和密码

更改slave的相关配置
change master to
master_host='192.168.216.33',
master_port=3306,
master_user='rep',
master_password='123',
#这2个配置项是根据mater的show master status;查询得出的
master_log_file='mysql-bin.000012',
master_log_pos=421;

启动slave ，
slave start;
查看salve 的状态，确定io-running 为yes
                                        确定thred-runing 也为 yes
show slave status\G;

如果报的是2003 connecting 则为网络不通，可能是密码，权限，和网络

>truncat 表名;删除表中的所有数据
>flush logs;刷新所有日志
>show master status;查看最后一个bin日志
>reset master ;清空bin日志

#mysqlbinlog 查看bin日志
如果不可以查看就是字符集问题，他是MySQL的一个bug，可以通过#mysqlbinlog --no-defaults bin日志
通过正常备份的文件恢复 ocal/mysql/bin/mysql -u root -p123 test </tmp/test.log
通过bin日志进行实时恢复
#/usr/local/mysql/bin/mysqlbinlog -uroot -p123 --no-defaults mysql-bin.000012 | /usr/local/mysql/bin/mysql -uroot -p123 test 把mysql-bin.00012恢复到test表中
--start-position= --stop-position=
--start-date= --stop-date=
>\s 显示数据库状态
############
数据备份
###########
#mysqldump -u root -p123 test -l -F '/tmp/test.sql';
-F 即lush logs;
-l 读锁，防止数据库被别人操作，保证备份的完整性

加读锁备份tables数据
>flush tables with read lock;
>mysqldump 进行备份
>unlock tables;解锁tables表

#############################################################
大数据的优化
##############################################################
分库分表/建立索引都可以达到对表的优化效果
###########
分表
###########
        垂直分表
        水平分表 硬件上分表/逻辑上分表 硬件分表后会增加sql语句，多个SQL语句频繁操作多个表效率不高
                        逻辑分表虽然
                        mysql的分区技术（mysql5.1以后才有的特性)把这个大的表通过分区技术分成多个表，但是在操作这些表的时候不需要像物理分表一样需要更多的SQL语句，只要把这些分区看成同一个表，这样就减少了SQL语句的操作次数
                        range分区
                                建表
                                create table employees(
                                id int not null,
                                fname varchar(30),
                                iname varchar(30),
                                hired date not null default'1970',
                                separated date not null default'999',
                                jib_code int not null,
                                store_id int not null
                                );
                                range分区   支持复合分区，对于日期类型
                                        partition by range(store_id)(
                                        partition p0 values less than(6),
                                        partition p1 values less than(11),
                                        partition p2 values less than(16),
                                        partition p3 values less than(21)
                                        );
                                list分区 支持固定取值的列
                                partition by list(store_id)(
                                partition p0 values in(3,5,6),
                                partition p1 values in(4,7,8),
                                partition p2 values in(9,10,11)
                                );
                                hash分区
                                partition by hash(year(hired))
                                partition 4;
                               
                                key分区
                                partition by key()
                               
                                show plugins; 里面有partition active；才能确定分区技术可以用
                                create table t2(
                                id int null
                                )
                                engine=innodb
                                partition by hash(id)
                                partitions 5;
                               
                                insert into t2 select * from t2;
                                watch -n1 |ls -lh
                               
innodb 表数据结构

        共享表空间，某一个数据库的所有的表数据和索引文件全部放在一个文件中，默认
        这个共享表空间的文件路径在data目录下。默认的文件名为，ibdata1初始化为10m
        独占表空间：每个表都将会生成独立的文件方式来进行存储，每个表都有一个.frm表描述文件，
        还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中
        innodb是共享表空间，必须做成独立表空间才可以加入分区技术
        innodu_file_per_table=1 就会由共享表空间转换为独立表空间，就可以使用分区技术

###################
SQL语句的优化
#####################
###################
表复制
表结构+表数据
create table t2 like t1;复制表结构
insert into t2 select  t1.name from t1;复制表数据
#####################
mysql索引
#################
alter table table_name add index index_name(column_list)
alter table table_name add unique(column_list)
alter table table_name add primary key(colum_list)
alter table table_name drop index index_name
alter table table_name drop primary key

create不可以创建主键索引
drop index index_name on table_name
create index index_name on table_name(column_list)
create unique index index_name on table_name(column_list)
show index from t1;

##################
mysql视图是一个中间表，可以跟着主表进行更新，而复制则不可以实时更新
#####################
create view v_t1 as select * from t1 where id>4 and id<11;
alter view
drop view
show tables;

##############
内置函数
###############

select concat("hello","myword") as myname 连接字符串
select length("linux"); 求长度
select ltrim("   li");        左去空
select rtrim("li    "); 右去空
select repeat("linux,",3);Linux重复3次
slect replace("linux is very good","linux","php");把Linux替换成php
select substing("linux is very good",0,5);从0开始去前面字符串中5个字符
select space(10)生成10个空格
select lcase("linux);转小写
select ucase("linux);转大写
select bin(12); 十进制转2进制
select ceiling(4.7);向上取整
select floor(5.3);向下取整

select curdate 当前日期
select curtime 当前时间
select unix_timestamp();
select week("2012-10-10"); 返回一年中第几周
select now 返回当前日期和时间

#################
mysql预处理语句
#################
prepare stmtl from 'select * from t1 where id>?;预处理语句
set@i=1;
execute stmt1 using @i;
drop prepare stmt1;

#################
mysql 事务处理必须是innodb
#################
set autocommit=0;取消自动提交
savepoint p1;设置保存点
delete from t1 where id=10;
savepoint p2;
rollback to p1;回滚

############################
mysql的触发器，当对t1表进行增删改，t2也同步进行增删改
#############################
添加
\d //
create trigger tg before insert on t1 for each row
begin
insert into t2(naem) values(new.name);new是新的必须加
end
//
show triggers;
show create triggers tg;
删除
d\ //
create trigger tg2 befor delete on t1 for each row
begin
delete from t2 where id=old.id;
end//
保证t1和t2的id号统一
更新
d\ //
create trigger tb3 before update on t1 for each row
begin
update t2 set name=new.name where name=old.name;
end//
查询
select * from t1 union select * from t2
#################
重排auto_increment
##################
truncat 清空表中数据，且把id至1
也可以
delete from t1;
alter table t1 auto_increment=1 ;

#############################
索引的优化
################################
like的搜索，还有正则和sphinx全文索引
使用Like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用
当使用or时前后都得加索引，这样索引才会别应用
当使用and前后都得加索引，这样索引才会别应用
如果类型是字符串，搜索的却是整形，那么也不会用到索引
show status like 'handle_read%';
handler_read_key读取这一行索引的次数
handler_read_rnd_next的值很高则意味着查询效率低，要使用索引

###################################
表优化，分析表
###################################
检查一个或多个表是否有错误
check table tb_name ;

优化表空间(耗费资源)
optimize tb_name ;

###################################
常用SQL的优化
##################################

大批量插入数据
load data into outfile "/tmp/test.txt";导出的文件数据
load data infile "/tmp/text.txt" into table t1;
关掉普通索引，可以加快数据的导入导出,关闭唯一索引可能会导致数据的错误
alter table table_name disable keys
alter table table_name enable keys
关闭唯一索引前提是保证导入的数据不会有冲突值
set unique_checks=0关闭唯一索引
set unique_check=1 开启唯一索引

innodb可以关闭事务提交，set autocommit=0;
对主键进行升序排序，这样可以提供导入数据的效率，前提是导入的数据中有id自增项

insert into t1(name) values(1),(2),(3);可以提供SQL执行效率不需要每次都连接关闭数据库一次



#############################################################
SQL语句的优化
##########################################################

select "linux is very good" regexp ".*";
select "linux is very good" regexp "linux";
select * from stu order by rand() limit 3;rand 是0-1 的随机数

利用group by 的with rollup可以得到更多的合计信息

###################################################################
mysql的主从复制
##################################################################
二进制日志 慢查询日志 事务日志 错误日志 一般查询日志 中继日志

mysql 是一个进程多个线程的工作模式

mysql-bin.xxxxx
        滚动:达到最大上限 flush logs 服务器重启
        置零：purge
       
        二进制日志格式
        statement 基于语句
        row  基于行
        mixed 混合的
       
        mysql-bin.index :二进制日志文件的索引文件
        show master status; 查看二进制的状态
        show binary logs; 查看所有的二进制日志
        show binlog events in "file";查看某个二进制日志里面的数据
       
        event:
        timestamp
        position,offset
        事件本身
       
        二进制进行即时点还原
               
        mysql 隔离级别
                read-uncommitted
                read-committed
                repeatable-read
                serializable
               
               
        复制的作用
                辅助实现备份
                高可用
                异地容灾
                负载均衡
               
读写分离
        mysql-proxy
        amoeba
       
        数据拆分
                cobar

mysql 5.6 gtid 全局事务号
                multi-thread replication 多线程复制
               
配置mysql 步骤
一、master
        1、启用二进制日志
                mysql-bin=master-bin
                log-bin-index=master-bin.index
        2、选择一个唯一 server-id
                server-id=0-2^32-1
        3、创建具有复制权限的用户
        replication slave 复制的从节点
        replication client 具有连接主服务器，从主服务器复制数据的权限
       
二、slave
        1、启用中继日志
        relay-log=relay-log
        relay-log-index =
        2、选择一个唯一的 server-id
        server-id=
        3、连接主服务器，并开始复制数据
                changer master to
                master_host='',master_port='',master_log_file='',master_log_pos='',master_user='',master_password='';
                start slave;
        复制线程：
                master:dump
                slave:io_thread,sql_thread
               
通用二进制包的安装
        mkdir -pv /mydata/data
        useradd  -r mysql
        chown -R mysql:mysql /mydata/data
       
        tar -zxf mysql-5.5  -C /usr/local/
        cd /usr/local
       
        ln -sv mysql-5.5.28-linux2.6-i686 mysql
        cd mysql
        chown -R root:mysql ./*
        scripts/mysql_install_db --user=mysql --datadir=/mydata/data
        cp support-files/my-large.cnf /etc/my.cnf
        cp support-files/mysql.server /etc/rc.d/init.d/mysqld
        chkconfig --add mysqld
        vim /etc/my.cnf
        在[mysqld]
        log-bin=master-bin
        log-bin-index=master-bin.index
        binlog_format=mixed 或者row 不用statement
        server-id=1
        innodb_file_per_table =1 让innodb的表索引和表数据分开
        datadir=/mydata/data
        server mysqld start
        vim /etc/profile.d/mysql.sh
        export PATH=$PATH:/usr/local/mysql/bin
        ./etc/profile.d/mysql.sh
        mysql 就可以进入数据库了
       
       
        show binlog events in "file";查看bin中的创建的事件
       
        在从服务器上设定
        show variables like 'read%';
        把read_only 改为on 关闭slave写，但是root任然可以写
        在配置中加 read-only=on
        show golabal variable like 'read_only';
        当下次再次启动时会读取里面的内容
        master.info
        relay-log.info
       
        show global variables like '%log%';
        sync_binlog =on 在主服务器上设定用于事务安全
       
        show global varibales like '%slave%';
       
        错误日志在/mydat/data中
       
       
       
######################################################
半同步的设置
##################################################       
        cd /usr/local/mysql
        cd lib 里面有半同步模块，是Google开发的
        semisync_master
       
        install plugin rpl_semi_sync_master soname 'semisync_master.so';
        show global variables like '%rpl%';
        set global rpl_semi_sync_master_enabled=1
       
       
        install Plugin rpl_semi_sync_slave soname 'semisync_slave.so';
        show variables like 'rpl%';
        set global rpl_semi_sync_slave_enabled=1
        stop slave io_thread ;
        start slave io_thread;
       
       
        percona: percona-tools 可以用来查看主从复制是否同步 装完之后会有一堆pt开头的命令
                mattkit-tools percona早期的工具名  命令都是以mk开头
               
               
################################################
                双主配置
########################################
               
设置主主
1、在两台服务器上各自建立一个具有复制权限的用户
2、修改配置文件
        主服务器
        [mysqld]
        server-id=10
        log-bin=mysql-bin
        relay-log=relay-mysql
        relay-log-index=relay-mysql.index
        auto-increment-increment=2
        auto-increment-offset=1
       
        从服务器
        [mysqld]
        server-id=20
        log-bin=mysql-bin
        relay-log=relay-mysql
        relay-log-index=relay-mysql.index
        auto-increment-increment=2
        auto-increment-offset=1
       
3、查看对方的show master status;添加change master to
       
        A：查看B的二进制日志文件及位置，并作为自己的复制起点
        B：如上查看A的相关日志文件及位置