Mysql应用MySQL数据库优化详解

发布时间：2020-12-12 02:50:35 所属栏目：MySql教程来源：网络整理

导读：《Mysql应用MySQL数据库优化详解》要点：本文介绍了Mysql应用MySQL数据库优化详解，希望对您有用。如果有疑问，可以联系我们。 MYSQL实例 mysql表复制? ????????????????????????????????????????????????????????????????????????????? MYSQL实例复制表结

《Mysql应用MySQL数据库优化详解》要点：
本文介绍了Mysql应用MySQL数据库优化详解，希望对您有用。如果有疑问，可以联系我们。

MYSQL实例mysql表复制??????????????????????????????????????????????????????????????????????????????

MYSQL实例复制表结构+复制表数据

MYSQL实例
mysql> create table t3 like t1;
mysql> insert into t3 select * from t1;

MYSQL实例mysql索引?????????????????????????????????????????????????????????????????????????????????

MYSQL实例
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADDPRIMARY KEY (column_list)
Create Index
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
drop index
DROP INDEX index_name ON talbe_name
alter table table drop
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

MYSQL实例mysql视图????????????????????????????????????????????????????????????????????????????????

MYSQL实例创建视图

MYSQL实例
mysql> create view v_t1 as select * from t1 where id>4 and id<11;
Query OK,0 rows affected (0.00 sec)

MYSQL实例view视图的帮助信息

MYSQL实例
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW

MYSQL实例查看视图

MYSQL实例
mysql> show tables;

MYSQL实例删除视图v_t1

MYSQL实例
mysql> drop view v_t1;

MYSQL实例mysql内置函数???????????????????????????????????????????????????????????????????????????

MYSQL实例字符串函数

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个空格

MYSQL实例数学函数

MYSQL实例
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
FLOOR (number2 ) //向下取整
MAX(num1,num2) //取最大值
MIN(num1,num2) //取最小值
SQRT(number2) //开平方
RAND() //返回0-1内的随机值

MYSQL实例日期函数

MYSQL实例
CURDATE() //返回当前日期
CURTIME() //返回当前时间
NOW() //返回当前的日期时间
UNIX_TIMESTAMP(date) //返回当前date的UNIX日间戳
FROM_UNIXTIME() //返回UNIX时间戳的日期值
WEEK(date) //返回日期date为一年中的第几周
YEAR(date) //返回日期date的年份
DATEDIFF(expr,expr2) //返回起始时间expr和结束时间expr2间天数

MYSQL实例mysql预处理语句????????????????????????????????????????????????????????????????????????

MYSQL实例设置stmt1预处理,传递一个数据作为一个where判断条件

MYSQL实例
mysql> prepare stmt1 from 'select * from t1 where id>?';

MYSQL实例设置一个变量
mysql> set @i=1;
执行stmt1预处理
mysql> execute stmt1 using @i;
设置@i为5
mysql> set @i=5;
再次去执行stmt1
mysql> execute stmt1 using @i;
如何删除预处理stmt1
mysql> drop prepare stmt1;

MYSQL实例mysql事务处理?????????????????????????????????????????????????????????????????????????

MYSQL实例
--关闭自动提交功能
mysql> set autocommit=0;
--从表t1中删除了一条记录
mysql> delete from t1 where id=11;
--此时做一个p1还原点:
mysql> savepoint p1;
--再次从表t1中删除一条记录:
mysql> delete from t1 where id=10;
--再次做一个p2还原点:
mysql> savepoint p2;
--此时恢复到p1还原点,当然后面的p2这些还原点自动会失效: 
mysql> rollback to p1;
--退回到最原始的还原点:
mysql> rollback ;

MYSQL实例mysql存储????????????????????????????????????????????????????????????????????????????????

MYSQL实例创建一个存储p1()

MYSQL实例
mysql> d //
mysql> create procedure p1()
-> begin
-> set @i=0;
-> while @i<10 do
-> select @i;
-> set @i=@i+1;
-> end while;
-> end;
-> //

MYSQL实例执行存储p1()

MYSQL实例
mysql> d ;
mysql> call p1();
--查看procedure p1()的status信息
mysql> show procedure statusG
--查看procedure p1()的具体信息:
mysql> show create procedure p1G

MYSQL实例mysql触发器??????????????????????????????????????????????????????????????????????????????

MYSQL实例
修改delimiter为//
mysql> d //
创建一个名字为tg1的触发器,当向t1表中插入数据时,就向t2表中插入一条数据
 
mysql> create trigger tg1 before insert on t1 for each ro
>begin
>insert into t2(id) values(new.id); 
>end//
--准备两个空表t1和t2
mysql> select * from t1;
mysql> select * from t2;
--向t1表中插入多条数据:
mysql> insert into t1 values(1),(2),(3),(4);
如何制作删除表t1后t2表中的记录也会跟着删除呢
mysql>d //
mysql> create trigger tg2 beforedelete on t1 for each row 
>begin delete from t2 where id=old.id;
>end//
mysql>d ;
如何制作更改表t1后t2表中的记录跟着个性呢
mysql>d //
mysql> create trigger tg3 beforeupdate on t1 for each row 
>begin update t2 set id=new.id where id=old.id; 
>end//
mysql>d ;
查看触发器
mysql> show triggers;

MYSQL实例重排auto_increment值?????????????????????????????????????????????????????????????

MYSQL实例
MYSQL数据库自动增长的ID如何恢复,清空表的时候.不能用
delete from tablename;
而是要用：
truncatetable tablename;
这样auto_increment 就恢复成1了
或者清空内容后直接用ALTER命令修改表:
altertable tablename auto_increment =1;

MYSQL实例利用GROUP BY的WITH ROLLUP???????????????????????????????????????????????

MYSQL实例
mysql> select * from demo; 
+-------+-------+ 
| cname | pname | 
+-------+-------+ 
| bj | hd | 
| bj | xc | 
| bj | hd | 
| sh | dh | 
| sh | rg | 
| sh | dh | 
+-------+-------+ 
9 rows in set (0.00 sec)

MYSQL实例对demo表依照cname、pname列分组对pname列进行聚合计算如下

MYSQL实例
mysql> select cname,pname,count(pname) from demo group by 
cname,pname;
+-------+-------+--------------+
| cname | pname | count(pname) |
+-------+-------+--------------+
| bj | hd | 3 |
| bj | xc | 2 |
| sh | dh | 3 |
| sh | rg | 1 |
+-------+-------+--------------+
4 rows in set (0.00 sec)

MYSQL实例同样使用with rollup关键字后,统计出更多的信息,如下.注意：with rollup不可以和ordery by同时使用

MYSQL实例
ysql> select cname,count(pname) from demo group by cname,pname 
with rollup;
+-------+-------+--------------+
| 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 |
+-------+-------+--------------+
7 rows in set (0.00 sec)

MYSQL实例使用外键需要注意的问题???????????????????????????????????????????????????????????????

MYSQL实例创建外键的方式

MYSQL实例
mysql>create table temp( id int,name char(20),foreign key(id) 
references outTable(id) on delete cascade on update cascade)；

MYSQL实例注意：Innodb类型的表支持外键,myisam类型的表,虽然创建外键可以成功,但是不起作用,主要原因是不支持外键.

MYSQL实例优化SQL语句的一般步骤????????????????????????????????????????????????????????????????

MYSQL实例通过show status命令了解各种SQL的执行频率

MYSQL实例
mysql> show [session|global]status;

MYSQL实例其中：session(默认)表示当前连接,global表示自数据库启动至今

MYSQL实例
mysql>show status;
mysql>show global status;
mysql>show status like ‘Com_%';
mysql>show global status like ‘Com_%';

MYSQL实例参数说明：

MYSQL实例Com_XXX表示每个XXX语句执行的次数如：
Com_select 执行select操作的次数,一次查询只累计加1
Com_update 执行update操作的次数
Com_insert 执行insert操作的次数,对批量插入只算一次.
Com_delete 执行delete操作的次数
只针对于InnoDB存储引擎的：

MYSQL实例InnoDB_rows_read 执行select操作的次数
InnoDB_rows_updated 执行update操作的次数
InnoDB_rows_inserted 执行insert操作的次数
InnoDB_rows_deleted 执行delete操作的次数
其他：

MYSQL实例connections 连接mysql的数量
Uptime 服务器已经工作的秒数
Slow_queries:慢查询的次数
定位执行效率较低的SQL语句

MYSQL实例
explain select * from table where id=1000;
desc select * from table where id=1000;

MYSQL实例通过EXPLAIN分析较低效SQL的执行计划

MYSQL实例
mysql> explain select count(*) from stu where name like "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: Using where; Using index 
1 row in set (0.00 sec)

MYSQL实例每一列的简单解释

MYSQL实例
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: Using where; Using index 执行情况的说明和描述

MYSQL实例索引问题?????????????????????????????????????????????????????????????????????????????????????

MYSQL实例MyISAM存储引擎的表的数据和索引是自动分开存储的,各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面,但可以有多个文件组成.MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引,例如name字段,可以只取name的前4个字符进行索引,这个特性可以大大缩小索引文件的大小,用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计.

MYSQL实例
mysql>create index ind_company2_name on company2(name(4));

MYSQL实例--其中company表名ind_company2_name索引名
MySQL如何使用索引
1、使用索引

MYSQL实例(1)对于创建的多列索引,只要查询的条件中用到最左边的列,索引一般就会被使用.如下创建一个复合索引.

MYSQL实例
mysql>create index ind_sales2_com_mon on sales2(company_id,moneys);

MYSQL实例然后按company_id进行查询,发现使用到了复合索引

MYSQL实例
mysql>explain select * from sales2 where company_id=2006G

MYSQL实例使用下面的查询就没有使用到复合索引.

MYSQL实例
mysql>explain select * from sales2 where moneys=1G

MYSQL实例(2) 使用like的查询,后面如果是常量并且只有%号不在第一个字符,索引才可能会被使用,如下：

MYSQL实例
mysql> explain select * from company2 where name like "%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: Using where 
1 row in set (0.00 sec)

MYSQL实例如下这个使用到了索引,而下面例子能够使用索引,区别就在于“%”的位置不同,上面的例子是吧“%”放在了第一位,而下面的例子则没有

MYSQL实例
mysql> explain select * from company2 where name like "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: Using where 
1 row in set (0.00 sec)

MYSQL实例(3)如果对大的文本进行搜索,使用全文索引而不使用like“%...%”.

MYSQL实例(4)如果列名是索引,使用column_name is null将使用索引.如下

MYSQL实例
mysql> explain select * from company2 where name is nullG 
*************************** 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: Using where 
1 row in set (0.00 sec)

MYSQL实例存在索引但不使用索引
(1)如果MySQL估计使用索引比全表扫描更慢,则不使用索引.例如如果列key_part1均匀分布在1到100之间,查询时使用索引就不是很好

MYSQL实例
mysql>select * from table_name where key_part1>1 and key_part<90;

MYSQL实例(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列,那么不会用到索引.Heap表只有在“=”的条件下会使用索引.

MYSQL实例(3)用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到.

MYSQL实例
mysql>show index from salesG 
*************************** 1. row *************************** 
…… 
key_name: ind_sales_year 
seq_in_index：1 
Column_name: year 
……

MYSQL实例从上面可以发现只有year列上面有索引.来看如下的执行计划.

MYSQL实例
mysql> explain select * from sales where year=2001 or country=‘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: Using where 
1 row in set (0.00 sec)

MYSQL实例(4)如果不是索引列的第一部分,如下例子:可见虽然在money上面建有复合索引,但是由于money不是索引的第一列,那么在查询中这个索引也不会被MySQL采用.

MYSQL实例
mysql> explain select * from sales2 where moneys=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: Using where 
1 row in set (0.00 sec)

MYSQL实例(5)如果like是以%开始,可见虽然在name上面建有索引,但是由于where 条件中like的值的“%”在第一位了,那么MySQL也会采用这个索引.

MYSQL实例(6)如果列类型是字符串,但在查询时把一个数值型常量赋值给了一个字符型的列名name,那么虽然在name列上有索引,但是也没有用到.

MYSQL实例
mysql> explain select * from company2 where name name=294G 
*************************** 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: Using where 
1 row in set (0.00 sec)

MYSQL实例而下面的sql语句就可以正确使用索引.

MYSQL实例
mysql> explain select * from company2 where name name=‘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: Using where 
1 row in set (0.00 sec)

MYSQL实例查看索引使用情况
如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数.

MYSQL实例Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救.

MYSQL实例
mysql> show status like '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 | 
+-----------------------+-------+ 
6 rows in set (0.00 sec)

MYSQL实例两个简单实用的优化方法????????????????????????????????????????????????????????????????

MYSQL实例分析表的语法如下:(检查一个或多个表是否有错误)

MYSQL实例
mysql> CHECK TABLE tbl_name[,tbl_name] …[option] …option = 
{ QUICK | FAST | MEDIUM| EXTENDED | CHANGED} 
mysql> check table sales; 
+--------------+-------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+--------------+-------+----------+----------+ 
| sakila.sales | check | status | OK | 
+--------------+-------+----------+----------+ 
1 row in set (0.01 sec)

MYSQL实例优化表的语法格式：

MYSQL实例OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表进行了很多的改动,则需要做定期优化.这个命令可以将表中的空间碎片进行合并,但是此命令只对MyISAM、BDB和InnoDB表起作用.

MYSQL实例
mysql> optimize table sales; 
+--------------+----------+----------+----------+ 
| Table | Op | Msg_type | Msg_text | 
+--------------+----------+----------+----------+ 
| sakila.sales | optimize | status | OK | 
+--------------+----------+----------+----------+ 
1 row in set (0.05 sec)

MYSQL实例常用SQL的优化???????????????????????????????????????????????????????????????????????????

MYSQL实例大批量插入数据
当用load命令导入数据的时候,适当设置可以提高导入的速度.

MYSQL实例对于MyISAM存储引擎的表,可以通过以下方式快速的导入大量的数据.

MYSQL实例ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新,可以提高速度,注意：对InnoDB表无效.

MYSQL实例
--没有使用打开或关闭MyISAM表非唯一索引：

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2; 
Query OK,529056 rows affected (1 min 55.12 sec) 
Records:529056 Deleted:0 Skipped:0 Warnings:0

MYSQL实例--使用打开或关闭MyISAM表非唯一索引：

MYSQL实例
mysql> alter table film_test2 disable keys; 
Query OK,0 rows affected (0.0 sec) 
mysql> load data infile ‘/home/mysql/film_test.txt'into table film_test2; 
Query OK,529056 rows affected (6.34 sec) 
Records:529056 Deleted:0 Skipped:0 Warnings:0 
mysql> alter table film_test2 enable keys; 
Query OK,0 rows affected (12.25 sec)

MYSQL实例--以上对MyISAM表的数据导入,但对于InnoDB表并不能提高导入数据的效率

MYSQL实例(1)针对于InnoDB类型表数据导入的优化

MYSQL实例因为InnoDB表的依照主键顺序保存的,所以将导入的数据主键的顺序排列,可以有效地提高导入数据的效率.

MYSQL实例
--使用test3.txt文本是按表film_test4主键存储顺序保存的

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; 
Query OK,1587168 rows affected (22.92 sec)

MYSQL实例Records:1587168 Deleted:0 Skipped:0 Warnings:0
--使用test3.txt没有任何顺序的文本(效率慢了1.12倍)

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test4.txt'into table film_test4; 
Query OK,1587168 rows affected (31.16 sec) 
Records:1587168 Deleted:0 Skipped:0 Warnings:0

MYSQL实例(2)关闭唯一性效验可以提高导入效率

MYSQL实例在导入数据前先执行set unique_checks=0,关闭唯一性效验,在导入结束后执行set unique_checks=1,恢复唯一性效验,可以提高导入效率.

MYSQL实例
--当unique_checks=1时

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; 
Query OK,1587168 rows affected (22.92 sec) 
Records:1587168 Deleted:0 Skipped:0 Warnings:0

MYSQL实例--当unique_checks=0时

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; 
Query OK,1587168 rows affected (19.92 sec) 
Records:1587168 Deleted:0 Skipped:0 Warnings:0

MYSQL实例(3)关闭自动提交可以提高导入效率

MYSQL实例在导入数据前先执行set autocommit=0,关闭自动提交事务,在导入结束后执行set autocommit=1,恢复自动提交,可以提高导入效率.

MYSQL实例
--当autocommit=1时

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; 
Query OK,1587168 rows affected (22.92 sec) 
Records:1587168 Deleted:0 Skipped:0 Warnings:0

MYSQL实例--当autocommit=0时

MYSQL实例
mysql> load data infile ‘/home/mysql/film_test3.txt'into table film_test4; 
Query OK,1587168 rows affected (20.87 sec) 
Records:1587168 Deleted:0 Skipped:0 Warnings:0

MYSQL实例优化insert语句
尽量使用多个值表的insert语句,这样可以大大缩短客户与数据库的连接、关闭等损耗.

MYSQL实例可以使用insert delayed(马上执行)语句得到更高的效率.

MYSQL实例将索引文件和数据文件分别存放不同的磁盘上.

MYSQL实例可以增加bulk_insert_buffer_size 变量值的方法来提高速度,但是只对MyISAM表使用当从一个文件中装载一个表时,使用LOAD DATA INFILE.这个通常比使用很多insert语句要快20倍.

MYSQL实例优化group by语句
如果查询包含group by但用户想要避免排序结果的损耗,则可以使用使用order by null来禁止排序：

MYSQL实例如下没有使用order by null来禁止排序

MYSQL实例
mysql> explain select id,sum(moneys) from sales2 group by idG 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales2 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 1000 
Extra: Using temporary;Using filesort 
1 row in set (0.00 sec)

MYSQL实例如下使用order by null的效果：

MYSQL实例
mysql> explain select id,sum(moneys) from sales2 group by id order by nullG 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales2 
type: ALL 
possible_keys: NULL 
key: NULL 
key_len: NULL 
ref: NULL 
rows: 1000 
Extra: Using temporary 
1 row in set (0.00 sec)

MYSQL实例优化嵌套查询
下面是采用嵌套查询的效果(可以使用更有效的链接查询(Join)替代).

MYSQL实例
mysql> explain select * from sales2 where company_id not in(select id 
from company2)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: Using where 
1 row in set (0.00 sec)
*************************** 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: Using index 
1 row in set (0.00 sec)

MYSQL实例下面是使用更有效的链接查询(Join)

MYSQL实例
mysql> explain select * from sales2 left join company2 on 
sales2.company_id = company2.id where sales2.company_id is nullG 
*************************** 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: Using where 
1 row in set (0.00 sec) 
*************************** 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: 
1 row in set (0.00 sec)

MYSQL实例从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善.连接(JOIN)子所以更有效率一些,是因为MySQL不需要再内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作.

MYSQL实例数据库优化??????????????????????????????????????????????????????????????????????????????????

MYSQL实例优化表的类型
在MySQL中,可以使用函数PROCEDUREANALYSE()对当前应用的表进行分析,改函数可以对数据表中列的数据类型提出优化建议,用户可以根据应用的实际情况酌情考虑是否实施优化.

MYSQL实例
mysql> select * from duck_cust procedure analyse()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') NOT NULL 
*************************** 2. row *************************** 
……

MYSQL实例大存储量解决
1.分库分表

MYSQL实例2.分区

MYSQL实例主要目的:

MYSQL实例1.减少表的记录数

MYSQL实例2.减小对操作系统的负担压力

MYSQL实例中间表
中间表的产生:
1.view 视图
2.重新生成一个新表

MYSQL实例Mysql服务器优化????????????????????????????????????????????????????????????????????????

MYSQL实例myisam读锁定
1.

MYSQL实例lock table t1 read
2.开启另一个mysql连接终端,接着去尝试:

MYSQL实例select * from t1
3.再insert、update和delete t1这张表,你会发现所有的数据都停留在终端上没有真正的去操作

MYSQL实例4.读锁定对我们在做备份大量数据时非常有用.

MYSQL实例mysqldump -uroot -p123 test >test.sql
myisam写锁定
1.

MYSQL实例lock table t1 write
2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作,都会停留在终端上,只有等第一个终端操作完毕,第二个终端才能真正执行.

MYSQL实例3.可见表的写锁定比读锁定更严格

MYSQL实例4.一般情况下我们很少去显式的去对表进行read 和write锁定的,myisam会自动进行锁定的.

MYSQL实例Mysql服务器优化???????????????????????????????????????????????????????????????????????

MYSQL实例二进制日志
1.log-bin=mysql-bin

MYSQL实例查看bin-log日志:

MYSQL实例mysql> show binary logs;
查看最后一个bin-log日志:

MYSQL实例mysql> show master status;
慢查询日志
开户和设置慢查询时间:

MYSQL实例vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查询次数:

MYSQL实例mysql> show global status like "%quer%"
socket问题
mysql socket无法登录

MYSQL实例1. 有时登录mysql时提示不能用socket登录,此时可以换成tcp方式去登录,但是可以测试时可以这样用,但是必须要在php去用之前把这个事情解决了.

MYSQL实例
[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhost
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 34
Server version: 5.0.77-log Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

MYSQL实例这样就可以登录,这样就不用mysql.sock来登录,而mysql.sock是启动mysqld服务时产生的

MYSQL实例root暗码丢失

MYSQL实例root暗码丢失破解

MYSQL实例
1.service mysqld stop
2. mysqld_safe --skip-grant-tables --user=mysql & 
//跳过授权表mysql.user和mysql.db这些表
3. mysql -uroot
4. set password=password("wei"); 
//用这一条语句结果报错,就是因为加了--skip-grant-tables
4. mysql>update user set password=password("wei") where user='root' 
and host='localhost';
5. mysql> set password for root@localhost=password("wei");
6. mysql> set password=password("wei"); 
//和第五步一样,都可能成功修改暗码

编程之家PHP培训学院每天发布《Mysql应用MySQL数据库优化详解》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!