2014年3月22日 星期六

揪出病態偷窺狂!專家教路:5 招防變態黑客

0

 不論在現實生活又或者網絡世界之中,「偷窺」事件可謂時有發生,先不說現實生活中的「色情偷窺狂」,今次就讓我們說說網絡上黑客的偷窺慾吧!

誰在暗處偷窺你?

你身邊有沒有同事是用東西遮蓋電腦上的視訊鏡頭?或者你自己都正在這樣做?雖然這似乎是過份憂慮,但其實當中隱藏著可怕的原因。我們可能都曾經聽說過別人的電郵遭黑客入侵,甚至收到附有自己電腦視訊鏡頭拍攝的個人私密照片的電郵,而這一切都可能隨時發生在你身上。

來自 Symantec 的專家發現俗稱「偷窺軟件」(Creepware)的「遠端存取木馬程式」(Remote Access Trojans, RAT)攻擊形式逐漸普及,未來恐怕會成為網絡欺凌(Cyber Bullying)的工具,我們該怎麼做才可以預防免受黑客入侵?以下專家便為大家提供 5 大預防黑客的秘訣!

甚麼是「偷窺軟件」(Creepware)?

偷窺軟件攻擊是指使用簡稱 RAT 的「遠端存取木馬程式」執行的攻擊,一旦 RAT 被安裝在受害者的電腦中,它將給予黑客在受害者電腦上幾乎全部的操控權限,黑客可遠端存取你的個人資料、偷窺你的銀行帳戶,更可怕的是它還可透過視訊或錄音設備紀錄你的影像和聲音。以下是 5 個典型的偷窺軟件攻擊行為:

1. 偷窺你的一舉一動:黑客可透過受害者的視訊鏡頭偷偷紀錄你的行為。

2. 盜取資料與檔案:銀行帳戶資料、個人照片、影片等資料或檔案可被黑客存取或刪除。

3. 黑函與性恐嚇:黑客透過使用受害者電腦偷取或偷拍的私人照片及影片,恐嚇受害者做出不雅舉動或勒索金錢。

4. 作弄受害者:黑客們為了一時興起,利用偷窺軟件讓受害者的電腦作出異常行為,例如開啟色情網頁、顯示暴力字句恐嚇或造成系統受損。

5. 代罪羔羊:黑客利用受害者的電腦,在受害者不察覺的情況下進行分散式阻斷服務攻擊(Distributed Denial of Service)及比特幣挖礦(Bitcoin mining)等行為,讓受害者成為代罪羔羊。

3 大危險行為

1. 網頁自動下載:瀏覽網頁時,使用者通常會不自覺地下載偷窺軟件到電腦中。

2. 惡意連結:可連結到以上網頁的惡意連結透過社交網站、聊天室、PTT 和垃圾郵件被持續傳播,黑客還可利用好友的名義或文字來散播惡意連結。

3. 點對點(Peer-to-Peer)資料分享:偷窺軟件通常隱藏在熱門的應用程式或遊戲程式中,並被放在點對點檔案的分享網站上,檔案一旦被執行,偷窺軟件會同時被安裝到電腦中。

5 招防禦偷窺軟件的秘訣

1. 確保病毒定義碼、操作系統與防護軟件都為最新版本。

2. 避免開啟來歷不明的郵件和點擊可疑郵件中的附加檔案。

3. 提防透過郵件、即時訊息和社交網站分享的連結。

4. 只從可靠且合法的來源下載檔案。

5. 提防可疑的電腦視訊行為,在沒有使用視訊時,請關閉前蓋,或以貼紙遮蓋鏡頭。

References

http://www.hkitblog.com/?p=19984


2013年11月16日 星期六

8 hot IT skills for 2014

0

When it comes to overall job prospects for IT professionals, 2014 will look a lot like this year, with 32% of companies expecting to increase head count in their IT shops, compared with 33% in 2013, according to Computerworld's annual Forecast survey.
But while demand will remain steady overall, there have been a few changes in the skill sets most desired by hiring managers. Unemployment "is probably close to zero for people with high-demand skill sets," says Michael Kirven, founder and CEO of Mondo, a technology resource provider. Employers in search of top skills, he says, need to be prepared to move fast. "If you want them, you can be 100% sure there are at least two other firms that want them, as well," he says.

1. Programming/application development
" 49% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 1
As it did in the 2013 Forecast survey, programming/application development tops the list of hot skills, although just under half of the 221 respondents said they will hire in this area, compared with 60% last year. Scot Melland, CEO of Dice Holdings, parent of IT jobs website Dice.com, concurs that software developers are the most sought-after technology workers and notes that they enjoy one of the lowest unemployment rates around — just 1.8%, according to the U.S. Bureau of Labor Statistics. It's no wonder, then, that respondents to the Computerworld 2014 Forecast survey named developer and programmer job openings as the most difficult to fill. The hottest specialties within that category, Melland says, are mobile development expertise and experience building secure applications.
Carbonite, an online backup service provider, expects to find a tight market for software developers and engineers as it shifts its business model to focus on the needs of small businesses, says Randy Bogue, vice president of talent at the Boston-based company. "While there are a lot of experienced software developers in the Boston area, there are just as many technology companies looking to hire them," he says. "We find this while looking for front-end developers, user experience engineers, mobile developers and pretty much any other software development position."
Lucille Mayer, CIO at BNY Mellon, also expects to have difficulty finding developers. The financial services company has several hundred openings, mainly in New York City and Pittsburgh, and about 40% of those are in development. Another 30% are in infrastructure, 20% are for business analysis/project management positions, and 10% are in management.
"Demand is high for skilled developers with three to five years' experience and a service delivery orientation," says Mayer, who is particularly interested in people with object-oriented development experience. Also important is finding people from diverse backgrounds, with diverse ideas and perspectives, she says.

Hospitality giant Hyatt is transitioning from a reliance on third-party service providers and aims to bring more development talent in-house. "We're looking to hire people who embrace agility and speed to move ideas to prototype and production quickly," says Alex Zoghlin, Hyatt's global head of technology.

2. Help desk/technical support
" 37% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 3
Help desk/tech support remained near the top of the list, moving up from No. 3 last year. Melland says that's an encouraging sign for the economy and the overall hiring outlook. "Organizations mainly add help desk and tech support when they're adding workers and expanding their technology infrastructure," he says. Also contributing to demand for support technicians is the fact that many companies are bringing the help desk back in-house after outsourcing that function; that's partly a response to the proliferation of mobile devices and company-provided Web services. Because of the complexity of such setups, "it's important for support staff to really understand what the company is doing, which argues for having this function closer to home," Melland says.
After several years of running a lean support function, Wolverine Advanced Materials in Dearborn, Mich., plans to hire a few help desk staffers in response to business growth and a decision to provide ITIL-based service management, says James Bland, network manager at the automotive materials supplier. "There is growth in the company, so we're more confident in hiring," he says.

3. Networking
" 31% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 8
Demand for networking skills jumped to No. 3 from eighth place last year. This correlates with the results of a recent survey by IT hiring firm Robert Half Technology, in which 55% of the respondents named network administration as the skill
The need for wireless connectivity is probably behind the interest in networking professionals, Melland says. "Demand for people with wireless networking experience is up 9% year over year," he says, and the unemployment rate for network and systems administrators is 1.1%.
Charles Whitby, lead network analyst at the Medical Center of Central Georgia, says growing use of wireless medical devices is definitely fueling his workload. In addition to the increased network traffic they produce, those devices require a lot of troubleshooting — as is the case when, for example, their firmware needs upgrading but it hasn't been approved by the Food and Drug Administration, he says.
Meanwhile, at Wolverine, Bland is looking to offload some networking responsibilities so he can concentrate on more strategic issues.

4. Mobile applications and device management
" 27% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 9
With mobile devices proliferating in both the corporate and consumer worlds, it's little wonder that mobile skills catapulted toward the top of the list, from No. 9 last year. And because of mobile's relatively new status, it's also not surprising that Computerworld survey respondents named mobile expertise the third most difficult skill to find, after development and BI/analytics skills.
Which of these skills do you expect it will be most difficult to hire for?
Among respondents who expect an increase in IT employee head count in the next 12 months
  • Programming/application development 32%
  • Business intelligence/analytics 21%
  • Mobile applications and device management 17%
  • Project management 14%
  • Security 14%
Source: Computerworld Forecast survey; base: 221 IT executive respondents; June 2013
Mobile app development is "a huge initiative" at PrimeLending in Dallas, says CIO Tim Elkins, and it will be a key hiring area next year. In addition to expanding its Salesforce.com development ranks, the mortgage provider hopes to hire two or three mobile developers, he says. PrimeLending's first mobile app is designed to enable its business partners — real estate agents and builders — to view loan statuses; its next one will be for consumers.
Elkins anticipates difficulty finding mobile developers and is therefore training a couple of current staffers to fill the need. "Salesforce.com developers are really tough to find because of the high demand, and so are mobile developers," he says.
Mobile expertise is also a priority for Hyatt, and Zoghlin says the company is trying to fill niche roles to ensure a consistent strategy across areas like mobility and user experience.

5. Project Management
" 25% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 2
While project management fell from its No. 2 position last year, it is considered a highly sought-after skill. Melland says that Dice has found demand for project managers to be second only to demand for software developers/engineers, having risen 11% from last year. That uptick, he says, is another positive sign for the economy as a whole, because it indicates that companies are willing to pursue strategic projects.
Mondo's Kirven attributes the demand for project managers to renewed interest in complex, strategic business-technology initiatives. "IT has historically been graded based on the success or failure of projects, so [companies are] making heavy investments in the business analyst/project manager layer," he says. "These people need to be able to talk to developers about technology and the right solution, but they also need to put on their business hat to gather requirements and prioritize needs and translate that into a programmable effort for IT."

6. Database Administration
" 24% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: Not ranked
Database administration — which didn't even make last year's list — will be hot in 2014, likely because of interest in big data. Kirven concedes that the term big data is a catch-all for everything companies want to do with the burgeoning stockpiles of information they store on internal systems and, increasingly, collect from sources such as social media sites, the Web and third parties. Much of the interest in big data originates in marketing, which wants to learn as much about customers as possible.
"Oracle DBAs, data architects — these people stay on the market for about an hour until they're hired," Kirven says. "People are looking for that person who can build a logical data map of their systems and aggregate relevant data so they can analyze and report on it."
DBAs with experience moving pieces of the IT infrastructure to the cloud will be highly sought after, says Melland, noting that demand for cloud skills is up 32% from last year.
To help kick off PrimeLending's big data initiative, Elkins says he is seeking systems analysts, developers and DBAs to integrate data from third parties, with the goal of easing the mortgage process. "Mortgages have been like a big black hole, with a lack of transparency and a lot of sitting and waiting," Elkins says. "Our focus in 2014 is to give consumers more control and an experience with mortgages that they've never had before."

7. Security Compliance/Governance
" 21% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 4
Security expertise seems to show up on every list of hot IT skills, and Melland says interest in cybersecurity will further drive demand, which is up 23% from last year. "It's one of those skills that falls into a lot of job types, like network engineering, software development and database architecture," he says. Respondents to a recent Robert Half Technology survey said security jobs are among the most challenging to fill, in addition to application development and database management positions.
With the increase in malware and cyberattacks, security has become a No. 1 priority for PrimeLending, which doubled its security staff this year, from four to eight people, Elkins says.

8. Business Intelligence/Analytics
" 18% of respondents said that they plan to hire for this skill in the next 12 months.
" Last year's ranking: No. 5
With the volume of global data predicted to expand by a factor of 44 from 2009 to 2020 and reach 35.2 zettabytes, according to IDC, companies are eager to gain a competitive edge by developing sophisticated analytics capabilities. Although BI/analytics is still considered a specialty and therefore has fewer postings than other job categories on Dice.com, Melland says it's the third fastest-growing skill area on the website, and demand is up 100% from last year. Analytics expertise is scarce, ranking second among the most difficult skills to find in the Computerworld survey. Accordingly, these professionals command high salaries, often into the six figures, Melland says.

At Wolverine, management's demand for data-driven insights is growing, so Bland is looking for people with BI skills who are also familiar with the Plex Systems ERP application, which the company uses. "We would definitely like to get more information out of [our ERP] system, so someone with BI experience would be great," he says. "We'd like to provide more information in a more timely manner so the business can be more proactive." Hyatt, says Zoghlin, is similarly looking for people "who can make analytics usable and useful for customers and colleagues."
Forecast 2014
Looking Beyond Tech Skills When Hiring IT Workers
Technology skills aren't the only factor to consider when assessing candidates for IT jobs. Employers should also consider applicants' interpersonal skills to ensure new hires will be effective in the workplace. The two most important characteristics, according to the Computerworld Forecast survey, are the ability to collaborate (cited by 66% of the respondents) and the ability to communicate with business users (62%). This comes as no surprise to Scot Melland, CEO of Dice Holdings. "So much technology is being used in every part of the organization that you need people who are good communicators," he says.
James Bland, network manager at Wolverine Advanced Materials, says those are skills he will seek in new hires. "I want to empower our users to know how IT can help them be more efficient and get their job done," he says, and that can happen only when IT helps translate systems capabilities into something the user can put to good use. "You can implement the best systems in the world, but if people don't understand what to do with them, they're useless," Bland says.
Lucille Mayer, CIO at BNY Mellon, says a customer-service mentality is a must. "Our IT department is called Client Technology Solutions, and every one of us has a client customer, whether it be internal or external," she says. "A service orientation and being customer-focused, collaborative and a great communicator is essential."
An important communication skill is speaking the language of various business domains, such as marketing, sales and finance, Melland says. In fact, according to Michael Kirven at Modis, employers are increasingly seeking people with knowledge of business disciplines in addition to tech skills, whether it's an HTML5 developer who understands the supply chain in retail or a Java developer with experience in financial derivatives trading systems. "Specialization can really drive innovation," he says.
At PrimeLending, it's all about cultural fit. "We hire for culture first," says CIO Tim Elkins. This is particularly true at the leadership level. "If we're going to hire a new manager, it's not just a matter of whether they're a good leader but whether they can adapt to our style," which Elkins calls "servant leadership" — meaning leaders are called to serve, not order people around.

References : 

2013年7月3日 星期三

32 Tips To Speed Up Your MySQL Queries

0

32 Tips To Speed Up Your MySQL Queries

If you are interested in how to create fast MySQL queries, this article is for you

1. Use persistent connections to the database to avoid connection overhead.
(使用持久化到數據庫的連接,以避免連接開銷。)

2. Check all tables have PRIMARY KEYs on columns with high cardinality (many rows match the key value). Well,`gender` column has low cardinality (selectivity), unique user id column has high one and is a good candidate to become a primary key.
(檢查所有表的主鍵列上的高基數(多少行匹配的鍵值)。嗯,`性別`列有低基數(選擇性),唯一的用戶ID列有高一,成為一個主鍵是一個很好的候選人。)

3. All references between different tables should usually be done with indices (which also means they must have identical data types so that joins based on the corresponding columns will be faster). Also check that fields that you often need to search in (appear frequently in WHERE, ORDER BY or GROUP BY clauses) have indices, but don’t add too many: the worst thing you can do is to add an index on every column of a table (I haven’t seen a table with more than 5 indices for a table, even 20-30 columns big). If you never refer to a column in comparisons, there’s no need to index it.
(所有的不同的表之間的引用通常應該 indices (這也意味著它們必須有相同的數據類型,因此,加入對應的列的基礎上會更快)。此外,還要檢查字段,你經常需要搜索(經常出現在哪裡,ORDER BY或GROUP BY子句)indices,但不要加太多,你可以做的最糟糕的事情是每列添加索引一個表(我還沒有看到超過5 indices 為表的一個表,甚至20-30列大)。如果你從來沒有參照比較中的一列,有沒有必要建立索引。)

4. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements.
(當你使用簡單的權限發出GRANT語句讓MySQL來減少客戶端執行語句時權限檢查的開銷。)

5. Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them.
(使用較少的RAM,每行只大的,因為他們需要持有的值存儲在他們的聲明列。)

6. Use leftmost index prefix — in MySQL you can define index on several columns so that left part of that index can be used a separate one so that you need less indices.
(使用索引的最左邊前綴 - 在 MySQL 中,你可以定義索引幾列,因此,該指數的左半部分,可以使用單獨的一個,這樣,你需要較少的 indices。)

7. When your index consists of many columns, why not to create a hash column which is short, reasonably unique, and indexed? Then your query will look like:
(當 index 包括許多列,為什麼不創建一個 hash 是短暫的,合理的獨特和索引的列?然後,您的查詢將看起來像這樣:)

SELECT *
FROM table
WHERE hash_column = MD5( CONCAT(col1, col2) )
AND col1='aaa' AND col2='bbb';


8. Consider running ANALYZE TABLE (or myisamchk --analyze from command line) on a table after it has been loaded with data to help MySQL better optimize queries.
(考慮運行ANALYZE TABLE(或myisamchk - 分析命令行)放在 table 上後,它已被載入數據幫助MySQL更好地優化查詢的.)
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
Column Value
Table The table name
Op Always analyze
Msg_type status, error, info, note, or warning
Msg_text An informational message

http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html

9. Use CHAR type when possible (instead of VARCHAR, BLOB or TEXT) — when values of a column have constant length: MD5-hash (32 symbols), ICAO or IATA airport code (4 and 3 symbols), BIC bank code (3 symbols), etc. Data in CHAR columns can be found faster rather than in variable length data types columns.
(使用CHAR類型(而不是VARCHAR,BLOB或TEXT) - 當有固定長度的列值:MD5哈希(32個符號),國際民航組織或機場的IATA代碼(4和3個符號),BIC銀行代碼(3個符號)等CHAR列中的數據,可以發現更快,而不是在可變長度的數據類型列。)

10. Don’t split a table if you just have too many columns. In accessing a row, the biggest performance hit is the disk seek needed to find the first byte of the row.
(如果你只是有太多列。不要把一個表。在訪問行,最大的性能損失,需要找到該行的第一個字節的磁盤尋道。)

11. A column must be declared as NOT NULL if it really is — thus you speed up table traversing a bit.
(A柱必須聲明為NOT NULL如果真的是 - 從而加快表遍歷位。)

12. If you usually retrieve rows in the same order like expr1, expr2, ..., make ALTER TABLE ... ORDER BY expr1, expr2, ... to optimize the table.
(如果你平時以相同的順序檢索行如表達式1,表達式2,...,使ALTER TABLE... ORDER BY表達式1,表達式2,...優化表。)

13. Don’t use PHP loop to fetch rows from database one by one just because you can — use IN instead, e.g.
(不要直接使用PHP循環從數據庫讀取行1只是因為你可以 - 使用一個代替,例如:)


SELECT *
FROM `table`
WHERE `id` IN (1,7,13,42);

14. Use column default value, and insert only those values that differs from the default. This reduces the query parsing time.
(使用列默認值,然後將只有那些不同於默認值。這降低了的查詢解析時間。)

15. Use INSERT DELAYED or INSERT LOW_PRIORITY (for MyISAM) to write to your change log table. Also, if it’s MyISAM, you can add DELAY_KEY_WRITE=1 option — this makes index updates faster because they are not flushed to disk until the table is closed.
(使用INSERT DELAYED 或INSERT LOW_PRIORITY(MyISAM)寫信給您的更改日誌表。此外,如果是MyISAM表,你可以添加DELAY_KEY_WRITE= 1選項 - 這使得索引更新快,因為它們不會刷新到磁盤,直到表關閉。)

16. Think of storing users sessions data (or any other non-critical data) in MEMORY table — it’s very fast.
(想想存儲用戶會話數據(或任何其他非關鍵數據)MEMORY表 - 這是非常快的。)
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

17. For your web application, images and other binary assets should normally be stored as files. That is, store only a reference to the file rather than the file itself in the database.
(對於你的web應用,圖像和其他二進制資產通常應被作為文件存儲。也就是說,存儲文件只是一個參考,而不是在數據庫中的文件本身。)

18. If you have to store big amounts of textual data, consider using BLOB column to contain compressed data (MySQL’s COMPRESS() seems to be slow, so gzipping at PHP side may help) and decompressing the contents at application server side. Anyway, it must be benchmarked.
(如果你有文本數據存儲金額較大,可以考慮使用BLOB列包含壓縮數據(MySQL's COMPRESS()似乎是緩慢的,所以在PHP端使用gzip壓縮可能會有所幫助),解壓縮在應用服務器端的內容。無論如何,它必須將基準。)

19. If you often need to calculate COUNT or SUM based on information from a lot of rows (articles rating, poll votes, user registrations count, etc.), it makes sense to create a separate table and update the counter in real time, which is much faster. If you need to collect statistics from huge log tables, take advantage of using a summary table instead of scanning the entire log table every time.
(如果你經常需要計算計數或總結的基礎上很多行信息(文章評價,投票表決時,註冊用戶數,等等),它創建一個單獨的表,並實時更新計數器,這是有道理要快得多。如果你需要從龐大的日誌表收集統計信息,利用使用匯總表,而不是每次掃描整個日誌表。)

20. Don’t use REPLACE (which is DELETE+INSERT and wastes ids): use INSERT … ON DUPLICATE KEY UPDATE instead (i.e. it’s INSERT + UPDATE if conflict takes place). The same technique can be used when you need first make a SELECT to find out if data is already in database, and then run either INSERT or UPDATE. Why to choose yourself — rely on database side.
(不要使用REPLACE(DELETE+ INSERT和wastes ids):使用INSERT... ON DUPLICATE KEY UPDATE ,而不是(即它的INSERT+UPDATE ,如果衝突發生)。同樣的技術也被使用時,你首先需要做一個選擇,以找出是否數據已經在數據庫,然後運行無論是INSERT或UPDATE。為什麼要選擇自己 - 依靠數據庫側面。)

21. Tune MySQL caching: allocate enough memory for the buffer (e.g. SET GLOBAL query_cache_size = 1000000) and define query_cache_min_res_unit depending on average query resultset size.
(調整MySQL的緩存:緩衝區分配足夠的內存(如 SET GLOBAL query_cache_size= 1000000),並定義平均查詢結果集的大小取決於query_cache_min_resultset_unit 變量。)

22. Divide complex queries into several simpler ones — they have more chances to be cached, so will be quicker.
(將複雜查詢分成幾個簡單的 - 他們有更多的機會進行高速緩存,所以會更快。)

23. Group several similar INSERTs in one long INSERT with multiple VALUES lists to insert several rows at a time: quiry will be quicker due to fact that connection + sending + parsing a query takes 5-7 times of actual data insertion (depending on row size). If that is not possible, use START TRANSACTION and COMMIT, if your database is InnoDB, otherwise use LOCK TABLES — this benefits performance because the index buffer is flushed to disk only once, after all INSERT statements have completed; in this case unlock your tables each 1000 rows or so to allow other threads access to the table.
(集團幾個類似的 INSERTs 在一個很長的INSERT多個值列出一次 I插入幾行:查詢方式將更快,因為事實上,連接+發送+解析查詢需要5-7倍的實際數據插入(取決於行大小)。如果這是不可能的,使用START TRANSACTION和COMMIT,如果你的數據庫是InnoDB,否則使用LOCK TABLES - 這得益於性能,因為索引緩衝區刷新到磁盤上只有一次,所有INSERT語句完成後,在這種情況下,打開數據表鎖每1000行左右,以允許其他線程訪問表。)

24. When loading a table from a text file, use LOAD DATA INFILE (or my tool for that), it’s 20-100 times faster.
(從文本文件中加載一個表時,使用LOAD DATA INFILE(或我的工具),它的速度快20-100倍。)

25. Log slow queries on your dev/beta environment and investigate them. This way you can catch queries which execution time is high, those that don’t use indexes, and also — slow administrative statements (like OPTIMIZE TABLE and ANALYZE TABLE)
(慢查詢日誌上的開發/測試環境和調查。這樣你就可以趕上查詢執行時間為高,那些不使用索引,也 - 慢行政報表(如OPTIMIZE TABLE和ANALYZE TABLE))
http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html

26. Tune your database server parameters: for example, increase buffers size.
(調整你的數據庫服務器參數,例如:增加緩衝區大小。)

27. If you have lots of DELETEs in your application, or updates of dynamic format rows (if you have VARCHAR, BLOB or TEXT column, the row has dynamic format) of your MyISAM table to a longer total length (which may split the row), schedule running OPTIMIZE TABLE query every weekend by crond. Thus you make the defragmentation, which means more speed of queries. If you don’t use replication, add LOCAL keyword to make it faster.
(如果你有大量的在你的應用程序中 DELETEs  或 updates  您的MyISAM表動態格式的行(如果你有VARCHAR,BLOB或TEXT列,該行有動態格式)的總長度較長(可能拆分行)調度的crond運行OPTIMIZE TABLE查詢每個週末。因此,你做碎片整理,這意味著更多的查詢速度。如果你不使用複製,添加 LOCAL 關鍵字,使其更快。)

28. Don’t use ORDER BY RAND() to fetch several random rows. Fetch 10-20 entries (last by time added or ID) and make array_random() on PHP side. There are also other solutions.
(不要使用ORDER BY RAND()來獲取一些隨機行。取10-20項(最後按時間添加或ID)和PHP側array_random()。也有其他的解決方案。)

29. Consider avoiding using of HAVING clause — it’s rather slow.
(考慮避免使用HAVING子句 - 這是相當緩慢的。)

30. In most cases, a DISTINCT clause can be considered as a special case of GROUP BY; so the optimizations applicable to GROUP BY queries can be also applied to queries with a DISTINCT clause. Also, if you use DISTINCT, try to use LIMIT (MySQL stops as soon as it finds row_count unique rows) and avoid ORDER BY (it requires a temporary table in many cases).
(在大多數情況下,一個DISTINCT子句可以被視為GROUP BY的一個特例,所以GROUP BY查詢適用的優化也可以應用於使用DISTINCT子句的查詢。另外,如果你使用DISTINCT,嘗試使用LIMIT(MySQL的停止,只要它發現ROW_COUNT獨特的行),避免ORDER BY(在許多情況下,它需要一個臨時表)。)


31. When I read “Building scalable web sites”, I found that it worth sometimes to de-normalise some tables (Flickr does this), i.e. duplicate some data in several tables to avoid JOINs which are expensive. You can support data integrity with foreign keys or triggers.
(當我讀到“Building scalable web sites”,我發現值得有時去一些表(Flickr的這個)正常化,即複製一些數據,以避免在多個表的連接,JOINs是昂貴的。可以支持外鍵或觸發器的數據完整性。)

32. If you want to test a specific MySQL function or expression, use BENCHMARK function to do that.
(如果你想測試一個特定的MySQL函數或表達式,使用BENCHMARK 功能做到這一點。)

中文為Google 翻譯

http://www.ajaxline.com/node/2099

2013年6月14日 星期五

MySQL優化全攻略-相關數據庫命令

0

在MySQL我們在使用SELECT做撈取資料的時候,有時候常常會效能低落,撈取資料需要很長的時間,有時候是SQL語法下得不好導致沒有使用到正確的索引去撈資料,我們這個時候就必須要檢查我們下的SQL語法到底有哪些地方需要改善。


▲ EXPLAIN
 EXPLAIN能夠分析SELECT命令的處理過程。這不僅對於決定是否要為表加上索引很有用,而且對於了解MySQL處理複雜連接的過程也很有用。

而 EXPLAIN 後的資料有下面這些欄位

  1. select_type
  2. table:關連到的資料表
  3. type:使用關聯查詢的類型(效率由好至壞排序)
  4. System
  5. const
  6. eq_ref
  7. ref
  8. fulltext
  9. ref_or_null
  10. index_merge
  11. unique_subquery
  12. index_subquery
  13. range
  14. index
  15. ALL



  • possible_keys:可能使用到的索引,從WHERE語法選擇出一個適合的欄位
  • key:實際使用到的索引,如果為NULL,則是沒有使用索引
  • key_len:使用索引的長度,長度越短 準確性越高
  • ref:顯示那一列的索引被使用,一般是一個常數(const)
  • rows:MySQL用來返回資料的筆數,可以簡單的把rows視為執行效能,越少越好
  • Extra:MySQL用來解析額外的查詢訊息
  • Distinct:當MySQL找到相關連的資料時,就不再搜尋。
  • Not exists:MySQL優化 LEFT JOIN,一旦找到符合的LEFT JOIN資料後,就不再搜尋。
  • Range checked for each Record(index map:#):無法找到理想的索引。此為最慢的使用索引。
  • Using filesort:當出現這個值時,表示此SELECT語法需要優化。因為MySQL必須進行額外的步驟來進行查詢。
  • Using index:返回的資料是從索引中資料,而不是從實際的資料中返回,當返回的資料都出現在索引中的資料時就會發生此情況。
  • Using temporary:同Using filesort,表示此SELECT語法需要進行優化。此為MySQL必須建立一個暫時的資料表(Table)來儲存結果,此情況會發生在針對不同的資料進行ORDER BY,而不是GROUP BY。
  • Using where:使用WHERE語法中的欄位來返回結果。
  • System:system資料表,此為const連接類型的特殊情況。
  • Const:資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行,這個值就是常數,因為MySQL會先讀這個值然後把它當做常數。
  • eq_ref:MySQL在連接查詢時,會從最前面的資料表,對每一個記錄的聯合,從資料表中讀取一個記錄,在查詢時會使用索引為主鍵或唯一鍵的全部。
  • ref:只有在查詢使用了非唯一鍵或主鍵時才會發生。
  • range:使用索引返回一個範圍的結果。例如:使用大於>或小於<查詢時發生。
  • index:此為針對索引中的資料進行查詢。
  • ALL:針對每一筆記錄進行完全掃描,此為最壞的情況,應該盡量避免。






下面這個例子顯示瞭如何用EXPLAIN提供的信息逐步地優化連接查詢。 (本例來自MySQL文檔,見

http://www.mysql.com/doc/E/X/EXPLAIN.html。原文寫到這裡似乎有點潦草了事,特加上此例。)

假定用EXPLAIN分析的SELECT命令如下所示:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
        tt.ProjectReference, tt.EstimatedShipDate,
        tt.ActualShipDate, tt.ClientID,
        tt.ServiceCodes, tt.RepetitiveID,
        tt.CurrentProcess, tt.CurrentDPPerson,
        tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
        et_1.COUNTRY, do.CUSTNAME
      FROM tt, et, et AS et_1, do
      WHERE tt.SubmitTime IS NULL
        AND tt.ActualPC = et.EMPLOYID
        AND tt.AssignedPC = et_1.EMPLOYID
        AND tt.ClientID = do.CUSTNMBR;


SELECT命令中出現的表定義如下:

※表定義

表列列類型
tt ActualPC CHAR(10)
 tt AssignedPC CHAR(10)
 tt ClientID CHAR(10)
 et EMPLOYID CHAR(15)
 do CUSTNMBR CHAR(15)
 

※索引

表索引
tt ActualPC
 tt AssignedPC
 tt ClientID
 et EMPLOYID (主鍵)
do CUSTNMBR (主鍵)
※tt.ActualPC值分佈不均勻

在進行任何優化之前,EXPLAIN對SELECT執行分析的結果如下:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked for each record (key map: 35)


每一個表的type都是ALL,它表明MySQL為每一個表進行了完全連接!這個操作是相當耗時的,因為待處理行的數量達到每一個表行數的乘積!

即,這裡的總處理行數為74 * 2135 * 74 * 3872 = 45,268,558,720。

這裡的問題之一在於,如果數據庫列的聲明不同,MySQL(還)不能有效地運用列的索引。在這個問題上,VARCHAR和CHAR是一樣的,除非它們

聲明的長度不同。由於tt.ActualPC聲明為CHAR(10),而et.EMPLOYID聲明為CHAR(15),因此這裡存在列長度不匹配問題。

為了解決這兩個列的長度不匹配問題,用ALTER TABLE命令把ActualPC列從10個字符擴展到15字符,如下所示:
mysql > ALTER TABLE tt MODIFY ActualPC VARCHAR(15);



現在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)了,執行EXPLAIN進行分析得到的結果如下所示:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
do ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

 �
這還算不上完美,但已經好多了(行數的乘積現在少了一個係數74)。現在這個SQL命令執行大概需要數秒鐘時間。

為了避免tt.AssignedPC = et_1.EMPLOYID以及tt.ClientID = do.CUSTNMBR比較中的列長度不匹配,我們可以進行如下改動:
mysql > ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
             MODIFY ClientID VARCHAR(15);

 �
現在EXPLAIN顯示的結果如下:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

這個結果已經比較令人滿意了。
 餘下的問題在於,默認情況下,MySQL假定tt.ActualPC列的值均勻分佈,而事實上tt表的情況並非如此。幸而,我們可以很容易地讓MySQL知

道這一點:
shell > myisamchk –analyze PATH_TO_MYSQL_DATABASE/tt
 shell > mysqladmin refresh

 �
現在這個連接操作已經非常理想,EXPLAIN分析的結果如下:

table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

接下來我們要討論的是數據庫性能優化的另一方面,即運用數據庫服務器內建的工具輔助性能分析和優化。

▲ SHOW

執行下面這個命令可以了解服務器的運行狀態:

mysql >show status;

該命令將顯示出一長列狀態變量及其對應的值,其中包括:被中止訪問的用戶數量,被中止的連接數量,嘗試連接的次數,並發連接數量最大

值,以及其他許多有用的信息。這些信息對於確定係統問題和效率低下的原因是十分有用的。

SHOW命令除了能夠顯示出MySQL服務器整體狀態信息之外,它還能夠顯示出有關日誌文件、指定數據庫、表、索引、進程和許可權限表的寶貴

信息。請訪問http://www.mysql.com/doc/S/H/SHOW.html了解更多信息。


OPTIMIZE

OPTIMIZE能夠恢復和整理磁盤空間以及數據碎片,一旦對包含變長行的表進行了大量的更新或者刪除,進行這個操作就非常有必要了。

OPTIMIZE當前只能用於MyISAM和BDB表。

結束語:從編譯數據庫服務器開始、貫穿整個管理過程,能夠改善MySQL性能的因素實在非常多,本文只涉及了其中很小的一部分。儘管如此


http://blog.kejyun.com/2012/12/Using-EXPLAIN-SQL-To-Analysis-Efficient-On-MySQL.html

http://blog.itcert.org/archives/1156

http://www.devshed.com/Server_Side/MySQL/Optimize/

http://www.chinabyte.com/builder/detail.shtm?buiid=1012&parid=1