現(xiàn)在市面上mysql主流版本號是5.5, 5.7 ,8.0栏笆，5.7這個版本相對來說穩(wěn)定性和兼容性都已經(jīng)得到市場的驗證断箫，是比較好的一個版本痕惋。

1.mysql中有MyISAM引擎與InnoDb引擎芍躏，他們之間區(qū)別是什么

InnoDb索引文件和數(shù)據(jù)文件是在一起的，只要查找索引文件后就可以連接到數(shù)據(jù)文件缅帘，查一次即可，效率高∧阉ィ現(xiàn)在主流使用InnoDb引擎

2.為什么InnoDb引擎表必須有主鍵钦无，并且推薦使用整形的自增方式?

即使不創(chuàng)建主鍵id，mysql還是會默認創(chuàng)建一個rowid作為自增主鍵盖袭，用來做范圍查詢失暂，這個rowid是看不到的彼宠，uuid是隨機的，這個沒法做范圍查詢弟塞。

3.慢查詢定位

慢查詢
show variables like '%query%';  #查詢慢日志相關(guān)信息,定位慢查詢
slow_query_log  默認是off關(guān)閉的凭峡，使用時，需要改為on打開
slow_query_log_file 記錄的是慢日志的記錄文件
long_query_time  默認是10秒决记，每次執(zhí)行的sql達到這個時長摧冀，就會被記錄
show status like '%slow_queries%';  查看慢查詢狀態(tài)
set global long_query_time = 1  修改慢查詢時間1s
set global slow_query_log = 'ON' #開啟慢查詢
注意:修改慢查詢時間后，記得需要重新連接才可以生效,只要把navicat關(guān)掉重新打開就行系宫，不需要重啟mysql
可以通過EXPLAIN查詢該語句是否生效索昂，全部掃描
通過慢查詢定位一些查詢比較慢的sql語句，在使用explain 工具排查該sql語句索引是否有生效扩借。
explain select * from person where id = 1  加上關(guān)鍵字explain就可以查詢到該條sql的索引是否生效
SHOW KEYS FROM attence;
id:選擇標識符
select_type:表示查詢的類型楼镐。
table:輸出結(jié)果集的表
partitions:匹配的分區(qū)
type:表示表的連接類型
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引字段的長度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表條件過濾的行百分比
Extra:執(zhí)行情況的描述和說明詢往枷，又是上述三種復(fù)雜查詢中
type列: 依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL
市場要求索引的級別起碼在range,比如select * from person就是index,把所有的索引查詢出來再查詢數(shù)據(jù)框产，select * from person where id >1那這個就是range級別，是根據(jù)二叉樹查詢的有范圍的错洁。如果索引是index或者all就需要優(yōu)化秉宿，性能太低。
誤區(qū): 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 * 沒有任何區(qū)別


1.不在索引列上做任何操作（計算、函數(shù)今艺、（自動or手動）類型轉(zhuǎn)換）韵丑，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
2. is null,is not null 也無法使用索引(解決方案，空值用專門特定常量值定義虚缎，比如def等)
EXPLAIN SELECT * FROM employees WHERE name is null
3. mysql在使用不等于（撵彻！=或者<>）的時候無法使用索引會導(dǎo)致全表掃
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%'這種方式陌僵，原理就是索引全部都是符合左側(cè)原則，如果%mei就相當于每個索引都查了下创坞，mei%就相當于最左側(cè)是mei然后rang范圍查出來對應(yīng)索引碗短。
如果一定要用%%這種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查詢導(dǎo)致索引失效的情況
(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還快點搭盾，所以就取消了索引咳秉，但是實際測下來還是索引塊，那就可以強制索引

可以設(shè)置單路排序的大小鸯隅，超過這個大小就是雙路澜建，雙路排序會回表，速度慢蝌以，但是不占內(nèi)存.單路排序占用內(nèi)存炕舵，但是數(shù)據(jù)都在內(nèi)存里排序操作不會回表，性能高
SHOW VARIABLES LIKE '%max_length_for_sort_data%';
SET max_length_for_sort_data = 1024;

left join左連接跟畅，左表為主咽筋，如果右表數(shù)據(jù)沒有就全部為null
right join右連接，右表為主徊件，如果左表數(shù)據(jù)沒有就全部為null
inner join內(nèi)斂: 掃描的時候會優(yōu)先選擇小表先進行掃描再去關(guān)聯(lián)大表奸攻，保證性能。
left join和right join要保證性能要考慮優(yōu)先在對應(yīng)那側(cè)放小表提升性能

Extra是null說明回表了虱痕，因為返回的字段當中有一些并沒有建立索引

image.png

optimizer_trace 分析sql索引語句
第一步:開啟optimizer_trace
SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on;
第二步:下面兩句sql在navicat中一起執(zhí)行,然后點擊結(jié)果2就可以查看到了,一條條執(zhí)行不行睹耐，必須要一起執(zhí)行
SELECT * FROM person order by person_account_id;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
 可以查看最下面的文檔，搜索optimizer_trace就可以查詢到
這個json關(guān)鍵字查詢:
join_preparation第一階段：SQl準備階段
join_optimization:第二階段：SQL優(yōu)化階段
rows_estimation: 預(yù)估標的訪問成本
range_analysis: 全表掃描情況
rows: 掃描行數(shù)
cost: 查詢成本 (到底是全表掃描還是用索引就看這個cost誰更小花的時間越少就用哪種)
potential_range_indexes: 查詢可能使用的索引
analyzing_range_alternatives: 分析各個索引使用成本
chosen: 是否選擇該索引
join_execution: 第三階段：SQL執(zhí)行階段
可以查看: [https://blog.csdn.net/weixin_31476341/article/details/113909485](https://blog.csdn.net/weixin_31476341/article/details/113909485)

image.png

select count(*) from person;
select count(1) from person;
select count(id) from person;
select count(name) from person;
select count(*)和select count(1)性能是一樣的沒有任何區(qū)別部翘。
select count(name)排除了name為null的數(shù)據(jù)
select count(id)包含其他字段為null的情況
理論上count(name)比count(id)要快硝训，因為count(id)查找的葉子節(jié)點是id索引對應(yīng)的data數(shù)據(jù)，需要從data數(shù)據(jù)里查新思，而name索引對應(yīng)的是id窖梁，查完后不需要回表

show open tables;    查看表上加過的鎖
unlock tables;       刪除表鎖

//刪除主外鍵關(guān)聯(lián)的表的強鏈接
SET foreign_key_checks = 0 刪除外鍵約束
TRUNCATE TABLE attence 刪表
SET foreign_key_checks = 1 啟動外鍵約束

//如何殺死事務(wù)的進程號
select *  from information_schema.innodb_trx t;    查詢開啟的事務(wù)信息
select t.trx_mysql_thread_id from information_schema.innodb_trx t;  查詢開啟的事務(wù)的進程號
kill 5;

//開啟事務(wù)提交和回滾
BEGIN;
update person set avatar_url = null,body_url=NULL where phone = '13918789456';
COMMIT;
ROLLBACK;


mysql默認的事務(wù)隔離級別為 repeatable-read 可重復(fù)讀
select @@tx_isolation;

MySQL事務(wù)隔離級別
臟讀:  當前session讀取到另外session未提交的事務(wù)的數(shù)據(jù)，另外session有可能會回滾該數(shù)據(jù)夹囚。 
可重復(fù)讀: 當前事務(wù)中已經(jīng)查詢到數(shù)據(jù)纵刘，在事務(wù)結(jié)束之前，如果有其他的Session對該數(shù)據(jù)做修改并且提交崔兴，還是用原來的數(shù)據(jù)彰导。 
不可重復(fù)讀: 當前事務(wù)中已經(jīng)查詢到數(shù)據(jù)，如果其他的Session對該數(shù)據(jù)發(fā)生改變的并且提交敲茄，用最新的數(shù)據(jù)。 
幻讀: 幻讀是針對數(shù)據(jù)插入（INSERT）操作來說的山析。假設(shè)事務(wù)A對某些行的內(nèi)容作了更改堰燎，但是還未提交，此時事務(wù)B插入了與事務(wù)A更改前的記錄相同的記錄行笋轨，并且在事務(wù)A提交之前先提交了秆剪，而這時赊淑，在事務(wù)A中查詢，會發(fā)現(xiàn)好像剛剛的更改對于某些數(shù)據(jù)未起作用仅讽，但其實是事務(wù)B剛插入進來的陶缺，讓用戶感覺很魔幻，感覺出現(xiàn)了幻覺洁灵，這就叫幻讀饱岸。(比如事務(wù)隔離級別是可重復(fù)讀

select … for update 語句是通過查詢進行加鎖，也就是id=2這條數(shù)據(jù)begin后沒有commit之前徽千，查詢好后苫费，就將id=2這條數(shù)據(jù)進行了行鎖，其他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后面的條件一定要是索引字段牍汹，否則會查詢?nèi)眍砦@樣就會鎖表）
InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖慎菲，并且該索引不能失效嫁蛇，否則都會從行鎖升級為表鎖。
sessionA
begin;
update person  set name='zhangsan6'  where balance='300';
commit;

修改的時候查詢的條件不是索引字段钧嘶，會走全表掃描 全表掃描的時候?qū)γ啃袛?shù)據(jù)都加上行鎖
棠众，最終形成表鎖。
sessionB
無法修改該任意一條數(shù)據(jù)有决，直接發(fā)生表鎖闸拿。
刪除表鎖
unlock tables;
避免引起標所的方案，最好更新的時候使用索引字段书幕，否則的話會進行全表掃描就會引發(fā)表鎖

【推薦】單表行數(shù)超過 500 萬行或者單表容量超過 2GB新荤，才推薦進行分庫分表。
說明：如果預(yù)計三年后的數(shù)據(jù)量根本達不到這個級別台汇，請不要在創(chuàng)建表時就分庫分表苛骨。

shardingJDBC

ShardingJDBC官網(wǎng)
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