Mysql设计层优化整理总结

③ 无法利用前缀索引。比如在btree中，Col1列的值“helloworld”，并加上索引，查询xx=helloword，自然可以利用索引，xx=hello，也可以利用索引. (左前缀索引)，但是hash索引不行，因为hash(‘helloword’)和hash(‘hello’)两者的关系仍为随机。

④ 无法对排序进行优化。

⑤ 必须回行，也就是说通过索引拿到数据位置，必须回到表中取数据。

使用索引好处：

可以非常快速定位我们需要找到的信息。

在现实生活中有许多地方使用到索引，例如：公交车站牌、书的目录、办公楼指示牌、查询汉字所在页码等。

索引速度快的原因：

数据表全部“字段”都可以做索引。

在没有索引的时候，sql语句查询会遍历全部的记录信息，找到与条件相符合的。

在有索引的时候是精准定位指定的记录信息，sql语句做数据查询的时候，要通过索引做条件，在索引里边可以通过算法快速、准确地获得记录索引字段及字段对应的物理地址，再通过物理地址去数据表中获得记录的详细信息。

索引类型：

① 主键索引（值不重复，auto_increment自增特性）

② 唯一索引（字段内容不能重复）

③ 普通索引

④ 全文索引（适合给文章字段设置）

⑤ 复合索引

索引的数据结构：

Myisam索引数据结构：

Myisam的数据和索引是分离的，数据结构为“非聚集型”，如下图：

myisam中，主索引和次索引，都指向物理行（磁盘位置）。

Myisam主键索引数据结构原理：

数据表有3个字段：Col1、Col2、Col3，其中Col1是主键：

节点：根节点、分支节点、叶子节点，索引字段内容根据算法存储在各个节点中。

叶子节点：字段内容+记录物理地址，物理地址与数据记录的物理地址一一对应。

空白指针：记录着下个节点的位置，通过算法寻找、指引到下个节点。

高度：索引结构从上到下的层数，高度不要过高或过低，最好趋向“正三角形”，有利于数据快速查找。

宽度：每层索引从左到右节点的数量。

Myisam(主键)索引：

索引字段内容 --------> 记录物理地址--------> 具体记录信息

Myisam非主键索引数据结构原理：

非主键索引的原理与主键索引基本一致：

索引和数据部分都是分离的，它们通过“物理地址”进行联系。

Innodb索引数据结构：

Innodb索引结构称为“聚合型”，“索引”和”数据”是合并在一个文件里边的。

innodb的主索引文件上直接存放该行数据，称为聚簇索引，次索引指向对主键的引用。

Innodb主键索引数据结构原理：

主键索引既存储索引值，又在叶子中存储行的数据。像innodb中，主键的索引结构中，既存储了主键值，又存储了行数据，这种结构称为“聚簇索引”。

Innodb(主键)索引：

索引字段内容 --------> 直接对应记录信息

这个表如果没有主键，则会unique key做主键，如果又没有unique key，则系统生成一个内部的rowid做主键。

Innodb非主键索引数据结构原理：

Innodb(非主键)索引：

索引字段内容 ---> 记录主键id值 --->(主键索引)---> 记录信息

innodb索引里边没有物理地址，非主键索引需要借助主键索引找到数据记录，所以：

非主键索引-->主键索引-->数据记录

聚簇索引的优劣：

优势：根据主键查询条目比较少时，不用回行（数据就在主键节点下）。

劣势：如果碰到不规则数据插入时，造成频繁的页分裂。

因此聚簇索引应注意的点：

聚簇索引的主键值，应尽量是连续增长的值，而不是要是随机值（不要用随机字符串或UUID），否则会造成大量的页分裂与页移动。

索引的业务设计依据：

1.频繁使用的字段设置索引：

sql语句中，被频繁用在where和order等条件里的字段适合用来创建索引。数据表创建完毕，预估哪些字段会被经常使用，可以给其创建索引。

2.执行时间长的sql语句考虑设计索引：

可以利用“慢查询日志”收集这样的sql语句并优化设计索引。

3.字段内容足够花样化，可以考虑设计索引：

例如类似性别字段不适合做索引，因为它的内容取值太少。

4.查询出来的记录条数比较少的时候可以使用索引：

例如，数据表总共有100万的记录信息

select * from 表名 where id > 5; //不会使用索引，记录范围大

select * from 表名 where id < 5; //会使用索引

5.sql语句里边有in条件，有时候可以给in条件的字段设计索引：

where id in (13,33,39)

6.两个表通过“外键”进行连接，该外键可以为之创建索引：

创建索引：

1.创建表的同时创建索引：

create table 表名(

...

primary key （字段),

unique key [索引名称] (字段),

key [索引名称] (字段),

fulltext key [索引名称] (字段)

)

除了主键索引，其他索引设置的同时可以给其起一个“索引名称”，名称不设置的话会与该索引字段名称一致，如果索引名称叠加（重复）则会自动额外设置序号。

索引创建完毕后查看创建表的SQL语句为：

2.给已经创建过了的表增加索引：

alter table 表名 add primary key （字段）;

alter table 表名 add unique key [索引名称] (字段);

alter table 表名 add key [索引名称] (字段);

alter table 表名 add fulltext key [索引名称] (字段);

测试：

首先创建一个没有任何索引的数据表，也不要给id设置auto_increment属性：

然后给它设置索引：

查看索引设置的效果：

索引简介：

前缀索引简介：

索引长度直接影响索引文件的大小，影响增删改的速度，并间接影响查询速度(占用内存多)。前缀索引是通过字段前N位创建的索引。

如果一个字段内容的前边的n位信息已经足够标识当前的字段内容，就可以把字段的前N位获得出来并创建索引，该索引占据更空间更小、运行速度更快。

前缀索引制作：

制作前缀索引语法：

alter table 表名 add key (字段(前n位位数)) ;

获取制作前缀索引的N的信息：

针对列中的值,从左往右截取部分，来建索引：

① 截的越短, 重复度越高,区分度越小, 索引效果越不好

② 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.

③ 去除字段重复内容并计算总数目。

④ 取字段的前(n)1、2、3.....位不重复的信息并计算总数目，n从1开始不断累加，直到总数目与 ①计算的总数目相等，此时N就是我们设计前缀索引的数字N信息。

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

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

mysql> select count(distinct left(word,6))/count(*) from dict;

+---------------------------------------+

| count(distinct left(word,6))/count(*) |

+---------------------------------------+

| 0.9992 |

+---------------------------------------+1 row in set (0.30 sec)

对于一般的系统应用来说，区别度能达到0.1，索引的性能就可以接受。

全文索引：

其他索引是把字段的内容作为一个整体进行索引设计，而全文索引会把文章的各个关键单词获得出来，制作为索引内容。Mysql5.6.4之前只有Myisam支持全文索引，之后 Myisam和Innodb都支持，类似我们有一篇作文，把作文中的一些关键字给获取出来当成是索引内容。

目前Mysql中只有英文支持全文索引，要使用支持中文的全文索引一般都是借助第三方软件sphinx，一般使用全文索引也不使用Mysql本身的。

注意：

索引字段类型必须为varchar或char或text类型。

举例使用fulltext全文索引：

创建表并插入测试语句：

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT

)engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES

('MySQL Tutorial','DBMS stands for DataBase ...'),

('How To Use MySQL Well','After you went through a ...'),

('Optimizing MySQL','In this tutorial we will show ...'),

('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

('MySQL vs. YourSQL','In the following database comparison ...'),

('MySQL Security','When configured properly, MySQL ...');

给title字段创建全文索引：

alter table articles add fulltext key (title);

查看创建好的全文索引：

全文索引使用语法规则：

正确：

select * from articles where match(字段) against(模糊内容); 不正确：

select * from articles where 字段 like ‘%模糊内容%’;

mysql本身的全文索引在做模糊查询的时候会有“自身的考虑”，把一些不常见的特殊内容给设计为索引内容，一些生活等常见用语（what where how等等）就不会设计为索引内容。

因此，例如下边的4次查询，只有一次是成功的：

复合索引：

多列索引的考虑因素一般是列的查询频率以及列的区分度：

以ecshop商城为例，goods表中的cat_id，brand_id做多列索引，从区分度看，brand_id区分度更高：

mysql>select count(distinct cat_id) / count(*) from goods;

+-----------------------------------+

| count(distinct cat_id) / count(*) |

+-----------------------------------+

| 0.2903 |

+-----------------------------------+

1 row in set (0.00 sec)

mysql>select count(distinct brand_id) / count(*) from goods;

+-------------------------------------+

| count(distinct brand_id) / count(*) |

+-------------------------------------+

| 0.3871 |

+-------------------------------------+

1 row in set (0.00 sec)

但从实际业务业务看，客户一般先选大分类再选小分类最后是品牌。最终选择index(cat_id,brand_id)来建立索引。

设置复合索引：

[primary/unique] key [索引名称] (字段1，字段2，字段3...)

多个字段组合设置索引，主键、唯一、普通索引的都可以设置为复合索引，如果不指明索引名称，就把索引的第一个字段获得出来当做索引名称使用。

给数据表的name和age字段设置为“复合索引”：

查看创建索引的结果：

测试：

现在再给name做一个唯一索引，为了让复合索引的name名称与普通索引的name名称起冲突，我们不设置索引名称。

结果没有冲突，系统把第二个name设置了一个“_2”的序号。

删除索引：

1.删除非主键索引（唯一、普通、全文、复合索引）

alter table 表名 drop key 索引名称;

2.删除主键索引

alter table 表名 drop primary key;

注：如果有“auto_increment”属性，要先除去“auto_increment”属性。

语句如下：

给id字段添加auto_increment属性：

alter table student4 modify id int unsigned not null auto_increment comment '主键id';

给id字段去除auto_increment属性：

alter table student4 modify id int unsigned not null comment '主键id';

删除主键：

主键和auto_increment属性的设置顺序：

①设置：先主键、再auto_increment。

②删除：先auto_increment、再主键。

在auto_increment存在的情形下删除主键，系统会报错：

索引覆盖：

索引覆盖是指如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据。这种查询速度就非常快，称为”索引覆盖”。

例如：

1.推断出是innodb引擎，物理行在叶子上，如果是mysam的话，物理行在磁盘上，查找不会这么慢，那应该不会出现这个问题。

2.有比较长的字段列，块比较多，id主键是聚簇索引，导致沿id查找时要跨许多小块。

3.id与ver是联合索引，且发生了索引覆盖。

看上面的sql语句，没有使用where、也没有使用order，只有ename的复合索引可用，并且查询的字段(ename,deptno)与ename复合索引字段(ename,deptno)完全一致，此时就把当前使用索引的情形称为是“索引覆盖”，也可以称为“黄金索引”，在索引里边就已经可以获得需要的信息(不需要去数据表里边做二次记录查询了)。

索引使用原则：

1.字段独立原则：

2.1左原则：

例如“like”模糊查询，左边内容固定，右边匹配模糊内容的模式就是——左原则。

模糊查询：like

%:匹配多个模糊内容。

_:匹配一个模糊内容。

例如符合“左原则”情况：

like '北京%' //“北京”内容右边出现多个模糊内容

like '北京_' //“北京”内容右边只出现一个模糊内容

例如不符合左原则情况：

like '%北京%'

like '_北京_'

like '%北京'

2.2对于左前缀不易区分的列，建立索引的技巧：

例如url列：

http://www.baidu.com

http://www.zixue.it

列的前11个字符都是一样的，不易区分，此时可以用以下两个办法来解决：

①把列内容倒过来存储，并建立索引：

Moc.udiab.www//:ptth

Ti.euxiz.www//://ptth

这样左前缀区分度大。

②伪hash索引效果：

同时存 url_hash列

3.复合索引（2个字段）

①第一个字段作为条件，有索引。

②全部字段都作为条件，不管前后顺序，有索引。

③单独使用其他字段（复合索引的非第一个字段）作为条件，没有索引。

上图，deptno本身是一个普通的索引，但是当前情况也没有用到该索引为什么呢？

因为是deptno>200的条件获得数据记录太多了，不会用到索引要想用到deptno的普通索引，可以把条件设置获取的记录的少一些(例如deptno>20000)。

4.or原则

①or左右条件字段都有索引，则都可以使用。

②or的左右只体现一个索引字段，则整体都没有使用。

btree索引的常见误区：

1.在where条件用的列上都加上索引：

例：

where cat_id=3 and price>100

误：cat_id和price上都加上索引。

因为只能用上cat_id或price索引其中一个索引，因为是独立的索引，同时只能用上1个。

2.在多列上建立索引后，查询哪个列，索引都将发挥作用：

误：多列索引上，索引发挥作用的话需要满足左前缀要求。

以 index(a,b,c)为例：

SQL语句	索引是否发挥作用
where a=3	是，只使用了a列
where a=3 and b=5	是，使用了a,b列
where a=3 and b=5 and c=4	是，使用了a,b,c
where b=3 或 where c=4	否
where a=3 and c=4	a列能发挥索引，c不能
where a=3 and b>10 and c=7	a能利用，b能利用，c不能利用
where a=3 and b like ‘xxxx%’ and c=7	a能利用，b能利用，c不能利用

测试：

假设某一个表有一个联合索引(c1,c2,c3,c4)，以下只能使用该联合索引的c1,c2,c3部分的语句为哪个？

A. where c1=x and c2=x and c4>x and c3=x

B. where c1=x and c2=x and c4=x order by c3

C. where c1=x and c4=x group by c3,c2

D. where c1=x and c5=x order by c2,c3

E. where c1=x and c2=x and c5=? order by c2,c3

创建表并插入数据：

create table t4 (

c1 tinyint(1) not null default 0,

c2 tinyint(1) not null default 0,

c3 tinyint(1) not null default 0,

c4 tinyint(1) not null default 0,

c5 tinyint(1) not null default 0,

index c1234(c1,c2,c3,c4)

);

insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

对于A：

c1=x and c2=x and c4>x and c3=x 等价于 c1=x and c2=x and c3=x and c4>x

因此 c1,c2,c3,c4都能用上。如下:

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

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: range

possible_keys: c1234

key: c1234

key_len: 4 #可以看出c1,c2,c3,c4索引都用上

ref: NULL

rows: 1

Extra: Using where

对于B：

select * from t4 where c1=1 and c2=2 and c4=3 order by c3

c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序，而c4没发挥作用。

mysql>explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: ref

possible_keys: c1234

key: c1234

key_len: 2

ref: const,const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

mysql>explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: ref

possible_keys: c1234

key: c1234

key_len: 2

ref: const,const

rows: 1

Extra: Using where; Using filesort

1 row in set (0.00 sec)

对于 C：

只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引

mysql>explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: ref

possible_keys: c1234

key: c1234

key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用

ref: const

rows: 1

Extra: Using where; Using temporary; Using filesort

1 row in set (0.00 sec)

这时候如果把group by的两个字段顺序换下，则不同：

mysql>explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: ref

possible_keys: c1234

key: c1234

key_len: 1

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

对于D：

c1确定的基础上，c2是有序的，c2之下c3是有序的，因此c2，c3发挥的排序的作用，不会用到filesort。

mysql>explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: ref

possible_keys: c1234

key: c1234

key_len: 1

ref: const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

对于E：

它等价与select * from t4 where c1=1 and c2=3 and c5=2 order by c3; 因为c2的值既是固定的，所以参与排序时并不考虑。

mysql>explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G

***************************1. row ***************************

id: 1

select_type: SIMPLE

table: t4

type: ref

possible_keys: c1234

key: c1234

key_len: 2

ref: const,const

rows: 1

Extra: Using where

1 row in set (0.00 sec)

一道题：

有商品表，有主键goods_id，栏目列cat_id，价格列price，在价格列上已经加了索引，但按价格查询还是很慢，问可能是什么原因，怎么解决?

原因：

在实际场景中，一个电商网站的商品分类很多，直接在所有商品中，按价格查商品，是极少的，一般客户都来到分类下，然后再查价格。

解决：

去掉单独的price列的索引，加 (cat_id,price)复合索引再使用。

索引与排序：

排序可能发生2种情况：

1.对于覆盖索引，直接在索引上查询时，就是有顺序的：using index。

2.先取出数据，形成临时表做filesort（文件排序，但文件可能在磁盘上，也可能在内存中）。

在实际项目中，目标就是取出来的数据本身就是有序的！利用索引来排序。

例如：

goods商品表，(cat_id，shop_price)组成联合索引，where cat_id=N order by shop_price，可以利用索引来排序。

select goods_id,cat_id,shop_price from goods order by shop_price;（// using where），按照shop_price索引取出的结果，本身就是有序的。

select goods_id,cat_id,shop_price from goods order by click_count;（// using filesort）用到了文件排序，即取出的结果需要再次排序。

重复索引与冗余索引：

重复索引:

是指在同1个列(如age)，或者顺序相同的几个列(age,school)，建立了多个索引，这种就是称之为重复索引，重复索引没有任何帮助，只会增大索引文件，拖慢更新（增删改）速度。

冗余索引:

冗余索引是指2个索引所覆盖的列有重叠，称为冗余索引。

比如 x,m,列，加索引index x(x)，index xm(x,m)两索引，两者的x列重叠了，这种情况称为冗余索引。甚至可以把 index mx(m,x) 索引也建立，mx与xm也不是重复的，列的顺序不一样。

索引碎片与维护：

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

比如：

表的引擎本来就为innodb ，可以再alter table xxx engine innodb，此时不会更改数据，但是会整理数据。

修复的专用命令：

optimize table 表名 ,也可以修复。

注意：

修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐。这个过程如果表的行数比较大，也是非常耗费资源的操作。所以，不能频繁的修复。

如果表的Update操作很频率，可以按周/月来修复，如果不频繁可以更长的周期来做修复。

二、分表设计

一个表里存储太多的记录的话会导致该表的活性大大降低，表的运行速度就会比较慢、效率也比较低，进而影响到Mysql数据库的整体性能。这时候可以采取分表设计，把记录信息平均分摊给各个子表来解决上面的问题。

分表设计的两种方式：

①逻辑分表：

逻辑分表是Mysql本身就支持的技术，使用者不需要考虑记录在哪个数据表存储。

②物理分表：

需要程序考虑并记录在哪个分表存储数据。

1.逻辑分表（分区）：

通过mysql自带的的分区功能，mysql将会根据指定的规则，把数据放在不同的表文件上，相当于在文件上被拆成了小块，但是给使用者的界面还是1张表。

分区常用的规则：

求余方式的两种（业务联系不紧密）：key和hash

范围方式的两种（业务联系紧密）：range和list

key分表：

分表设计好后，可以简单（非严格）理解为通过“求余”算法把记录写到各个分表中。例如下图分表字段id对分表数量5，进行求余然后填充数据记录信息到对应分区。

create table 表名( .....

)engine=Myisam charset=utf8

partition by key(分表字段) partitions 分表数量;

分表文件在硬盘中存储的效果：

向分表中写入数据：

磁盘文件体现如下：

hash分表：

根据表达式或字段的方式进行分表设计。

create table 表名( .....

)engine=Myisam charset=utf8

partition by hash(表达式/字段) partitions 数量;

创建分表：

分表文件效果如下：

向分表中insert入数据：

存储效果：

range分表：

该分表与业务联系非常紧密，数据可以根据分表算法严格填充到分表中。分表字段可以是表达式也可以是独立字段。

create table 表名( .....

)engine=Myisam charset=utf8

partition by range(表达式/字段)(

partition 名称 values less than (常量),

...

)

又例如：

按range分区

create table goods (

id int,

uname char(10)

)engine myisam

partition by range(id) (

partition p1 values less than (10),

partition p2 values less than (20),

partition p3 values less than MAXVALUE

);

（分表为innodb存储引擎）需要注意的是，每个innodb数据表有独立的数据文件，如下图：

（set global innodb_file_per_table=1; //设置innodb独立数据文件，但是不建议使用）

创建分表的效果：

insert入数据：

存储效果：

如果想要插入的数据超过了分表的范围，则报错禁止写入：

list分表：

该分表与业务联系非常紧密，数据可以根据分表算法严格填充到分表中，创建分表的内容可以是表达式也可以是独立字段。

create table 表名( .....

)engine=Myisam charset=utf8

partition by list(表达式/字段)(

partition 名称 values in (列表范围),

...

)

根据月份创建季节分表：

分表效果：

insert入数据：

存储效果：

2.分表管理（增加、减少）：

删除分表：

①在key/hash领域不会造成数据丢失（删除分表后数据会重新整合到剩余的分表去）。

②在range/list领域会造成数据丢失。

求余方式（key/hash）：

alter table 表名 coalesce partition 数量;

范围方式（range/list）：

alter table 表名 drop partition 分表名称;

①删除hash类型分表：

剩余一个分表的时候的存储效果：

数据不会变化：

当试图删除最后一个分表的时候：

报错，禁止删除！

②删除list类型分表：

删除对应名称的分表之后，该分表的数据也被删除了。

增加分表：

求余方式（key/hash）：

alter table 表名 add partition partitions 数量;

范围方式（range/list）：

alter table 表名 add partition(

partition 名称 values less than (常量)

或

partition 名称 in (n,n,n)

);

①增加hash分表：

增加后一共有6个分表体现：

如上图，分表增加好后，又把数据平均地分配给各个分表存储了。

①增加range分表：

效果：

2.物理分表：

物理方式分表设计是自己手动创建多个数据表出来，然后例如PHP等程序需要考虑分表算法：数据往哪个表写，从哪个表读等。

例如下PHP程序实现在哪个分表对数据的读取、修改、删除：

PHP程序对某个分表实现数据添加：

3.垂直分表：

水平分表：

是把一个表的全部记录信息分别存储到不同的分表之中。

垂直分表：

是把一个表的全部字段分别存储到不同的表里边。

实际项目中，一个数据表设计好了，里边有许多字段，但是这些字段有的是经常使用的，有的是不常用的。在进行正常数据表操作的时候，不常用的字段也会占据一定的资源，对整体操作的性能造成一定的干扰、影响。为了减少资源的开销、提升运行效率，就可以把不常用的字段给创建到一个专门的辅表中去。而这种同一个业务表的不同字段分别存储到不同数据表的过程就是“垂直分表”。