Mysql相关知识

a2005147

　　1. Mysql常规使用
　　安装
　　server: sudo apt-get install mysql-server
　　配置
　　default是只允许本地访问的，如果要其它机器访问，需要修改/etc/mysql/my.cnf配置文件。
　　default根用户是没有密码的，所以用根用户进入，$mysql -u root，一般只有在设置时才要进入根用户模式。
　　
　　基本操作
　　为root设置密码->为root建立所需要的数据库db->并为该db创建用户->设置远程访问或控制该db&&/etc/mysql/my.cnf(bind-address=127.0.0.1--bind-address=本机ip)
　　
　　对数据库的操作
　　显示数据库列表 show databases;
　　使用某个数据库 use db_name;
　　显示库中的数据表 show tables;
　　显示数据表的结构 describe table_name;
　　
　　建库 create database db_name;
　　使用该数据库 use db_name;
　　建表 create table table_name (字段设定列表);
　　删库，删表 drop database db_name, drop table table_name
　　
　　sql文的使用
　　
　　应用
　　如：
　　一个建库和建表以及插入数据的实例
　　school.sql
　　

drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ’深圳’,
year date
); //建表结束
//以下为插入字段
insert into teacher values(’’,’glchengang’,’深圳一中’,’1976-10-10’);
insert into teacher values(’’,’jack’,’深圳一中’,’1975-12-23’);
　　
　　mysql -uroot -ppassword < school.sql
　　
　　将文本数据转到数据库中
　　文本数据应符合的格式：字段数据之间用tab键隔开，null值用\\n来代替
　　data.txt
　　

3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23
　　
　　load data local infile data.txt into table table_name
　　
　　备份数据库
　　导出表
　　mysqldump --opt school > school.sql
　　school.sql是一个文本文件，文件名任取。
　　导出库
　　mysqldump --databases db1 > db1.sql 
　　mysqldump --all-databases > all-databases.sql
　　
　　2. Mysql优化技巧
　　对数据库的快速访问和响应.
　　
　　1. 优化Mysql查询缓存
　　在Mysql服务器上进行查询，可以启用高速查询缓存。让数据库引擎在后台处理是提高性能的最有效方法之一。
　　当同一个查询被执行多次时，如果结果是从缓存中提取，那是相当快的。
　　Query Cache
　　在使用中，查询缓存会存储一个select查询的文本与被传送到客户端的相应结果。如果之后接受到一个同样的查询，服务器将从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。
　　
　　Note:查询缓存绝不返回过期数据。当数据被修改后，在查询缓存中的任何相关词条均被清除。
　　
　　Note: SELECT * FROM tbl_name 和 select * from tbl_name对于query cache是认为不同的。
　　
　　Note:如果查询结果被从查询缓存中返回，那么状态变量Com_select将不会被增加，但是Qcahce_hits却会增加。
　　
　　设置：/etc/mysql/my.cnf中
　　query_cache_limit 不缓存大于这个值的结果
　　query_cache_min_res_unit 查询缓存以最小的尺寸分配块。当一个查询执行完成，最后的结果块被修整到实际数据的大小。
　　query_cache_size 为了存储老的查询结果而分配的内存数量（以字节指定）。如果设置它为0，查询缓存将被禁止（default 0）
　　query_cache_type
　　0(off，不缓存或重新得到结果)
　　1(on，缓存所有的结果，除了select sql_no_cache...)
　　2(demand，仅缓存select sql_cache...)
　　
　　query_cache_type 使用Qcache的方式
　　select @@query_cache_type;
　　select sql_no_cache id, name from table_name;
　　set session query_cache_type=off;
　　
　　系统变量have_query_cache设置Qcache是否可用
　　show variables like 'have_query_cache';
　　
　　系统变量query_cache_size
　　select @@global.query_cache_size;
　　set @@global.query_cache_size=10000;
　　
　　query_cache_limit控制Qcache结果的最大值
　　select @@global.query_cache_limit;
　　set @@global.query_cache_limit=1000;
　　
　　查询缓存的状态和维护
　　FLUSH QUERY CACHE 命令可以整理查询缓存，以便更好的利用它的内存
　　RESET QUERY CACHE 从查询缓存中移除所有的查询结果
　　
　　SHOW VARIABLES LIKE 'have_query_cache' 检查查询缓存在你的Mysql中是否被引进
　　SHOW STATUS 可以监视查询缓存的性能
　　

Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目
Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached.
　　
　　Note:Qcache_total_blocks和Qcache_free_blocks可能显示查询缓存的碎片。在FLUSH QUERY CACHE之后，只有剩下一个单独的（大块）空闲块
　　
　　
　　2. 用EXPLAIN使你的select查询更加清晰
　　3. 利用LIMIT 1取得唯一行
　　增加一个LIMIT 1会令查询更加有效。这样数据库引擎发现只有1后将停止扫描，而不是去扫描整个表或索引。
　　select 1 from table_name where state="xxx" limit 1;
　　4. 索引中的检索字段
　　alter table 'table_name' add index ('c_name');
　　
　　小技巧
　　磁盘碎片整理
　　optimize table table_name 
　　或
　　myisamchk -r table_name
　　
　　explain来查看瓶颈
　　
　　mysql gui
　　sudo apt-get install mysql-gui-tool-common