MySQL优化

来源:http://www.prospettivedarte.com 作者:计算机教程 人气:157 发布时间:2019-05-11
摘要:聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引,且一张表只允许存在一

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

表的优化

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

在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

列类型选择

  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 定长一个字节


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

因此,MYSQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。下面,我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构

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.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、必须回行,只能取到位置,还需靠这个位置去数据区取数据。
  2. btree索引的常见误区
    2.1 在where条件常用的列上都加上索引
    例:where cat_id = 4 and price > 100;
    误:在cat_id和price上都加上索引。
    原因:只能用上cat_id或price索引,因为是独立的索引,同时只能用上一个。
    2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用
    误:多列索引上,索引发挥作用,需要满足左前缀要求。

    皇牌天下投注网 1

    多列索引的发挥示意图

用到 = 时,表示用了这个索引,用了 =
以外的,这个索引只用了一部分,其后面的索引不能被利用。  
like "xxx%" 这个索引被用上了  
like "%xxx" 这个索引没有被用上  
只有上一个索引被完全用上,下一个索引才有可能被用上。  
我们称之为***左前缀原则***。  
**索引问题(注意联合索引的顺序!)**  

![](https://upload-images.jianshu.io/upload_images/8648067-70da61c3b29784a7.png)

索引问题


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

皇牌天下投注网 2

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

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

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

皇牌天下投注网 3

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

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

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

myisam的索引存储图如下,可以看出,无论是id还是cat_id,下面都存储有执行物理地址的值。通过主键索引或者次索引来查询数据的时候,都是先查找到物理位置,然后再到物理位置上去寻找数据。

聚簇索引和非聚簇索引

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

皇牌天下投注网 4

myisam

myisam,news表为例
有3个文件

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

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

皇牌天下投注网 5

innodb

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

注意:innodb来说

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

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

皇牌天下投注网 6

聚簇索引的页分裂

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

测试结果图

皇牌天下投注网 7

测试结果图

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建立索引

皇牌天下投注网 8

分析索引使用

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

本文由皇牌天下投注网发布于计算机教程,转载请注明出处:MySQL优化

关键词:

上一篇:前端开发总结

下一篇:没有了

最火资讯