对于MySQL性能优化的文章可以说是成千上万,不过大多数都是拷贝和粘贴,或者没有细化。这里虽然是老调重弹,但希望能有不一样的新意给大家分享。

添加索引

1. 在经常访问的列上添加索引

注意:很小的表不需要建立索引表。因为索引会占用一定的额外的磁盘空间,每个索引字段都要求MySQL在这个字段和数据库中的具体位置上为每条记录保存信息。

2. 未加索引之前的分析

例如:EXPLAIN select * from users where login LIKE ‘%lily%’;

3. 增加索引后的分析

我们给login字段增加一个索引,然后再使用EXPLAIN 关键字分析。
例如:EXPLAIN select * from users where login LIKE ‘%lily%’;我们使用SHOW STATUS 或 mysqladmin 扩展命令来显示值,检查索引的有效性:查看Handle_read_rnd_next 值是否较高,如果较低则表明索引利用率很差,即不经常使用。如果很高的话,就相当于直接到该记录取值,不会进行全表扫描。注意:索引会加快搜索的速度,但是也会减慢写的操作,如INSERT,DELETE 或UPDATE 等操作。如果一个表经常读,而不经常写和修改操作,可以尝试细分这个表的信息,而不是建立索引。在查询时,使用ORDER BY 命令和ALERT TABLE命令,这样会读得更快,效率也更高。

4. 全文索引

从MySQL 3.23开始,提供了一种高效的方法来检索在char、varchar或text字段类型中的文本,并尽可能生成用户最需要的结果。比如,用户使用一个字符串,如china is great来进行搜索,MySQL会把搜索的文本分解为单词,从记录中查找相似的内容返回给用户。

创建全文索引与创建普通索引没有太大的区别,格式如下:
ALERT TABLE tbl ADD FULLTEXT INDEX (synopsis);
其中,synopsis是自定义的全文索引变量名称。查询时使用MATCH函数
SELECT title,author FROM userinfo WHERE MATCH(synopsis) AGAINST (‘suspense’);

MySQL缓存和性能调优

我们可以使用以下方法对MySQL的数据缓存和性能进行调整。

1. 查询高速缓存

确认缓存或显示缓存大小
mysql> SHOW VARIABLES LIKE ‘%query_cache%’;
在显示的结果中,其中query_cache_type=OFF ,则表示高速缓存已经关闭。
如果query_cache_size=0,则高速缓存也是关闭的。
查看使用高速缓存和非高速缓存的性能对比:
Mysql>SELECT SQL_CACHE * FROM employees;
Mysql>SELECT SQL_NO_CACHE * FROM employees;
经常运行的查询均启用高速缓存,性能将是十分明显的。
注意:一旦表中的数据有变化,MySQL会自动删除当前的高速缓存,并重建。

2. MySQL 系统微调

我们可以对MySQL参数做如下调整。

1. key_buffer_size
该变量控制索引缓冲可以使用内存的数量。这个值越高,索引可以使用的内存就越多,性能就越好。一般情况下,可以保持这个值在服务器中使用25%~30%的内存。

2. max_connections变量
MySQL变量建议使用公式table_cache=max_connections * N设置table_cache,其中N是标准链接中表的数量。

3. 修改高速缓存:table_cache
下篇再续…有兴趣请继续关注…