mysql学习之性能优化

mysql性能优化

本文所谈的性能优化不涉及 硬件和操作系统层级，主要涉及sql数据库（其实是数据库中的表）储存引擎（MyIsam、InnoDB、Csv 、Memory等）选择，sql语句的优化。

存储引擎选择

储存引擎选择 取决于具体的应用场景，设置储存引擎的具体方法：

1、配置文件 my.ini 中修改

在 [mysqld] 下面添加行
default-storage-engine=InnoDB
然后重启服务，则数据库默认存储引擎为InnoDB。

2、创建表的时候设置

CREATE TABLE `tbl123` (
  `id` int(11) DEFAULT NULL,
  `Name` varchar(15) DEFAULT NULL,
  `email` varchar(20) DEFAULT 'test@test.com',
  `age` tinyint(4) DEFAULT '18',
  KEY `index1` (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

3、通过修改表属性设置

alter table tbl123 ENGINE=InnoDB;

查看修改结果
show create table tbl123;

各引擎特点：

MyIsam：文件占用空间小，使用数据查询，表级锁，适合数据读操作。

InnoDB：文件大，支持事务（特性：A（原子性）、C（一致性）、I（隔离性）、D（持久性））和外键（Foreign Key）约束，行级锁，并发支持好，适合数据写操作，从Mysql5.5版本开始，InnoDB是默认的表存储引擎。

Csv：在数据保存在文件中，不支持字段 自增和非空，操作速度慢，效率较低。

Memory：存取在内存中实现，速度快，但是不适合大量数据的操作，而且只能进行临时数据储存。

以上两种引擎的默认索引结构为B树，而Memory引擎支持hash（默认）和B树两种索引结构。

语句优化

sql语句执行（解析器解析）顺序举例：

select a.name, b.mail from tbl1 as a join class1 as b on a.id=b.cid where a.id>5 order by b.cid limit 5,6;

以上语句执行顺序为：

from 语句
on 条件
join 语句
where 语句
（group by 结果分组，改变表引用
having 分组条件）
select 语句
order by 排序
limit 数据分页

为了检测sql语句的执行效率，需要引入 sql语句执行过程的 分析器----explain。
例如：
explain select * from tbl123;

结果一般类似以下：

+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t2     | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |                                |
|  1 | SIMPLE      | tbl123 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+--------+------+---------------+------+---------+------+------+--------------------------------+

其输出信息包括：

1、sql语句类型（select_type）

SIMPLE：简单SELECT查询
PRIMARY：最外面的SELECT，join类型的查询
SUBQUERY：子查询中的第一个SELECT（不含UNION）
UNION：UNION（联合）查询的第二个或其后的SELECT语句
UNION RESULT：UNION 查询的结果

2、语句执行顺序（id）

id相同时，执行顺序从上至下；
id不同时，id越大，越早执行。

3、索引使用情况

possible_keys：可用索引
key：实际使用的索引
key_len：索引使用的字段字节总数

4、检索行数（rows）和查询的表（table）

5、语句的联接类型（type）

常见的类型如下：

system：使用系统表，查出唯一数据记录
const：根据主键或唯一索引，查出一条唯一数据记录
eq_ref：多表联合查询，关联的字段都是各表的主键
ref：使用全列索引或外键进行查询。
range：范围查询
index：索引查询
ALL：全表搜索

sql语句效率从上到下依次降低。

6、额外信息（Extra）

包含信息：

文件排序（Using filesort）
临时表使用（Using temporary）
使用索引（Using index）
使用条件（Using where）

执行性能优化时，根据explain 语句分析结果，对索引和查询条件进行优化，避免模糊查询（like语句）和全表（select * …）查询，注意复合索引中索引字段的引用顺序，避免索引失效和计算表达式等。