MySQL数据库优化详解
mysql表复制
复制表结构+复制表数据
mysql>createtablet3liket1; mysql>insertintot3select*fromt1;
mysql索引
ALTERTABLE用来创建普通索引、UNIQUE索引或PRIMARYKEY索引 ALTERTABLEtable_nameADDINDEXindex_name(column_list) ALTERTABLEtable_nameADDUNIQUE(column_list) ALTERTABLEtable_nameADDPRIMARYKEY(column_list) CreateIndex CREATEINDEXindex_nameONtable_name(column_list) CREATEUNIQUEINDEXindex_nameONtable_name(column_list) dropindex DROPINDEXindex_nameONtalbe_name altertabletabledrop ALTERTABLEtable_nameDROPINDEXindex_name ALTERTABLEtable_nameDROPPRIMARYKEY
mysql视图
创建视图
mysql>createviewv_t1asselect*fromt1whereid>4andid<11; QueryOK,0rowsaffected(0.00sec)
view视图的帮助信息
mysql>?view ALTERVIEW CREATEVIEW DROPVIEW
查看视图
mysql>showtables;
删除视图v_t1
mysql>dropviewv_t1;
mysql内置函数
字符串函数
CONCAT(string2[,…])//连接字串 LCASE(string2)//转换成小写 UCASE(string2)//转换成大写 LENGTH(string)//string长度 LTRIM(string2)//去除前端空格 RTRIM(string2)//去除后端空格 REPEAT(string2,count)//重复count次 REPLACE(str,search_str,replace_str)//在str中用replace_str替换search_str SUBSTRING(str,position[,length])//从str的position开始,取length个字符 SPACE(count)//生成count个空格
数学函数
BIN(decimal_number)//十进制转二进制 CEILING(number2)//向上取整 FLOOR(number2)//向下取整 MAX(num1,num2)//取最大值 MIN(num1,num2)//取最小值 SQRT(number2)//开平方 RAND()//返回0-1内的随机值
日期函数
CURDATE()//返回当前日期 CURTIME()//返回当前时间 NOW()//返回当前的日期时间 UNIX_TIMESTAMP(date)//返回当前date的UNIX日间戳 FROM_UNIXTIME()//返回UNIX时间戳的日期值 WEEK(date)//返回日期date为一年中的第几周 YEAR(date)//返回日期date的年份 DATEDIFF(expr,expr2)//返回起始时间expr和结束时间expr2间天数
mysql预处理语句
设置stmt1预处理,传递一个数据作为一个where判断条件
mysql>preparestmt1from'select*fromt1whereid>?';
设置一个变量
mysql>set@i=1;
执行stmt1预处理
mysql>executestmt1using@i;
设置@i为5
mysql>set@i=5;
再次去执行stmt1
mysql>executestmt1using@i;
如何删除预处理stmt1
mysql>droppreparestmt1;
mysql事务处理
--关闭自动提交功能 mysql>setautocommit=0; --从表t1中删除了一条记录 mysql>deletefromt1whereid=11; --此时做一个p1还原点: mysql>savepointp1; --再次从表t1中删除一条记录: mysql>deletefromt1whereid=10; --再次做一个p2还原点: mysql>savepointp2; --此时恢复到p1还原点，当然后面的p2这些还原点自动会失效: mysql>rollbacktop1; --退回到最原始的还原点: mysql>rollback;
mysql存储
创建一个存储p1()
mysql>\d// mysql>createprocedurep1() ->begin ->set@i=0; ->while@i<10do ->select@i; ->set@i=@i+1; ->endwhile; ->end; ->//
执行存储p1()
mysql>\d; mysql>callp1(); --查看procedurep1()的status信息 mysql>showprocedurestatus\G --查看procedurep1()的具体信息: mysql>showcreateprocedurep1\G
mysql触发器
修改delimiter为// mysql>\d// 创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据 mysql>createtriggertg1beforeinsertont1foreachro >begin >insertintot2(id)values(new.id); >end// --准备两个空表t1和t2 mysql>select*fromt1; mysql>select*fromt2; --向t1表中插入多条数据: mysql>insertintot1values(1),(2),(3),(4); 如何制作删除表t1后t2表中的记录也会跟着删除呢 mysql>\d// mysql>createtriggertg2beforedeleteont1foreachrow >begindeletefromt2whereid=old.id; >end// mysql>\d; 如何制作更改表t1后t2表中的记录跟着个性呢 mysql>\d// mysql>createtriggertg3beforeupdateont1foreachrow >beginupdatet2setid=new.idwhereid=old.id; >end// mysql>\d; 查看触发器 mysql>showtriggers;
重排auto_increment值
MYSQL数据库自动增长的ID如何恢复，清空表的时候。不能用 deletefromtablename; 而是要用： truncatetabletablename; 这样auto_increment就恢复成1了 或者清空内容后直接用ALTER命令修改表: altertabletablenameauto_increment=1;
利用GROUPBY的WITHROLLUP
mysql>select*fromdemo; +-------+-------+ |cname|pname| +-------+-------+ |bj|hd| |bj|xc| |bj|hd| |sh|dh| |sh|rg| |sh|dh| +-------+-------+ 9rowsinset(0.00sec)
对demo表按照cname、pname列分组对pname列进行聚合计算如下
mysql>selectcname,pname,count(pname)fromdemogroupby cname,pname; +-------+-------+--------------+ |cname|pname|count(pname)| +-------+-------+--------------+ |bj|hd|3| |bj|xc|2| |sh|dh|3| |sh|rg|1| +-------+-------+--------------+ 4rowsinset(0.00sec)
同样使用withrollup关键字后，统计出更多的信息，如下。注意：withrollup不可以和orderyby同时使用
ysql>selectcname,pname,count(pname)fromdemogroupbycname,pname withrollup; +-------+-------+--------------+ |cname|pname|count(pname)| +-------+-------+--------------+ |bj|hd|3| |bj|xc|2| |bj|NULL|5| |sh|dh|3| |sh|rg|1| |sh|NULL|4| |NULL|NULL|9| +-------+-------+--------------+ 7rowsinset(0.00sec)
使用外键需要注意的问题
创建外键的方式
mysql>createtabletemp(idint,namechar(20),foreignkey(id) referencesoutTable(id)ondeletecascadeonupdatecascade)；
注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键。
优化SQL语句的一般步骤
通过showstatus命令了解各种SQL的执行频率
mysql>show[session|global]status;
其中：session（默认）表示当前连接，global表示自数据库启动至今
mysql>showstatus; mysql>showglobalstatus; mysql>showstatuslike‘Com_%'; mysql>showglobalstatuslike‘Com_%';
参数说明：
Com_XXX表示每个XXX语句执行的次数如：
Com_select执行select操作的次数，一次查询只累计加1
Com_update执行update操作的次数
Com_insert执行insert操作的次数，对批量插入只算一次。
Com_delete执行delete操作的次数
只针对于InnoDB存储引擎的：
InnoDB_rows_read执行select操作的次数
InnoDB_rows_updated执行update操作的次数
InnoDB_rows_inserted执行insert操作的次数
InnoDB_rows_deleted执行delete操作的次数
其他：
connections连接mysql的数量
Uptime服务器已经工作的秒数
Slow_queries:慢查询的次数
定位执行效率较低的SQL语句
explainselect*fromtablewhereid=1000; descselect*fromtablewhereid=1000;
通过EXPLAIN分析较低效SQL的执行计划
mysql>explainselectcount(*)fromstuwherenamelike"a%"\G ***************************1.row*************************** id:1 select_type:SIMPLE table:stu type:range possible_keys:name,ind_stu_name key:name key_len:50 ref:NULL rows:8 Extra:Usingwhere;Usingindex 1rowinset(0.00sec)
每一列的简单解释
id:1
select_type:SIMPLE表示select的类型，常见的取值有SIMPLE（）简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SESECT）等
table:stu 输出结果集的表
type:range表示表的连接类型，性能有好到差：system（表仅一行）、const（只一行匹配）、eq_ref（对于前面的每一行使用主键和唯一）、ref（同eq_ref，但没有使用主键和唯一）、ref_or_null（同前面对null查询）、index_merge（索引合并优化）、unique_subquery（主键子查询）、index_subquery（非主键子查询）、range（表单中的范围查询）、index（都通过查询索引来得到数据）、all（通过全表扫描得到的数据）
possible_keys:name,ind_stu_name表查询时可能使用的索引。
key:name 表示实际使用的索引。
key_len:50索引字段的长度
ref:NULL
rows:8扫描行的数量
Extra:Usingwhere;Usingindex执行情况的说明和描述
索引问题
MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。
mysql>createindexind_company2_nameoncompany2(name(4));
--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引
(1)对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。
mysql>createindexind_sales2_com_mononsales2(company_id,moneys);
然后按company_id进行查询，发现使用到了复合索引
mysql>explainselect*fromsales2wherecompany_id=2006\G
使用下面的查询就没有使用到复合索引。
mysql>explainselect*fromsales2wheremoneys=1\G
(2)使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，如下：
mysql>explainselect*fromcompany2wherenamelike"%3"\G ***************************1.row*************************** id:1 select_type:SIMPLE table:company2 type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:1000 Extra:Usingwhere 1rowinset(0.00sec)
如下这个使用到了索引，而下面例子能够使用索引，区别就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子则没有
mysql>explainselect*fromcompany2wherenamelike"3%"\G ***************************1.row*************************** id:1 select_type:SIMPLE table:company2 type:range possible_keys:ind_company2_name key:ind_company2_name key_len:11 ref:NULL rows:103 Extra:Usingwhere 1rowinset(0.00sec)
(3)如果对大的文本进行搜索，使用全文索引而不使用like“%...%”.
(4)如果列名是索引，使用column_nameisnull将使用索引。如下
mysql>explainselect*fromcompany2wherenameisnull\G ***************************1.row*************************** id:1 select_type:SIMPLE table:company2 type:ref possible_keys:ind_company2_name key:ind_company2_name key_len:11 ref:const rows:1 Extra:Usingwhere 1rowinset(0.00sec)
存在索引但不使用索引
(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好
mysql>select*fromtable_namewherekey_part1>1andkey_part<90;
(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。
(3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
mysql>showindexfromsales\G ***************************1.row*************************** …… key_name:ind_sales_year seq_in_index：1 Column_name:year ……
从上面可以发现只有year列上面有索引。来看如下的执行计划。
mysql>explainselect*fromsaleswhereyear=2001orcountry=‘China'\G ***************************1.row*************************** id:1 select_type:SIMPLE table:sales type:ALL possible_keys:ind_sales_year key:NULL key_len:NULL ref:NULL rows:12 Extra:Usingwhere 1rowinset(0.00sec)
(4)如果不是索引列的第一部分，如下例子:可见虽然在money上面建有复合索引，但是由于money不是索引的第一列，那么在查询中这个索引也不会被MySQL采用。
mysql>explainselect*fromsales2wheremoneys=1\G ***************************1.row*************************** id:1 select_type:SIMPLE table:sales2 type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:1000 Extra:Usingwhere 1rowinset(0.00sec)
(5)如果like是以%开始，可见虽然在name上面建有索引，但是由于where条件中like的值的“%”在第一位了，那么MySQL也会采用这个索引。
(6)如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，那么虽然在name列上有索引，但是也没有用到。
mysql>explainselect*fromcompany2wherenamename=294\G ***************************1.row*************************** id:1 select_type:SIMPLE table:company2 type:ALL possible_keys:ind_company2_name key:NULL key_len:NULL ref:NULL rows:1000 Extra:Usingwhere 1rowinset(0.00sec)
而下面的sql语句就可以正确使用索引。
mysql>explainselect*fromcompany2wherenamename=‘294'\G ***************************1.row*************************** id:1 select_type:SIMPLE table:company2 type:ref possible_keys:ind_company2_name key:ind_company2_name key_len:23 ref:const rows:1 Extra:Usingwhere 1rowinset(0.00sec)
查看索引使用情况
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数。
Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。
mysql>showstatuslike'Handler_read%'; +-----------------------+-------+ |Variable_name|Value| +-----------------------+-------+ |Handler_read_first|0| |Handler_read_key|5| |Handler_read_next|0| |Handler_read_prev|0| |Handler_read_rnd|0| |Handler_read_rnd_next|2055| +-----------------------+-------+ 6rowsinset(0.00sec)
两个简单实用的优化方法
分析表的语法如下:(检查一个或多个表是否有错误)
mysql>CHECKTABLEtbl_name[,tbl_name]…[option]…option=
{QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
mysql>checktablesales;
+--------------+-------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+--------------+-------+----------+----------+
|sakila.sales|check|status|OK|
+--------------+-------+----------+----------+
1rowinset(0.01sec)
优化表的语法格式：
OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]
如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对MyISAM、BDB和InnoDB表起作用。
mysql>optimizetablesales; +--------------+----------+----------+----------+ |Table|Op|Msg_type|Msg_text| +--------------+----------+----------+----------+ |sakila.sales|optimize|status|OK| +--------------+----------+----------+----------+ 1rowinset(0.05sec)
常用SQL的优化
大批量插入数据
当用load命令导入数据的时候，适当设置可以提高导入的速度。
对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。
ALTERTABLEtbl_nameDISABLEKEYS
loadingthedata
ALTERTABLEtbl_nameENABLEKEYS
DISABLEKEYS和ENABLEKEYS用来打开或关闭MyISAM表非唯一索引的更新，可以提高速度，注意：对InnoDB表无效。
--没有使用打开或关闭MyISAM表非唯一索引：
mysql>loaddatainfile‘/home/mysql/film_test.txt'intotablefilm_test2; QueryOK,529056rowsaffected(1min55.12sec) Records:529056Deleted:0Skipped:0Warnings:0
--使用打开或关闭MyISAM表非唯一索引：
mysql>altertablefilm_test2disablekeys; QueryOK,0rowsaffected(0.0sec) mysql>loaddatainfile‘/home/mysql/film_test.txt'intotablefilm_test2; QueryOK,529056rowsaffected(6.34sec) Records:529056Deleted:0Skipped:0Warnings:0 mysql>altertablefilm_test2enablekeys; QueryOK,0rowsaffected(12.25sec)
--以上对MyISAM表的数据导入，但对于InnoDB表并不能提高导入数据的效率
（1）针对于InnoDB类型表数据导入的优化
因为InnoDB表的按照主键顺序保存的，所以将导入的数据主键的顺序排列，可以有效地提高导入数据的效率。
--使用test3.txt文本是按表film_test4主键存储顺序保存的
mysql>loaddatainfile‘/home/mysql/film_test3.txt'intotablefilm_test4; QueryOK,1587168rowsaffected(22.92sec)
Records:1587168Deleted:0Skipped:0Warnings:0
--使用test3.txt没有任何顺序的文本（效率慢了1.12倍）
mysql>loaddatainfile‘/home/mysql/film_test4.txt'intotablefilm_test4; QueryOK,1587168rowsaffected(31.16sec) Records:1587168Deleted:0Skipped:0Warnings:0
（2）关闭唯一性效验可以提高导入效率
在导入数据前先执行setunique_checks=0，关闭唯一性效验，在导入结束后执行setunique_checks=1，恢复唯一性效验，可以提高导入效率。
--当unique_checks=1时
mysql>loaddatainfile‘/home/mysql/film_test3.txt'intotablefilm_test4; QueryOK,1587168rowsaffected(22.92sec) Records:1587168Deleted:0Skipped:0Warnings:0
--当unique_checks=0时
mysql>loaddatainfile‘/home/mysql/film_test3.txt'intotablefilm_test4; QueryOK,1587168rowsaffected(19.92sec) Records:1587168Deleted:0Skipped:0Warnings:0
（3）关闭自动提交可以提高导入效率
在导入数据前先执行setautocommit=0，关闭自动提交事务，在导入结束后执行setautocommit=1，恢复自动提交，可以提高导入效率。
--当autocommit=1时
mysql>loaddatainfile‘/home/mysql/film_test3.txt'intotablefilm_test4; QueryOK,1587168rowsaffected(22.92sec) Records:1587168Deleted:0Skipped:0Warnings:0
--当autocommit=0时
mysql>loaddatainfile‘/home/mysql/film_test3.txt'intotablefilm_test4; QueryOK,1587168rowsaffected(20.87sec) Records:1587168Deleted:0Skipped:0Warnings:0
优化insert语句
尽量使用多个值表的insert语句，这样可以大大缩短客户与数据库的连接、关闭等损耗。
可以使用insertdelayed（马上执行）语句得到更高的效率。
将索引文件和数据文件分别存放不同的磁盘上。
可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是只对MyISAM表使用当从一个文件中装载一个表时，使用LOADDATAINFILE。这个通常比使用很多insert语句要快20倍。
优化groupby语句
如果查询包含groupby但用户想要避免排序结果的损耗，则可以使用使用orderbynull来禁止排序：
如下没有使用orderbynull来禁止排序
mysql>explainselectid,sum(moneys)fromsales2groupbyid\G ***************************1.row*************************** id:1 select_type:SIMPLE table:sales2 type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:1000 Extra:Usingtemporary;Usingfilesort 1rowinset(0.00sec)
如下使用orderbynull的效果：
mysql>explainselectid,sum(moneys)fromsales2groupbyidorderbynull\G ***************************1.row*************************** id:1 select_type:SIMPLE table:sales2 type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:1000 Extra:Usingtemporary 1rowinset(0.00sec)
优化嵌套查询
下面是采用嵌套查询的效果（可以使用更有效的链接查询（Join）替代）。
mysql>explainselect*fromsales2wherecompany_idnotin(selectid fromcompany2)\G ***************************1.row*************************** id:1 select_type:SIMPLE table:sales2 type:ALL possible_keys:NULL key:NULL key_len:NULL ref:NULL rows:1000 Extra:Usingwhere 1rowinset(0.00sec) ***************************2.row*************************** id:2 select_type:SIMPLE table:company2 type:index_subquery possible_keys:ind_company2_id key:ind_company2_id key_len:5 ref:func rows:2 Extra:Usingindex 1rowinset(0.00sec)
下面是使用更有效的链接查询（Join）
mysql>explainselect*fromsales2leftjoincompany2on sales2.company_id=company2.idwheresales2.company_idisnull\G ***************************1.row*************************** id:1 select_type:SIMPLE table:sales2 type:ALL possible_keys:ind_sales2_companyid_moneys key:ind_sales2_companyid_moneys key_len:5 ref:count rows:1 Extra:Usingwhere 1rowinset(0.00sec) ***************************2.row*************************** id:2 select_type:SIMPLE table:company2 type:index_subquery possible_keys:ind_company2_id key:ind_company2_id key_len:5 ref:func rows:1 Extra: 1rowinset(0.00sec)
从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）子所以更有效率一些，是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
数据库优化
优化表的类型
在MySQL中，可以使用函数PROCEDUREANALYSE（）对当前应用的表进行分析，改函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。
mysql>select*fromduck_custprocedureanalyse()\G ***************************1.row*************************** Field_name:sakila.duch_cust.cust_num Min_value:1 Max_value:6 Min_length:1 Max_length:1 Empties_or_zeros:0 Nulls:0 Avg_value_or_avg_length:3.5000 Std:1.7078 Optimal_fieldtype:ENUM(‘1',‘2',‘3',‘4')NOTNULL ***************************2.row*************************** ……
大存储量解决
1.分库分表
2.分区
主要目的:
1.减少表的记录数
2.减小对操作系统的负担压力
中间表
中间表的产生:
1.view视图
2.重新生成一个新表
Mysql服务器优化
myisam读锁定
1.
locktablet1read
2.开启另一个mysql连接终端,接着去尝试:
select*fromt1
3.再insert、update和deletet1这张表，你会发现所有的数据都停留在终端上没有真正的去操作
4.读锁定对我们在做备份大量数据时非常有用.
mysqldump-uroot-p123test>test.sql
myisam写锁定
1.
locktablet1write
2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作，都会停留在终端上，只有等第一个终端操作完毕,第二个终端才能真正执行.
3.可见表的写锁定比读锁定更严格
4.一般情况下我们很少去显式的去对表进行read和write锁定的,myisam会自动进行锁定的.
Mysql服务器优化
二进制日志
1.log-bin=mysql-bin
查看bin-log日志:
mysql>showbinarylogs;
查看最后一个bin-log日志:
mysql>showmasterstatus;
慢查询日志
开户和设置慢查询时间:
vi/etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查询次数:
mysql>showglobalstatuslike"%quer%"
socket问题
mysqlsocket无法登录
1.有时登录mysql时提示不能用socket登录，此时可以换成tcp方式去登录，但是可以测试时可以这样用，但是必须要在php去用之前把这个事情解决了.
[root@localhostmysql]#mysql-uroot-pwei--protocoltcp-hlocalhost WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis34 Serverversion:5.0.77-logSourcedistribution Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer. mysql>
这样就可以登录,这样就不用mysql.sock来登录，而mysql.sock是启动mysqld服务时产生的
root密码丢失
root密码丢失破解
1.servicemysqldstop
2.mysqld_safe--skip-grant-tables--user=mysql&
//跳过授权表mysql.user和mysql.db这些表
3.mysql-uroot
4.setpassword=password("wei");
//用这一条语句结果报错,就是因为加了--skip-grant-tables
4.mysql>updateusersetpassword=password("wei")whereuser='root'
andhost='localhost';
5.mysql>setpasswordforroot@localhost=password("wei");
6.mysql>setpassword=password("wei");
//和第五步一样,都可能成功修改密码