MySQL优化

图片 11

 

2.开启profiling:SET profiling=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两种引擎的索引结构

`id` char(64) NOT NULL,

聚簇索引的页分裂

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

测试结果图

图片 1

测试结果图

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

id int unsigned primary key auto_increment

图片 2

echo “insert success”;

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)

 

`str3` varchar(3000) DEFAULT NULL,

理想的索引

  1. 查询频繁
  2. 区分度高
  3. 长度小
  4. 尽量能覆盖常用查询字段

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

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

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

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

// 计算区分度,找到一个合理的x,来确定取字段前几位作为索引
// 结果越解决1越好,但是也要注意x的长度,越短越好
select ( (select count(distinct left(filed, x) from table)) / (select count(*) from table));
  1. 对于左前缀不易区分的列,建立索引的技巧
    如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 多列索引
从商城的实际业务来看,顾客一般先选择大分类==>小分类==>品牌
因此我们可以

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

innodb的索引存储图如下,我们会发现,主键索引下面直接存储有数据,而次索引下,存储的是主键的id。通过主键查找数据的时候,就会很快查找到数据,但是通过次索引查找数据的时候,需要先查找到对应的主键id,然后才能查找到对应的数据。

1.myisam中, 主索引和次索引都指向物理行(磁盘位置);

索引覆盖

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

图片 3

分析索引使用

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

 

3.如果没有unique,则系统生成一个内部的rowid做主键

索引与排序

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

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

`ver` int(11) NOT NULL DEFAULT ‘0’,

表的优化

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

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

5.创建php文件批量插入数据:

索引碎片与维护

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

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

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

图片 4

PRIMARY KEY (`id`),

聚簇索引和非聚簇索引

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

图片 5

myisam

myisam,news表为例
有3个文件

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

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

图片 6

innodb

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

注意:innodb来说

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

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

nnodb的主索引文件上
直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).

PRIMARY KEY (`id`),

索引优化策略

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

  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 在多列上建立索引后,查询哪个列,索引都将发挥作用
    误:多列索引上,索引发挥作用,需要满足左前缀要求。

    图片 7

    多列索引的发挥示意图

用到 = 时,表示用了这个索引,用了 =
以外的,这个索引只用了一部分,其后面的索引不能被利用。  
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

图片 8

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

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

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

图片 9

分析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,下面都存储有执行物理地址的值。通过主键索引或者次索引来查询数据的时候,都是先查找到物理位置,然后再到物理位置上去寻找数据。

`str2` varchar(3000) DEFAULT NULL,

重复索引和冗余索引

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

注意: innodb来说, 
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键 
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4:
像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

高性能索引策略
:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢。因此对于innodb的主键尽量用整型,而且是递增的整型,如果是无规律的数据,将会产生的页的分裂,影响速度。

论坛经典题目

图片 10

题目

图片 11

分析

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

4.建立数据表:

SQL语句优化

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

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

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

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

条件:在id、ver上有联合索引,表中有几个很长的字段,总共100000条数据

1.首先查看是否开启profiling功能:SHOW VARIABLES LIKE ‘%pro%’;

CREATE TABLE `t8` (

`str3` varchar(3000) DEFAULT NULL,

?>

`str1` varchar(3000) DEFAULT NULL,

原因:

2.由于innodb是聚簇索引,主索引id文件上,存放了该行的数据,当表中某个字段的数据很大时,在硬盘上一个数据块所能存放的行数就变少,所以数据块变多。当order
by id时,会扫描很多个不同的数据块,导致性能降低。而order by
id,ver为联合索引(次索引),次索引不用扫描很大的数据量,并且只筛选id,产生索引覆盖,所以速度快很多。

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图