内容概要
- 如何操作索引
- 如何判断当前SQL语句是否走了索引
- 脏读、幻读、不可重复等理论
如何操作索引
# 查看当前表内部索引值
show index from m1;
# 删除索引
alter table m1 drop index 索引名(ind_name、uni_pwd)
alter table m1 drop index ind_name;
1、主键索引：
alter table m1 add primary key pri_id(id);
2、唯一键索引：
alter table m1 add unique key uni_pwd(pwd);
# 添加唯一键索引的前提是：保证数据没有重复。不然会报错。
# 怎样判断有重复的数据：
思路一：可以使用group by分组pwd，然后在使用count计数。
select name,count(pwd) from m1 group by pwd;
思路二：可以利用去重来判断是否有重复数据
select count(pwd) from m1; # 3
select count(distinct pwd) from m1; # 2，去重之前是3，之后是3
3、普通索引：
alter table m1 add index ind_name(name);
3.1、前缀索引（属于普通索引）：根据字段的前N个字符建立索引
# 什么时候使用前缀索引？
避免对大列（数据多的列）建索引，如果有使用前缀索引。
eg:搜过博客、百度等
alter table m1 add index ind_name(name(10)); # 把姓名前10个字符做成索引
3.2、联合索引：多个字段建立一个索引
# 最左使用原则：把最常用来做条件查询的列放在最前面
eg:相亲平台，搜索心意对象时候
alter table m1 add index idx_all(gender,age,money); # 把多个字段名建成一个索引
例子：
where a.女生 and b.年龄 and c.身家
index(a,b,c)
特点：前缀生成特效
a,ab,ac # 可以走索引或者部分走索引
b,bc,c,ca # 不走索引
explain句式（慢查询优化）
1、全表索引：在explain语句结果中type为ALL
不走索引、一行行查找数据、效率极其低下，生产环境下尽量不要书写类似的SQL
2、索引扫描：在explain语句结果中type不为ALL
走索引，加快数据查询，建议书写该类型SQL
3、explain：就是帮助我们查看SQL语句属于哪种扫描
查找：
explain select * from m1 where id=1; # 级别：const，走索引扫描
explain select * from student; # type；ALL，不建议使用这种 优化：limit 1;
4、常见的索引扫描：优化到级别到range就好
1) index
2) range
3) ref
4) eg_ref
5) const
6) system
7) null
# 从上到下，性能从最差到最好，我们认为至少要达到range级别。
5、不会走索引的情况：
1) 没有查询条件，或者查询条件没有建议索引
2) 查询结果集超过原表中的大部分数据（25%以上），不会走索引 # limit 控制下
3) 索引本身失效，统计数据不真实
4) 查询条件使用函数在索引列上或者对索引进行运算，运算包括（+，-，*，/）
5) 隐式转行导致索引失效
# 字段是字符类型，查询使用整形
create table m1(id int,name varchar(32),telnum varchar(32)) # telnul类型是字符
insert into m1 values(1,'hh,'110'),(2,'dd',220);
alter table m1 add index idx_tel(telnum); # 建立普通查询
explain select * from m1 where telnum=120; # 查询级别为ALL，telnul类型是字符
explain select * from m1 where telnum='120'; # 查询级别为ref，telnul类型是字符
6) <> ,not in不走索引
单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成union
7）like "%_"百分号在最前面不走
like '%100' : 不走索引
like '100%' ：走索引
8）单独引用联合索引里非第一位置的索引列不走 # 最左匹配原则
# 索引的创建会加快数据的查询速度，但是一定程度会拖慢数据的插入和删除速度
# 重点：2、5、7、8
隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改。
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，其他事务可以读取未提交的数据，这一现象也称之为"脏读" # 读的是修改了但是没有提交的数据称之为脏读。
2.read committed(提交读) # InnoDB默认支持
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
浙公网安备 33010602011771号