navicat?連不上虛擬機數(shù)據(jù)庫的常見原因防火墻沒有關(guān)閉
查看防火墻狀態(tài)：?systemctl status firewalld.service
綠的running表示防火墻開啟
執(zhí)行關(guān)閉命令：?systemctl stop firewalld.service
再次執(zhí)行查看防火墻命令：systemctl status firewalld.service
執(zhí)行開機禁用防火墻自啟命令? ：?systemctl disable firewalld.service
完成
1. 索引作用
提供了類似于書中目錄的作用,目的是為了優(yōu)化查詢
2. 索引的種類(算法)
B樹索引
Hash索引
R樹
Full text
GIS
聚集索引
MySQL自己生成的；聚集索引的葉子節(jié)點存的整行數(shù)據(jù)治唤，只能有一個
輔助索引
葉子節(jié)點應(yīng)該存的是id 再去調(diào)聚集索引虽风，可以有多個萤彩；
單列輔助索引
聯(lián)合索引（覆蓋索引）比較重要
唯一索引
索引的命令操作
查詢索引
pri? ? 主鍵索引
MUL? 輔助索引
UNI? 唯一索引
創(chuàng)建索引
alter table `2-27` add index idx_name(SKUID);
唯一索引
mysql>alter table `2-27` add unique index_uid(SSUID);
ERROR 1062 (23000): Duplicate entry '3679998' for key 'index_uid'
聯(lián)合索引
mysql>alter table `2-27` add index idx_SS(SKUID,SSUID);
前綴索引
alter table city add index idx_dis(district(5))；
7.3刪除索引
alter table `2-27` drop index idx_name;
展示索引
show index from `2-27`;
壓力測試
未優(yōu)化前的測試? 測試代碼如下：
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='city' --query="select * from city.nine where SKUID='10952'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
執(zhí)行計劃獲取及分析
(1)獲取到的是優(yōu)化器選擇完成的,他認(rèn)為代價最小的執(zhí)行計劃.作用:語句執(zhí)行前,先看執(zhí)行計劃信息,可以有效的防止性能較差的語句帶來的性能問題.如果業(yè)務(wù)中出現(xiàn)了慢語句淆党，我們也需要借助此命令進行語句的評估，分析優(yōu)化方案。(2)select獲取數(shù)據(jù)的方法1.全表掃描(應(yīng)當(dāng)盡量避免,因為性能低)2.索引掃描3.獲取不到數(shù)據(jù)
執(zhí)行計劃獲取
desc select * from city.nine where SKUID='10952';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key? | key_len | ref? | rows? | filtered | Extra? ? ? |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|? 1 | SIMPLE? ? ? | nine? | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL | 918678 |? ? 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
table: city ---->查詢操作的表 **
possible_keys: CountryCode,idx_co_po? ? ? ---->可能會走的索引? **
key: CountryCode? ---->真正走的索引? ? ***
type: ref? ---->索引類型? ? ? ? *****
Extra: Using index condition? ? ? ? ? ? ? ---->額外信息? ? ? ? *****
從左到右性能依次變好.
ALL:
全表掃描,不走索引
索引掃描：?index <range <ref <eq_ref < const(system)? <null
例子:1.查詢條件列,沒有索引SELECT*FROMt_100wWHEREk2='780P';
2.查詢條件出現(xiàn)以下語句(輔助索引列)
USEworldDESCcity;DESCSELECT*FROMcityWHEREcountrycode<>'CHN';
DESCSELECT*FROMcityWHEREcountrycodeNOTIN('CHN','USA');DESCSELECT*FROMcityWHEREcountrycodeLIKE'%CH%';
注意:對于聚集索引列,使用以上語句,依然會走索引DESCSELECT*FROMcityWHEREid<>10;
修改字段名
alter table ten change 序號 id int;
修改字段屬性
alter table ten modify 序號 int not null primary key;
index:全索引掃描
mysql>desc select * from ten;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key? | key_len | ref? | rows? | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
|? 1 | SIMPLE? ? ? | ten? | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL | 30835 |? 100.00 | NULL? |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql>desc select id from ten;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type? | possible_keys | key? ? | key_len | ref? | rows? | filtered | Extra? ? ? |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|? 1 | SIMPLE? ? ? | ten? | NULL? ? ? | index | NULL? ? ? ? ? | PRIMARY | 4? ? ? | NULL | 30835 |? 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
range:索引范圍掃描? <> = and between or like? 性能比較好的是> <
mysql>desc select * from ten where id>200;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type? | possible_keys | key? ? | key_len | ref? | rows? | filtered | Extra? ? ? |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
|? 1 | SIMPLE? ? ? | ten? | NULL? ? ? | range | PRIMARY? ? ? | PRIMARY | 4? ? ? | NULL | 15417 |? 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
最好把or改成union
ref? 輔助索引等值查詢
desc select? * from ten where SKUID= '1452256'
eq_ref :多表連接時,子表使用主鍵列或者唯一列作為連接條件：
A?join B
on a.x = B.y
const(system):主鍵和唯一鍵
壓力測試結(jié)果:
