http://www.wyzc.com/study/8652/1859/#6490
mysql版本
mysql community edition 社区版 +加入一些未经过测试的代码,未经过压力测试和性能测试,是企业版的小白鼠,有很多bug,不提供支持,没有企业版的组件
商业版
商业组件有
企业级备份
企业级高可用功能
企业级可扩展性能 enterprise scalability
企业级的安全功能 enterprise security
企业级的审计功能 enterprise audit
企业级的监控 enterprise monitor
工作平台 workbench
技术支持
安装
可以从官网上下载安装包(推荐
下载源代码包
狭隘二进制包
从光盘安装
源代码安装可以更具系统的配置调节参数,使mysql性能得到提高
但是大量安装会很麻烦,一般都是安装包安装
www.mysql.com-->downloads--->
看到
MySQL Community Edition
(GPL)
MySQL Cluster CGE
(commercial)
MySQL Enterprise Edition
(commercial)
熟悉mysql
desc table名;
show create table t1;
show create table t1 /G;查看表结构
insert into table tablename SET id=1,name=tube;
insert intotable tablename(id,name) Values (2,kevin),(3,mark)
总结
建议从官网网站上下载mysql
尽量不同用源码编译安装mysql,源代码用于研究和小面积安装
交互式和非交互式
登陆mysql -P -h -u等 -p 注意空格的应用啊
库名和表名注意大小写
--------------------------------------------
--------------------------------------------
yum安装mysql
rpm安装,方便快捷,但是不能自定义参数
在官网上用www.mysql.com--》download---》new1 mysql yumrepository
下载后得到mysql-community-release-el6-5.noarch.rpm
查看这个包的内容
rpm -qpl mysql-community-release-el6-5.noarch.rpm
-qpl列出RPM软件包内的文件信息
显示如下
/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
/etc/yum.repos.d/mysql-community-source.repo
/etc/yum.repos.d/mysql-community.repo
我们安装这个rpm
rpm -ivh mysql-community-release-el6-5.noarch.rpm 后 就就会自动配置好了yum
这时我们就会yum list | grep MySQL
会列出所用的包
yum install MySQL-client MySQL-server 就会安装好客户端和服务器
如果自己在公司构建yum源呢
vi /etc/yum.repos.d/
vi mysql-community.repo
记录了mysql的多种版本,5.5 5.6等
将地址copy 把地址复制到网页,把所有目录同步过来,就可以做本地的yum源了
源码安装,可以定制
同样去网站 download -在select platform时选择 source code
源码安装可以定制安装位置
加优化参数
下载后mysql-5.6.17.tar.gz
tar zxvf mysql..gz -C /usr/src/
5.5以后就有所改变了不用configure makeinstall make了
而是用cmake安装,所以要安装cmake
yum install cmake
cmake . 表示在当前目录开始编译
gmake
make install
或者cmake && gmake && make install
以上为默认安装
也可以自定义
cmake . +可选项
看mysql 官方文档 在2.4.4
infile 辅助mysql可以调用外部的sql 或系统命令,是否开启
install_bindir mysql的命令放在那里
docreadmedir
includes
cmake -D参数 1是打开 0关闭
myisam merge memory csv 等存储引擎默认加载
engine archive blackhole example federated nob partition 等存储引擎需要用命令添加
-DWITH_innodebase_storage_egine=1
-DWITH_ARCHIVE_STOREAGE_ENNGINE=1
-DWITH_partition _STOREAGE_ENNGINE=1 分区存储引擎打开
等等
自作一个cmake.sh
cmake -D***********************
如果在cmake 中指定用户是mysql额话,还需要再系统中添加group和user mysql 然后再cmake
通过srpm安装,可以像源码一样的定制,又可以大量方便的部署
官网下载mysql-5.6.17-1.el6.src.rpm
cp mysql.src.rpm /home/tubeiu/
cd /home/tubeliu/
ll
chown tubeliu. mysql.src.rpm想用yubeliu用户安装所以改权限
没错是“.空格”
yum list | grep rpm-build
构建src.rpm必须安装rpm-build
yum -y install rpm-build
su - tubeliu
rpmbuild ~ 构建rpm创建的目录,存放一些中间文件
rpm -ivh mysql.src.rpm
cd /home/tubeliu/repbuild
ls -R递归查看所有目录
在./SPECS下有mysql.spec文件
mysql.spec官方有大部分配置
主要看buid full release 向cmake的配置可以添加删除
-D****
然后保存退出
最后根据mysql.spec创建rpm包了
rpmbuild -bb mysql.spec等等就自动完成了
报错gperf is needed by mysql。。。,从centos 可以下载
安装中报错
yum install libaio-devel
安装完成后,在RPMS下回有大量的rpm包了
谢谢。
索引
索引并非越多越好--写数据也要写索引,和重新计算索引
数据量不多不需要建索引
列中值变化不多不需建索引
唯一性拘束使用唯一索引
注意Hash索引的了解
- A.Hash 索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询
- B.Hash 索引无法被用来避免数据的排序操作
- C.Hash 索引不能利用部分索引键查询
- D.Hash 索引在任何时候都不能避免表扫描
- E.Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高
(id int primary key, --简历
name varchar(10).
index(name)); --简历普通索引
创建普通索引
查看索引效果
mysql> create table t_1 (id int,name char(10),index(name));
Query OK, 0 rows affected (0.08 sec)
mysql> show create table t_1
-> ;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| t_1 | CREATE TABLE `t_1` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在已存在的表中添加索引
create index index_name on t1(col);
alter table t1 add index index_name(col);
创建唯一索引
组合索引
组合索引依照最左端原则
如(id name age)作为组合索引
查询id或检索id name或者id age 都会使用索引,如果使用name age 是不会使用索引的必须带上最左端的id
mysql> create table t_4 (
-> id int,
-> name varchar(10),
-> age int,
-> index MutiIdx (id,name,age) 组合索引
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> show create table t_4
-> ;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_4 | CREATE TABLE `t_4` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `MutiIdx` (`id`,`name`,`age`) 组合索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
索引使用效果的展示
mysql> insert into t_4 values
-> (1,'AAA',10),
-> (2,'BBB',11),
-> (3,'CCC',12),
-> (4,'DDD',13),
-> (5,'EEE',14);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
-------------------------------------------------------------------------------------------
只用id使用了mutidx索引
EXPLAIN可以查看一个查询的执行计划,
mysql> explainselect name,age from t_4 where id<3\G; 我们只使用id
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_4
type: index 使用了索引
possible_keys: MutiIdx 使用的索引名
key: MutiIdx
key_len: 23
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
------------------------------------------------------------------------
用id和age 也是用了mutidx索引
mysql> explain select name,age from t_4 where id<3 and age<15\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_4
type: index
possible_keys: MutiIdx
key: MutiIdx
key_len: 23
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
-------------------------------------------------------------------------------------
只用age,
mysql> explain select name,age from t_4 where age<15\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_4
type: index
possible_keys: NULL
key: MutiIdx
key_len: 23
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
全文索引
是,在MySQL 5.6中只有InnoDB和MyISAM存储引擎支持全文索引
fulltext index_name
更改索引
alter table t1 add index name(name(20));添加普通索引
alter table t2 drop index name;
create index ind_name on t_4(id);
drop index ind_name on t_4;
测试索引性能
建测试表mysql> create table testx1(id int,num int,pass varchar(50));
Query OK, 0 rows affected (0.06 sec)
插入数据
在系统提示符下执行创建100万行数据
for((i=1;i<=1000000;i++));do 'mysql -u mysql test -e "insert into testx1 values($i,floor($i+rand()*$i,md5($i));"'done
[root@mysql01 ~]# vi 1.sh
#!/bin/sh
for((i=1;i<=1000000;i++));
do mysql -u mysql test -e "insert into testx1 values($i,rand()*$i,md5($i));"解释在do后同‘ ****’ 不好用,提示命令无法找到,floor函数在我的体统提示错误,就不用了
done
首先要刷新一下查询缓存,不要因为查询缓存影响我们的测试
reset query cache;
然后select num,pass from testx1 where id>=5000 and id<=5005;
结果为0.05s
如果我们在id上加索引后
reset query cache;
同样的查询为0.00s
------------------------------------------------
先创建索引再导入数据或先导入数据再创建索引哪个导入的时间更短????
insert into test2 select * fromtest1 test2有索引
时间为0、87s
insert into test3 select * from test1 test3没有索引
时间为0、65s
经过测试空表没有索引,导入更快,但是后期建立索引可能会花费更过时间,但是表再建立索引前已经可以撑场使用了
索引常识
- A.列中包含有NULL的值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。
- B.一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
- C.不要在列上进行运算,这将导致索引失效而进行全表扫描。
- 索引的设计原则,不是越多越好,过多的索引引起多的写入
- fulltext 索引尽在MYISAM上使用
--------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
mysql Replication
mysql 5.6以后推出GRIDs Replication 那么GRIDs是什么?
GRIDs是全局事务I
5.6以后有个自动的failover
replication实现将一台数据从一台同步到多台上去。默认情况下是异步复制,通过配置可以同步整个库或者部分或表
原理
主将数据改变记录到二进制日志中,从数据库再通过这个日志进行数据操作保存到本地的relay log(二进制),然后数据库再根据relaylog日志进行数据写入
主 从
DML----binlog -------------------------->relay log ----DML ---DB
replication的作用
故障切换 fail over
备份服务器 backup server
高可用 High Performance 可以读写分离
replication 简单实验
master ---slave 主从模式
STEP1
slave是通过mysql连接登陆到MaSTER上来读取二进制日志的,因此需要在Master上给Slave配置权限
master>GRANT REPLICATION SLAVE ON *.* TO‘repL’@‘slave_ip’ INDENTIFIED BY ‘password’;
//repl为用户名,这个是创建一个用户名和密码,用户名或密码不需要主或从以前存在
master>FLUSH PRIVILEGES
设置后,可以在从库上测试连接
slave>mysql -h192.168.1.1 -urepl -prepl
如果不成功,检查主iptables -L查看防火墙
STEP2
在MASTER上需要打开二进制日志,并表示server-d(server—id用语唯一标示主机,数字取值范围1···2^32方-1)s
Master#vi /etc/my.cnf
[mysqld]
log-bin //打开二进制日志
binlog-format=row //二进制日志以row行形式记录
syc-binlog=1
serverid=1
MASTER#service mysql restart 配置好my.cnf后需要重启mysql服务使其生效
STEP3
制作一个master完整备份,并且执行prepare
?为什么能通过二进制文件进行同步还做主备份和从恢复(初始同步干什么)
回答:完全通过二进制同步是可以的,但会占用主大量的磁盘io和带宽。所以才进行备份恢复来完成初始同步。
#innobackupex --user=dba --password=xxx /var/lib/bakcup/ dba是提前创建的
#innobackupex --use-memory=500m --apply-log /var/lib/backup/2015......