Mysql 优化

Mysql优化的一般步骤：

1、通过show status了解服务器状态信息

以下几个参数是针对Innodb存储引擎计数：

Innodb_rows_read　select查询返回的行数；
Innodb_rows_inserted执行Insert操作插入的行数；
Innodb_rows_updated 执行update操作更新的行数；
Innodb_rows_deleted　执行delete操作删除的行数。

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少.

Mysql服务器的连接信息
慢查询信息

2、定位执行效率低的SQL语句

可以通过慢查询日志定位那些执行效率较低的sql语句
http://10.64.10.104/index.php?action=report&datasource=localhost

3、explain分析低效SQL的执行计划

EXPLAIN列的解释

select:

simple: 简单的 select （没有使用 union或子查询）

primary: 最外层的 select。

union: 第二层，在select 之后使用了 union。
dependent union: union 语句中的第二个select，依赖于外部子查询
subquery: 子查询中的第一个 select
dependent subquery: 子查询中的第一个 subquery依赖于外部的子查询
derived: 派生表 select（from子句中的子查询）

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system>const>eq_ref>ref>ref_or_null>unique_subquery>index_subquery>range>index>all

const，system 当mysql能对查询的部分就行优化，并且转换成一个常量的时候，它就会使用这种访问类型了。比如你把一行的主键当做where条件放进去，那mysql就可以把它转换成一个常量，然后查询。

system :表只有一行记录（等于系统表）。这是 const表连接类型的一个特例。
const :表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。const 用于在和 primary key 或unique 索引中有固定值比较的情形。

select * from tbl_name where primary_key=1;

eq_ref :从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。它用在索引所有部分都用于做连接并且这个索引是一个primary key 或 unique 类型。

select * from ref_table,other_table where ref_table.key_column=other_table.column; key_column是一个primary key 或 unique 类型

ref 这也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

select * from ref_table,other_table where ref_table.key_column=other_table.column; key_column不是primary key 或 unique索引；

ref_or_null : 这种连接类型类似 ref ，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。

select * from ref_table where key_column=expr or key_column is null ;

unique_subquery: in 子查询唯一索引和主键索引

value in (select primary_key from single_table where some_expr)

ndex_subquery :in 子查询非唯一索引和主键索引

value in (select key_column from single_table where some_expr)

range 这个一般就是在你的where语句中出现了between或者“>”这种符号的时候会出现这个。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

select * fromtbl_name where key_column between 10 and 20;

index type列中出现了index，含义仅仅是局限在扫描全表的顺序是按照索引顺序扫描的，仅仅是按索引顺序去扫描的。它的有点是避免了排序，因为索引就是已经排序好的，缺点就是要承担按照索引次序读取整张表的开销。如下，这个查询中order by id，id是这个表的索引，但是因为没有在where中出现任何的索引列，所以它也只是索引顺去扫描了全表。

select * from table order by index limit 10;

All 这个就是所谓的全表扫描，没有用到任何的index，mysql就是从头到尾把整个表遍历一边，找到所需要的数据行。效率是最差的。

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。

索引优化：

对于以查询为主的数据库，索引显得尤其重要。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

1、索引类型

普通索引
　　这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
- 创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
- 修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
- 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引
　　这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：
- 创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
- 修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
- 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
主键
　　主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
全文索引
　　在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。常用于模糊搜索。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。
复合索引
对于语句： select * from users where area=’beijing’ and age=22; 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，性能有待提高。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

2、选择索引列

1、可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列，在join子句中出现的列，orderby列：

SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引
WHERE firstname='Mike' ##考虑使用索引
AND lastname='Sullivan' ##考虑使用索引

2、MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符开头的情形。

3、只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4、NOT IN和<>以及！=操作都不会使用索引将进行全表扫描。

3、索引缺点

首先，索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。

第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

例子：

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; //创建（uid，x，y）复合索引

语句优化：

1、limit使用

当只要一行数据时使用 LIMIT 1，当你查询表的有些时候，你已经知道结果只会有一条结果，在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

2、少用select *

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

3、 **count(*)优化**

a. select count(*) from world.city where id > 5;
b. select (select count(*) from world.city) – count(*) from world.city where id <= 5;

根据id>5和id<=5扫描行数的数量级选择a，b；当没有where语句的时候直接select count(*) from world.city这样会更快，因为mysql总是知道表的行数。

4、尽量使用数字型字段.

一部分开发人员和数据库管理人员喜欢把包含数值信息的字段设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

使用 ENUM 而不是 VARCHAR ，ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。

5、 union代替or

or将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以使用UNION合并查询： select id from t where num=10 union all select id from t where num=20

6、索引字段上进行运算会使索引失效。

尽量避免在WHERE子句中对字段进行函数或表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：SELECT * FROM T1 WHERE F1/2=100 应改为: SELECT * FROM T1 WHERE F1=100*2

7. 避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符.以及like “%xx”

因为这会使系统无法使用索引,而只能直接搜索表中的数据。例如: SELECT id FROM employee WHERE id != “B%” 优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。在in语句中能用exists语句代替的就用exists.

8. 必要时强制查询优化器使用某个索引

SELECT * FROM T1 WHERE nextprocess = 1 AND processid IN (8,32,45) 改成：

SELECT * FROM T1 (INDEX = IX_ProcessID) WHERE nextprocess = 1 AND processid IN (8,32,45)
则查询优化器将会强行利用索引IX_ProcessID 执行查询。

9、程序中如果一次性对同一个表插入多条数据，比如以下语句：

insert into person(name,age) values(‘xboy’, 14);

insert into person(name,age) values(‘xgirl’, 15);
insert into person(name,age) values(‘nia’, 19);
把它拼成一条语句执行效率会更高.
insert into person(name,age) values(‘xboy’, 14), (‘xgirl’, 15),(‘nia’, 19);

10、ORDER BY语句的MySQL优化：

a. ORDER BY + LIMIT组合的索引优化。如果一个SQL语句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

这个SQL语句优化比较简单，在[sort]这个栏位上建立索引即可。

b. WHERE + ORDER BY + LIMIT组合的索引优化，形如：

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

c.WHERE+ORDER BY多个栏位+LIMIT，比如:

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

对于这个语句，加一个这样的索引(uid,x,y)。

11、使用临时表提供查询性能

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。 SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …