mysql性能优化

本文主要是介绍mysql性能优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

现在市面上mysql主流版本号是5.5, 5.7 ,8.0，5.7这个版本相对来说稳定性和兼容性都已经得到市场的验证，是比较好的一个版本。

###1.mysql中有MyISAM引擎与InnoDb引擎，他们之间区别是什么
InnoDb索引文件和数据文件是在一起的，只要查找索引文件后就可以连接到数据文件，查一次即可，效率高。现在主流使用InnoDb引擎

2.为什么InnoDb引擎表必须有主键，并且推荐使用整形的自增方式?

即使不创建主键id，mysql还是会默认创建一个rowid作为自增主键，用来做范围查询，这个rowid是看不到的，uuid是随机的，这个没法做范围查询。
###3.慢查询定位

慢查询
show variables like '%query%';  #查询慢日志相关信息,定位慢查询
slow_query_log  默认是off关闭的，使用时，需要改为on打开
slow_query_log_file 记录的是慢日志的记录文件
long_query_time  默认是10秒，每次执行的sql达到这个时长，就会被记录
show status like '%slow_queries%';  查看慢查询状态
set global long_query_time = 1  修改慢查询时间1s
set global slow_query_log = 'ON' #开启慢查询
注意:修改慢查询时间后，记得需要重新连接才可以生效,只要把navicat关掉重新打开就行，不需要重启mysql
可以通过EXPLAIN查询该语句是否生效，全部扫描
通过慢查询定位一些查询比较慢的sql语句，在使用explain 工具排查该sql语句索引是否有生效。
explain select * from person where id = 1  加上关键字explain就可以查询到该条sql的索引是否生效
SHOW KEYS FROM attence;
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明询，又是上述三种复杂查询中
type列: 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
市场要求索引的级别起码在range,比如select * from person就是index,把所有的索引查询出来再查询数据，select * from person where id >1那这个就是range级别，是根据二叉树查询的有范围的。如果索引是index或者all就需要优化，性能太低。
误区: index级别扫描全部索引的文件，all: 全表物理扫描

select * from person where phone = '13918774587' and post_number = '0140'
select phone,post_number from person where phone = '13918774587' and post_number = '0140'
如果查询速度慢，可以只返回索引字段，用索引查的返回的字段也是索引，如果返回的字段当中只要有一个不是索引里的，那就跟select * 没有任何区别


1.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
2. is null,is not null 也无法使用索引(解决方案，空值用专门特定常量值定义，比如def等)
EXPLAIN SELECT * FROM employees WHERE name is null
3. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫
EXPLAIN SELECT * FROM employees WHERE name != 'zhangsan'
4.字符串不加单引号索引失效
EXPLAIN SELECT * FROM employees WHERE name = 62440312321;
EXPLAIN SELECT * FROM employees WHERE name = '62440312321';
5.少用or,用它连接时很多情况下索引会失效
EXPLAIN SELECT * FROM employees WHERE name = 'zhangsan' or name = 'wangmazi';
6.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
EXPLAIN SELECT * FROM employees WHERE name like '%mei';
EXPLAIN SELECT * FROM employees WHERE name like 'mei%';
问题：解决like'%字符串%'索引不被使用的方法？
1. 使用覆盖索引，查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name FROM employees WHERE name like '%mei';
使用like避免索引失效用like 'mei%'这种方式，原理就是索引全部都是符合左侧原则，如果%mei就相当于每个索引都查了下，mei%就相当于最左侧是mei然后rang范围查出来对应索引。
如果一定要用%%这种like可以用select name from employes where name like '%mei%'  这样的话都是查的索引文件并且返回索引，没有去data回表查，然后在通过select * from employes where name in ('上个sql查询到的所有name'),这个时候用的索引是ref性能会很高，使用子查询select * from employes where id in (select id from employes where name like '%mei%');
 Mysql使用IN查询导致索引失效的情况
(https://blog.csdn.net/u010963948/article/details/90450014)
explain select * from attence force index(department_id) where department_id in (2 , 20   )
可以使用force index强制索引，原理就是mysql觉得那么多in，直接用all还快点，所以就取消了索引，但是实际测下来还是索引块，那就可以强制索引

可以设置单路排序的大小，超过这个大小就是双路，双路排序会回表，速度慢，但是不占内存.单路排序占用内存，但是数据都在内存里排序操作不会回表，性能高
SHOW VARIABLES LIKE '%max_length_for_sort_data%';
SET max_length_for_sort_data = 1024;

left join左连接，左表为主，如果右表数据没有就全部为null
right join右连接，右表为主，如果左表数据没有就全部为null
inner join内敛: 扫描的时候会优先选择小表先进行扫描再去关联大表，保证性能。
left join和right join要保证性能要考虑优先在对应那侧放小表提升性能

Extra是null说明回表了，因为返回的字段当中有一些并没有建立索引

optimizer_trace 分析sql索引语句
第一步:开启optimizer_trace
SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on;
第二步:下面两句sql在navicat中一起执行,然后点击结果2就可以查看到了,一条条执行不行，必须要一起执行
SELECT * FROM person order by person_account_id;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
 可以查看最下面的文档，搜索optimizer_trace就可以查询到
这个json关键字查询:
join_preparation第一阶段：SQl准备阶段
join_optimization:第二阶段：SQL优化阶段
rows_estimation: 预估标的访问成本
range_analysis: 全表扫描情况
rows: 扫描行数
cost: 查询成本 (到底是全表扫描还是用索引就看这个cost谁更小花的时间越少就用哪种)
potential_range_indexes: 查询可能使用的索引
analyzing_range_alternatives: 分析各个索引使用成本
chosen: 是否选择该索引
join_execution: 第三阶段：SQL执行阶段
可以查看: [https://blog.csdn.net/weixin_31476341/article/details/113909485](https://blog.csdn.net/weixin_31476341/article/details/113909485)

select count(*) from person;
select count(1) from person;
select count(id) from person;
select count(name) from person;
select count(*)和select count(1)性能是一样的没有任何区别。
select count(name)排除了name为null的数据
select count(id)包含其他字段为null的情况
理论上count(name)比count(id)要快，因为count(id)查找的叶子节点是id索引对应的data数据，需要从data数据里查，而name索引对应的是id，查完后不需要回表

show open tables;    查看表上加过的锁
unlock tables;       删除表锁

//删除主外键关联的表的强链接
SET foreign_key_checks = 0 删除外键约束
TRUNCATE TABLE attence 删表
SET foreign_key_checks = 1 启动外键约束

//如何杀死事务的进程号
select *  from information_schema.innodb_trx t;    查询开启的事务信息
select t.trx_mysql_thread_id from information_schema.innodb_trx t;  查询开启的事务的进程号
kill 5;

//开启事务提交和回滚
BEGIN;
update person set avatar_url = null,body_url=NULL where phone = '13918789456';
COMMIT;
ROLLBACK;


mysql默认的事务隔离级别为 repeatable-read 可重复读
select @@tx_isolation;

MySQL事务隔离级别
脏读:  当前session读取到另外session未提交的事务的数据，另外session有可能会回滚该数据。 
可重复读: 当前事务中已经查询到数据，在事务结束之前，如果有其他的Session对该数据做修改并且提交，还是用原来的数据。 
不可重复读: 当前事务中已经查询到数据，如果其他的Session对该数据发生改变的并且提交，用最新的数据。 
幻读: 幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。(比如事务隔离级别是可重复读

select … for update 语句是通过查询进行加锁，也就是id=2这条数据begin后没有commit之前，查询好后，就将id=2这条数据进行了行锁，其他session无法对它进行修改
begin;
select * from person where id=2 for update;
commit;

间隙锁的作用
sessionA
begin;
update person set name='zhangsan6' where id>18 and id<22;
commit;
从id>18  and id <22 上了间隙锁，在没有释放锁的时候 其他的session无法对该段位做操作。

sessionB
INSERT INTO `person  ` VALUES (19, 'zhangsan19', '500');


如何避免行锁升级表锁（不管是delete或者update，where后面的条件一定要是索引字段，否则会查询全表，这样就会锁表）
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则都会从行锁升级为表锁。
sessionA
begin;
update person  set name='zhangsan6'  where balance='300';
commit;

修改的时候查询的条件不是索引字段，会走全表扫描 全表扫描的时候对每行数据都加上行锁
，最终形成表锁。
sessionB
无法修改该任意一条数据，直接发生表锁。
删除表锁
unlock tables;
避免引起标所的方案，最好更新的时候使用索引字段，否则的话会进行全表扫描就会引发表锁

【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

shardingJDBC

ShardingJDBC官网
https://gitee.com/Sharding-Sphere
https://shardingsphere.apache.org/document/current/cn/overview/  使用说明
使用Sharding-JDBC 分库分表
https://www.cnblogs.com/coderzhw/p/11094305.html
https://www.i847.cn/article/13.html

这篇关于mysql性能优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！