当前位置:首页 > 技术文章 > mysql > mysql优化
mysql优化
文章来源:本站原创  浏览次数:89  发布日期:2013-02-05

1、  命令行登陆mysql方式,mysql –u root –p ,然后再键入密码。

2、  Mysqlreport 的使用,查看mysql的性能工具(主要是对mysql show statusshow innodb status 两个命令的结果的分析)

3、  数据库索引:

(1)      数据库索引分为:普通索引、唯一索引、主键、全文索引。

(2)      使用explain分析查询语句的索引使用情况。

例如:explain select * from test whereid =1;

其中可以得到2个重要的信息,typekey.

type const 意味着这次查询通过索引直接找到一个匹配行。

type all 意味着这次查询进行全表扫描。

type ref 意味着这次查询通过查询找到多个匹配行。

key Primary ,意味着这次查询使用主键查询

模糊查询不管有没有索引,都是全表扫描

(3)      使用组合索引:

多个字段加到一个索引里面。按照最左前缀的原则来进行索引查询

例如: key1,key2,key3

           Where key1=1Where key1=1 and key2=2 Where key1=1 and key2=2 and key3=3

对于order by group by 也是遵循最左原则,查询优化器会自动排序

InnoDBMyISAM存储引擎使用组合索引,结果有所不同。最好实际分析

(4)      使用慢查询来分析SQL查询的时间。

mysql中开启慢查询很简单,在my.cnf中增加以下配置选项:

long_query_time = 1

log-slow-queries = /data/var/mysql_slow.log

代表mysql为把查询时间超过1秒的查询记录到日志里。

log-queries-not-using-indexes //记录没有使用索引的查询

查看慢查询日志的工具是 mysqlsla

(5)      索引缓存。

缓存的大小由 key_buffer 的配置选项来决定。

MyISAM只缓存索引,不缓存数据。数据库崩溃的话,MyISAM很难恢复数据。

InnoDB差用预写日志方式,崩溃的话,可以通过日志恢复。

(6)      锁定与等待。

MyISAM采用表锁定。如果查询多,数据变动小的话。不会有多大的影响,如果更新频繁或者多的话。Select必须等待所有更新完成,才进行查询。

InnoDB采用行锁定。如果你的站点的大部分数据是靠用户来更新的话,行锁定可能更好。行锁定可以并发进行。互不影响

通过show processlist命令可以监视链接到mysql的进程状态。

在数据量很大的时候MyISAM查询比InnoDB的要快的多。

           InnoDB提供行锁定、外键、事务和易于修复等特性。

        innodb_flush_log_at_trx_commit=1//表示事务提交时立即将事务日志写入磁盘,同时数据和索引也立即更新。

        innodb_flush_log_at_trx_commit=0//每隔一秒

        innodb_flush_log_at_trx_commit=2//立即写入磁盘文件,但不立即刷新到磁盘,而是每隔一秒。

           innodb_flush_method= O_DIRECT //跳过文件缓冲区。提高I/O性能

(7)      使用查询缓存

默认配置下,MYSQL是没有开启的,我们设置以下几个选项。

query_cache_size=268435456

query_cache_type=1

query_cache_limit=1048576

查询缓存不适合更新和查询交替执行的场所。那样反而更慢。

(8)      临时表。

         临时表可以创建在磁盘、内存、临时文件中。tmp_table_size可以设置临时表在内存中的大小。

(9)      线程池:在应用中使用持久链接可以减少线程的重复创建。

可以设置缓存线程的个数,thread_cache_size=100,缓存100个线程。

(10)  反范式化设计。

对于一些需要的数据,如社交网站的FEED功能,可以创建一个反范式的表来保存这些数据。

(11)  放弃关系型数据库,使用memcacheDB,使用key-value来存储数据,速度更快。

4、  数据库的扩展

(1)      主从复制:

·开启主服务器上的二进制日志

·在主服务器和从服务器上分别进行简单的配置和授权

(2)      读写分离

主服务器用来写入操作,从服务器用来进行读操作

(3)      适用反向代理

Mysql proxy通过LUA脚本来描述转发规则

(4)      垂直分区

将不同的数据库从主服务器上分开,尽量避免适用联合查询。

(5)      水平分区

将同一个数据库表中的记录通过特定的算法进行分离,分别保存在不同的数据表中。从而可以部署在不同的服务器上。

·按id的奇偶性来进行划分,还可以 ,划分10个分区。

·分区索引字段,一般会用主键或外键

·分区算法:哈希算法、范围、映射关系(推荐)。

(6)      分区反向代理

Spock proxy,通过它能够访问多个MYSQL水平分区。

原文来自:mysql优化 http://www.redyun.net/technology/37.html

红云案例Case