MySQL性能優化問題？

一．Mysql性能優化之-影響性能因素

1.商業需求的影響

2.系統架構及實現的影響

①二級制多媒體數據

②超大文本數據

3.查詢語句對性能的影響

在數據管理軟體中最大的瓶頸就是磁碟的IO，也就是在磁碟存儲的操作上，當對於同一份數據，我們以不同的方式去尋找某一點的內容是時，所讀取得數據量可能有天壤之別，所消耗的資源區別也是非常多的大的。

功能相同的兩條SQL的在性能方面的差異。

我們在執行SQL語句時可以用explain來查看執行的計劃。

mysql>explain

->select stuid,stuname,carid from test1.tb1 where stuid between 3000 and 5000

->order by stuid desc

->limit 20 G

還可以打開mysql的profiling功能，查看SQL語句的實際執行計劃。

mysql>set profiling=1;

->select stuid,stuname,carid from test1.tb1 where stuid between 3000 and 5000 order by stuid desc limit 5 G;

mysql>show profile;

1. 資料庫schema設計對性能的影響；
2. 硬體選擇對性能的影響；
3. Mysql性能優化之-索引

對於大型網站來說，單日就能產生幾十萬幾百萬的數據，沒有索引的查詢會變得非常緩慢。。。。。。。

做一個簡單的測試，我們創建一個tb1表，向表裡插入20000條數據。

[root@localhost]cat msyql_3.sh

#!/bin/bash

HOSTNAME="localhost"

PORT="3306"

USERNAME="root"

PASSWORD="123456"

DBNAME="test1"

TABLENAME="tb1"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}" 2>/dev/null

create_db_sql="create database if not exists ${DBNAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}" 2>/dev/null

create_table_sql="create table if not exists ${TABLENAME} (stuid int not null primary key,stuname varchar(20) not null,stusex char(1) not null,carid varchar(20) not null,birthday datetime,entertime datetime ,address varchar(100) default null)"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}" 2>/dev/null

i=1

while [ $i -le 20000 ]

do

insert_sql="insert into ${TABLENAME} values ($i,zhangsan,1,123563546547,1999-10-10,2016-3-20,zhongguo beijing changpin)"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}" 2>/dev/null

let i++

done

select_sql="select count(*) from ${TABLENAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}" 2>/dev/null

情況一：stuname沒有創建索引的情況下；

mysql> explain select stuid,stuname,stusex,carid,entertime from test1.tb1 where stuname=adminG;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: ref

possible_keys: index_stuname,stuname_carid_entertime

key: index_stuname

key_len: 22

ref: const

rows: 18910

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.03 sec)

情況二：stuname創建索引之後；

mysql> explain select stuid,stuname,stusex,carid,entertime from test1.tb1 where stuname=adminG;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: ref

possible_keys: index_stuname,stuname_carid_entertime

key: index_stuname

key_len: 22

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

在查找「stuname」的時候，如果在stuname上創建了索引，MySQL無須掃描全表，即可準確找到該記錄，相反MySQL會掃描全表。

所以在資料庫表中，對欄位建立索引可以大大提高查詢速度。

註：索引是在存儲引擎中實現的，而不是在伺服器層中實現的，所以，每種存儲引擎的索引都不一定完全相同。並不是所有的存儲引擎都支持所有的索引類型。

什麼是索引？

索引（index）是幫助mysql高效獲取數據的數據結構，它存在的形式是文件。索引能夠幫助我們快速定位數據。更通俗的說，索引好比就是一本書的目錄，可以加快資料庫的查詢速度。

為什麼要使用索引？

索引可以讓mysql高效運行，可以大大提高mysql的查詢（包括排序，分組）效率；數據約束（唯一索引的建立）

索引能給我們帶來什麼好處？

提高查詢速率，快速定位數據。

使用索引產生的代價？

1. 索引本身是以穩健的形式存放在硬碟里，需要的時候才載入至內存，所以添加索引會增加磁碟的開銷；
2. 寫數據，需要更新索引，對資料庫是一個很大的開銷，降低表更新、添加和刪除的速度，不建議使用索引的情況。

①表記錄較少

②索引的選擇性較低，所謂的索引的選擇性，是指不重複的索引值與表記錄數的比值，取值範圍（0-1），即一個索引包含多個列。

1. 普通索引：這是最基本的索引，沒有任何限制。
2. 唯一索引：與普通索引類似，不同的是索引的列的值必須唯一，但允許空值 null。如果是組合索引，組合列的值必須唯一。

主鍵索引：一種特殊的唯一索引，不允許有控制，一般建立表的是時候同事建立主鍵索引

1. 組合索引：為了進一步提升MySQL的效率，就要考慮建立組合索引。

將username，city，age建到一個索引裡面

create index username_city_age on test1.tb1(username,city,age)

如果分別在username，city，age上建立單列索引，讓該表有三個單列索引，查詢時和上面提到的組合索引也會不太一樣，遠遠低於組合索引，雖然此時有了三個索引，但mysql只能用到其中那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引，其實相當與建立了下面三組組合索引：

Username，city，age 、 username city 、username 為什麼沒有city，age這樣的組合索引呢？這是因為mysql組合索引「最左前綴」的結果。

1. 全文索引

只用於myisam表對文本域進行索引。欄位類型包括（char，varcahr，test）

對於大容量的數據表，生成全文索引是一個非常消耗時間非常消耗硬碟空間的做法。

查看索引：

mysql>show index from tablename;

msyql>show keys from tablename;

需要在什麼情況下建立索引？

一般來說，在where和join子句中出現的列需要建立索引。

例如：

在username上建立索引

Select * from test1.tb1 where username=」admin」 and city=」鄭州」

使用索引注意事項：

剛才提到只有某些時候的like才需要建立索引。因為在以通配符%和_開頭做查詢的時候，mysql不會使用索引，例如：

select * from test1.tb1 where username like 『admin%』

而下句就不會使用：

select * from test1.tb1 where name like 『%adnub』

不要在列上進行運算。例如：

select * from users where year(adddate)<2016;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

Select * from users where adddate<2016-01-01』;

總結：

選擇索引列：

1. 性能優化過程中，選擇在那個列上創建索引是最重要的步驟之一。可以考慮使用索引的主要兩種類型的列：在where子句中出現的列，在join子句中出現的列。
2. 考慮列中值的分布，索引的基數越大，索引的效果越好。
3. 使用短索引，如果對字元串列進行索引，應該指定一個前綴長度，可節省大量索引的空間，提升查詢速度。
4. 利用最左前綴。
5. 不要過度索引，只保持所需的索引。每個額外的索引都要佔用額外的磁碟空間，並降低寫操作的性能。在修改表的內容時，索引必須進行更新，有時可能需要重構，因此索引越多，所花的花間越長。
6. Mysql只對以下操作符才使用索引：<,<=,=,>=,between,in

以及某些時候的like（不以通配符%或_開頭的情形）。