Basic of MySQL
創(chuàng)建數(shù)據(jù)庫(kù):
1
mysql
>
create
database
xxj;
2
Query OK,
1
row affected (
0.00
sec)
列舉數(shù)據(jù)庫(kù):
1
mysql
>
show databases;
2
+
--
------------------+
3
|
Database
|
4
+
--
------------------+
5
|
information_schema
|
6
|
mysql
|
7
|
xxj
|
8
+
--
------------------+
進(jìn)入某個(gè)數(shù)據(jù)庫(kù)：
1
mysql
>
use
xxj;
2
Database
changed
創(chuàng)建表：
1
mysql
>
create
table
test(id
int
primary
key
auto_increment, name
varchar
(
20
)
not
null
, pwd
varchar
(
20
)
default
'
123
'
);
2
Query OK,
0
rows affected (
0.01
sec)
設(shè)置了主鍵，默認(rèn)值，not null；
刪除表：
mysql
>
drop
table
test;
Query OK,
0
rows affected (
0.00
sec)
查看表：
1
mysql
>
desc
test;
2
+
--
-----+-------------+------+-----+---------+----------------+
3
|
Field
|
Type
|
Null
|
Key
|
Default
|
Extra
|
4
+
--
-----+-------------+------+-----+---------+----------------+
5
|
id
|
int
(
11
)
|
NO
|
PRI
|
NULL
|
auto_increment
|
6
|
name
|
varchar
(
20
)
|
NO
|
|
NULL
|
|
7
|
pwd
|
varchar
(
20
)
|
YES
|
|
123
|
|
8
+
--
-----+-------------+------+-----+---------+----------------+
9
3
rows
in
set
(
0.01
sec)
改變表：
1
mysql
>
alter
table
test
add
column
addr
text
;
2
Query OK,
0
rows affected (
0.34
sec)
3
Records:
0
Duplicates:
0
Warnings:
0
1
mysql
>
alter
table
test
drop
column
addr;
2
Query OK,
0
rows affected (
0.01
sec)
3
Records:
0
Duplicates:
0
Warnings:
0
刪除主鍵：
mysql
>
alter
table
test
drop
primary
key
;
Query OK,
0
rows affected (
0.01
sec)
Records:
0
Duplicates:
0
Warnings:
0
添加主鍵：
1
mysql
>
alter
table
test
add
primary
key
(id);
2
Query OK,
0
rows affected (
0.01
sec)
3
Records:
0
Duplicates:
0
Warnings:
0
插入記錄：
1
mysql
>
insert
into
test(name, pwd)
values
("xxj", "
1323
");
2
Query OK,
1
row affected,
1
warning (
0.00
sec)
更新：
1
mysql
>
update
test
set
pwd
=
'
2222
'
where
id
=
1
;
2
Query OK,
1
row affected (
0.03
sec)
3
Rows matched:
1
Changed:
1
Warnings:
0
4
5
mysql
>
select
*
from
test;
6
+
--
--+------+------+
7
|
id
|
name
|
pwd
|
8
+
--
--+------+------+
9
|
1
|
xxj
|
2222
|
10
+
--
--+------+------+
11
1
row
in
set
(
0.00
sec)
清空表：
1
mysql
>
delete
from
test;
2
Query OK,
2
rows affected (
0.00
sec)
3
4
mysql
>
select
*
from
test;
5
Empty
set
(
0.00
sec)
刪除記錄：
1
mysql
>
delete
from
test
where
id
=
4
;
2
Query OK,
1
row affected (
0.00
sec)
查詢：
1
mysql
>
select
*
from
test
where
name
like
'
xxj%
'
;
2
+
--
--+------+------+
3
|
id
|
name
|
pwd
|
4
+
--
--+------+------+
5
|
7
|
xxj
|
123
|
6
|
6
|
xxj3
|
123
|
7
|
8
|
xxj2
|
123
|
8
+
--
--+------+------+
9
3
rows
in
set
(
0.00
sec)
10
11
mysql
>
select
*
from
test
where
name
like
'
%xxj%
'
;
12
+
--
--+-------+------+
13
|
id
|
name
|
pwd
|
14
+
--
--+-------+------+
15
|
7
|
xxj
|
123
|
16
|
6
|
xxj3
|
123
|
17
|
8
|
xxj2
|
123
|
18
|
11
|
xxxj2
|
123
|
19
+
--
--+-------+------+
20
4
rows
in
set
(
0.00
sec)
group/having：
1
mysql
>
select
count
(
*
)
as
c
from
xxb
group
by
age
having
c
>
0
;
2
+
--
-+
3
|
c
|
4
+
--
-+
5
|
2
|
6
|
1
|
7
|
1
|
8
|
1
|
9
+
--
-+
10
4
rows
in
set
(
0.00
sec)
?還有distinct去重，order by 排序等。
高級(jí)一點(diǎn)
join
1
mysql
>
create
table
xxa(id
int
primary
key
auto_increment, name
varchar
(
20
));
2
Query OK,
0
rows affected (
0.01
sec)
3
4
mysql
>
create
table
xxb(id
int
primary
key
auto_increment, age
int
);
5
Query OK,
0
rows affected (
0.00
sec)
6
7
mysql
>
show tables;
8
+
--
-------------+
9
|
Tables_in_xxj
|
10
+
--
-------------+
11
|
test
|
12
|
xxa
|
13
|
xxb
|
14
+
--
-------------+
15
3
rows
in
set
(
0.00
sec)
16
17
mysql
>
insert
into
xxa(name)
values
(
'
xxj
'
), (
'
xxa
'
), (
'
xxb
'
), (
'
xxc
'
);
18
Query OK,
4
rows affected (
0.00
sec)
19
Records:
4
Duplicates:
0
Warnings:
0
20
21
mysql
>
insert
into
xxb(age)
values
(
10
), (
11
), (
12
), (
13
);
22
Query OK,
4
rows affected (
0.00
sec)
23
Records:
4
Duplicates:
0
Warnings:
0
left join：
1
mysql
>
select
*
from
xxa
left
join
xxb
on
xxa.id
=
xxb.id;
2
+
--
--+------+------+------+
3
|
id
|
name
|
id
|
age
|
4
+
--
--+------+------+------+
5
|
1
|
xxj
|
1
|
10
|
6
|
2
|
xxa
|
2
|
11
|
7
|
3
|
xxb
|
3
|
12
|
8
|
4
|
xxc
|
4
|
13
|
9
|
5
|
xxd
|
NULL
|
NULL
|
10
+
--
--+------+------+------+
11
5
rows
in
set
(
0.00
sec)
right join:
1
mysql
>
select
*
from
xxa
right
join
xxb
on
xxa.id
=
xxb.id;
2
+
--
----+------+----+------+
3
|
id
|
name
|
id
|
age
|
4
+
--
----+------+----+------+
5
|
1
|
xxj
|
1
|
10
|
6
|
2
|
xxa
|
2
|
11
|
7
|
3
|
xxb
|
3
|
12
|
8
|
4
|
xxc
|
4
|
13
|
9
|
NULL
|
NULL
|
8
|
14
|
10
+
--
----+------+----+------+
11
5
rows
in
set
(
0.00
sec)
inner join:
1
mysql
>
select
*
from
xxa, xxb
where
xxa.id
=
xxb.id;
2
+
--
--+------+----+------+
3
|
id
|
name
|
id
|
age
|
4
+
--
--+------+----+------+
5
|
1
|
xxj
|
1
|
10
|
6
|
2
|
xxa
|
2
|
11
|
7
|
3
|
xxb
|
3
|
12
|
8
|
4
|
xxc
|
4
|
13
|
9
+
--
--+------+----+------+
10
4
rows
in
set
(
0.00
sec)
limit:
1
mysql
>
select
*
from
xxa, xxb
where
xxa.id
=
xxb.id limit
2
,
3
;
2
+
--
--+------+----+------+
3
|
id
|
name
|
id
|
age
|
4
+
--
--+------+----+------+
5
|
3
|
xxb
|
3
|
12
|
6
|
4
|
xxc
|
4
|
13
|
7
+
--
--+------+----+------+
8
2
rows
in
set
(
0.00
sec)
limit 有兩個(gè)參數(shù)，第一個(gè)是偏移量（從0開(kāi)始），第二個(gè)是返回結(jié)果數(shù)目。
limit n相當(dāng)于limit 0, n。
存儲(chǔ)過(guò)程：
1
mysql
>
delimiter $
2
mysql
>
create
procedure
test(
in
p
int
)
begin
insert
into
xxb(age)
values
(p);
end
$
3
Query OK,
0
rows affected (
0.00
sec)
4
5
mysql
>
delimiter ;
6
mysql
>
call test(
100
);
7
Query OK,
1
row affected (
0.02
sec)
8
9
mysql
>
select
*
from
xxb;
10
+
--
--+------+
11
|
id
|
age
|
12
+
--
--+------+
13
|
1
|
10
|
14
|
2
|
11
|
15
|
3
|
12
|
16
|
4
|
13
|
17
|
5
|
22
|
18
|
6
|
55
|
19
|
7
|
100
|
20
+
--
--+------+
21
7
rows
in
set
(
0.00
sec)
這里一開(kāi)始要用delimiter $修改一下結(jié)束符為$，這樣才能在終端寫(xiě)存儲(chǔ)過(guò)程，創(chuàng)建好存儲(chǔ)過(guò)程之后再改回來(lái)就行。
1
mysql
>
show
procedure
status;
2
+
--
---+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3
|
Db
|
Name
|
Type
|
Definer
|
Modified
|
Created
|
Security_type
|
Comment
|
character_set_client
|
collation_connection
|
Database
Collation
|
4
+
--
---+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
5
|
xxj
|
a
|
PROCEDURE
|
root
@localhost
|
2014
-
10
-
09
11
:
51
:
09
|
2014
-
10
-
09
11
:
51
:
09
|
DEFINER
|
|
utf8
|
utf8_general_ci
|
latin1_swedish_ci
|
6
|
xxj
|
b
|
PROCEDURE
|
root
@localhost
|
2014
-
10
-
09
12
:
01
:
21
|
2014
-
10
-
09
12
:
01
:
21
|
DEFINER
|
|
utf8
|
utf8_general_ci
|
latin1_swedish_ci
|
7
|
xxj
|
test
|
PROCEDURE
|
root
@localhost
|
2014
-
10
-
09
12
:
02
:
42
|
2014
-
10
-
09
12
:
02
:
42
|
DEFINER
|
|
utf8
|
utf8_general_ci
|
latin1_swedish_ci
|
8
+
--
---+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
9
3
rows
in
set
(
0.00
sec)
10
11
mysql
>
drop
procedure
a;
12
Query OK,
0
rows affected (
0.02
sec)
使用存儲(chǔ)過(guò)程的好處：
-
減少了服務(wù)器/客戶端網(wǎng)絡(luò)流量
- 過(guò)程中的命令作為代碼的單個(gè)批處理執(zhí)行。 這可以顯著減少服務(wù)器和客戶端之間的網(wǎng)絡(luò)流量，因?yàn)橹挥袑?duì)執(zhí)行過(guò)程的調(diào)用才會(huì)跨網(wǎng)絡(luò)發(fā)送。 如果沒(méi)有過(guò)程提供的代碼封裝，每個(gè)單獨(dú)的代碼行都不得不跨網(wǎng)絡(luò)發(fā)送。
-
更強(qiáng)的安全性
- 多個(gè)用戶和客戶端程序可以通過(guò)過(guò)程對(duì)基礎(chǔ)數(shù)據(jù)庫(kù)對(duì)象執(zhí)行操作，即使用戶和程序?qū)@些基礎(chǔ)對(duì)象沒(méi)有直接權(quán)限。 過(guò)程控制執(zhí)行哪些進(jìn)程和活動(dòng)，并且保護(hù)基礎(chǔ)數(shù)據(jù)庫(kù)對(duì)象。 這消除在了單獨(dú)的對(duì)象級(jí)別授予權(quán)限的要求，并且簡(jiǎn)化了安全層。
-
代碼的重復(fù)使用
- 任何重復(fù)的數(shù)據(jù)庫(kù)操作的代碼都非常適合于在過(guò)程中進(jìn)行封裝。 這消除了不必要地重復(fù)編寫(xiě)相同的代碼、降低了代碼不一致性，并且允許擁有所需權(quán)限的任何用戶或應(yīng)用程序訪問(wèn)和執(zhí)行代碼。
-
更容易維護(hù)
- 在客戶端應(yīng)用程序調(diào)用過(guò)程并且將數(shù)據(jù)庫(kù)操作保持在數(shù)據(jù)層中時(shí)，對(duì)于基礎(chǔ)數(shù)據(jù)庫(kù)中的任何更改，只有過(guò)程是必須更新的。
- 應(yīng)用程序?qū)颖３知?dú)立，并且不必知道對(duì)數(shù)據(jù)庫(kù)布局、關(guān)系或進(jìn)程的任何更改的情況。
-
改進(jìn)的性能
- 默認(rèn)情況下，在首次執(zhí)行過(guò)程時(shí)將編譯過(guò)程，并且創(chuàng)建一個(gè)執(zhí)行計(jì)劃，供以后的執(zhí)行重復(fù)使用。 因?yàn)椴樵兲幚砥鞑槐貏?chuàng)建新計(jì)劃，所以，它通常用更少的時(shí)間來(lái)處理過(guò)程。
- 如果過(guò)程引用的表或數(shù)據(jù)有顯著變化，則預(yù)編譯的計(jì)劃可能實(shí)際上會(huì)導(dǎo)致過(guò)程的執(zhí)行速度減慢。 在此情況下，重新編譯過(guò)程和強(qiáng)制新的執(zhí)行計(jì)劃可提高性能。
查看當(dāng)前狀態(tài)
1
mysql
>
show processlist;
2
+
--
--+------+-----------+------+---------+------+-------+------------------+
3
|
Id
|
User
|
Host
|
db
|
Command
|
Time
|
State
|
Info
|
4
+
--
--+------+-----------+------+---------+------+-------+------------------+
5
|
47
|
root
|
localhost
|
xxj
|
Query
|
0
|
init
|
show processlist
|
6
+
--
--+------+-----------+------+---------+------+-------+------------------+
7
1
row
in
set
(
0.00
sec)
8
mysql
>
show status
like
'
%aborted%
'
;
9
+
--
----------------+-------+
10
|
Variable_name
|
Value
|
11
+
--
----------------+-------+
12
|
Aborted_clients
|
0
|
13
|
Aborted_connects
|
1
|
14
+
--
----------------+-------+
15
2
rows
in
set
(
0.00
sec)
16
17
mysql
>
show variables
like
'
%connection%
'
;
18
+
--
------------------------+-----------------+
19
|
Variable_name
|
Value
|
20
+
--
------------------------+-----------------+
21
|
character_set_connection
|
utf8
|
22
|
collation_connection
|
utf8_general_ci
|
23
|
max_connections
|
151
|
24
|
max_user_connections
|
0
|
25
+
--
------------------------+-----------------+
26
4
rows
in
set
(
0.03
sec)
或者：
1
root
@xxj
-
VirtualBox:
~
# mysqladmin
-
uroot
-
p processlist
2
Enter password:
3
+
--
--+------+-----------+----+---------+------+-------+------------------+
4
|
Id
|
User
|
Host
|
db
|
Command
|
Time
|
State
|
Info
|
5
+
--
--+------+-----------+----+---------+------+-------+------------------+
6
|
48
|
root
|
localhost
|
|
Query
|
0
|
init
|
show processlist
|
7
+
--
--+------+-----------+----+---------+------+-------+------------------+
8
root
@xxj
-
VirtualBox:
~
# mysqladmin
-
uroot
-
p status
9
Enter password:
10
Uptime:
354349
Threads:
1
Questions:
205
Slow queries:
0
Opens:
335
Flush tables:
1
Open
tables:
83
Queries per second
avg
:
0.000
變量可以通過(guò)配置文件my.conf來(lái)修改。當(dāng)連接請(qǐng)求大于默認(rèn)連接數(shù)后，就會(huì)出現(xiàn)無(wú)法連接數(shù)據(jù)庫(kù)的錯(cuò)誤。
索引
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.?
B樹(shù)索引可以用于查找、排序、求最大最小值、范圍查詢等。
hash索引只能用于作=或者<=或者>=查找，不能用作<或>查找。
- They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values.
- The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
- MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table.
- Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
部分摘自：http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
http://blog.codinglabs.org/articles/theory-of-mysql-index.html 這一篇提到mysql實(shí)際是用B+樹(shù)（myisam和innodb引擎都是）。估計(jì)官網(wǎng)所說(shuō)的B-tree，其實(shí)指的是B+樹(shù)吧，畢竟B+樹(shù)也是B樹(shù)的一個(gè)變種。?
為了進(jìn)行高效查詢，可以在數(shù)據(jù)表上針對(duì)某一字段建立索引，由于該索引包括了一個(gè)指向數(shù)據(jù)的指針，數(shù)據(jù)庫(kù)服務(wù)器則只沿著索引排列的順序?qū)H有一列數(shù)據(jù)的索引進(jìn)行讀取（只建立一個(gè)索引）直至索引指針指向相應(yīng)的記錄上為止。由于索引只是按照一個(gè)字段進(jìn)行查找，而沒(méi)有對(duì)整表進(jìn)行遍歷，因此一般說(shuō)來(lái)索引查找比全表掃描的速度快。
那么，是不是使用索引查詢一定比全表掃描的速度快呢？答案是否定的。如果查詢小型數(shù)據(jù)表（記錄很少）或是查詢大型數(shù)據(jù)表（記錄很多）的絕大部分?jǐn)?shù)據(jù)，全表掃描更為實(shí)用。例如，查詢“性別”字段，其值只能是“男或女”，在其上建立索引的意義就不大，甚至不允許在布爾型、大二進(jìn)制型（備注型、圖像型等）上建立索引。
1.聚集索引
其實(shí)，我們的漢語(yǔ)字典的正文本身就是一個(gè)聚集索引。比如，我們要查“安”字，就會(huì)很自然地翻開(kāi)字典的前幾頁(yè)，因?yàn)椤鞍病钡钠匆羰莂n, 字典的正文部分本身就是一個(gè)目錄，您不需要再去查其他目錄來(lái)找到您需要找的內(nèi)容。
我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。
2.非聚集索引
如果您認(rèn)識(shí)某個(gè)字，您可以快速地從自動(dòng)中查到這個(gè)字。但您也可能會(huì)遇到您不認(rèn)識(shí)的字，不知道它的發(fā)音，這時(shí)候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個(gè)字后的頁(yè)碼直接翻到某頁(yè)來(lái)找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁(yè)碼是672頁(yè)，檢字表中“張”的上面是“馳”字，但頁(yè)碼卻是63頁(yè)，“張”的下面是“弩”字，頁(yè)面是390頁(yè)。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實(shí)際上就是他們?cè)诜蔷奂饕械呐判颍亲值湔闹械淖衷诜蔷奂饕械挠成洹Ｎ覀兛梢酝ㄟ^(guò)這種方式來(lái)找到您所需要的字，但它需要兩個(gè)過(guò)程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁(yè)碼。
我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。
非聚集索引可以建多個(gè)，具有B樹(shù)結(jié)構(gòu)，其葉級(jí)節(jié)點(diǎn)不包含數(shù)據(jù)頁(yè)，只包含索引行。
聚集索引中，數(shù)據(jù)所在的數(shù)據(jù)頁(yè)是葉級(jí)，索引數(shù)據(jù)所在的索引頁(yè)是非葉級(jí)。聚集索引在任何一種數(shù)據(jù)表中只能建立一個(gè)；并且建立聚集索引需要至少相當(dāng)于源表120%的附加空間，以存放源表的副本和索引中間頁(yè)。
在只建立了非聚集索引的情況下，每個(gè)葉級(jí)節(jié)點(diǎn)指明了記錄的行定位符（RID）；而在既有聚集索引又有非聚集索引的情況下，每個(gè)葉級(jí)節(jié)點(diǎn)所指向的是該聚集索引的索引鍵值，即數(shù)據(jù)記錄本身。
當(dāng)數(shù)據(jù)發(fā)生更新的時(shí)候，SQLS只負(fù)責(zé)對(duì)聚集索引的鍵值加以維護(hù)，而不必考慮非聚集索引。只要我們?cè)贗D類的字段上建立聚集索引，而在其它經(jīng)常需要查詢的字段上建立非聚集索引，通過(guò)這種科學(xué)的、有針對(duì)性的在一張表上分別建立聚集索引和非聚集索引的方法，我們既享受了索引帶來(lái)的靈活與快捷，又相對(duì)避免了維護(hù)索引所導(dǎo)致的大量的額外資源消耗。
索引有一些先天不足：
1、系統(tǒng)要占用大約為表的1.2倍的硬盤(pán)和內(nèi)存空間來(lái)保存索引；
2、更新數(shù)據(jù)的時(shí)候，系統(tǒng)必須要有額外的時(shí)間來(lái)同時(shí)對(duì)索引進(jìn)行更新，以維持?jǐn)?shù)據(jù)和索引的一致性。
在如下字段建立索引應(yīng)該是不恰當(dāng)?shù)模?
1、很少或從不引用的字段；
2、邏輯型的字段，如男或女(是或否)等。
clustered和nonclustered是ms sql里面的概念；mysql不太一樣。
When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create.
If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table.
Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index records.)
If you are using the 'MyISAM' engine, no index (not even PRIMARY KEY) is 'clustered'. If you are using InnoDB, the PRIMARY KEY is always clustered, no choice. Secondary indexes are never clustered, no choice.
1
mysql
>
show
create
table
xxa;
2
+
--
-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3
|
Table
|
Create
Table
|
4
+
--
-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5
|
xxa
|
CREATE
TABLE
`xxa` (
6
`id`
int
(
11
)
NOT
NULL
AUTO_INCREMENT,
7
`name`
varchar
(
20
)
DEFAULT
NULL
,
8
PRIMARY
KEY
(`id`)
9
) ENGINE
=
MyISAM AUTO_INCREMENT
=
6
DEFAULT
CHARSET
=
latin1
|
10
+
--
-----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11
1
row
in
set
(
0.00
sec)
Since the data is part of the index that sorted and deliberately fragmented, it is obvious that only one cluster key can be used for a single table. Also, there is one more important consequence, namely they are the write operations, and especially changing the key fields in existing data. It is extremely resource-intensive process. Try to use rarely changed fields for the clustered indexes.
1
mysql
>
create
index
name_index
on
xxa(name);
2
Query OK,
5
rows affected (
0.01
sec)
3
Records:
5
Duplicates:
0
Warnings:
0
4
5
mysql
>
drop
index
name_index;
6
ERROR
1064
(
42000
): You have an error
in
your SQL syntax;
check
the manual that corresponds
to
your MySQL server version
for
the
right
syntax
to
use
near
''
at line
1
7
mysql
>
drop
index
name_index
on
xxa;
8
Query OK,
5
rows affected (
0.01
sec)
9
Records:
5
Duplicates:
0
Warnings:
0
視圖
1
mysql
>
create
or
replace
view
v
as
select
name
from
xxa;
2
Query OK,
0
rows affected (
0.00
sec)
3
4
mysql
>
select
*
from
v;
5
+
--
----+
6
|
name
|
7
+
--
----+
8
|
xxj
|
9
|
xxa
|
10
|
xxb
|
11
|
xxc
|
12
|
xxd
|
13
+
--
----+
14
5
rows
in
set
(
0.01
sec)
使用視圖的理由是什么？
1.安全性。一般是這樣做的:創(chuàng)建一個(gè)視圖，定義好該視圖所操作的數(shù)據(jù)。之后將用戶權(quán)限與視圖綁定。這樣的方式是使用到了一個(gè)特性：grant語(yǔ)句可以針對(duì)視圖進(jìn)行授予權(quán)限。
2.查詢性能提高。
3.有靈活性的功能需求后，需要改動(dòng)表的結(jié)構(gòu)而導(dǎo)致工作量比較大。那么可以使用虛擬表的形式達(dá)到少修改的效果。這是在實(shí)際開(kāi)發(fā)中比較有用的。
例子：假如因?yàn)槟撤N需要，a表與b表需要進(jìn)行合并起來(lái)組成一個(gè)新的表c。最后a表與b表都不會(huì)存在了。而由于原來(lái)程序中編寫(xiě)sql分別是基于a表與b表查詢的，這就意味著需要重新編寫(xiě)大量的sql(改成向c表去操作數(shù)據(jù))。而通過(guò)視圖就可以做到不修改。定義兩個(gè)視圖名字還是原來(lái)的表名a和b。a、b視圖完成從c表中取出內(nèi)容。
說(shuō)明：使用這樣的解決方式，基于對(duì)視圖的細(xì)節(jié)了解越詳細(xì)越好。因?yàn)槭褂靡晥D還是與使用表的語(yǔ)法上沒(méi)區(qū)別。比如視圖名a，那么查詢還是"select * from a"。
4.復(fù)雜的查詢需求。可以進(jìn)行問(wèn)題分解，然后將創(chuàng)建多個(gè)視圖獲取數(shù)據(jù)。將視圖聯(lián)合起來(lái)就能得到需要的結(jié)果了。
視圖的工作機(jī)制:當(dāng)調(diào)用視圖的時(shí)候，才會(huì)執(zhí)行視圖中的sql，進(jìn)行取數(shù)據(jù)操作。視圖的內(nèi)容沒(méi)有存儲(chǔ)，而是在視圖被引用的時(shí)候才派生出數(shù)據(jù)。這樣不會(huì)占用空間，由于是即時(shí)引用，視圖的內(nèi)容總是與真實(shí)表的內(nèi)容是一致的。
視圖這樣設(shè)計(jì)有什么好處？節(jié)省空間，內(nèi)容是總是一致的話，那么我們不需要維護(hù)視圖的內(nèi)容，維護(hù)好真實(shí)表的內(nèi)容，就可以保證視圖的完整性了。
對(duì)于可更新視圖，可給定WITH CHECK OPTION子句來(lái)防止插入或更新行，除非作用在行上的select_statement中的WHERE子句為“真”。
視圖也是一種表，是虛擬表。不能與已有的表(視圖)出現(xiàn)重名。
1
mysql
>
update
xxa
set
name
=
'
xxe
'
;
2
Query OK,
5
rows affected (
0.00
sec)
3
Rows matched:
5
Changed:
5
Warnings:
0
4
5
mysql
>
select
*
from
v;
6
+
--
----+
7
|
name
|
8
+
--
----+
9
|
xxe
|
10
|
xxe
|
11
|
xxe
|
12
|
xxe
|
13
|
xxe
|
14
+
--
----+
15
5
rows
in
set
(
0.00
sec)
16
17
mysql
>
select
*
from
xxa;
18
+
--
--+------+
19
|
id
|
name
|
20
+
--
--+------+
21
|
1
|
xxe
|
22
|
2
|
xxe
|
23
|
3
|
xxe
|
24
|
4
|
xxe
|
25
|
5
|
xxe
|
26
+
--
--+------+
27
5
rows
in
set
(
0.00
sec)
28
29
mysql
>
update
v
set
name
=
'
xxf
'
;
30
Query OK,
5
rows affected (
0.00
sec)
31
Rows matched:
5
Changed:
5
Warnings:
0
32
33
mysql
>
select
*
from
xxa;
34
+
--
--+------+
35
|
id
|
name
|
36
+
--
--+------+
37
|
1
|
xxf
|
38
|
2
|
xxf
|
39
|
3
|
xxf
|
40
|
4
|
xxf
|
41
|
5
|
xxf
|
42
+
--
--+------+
43
5
rows
in
set
(
0.00
sec)
Storage Engine
數(shù)據(jù)的大小，是一個(gè)影響你選擇什么樣存儲(chǔ)引擎的重要因素，大尺寸的數(shù)據(jù)集趨向于選擇InnoDB方式，因?yàn)槠渲С质聞?wù)處理和故障恢復(fù)。數(shù)據(jù)庫(kù)的大小決定了故障恢復(fù)的時(shí)間長(zhǎng)短，InnoDB可以利用事務(wù)日志進(jìn)行數(shù)據(jù)恢復(fù)，這會(huì)比較快。而MyISAM可能會(huì)需要幾個(gè)小時(shí)甚至幾天來(lái)干這些事，InnoDB只需要幾分鐘。
您操作數(shù)據(jù)庫(kù)表的習(xí)慣可能也會(huì)是一個(gè)對(duì)性能影響很大的因素。比如： COUNT() 在 MyISAM 表中會(huì)非常快，而在InnoDB 表下可能會(huì)很痛苦。而主鍵查詢則在InnoDB下會(huì)相當(dāng)相當(dāng)?shù)目欤枰⌒牡氖侨绻覀兊闹麈I太長(zhǎng)了也會(huì)導(dǎo)致性能問(wèn)題。大批的inserts 語(yǔ)句在MyISAM下會(huì)快一些，但是updates 在InnoDB 下會(huì)更快一些——尤其在并發(fā)量大的時(shí)候。
如果是一些小型的應(yīng)用或項(xiàng)目，那么MyISAM 也許會(huì)更適合。當(dāng)然，在大型的環(huán)境下使用MyISAM 也會(huì)有很大成功的時(shí)候，但卻不總是這樣的。如果你正在計(jì)劃使用一個(gè)超大數(shù)據(jù)量的項(xiàng)目，而且需要事務(wù)處理或外鍵支持，那么你真的應(yīng)該直接使用InnoDB方式。但需要記住InnoDB 的表需要更多的內(nèi)存和存儲(chǔ)，轉(zhuǎn)換100GB 的MyISAM 表到InnoDB 表可能會(huì)讓你有非常壞的體驗(yàn)。
MyISAM類型不支持事務(wù)處理等高級(jí)處理，而InnoDB類型支持。MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行速度比InnoDB類型更快，但是不提供事務(wù)支持，而InnoDB提供事務(wù)支持已經(jīng)外部鍵等高級(jí)數(shù)據(jù)庫(kù)功能。
以下是一些細(xì)節(jié)和具體實(shí)現(xiàn)的差別：
◆1.InnoDB不支持FULLTEXT類型的索引。
◆2.InnoDB 中不保存表的具體行數(shù)，也就是說(shuō)，執(zhí)行select count(*) from table時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count(*)語(yǔ)句包含 where條件時(shí)，兩種表的操作是一樣的。
◆3.對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
◆4.DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。
◆5.LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性(例如外鍵)的表不適用。
另外，InnoDB表的行鎖也不是絕對(duì)的，假如在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表，例如update table set num=1 where name like “%aaa%”
兩種類型最主要的差別就是Innodb 支持事務(wù)處理與外鍵和行級(jí)鎖.而MyISAM不支持.所以MyISAM往往就容易被人認(rèn)為只適合在小項(xiàng)目中使用。
我作為使用MySQL的用戶角度出發(fā)，Innodb和MyISAM都是比較喜歡的，但是從我目前運(yùn)維的數(shù)據(jù)庫(kù)平臺(tái)要達(dá)到需求：99.9%的穩(wěn)定性，方便的擴(kuò)展性和高可用性來(lái)說(shuō)的話，MyISAM絕對(duì)是我的首選。
原因如下：
1、首先我目前平臺(tái)上承載的大部分項(xiàng)目是讀多寫(xiě)少的項(xiàng)目，而MyISAM的讀性能是比Innodb強(qiáng)不少的。
2、MyISAM的索引和數(shù)據(jù)是分開(kāi)的，并且索引是有壓縮的，內(nèi)存使用率就對(duì)應(yīng)提高了不少。能加載更多索引，而Innodb是索引和數(shù)據(jù)是緊密捆綁的，沒(méi)有使用壓縮從而會(huì)造成Innodb比MyISAM體積龐大不小。
3、從平臺(tái)角度來(lái)說(shuō)，經(jīng)常隔1，2個(gè)月就會(huì)發(fā)生應(yīng)用開(kāi)發(fā)人員不小心update一個(gè)表where寫(xiě)的范圍不對(duì)，導(dǎo)致這個(gè)表沒(méi)法正常用了，這個(gè)時(shí)候MyISAM的優(yōu)越性就體現(xiàn)出來(lái)了，隨便從當(dāng)天拷貝的壓縮包取出對(duì)應(yīng)表的文件，隨便放到一個(gè)數(shù)據(jù)庫(kù)目錄下，然后dump成sql再導(dǎo)回到主庫(kù)，并把對(duì)應(yīng)的binlog補(bǔ)上。如果是Innodb，恐怕不可能有這么快速度，別和我說(shuō)讓Innodb定期用導(dǎo)出xxx.sql機(jī)制備份，因?yàn)槲移脚_(tái)上最小的一個(gè)數(shù)據(jù)庫(kù)實(shí)例的數(shù)據(jù)量基本都是幾十G大小。
4、從我接觸的應(yīng)用邏輯來(lái)說(shuō)，select count(*) 和order by 是最頻繁的，大概能占了整個(gè)sql總語(yǔ)句的60%以上的操作，而這種操作Innodb其實(shí)也是會(huì)鎖表的，很多人以為Innodb是行級(jí)鎖，那個(gè)只是where對(duì)它主鍵是有效，非主鍵的都會(huì)鎖全表的。
5、還有就是經(jīng)常有很多應(yīng)用部門(mén)需要我給他們定期某些表的數(shù)據(jù)，MyISAM的話很方便，只要發(fā)給他們對(duì)應(yīng)那表的frm.MYD,MYI的文件，讓他們自己在對(duì)應(yīng)版本的數(shù)據(jù)庫(kù)啟動(dòng)就行，而Innodb就需要導(dǎo)出xxx.sql了，因?yàn)楣饨o別人文件，受字典數(shù)據(jù)文件的影響，對(duì)方是無(wú)法使用的。
6、如果和MyISAM比insert寫(xiě)操作的話，Innodb還達(dá)不到MyISAM的寫(xiě)性能，如果是針對(duì)基于索引的update操作，雖然MyISAM可能會(huì)遜色I(xiàn)nnodb,但是那么高并發(fā)的寫(xiě)，從庫(kù)能否追的上也是一個(gè)問(wèn)題，還不如通過(guò)多實(shí)例分庫(kù)分表架構(gòu)來(lái)解決。
7、如果是用MyISAM的話，merge引擎可以大大加快應(yīng)用部門(mén)的開(kāi)發(fā)速度，他們只要對(duì)這個(gè)merge表做一些select count(*)操作，非常適合大項(xiàng)目總量約幾億的rows某一類型(如日志，調(diào)查統(tǒng)計(jì))的業(yè)務(wù)表。
當(dāng)然Innodb也不是絕對(duì)不用，用事務(wù)的項(xiàng)目如模擬炒股項(xiàng)目，我就是用Innodb的，活躍用戶20多萬(wàn)時(shí)候，也是很輕松應(yīng)付了，因此我個(gè)人也是很喜歡Innodb的，只是如果從數(shù)據(jù)庫(kù)平臺(tái)應(yīng)用出發(fā)，我還是會(huì)首選MyISAM。
另外，可能有人會(huì)說(shuō)你MyISAM無(wú)法抗太多寫(xiě)操作，但是我可以通過(guò)架構(gòu)來(lái)彌補(bǔ)，說(shuō)個(gè)我現(xiàn)有用的數(shù)據(jù)庫(kù)平臺(tái)容量：主從數(shù)據(jù)總量在幾百T以上，每天十多億 pv的動(dòng)態(tài)頁(yè)面，還有幾個(gè)大項(xiàng)目是通過(guò)數(shù)據(jù)接口方式調(diào)用未算進(jìn)pv總數(shù)，(其中包括一個(gè)大項(xiàng)目因?yàn)槌跗趍emcached沒(méi)部署,導(dǎo)致單臺(tái)數(shù)據(jù)庫(kù)每天處理 9千萬(wàn)的查詢)。而我的整體數(shù)據(jù)庫(kù)服務(wù)器平均負(fù)載都在0.5-1左右。
這一部分節(jié)選自：http://www.php100.com/html/webkaifa/database/Mysql/2011/0326/7789.html
建立用戶
1
mysql
>
insert
into
user
(Host,
User
, Password)
values
("localhost", "xxj", password("
123456
"));
2
mysql
>
flush
privileges
;
不同用戶創(chuàng)建的庫(kù)默認(rèn)是不能相互看到的。要給權(quán)限；
1
mysql
>
grant
select
on
xxj.
*
to
xxj@
'
localhost
'
identified
by
'
123456
'
with
grant
option
;
2
Query OK,
0
rows affected (
0.00
sec)
1
mysql
>
show grants;
2
+
--
----------------------------------------------------------------------------------------------------------+
3
|
Grants
for
xxj
@localhost
|
4
+
--
----------------------------------------------------------------------------------------------------------+
5
|
GRANT
USAGE
ON
*
.
*
TO
'
xxj
'
@
'
localhost
'
IDENTIFIED
BY
PASSWORD
'
*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
'
|
6
|
GRANT
SELECT
ON
`xxj`.
*
TO
'
xxj
'
@
'
localhost
'
WITH
GRANT
OPTION
|
7
+
--
----------------------------------------------------------------------------------------------------------+
8
2
rows
in
set
(
0.00
sec)
刪除權(quán)限：
mysql
>
revoke
select
on
xxj.
*
from
'
xxj
'
@
'
localhost
'
;
Query OK,
0
rows affected (
0.00
sec)
刪除權(quán)限后再執(zhí)行的錯(cuò)誤是沒(méi)有權(quán)限，而不是表不存在；
1
mysql
>
select
*
from
test;
2
ERROR
1142
(
42000
):
SELECT
command denied
to
user
'
xxj
'
@
'
localhost
'
for
table
'
test
'
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
