mysql优化

一、游标

定义：存储在MySQL服务器上的数据库查询，是一种被select语句检索出来的结果集。

作用：方便在检索出来的结果集中前进或后退一行或多行。

游标主要用于交互式应用；MySQL中的游标只能用于存储过程（和函数）。

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于begin和end语句之间的一组语句）：

delete；

insert；

update；

其他MySQL语句不支持触发器。。。

MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那没当PHP脚本执行完成后，该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

1、mysql常用命令
create database name; 创建数据库
use databasename; 选择数据库
drop database name 直接删除数据库，不提醒
show tables; 显示表
SELECT * from information_schema.VIEWS 显示视图
describe tablename; 表的详细描述
select 中加上distinct去除重复字段
mysqladmin drop databasename 删除数据库前，有提示。
显示当前mysql版本和当前日期
select version(),current_date;

2、修改mysql中root的密码：
shell>mysql -u root -p
mysql> update user set password=password(”xueok654123″) where user=’root’;
mysql> flush privileges //刷新数据库
mysql>use dbname； 打开数据库：
mysql>show databases; 显示所有数据库
mysql>show tables; 显示数据库mysql中所有的表：先use mysql；然后
mysql>describe user; 显示表mysql数据库中user表的列信息）；

3、grant
创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个
mysql> grant all privileges on *.* to user@localhost identified by ’something’ with
增加新用户
格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;
删除授权：
mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”;
mysql> flush privileges;
创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb
mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’
重命名表:
mysql > alter table t1 rename t2;

4、mysqldump
备份数据库
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
恢复数据库
shell> mysqladmin -h myhost -u root -p create dbname
shell> mysqldump -h host -u root -p dbname < dbname_backup.sql
如果只想卸出建表指令，则命令如下：
shell> mysqladmin -u root -p -d databasename > a.sql
如果只想卸出插入数据的sql命令，而不需要建表命令，则命令如下：
shell> mysqladmin -u root -p -t databasename > a.sql
那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢？
　　 mysqldump -T./ phptest driver
其中，只有指定了-T参数才可以卸出纯文本文件，表示卸出数据的目录，./表示当前目录，即与mysqldump同一目录。如果不指定driver 表，则将卸出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。

5、可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。
例如，如果在文件my_file.sql 中存放有查
询，可如下执行这些查询：
例如，如果您想将建表语句提前写在sql.txt中:
mysql > mysql -h myhost -u root -p database > sql.txt

优化：
a、表的设计合理化 符合三范式
b、适当增加索引 普通索引 主键索引 唯一索引 全文索引
c、分表技术 水平分割 垂直分割
d、存储过程 或者触发器 模块化编程 可以提高速度
e、读写分离 update delete add
f、对mysql配置优化 配置最大并发数 调整缓存大小
g、mysql服务器硬件升级
h、定时清除不需要数据 定时进行碎片清理（myisam）

范式：
1、表的列不可在分解
2、表中的记录是唯一的 就满足二范式 通常设计主键
3、表中不要有冗余数据 不要把一个表中的数据加到一个表中 例如 一个学生对应一个班级 班级可以单独设计一个表 不要把班级数据强行插入到学生表后面

show status
常用的
show status like 'uptime'查询当前MySQL本次启动后的运行统计时间
show staus like 'com_select' 或者'com_insert'类推
show staus like 'connections'
显示慢查询次数
show staus like 'slow_queries' 大于10秒 mqsql认为是慢查询
修改mysql的慢查询
show variables like 'long_query_time'//可以显示当前慢查询的时间
set long_query_time =1 //可以修改慢查询时间

1、默认情况下，mysql认为10秒是一个慢查询
2、数据库可以有各种数据对象：表 存储过程 视图 函数 触发器
3、调用存储过程 call 存储过程名
4、删除自定义函数 drop function 函数名()
5、为了存储过程能够正常执行，我们需要把命令执行结束符修改
得利meter $$ 此时 分号不能结束了 $$为一个新的命令结束符

优化问题：
通过explain语句可以分析 mysql如何执行你的sql语句

建立适当的索引：
四种索引：主键索引
唯一索引
全文索引
普通索引

1、添加
a、主键索引添加
当一张表，把某个列设为主键的时候，则该列就是主键索引
可以在创建表之后再创建索引：alter tabale 表名 add primary key（表名）
b、普通索引
一般来说 普通索引的创建 是先创建表 然后再创建普通索引
create index 索引名 on 表名(列1，列2)
c、创建全文索引
全文索引 主要是针对文件，文本的检索 比如文章，全文索引针对MyISAM有效
不能用like
正确用法：select * from articles where match(title,body) against('database') 列1，列2
说明：1、fulltext只针对myisam生效
2、针对英文生效 中文(sphinx)技术处理中文
3、使用方法是 match(字段名)against('关键字')
4、全文索引一个叫停止词，因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用的词和字符不会创建，称为停止词
d、唯一索引
当表的某列被指定为unique约束时，这列就是唯一索引
create table ddd(id int primary key auto_increment,name varchar(32)unique);
此时name就是唯一索引
unique字段可以为null，并可以有多个，但是如果是具体内容，则不能重复
主键字段，不能为null，也不能重复 不能复合索引

在创建表后，再去创建唯一索引，
create unique index 索引名 on 表名(列名)

2、查询索引
desc表名 【该方法的缺点是 不能够显示索引名】
show index from 表名
show keys from 表名

3、删除
drop index 索引名 on 表名
alter table 表名 drop index 索引名

如果删除的是主键索引
alter table 表名 drop primary key
4、修改
先删除 再重新创建

为什么创建索引之后 速度会变快？
没加索引之前 会查询所有的记录 加了之后会根据算法去计算 一半一半的分开查
二叉树算法 log2N次方 10次 就会查询2的10次方 会记录文件的物理位置

索引的代价：占用磁盘空间 对dml的语句影响效率，需维护索引文件

在哪些列上适合添加索引？
被频繁作为查询条件字段应该作为索引
唯一性太差的字段不适合单独创建索引 即便频繁作为查询条件 性别
更新频繁的字段不适合创建索引
不会出现在where子句中字段不该创建索引

满足以下条件的字段，才应该创建索引
1、肯定在where条件经常使用
2、该字段的内容不是唯一的几个值（性别字段）
3、字段内容不是经常频繁变化

索引的使用：
1、alter table dept add index mine_index(dname,loc)
说明：对于创建的多列索引，只要查询使用了最左边的列，索引一般就会被使用
2、explain：可以帮助我们在不真正执行某个sql时，就执行mysql怎样执行，这样利用我们去分析sql指令
各个字段意义：id:查询序列号
select_type：查询类型
table：查询表名
type：扫描方式：all全表扫描
possible_key：可能使用到的索引
key：实际使用的索引
rows:该sql语句扫描了多少行，可能得到多少记录
extra：using filesort 排序方式
当query中包含order By操作，而且无法利用索引完成排序
using temprary 默写操作必须使用临时表，常见 group By order by
3、对于使用like的查询，查询如果是'%aaa'的不使用索引或者_，'aaa%'会使用到索引，如果一定使用%只能不使用索引
4、如果条件中有or，即使其中有条件带有索引也不会使用，使用到的所有字段都必须建索引，尽量避免使用or
5、如果列类型是字符串，一定要用单引号引起来 不然索引失效
6、mysql发现使用全表扫描比使用索引快，就不会使用索引

show status like 'Handler_read%'索引使用情况

sql语句的小技巧：
1、优化 group by 语句：
在使用groupby查询时，默认分组时，还会排序，但用户想要避免排序结果的消耗，则可以使用order by null;
2、有些情况下，可以使用连接替代子查询，因为使用join，mysql不需要再内存中创建临时表

如何选择mysql的存储引擎
myisam：如果表对事务要求不高，并且以查询为主，考虑使用这个，例如：bbs的发帖表，回复表
一定要进行定期碎片清理，不然即便delete语句之后，数据还存在 清理语句optimize table 表名

innodb：对事务要求更高，保存的数据都是重要数据，例如订单表 账号表

memory：比如我们数据变化频繁，不需要入库，同事频繁的查询和修改

myisam和innodb区别：
1、事务安全
2、查询和添加速度
3、支持全文索引
4、锁机制
5、myisam不支持外键 innodb支持
手动备份数据库：
备份数据库：
mysqldump -u root -proot 数据库 > 文件路径 d:\temp.bak
备份表：
mysqldump -u root -proot 数据库[表名1 表名2] > 文件路径

恢复备份文件：source d:\temp.dept.bak

分表：
把某个表的某些字段 这些字段 并不是经常关心的 但是数据量很大 可以把这些字段单独放在另外一张表中 从而提高效率

表的字段是包小不保大，尽量节省空间
tinyint<1>表示0填充 前面01 02 03

1、关于网站的图片和视频存放
数据表中，一般只是存放图片或者视频的路径，资源是放在文件系统(往往配合独立的服务器)

2、优化我们的mysql配置
修改服务器端口 修改之后 mysql_connect也要修改
最大连接数
主要是内存，主要是是innodb，对于muisam， 调整key_buffer_size
默认存储引擎

读写分离：如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库压力分散

3、mysql增量备份：
mysql会以二进制的形式，自动把用户对mysql数据库的操作，记录到文件，当用户希望回复的时候可以使用备份文件，进行恢复
增量备份会记录dml语句 创建表的语句 不会记录查询语句
记录的（操作语句本身 操作时间 position）

步骤：
1、配置my.ini文件 启用二进制备份
log-bin= 把备份文件放在指定目录下
2、启动mysql得到文件
索引文件：里面是有哪些增量备份文件
可以使用mysqlbinlog程序来查看备份文件额内容
cmd进入到控制台
恢复：mysqlbinlog --stoptime "2010-08-20 18:07:53" start-time "2011-09-20 18:07:53" d:/binlog/shunuing.0001 | mysql -uroot -p
还有position