浅谈mysql数据库优化

一个数据库的性能，要从设计阶段就规划好，如果从设计阶段就规划好了，上千万数量级的单表，性能也一样很好。下面介绍一下本人的浅显经验。
1. 根据需求确定好数据库引擎
如果设计数据安全，最好选择innodb方式，MyIsam的引擎虽然查询效率高，但是一旦出问题要进行数据修复，是一件让人头疼的事情，innodb支持事物，如果操作不成功，会自动回滚。
在表结构设计时选用最适用的字段，MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义电话号码这个字段时，如果将其设置为CHAR(255)，显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(12)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
2. 查询时尽量适用join代替子查询
这样可以大大提高效率，尤其是join 的字段在索引上的时候，性能会更好。
3. 使用union来代替手动创建临时表。
4. 使用事物来保持数据的一致性
事物的一个作用是当事物在begin和commit之间有错误时，就回回滚，这样就可以保持数据的一致性和完整性，事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
5. 锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户
来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。
6. 如果涉及的表的关联性，那就要用好外键了
7. 使用索引来提高检索速度
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(), MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对哪些字段建立索引呢？一般说来，索引应建立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
8. 优化查询语句
查询语句的优化也能带来很的性能的提高，sql语句的where条件最好在同类型之间进行比较，避免在数据库做数据转换，不要再字段上做算数运算操作，尽量别用like和通配符，这样效率会很低。
9. mysql数据库一些配置参数对数据库性能有很大影响
大家可以根据数据库服务器的性能去配置数据库的参数，下面是我从mysql说明文档上摘取下来的配置参数定义：
back_log：
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout：
mysqld服务器在用Bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout：
一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。
delayed_insert_limit：
在插入delayed_insert_limit行后，INSERT DELAYED处理器将检查是否有任何SELECT语句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size：
应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了，任何进行INSERT DELAYED的客户将等待直到队列又有空间了。
flush_time：
如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout：
服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout。
join_buffer_size：
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）
key_buffer_size：
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据，你将必须为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用LOCK TABLES。
long_query_time：
如果一个查询所用时间超过它(以秒计)，Slow_queries记数器将被增加。
max_allowed_packet：
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。
max_connections：
允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件描述符限制的注释。
max_connect_errors：
如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用FLUSH HOSTS命令疏通一台主机。
max_delayed_threads：
不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样。
max_join_size：
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个WHERE子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length：
在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。
max_tmp_tables：
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length：
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer：
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
sort_buffer：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
table_cache：
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。 
tmp_table_size：
如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，如果你做很多高级GROUP BY查询，增加tmp_table_size值。
thread_stack：
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。
wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。
10. 当到对大表数据进行删除操作时，最好用主键定位，而且这张大表索引越少越好
Mysql官方文档上建议删除GB级数据时最好把不需要删除的数据先插入到结构相同的另外一张新表里面，删除旧表，把新表重新命名为旧表名称，这样删除就很快了。