表的数据量
数据量越大，树的高度就会变高，理论上三层索引树的高度最为理想，可以支持百万级别的数据量
解决：可以使用分表(横切，竖切)，分库，增加缓存，解决数据量大，查询慢
索引键值过长
数据类型
desc/explain
执行计划：在一条sql执行之前，指定执行的方案
desc select * from s1;
mysql> desc select * from ceshi_table;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | ceshi_table | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from ceshi_table;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | ceshi_table | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
select_type
table
type
显示执行计划的类型，优先级从低到高如下，优化时至少达到range或者ref级别
all < index < range < ref < eq_ref < const < system
all 全表扫描(不走索引)
index 全索引扫描
扫描整个索引树，才能获取到所有数据，这样的索引失去意义
desc select count(*) from s1;
range 索引范围扫描（注意点：范围太大，不能命中索引），慢查询
desc select * from s1 where id < 10; #type=range
desc select * from s1 where id < 1000000; #type=all
desc select * from s1 where id between 1 and 10;#type=range
desc select * from s1 where id between 1 and 100000; #type=all
desc select * from s1 where email like "%w%"; #type=all
desc select * from s1 where email like "w%"; #type=range
‘‘‘如果范围过大，不能命中索引，如果范围适当，可以命中索引‘‘‘
desc select * from s1 where id in (1,2,3)
#对in或者or这样的语句进行优化
‘‘‘优化：union all比union速度快，union在合并数据之后，多一步去重操作‘‘‘
desc select * from s1 where id=1
union all
select * from s1 where id = 1;
desc select * from s1 where id=1
union
select * from s1 where id = 1;
#优化or条件
desc select * from s1 where id = 10 or name =‘aaaa‘;
desc select * from s1 where id = 10
union all
select * from s1 where name = ‘aaaa‘;
ref 普通索引查询(非唯一)
desc select * from s1 where email = ‘xboyww10@oldboy‘;
desc select * from s1 where id = 10;
eq_ref 唯一性索引(联表)
alter table s1 drop index index_id;
alter table s1 add primary key(id);
desc select * from s1 where id = 10;
create database db0624;
use db0624;
#创建一张表
create table class1(id int,classname varchar(255));
create table student1(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
class_id int
);
#插入数据
insert into student1 values(null,;"wangbaoqiang",82,2);
insert into student1 values(null,;"wanglihong",7,1);
insert into student1 values(null,;"wangwen",7,2);
insert into class1 values(1,"python30");
insert into class1 values(2,"python31");
‘‘‘要求：应用在多联表中，被关联的字段需要主键或者唯一，表之间的关系为一对一并且数据条数相同‘‘‘
desc select student1.age from student1,class1 where student1.class_id = class1.id;#ALL
alter table class1 add primary key(id);
desc select student1.age from student1,class1 where student1.class_id = class1.id;#INDEX
delete from student1 where id = 3;
desc select student1.age from student1,class1 where student1.class_id = class1.id;#EQ_REF
const：主键或者唯一索引(单表)
‘‘‘针对于primary key 和unique索引等值查询‘‘‘
desc select * from class1 where id=1; #const
desc select * from class1 where id>1; #range
system(了解)
possible_keys：执行sql时，可能用到的索引是谁
key：执行sql时，实际用到的索引是谁
show index from s1;展现表s1所有的索引
key_len :判断联合索引覆盖的长度(通过字节数可以判定出到底触发了那些)
#在没有not null约束的时候，默认预留一个字节，标记是空或者非空
#utf8 通常情况下，中文1个字符占用3个字节，字母占用1个字节，极个别的生僻字占4个字节
#varchar每次存储数据的时候，系统底层默认会额外预留2个字节
有not null(不为空) 没有not null(可为空)
tinyint 1 1+1
int 4 4+1
char(5) 5*3 5*3+1
varchar(5) 5*3 + 2 5*3+2+1
create table t100(
n1 int,
n2 int not null,
n3 char(5),
n4 char(5) not null,
n5 varchar(5),
n6 varchar(5) not null,
index index_n1(n1,n2,n3),
index index_n4(n4,n5,n6)
);
insert into t100 values(1,2,"a","b","aa","bb");
insert into t100 values(1,2,"a","b","aa","bb");
insert into t100 values(1,2,"a","b","aa","bb");
insert into t100 values(1,2,"a","b","cc","dd");
#把数据表中的数据导入
mysql> desc t100;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| n1 | int(11) | YES | MUL | NULL | |
| n2 | int(11) | NO | | NULL | |
| n3 | char(5) | YES | | NULL | |
| n4 | char(5) | NO | MUL | NULL | |
| n5 | varchar(5) | YES | | NULL | |
| n6 | varchar(5) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from t100;
+------+----+------+----+------+----+
| n1 | n2 | n3 | n4 | n5 | n6 |
+------+----+------+----+------+----+
| 1 | 2 | a | b | aa | bb |
| 1 | 2 | a | b | aa | bb |
| 1 | 2 | a | b | aa | bb |
| 1 | 2 | a | b | cc | dd |
+------+----+------+----+------+----+
4 rows in set (0.00 sec)
n1->5B n2->4B n3->16B 5+4+16=25
desc select * from t100 where n1 = 2 and n2 = 2 and n3 ="a";#命中n1 n2 n3
desc select * from t100 where n1 = 1 and n2 = 2 and n3 ="a";#如果有重复数据，不会触发联合索引
desc select * from t100 where n1 = 1 and n2 = 2;#如果有重复数据，不会触发联合索引
desc select * from t100 where n1 = 2 and n2 = 2;# n1 -> 5B n2->4B 命中n1,n2
desc select * from t100 where n1 = 2;#n1->5B 命中n1
desc select * from t100 where n1 = 2 and n3 = "a"; #n1-5B 命中n1，没有命中n3
#index(a,b,c)-> a,ab,abc 创建了三组索引，符合最前缀原则，第一个字段必须存在才能触发
A：原子性
C：一致性
I：隔离性
D：持久性
隔离性：隔离级别
+ 脏读：没提交的数据被读出来了
+ 不可重读：前后多次读书，结果数据内容不一样(同一个会话里，在不修改的情况下，永远只看到同样的一份数据)
+ 幻读：前后多次读取，结果数据的总量不一样
select @@ tx_isolation;
查询当前是否自动提交数据
select @@ autocommit;
修改mysql配置文件
D:\MYSQL5.7\mysql-5.7.25-winx64\my.ini
#系统默认的隔离级别（REPEATABLE-READ）
transaction_isolation = READ-UNCOMMITTED
#防止系统自动提交数据
autocommit = 0
#重启mysql
net stop mysql
net start mysql
脏读
不可重复读
#窗口1
begin;
update t1 set k1=‘abc‘ where id=1
select * from t1;
commit;
#窗口2
select * from t1;数据也跟着改了是不可重读
幻读
#窗口1
begin;
insert into t1 values(4,‘c‘,50);
select * from t1;
commit;
#窗口2
select * from t1;数量也跟着增加是幻读
通过二次提交commit，可以让多用户同步数据；
事务应用的技术(了解)
添加、删除 约束 not null
#alter table 表名 modify 字段名 类型
alter table t1 modify id int not null
alter table t1 nodify id int
添加、删除 unique唯一索引
#alter table 表名 add unique(id)
alter table t1 add unique(id)
alter table t1 drop index id
添加、删除 primary key
#alter table 表名 add primary key(id)
alter table t1 add primary key(id)
alter table t1 drop primary key
添加、删除 foreign key 外键（先通过desc表 找到外键的名字，然后再删）
alter table student1 drop foreign key student1_ibkf_1#删除
alter table student1 add foreign key(classid) references class(id)#添加
mysql sql优化 & 事务处理特征 & 约束添加、删除
原文：https://www.cnblogs.com/leiyu567567/p/14953942.html