java中的mysql优化

Mysql优化

a.设计合理Schema（表结构）

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
3. UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。
5. timestamp使用4个字节存储空间，datetime使用8个字节存储空间。但是timestamp局限性很大。
6. 尽可能不要使用枚举类
7. 表的参数不易过多
8. 表的大小不要过大

b.创建高性能索引

索引的类别

• PRIMARY KEY： 主键，这意味着索引值必须是唯一的，且不能为NULL。
• UNIQUE:创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• INDEX: 普通索引，索引值可出现多次。（普通索引的性能会优于唯一索引，因为唯一索引不能是change buffer）
• FULLTEXT: 全文索引.（底层实现时倒排索引）

使用索引的注意事项
1.表的主键和外键必须有索引
2.经常出现where字句的字段
3.经常要查询的列
4.选择性高的字段
5.经常用户排序的字段
6.数据超过300以上
7.表的索引最好不要超过5个
8.存在索引的字段不要进行函数操作

索引失效的情况：
1.对于组合索引，存在“左原则”，如果筛选条件没有name，那么只要city是不生效的
2.使用like查询，使用前匹配，如‘%aa’,索引是无效的
3.条件中有or，部分字段没有建立索引时，索引是无效的
4.数据库中的数据表数据过少
5.如果列类型是字符串，条件要用 ''包围，否则无效
6.使用is null或者 is not null时，索引无效

覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据。
联合索引：同时对多个字段建立索引,其遵循左原则

alert table test add INDEX `sindex` (`a`,`b`,`c`)
select a,b,c from test where a=1 and b = 1 #索引生效
select a,b,c from test where b=1 and c = 1 #索引失效

索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。
5.6版本后会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度。
回表查询

聚集索引(clustered index) ：通常为主键索引
普通索引(secondary index) ：其他索引

select * from t where name='lisi';

（1）先通过普通索引定位到主键值（即id）；
（2）在通过聚集索引（即id）定位到行记录；
回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

B+树索引 和哈希索引
InnoDB和MyISAM都是使用B+Tree（平衡二叉树+有序数组链表）进行索引。
memery使用Hash进行索引

• B+树索引 和哈希索引的比较
• 如果是等值查询，哈希索引有绝对优势
• 范围查询检索，哈希索引性能比较差
• 哈希索引不支持排序，和模糊查询
• 存在大量重复键，由于存在哈希碰撞问题，哈希索引的性能比较低

c.根据业务需求选择合理存储引擎

Mysql存储引擎
常见的有InnoDB、MyISAM、MEMMORY、Archive

• InnoDB:唯一支持外键，支持事务，用于大规模活跃数据查询 (行锁)
• MyISAM:不支持外键、主键和事务，支持数据压缩,强调数据快速读取，用于冷数据查询。（表锁）
• MEMORY:默认使用HASH索引，数据存储在内存中，读取速度快。（表锁）
• MERGE:是一组MyISAM引擎的组合，用来进行多表的进行查询和更新操作。（表锁）

InnoDB和MyISAM索引之间的区别

• InnoDB是聚集索引，InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；（InnoDB会有回表操作）
• MyISAM是非聚集索引，MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。（MyISAM不会有回表操作）

数据存储方式的差异
Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

MyISAM特点时是索引和数据文件是分离。

表级锁
不会出现死锁，发生锁冲突几率高，并发低（不同表的读取会阻塞，写操作会阻塞其他操作）。
场景：适用那些更新数据不频繁的情况。
表级锁的两种模式： 表共享读锁、表独占写锁

• 表共享读锁：对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
• 表独占写锁：对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

行级锁
行级锁，会出现死锁，发生锁冲突几率低，并发高。
场景：当数据会增、删、改的情况下，为了保证数据一致性，需要加上排它锁。

• 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。（行级锁是借助索引来实现的，所以建立索引能避免升级成表级锁，进而提升性能 ）
• 两个事务不能锁同一个索引。
• insert，delete，update在事务中都会自动默认加上排它锁。（也可以选择手段去添加 sql后 for update）

页级锁
mysql5.1之前BDB引擎支持页级锁。
开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

间隙锁（串行化使用它预防幻读）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内并不存在的记录，叫做间隙
InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁

-- 用户A update user set count=8 where id>2 and id<6 
-- 用户B update user set count=10 where id=5;

id不在 （id>2 and id <6）,但是同样会被A阻塞，只有当A提交后 ，B的更新操作会执行。

mvcc多版本并发控制

• mvcc是一种行级锁的变种，它通过多版本的方式实现了非阻塞的读操作（修改、删除、新增时不会加入排他锁）。
• mvcc是适用隔离级别为 读未提交和可重复读两种隔离级别
• mvcc为读未提交提供语句级别的快照（历史版本），为可重复读提供事务级别的快照（历史版本）。
• mvcc的实现是通过每行记录中隐藏的创建时间和删除时间来实现的。（这里的时间存储的是系统版本号）

repeatable-read 可重复读的实现依赖于mvcc机制。（在一个事务中的两次select中 穿插另一个事务一次update，两次结果是一致的）

d.关于查询缓存的优化

1.尽量使用小表而不使用大表
2.启动缓存的情况下 写入数据尽可能一次写入 重复写入会导致服务器崩溃
3.尽量不要在数据库或者表的基础上使用查询缓存 只针对一些查询语句进行缓存
a.query_cache_type=DEMAND
b. 在进行缓存的SQL语句 加上SQL_CACHE 其他加上SQL_NO_CACHE

查看查询缓存的相关配置

show variables like '%query_cache%'

• have_query_cache：当前的 MySQL 版本是否支持“查询缓存”功能。
• query_cache_limit：MySQL 能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是 1048576(1MB)。
• query_cache_min_res_unit：查询缓存分配的最小块（字节）。默认值是 4096（4KB）。
• query_cache_size：为缓存查询结果分配的总内存。
• query_cache_type：默认为 on，可以缓存除了以 select sql_no_cache 开头的所有查询结果。
• query_cache_wlock_invalidate：如果该表被锁住，是否返回缓存中的数据，默认是关闭的。

mysql是默认开启查询缓存的，sql加上这句不启用查询缓存

• 一个更新频率非常低而只读查询频率非常高的场景下，打开查询缓存
• 对于频繁更新的数据库，建议关闭查询缓存

select * from tb_user SQL_NO_CACHE

e.分表、分区和分片

分表
分表是一种逻辑上的概念：
a.利用主表作为查询的接口，表一表二作为存储数据的实际表单
b.只适应MyISAM 引擎
c.通过union将表合并 一定程度上会影响性能
d.与视图不一样的是，视图可以查询多个不同结果的表
第一步：创建子表1和子表2

DROP table IF EXISTS tb_member1;
create table tb_member1(
    id bigint primary key auto_increment ,
    name varchar(20),
    sex tinyint not null default '0'
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
DROP table IF EXISTS tb_member2;
create table tb_member2(
    id bigint primary key auto_increment ,
    name varchar(20),
    sex tinyint not null default '0'
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
//创建tb_member2也可以用下面的语句  create table tb_member2 like tb_member1;

第二步：创建主表

DROP table IF EXISTS tb_member;
create table tb_member(
id bigint primary key auto_increment ,
name varchar(20),
sex tinyint not null default '0'
)ENGINE=MERGE UNION=(tb_member1,tb_member2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;

第三步：数据存储与查询

向表一插入数据：insert into tb_member1(id,name,sex) select id,name,sex from member where id%2=0; //这里区分表一表二
向表二插入数据：insert into tb_member2(id,name,sex) select id,name,sex from member where id%2=1;
查看一下主表的数据：select * from tb_member;

分区
分区是物理上的， 可以由多个物理子表组成。
水平分区表

• range分区：使用 values less than
• list分区：使用集合
• hash分区：给被hash的值 添加一个值或者表达式 从而进行分区
• 线性hash分区
• key分区
• 复合分区

ranger分区：

create table t1(
int id,
int year(10)
)
partition by range(year)
(
partition p1 values less than(10), #0-10
partition p1 values less than(20), #10-20
partition p1 values less than maxvalue#20以上
);

list分区

create table t2(
int id,
int year(10)
)
partition by list(year)
(
partition p1 values in (1,2,3)#1、2、3
partition p1 values in(4),#4
partition p1 values in(5)#5
);

垂直分区表:
a.将数据分为热数据和冷数据，热数据（账号、密码）使用一张表，冷数据（用户地址）使用另一张表。
b.使用id 进行记录关联

分片(本质上通常就是分库)

分片对数据进行类似于分区水平或者垂直的处理，但是分片可以跨DB等。

f.其他优化角度

1. 使用join 来代替子查询
2. 使用联合(UNION)来代替手动创建的临时表
3. 添加一定的冗余字段，减少频繁的join联表查询(比如在订单表中，‘客户名称’字段就是冗余字段，加了这个字段，就需要在客户信息表修改（客户名称改变）的时候，多做一个更新订单表中‘客户名称’字段的动作。)
4. 使用外键：锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联，必要情况下可以舍弃外键提升性能。
5. 使用explain关键字来优化查询语句和表结构

explain的扩展

查询用户表

EXPLAIN SELECT * from tb_user WHERE id ="1"

通过explain我们可以：

1. 表的读取顺序

2. 数据读取操作的操作类型

3. 哪些索引可以使用

4. 哪些索引被实际使用

5. 表之间的引用

6. 每张表有多少行被优化器查询

通过3、4我们就可以知道哪些索引可能存在失效的情况。