Basic of MySQL
創建數據庫:
1
mysql
>
create
database
xxj;
2
Query OK,
1
row affected (
0.00
sec)
列舉數據庫:
1
mysql
>
show databases;
2
+
--
------------------+
3
|
Database
|
4
+
--
------------------+
5
|
information_schema
|
6
|
mysql
|
7
|
xxj
|
8
+
--
------------------+
進入某個數據庫：
1
mysql
>
use
xxj;
2
Database
changed
創建表：
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)
設置了主鍵，默認值，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 排序等。
高級一點
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 有兩個參數，第一個是偏移量（從0開始），第二個是返回結果數目。
limit n相當于limit 0, n。
存儲過程：
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)
這里一開始要用delimiter $修改一下結束符為$，這樣才能在終端寫存儲過程，創建好存儲過程之后再改回來就行。
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)
使用存儲過程的好處：
-
減少了服務器/客戶端網絡流量
- 過程中的命令作為代碼的單個批處理執行。 這可以顯著減少服務器和客戶端之間的網絡流量，因為只有對執行過程的調用才會跨網絡發送。 如果沒有過程提供的代碼封裝，每個單獨的代碼行都不得不跨網絡發送。
-
更強的安全性
- 多個用戶和客戶端程序可以通過過程對基礎數據庫對象執行操作，即使用戶和程序對這些基礎對象沒有直接權限。 過程控制執行哪些進程和活動，并且保護基礎數據庫對象。 這消除在了單獨的對象級別授予權限的要求，并且簡化了安全層。
-
代碼的重復使用
- 任何重復的數據庫操作的代碼都非常適合于在過程中進行封裝。 這消除了不必要地重復編寫相同的代碼、降低了代碼不一致性，并且允許擁有所需權限的任何用戶或應用程序訪問和執行代碼。
-
更容易維護
- 在客戶端應用程序調用過程并且將數據庫操作保持在數據層中時，對于基礎數據庫中的任何更改，只有過程是必須更新的。
- 應用程序層保持獨立，并且不必知道對數據庫布局、關系或進程的任何更改的情況。
-
改進的性能
- 默認情況下，在首次執行過程時將編譯過程，并且創建一個執行計劃，供以后的執行重復使用。 因為查詢處理器不必創建新計劃，所以，它通常用更少的時間來處理過程。
- 如果過程引用的表或數據有顯著變化，則預編譯的計劃可能實際上會導致過程的執行速度減慢。 在此情況下，重新編譯過程和強制新的執行計劃可提高性能。
查看當前狀態
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
變量可以通過配置文件my.conf來修改。當連接請求大于默認連接數后，就會出現無法連接數據庫的錯誤。
索引
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樹索引可以用于查找、排序、求最大最小值、范圍查詢等。
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實際是用B+樹（myisam和innodb引擎都是）。估計官網所說的B-tree，其實指的是B+樹吧，畢竟B+樹也是B樹的一個變種。?
為了進行高效查詢，可以在數據表上針對某一字段建立索引，由于該索引包括了一個指向數據的指針，數據庫服務器則只沿著索引排列的順序對僅有一列數據的索引進行讀取（只建立一個索引）直至索引指針指向相應的記錄上為止。由于索引只是按照一個字段進行查找，而沒有對整表進行遍歷，因此一般說來索引查找比全表掃描的速度快。
那么，是不是使用索引查詢一定比全表掃描的速度快呢？答案是否定的。如果查詢小型數據表（記錄很少）或是查詢大型數據表（記錄很多）的絕大部分數據，全表掃描更為實用。例如，查詢“性別”字段，其值只能是“男或女”，在其上建立索引的意義就不大，甚至不允許在布爾型、大二進制型（備注型、圖像型等）上建立索引。
1.聚集索引
其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是an, 字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容。
我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為“聚集索引”。
2.非聚集索引
如果您認識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然后根據這個字后的頁碼直接翻到某頁來找到您要找的字。但您結合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現在您看到的連續的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然后再翻到您所需要的頁碼。
我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。
非聚集索引可以建多個，具有B樹結構，其葉級節點不包含數據頁，只包含索引行。
聚集索引中，數據所在的數據頁是葉級，索引數據所在的索引頁是非葉級。聚集索引在任何一種數據表中只能建立一個；并且建立聚集索引需要至少相當于源表120%的附加空間，以存放源表的副本和索引中間頁。
在只建立了非聚集索引的情況下，每個葉級節點指明了記錄的行定位符（RID）；而在既有聚集索引又有非聚集索引的情況下，每個葉級節點所指向的是該聚集索引的索引鍵值，即數據記錄本身。
當數據發生更新的時候，SQLS只負責對聚集索引的鍵值加以維護，而不必考慮非聚集索引。只要我們在ID類的字段上建立聚集索引，而在其它經常需要查詢的字段上建立非聚集索引，通過這種科學的、有針對性的在一張表上分別建立聚集索引和非聚集索引的方法，我們既享受了索引帶來的靈活與快捷，又相對避免了維護索引所導致的大量的額外資源消耗。
索引有一些先天不足：
1、系統要占用大約為表的1.2倍的硬盤和內存空間來保存索引；
2、更新數據的時候，系統必須要有額外的時間來同時對索引進行更新，以維持數據和索引的一致性。
在如下字段建立索引應該是不恰當的：
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.安全性。一般是這樣做的:創建一個視圖，定義好該視圖所操作的數據。之后將用戶權限與視圖綁定。這樣的方式是使用到了一個特性：grant語句可以針對視圖進行授予權限。
2.查詢性能提高。
3.有靈活性的功能需求后，需要改動表的結構而導致工作量比較大。那么可以使用虛擬表的形式達到少修改的效果。這是在實際開發中比較有用的。
例子：假如因為某種需要，a表與b表需要進行合并起來組成一個新的表c。最后a表與b表都不會存在了。而由于原來程序中編寫sql分別是基于a表與b表查詢的，這就意味著需要重新編寫大量的sql(改成向c表去操作數據)。而通過視圖就可以做到不修改。定義兩個視圖名字還是原來的表名a和b。a、b視圖完成從c表中取出內容。
說明：使用這樣的解決方式，基于對視圖的細節了解越詳細越好。因為使用視圖還是與使用表的語法上沒區別。比如視圖名a，那么查詢還是"select * from a"。
4.復雜的查詢需求。可以進行問題分解，然后將創建多個視圖獲取數據。將視圖聯合起來就能得到需要的結果了。
視圖的工作機制:當調用視圖的時候，才會執行視圖中的sql，進行取數據操作。視圖的內容沒有存儲，而是在視圖被引用的時候才派生出數據。這樣不會占用空間，由于是即時引用，視圖的內容總是與真實表的內容是一致的。
視圖這樣設計有什么好處？節省空間，內容是總是一致的話，那么我們不需要維護視圖的內容，維護好真實表的內容，就可以保證視圖的完整性了。
對于可更新視圖，可給定WITH CHECK OPTION子句來防止插入或更新行，除非作用在行上的select_statement中的WHERE子句為“真”。
視圖也是一種表，是虛擬表。不能與已有的表(視圖)出現重名。
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
數據的大小，是一個影響你選擇什么樣存儲引擎的重要因素，大尺寸的數據集趨向于選擇InnoDB方式，因為其支持事務處理和故障恢復。數據庫的大小決定了故障恢復的時間長短，InnoDB可以利用事務日志進行數據恢復，這會比較快。而MyISAM可能會需要幾個小時甚至幾天來干這些事，InnoDB只需要幾分鐘。
您操作數據庫表的習慣可能也會是一個對性能影響很大的因素。比如： COUNT() 在 MyISAM 表中會非常快，而在InnoDB 表下可能會很痛苦。而主鍵查詢則在InnoDB下會相當相當的快，但需要小心的是如果我們的主鍵太長了也會導致性能問題。大批的inserts 語句在MyISAM下會快一些，但是updates 在InnoDB 下會更快一些——尤其在并發量大的時候。
如果是一些小型的應用或項目，那么MyISAM 也許會更適合。當然，在大型的環境下使用MyISAM 也會有很大成功的時候，但卻不總是這樣的。如果你正在計劃使用一個超大數據量的項目，而且需要事務處理或外鍵支持，那么你真的應該直接使用InnoDB方式。但需要記住InnoDB 的表需要更多的內存和存儲，轉換100GB 的MyISAM 表到InnoDB 表可能會讓你有非常壞的體驗。
MyISAM類型不支持事務處理等高級處理，而InnoDB類型支持。MyISAM類型的表強調的是性能，其執行速度比InnoDB類型更快，但是不提供事務支持，而InnoDB提供事務支持已經外部鍵等高級數據庫功能。
以下是一些細節和具體實現的差別：
◆1.InnoDB不支持FULLTEXT類型的索引。
◆2.InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM只要簡單的讀出保存好的行數即可。注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的。
◆3.對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引。
◆4.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。
◆5.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對于使用的額外的InnoDB特性(例如外鍵)的表不適用。
另外，InnoDB表的行鎖也不是絕對的，假如在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表，例如update table set num=1 where name like “%aaa%”
兩種類型最主要的差別就是Innodb 支持事務處理與外鍵和行級鎖.而MyISAM不支持.所以MyISAM往往就容易被人認為只適合在小項目中使用。
我作為使用MySQL的用戶角度出發，Innodb和MyISAM都是比較喜歡的，但是從我目前運維的數據庫平臺要達到需求：99.9%的穩定性，方便的擴展性和高可用性來說的話，MyISAM絕對是我的首選。
原因如下：
1、首先我目前平臺上承載的大部分項目是讀多寫少的項目，而MyISAM的讀性能是比Innodb強不少的。
2、MyISAM的索引和數據是分開的，并且索引是有壓縮的，內存使用率就對應提高了不少。能加載更多索引，而Innodb是索引和數據是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。
3、從平臺角度來說，經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的范圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的文件，隨便放到一個數據庫目錄下，然后dump成sql再導回到主庫，并把對應的binlog補上。如果是Innodb，恐怕不可能有這么快速度，別和我說讓Innodb定期用導出xxx.sql機制備份，因為我平臺上最小的一個數據庫實例的數據量基本都是幾十G大小。
4、從我接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能占了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。
5、還有就是經常有很多應用部門需要我給他們定期某些表的數據，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的文件，讓他們自己在對應版本的數據庫啟動就行，而Innodb就需要導出xxx.sql了，因為光給別人文件，受字典數據文件的影響，對方是無法使用的。
6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫性能，如果是針對基于索引的update操作，雖然MyISAM可能會遜色Innodb,但是那么高并發的寫，從庫能否追的上也是一個問題，還不如通過多實例分庫分表架構來解決。
7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些select count(*)操作，非常適合大項目總量約幾億的rows某一類型(如日志，調查統計)的業務表。
當然Innodb也不是絕對不用，用事務的項目如模擬炒股項目，我就是用Innodb的，活躍用戶20多萬時候，也是很輕松應付了，因此我個人也是很喜歡Innodb的，只是如果從數據庫平臺應用出發，我還是會首選MyISAM。
另外，可能有人會說你MyISAM無法抗太多寫操作，但是我可以通過架構來彌補，說個我現有用的數據庫平臺容量：主從數據總量在幾百T以上，每天十多億 pv的動態頁面，還有幾個大項目是通過數據接口方式調用未算進pv總數，(其中包括一個大項目因為初期memcached沒部署,導致單臺數據庫每天處理 9千萬的查詢)。而我的整體數據庫服務器平均負載都在0.5-1左右。
這一部分節選自：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
;
不同用戶創建的庫默認是不能相互看到的。要給權限；
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)
刪除權限：
mysql
>
revoke
select
on
xxj.
*
from
'
xxj
'
@
'
localhost
'
;
Query OK,
0
rows affected (
0.00
sec)
刪除權限后再執行的錯誤是沒有權限，而不是表不存在；
1
mysql
>
select
*
from
test;
2
ERROR
1142
(
42000
):
SELECT
command denied
to
user
'
xxj
'
@
'
localhost
'
for
table
'
test
'
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
