mysql 高级和索引优化,目的:查的好,查的快,性能好

1-事物隔离级别：

更新丢失，

并发情况下，对同一字段进行更新，就会出现更新丢失，采用乐观锁，比较版本号或时间戳可解决

读未提交

解决了更新丢失但是会引起脏读，二个session。sessionA中执行sql语句，读到了sessionB中未提交的数据

读已提交，

解决了更新丢失，脏读，但是会出现不可重复读，二个session。 sessionA未提交，执行查询语句读到了sessionB中提交，更新数据。乍一看没有问题，但他却不满足隔离性，实际开发sessionA中第一次读到数据为a，然后执行了一系列操作，此时sessionB提交了更新操作将a改为b并提交，之后sessionA再次读取这条语句，读到了b，这样就是不对的，应该是不论读取多少次，都是a，如果sessionA提交了，此时读到b是正确的

可重复读，

解决了，更新丢失，脏读，不可重复读，但是会出现幻读，什么是幻读？MVCC机制，select语句读取的是快照，这个快照不一定是最新的，insert update语句是操作的最新的快照，二个session，sessionA中查询语句有四条id最大为4，sessionB新增了一条语句id为5并已提交，此时由于隔离级别是可重复读，所以sessionA读操作仍然是四条数据，但此时如果sessionA执行更新操作，对id为5的数据进行更新，发现是可以更新成功的，这就出现了幻觉，幻读。
如何解决幻读？序列化，或者使用间隙锁，或者代码中使用各种锁机制。什么是间隙锁？了sessionA执行更新操作 update tableA set name="abc" where id>5 and id<15 ，此时数据库中数据共5条，id最大为5，sessionB中对同一个表新增一条数据，此时不会成功会一直阻塞在这里。将id为6-14 的数据都锁起来了，这就是间隙锁

序列化

一:mysql逻辑架构介绍: 四层: 连接层, 服务层,引擎层,硬件存储层

mysql的存储引擎对比: 这里只对比 MyISAM 和 InnoDB

=======================================================================================================================

七种JOIN理论, 这个七种sql语句必须要掌握

内连接: select * from tabelA a inner join tableB b on a.key = b.key
左连接: select * from tabelA a left join tableB b on a.key = b.key
右连接: select * from tabelA a right join tableB b on a.key = b.key
A独有 : select * from tableA a left join tableB b on a.key = b.key where b.key is null
B独有 : select * from tableA a right join tableB b on a.key = b.key where a.key is null
A,B满连接: 这里要使用到 union 关键字,这个关键字作用合并去重
select * from tableA a left join tableB b on a.key = b.key
union
select * from tableA a right join tableB b on a.key = b.key
A独有+B独有:
select * from tableA a left join tableB b on a.key = b.key where b.key is null
union
select * from tableA a right join tableB b on a.key = b.key where a.key is null

=======================================================================================================================

二:索引优化:
2.1: 什么是索引: 排好序的快速查找的数据结构. 从定义可以看出索引的二个维度, 对应sql语句的二个地方, where条件, Order by

2.2:什么情况下应该创建索引:
1: 主键自动建立唯一索引,
2: 频繁作为查询条件的字段应该创建索引,
3: 查询中与其他表关联的字段, 外键关系建立索引
4: 频繁更新的字段不适合创建索引, 因为每次更新不单单是更新了记录,记录更新后还会更新索引
5: where条件中用不到的字段不创建索引
6: 单键索引和组合索引的选择? (高并发情况下倾向组合索引)
7: 查询中排序的字段,排序字段若通过索引去访问,将大大提高排序速度 (这句话意思是: 假如有三个字段,name,age,email的索引,我们希望mysq按照这个 name,age,email顺序来查找, order by 排序的时候也要按照这个顺序来排序,就是说我们建立索引字段时候,还要考虑是否和 order by字段撞车)
8: 查询中统计或者分组字段,(group by 的字段, 也与索引有关系, 分组的前提是已经排好序)

什么情况下不要创建索引:
1: 表记录太少, 建与不建都一样
2: 经常增删改的字段,不建索引, 因为如果建了索引,提高了查询速度,却降低了更新表(不仅要保存数据还要保存索引文件)的速度
3: 数据重复且分布均匀的字段,没有必要建索引, 比如国籍, 性别,

=======================================================================================================================

2.3: mysql有四层架构,第二层服务层,中有个sql优化分析器,他会检查sql语句,按照自己认为最优的检索方式去执行, 但这种方法不一定是我们认为最优的方式, 我们通过什么方式可以知道某一条sql语句mqsyl的执行方式和我们认为的不同呢? 有4种方式
答:通过 explain 关键字 (执行计划),
怎么用: explain + sql语句结果如图:

能干嘛: 1:表的读取顺序, 2:数据读取操作的操作类型, 3:哪些所以可以使用, 4:哪些索引被实际使用, 5:表之间的引用, 6: 每张表有多少行被优化器查询

2.3.1: 这些表头的字段分别是什么意思:接下来一一解释
id: 他是select查询的序列号.包含一组数字,表示查询中执行select子句或操作表的顺序,
有三种情况:
1: id相同,执行顺序由上到下,看图 id指的是 t1, t2 , t3表的操作顺序,都为1, 顺序执行

2: id不同, 如果是子查询,id的序号会递增, id值越大优先级越高,越先被执行,看图, select_type 字段解释: primary(主查询) subquery(子查询)

3: id相同不同,同时存在, 看图

select_type: 主要是用于区别查询的类型, 简单查询,联合查询,子查询,等复杂查询, 看图如下:

type:访问类型排列, 显示查询使用了何种类型,共8种值,从最好到最差依次是: system > const > eq_ref > ref > range > index > All 一般来说,得保证查询至少达到range级别,最好能达到ref
All:表示全表扫描,所以最差,
system: 表中只有一行记录(等于系统表) 这只是const类型的特例,一般不会出现,可以忽略不计
const: 表示通过索引一次就找到了,const用于比较 primary key或者 unique索引,因为只匹配一行数据,所以很快, 比如将主键置于 where列表中,mysql就能讲该查询转换一个常量
eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,比如主键, 唯一索引扫描
ref: 非唯一性索引扫描,返回匹配某个单独值得所有行, 本质上也是一种索引访问,它返回匹配某个值得所有行,可能有多个行,属于查找和扫描的混合体
range: 只检索给定范围的行,使用一个索引来选择行, key列显示使用了哪个索引, 比如where语句中的 between, < ,>, in等的查询,
index: 指的是整个索引树的扫描.比All快,
All : 全表扫描. 是从硬盘中读取

possible_keys 和 key 显示是否使用到了索引, 是否索引失效,多个索引竞争的情况下,mysql使用到哪个索引. possible_keys,查询时候,只要涉及字段上存在索引, 就将该索引列出,但不一定被查询实际使用
key:显示查询实际上使用了哪个索引,如果为null, 要么没有索引,要么建了索引,没有使用到,索引失效.. 查询中如果使用了覆盖索引,则该索引仅出现在key列中,
举例看图: 先创建索引 create index idx_col1_col2 on t1(col1,col2) 这个sql是t1表创建了 col1,col2 二个索引, 图中的sql语句是在t1表中查询 col1, col2,这二个列,看执行计划, type类型为index,说明是扫描索引树, possible_keys 为null,说明按道理没有使用索引, key= idx_col1_col2, 说明实际上使用了索引, 刚好查询的col1, col2 这二个列的顺序,个数,与所建的复合索引,idx_col1_col2刚好一一匹配, mysql就会从索引树上取,而不用全表扫描了, 这个索引也就是覆盖索引,仅出现在key中,possible_keys列不会出现

key_len: 表示索引中使用的字节数,可通过这个值计算查询中使用索引的长度, 在不损失精度的情况下长度越短越好, key_len显示的值表示索引字段的最大可能长度.并非实际使用长度

看图:中可得到, 这二条sql语句,type= ref,用的是非唯一索引,由于where条件不同,条件越多,精度越高,对应的key_len 越大,而结果是一样的,

ref: 显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值, 如图所示,: t2表的 col1字段,和 const常量用来查找索引列的值

rows: 根据表统计信息级索引选用情况,大致估算出找到所需的记录所需要读取的行数, 这个越小越好

Extra: 包含不适合在其他列中显示但十分重要的额外信息比如 Using filesort, Using temporary, Using index, Using where, Using join buffer(表示使用了连接缓存),
Using filesort: (九死一生)文件内排序,就是说mysql自己重新排序,然后读取,二不是按照表内的索引顺序进行读取, mysql无法利用索引完成排序的操作叫文件排序,导致性能速度变慢, 比如看图:

Using temporary: (十死无生)使用了临时表保存中间结果,mysql在对查询结果排序时使用了临时表, 常见于排序 order by 和分组查询 group by,

Using index: 这个就是前面说过的覆盖索引, 如果查询的字段, 顺序和个数,刚刚好和联合索引的顺序和个数一致, 或者部分一致(联合索引,只用了其中几个), 那么此时的查询,mysql会直接从索引树中拿,并返回, 这种查询的效率是很高的

=======================================================================================================================
好了,前面都是打基础, 了解一些 explain计划表头各个字段的含义, 接下来开始进入正题,索引优化
案例一: 单表索引优化: sql语句中如果用到了范围查询, 范围所在字段,不要建索引, 因为该范围字段会让mysql引起文件内排序
案例二: 二张表关联的索引优化: 如果是左连接sql语句(select * from tableA a left join tableB b on a.key = b.key),在右表 key字段建立索引,因为左表是全部,右表驱动左表,所以在右表建索引(建在左表的话是全部扫描). 同理如果是右连接sql语句,在左表建索引
案例三: 三表索引优化: 比如 select * from class left join book on class.card = book.card left join phone on book.card = phone.card, 这种三表关联的左连接sql语句,在右表建索引, 于是在 book, phone表的card字段建立索引
尽可能减少JOIN语句中的嵌套循环总次数, 永远用小结果集驱动大的结果集,
优先优化嵌套循环中的内层循环,
保证join语句中被驱动表上join条件字段已经被索引
当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下, 不要太吝啬 JoinBuffer的设置(有时候可以调大些)

=======================================================================================================================

索引失效(应该避免):各种原因如下

联合索引的顺序, 和 sql语句中where条件的字段顺序,保持一致, 最好不要断, 口诀:带头大哥不能死,中间兄弟不能断
sql语句中有in , like ,between and, >, < 等等这些范围字段, 这些范围条件右边的列如果有索引, 该索引会失效,

like %abc% %写在字段最后,这种情况下可以避免全表扫描, 其余情况都会导致全表扫描

问题: 解决like '%字符串%' 时索引不被使用的方法?
答: 使用覆盖索引,explain结果是扫描索引树index, 假如设置的联合索引是: name, age,email, 那么只要 select 后的字段,与这个联合索引沾边(比如: select name from.... select name, age from....... select name, age,email from...... select age from..... select age ,email from...... select email from.... select id from... 因为id是主键索引),结果都是扫描索引树, 如果select 后的字段大于了联合索引,或者出现了联合索引中不存在的字段结果都是全表扫描, (比如 : select * from ...... select name, age,email, sex from ...... select name,sex from ...... 索引失效这些都是全表扫描 )

重罪: 字符串不加单引号导致索引失效, 比如name = 2000 和 name = '2000' 都能查出数据,是因为mysql 内部会有自动转型,将2000,转为string类型, 由于转型了,索引会失效, 看执行计划可以看到没有使用到索引....

口诀:
带头大哥不能死,中间兄弟不能断.
永远符合最左原则,索引列上无计算(包括:手动,自动,函数,隐式转换).
like % 加右边,范围右边全失效
字符串里有引号

查询截取分析: 1:查询优化, 2:慢查询日志, 3:批量数据脚本, 4: Show Profile 5:全局查询日志

分析=======
1: 观察,至少跑一天,看看生产的慢sql情况
2: 开启慢查询日志, 设置阈值,比如超过5秒就是慢sql, 并将他抓取出来, 默认是关闭的,
---2.1 (show variables liek '%slow_query_log%'; 可以看到默认是关闭的,看到日志文件的路径)
---2.2 怎么开启: set global slow_query_log = 1( 这个只对当前的数据库生效,如果mysql重启就失效了), 如果要永久生效,就必须修改配置文件my.cnf,
---2.3 接下来设置阈值(show variables liek 'long_query_time';查看默认阈值多少, 大于这个阈值,才会被记录在日志文件中) set global long_query_time = 3(设置阈值为3秒),此时需要重新开启一个会话才可以看到生效, 到日志文件中后,cat abc.log, 就可看到abc这条日志打印出来了.... 查询有多少条慢查询记录: show global status like '%slow_queries%';
3: explain + sql分析 (到这,能80% 知道原因,可能是sql写的问题)
4: Show Profile 查询慢sql在mysql服务器里面的执行细节和生命周期情况
5: 运维, 或DBA ,进行sql数据库服务器的参数调优

=========================================================
上面的都是查询优化, 接下来是排序优化, order by , group by

查询优化: 永远小表驱动大表(类似for循环嵌套, 最外层的for循环次数越少,性能好),

mysql 锁机制
锁分类: 从对数据的操作类型类分: 读锁,写锁. 对数据操作的粒度来说: 行锁,表锁
表锁: 偏向MYISAM存储引擎,开销小,加锁快, 无死锁; 锁定粒度大, 发生锁冲突的概率最高,并发度最低
查看锁的命令: show open tables; 可以查出那个库那张表有锁
手动给表加锁: clock table 表1名 read/write , 表2名 read/write;
释放表锁: unclock tables;

一个表设置了表读锁后, 不同的会话都可以读这个表,这个表如果要修改,需要先释放读锁

会话1:对某张表设置了读锁后, 其余的会话和这个会话1,读这张表是可以的, 会话1修改这个表不行,会话1读取别的表也不行,必须要这个表释放读锁,别的会话修改这个表也不行,会阻塞在这里,当这个表释放读锁之后,阻塞的sql会依次执行