mysql优化

sql

查看存储引擎

查看默认的整个数据库引擎

1
2
show engines;
show variables like '%storage_engine%';

默认为InnoDB

可以为每个特定的表指定引擎。查看指定表引擎：

  1. 查看建表语句

    1
    show create table table_name;

  2. 查看表信息

    1
    show table status from database_name where name = 'table_name'\G

指定引擎

建表时指定引擎

1
2
3
4
5
6
7
8
9
10
11
-- 创建数据库
create database practice default character set utf8;
-- 建表，指定引擎
create table info(
	id int(4) auto_increment,
	name varchar(10),
	age int(2),
	score int(3),
	primary key(id)
) engine=myisam auto_increment=1
default charset=utf8;

修改引擎

1
alter table info engine=innodb;

sql语句解析过程

链接：https://www.cnblogs.com/annsshadow/p/5037667.html

1
2
3
4
-- 编写语句
select distinct...from...join...on...where...group by...having...order by...limit...
-- 执行过程
from...join...on...where...group by...having...select distinct...order by...limit...

sql优化基础

基础的系统设置包含了内存，io调度策略，文件连接数，core file大小

内存：别用swap空间，因为会更加慢

io调度：使用mq-deadline

文件连接数和core file：查看ulimiit -a并修改

sql优化（索引）

sql的优化主要是优化索引。索引的数据结构分为：B数，hash数。mysql默认为B数，通过B数这种数据结构（类似二分法），查找数据不需要循环查找，可节省很多时间。

可以为同一张表的不同字段设置索引，但不是越多越好。索引是一种数据结构，也可以称为表。独立与原表的数据。所以如果需要经常对某些字段进行查询，那么就可以建索引，毕竟索引的数据小很多，查询速度快。

索引的优缺点/适用场景：

  1. 索引需要占据的存储空间大
  2. 提高查询效率（IO）。但是会降低增、删、改的效率
  3. 如果字段会经常更新，那么更新一次，索引需要重新生成一次，耗费时间
  4. 很少使用的列，不必加索引
  5. 表的数据量少，不必加索引

索引分类/创建/删除

建索引的时机

在数据已经导入表后，再建索引。

通过create或者alter创建

  1. 单值：单列为一个索引，不过一个表可以有多个

    1
    2
    3
    create index index_name on table_name(column_name);
    -- 举例
    create index name_index on info(name);
    1
    alter table info add index name_index(name);

  1. 唯一：数据不能重复

    1
    2
    3
    create unique index index_name on table_name(column_name);
    -- 举例
    create unique index name_index on info(name);
    1
    alter table info add unique index name_index(name);

  1. 复合：多列构成的索引，但是查找时只要找到就ok，不一定要全部都用上

    1
    2
    3
    create index index_name on table_name(column1_name,column2_name,...);
    -- 举例
    create index name_age_index on info(name,age);
    1
    alter table info add index name_age_index(name,age);

主键索引

如果字段为primary key主键，那么为主键索引。主键索引与唯一索引的区别：主键索引不能为null，唯一索引可以为null

除了在建表的时候指定，还可以在建完表后

1
ALTER TABLE Employees ADD PRIMARY KEY(ID);

删除索引：

  1. 删除表就会删除索引

    1
    drop table table_name;

  1. 删除索引

    1
    drop index index_name on table_name;

查看索引

1
show index from table_name;

sql性能分析

最主要的是分析sql执行计划，但是也要注意到mysql的优化器（自带）可能会干扰我们的优化语句

explain可以模拟sql优化器执行sql语句，查看sql性能问题

1
2
3
4
5
6
explain select * from info;
+------+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id   | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+------+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|    1 | SIMPLE      | info  | system | NULL          | NULL | NULL    | NULL | 0    | Const row not found |
+------+-------------+-------+--------+---------------+------+---------+------+------+---------------------+

id：编号

  • 当id相等时，执行顺序为从上往下
  • 当id不等时，先执行id大的语句（这种情况为嵌套子查询，里层往外查询）

select_type：查询类型

  • primary：主查询
  • subquery：子查询
  • simple：不包含子查询和联合查询
  • derived：派生查询，在查询的时候用到了临时表
    • 在from子查询中，只有一张表
    • 在from子查询中，有table1 union table2，那么table1为derived
  • union：上面的table2为union
  • union result：table1和table2为union查询

table：查询表

type：索引类型

  • 优化等级：

    1
    system > const > eq_ref > ref > range > index > all

  • 达到ref和range就是比较好的sql语句

  • eq_ref：使用primary key或者unique key作为查询条件或者不存在重复值的某列

  • ref：使用索引列查询，不用like匹配，且查询到的相同数据结果为0-n行

    1
    explain select * from dept_emp where dept_no = 'd001' limit 1,10;

  • range：查询范围的索引列，范围查询中in不一定有效

    1
    explain select * from dept_emp where emp_no < 10108;

  • index：查询全部索引的数据

    1
    explain select dept_no from dept_emp;

  • all：查询表中的所有数据

    1
    explain select * from dept_emp where dept_no like '%d001%';

possible_keys：预测用到的索引

key： 实际用到的索引

key_len：实际使用的索引的数据类型长度

ref：表之间的引用

  • const：值引用

  • NULL：某一个表对应字段没有索引

rows：通过索引查询到的数据条数

extra：

  • using filesort，（where列与order by列不同）性能消耗大，需要额外一次查询。解决方法：创建复合索引
  • using temporary：一般在group by中出现，性能消耗大，用到了临时表。解决方法：需要查询什么列就用什么列分组
  • using index：性能提升，不读取原文件，从索引表中获取数据，不需要回表查询
  • using where：需要回表查询，查询没有索引的数据
  • impossible where：where后面的语句不成立

多表执行顺序

当有多个表联合查询时，每两个表结合会产生迪卡尔集，那么会自动选择数据量小的表先结合，这样对系统内存消耗的较少。

语句优化

  1. 查询列的顺序与where条件列的顺序一致

    1
    select a,b,c from some_table where a=1 and b=2 and c=3;

  2. 复合索引不要跨列使用，且需要从第一个索引开始写，否则后面的索引还是需要回表查询的

    1
    2
    3
    create index a_b_c of some_table(a,b,c);
    -- 索引列c跨索引了，为无效索引会回表查询
    select a,b,c from some_table where a=1 and c=2;

shell终端中文问题

使用mysql客户端在shell中运行时，中文无法显示

1
set names utf8;

set names utf8指定了客户端连接服务器的编码规则为utf8，两者之间传输消息字符时采用的也是utf8，相当于设置以下三个变量：

1
2
3
set character_set_client = utf8;     
set character_set_results = utf8;    
set character_set_connection = utf8;