首页 > 代码库 > sql学习笔记（18）-----------数据库创建过程
sql学习笔记（18）-----------数据库创建过程
该数据库有三个在线重做日志组。通过LOGFILE子句来指定，MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY都是对日志文件的限制。
MAXDATAFILES指定了数据库能够打开的数据文件的最大数量，该数量会影响到控制文件的大小。在创建数据库的过程中所设定的參数，有些会受到其它因素的影响。
比方，设定了MAXDATAFILES之后，Oracle在控制文件里要分配空间存放数据文件的文件名称。即使数据库仅仅有一个数据文件。然而，由于控制文件本身也有大小的限制（跟操作系统有关）。非常有可能不能把全部CREATE DATABASE的參数都设置为他们理论上的最大值。
初始化文件里的LOG_ARCHIVE_DEST_1,LOG_ARCHIVE_FORMAT, and LOG_ARCHIVE_START三个參数与归档有关。
mysql表复制
复制表结构+复制表数据
|
1
2
|
mysql> createtable
t3 liket1;
mysql> insertinto
t3 select*
from t1; |
mysql索引
|
1
2
3
4
5
6
7
8
9
10
11
12
|
ALTER
TABLE用来创建普通索引、UNIQUE索引或PRIMARYKEY索引
ALTER
TABLE table_name ADDINDEX
index_name (column_list)ALTER
TABLE table_name ADDUNIQUE
(column_list) ALTER
TABLE table_name ADDPRIMARY KEY(column_list)
Create
IndexCREATE
INDEX index_name ONtable_name (column_list)
CREATE
UNIQUE INDEX index_nameON
table_name (column_list)drop
indexDROP
INDEX index_name ONtalbe_name
alter
table table dropALTER
TABLE table_name DROPINDEX
index_name ALTER
TABLE table_name DROPPRIMARY
KEY |
mysql视图
创建视图
|
1
2
|
mysql> createview
v_t1 asselect
* fromt1
where id>4
and id<11; Query OK, 0
rows affected (0.00 sec) |
view视图的帮助信息
|
1
2
3
4
|
mysql> ? viewALTER
VIEWCREATE
VIEWDROP
VIEW |
查看视图
|
1
|
mysql> show tables; |
删除视图v_t1
|
1
|
mysql> dropview
v_t1; |
mysql内置函数
字符串函数
|
1
2
3
4
5
6
7
8
9
10
|
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个空格 |
数学函数
|
1
2
3
4
5
6
7
|
BIN (decimal_number ) //十进制转二进制CEILING (number2 ) //向上取整FLOOR (number2 ) //向下取整MAX(num1 ,num2) //取最大值MIN(num1,num2) //取最小值SQRT(number2) //开平方RAND() //返回0-1内的随机值 |
日期函数
|
1
2
3
4
5
6
7
8
|
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推断条件
|
1
|
mysql> preparestmt1
from ‘select * from t1 where id>?
|
设置一个变量
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事务处理
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
--关闭自己主动提交功能
mysql> setautocommit=0;
--从表t1中删除了一条记录
mysql> deletefrom
t1 whereid=11;
--此时做一个p1还原点:
mysql> savepoint p1;--再次从表t1中删除一条记录:mysql> deletefrom
t1 whereid=10;
--再次做一个p2还原点:
mysql> savepoint p2;--此时恢复到p1还原点。当然后面的p2这些还原点自己主动会失效: mysql> rollbackto
p1; --退回到最原始的还原点:
mysql> rollback; |
mysql存储
创建一个存储p1()
|
1
2
3
4
5
6
7
8
9
10
|
mysql> \d //mysql> create procedure p1()-> begin -> set@i=0;
-> while@i<10
do-> select@i;
-> set@i=@i+1;
-> end while;-> end; -> // |
运行存储p1()
|
1
2
3
4
5
6
|
mysql> \d ;
mysql> call p1();
--查看procedure p1()的status信息mysql> show procedure status\G--查看procedure p1()的详细信息:mysql> show create procedure p1\G |
mysql触发器
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
改动delimiter为//mysql> \d //创建一个名字为tg1的触发器,当向t1表中插入数据时。就向t2表中插入一条数据 mysql> create trigger tg1 before insert on t1for
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 t1for
each row >begin delete from t2 whereid=old.id;>end//mysql>\d ;
怎样制作更改表t1后t2表中的记录跟着个性呢mysql>\d
//mysql> create trigger tg3 beforeupdate on t1for
each row >begin update t2set
id=new.idwhere
id=old.id; >end//mysql>\d ;
查看触发器 mysql> show triggers; |
重排auto_increment值
|
1
2
3
4
5
6
7
8
9
10
11
|
MYSQL数据库自己主动增长的ID怎样恢复，清空表的时候。
delete from tablename;而是要用： truncatetable tablename;这样auto_increment 就恢复成1了 或者清空内容后直接用ALTER命令改动表: altertable tablename auto_increment =1; |
利用GROUP BY的WITH ROLLUP
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> select* from demo;
+-------+-------+ | cname | pname | +-------+-------+ | bj | hd |
| bj | xc |
| bj | hd |
| sh | dh |
| sh | rg |
| sh | dh |
+-------+-------+ 9 rows inset
(0.00 sec) |
对demo表依照cname、pname列分组对pname列进行聚合计算例如以下
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> selectcname,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 inset
(0.00 sec) |
相同使用with rollupkeyword后，统计出很多其它的信息。例如以下。
注意：with rollup不能够和ordery by同一时候使用
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
ysql> selectcname,pname,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 inset
(0.00 sec) |
使用外键须要注意的问题
创建外键的方式
|
1
2
|
mysql>create table temp(id
int, name char(20), foreign key(id) references outTable(id) on delete cascade on update cascade)。 |
注意：Innodb类型的表支持外键，myisam类型的表。尽管创建外键能够成功，可是不起作用，主要原因是不支持外键。
优化SQL语句的一般步骤
通过show status命令了解各种SQL的运行频率
|
1
|
mysql> show [session|global]status; |
当中：session（默认）表示当前连接，global表示自数据库启动至今
|
1
2
3
4
|
mysql>show status;mysql>show global status;mysql>show status like ‘Com_%‘;mysql>show global status like ‘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 server已经工作的秒数
Slow_queries:慢查询的次数
定位运行效率较低的SQL语句
|
1
2
|
explain select*
from table
where id=1000;desc
select * fromtable
where id=1000; |
通过EXPLAIN分析较低效SQL的运行计划
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 inset
(0.00 sec) |
每一列的简单解释
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 运行情况的说明和描写叙述
索引问题
MyISAM存储引擎的表的数据和索引是自己主动分开存储的，分别是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但能够有多个文件组成。MySQL眼下不支持函数索引，可是能对列的前面某一部分进行索引，比如name字段，能够仅仅取name的前4个字符进行索引。这个特性能够大大缩小索引文件的大小，用户在设计表结构的时候也能够对文本列依据此特性进行灵活设计。
|
1
|
mysql>create index ind_company2_name on company2(name(4)); |
--当中company表名ind_company2_name索引名
MySQL怎样使用索引
1、使用索引
(1)对于创建的多列索引。仅仅要查询的条件中用到最左边的列。索引一般就会被使用。例如以下创建一个复合索引。
|
1
|
mysql>create index ind_sales2_com_mon on sales2(company_id,moneys); |
然后按company_id进行查询。发现使用到了复合索引
|
1
|
mysql>explain
select * from sales2 where company_id=2006\G |
使用以下的查询就没有使用到复合索引。
|
1
|
mysql>explain
select * from sales2 where moneys=1\G |
(2) 使用like的查询，后面假设是常量而且仅仅有%号不在第一个字符。索引才可能会被使用。例如以下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 inset
(0.00 sec) |
例如以下这个使用到了索引。而以下样例可以使用索引，差别就在于“%”的位置不同，上面的样例是吧“%”放在了第一位，而以下的样例则没有
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 inset
(0.00 sec) |
(3)假设对大的文本进行搜索，使用全文索引而不使用like“%...%”.
(4)假设列名是索引。使用column_name is null将使用索引。例如以下
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> explain
select * from company2 where name is null\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: Using where 1 row inset
(0.00 sec) |
存在索引但不使用索引
(1)假设MySQL预计使用索引比全表扫描更慢，则不使用索引。比如假设列key_part1均匀分布在1到100之间，查询时使用索引就不是非常好
|
1
|
mysql>select* from table_name where key_part1>1 and key_part<90; |
(2)假设使用MEMORY/HEAP表而且where条件中不使用“=”进行索引列。那么不会用到索引。Heap表仅仅有在“=”的条件下会使用索引。
(3)用or切割开的条件。假设or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
|
1
2
3
4
5
6
7
|
mysql>show index from sales\G *************************** 1. row *************************** …… key_name: ind_sales_year seq_in_index：1
Column_name: year …… |
从上面能够发现仅仅有year列上面有索引。
来看例如以下的运行计划。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 inset
(0.00 sec) |
(4)假设不是索引列的第一部分，例如以下样例:可见尽管在money上面建有复合索引，可是因为money不是索引的第一列，那么在查询中这个索引也不会被MySQL採用。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 inset
(0.00 sec) |
(5)假设like是以%開始。可见尽管在name上面建有索引。可是因为where 条件中like的值的“%”在第一位了，那么MySQL也会採用这个索引。
(6)假设列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name。那么尽管在name列上有索引，可是也没实用到。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> explain
select * from company2 where name name=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: Using where 1 row inset
(0.00 sec) |
而以下的sql语句就能够正确使用索引。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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 inset
(0.00 sec) |
查看索引使用情况
假设索引正在工作。Handler_read_key的值将非常高，这个值代表了一个行被索引值读的次数。
Handler_read_rnd_next的值高则意味着查询执行低效。而且应该建立索引补救。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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 inset
(0.00 sec) |
两个简单有用的优化方法
分析表的语法例如以下:(检查一个或多个表是否有错误)
|
1
2
3
4
5
6
7
8
9
|
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 inset
(0.01 sec) |
优化表的语法格式：
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]
假设已经删除了表的一大部分，或者假设已经对含有可变长度行的表进行了非常多的修改。则须要做定期优化。
这个命令能够将表中的空间碎片进行合并，可是此命令仅仅对MyISAM、BDB和InnoDB表起作用。
|
1
2
3
4
5
6
7
|
mysql> optimize table sales; +--------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------+ | sakila.sales | optimize | status | OK | +--------------+----------+----------+----------+ 1 row inset
(0.05 sec) |
经常使用SQL的优化
大批量插入数据
当用load命令导入数据的时候。适当设置能够提高导入的速度。
对于MyISAM存储引擎的表，能够通过下面方式高速的导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS
DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新，能够提快速度，注意：对InnoDB表无效。
--没有使用打开或关闭MyISAM表非唯一索引：
|
1
2
3
|
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 |
--使用打开或关闭MyISAM表非唯一索引：
|
1
2
3
4
5
6
7
|
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_test2enable
keys; Query OK,0 rows affected (12.25 sec) |
--以上对MyISAM表的数据导入，但对于InnoDB表并不能提高导入数据的效率
（1）针对于InnoDB类型表数据导入的优化
由于InnoDB表的依照主键顺序保存的，所以将导入的数据主键的顺序排列，能够有效地提高导入数据的效率。
--使用test3.txt文本是按表film_test4主键存储顺序保存的
|
1
2
|
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
--使用test3.txt没有不论什么顺序的文本（效率慢了1.12倍）
|
1
2
3
|
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 |
（2）关闭唯一性效验能够提高导入效率
在导入数据前先运行set unique_checks=0，关闭唯一性效验，在导入结束后运行set unique_checks=1，恢复唯一性效验，能够提高导入效率。
--当unique_checks=1时
|
1
2
3
|
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 |
--当unique_checks=0时
|
1
2
3
|
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 |
（3）关闭自己主动提交能够提高导入效率
在导入数据前先运行set autocommit=0。关闭自己主动提交事务。在导入结束后运行set autocommit=1，恢复自己主动提交，能够提高导入效率。
--当autocommit=1时
|
1
2
3
|
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 |
--当autocommit=0时
|
1
2
3
|
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 |
优化insert语句
尽量使用多个值表的insert语句，这样能够大大缩短客户与数据库的连接、关闭等损耗。
能够使用insert delayed（立即运行）语句得到更高的效率。
将索引文件和数据文件分别存放不同的磁盘上。
能够添加bulk_insert_buffer_size 变量值的方法来提快速度。可是仅仅对MyISAM表使用当从一个文件里装载一个表时，使用LOAD DATA INFILE。这个通常比使用非常多insert语句要快20倍。
优化group by语句
假设查询包括group by但用户想要避免排序结果的损耗，则能够使用使用order by null来禁止排序：
例如以下没有使用order by null来禁止排序
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> explain
select id,sum(moneys) from sales2 group byid\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 temporary;Using filesort 1 row inset
(0.00 sec) |
例如以下使用order by null的效果：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> explain
select id,sum(moneys) from sales2 group byid
order by null\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 temporary 1 row inset
(0.00 sec) |
优化嵌套查询
以下是採用嵌套查询的效果（能够使用更有效的链接查询（Join）替代）。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> explain
select * from sales2 where company_id notin(selectid 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 inset
(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 inset
(0.00 sec) |
以下是使用更有效的链接查询（Join）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> explain
select * from sales2 leftjoin
company2 on sales2.company_id = company2.idwhere sales2.company_id is null\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: Using where 1 row inset
(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 inset
(0.00 sec) |
从运行计划中能够明显看出查询扫描的记录范围和使用索引的情况都有了非常大的改善。连接（JOIN）子所以更有效率一些，是由于MySQL不须要再内存中创建暂时表来完毕这个逻辑上的须要两个步骤的查询工作。
数据库优化
优化表的类型
在MySQL中，能够使用函数PROCEDUREANALYSE（）对当前应用的表进行分析，改函数能够对数据表中列的数据类型提出优化建议，用户能够依据应用的实际情况酌情考虑是否实施优化。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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 *************************** …… |
大存储量解决
1.分库分表
2.分区
主要目的:
1.降低表的记录数
2.减小对操作系统的负担压力
中间表
中间表的产生:
1.view 视图
2.又一次生成一个新表
Mysqlserver优化
myisam读锁定
1.
lock table t1 read
2.开启还有一个mysql连接终端,接着去尝试:
select * from t1
3.再insert、update和delete t1这张表。你会发现全部的数据都停留在终端上没有真正的去操作
4.读锁定对我们在做备份大量数据时很实用.
mysqldump -uroot -p123 test >test.sql
myisam写锁定
1.
lock table t1 write
2.打开还有一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作，都会停留在终端上。仅仅有等第一个终端操作完成,第二个终端才干真正运行.
3.可见表的写锁定比读锁定更严格
4.普通情况下我们非常少去显式的去对表进行read 和write锁定的,myisam会自己主动进行锁定的.
Mysqlserver优化
二进制日志
1.log-bin=mysql-bin
查看bin-log日志:
mysql> show binary logs;
查看最后一个bin-log日志:
mysql> show master status;
慢查询日志
开户和设置慢查询时间:
vi /etc/my.cnf
log_slow_queries=slow.log
long_query_time=5
慢查询次数:
mysql> show global status like "%quer%"
socket问题
mysql socket无法登录
1. 有时登录mysql时提示不能用socket登录，此时能够换成tcp方式去登录，可是能够測试时能够这样用。可是必需要在php去用之前把这个事情攻克了.
|
1
2
3
4
5
6
|
[root@localhost mysql]# mysql -uroot -pwei --protocol tcp -hlocalhostWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connectionid
is 34 Server version: 5.0.77-log Source distributionType ‘help;‘or
‘\h‘ for
help. Type ‘\c‘to
clear the buffer.
mysql> |
这样就能够登录,这样就不用mysql.sock来登录。而mysql.sock是启动mysqld服务时产生的
rootpassword丢失
rootpassword丢失破解
|
1
2
3
4
5
6
7
8
9
10
11
|
1.service mysqld stop2. mysqld_safe --skip-grant-tables --user=mysql & //跳过授权表mysql.user和mysql.db这些表3. mysql -uroot
4. set password=password("wei"); //用这一条语句结果报错,就是由于加了--skip-grant-tables4. 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"); //和第五步一样,都可能成功改动password |
sql学习笔记（18）-----------数据库创建过程