索引结构-优化查询

什么是索引：

MySql 官方对索引的定义为：索引（Index）是帮助MySql高效获取数据的数据结构。
由此可以得到索引的本质：索引是一种数据结构。

可以简单理解为：排好序的快速查找数据结构
在数据之外，数据库系统还维护了满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式实例：

一般来说 索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式储存在磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，唯一索引默认都是使用B＋树索引。统称索引。当然，除了B＋树这种类型的索引之外，还有哈希索引（hashindex）等

索引优劣势

提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
虽然索引大大提高了查询速度，但是它却会降低更新表的速度，如对表进行INSERT，UPDATE，DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件变化后的索引信息

索引分类：

单值索引： 即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引： 索引列的值必须唯一，但允许有空值
复合索引：一个索引包含了多个列

• 创建

create [unique] index indexName on table(columnname1，columnname1)；
alter mytable add [unique] index [indexName] on (columnname)

• 删除

drop index indexName on table;

• 查看

show index from table_name\G

mysql 索引结构：

• BTree索引
• Hash索引
• full-text全文索引
• R-Tree索引

Explain

是什么？

使用Explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的，分析你的查询语句或是表结构的性能瓶颈

• id - 表的加载顺序
  id相同： 执行顺序由上至下
  

• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  

• id不同 id相同 同时存在
  
  先走id为2的表，然后再走id为1的表，id为1有两个，需要顺序执行，先执行第一个id为1，再执行第二个id为1。

• select_type - 数据读取操作类型

  • SIMPLE : 简单的select查询，查询中不包含子查询或者union
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则贝标记为primary
  • SUBQUERY: 在select或where列表中包含了子查询
  • DERIVED：在form列表中包含的子查询被标记为derived（衍生） Mysql会把这些子查询的查询结果放在临时表里
  • UNIOX：若第二个select出现在union之后，则被标记为union；
  • UNIOXRESULT： 两种union 表的合并结果

• table-表

  • 显示这一行的数据是关于哪张表的

• type-访问类型排序

  • 显示查询使用了何种类型，从最好到最差依次是：system > const > eq_ref > ref > range > index > all all为最差的 代表了全表扫描

    • system ： 表只有一行记录，这是属于const类型 平时不会出现，这个也可以忽略不计

    • const : 表示通过索引一次就找到了，const用于比较primary 或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，MySql就能将该查询转换为一个常量
      

    • eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

    • ref: 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

    • range : 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where 语句中出现了between，< ,> ,in 等的查询，这中范围扫描索引要比全表扫描要好，因为它是区间扫描。

    • index ： 检索索引树； 索引在内存中

    • all ： 检索磁盘中的全表

• possible_keys- 哪些索引可以使用

• key- 哪些索引实际被使用

  • 查询中若使用了覆盖索引，则该索引仅出现在key列表中，select 的字段 与 索引的字段刚好吻合，（建立索引时的个数与顺序一致）

• key_len

  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，长度越短越好，显示的值为索引字段的最大可能长度，并非实际使用长度，key_len是根据表定义计算而得，不是通过表内检索出的，同样的查询结果，越小越好

• ref 表之间的引用

  • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

• rows 每张表有多少行被优化器查询 :

  • 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要记录的行数，越少越好。

• Extra： 包含不适合在其他列中显示但十分重要的额外信息

  • Using filesort ：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作称为文件排序。简单来说就是没有使用到索引的排序，使用的是内部重新排的序
  • Using temporary:使用了临时表保存中间结果。Mysql在对查询结果排序时使用临时表，常见于排序order by 和分组查询group by。
  • Using index ： 使用了覆盖索引，避免访问了表的数据行， 如果同时出现using where ，表面索引被用来执行索引键值的查找，如果没有同时出现using where ，表明索引用来读取数据而非执行查找动作。
  • Using where : 使用了where
  • Using join buffer ：使用了连接缓存
  • impossible where ： where 的值始终为false

索引条件

• 全值匹配我最爱
  索引 idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

• 最佳左前缀法则
  如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
  经过试验结论 建立了 idx_nameAge 索引 id 为主键
  1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。
  既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
  2.除开上述条件 才满足最左前缀法则。
  
• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  
• 存储引擎不能使用索引中范围条件右边的列
  范围 若有索引则能使用到索引，范围条件右边的索引会失效(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)
  
• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
  
• mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
  索引 idx_nameAgeJob
  idx_name
  使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型
  前提 where and 后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name=‘xxx’ ,这种情况下，mysql自动优化，将 name=‘xxx’ 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)
  
• is not null 也无法使用索引,但是is null是可以使用索引的
  
• like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
  like ‘%abc%’ type 类型会变成 all
  like ‘abc%’ type 类型为 range ，算是范围，可以使用索引
  
• 字符串不加单引号索引失效
  底层进行转换使索引失效，使用了函数造成索引失效
  
• 少用or,用它来连接时会索引失效
  

查询优化

单表：

• 全值匹配
• 最左前缀匹配原则等等
• 建立最合适的索引

多表：

• 被驱动表 join 后的表为被驱动表 (需要被查询)
• left join 时，选择小表作为驱动表，大表作为被驱动表。索引加在右表上，left join 相反
• inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
• 子查询尽量不要放在被驱动表，有可能使用不到索引。

子查询：

• 永远小表驱动大表
  
  

  Orderby:

  • 尽量使用Index方式排序，避免使用File Sort方式排序，尽可能在索引列上完成排序操作，遵照索引建的最左前缀，如果不在索引列上，file sort有两种算法：
    单路排序：
    MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
    读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
    双路排序
    从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
    它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，
    因为它把每一行都保存在内存中了。

group by:

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀
• 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
• where高于having，能写在where限定的条件就不要去having限定了。

慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

查看/使用：
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，
可以通过设置slow_query_log的值来开启

SHOW VARIABLES LIKE '%slow_query_log%';

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，
如果MySQL重启后则会失效。

多慢的sql才会被记录呢？
这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
命令：SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阙值时间

set global long_query_time=1

修改为阙值到1秒钟的就是慢sql

为什么设置后看不出变化？

需要重新连接或新开一个会话才能看到修改值。 SHOW VARIABLES LIKE 'long_query_time%';  
或者通过set session long_query_time=1来改变当前session变量;

查询当前系统中有多少条慢查询记录

show global status like '%Slow_queries%';

实验一条慢sql

跟踪日志信息

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
 
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
 
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
 
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

Count: 2 Time=2.79s (5s) Lock=0.00s (0s) Rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]

出现次数(Count),
执行最长时间(Time),
累计总耗费时间(Time),
等待锁的时间(Lock),
发送给客户端的行总数(Rows),
扫描的行总数(Rows),

show profile

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
是否支持，看看当前的mysql版本是否支持

Show  variables like 'profiling';

默认是关闭，使用前需要开启

开启功能，默认是关闭，使用前需要开启

show variables like 'profiling';
 
set profiling=1;

select * from emp group by id%10 limit 150000;
select * from emp group by id%20  order by 5

查看所有执行过的sql show profiles;

诊断SQL，show profile cpu,block io for query n; (n为上一步前面的问题SQL数字号码);

type:  
 | ALL                        --显示所有的开销信息  
 | BLOCK IO                --显示块IO相关开销  
 | CONTEXT SWITCHES --上下文切换相关开销  
 | CPU              --显示CPU相关开销信息  
 | IPC              --显示发送和接收相关开销信息  
 | MEMORY           --显示内存相关开销信息  
 | PAGE FAULTS      --显示页面错误相关开销信息  
 | SOURCE           --显示和Source_function，Source_file，Source_line相关的开销信息  
 | SWAPS            --显示交换次数相关开销的信息

全局日志抓取

在mysql的my.cnf中，设置如下：
#开启
general_log=1   
# 记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE


 
命令
set global general_log=1;
 
#全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中
set global log_output='TABLE';
 
 
 
 
 此后 ，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
 
select * from mysql.general_log;
 
 
 

尽量不要在生产环境开启这个功能。

sql 优化整个过程：

1.观察，至少跑1天，看看生产的慢SQL情况。
2.开启慢查询日志，设置阙值，比如超过5秒钟的就是慢sql，并将它抓取出来，
3.explain + 慢sql分析
4.show profile 查询sql在mysql服务器里面的执行细节和生命周期情况
5.运维经理 or DBA ，进行sql数据库服务器的参数调优。

MySql 锁机制

从对数据操作的类型（读\写）分：
读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

从对数据操作的粒度分：

• 表锁：
  偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
  【手动增加表锁】
  lock table 表名字1 read，表名字2 write;
  【查看表上加过的锁】
  show open tables;
  
  【释放表锁】
  unlock tables;

我们为mylock表加read锁(读阻塞写例子)

session_1	session_2
获得表mylock的READ锁定	连接终端
当前session可以查询该表记录	其他session也可以查询该表的记录
当前session不能查询其它没有锁定的表	其他session可以查询或者更新未锁定的表
当前session中插入或者更新锁定的表都会提示错误：	其他session插入或者更新锁定表会一直等待获得锁：
释放锁	Session2获得锁，插入操作完成：

MyISAM 读锁，只能读当前表，不能修改当前表，别人可以读被锁的表，如果别人对锁定的表进行修改，则会进入阻塞，直到释放锁

注意： 一次只能锁一张表，锁了别的表会自动解锁前一张表

写锁案例：

MyISAM 写锁，可以读写当前表，别人不可以读写被锁的表，如果别人对锁定的表进行读写，则会进入阻塞，直到释放锁

如何分析表锁定

可以通过检查table_locks_waited 和 table_locks_immediate状态变量来分析系统上的表锁定：

mysql> show status like 'table%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Table_locks_immediate | 10500337 |
| Table_locks_waited    | 1        |
+-----------------------+----------+
2 rows in set (0.00 sec)

Table_locks_immediate ： 产生表级锁定的次数
Table_locks_waited ：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值就加１），此值越高　则说明存在着较严重的表级锁争用情况；

myisam不适合做写为主的表引擎，因为它的读写机制是写优先，加了写锁后，其他线程只能进入阻塞状态。

• 行锁
  偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
  Innodb 与Myisam的最大不同有两点：一是支持事务（Transaction） ： 二是采用了行级锁

Session_1	Session_2
更新但是不提交，没有手写commit;	Session_2被阻塞，只能等待
提交更新	解除阻塞，更新正常进行
	commit

在没有索引，没有正确使用索引，行锁就会升级为表锁

在查询时加行锁：

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

begin;
SELECT ... LOCK IN SHARE MODE;
commit;

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

begin;
select * from test_innodb_lock where a = 8 for update;
commit;

间隙锁危害:
【什么是间隙锁】
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）。

【危害】
因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

【如何分析行锁定】
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

mysql>show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits：当前正在等待锁定的数量；
Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg：每次等待所花平均时间；
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
Innodb_row_lock_waits：系统启动后到现在总共等待的次数；
对于这5个状态变量，比较重要的主要是
Innodb_row_lock_time_avg（等待平均时长），
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

最后可以通过
SELECT * FROM information_schema.INNODB_TRX\G;
来查询正在被锁阻塞的sql语句。

主从复制：

MySQL复制过程分成三步：
1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2 slave将master的binary log events拷贝到它的中继日志（relay log）；
3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

基本准则：
每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个salve

复制的最大问题：
延时