請問mysql「字元查詢」和「數字查詢「的效率問題？

01-29

（前提：肯定數字查詢要比字元查詢快，只是我們不得不採用字元查詢，所以請大家分析下採用字元查詢對系統的影響，或者進而採用何種優化才不會過多的影響系統效率）
用戶資料庫：（用戶表，用戶關係表）總量級達到上億，已經決定採用讀寫分離， Sharding 技術。而且在上層有緩存memcached,每個資料庫維護百萬級用戶，由於採用的是按用戶名hash進行 Sharding ，所以採用用戶名做主鍵，如果引入數字id做標識的話會複雜化我們的邏輯（比如引入映射資料庫...)，對於百萬級用戶我們對資料庫的直接操作均以用戶名做查找條件，這樣會對我們的系統效率產生多大影響，如果對用戶名建索引後呢？
謝謝各位老師的關注回答。

（前提：肯定數字查詢要比字元查詢快，只是我們不得不採用字元查詢，所以請大家分析下採用字元查詢對系統的影響，或者進而採用何種優化才不會過多的影響系統效率）

用戶資料庫：（用戶表，用戶關係表）總量級達到上億，已經決定採用讀寫分離， Sharding 技術。而且在上層有緩存memcached,每個資料庫維護百萬級用戶，由於採用的是按用戶名hash進行 Sharding ，所以採用用戶名做主鍵，如果引入數字id做標識的話會複雜化我們的邏輯（比如引入映射資料庫...)，對於百萬級用戶我們對資料庫的直接操作均以用戶名做查找條件，這樣會對我們的系統效率產生多大影響，如果對用戶名建索引後呢？

1.數字查詢肯定比字元串快，基於數字比較的話：CPU運算量肯定更少，需要的內存、邏輯IO和物理IO更少；

要想達到數字查詢條件的效率是無法達到的，索引創建等都是類似的，唯一可能多做的地方就是創建一些索引或組合索引的時候，可以適當地縮短索引欄位的長度；

2.你按用戶的帳號名稱做分表，且你已經採用了MC緩存，用戶表、用戶關係表

2.1 方法一分析：那麼Account_Name欄位可能不得不創建為主鍵或作為主鍵的一部分，且每張表都會引用，若是用MySQL的索引組織表存儲引擎InnoDB，就會存在性能上的問題

優化措施：為Account_Name欄位改創建為唯一索引，每張表都各自創建一個無意義主鍵，且任何錶都不引用自無意義的自增整型欄位值

2.2 方法二分析：你無法像QQ一樣要求用戶提供數字ID的模式，但是你可以內部增加一層，為何說可以考慮自己增加一層，因為你描述到："每個資料庫維護百萬級用戶"，當然我不知道你拆分成多少個庫了，相信不會超過32個，也即不超過3200W，且已經使用了緩存技術。

優化：創建一個索引表，用於內部轉換字元串用戶名與內部UID之間的對應關係，為加快速度可以直接存放到MC中緩存，且只要用戶登錄一次之後，後續所有操作其實可以考慮不再查詢MC的。

然後對用戶的拆分就根據UID進行拆分.....剩下的就跟各大公司的做法類似了

備註：InnoDB索引組織表使用字元串做主鍵，會導致塊裂開增加、塊內排序增加量增加、架構要求每個二級索引都需要冗餘用戶帳號等，也即造成額外大量的物理IO消耗

簡化成以下兩個問題

1. 字元類型和數字類型之間的性能差別，用字元串做索引掃描是否會有性能損耗？

兩者的主要差別就在於，字元類型有字符集的概念，每次從存儲端到展現端之間都有一個字符集編碼的過程。而這一過程主要消耗的就是CPU資源，對於In-memory的操作來說，這是一個不可忽視的消耗。如果要固化到具體測試結果，我們這邊的經驗數據是20%，具體值還是和環境和數據有關係。此外，latin1 和 UTF8 之間也有10%左右的性能差別。

2. 使用hash(用戶名)作為分區鍵是否合理？

至於分區鍵的選擇，個人感覺就和業務需求有很大關係了。如果按照題主所說，引入數字ID分區會加大程序複雜度的話。那用hash作為分區鍵也未嘗不可（一般都以自增ID取模做分區），只是分發前多了一步hash操作（消耗CPU資源）。一般經驗是CPU是最容易橫向擴展的資源，說白了就是堆機器。如果一台機器hash算不過來了，那就多上幾台做Load Balance。再說白一點就是省下的開發成本和省下的機器成本之間的權衡。具體的結果還是測試數據說了算。

不太明白為什麼會產生這個問題

1) 首先我猜測你有一張表存的是user信息

我猜測這張表肯定有個自增的key user_id, 因為你說你用username來做primary key, 所以user_id 起碼是個unique key

2) 如果1不成立, 你沒有user_id, 可以的話讓我看一下你的schema, 然後這張表要跟什麼交互

3) 如果1成立, 目測你可以做一個map (username-&>userid), 丟在memcached裡面, 然後用userid來去資料庫取

-------------

回到你的問題, 你用的用戶名查找, 是模糊查找還是精確查找? 更比如, 如果你要用這個來做為一個join的條件, 我的經驗是會比有index的數字慢很多倍.

關於PrimaryKey的問題，給題主一點參考：

A previous author wrote: "Generally, avoid PrimaryKeys that have meaning. Names are not unique, and many seemingly unique identifiers such as Social Security numbers actually are not, due to real-world data reliability problems." In short, this is a recommendation to always have an auto-generated (typically numeric) SurrogateKey? instead of a domain-based LogicalKey?. This is a rather pat answer to a complex issue, though it is one that will suffice in a number of cases and is at least preferable to having no PrimaryKey at all. The controversy is addressed in AutoKeysVersusDomainKeys.

[1]

然後我本人其實給不了什麼建議了。

[1]http://c2.com/cgi/wiki?DatabaseBestPractices

在表中建立一個針對用戶名對應的 name_id

用戶名為字元串，但 name_id 為整形，name_id = hashToInt(name)

然後在 name_id 上添加索引。

當然 name_id 的演算法有很多，可以在程序中實現。

所以現在就把針對字元串的索引，變更為針對整形的索引。

參考這裡 Possible INDEX on a VARCHAR field in MySql