java中的mysql优化

浅浅的花香味﹌ 2023-02-18 15:26 37阅读 0赞

## 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特点时是索引和数据文件是分离。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70]

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

*  表共享读锁：对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的实现是通过每行记录中隐藏的创建时间和删除时间来实现的。（这里的时间存储的是系统版本号）

![在这里插入图片描述][20200627230512682.png]  
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%'

![在这里插入图片描述][20200618182306844.png]

*  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等。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70 1]

## f.其他优化角度 ##

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

## explain的扩展 ##

查询用户表

EXPLAIN SELECT * from tb_user WHERE id ="1"

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70 2]  
通过explain我们可以：  
1. 表的读取顺序  
2. 数据读取操作的操作类型  
3. 哪些索引可以使用  
4. 哪些索引被实际使用  
5. 表之间的引用  
6. 每张表有多少行被优化器查询

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200714231358162.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA==,size_16,color_FFFFFF,t_70
[20200627230512682.png]: https://img-blog.csdnimg.cn/20200627230512682.png
[20200618182306844.png]: https://img-blog.csdnimg.cn/20200618182306844.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200618183021901.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200618184733236.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200812232411287.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDk5MDgxOA==,size_16,color_FFFFFF,t_70#pic_center