navicat 连不上虚拟机数据库的常见原因防火墙没有关闭
查看防火墙状态： systemctl status firewalld.service
绿的running表示防火墙开启
执行关闭命令： systemctl stop firewalld.service
再次执行查看防火墙命令：systemctl status firewalld.service
执行开机禁用防火墙自启命令 ： systemctl disable firewalld.service
完成
1. 索引作用
提供了类似于书中目录的作用,目的是为了优化查询
2. 索引的种类(算法)
B树索引
Hash索引
R树
Full text
GIS
聚集索引
MySQL自己生成的；聚集索引的叶子节点存的整行数据，只能有一个
辅助索引
叶子节点应该存的是id 再去调聚集索引，可以有多个；
单列辅助索引
联合索引（覆盖索引）比较重要
唯一索引
索引的命令操作
查询索引
pri 主键索引
MUL 辅助索引
UNI 唯一索引
创建索引
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'
联合索引
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`;
压力测试
未优化前的测试 测试代码如下：
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
执行计划获取及分析
(1)获取到的是优化器选择完成的,他认为代价最小的执行计划.作用:语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题.如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。(2)select获取数据的方法1.全表扫描(应当尽量避免,因为性能低)2.索引扫描3.获取不到数据
执行计划获取
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.查询条件出现以下语句(辅助索引列)
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):主键和唯一键
压力测试结果:
