一. mysql框架和基本介紹
1. 框架圖

更詳細(xì)：

2. 存儲引擎
MYISAM與INNODB對比：
MYISAM：mysql5.1及以前版本的默認(rèn)存儲引擎。支持全文檢索，壓縮，表級鎖等，但不支持事務(wù)，行級鎖，崩潰后的數(shù)據(jù)恢復(fù)等
INNODB：mysql5.5及之后的默認(rèn)存儲引擎。支持事務(wù)，行級鎖，數(shù)據(jù)恢復(fù)，mysql5.6 中的innodb(1.2)支持全文檢索。

如何選擇：innodb對于絕大多數(shù)的用戶都是最佳的選擇，除非某些存儲引擎能滿足特殊需求且使用者很了解這種存儲引擎。

二.?查詢性能的方法
1. 配置文件 my.cnf (linux),?
讀配置文件的一般順序：
? ? ?1) ? ? /etc/my.cnf
? ? ?2) ? ? ?DATADIR/my.cnf
? ? ?3) ? ? ?~/.my.cnf
可以通過命令獲知：
mysqld --verbose --help | grep -A 1 'Default options’
結(jié)果：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

2. 環(huán)境變量
從my,cnf讀取環(huán)境變量值，
查看變量值方法： show variables like?“”, (不清楚變量名時，用模糊匹配%)
例如

設(shè)置(全局)變量方法：
set (global) 變量名=變量值
例如設(shè)置 開啟慢查詢?nèi)罩?br /> set global slow_query_log=1;
設(shè)置后查看變量值
show variables like "%slow%”;

| slow_query_log??????????? | ON?

變量列表：http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html

分析sql語句時一些有用且默認(rèn)不開啟的變量：
1） 慢查詢?nèi)罩? 開關(guān) slow_query_log, 閾值：long_query_time (單位 秒)
2） sql剖析工具profile，開關(guān)：profiling，歷史記錄數(shù)：profiling_history_size(最大為100)
3) ? 記錄每條sql語句，開關(guān)：general_log, log文件位置：general_log_file
…?…

3. 查看sql服務(wù)狀態(tài)
語法?SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]清除狀態(tài) FLUSH STATUS;
清除表緩存?
reset query cache ;
FLUSH TABLE [TABLE NAME]
? ??
用法舉例
FLUSH STATUS；
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler_read%’; ?（SHOW SESSION STATUS LIKE 'Handler_%’;）
EXPLAIN SELECT?…;

具體參數(shù)可以參考：
http://lxneng.iteye.com/blog/451985
http://hi.baidu.com/thinkinginlamp/item/8d038333c6b0674a3075a1d3

4.?查詢剖析工具?show profiles
開啟?
set profiling=1;
set profiling_history_size=50 ?最大為100

用法：
show profiles;
show profile; //展示最后一條query時間消耗
show profile for query ID； // 替換ID值 從show profiles 表里。
顯示更多內(nèi)容
show profile cpu,block io for query 4;

5. 獲取查詢計劃的信息 explain?
用法 explain ?[sql查詢語句]
例如：

注意每列的含義

三.?數(shù)據(jù)類型優(yōu)化
1. 選擇優(yōu)化的數(shù)據(jù)類型原則
? ? ?1）盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型。例如： 只存0-200，用tinyint unsigned 更好。
? ? ?2）選擇簡單的數(shù)據(jù)類型。例如整形比字符操作代價更低，存儲時間用datetime而不是字符串，用整形存儲ip。

2. 數(shù)據(jù)類型（主要說整形和字符串）
? ? ?1）整型?
TINYINT 1 字節(jié) (-128，127) (0，255) 小整數(shù)值?
SMALLINT 2 字節(jié) (-32 768，32 767) (0，65 535) 大整數(shù)值?
MEDIUMINT 3 字節(jié) (-8 388 608，8 388 607) (0，16 777 215) 大整數(shù)值?
INT或INTEGER 4 字節(jié) (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整數(shù)值?
BIGINT 8 字節(jié) (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 極大整數(shù)值?

思考：tinyint(1)和tinyint(2)存儲空間相比較？

? ? ?2）字符串
varchar:內(nèi)容+長度，0-255字節(jié)。
char:長度不足時，空格補(bǔ)足，0-255字節(jié)。

如何選擇：
選擇用varchar：字符串的最大長度比平均長度大很多，列更新少。但是一定要估算好長度（排序時）。

text和blob，大數(shù)據(jù)類型。

? ? ? 3）浮點，時間，位 等

四. 高性能索引
1. 索引基礎(chǔ)
索引結(jié)果為B+樹。

B樹：
? ? ? ?

B-樹：


B+樹：


MYISAM 主索引（輔助索引結(jié)構(gòu)與其相同）：

INNODB 主索引：

INNODB輔助索引：


索引優(yōu)點：
? ? ?1） 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量；
? ? ?2） 索引可以幫助服務(wù)器避免排序和臨時表；
? ? ?3） 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O。

2. 高性能索引策略

1） 獨立的列
? ? ?將索引單獨放到比較符號的一側(cè)，否則無法利用索引。

2）前綴索引和索引選擇性

3）合適的索引順序

4）覆蓋索引
極大的提高性能。

5）使用索引掃描做排序

其他策略:
1）多條件過濾，盡量重復(fù)利用索引，(sex,country,age)有索引，現(xiàn)在有查詢條件 sex，country，region，age 或者sex,country,region,city,age需要再建索引嗎？
例如：(gender,name) ?gender,gender name
select * from staff where name like?“123”；
select * from staff where gender in (0,1) and name like?“123"；
2） 避免多個范圍查詢
3）延遲關(guān)聯(lián)
select *? from table2 order by cnt,id limit 100000,10;

select *? from table2 join (select id from table2 order by cnt limit 100000,10) as x using(id) ;

五. 查詢性能優(yōu)化
1） 查詢執(zhí)行的基礎(chǔ)

2） 查詢優(yōu)化器的局限性
union限制
(select first_name,last_name from actor order by last_name) union (select first_name,last_name from customer order by last_name) order by last_name limit 20;

(select first_name,last_name from actor order by last_name limit 20) union (select first_name,last_name? from customer order by last_name limit 20) order by last_name limit 20 ;

在同一個表上查詢和更新
update foo as outer set cnt= (select count(*) from foo as inner where inner.type=outer.type);

update foo join (select type,count(*) as cnt from foo group by type) as der using(type) set foo.cnt=der.cnt;

3）優(yōu)化特定關(guān)聯(lián)查詢
優(yōu)化關(guān)聯(lián)：
explain select * from film inner join (film_actor,actor) on (film.film_id = film_actor.film_id and film_actor.actor_id = actor.actor_id); explain select straight_join * from film inner join (film_actor,actor) on (film.film_id = film_actor.film_id and film_actor.actor_id = actor.actor_id);

優(yōu)化limit
優(yōu)化sql_calc_found_rows
?select sql_calc_found_rows *? from table2 order by cnt,id limit 100000,10;
?select found_rows();

selelt count(*) from table ..

轉(zhuǎn)載于:https://www.cnblogs.com/yunlong/p/3901383.html

總結(jié)

以上是生活随笔為你收集整理的mysql基本介绍和优化技巧的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。