MySql优化分析

原理

MYSQL逻辑分层：连接层服务层引擎层存储层
InnoDB(默认) ：事务优先（适合高并发操作；行锁）
MyISAM ：性能优先（表锁）

SQL优化

编写过程：

sql select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit ..
解析过程：

sql from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...

索引分类

主键索引：不能重复。id 不能是null
唯一索引：不能重复。id 可以是null
单值索引：单列， age ;一个表可以多个单值索引,name。
复合索引：多个列构成的索引（相当于二级目录： z: zhao） (name,age) (a,b,c,d,...,n)

分析SQL

分析SQL的执行计划 : explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员知道自己编写的SQL状况
MySQL查询优化其会干扰我们的优化
explain字段

参数	说明
id	编号
select_type	查询类型
table	表
type	类型
possible_keys	预测用到的索引
key	实际使用的索引
key_len	实际使用索引的长度
ref	表之间的引用
rows	通过索引查询到的数据量
Extra	额外的信息

例子数据

create table course

(

cid int(3),

cname varchar(20),

tid int(3)

);

create table teacher

(

tid int(3),

tname varchar(20),

tcid int(3)

);

create table teacherCard

(

tcid int(3),

tcdesc varchar(200)

);

insert into course values(1,'java',1);

insert into course values(2,'html',1);

insert into course values(3,'sql',2);

insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);

insert into teacher values(2,'tw',2);

insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;

insert into teacherCard values(2,'twdesc') ;

insert into teacherCard values(3,'tldesc') ;

id（编号）:

id值相同，从上往下顺序执行

--查询教授SQL课程的老师的描述（desc）

explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid

and t.tcid = tc.tcid and c.cname = 'sql' ;

id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层再查外层)

--将以上 多表查询 转为子查询形式：

explain select tc.tcdesc from teacherCard tc where tc.tcid =

(select t.tcid from teacher t where  t.tid =

   (select c.tid from course c where c.cname = 'sql')

);

id值有相同，又有不同： id值越大越优先；id值相同，从上往下顺序执行

--子查询+多表：

explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid

and t.tid = (select c.tid from course c where cname = 'sql') ;

select_type（查询类型）:
- PRIMARY:包含子查询SQL中的主查询（最外层）
- SUBQUERY：包含子查询SQL中的子查询（非最外层）
- simple:简单查询（不包含子查询、union）
- derived:衍生查询(使用到了临时表)
- union:
  - a.在from子查询中只有一张表
```
    explain select  cr.cname 	from ( select * from course where tid in (1,2) ) cr ;
```
  - b.在from子查询中，如果有table1 union table2 ，则table1 就是derived,table2就是union
```
    explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;
```
- union result :那些表之间存在union查询
type（索引类型、类型）
- 例子数据
```
    create table test01

    (

        tid int(3),

        tname varchar(20)

    );

    insert into test01 values(1,'a') ;

    commit;

    --增加索引

    alter table test01 add constraint tid_pk primary key(tid) ;

    explain select * from (select * from test01 )t where tid =1 ;
```
- 执行效率：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- 常用的类型：system>const>eq_ref>ref>range>index>all
- 要对type进行优化的前提：有索引,其中：system,const只是理想情况；实际能达到 ref>range;
- system（忽略）: 只有一条数据的系统表；或衍生表只有一条数据的主查询
- const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引（类型与索引类型有关）
- eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）
- ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
- range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为无索引all)
- index：查询全部索引中数据
- all：查询全部表中的数据
possible_keys
- 可能用到的索引，是一种预测，不准
- 如果 possible_key/key是NULL，则说明没用索引
key
- 实际使用到的索引
key_len
- 作用：用于判断复合索引是否被完全使用（a,b,c）
ref
- 注意与type中的ref值区分，作用：指明当前表所参照的字段
rows
- 被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)
Extra
- using filesort ：性能消耗大；需要“额外”的一次排序（查询）。常见于 order by 语句中

MySql最大连接数

mysql的最大连接数默认是100, 最大可以达到16384，可以通过max_connections设置

对MySQL语句性能优化的16条经验

为查询缓存优化查询
EXPLAIN 我们的SELECT查询(可以查看执行的行数)
当只要一行数据时使用LIMIT 1
为搜索字段建立索引
在Join表的时候使用相当类型的列，并将其索引
千万不要 ORDER BY RAND ()
避免SELECT *
永远为每张表设置一个ID
可以使用ENUM 而不要VARCHAR
尽可能的使用NOT NULL
固定长度的表会更快
垂直分割
拆分打的DELETE或INSERT语句
越小的列会越快
选择正确的存储引擎
小心 "永久链接"

MyISAM和InnoDB比较

	MyISAM	InnoDB
事务	不支持	支持
数据行锁定	不支持，只有表锁定	支持
外键约束	不支持	支持
表空间大小	相对小	相对大
全文索引	支持	不支持
关注点	性能（select）	事务

原理

SQL优化

索引分类

分析SQL

MySql最大连接数

对MySQL语句性能优化的16条经验

MyISAM和InnoDB比较

相关文章