数据库性能优化

1.什么是数据库优化
mysql的性能优化就是通过合理安排资源，调整系统参数使得mysql运行更快，更节省资源。mysq数据库优化的方面有很多，比如查询速度，更新速度，服务器等等，但是要保证一个原则：减少系统的瓶颈，减少资源的占用，增加系统的反应速度。
可以使用show status like 'value';查询mysql数据库的性能参数

connections:连接mysql服务器的次数
uptime:mysql服务器的上线时间
slow_queries:慢查询次数
com_select:查询操作的次数
com_insert:插入操作的次数
com_update:更新操作的次数
com_delete:删除操作的次数

2.优化查询
（1）分析查询语句
使用explain和describe语句

explain [extended] select  select_option

使用extended关键字，explain语句将产生附加信息；select_option是select语句的查询选项，包括from where子句等

id:SELECT的标识符，这是select的查询序列号。
select_type:是select的类型，其中SIMPLE表示简单查询；PRIMARY表示主查询或者是最外层的查询语句；UNION表示连接查询的第二个或者后面的查询语句；DEFENDENT UNION表示连接查询中的第二个或者后面的SELECT查询语句，取决于外面的查询；UNION RESULT表示连接查询的结果；SUBQUERY表示子查询的第一个SELECT语句；DEPENDENT SUBQUERY，子查询的第一个SELECT，取决于外面的查询；DERIVED，导出表的SELECT。
table:查询的表名
type:表示表的连接类型。其中system是仅有一行的系统表，是const连接类型的一个特例；const数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待，const表查询的速度很快，因为只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的组合。
eq_ref:对于每个来自前面的表的行组合，从该表中读取一行。当每一个索引的所有部分都在查询中使用并且索引是UNIQUE或者PRIMARY KEY时，就可以使用这种类型，它也可以使用于“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的列的表达式。
ref:对于来自前面的表的任意组合，将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE，也不是PRIMARY KEY的情况，或者查询中使用了索引咧的左子集（索引中左边部分列的组合），ref可以用于=或者<=>操作符的带索引的列。
ref_or_null:连接类型如同ref，但是添加了mysql可以专门搜索包含NULL值的行，在解决子查询中经常使用该连接类型的优化。
index_merge:该链接类型表示使用了索引合并优化的方法，在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
unique_subquery:是一个索引查找函数，可以完全替换子查询，效率更高。
index_query:类似于unique_query，可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引
value in(select key_column from single_table where some_expr)
range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，key_len包含所使用索引的最长关键元素。当使用=,<>,>,>=,<=,<,is null,<=>,between或者In操作符，用常量比较关键字列时，类型为range.
index:连接类型与ALL相同，除了只扫描索引树，通常比ALL快，因为索引文件通常比数据文件小。
ALL:对前面表的任意行组合，进行完整的表的扫描，如果表是第一个没有标记const的表，这样不好，并且其他情况下很差，通常可以增加更多的索引来避免使用ALL连接。

• possible_key:指出mysql使用哪个索引在该表中找到行。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用某些咧或适合索引的列来提高查询性能。如果是这样，可以创建合适的索引来提高查询的性能。
• key:表示查询实际使用到的索引，如果没有选择索引，该列的值是NULL，要想强制mysql使用或者忽略possible_key中的索引，在查询中使用FORCE INDEX,USE INDEX或者IGNORE INDEX
• key_len:表示mysql选择的索引字段按照字节计算的长度，如果键为NULL，则长度为NULL,通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段。
• ref:表示使用mysql哪个列或常数与索引一起来查询记录。
• rows:显示mysql在表中进行查询时必须检查的行数。
• extra:该列mysql在处理查询的时候的详细信息。
  （2）DESCRIBE和EXPLAIN一样

DESCRIBE SELECT select_options

（3）.索引对查询速度的影响
mysql中提高性能的一个最有效的方式就是对数据表设计合理的索引，索引提供了高校访问数据的方法，并且加快了查询的速度。如果查询时没有使用索引的话，就需要对表中的记录进行扫描，在数据特别多的情况下，这样查询的速度就会很慢，但是如果建立索引，通过索引来查询的话，查询语句就可以根据索引很快的定位到待查记录，从而减少查询记录的次数。
（4）.使用索引查询
使用索引可以提高查询速度，但是也不是所有的索引都可以提高速度，有存在特殊的情况：
（a）在使用like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有当"%"不在第一个位置，索引才会起作用。
（b）mysql可以给多个字段创建索引，一个索引可以包含16个字段。对于多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
（c）查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引，否则查询不使用索引。
（5）.优化子查询
子查询可以一次完成很多逻辑需要多个步骤才能完成的SQL操作。子查询虽然可以使用查询语句很灵活，但是执行效率不高，执行子查询时，查询完毕后在撤销这些临时表。因此，子查询的速度回受到一定的影响。如果查询的数据量比较大，这种影响也会增大。
在mysql中可以使用连接的方式代替子查询，连接查询不需要建立临时表，它的速度比子查询要快，如果查询中使用索引的话，性能会更好。连接的效率高是因为mysql不需要再内存中创建临时表来完成查询工作。
3.优化数据库结构
（1）把数据库表中自多较多的表，若存在一些字段的使用平率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率很低的字段的存在而变得缓慢。
（2）对于经常联合查询的表，可以建立中间表来提高查询效率，通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询的效率。
（3）增加冗余字段
（4）优化插入记录的速度
插入记录时，影响插入速度的主要是索引、唯一性校验、一次性插入记录条数等。
下面总结几种针对MyISAM引擎优化方法：
（a）禁用索引
对于费控表，插入记录时，mysql会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录速度，对于这种情况，可以以在插入记录之前禁用索引，插入后在重启索引。
禁用索引：alter table table_name disable keys
开启索引：alter table table_name enable keys
针对空表批量导入数据，则不需要进行此操作，因为MyISAM存储引擎的表是在导入数据后才建立索引的。
（b）禁用唯一性检查

set unique_checks=0;(禁用)
set unique_checks=1;(开启)

（c）使用批量插入
（d）使用load data infile批量导入
（5）分析表
主要分析关键字的分布，语法如下：

analyze [local|no_write_to_binlog] table tb1_name[,tb1_name]...

LOCAL关键字是NO_WRITE_TO_BINLOG关键字别名，二者都是执行过程不写入二进制日志，tb1_name是分析表的表名，可以为一个或者多个
在分析过程中，数据库会自动对表加上一个只读锁，在分析期间只能读取表中的记录，不能更新和插入记录，ANALYZE TABLE语句能够分析InnoDB,BDB,MyISAM类型的表。

table:表示分析表的表名
Op:表示执行的操作。
msg_type:信息的类型，通常有status,info,note,warning,error
msg_text:显示信息。
（6）检查表
mysql中可以使用check table语句来检查表，check table语句能够检查InnoDB和MyISAM类型的表是否存在错误，对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据，并且，check table也可以检查视图是否有错误存在。

check table tb_name[,tb2_name]...[option]...
option={quick|fast|medium|extended|changed}

quick:不扫描行，不检查错误连接
fast:只检查没有被正确关闭的表
changed:只检查上次检查后被更改的表和没有被正确关闭的表
medium:扫描行，以验证被删除的连接是有效的，也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点
extended:对每行的所有关键字进行一个全面的关键字查找，这可以确保表是100%一致的，但是花费时间较长。
option只对MyISAM类型的表有效，对于InnoDB类型的表无效。CHECK TABLLE语句在执行过程中也会给表加上只读锁。
（7）优化表
只对InnoDB和MyISAM类型的表有效，但是只能优化表中的VARCHAR,BLOB,TEXT类型的字段。

optimize [local|no_write_to_binlog] table tb_name [,tb1_name]...

通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片，它在执行的过程中也会被加上只读锁。
4.优化mysql服务器
（1）服务器硬件优化

• 配置较大的内存
• 配置高速磁盘系统，减少读盘的等待时间，提高响应速度
• 合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
• 配置多处理器，mysql是多线程数据库，多处理器可同时执行多个线程。