MySQL优化

作者: PYM_祺 | 来源:发表于2018-01-07 18:25 被阅读0次

表的优化

定长与变长分离
如id int，占4个字节，char(4)占4个字符长度，也是定长，time每一单元占的字节也是固定的。
核心且常用字段，宜建成定长，放在一张表中。
而varchar, text, blob，这种变长字段，适合单放一张表，用主键与核心表关联起来。
常用字段和不常用字段分离
需要结合具体业务来分析，分析字段的查询场景，查询频度低的字段，单拆出来。
在1对多，需要关联统计的字段上，添加冗余字段
可以将需要连表查询的数据，作为一个冗余字段添加到主表中。

列类型选择

字段类型优先级 (特点：定长 > 变长)
整型 > date, time > enum, char > varchar > blob, text
整型：定长，没有国家、地区之分，没有字符集差异。
比如tinyint 1,2,3,4,5 <==> char(1) a,b,c,d,e 都是1个直接，但是order by排序，前者块。
原因：后者需要考虑字符集与校对集（就是排序规则）
time:定长，运算快，节省空间；考虑时区，写SQL不方便 where > '2018-01-01'
enum:能起到约束目的，内部还是整型。
char:定长，考虑字符集和校对集。
varchar:变长，考虑字符集和校对集，速度慢。
text/blob:无法使用内存临时表（排序操作只能在磁盘上进行）。
附：关于date/time的选择，直接选 int unsigned not null 存储时间戳。

性别：以UTF8为例
char(1)，3个字节
enum('男","女"); // 内部转换成数字来存，多了一个转换过程
tinyint(1); // 0 1 2 定长一个字节

够用就行，不要慷慨
原因：大的字段浪费内存，影响速度。
尽量避免用NULL
原因：NULL不利于索引，要用特殊的字节来标注。

mysql创建单个和联合索引

首先创建一个表：

create table t1 (
    id int primary key,
    username varchar(20),
    password varchar(20)
);

创建单个索引的语法：

create index 索引名 on 表名（字段名）

索引名一般是：表名_字段名
给id创建索引：

create index t1_id on t1(id);

创建联合索引的语法：

create index 索引名 on 表名（字段名1，字段名2）

给username和password创建联合索引：

create index t1_username_password on t1(username,password)

索引优化策略

查询数据时，会先搜索索引，找到对应的索引，再通过这个索引找到数据表中的具体位置，取出数据。索引可以加速原先顺序查找的速度。

索引类型
1.1 B-tree索引
注：名叫Btree索引，都用的平衡树，但在具体实现上，各引擎稍有不同。
myisam,innodb中默认用的是B-tree索引。
1.2 hash索引
在memory表里，默认是hash索引（放在内存中），hash的理论时间复杂度为O(1)。
疑问：既然hash的查找如此高效，为什么不都用hash索引？
答： 1、hash可能会出现重复的值。2、hash算出来的值比较随机，磁盘上不见得有位置可以存放。3、hash虽然找具体的值很快，但是如果想找一个范围的值就难了。4、无法利用前缀索引。5、排序也无法优化。6、必须回行，只能取到位置，还需靠这个位置去数据区取数据。
btree索引的常见误区
2.1 在where条件常用的列上都加上索引
例：where cat_id = 4 and price > 100;
误：在cat_id和price上都加上索引。
原因：只能用上cat_id或price索引，因为是独立的索引，同时只能用上一个。
2.2 在多列上建立索引后，查询哪个列，索引都将发挥作用
误：多列索引上，索引发挥作用，需要满足左前缀要求。
多列索引的发挥示意图
用到 = 时，表示用了这个索引，用了 = 以外的，这个索引只用了一部分，其后面的索引不能被利用。
like "xxx%" 这个索引被用上了
like "%xxx" 这个索引没有被用上
只有上一个索引被完全用上，下一个索引才有可能被用上。
我们称之为左前缀原则。
索引问题（注意联合索引的顺序！）
索引问题
A : 用了 c1 c2 c3 c4
B：用了 c1 c2 c3（排序时用到）
C：用了 c1
D：用了 c1 c2 c3
E：用了 c1 c2 c3

分析SQL语句索引使用情况 explain

explain select * from t4 where c1=3 and c2=4 and c4>5 and c3=2 \G

分析SQL语句索引使用情况结果1

其中key_len = 4，说明4个索引都用上了。
再试试这个语句

explain select * from t4 where c1=3 and c2=4 and c4=5 order by c3 \G

分析SQL语句索引使用情况结果2

key_len = 2，说明c1,c2在查询时被用上，但是c3在排序的时候其实也被用上了。

一道面试题：
有商品表，有主键，goods_id，栏目列cat_id，价格price
说：在价格列上已经添加了索引，但按价格查询还是很慢，问可能是什么原因，怎么解决？
答：在实际场景中，一个电商网站的商品分类很多，直接在所有商品中，按价格查询商品，是极少的，一般客户都是来到分类下，然后再查。
改正：去掉单独的price列的索引，加（cat_id，price）复合索引，再查询。
如果根据日志统计，发现好多人这样查：电脑=>某某品牌=>价格 index（cat_id，brand_id，price）

聚簇索引和非聚簇索引

myisam与innodb引擎，索引文件的异同

myisam

以myisam，news表为例
有3个文件

news.frm
news.myd 数据文件
news.myi 索引文件
索引文件和数据文件分离的，叫非聚簇索引，索引myisam是非聚簇索引，从索引上找到后还要去数据里面取。

对于innodb，其索引叶子节点很大，里面还存放了一些数据信息。

innodb
找到数据后，不用回到数据文件（这个过程称为：回行）找数据，这种叫做聚簇索引。
innodb的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用（为了防止次索引叶子节点过大，也与主键索引的数据重复）。
myisam中，主索引和次索引，都指向物理行（磁盘位置）。

注意：innodb来说

主键索引，既存储索引值，又在叶子中存储行的数据。
如果没有主键（primary key），则会unique key作主键。
如果没有unique，则系统生成一个内容的rowid做主键。
像innodb中，主键的索引结构中，既存储了主键值，又存储了行数据，这种结构成为“聚簇索引”

聚簇索引
优势：根据主键查询条目比较少时，不用回行（数据就在主键节点下）。
劣势：如果碰到不规则数据插入时，造成频繁的页分裂。

聚簇索引的页分裂

为什么会产生页分裂？
这是因为聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态（插入主键不规律，树状结构要多次变化）。从而导致了页分裂，因为叶子节点很重，所以速度会很慢。
测试：
创建2张表

create table t8(
    id int primary key,
    c1 varchar(500),
    c2 varchar(500),
    c3 varchar(500),
    c4 varchar(500),
    c5 varchar(500),
    c6 varchar(500)
) engine innodb charset utf8;
create table t9(
    id int primary key,
    c1 varchar(500),
    c2 varchar(500),
    c3 varchar(500),
    c4 varchar(500),
    c5 varchar(500),
    c6 varchar(500)
) engine innodb charset utf8;

写一个php脚本，用于插入1W条无规则的主键数据和1W条规则的主键数据，来看看区别。

<?php
set_time_limit(0);
$conn = mysql_connect('localhost','root','1234');
mysql_query('use test;');

//自增长主键
$str = str_repeat('a', 500);
$startTime = microtime(true);
for($i=1;$i<=10000;$i++){
    mysql_query("insert into t8 values($i,'$str','$str','$str','$str','$str','$str')");
}
$endTime = microtime(true);
echo $endTime-$startTime.'<br/>';

//无序的主键
$arr = range(1, 10000);
shuffle($arr);
$startTime = microtime(true);
foreach($arr as $i){
    mysql_query("insert into t9 values($i,'$str','$str','$str','$str','$str','$str')");
}
$endTime = microtime(true);
echo $endTime-$startTime.'<br/>';

测试结果图

测试结果图
1W条规则的数据：998秒 = 16分钟
1W条不规则的数据：1939秒 = 32分钟
结论：
聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值, (不要用随机字符串或UUID)，否则会造成大量的页分裂与页移动。在使用InnoDB的时候最好定义成：

id int unsigned primary key auto_increment

索引覆盖

对于myisam来说，是非聚簇索引，要查具体数据时，需要回行，去到磁盘上取数据，这会拖慢速度，如何让它不用回行呢？我们可以使用索引覆盖。

解释一：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

索引覆盖举例1
索引覆盖是指建索引的字段正好是覆盖查询条件中所涉及的字段,这里需要注意的是,必须是从第一个开始覆盖,比如:

索引字段	条件字段	有没有覆盖
a,b,c	a,b	覆盖了
a,b,c	b,c	没有覆盖

例子： select<字段A,B….> from <数据表 T> where <条件字段C>。在MySQL中建立覆盖索引采用Create index idx on T(C,A,B)，建立组合索引时，字段的顺序很重要，要将条件字段C放在组合索引的第一位，把它做为在索引的上层结构的主要排序对象，且仅有它包含统计数据，也就是非子叶层查找出符合的记录，然后在存放有其他字段记录的子叶层读取所需要的数据（也就是以字段内容CAB建立索引，我们通过C找到后，所需要的数据AB都在这个索引上，不需要再回行去取数据；索引的顺序很重要，如果前面的利用不上，后面的也无法利用）。
索引覆盖举例2
我们给name,age建立了索引，但是没有给intro建立索引

分析索引使用
因为name为索引，值可以自己取到，不需要回行。
而intro没有索引，需要回行去取值。
当Extra：Using index的时候，没有回行，速度更快。
小结：索引覆盖可以大大提高查询速度，在大数据量的时候尤其明显。

论坛经典题目

题目

分析

因为innodb主索引的叶子很大，所以搜索慢于联合索引id,ver，联合索引慢是因为它的叶子节点只是存放主索引的引用。
所以叶子节点的大小也是影响索引的速度，说明这张表的设计不太合理。

理想的索引

查询频繁
区分度高
长度小
尽量能覆盖常用查询字段

区分度高：100万用户，性别基本上男、女各为50w，区分度就低。
索引长度直接影响索引文件的大小，影响增删改查的速度，并见解影响查询速度（占用内存多）。

针对列中的值，从左往右截取部分，来建立索引
1：截得越短，重复度越高，区分度越小，索引效果不好
2：截得越长，重复度越低，区分度越高，索引效果越好，但带来的影响也越大===>增删改变慢，并间接影响查询速度。

所以我们要在区分度 + 长度二者上，取得一个平衡。

计算区分度
惯用手法：截取不同长度并测试其区分度。

// 计算区分度，找到一个合理的x，来确定取字段前几位作为索引
// 结果越解决1越好，但是也要注意x的长度，越短越好
select ( (select count(distinct left(filed, x) from table)) / (select count(*) from table));

对于左前缀不易区分的列，建立索引的技巧
如url列
http://www.baidu.com
http://www.mongodb.org
列的前11个字符都是一样的，不易区分，可以用如下2个办法来解决
2.1 把列内容倒过来储存，并建立索引
com.baidu.www//:http
org.mongodb.www//:http
2.2 伪hash索引效果
同时存url_hash列

create table t10 (
    id int primary key,
    url char(60) not null default ''
);

insert into t10 values
(1, 'http://www.baidu.com'),
(2, 'http://www.sina.com'),
(3, 'http://www.sohu.com.cn'),
(4, 'http://www.onlinedown.net'),
(5, 'http://www.gov.cn');
// 增加一个新字段，这个字段的值是url经过hash之后的值
// crc32（$str）能把一个字符串转换成一个32位的无符号整数
// 我们对这个hash值来加索引
alter table t10 add urlcrc int unsigned not null;

2.3 多列索引
从商城的实际业务来看，顾客一般先选择大分类==>小分类==>品牌
因此我们可以

index(cat_id,brand_id)建立索引
index(cat_id,shop_price)建立索引
index(cat_id,brand_id,shop_price)建立索引，这个索引和1中的前2个一样，因此就不用建立1中的索引

索引与排序

排序可能发生2中情况：
1：对于覆盖索引，直接在索引上查询时，就是有顺序的，using index，在innodb引擎中，沿着索引字段排序，也是自然有序的，对于myisam引擎，如果按某索引字段排序，如id，但取出的字段有未索引字段，如goods_name，myisam的做法，不是索引=>回行，而是先取出所有行，再进行排序。
2：先取出数据，形成临时表做filesort再排序（文件排序，但文件可能在磁盘上，也可能在内存中）
搜索和排序的字段不一致时，可能出现filesort
我们的争取目标----取出来的数据本身就是有序的！利用索引来排序。（也就是说我们的sql语句在排序的时候，最好能利用上索引，我们可以用explain这个sql语句，最好不要出现 using filesort）

重复索引和冗余索引

重复索引：是指在同一个列，或者顺序相同的几个列，建立了多个索引，成为重复索引，重复索引没有任何帮助，只会增大索引文件，拖慢更新速度，去掉。
冗余索引：是指两个索引所覆盖的列有重叠（但是前后顺序不一样），称为冗余索引。

索引碎片与维护

在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片，我们可以通过一个nop操作（不产生对数据实质影响的操作），来修改表。
比如：表的引擎为innodb，可以

alter table xxx engine innodb;
// 或者
optimize table xxx；

注意：修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对其，这个过程，如果表的行数比较大，也是非常耗费资源的操作，所以不能频繁的修复。
如果表的update操作很频繁，可以按周/月来修复；
如果不频繁，可以更长的周期来做修复。

SQL语句优化

1： sql语句的时间花在哪儿？
答：等待时间，执行时间。
这两个时间并非孤立的，如果单条语句执行的快乐，对其他语句的锁定也就少了，所以我们来分析如何降低执行时间。

2：SQL语句执行的时间又花在哪里了？
答：
a:查找==>沿着索引查找，慢者可能全表扫描
b:取出==>查到行后，把数据取出来

3：如何查询快？
答：
a: 查询的快==>联合索引的顺序，区分度，长度
b: 取的快，索引覆盖
c: 传输的少，更少的行和列

切分查询：按数据拆成多次
例：插入10000行数据，每1000条为单位
分解查询：按逻辑把多表连接查询分成多个简单的SQL