单体架构优化之MySQL优化

最新推荐文章于 2024-04-07 18:57:31 发布

Mamba0824

最新推荐文章于 2024-04-07 18:57:31 发布

阅读量310

点赞数

本文链接：https://blog.csdn.net/Mamba0824/article/details/100661338

版权

MySQL优化之最大连接数

100、150、200

最大连接数对系统有什么影响？

show variables like '%max_connections%';

测试一：如果最大连接数超过100,会怎么样？

修改最大连接数
my.ini文件中修改如下属性：
max_connections=100

测试代码开发步骤：
springmvc+mybatis
1、配置springmvc
    web.xml
    spring.xml(applicationContext.xml)
    <mvc:annotaion-driven/>
    <context:conponent-scan basePackage=""/>
    
2、写一个controller检查配置是否生效
3、配置mybatis
    对象、关系（表）、映射
    
    mapper接口
    
    mybatis整合spring
        
        sessionfactory
        
        
记忆的关键词
关系、对象、映射、sessionfactory、tranactionManager、mapperScan
    
    
4、写一个测试案例，测试mybatis是否成功


tomcat线程池200、c3p0连接池200、数据库连接池200。
最大连接数、最小连接数、最大休眠时间等等

测试500万条数据查询太慢，用户数较多的情况，会导致有用户请求失败。

优化：
    1、提升查询速度
        a、数据库优化
            i、加入查询缓存
            ii、增大连接数
            iii、建立索引
                SQL优化：
                SQL优化主要是查询优化，查询优化的重心就是提升查询速度，而提高查询速度的核心是简历索引。SQL就是避免写出的SQL导致索引失效。所以有些SQL不能写，比如：
                a、模糊查询不能在前面加%
                b、is null或者is not null(只要是null的字段是不会有索引)。
                c、不要使用*查询所有字段，这会降低查询速度。并且有额外的内存开销。
                d、in或者not in会影响性能
                
                
                1、避免在索引列上出现null。
                2、不要再索引列上进行算术运算。：select age+1 from  user
                3、避免实现!=或者<>、is null或者is not null、in等可能导致全表遍历的操作。
                4、模糊查询只能使用右边%。
                5、where语句后尽可能少用小括号、或者不要出现小括号嵌套小括号。
                6、其他

一、最大连接数（Jmeter进行压测）

show variables like '%max_connections%';
set global max_connections=1000 //此修改方式重启数据库之后会重置。

永久修改：修改MySQL数据库的配置文件（my.ini）

二、查询缓存
开启：
修改c盘C:\ProgramData\MySQL\MySQL Server 5.7下的my.ini文件
query_cache_size=200M
query_cache_type=1

结论：
1、如果对缓存对应的数据进行修改、新增，会导致缓存清掉。
2、查询比较频繁的数据库，可以使用查询缓存。

缺点：
1、会导致修改和新增的效率变慢，所以在需要频繁修改新增的数据库不要使用查询缓存。




三、InnoDB和MyISAM的区别
查看表的引擎：show create table tablename；

3.1、InnoDB：行锁，事务，外键。
数据库InnoDB锁机制是需要运行在事务中的。
行锁：
select  math from zje where math>60 lock in share mode；//共享锁
select math from zje where math >60 for update； //排他锁

总结：
1、行锁只有InnoDB有。
2、提交事务就是释放锁。
3、锁需要在事务内开启。

3.1.1、事务隔离级别
事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

事务并发所带来的问题：
1、脏读：
事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：（行锁）
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：（表锁）
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表


查询默认事务隔离级别：select @@tx_isolation;
修改事务隔离级别：set session transaction isolation level read uncommitted; 


总结：
1、事务的隔离级别是为了解决事务的并发问题。
2、事务的并发问题有：脏读、不可重复读、幻读。


3.2、MyISAM：表锁，不支持事务，查询效率高，全文索引。
表锁：myisam自动上锁。手动上锁代码如下：
Lock table 表名 read；
    SQL操作；
Unlock tables；



四、索引
4.1、索引分类
4.1.1、普通索引
create index name on s1(name);
4.1.2、唯一索引
create unique index age on s1(age);
4.1.3、聚合索引
create index name on s1(id,name);
聚合索引按照从左到右的匹配原则。也就是必须先匹配ID才能匹配name查询。
4.1.4、全文索引（MyISAM）
ALTER TABLE article ADD FULLTEXT INDEX 索引名称(title, content)

全文检索的查询方式：
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
注意：全文索引只适合MyISAM引擎的数据表。并且只能对英文进行检索。




五、SQL优化
SQL优化的重心是查询优化，查询优化的重心是建立索引。所以查询优化主要是避免出现导致索引失效的查询。
1、避免在索引列上出现null。
2、不要再索引列上进行算术运算。：select age+1 from  user
3、避免实现!=或者<>、is null或者is not null、in等可能导致全表遍历的操作。
4、模糊查询只能使用右边%。
5、where语句后尽可能少用小括号、或者不要出现小括号嵌套小括号。
6、其他


参考官方文档：
https://dev.mysql.com/doc/refman/5.7/en/table-size-limit.html
https://dev.mysql.com/doc/refman/5.7/en/optimization.html



总结：
1、表不能太多（超过200张）
2、单表的列数不能太多（超过40列）
3、32位系统单表最大4G。64位没有限制。