Mysql设计和优化

一、MySQL不同版本分支的特性 1、官方MySQL的现状

−主流版本MySQL5.5, MySQL 5.6展望MySQL 5.7（线程池、性能、复制等，比5.6快了3倍，每秒能达到50W查询，1.6 mil QPS）

−能和社区竞争的版本算是MySQL5.6(从开源社区Percona那边学习了很多优秀的Patch合并过来)．

−MySQL5.7引入Online DDLtable Schema

−引擎Innodb-plugin

−Oracle目前把MySQL分成社区版和企业版两个版本，同时推荐收费的扩展模块(如thread-pool)

—官方力推MySQL NDBCluster

2、Percona-Server的现状

−MySQL原来官方团队里做咨询的人组的队

−在MySQL5.0.X版本中大放异采，性能比官方版本好很多，管理方面也比官方的优秀

−发布支持一堆MySQL周边有用的tools

−MySQL5.6的出来在性能及管理方面给Percona压力也比较大小

−目前Percona-Server也开始大力推荐Percona-Cluster

−Percona也算是业界做MySQL服务比较好的一个公司

−Percona从MySQL5.6后的目标是：把官方收费的企业版可以享受到功能社区化

PerconaMySQL= MySQL Enterprise +收费的Plugin

3、MariaDB现状

−ＭySQL创始人Monty在Oracle收购MySQL后创建，目标在于kill MySQL

−完全开源，重构了Server端，更稳定，更高效

−目前来看是一个比较给力的版本

−特性比较多，比较有用的扩展模块也免费如(thead-pool)

−目前和SkySQL联合发展

−MariaDB10.X将是一个革命性的版本

−多通道复制，基于主建并发复制

−同时提供ＭariaDBGaleraCluster支持

−MariaDB接管了InfiniDB—列式存储

4、WebScaleSQL现状

−Facebook, Google, Linkedin, Twitter几家公司在MySQL5.6 上构建的一个分支

−alibaba目前也加入了该版本的开发

−几家公司共享了开发成果，优秀的patch共享

−便重互联网应用

−表空间碎片整理

−SQL运行超时

5、其它有潜力的数据库

−TokuDB(分社区版和企版)

    −目前写入最快，压缩最好的一个存储引擎

−ScaleBase(完全收费)

    −可以在存储引擎层做数据的分区，实现数据的横向扩容

−MongoDB

    −基于Object，KV存储，自动分区

−Redis

    −基于KV,SET,LIST内存级别存

6、结论

•每个版本都有独到之处，选择什么就是赢在起跑线，推荐Percona版本，关注WebSacaleSQL

•如果有大量数据写入，推荐Tokudb

•选择合适的NoSQL产品加速请求：Redis

•做LBS相关应用可以考虑：MongoDB

二、数据库基本常识 1、数据库特性–select version();

A、5.1可以利用4个核，5.5可以用24核，5.6可以用48-64核

推荐用5.6，可以用子查询，之前的版本不能用子查询

1核分配5个连接数左右(4-8个)

5.5总连接数控制在150以下

5.6总连接数控制在200-300

B、每个链接是一个线程(非thread pool)，每个query只能使用到一个核

C、无执行计划缓存(无执行计划预编译)

D、有Result缓存，但比较鸡肋 2、数据库目录说明

mac： sudo su后进入目录查看

/usr/local/mysql/data/XXXXX

windows： my.ini 文件 datadir=” C:/ProgramData/MySQL/MySQL Server 5.6/Data/”

   .frm 表结构

   .MYD 数据文件

   .MYI 索引文件

   .ibd  分区文件

三、数据库设计 1、命名

与应用名称尽量一致，使用小写字符、数字和下划线组合，命名长度不超过30个字符，不使用关键字保留字。 2、字符集（character sets）

latin1字符集1个字母占据1个字节。—默认

gbk字符集1个汉字占据2个字节—-在中国可用

utf8字符集1个汉字占据3个字节—支持全部字符，占用空间大

utf8mb4 兼容存放4字节的unicode（Emoji 表情）

SELECT t.TABLE_SCHEMA AS 实例名,t.TABLE_COMMENT AS 数据表名称,t.TABLE_NAME AS 数据表代码,t.ENGINE AS 表的存储引擎,t.TABLE_ROWS AS 当前记录条数,t.AVG_ROW_LENGTH AS 平均每行包括的字节数,round(t.DATA_LENGTH/1024/1024,2) AS ‘整个表的数据量(MB)’,round(t.INDEX_LENGTH/1024/1024,2) AS ‘索引占用磁盘的空间大小(MB)’ FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA=’’ AND t.TABLE_ROWS> 0 ORDER BY t.TABLE_ROWS DESC;

Tips：物理大小不超过10G，行数不过亿，平均物理行长度不超过8KB 3、校对（collations）

_ai

Accent insensitive 重音不敏感

_as

Accent sensitive 重音敏感

_ci

Case insensitive 不区分大小写

_cs

case-sensitive 区分大小写

_bin

Binary 二进制

四、数据表设计

SELECT t.TABLE_NAME AS 数据表代码,t.TABLE_COMMENT AS 数据表名称,c.COLUMN_NAME AS 字段代码,c.COLUMN_COMMENT AS 字段名,c.ORDINAL_POSITION AS 表中字段顺序,c.COLUMN_TYPE AS 完整的字段类型,c.IS_NULLABLE AS 是否可为空,c.COLUMN_DEFAULT AS 默认值,c.EXTRA AS 自增长类型,CASE WHEN c.EXTRA=’AUTO_INCREMENT’ THEN ‘True’ ELSE ‘False’ END AS 是否自增长字段,c.COLUMN_KEY AS 主键索引名,CASE WHEN c.COLUMN_KEY=’PRI’ THEN ‘True’ ELSE ‘False’ END AS 是否为主键 FROM information_schema.COLUMNS c,information_schema.TABLES t WHERE c.TABLE_SCHEMA=’tsp_plateform_b21’ AND t.TABLE_SCHEMA=’tsp_plateform_b21’ AND t.TABLE_NAME=’ts_base_user’ AND c.TABLE_NAME=t.TABLE_NAME ORDER BY t.TABLE_NAME;

1、引擎选择

•MyISAM 建议放弃

•Innodb推荐使用

•Tokudb高速写入使用

•Memory根据需要使用

•Inforbright/InfiniDB OLAP环境

•FEDERATED跨网络使用的一个引擎（基本没使用案例）

•Ndbclustermysqlcluster的引擎

2、命名规则

a、数据表一律使用前缀(业务名_表名) sys_，temp/tmp_，stat/tj_，_bak/delete等。

b、所有表和字段添加中文注释，使用小写字符、数字和下划线组合，禁止数字开头，不使用复数，不使用关键字保留字，不超过30字符，尽量全英文或全中文。 3、常用字段设计

a、整型

1 bytes = 8 bit ,一个字节最多可以代表的数据长度是2的8次方 11111111 在计算机中也就是-128到127

类型

占用字节

从

到

Bit

1bit

Tinyint

-128

127

Smallint

-32768

32767

Mediumint

-8388608

8388607

Int

-2147483648

2147483647

Bigint

-9223372036854775808

9223372036854775807

大错特错：int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字。

tinyint(1) 和 tinyint(4) 中的1和4并不表示存储长度，只有字段指定zerofill是有用，如tinyint(4)，如果实际值是2，如果列指定了zerofill，查询结果就是0002，左边用0来填充。

CREATE TABLE inttest (

t tinyint(1) NULL,

t2 tinyint(4) NULL,

t3 int(1) NULL,

t4 int(3) zerofill

);

b、字符串类型

char是一种固定长度的类型，char(M)类型的数据列里，每个值都占用M个字符，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）

varchar则是一种可变长度的类型，在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．

为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．例外：长度小于4个字符的char数据列不会被转换为varchar类型

tip1：varchar(N), 这里的Ｎ是指字符数，并不是字节数．占用的字节数与编码有关

设置varchar(2),插入时，汉字可以插入2个，一个汉字占3个字节，字母可以插入2个，一个字母占一个字节—测试

tip2：mysql5之前N表示字节数

经验之谈：BIGINT/char(3)/TINYINT

•type列：8-bytes vs 3（utf8实际*3）-bytes vs 1-byte

•1000万行记录，type列分别采用上述三种类型

•存储空间对比

•char(3)相比tinyint大280MB

•bigint相比tinyint大136MB

•根据PK随机读取1000万次

   •char(3)相比tinyint多691MB

 •bigint相比tinyint多3758MB

•根据type列（有索引）随机读取50万次

 •char(3)相比tinyint多423MB

 •bigint相比tinyint多446MB

 •char(3)还有隐式类型转换风险

•综上，强烈建议小范围的枚举型采用tinyint

c、浮点型必须使用decimal字段

tips：float占4个字节，double占8个字节，decimail(M,D)占M+2个字节，M默认为10。

mysql> CREATE TABLE t3 (c1 float(10,2),c2 decimal(10,2));

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t3 values (999998.02, 999998.02);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;

+———–+———–+

999998.00

999998.02

+———–+———–+

1 row in set (0.00 sec)

d、时间类型

日期时间类型

占用空间

日期格式

最小值

最大值

零值表示

DATETIME

8 bytes

YYYY-MM-DD HH:MM:SS

1000-01-01 00:00:00

9999-12-31 23:59:59

0000-00-00 00:00:00

TIMESTAMP

4 bytes

YYYY-MM-DD HH:MM:SS

1970-01-01 08:00:01

2038 年的某个时刻

00000000000000

DATE

4 bytes

YYYY-MM-DD

1000-01-01

9999-12-31

0000-00-00

TIME

3 bytes

HH:MM:SS

-838:59:59

838:59:59

00:00:00

YEAR

1 bytes

YYYY

1901

2155

0000

用TIMESTAMP代替DATETIME吗？

•5.6.5以前确实强烈建议这么做

•5.6.5以后基本可忽略这个规范了

•5-bytes（之前是8-bytes）vs 4-bytes

•DATETIME支持的范围更大

•DATETIME也支持初始及自动更新成CURRENT_TIMESTAMP

•综上，根据实际情况选择吧！

e、其他注意事项

1)、字段长度偏移量不要超过50%，不在数据库中存储图片、文件等大数据，尽可能

不用Text、blob类型，增加存储空间的占用，读取速度会减慢。

2)、varchar长度不超过5000，不固定的文本用关联附表，并先压缩。

3）、不要给数据库留NULL，尽可能的使用NOT NULL填充，在count和where中null判断

都有影响。

select count(user_id) from dw_user where order is null; 结果：5875

select count(0) from dw_user where order is null; 结果：5875

select count(order) from dw_user where order is null; 结果：0

五、如何优化 1、慢sql定位

–查询慢查询时间

   show variables like 'long_query_time';

  --修改慢查询时间

   set long_query_time=1;

  --慢查询日志路径等信息

  show variables like "%slow%";  

   —开启日志

   set GLOBAL slow_query_log=ON

  --慢查询次数

   show status like 'slow_queries';

–安全模式写慢sql日志

mysqld.exe –safe-mode –slow-query-log

2、执行计划解读

Explain sql语句;

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1、Id 执行顺序，越大越先执行，id相同，由上至下执行。

2、type：system(系统常量)、const(常量)、eq_ref(唯一索引)、ref(非唯一索引)、range(范围扫描)、index(索引扫描)、ALL(全表扫描)

3、possible_keys可能使用的索引 key真实使用的索引

4、key_len 索引长度，越短越好

5、row 请求数据行数，越短越好

6、Extra 看到Using temporary和Using filesort时，就得考虑使用索引了

SQL 性能优化的目标：

  至少要达到  range 级别，要求是 ref 级别，如果可以是 consts最好

3、优化总纲

a.尽量避免大事务操作，提高系统并发能力。尽量避免全表扫描，尽可能在where及order by 涉及的列上建立索引，一个表的索引数最好不要超过 6 个。（单表2GB以上拆分区表）

b.采用队列方式合并多次写请求，持续写入，避免瞬间压力

c.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

d.尽量使用单表查询,非要关联的话小表在前大表在后，能够尽快定位到数据的查询条件放在前面

e.对于多张大数据量的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，性能很差。

f.查询量非常大的时候，考虑缓存、分表和分页处理

六、优化技巧 1、查询内容

a、不要使用 select * from t ，用具体的字段列表代替“*”（消耗CPU和IO、消耗网络带宽），不要返回用不到的任何字段。

b、函数外的统计，尽量放到内存中执行

Select sum(a)+sum(b) as c

C、尽可能的使用count(*),id为空的话，count(id)就统计不到

•mysql> select count(id) ,count(1), count(*) from u1;

•+———–+———-+———-+

•

count(id)

count(1)

count(*)

•+———–+———-+———-+

•

•+———–+———-+———-+

•1 row in set (0.00 sec)

d、tinyint在使用时容易变为布尔型，代码中记得转换，用case时等于更优

case status when 0 then do1 when 1 do2 end

===》

case when status=0 then do1 when status=1 then do2 end 2、查询条件

a、避免数据类型不一致

SELECT * FROM t WHERE id = ‘19’;—–>SELECT * FROM t WHERE id = 19;

b、尽量少用>=，直接使用>，可提升查询效率

Select * from dw_user where user_id>=101; —-—多一次等于的判断

Select * from dw_user where user_id>100;

C、判断是否为null应该用is null，如果直接使用=null，则不启用索引

d、在where子句中尽量避免使用!=或<>操作符，引擎会放弃使用索引而进行全表扫描

e、应尽量避免在 where 子句中对”=”左边进行函数、算数运算或表达式运算，这将导致引擎放弃使用索引而进行全表扫描。

int类型：addtime >= UNIX_TIMESTAMP(‘2017-11-09 00:00:00’);

时间类型：addtime >= DATE_FORMAT(‘2017-03-03’,’%Y-%m-%d’)

f、like前面有%会失效，如果字段长的话，可以考虑使用全文检索

g、应尽量避免在 where 子句中使用 or 来连接条件，必须全部索引存在才有效，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描—联合索引

h、使用索引时，关联表的条件字段中字段的长度和编码必须一致

a.fk_user_id = b.user_id ，fk_user_id 的编码是utf8 而 user_id 的编码方式是utf8mb4的，所以导致索引失效 3、In优化

a、用in替换or（针对少量的or）

低效查询 SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

—–>

高效查询 SELECT * FROM t WHERE LOC_IN IN (10,20,30);

b、in 和 not in 也要慎用（in控制1000内）：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

==》

select num from a where exists(select 1 from b where num=a.num)

•In 后面不能接子查询

•select * from tb1 where tb1.id in (select id from tb2 where tb2.c1…)

====》

•Select tb1.* from tb1 , (select id from tb2 where tb2.c1…) t where tb1.id = t.id;

4、group by/order by优化

a、默认Group by会对该字段排序，如果不是明确需要排序加上order by null

•如果明确需要升序或降序加上Group by DESC/ASC

select * from emp group by deptno order by null;

tips：使用group by、not in、not like <>等都不使用索引

b、如果有 order by 的场景，请注意利用索引的有序性。

where id=x order by col2

在MySQL5.1的执行顺序是先排序然后现取等号，需要索引( col2, id) 。

之后要用索引(id,col2)，order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file _ sort 的情况，影响查询性能。索引中有范围查找，那么索引有序性无法利用

5、limit优化

•LIMIT N，M；

•取N行记录后再取M行返回给前端

•高效分页实现：

•Select id from tb limit 100000, 1;

•Select  * from tb where id>=N limit 100;

•关于LIMIT块yahoo有一个PPT可以找来学习一下 6、union all 而非union

•如果不需要对结果集进行去重，则用union all

•Union 有去重的开销

•比较：

•>select * from t1 union select * from t1;

•500 rows in set (0.02 sec)

•>select * from t1 union all select * from t1;

•1000 rows in set (0.00 sec)

7、子查询优化

select * from (

sql1 unoin sql2 union sql3

) where conditions

优化方案：condistions放到每个sql语句里。

8、更新语句优化

a、Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

b、案例

    •update sc_brand_infoset logo_pic_third=“” where id not in (select brandidfrom sc_product_info_apiwhere pricemin=11 group by brandid)

    •优化后语句：

    •UPDATE sc_brand_infoa LEFT JOIN (SELECT brandidFROM sc_product_apiwhere pricemin=11 group by brandid) b on(a.id= b.brandid) SET a.logo_pic_third="" WHERE b.idis NULL;

9、其他大表操作优化

–大表加字段

alter table ttt algorithm=copy, add column sid int not null default 0 ;

–批量数据写法

insert /+append/ into dw_user_point(user_id,point,point_used,point_expired)

select user_id,50,0,0 from dw_user where sina_status=3

on duplicate key update point=point+50;

Mysql设计和优化

Mysql

Mysql设计和优化

CATALOG

FEATURED TAGS

FRIENDS