MySQL性能优化详解

1、Mysql中，可以使用SHOW STATUS语句查询一些Mysql数据库的性能参数。****SHOW STATUS语句语法如下所示：
SHOW STATUS LIKE ‘value’;
其中，value是要查询的参数值，一些常用的性能参数如下：
a、Connections，连接mysql服务器的次数。
b、Uptime，mysql服务器的上线时间。
c、Slow_queries,慢查询的次数。
d、Com_select，查询操作的次数。
e、Com_insert，插入操作的次数。
f、Com_update，更新操作的次数。
g、Com_delete，删除操作的次数。

1 -- 查询mysql服务器的连接次数
2 SHOW STATUS LIKE 'Connections';
3 
4 -- 查询mysql服务器的慢查询次数。慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。
5 SHOW STATUS LIKE 'Slow_queries';

2、分析查询语句。

通过对查询语句的分析，可以了解查询语句执行的情况，找出查询语句执行的瓶颈，从而优化查询语句。mysql中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。
EXPLAIN语句的基本语法如下：
EXPLAIN [EXTENDED] SELECT select_options;
使用EXTENED关键字,EXPLAIN语句将产生附加信息。select_options是select语句的查询选项，包括from where子句等等。
执行该语句，可以分析EXPLAIN后面的select语句的执行情况，并且能够分析出所查询的表的一些特征。
例如：EXPLAIN SELECT * FROM tb_brand;

查询结果进行解释说明：
a、id：select识别符，这是select的查询序列号。id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。id相同，从上往下执行。
b、select_type：标识select语句的类型。
它可以是以下几种取值：
  b1、SIMPLE（simple）表示简单查询，其中不包括连接查询和子查询。
  b2、PRIMARY（primary）表示主查询，或者是最外层的查询语句。
  b3、UNION（union）表示连接查询的第2个或者后面的查询语句。
  b4、DEPENDENT UNION（dependent union）连接查询中的第2个或者后面的select语句。取决于外面的查询。
  b5、UNION RESULT（union result）连接查询的结果。
  b6、SUBQUERY（subquery）子查询的第1个select语句。
  b7、DEPENDENT SUBQUERY（dependent subquery）子查询的第1个select,取决于外面的查询。
  b8、DERIVED（derived）导出表的SELECT(FROM子句的子查询)。
c、table：表示查询的表。
d、type：表示表的连接类型。
下面按照从最佳类型到最差类型的顺序给出各种连接类型。
  d1、system，该表是仅有一行的系统表。这是const连接类型的一个特例。
  d2、const，数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为它们只读一次。const用于使用常数值比较primary key或者unique索引的所有部分的场合。
例如：EXPLAIN SELECT * FROM user WHERE id=1;
  d3、eq_ref，对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或者PRIMARY KEY时候，即可使用这种类型。eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或者一个在该表前面所读取的表的列的表达式。
例如：EXPLAIN SELECT * FROM user,db_company WHERE user.company_id = db_company.id;
  d4、ref对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于索引既不是唯一索引也不是primaey key的情况，或者查询中使用了索引列的左子集，即索引中左边的部分组合。ref可以用于使用=或者<=>操作符的带索引的列。例如：explain select balance from account where name = ‘kun’ #ref
  d5、ref_or_null，该连接类型如果ref，但是如果添加了mysql可以专门搜索包含null值的行，在解决子查询中经常使用该连接类型的优化。
  d6、index_merge，该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。
  d7、unique_subquery，该类型替换了下面形式的in子查询的ref。是一个索引查询函数，可以完全替代子查询，效率更高。
  d8、index_subquery，该连接类型类似于unique_subquery，可以替换in子查询，但是只适合下列形式的子查询中非唯一索引。
  d9、range，只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。key_len包含所使用索引的最长关键元素。当使用=，<>,>,>=,<,<=,is null,<=>，between或者in操作符，用常量比较关键字列时，类型为range。
  d10、index,该连接类型与all相同，除了只扫描索引树。通常比all快，只是扫描索引库而没有扫描具体的数据文件，属于全表扫描的一种。例如select id from account,即只查有索引的列。
  d11、all，对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没有标记const的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用all连接。
e、possible_keys:possible_keys列指出mysql能使用哪个索引在该表中找到行。如果该列是null，则没有相关的索引。在这种情况下，可以通过增加where子句看它是否引起某些列或者适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。
f、key：表示查询实际使用到的索引，如果没有选择索引，该列的值是null，要想强制mysql使用或者忽视possible_key列中的索引，在查询中使用force index、use index或者ignore index。
g、key_len：表示mysql选择索引字段按照字节计算的最大存储长度，而不是索引字段实际占用的长度，如果键是null，则长度为1。（①假如某个字段长度为10并且为char类型并不允许为null的索引字段的key_len为30（utf-8占3个字节，3x10=30），允许为空的key_len为31,多了一个null。同样地varchar不允许为null得key_len为32（3x10+2=32,2为可变长度），varchar允许为null的key_len为33）

h、ref:表示使用那个索引类的详细记录,const表示常量。


i、rows:显示mysql在表中进行查询必须检查的行数。
j、Extra:该列mysql在处理查询时的详细信息。

3、DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。DESCRIBE语句的语法形式如下所示：DESCRIBE SELECT * FROM tb_brand。DESCRIBE可以缩写成DESC。

4、索引对查询速度的影响

mysql中提高性能的最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快了查询的速度。因此，索引对查询的速度有着至关重要的影响。使用所以可以快速的定位到表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。如果查询的时候没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询的记录，从而减少查询的记录数，达到提高查询速度的目的。

举例说明，使用索引和不使用索引的区别：

不使用索引：rows列的值是26，说明此查询语句扫描了26条记录。

使用索引：rows列的值是1，说明此查询语句扫描了1条记录。查询速度自然变快了。这种方法也可以判断自己添加的所以是否起了作用。

5、使用索引查询，几种特殊情况。
　　索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起到作用。下面是几种比较特殊的情况。在这些情况下，有可能使用带有索引的字段查询时，索引并没有起到作用。
　　
5.1、使用like关键字的查询语句。在使用like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不会起作用（但是可以使用覆盖索引解决）。只有"%"不在第一个位置，索引才会起到作用。

使用模糊查询，”%“在前面的结果，显示索引没有起了作用了的。

使用模糊查询，”%“在后面的结果，显示索引起了作用了的。

5.2、使用多列索引的查询语句。mysql可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中第1个字段的时候，索引才会被使用。（最左前缀原则）

多列所有的第一个字段作为查询条件的时候，发现索引起了作用了的:

5.3、使用or关键字的查询语句。使用语句的查询条件中只有or关键字，且or前后的两个条件中的列都有索引时，查询中才使用索引（建议使用Union联合查询）。否则，查询将不适用索引。

使用name索引，sex索引，查询出符合条件的。

1、尽量全值匹配
2、最左前缀原则


5、如果某个字段使用了范围查询，该字段以后的索引将会失效。
6、尽量使用覆盖索引。
7、不等于要慎用，有可能会导致全表扫描；如果使用了覆盖索引，使用不等于不会索引不会失效
8、（1）表定义某字段为不允许为null，当查询的时候使用null或者not null索引都会失效，解决方法：为了使用not null可以使用覆盖索引。
（2）表定义某字段允许为null，当查询的时候使用null不会导致索引失效；当查询使用not null会导致索引失效，解决方法：为了使用not null可以使用覆盖索引。



6、慢查询与SQL优化

（1）启动慢查询

set GLOBAL slow_query_log = on



常用命令：

（1）show engines;  // 查看mysql支持的存储引擎
（2）show variables like '%storage_engine%' //查看当前mysql默认的存储引擎
（3）show variables like '%per_table%'  //查看是否开启独立表空间，mysql5.5以及之前都是使用系统表空间。
（4）show create table tableName;  // 查看创建表的SQL语句以及存储引擎
（5）show variables like '%datadir%'  //查看表的数据文件存在磁盘的位置
（6）批量插入到数据库

	select * into OUTFILE 'F:\\account.txt' from account
	
	create table account2 select * from account where 1=2
	
	load data infile 'F:\\account.txt' into table account2

---

欢迎关注公众号Java技术大本营，会不定期分享BAT面试资料等福利。

---