mysql 高级学习笔记

高级知识讲解：

一、索引知识：

　　1. 索引的概念：一种用于快速查找（排序）的数据结构。

　　2. mysql innerdb引擎试用Btree树来存储索引值。

　　3. 聚簇索引概念：一般都是主键做聚簇索引，它的特点就是表行数据的存储位置顺序和索引存的值的顺序一样。

　　注意：1.主键默认会建立聚簇索引 2.sqlserver中好像还可以自行建立聚簇索引，此时主键索引就不会是聚簇索引了。

　　4.唯一索引：就是列数据是唯一的，比如身份证号码。主键索引默认也是唯一的数据。

　　5.覆盖索引（索引覆盖）的概念：
　　覆盖索引不是指某种索引类型，而是说 select 字段刚好和索引建立的字段在个数、顺序一样。如果存在这样的话，那么都会使用到对应的索引。并且取值都是在索引中获得而不需要从数据行中得到。
　　ps: explain select appname,appprovider from tb_app
　　

从中可以看到 possible_keys是null,但是key实际是使用到了idx_appname_appprovider 这个索引.

6.建立索引的注意点：
　　1. 一般都是在where、group by、order by 和 联表查询的字段建立索引。
　　2. 非主键索引查找数据的方式是间接通过主键索引的值查找？？？
　　3. 联表查询碰到left、right join 的方式时，一般都是在词表建立索引，比如 A left join B 因为A表行是返回所有行数据，根据小表驱动大表的原则，所以要在B表中建立索引。
　　4. 一般都是以建立复合索引（多个列组成的索引）为优先,原因是：1. 复合索引可以节约空间 2.表的索引越多对表更新表的效率越差。3. 可能有时候使用到覆盖索引。

7. 索引失效问题：
　　1. where 条件字段的查询顺序必须要和建立索引的字段顺序一样，才能使用到索引。比如 建立的 idx_name_age（建立在name和age的索引），那么where 字段的必须要有name （第一个并必须要有才能用到这个索引查找顺序？？？
　　mysql 实践：name字段在第二个或是第三个位置都是可以使用到索引的，为什么呢 因为mysql优化器自动解析索引的正确使用方式），age 字段如果不存在还是会用到这个索引，单使用到这个索引的部分索引。
　　length 字段值会小。（带头大哥不能断，就是说复合索引的第一个字段必须出现）

二、锁
　　1.锁的分类：读、写锁两种分类

1.1 表锁
　　读表锁语法： lock table 表名 read 。 特点：读锁是一个共享锁，也就是说不同的会话都可以select 表的内容,但是不能更新表的内容，表锁是MyIsim 支持。没有事务级别的说法。
　　写表锁语法： lock table 表名 write。 特点：写锁是排它锁，也就是说只要给表设置了写锁，那么会话之间的操作就是排斥的。给表A设置写锁，那么两个会话对A的操作是不能同时进行的（除了select操作）
　　解锁语法： unlock tables.

2.锁的粒度：表锁，行锁，MyISAM引擎表锁和InnoDB行锁.

　　表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

　　　　行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。

　　页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。

2.1 行锁
　　在操作行锁的时候，首先要设置innodb 引擎的自动提交为0 set autocommite=0，然后手动提交更新操作 commit。

2.2 行锁支持事务.

2.2.1 事务有四种隔离级别：为了有效保证并发读取数据的正确性，提出的事务隔离级别.
　　数据库事务的隔离级别有4个，由低到高依次为Read uncommitted(未授权读取、读未提交)、Read committed（授权读取、读提交）、Repeatable read（可重复读取）、Serializable（序列化），这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

2.2.2 并发事务引起的几种问题：（表锁是没有事务并发问题出现的，因为每次都是锁住表，不存在脏读或是幻读的情况）
　　1、更新丢失
　　两个事务都同时更新一行数据，一个事务对数据的更新把另一个事务对数据的更新覆盖了。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
　　2、脏读
　　一个事务读取到了另一个事务未提交的数据操作结果。
　　3、不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。
　　包括以下情况：
　　（1） 虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。
　　（2） 幻读（Phantom Reads）：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据（这里并不要求两次查询的SQL语句相同）。这是因为在两次查询过程中有另外一个事务插入数据造成的。


3.查看数据库锁的状态：
　　show open tables：查看数据库那些表上锁了。



三、 慢查询日志

　　1.常用explain sql 来分析sql的执行情况

　　2. show profile（首先要设置数据库的profile为1） 来查看sql的执行周期情况

　　　　show profiles;

　　　　show profile cpu,block io for query 1; 查看执行的query id 等于1 的那条sql执行情况（cpu,block io CPU和I/O 操作的情况）。

3. 开启慢查询日志：

　　1.查看慢查询查询是否开启情况： show variables like ‘%slow_query_log%‘;

　　2.设置全局的慢查询日志开启: set global slow_query_log=1;

　　3. 查看慢查询的设置时间： show variables like ‘%long_time_log%‘; 默认是10s

　　4. 设置慢查询的阙值时间: set global long_time_log=3； 设置慢查询的阙值时间为3s

mysql 高级学习笔记

标签：位置   做了   顺序   优化器   复合   left join   会话   time   appname