mysql高级学习笔记 mysql高级学习笔记

什么是索引？索引（index）

Mysql官方对索引的定义是：索引是帮助mysql高效获取数据的数据结构。所以，索引的本质是数据结构。
可以简单的理解为“排好序的快速查找数据结构”。
索引的目的在于提高查询效率，可以类比字典。
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
一般来说索引也很大，不可能全部存储在内存中，因此索引往往一索引文件的形式存在磁盘中。
Java中的默认索引都是B+树索引，除此之外，我们们常见的是hash索引。
索引的优势：
类似大学图书馆见书目索引，提高数据库检索效率，降低数据库的IO成本。
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
索引的劣势：
实际上索引也是一张表，该表保存了主键与索引字段，并且指向实体表的记录，所以索引也是要占用空间的。
索引虽然会提高检索效率，但是会减低更新标的速度，因为insert update、delete也会操作索引文件，会调整因为更新等操作带来的键值变化后的索引信息。
建议：一张表建立的索引最多不超过5个。（仅仅是建议）
Sql索引分类：
单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：一个索引包含多个列
、
基本语法：
中括号代表可以省略，，唯一索引就加unique这个关键字，不是唯一索引就不加, columnname多个就是复合索引。
创建索引：
① Create [unique] index indexName on mytable(columnname(length));
② Alter mytable add [unique] index [indexName] on (columnName(length));
删除：drop index [indexName] on mytable;
查看： show index from table_name
四种方法添加数据库表的索引：ALTER的使用
ALTER TABLE tb_name ADD PRIMARY KEY (column_list); 该语句添加一个主键，这意味着索引值必须是唯一的，且不能是空。
ALTER TABLE tb_name ADD UNIQUE index_name (column_list); 这条语句创建的索引的值必须是惟一的（除了null外，null可能会出现多次）
ALTER TABLE tb_nameADD INDEX index_name(column_list); 添加普通索引。索引值可出现多次
ALTER TABLE tb_nameADD FULLTEXT index_name (column_list); 该语句指定了索引为FULLTEXT,用于全文索引。
Mysql的索引结构：（后三个了解即可）
1.BTree索引 java中mysql一般来说默认是BTree索引

2. Hash索引3.full-text全文索引4.R-Tree索引

一个bTree树的检索原理如下图：

文章图片
image.png 真实的数据存在于叶子结点
非叶子节点不存储真实数据，只存储指引搜索方向的数据项，例如17,35并不真实存在于数据表中。
哪些情况需要创建索引？

主键自动创建唯一索引
频繁作为查询条件的字段适合创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为在更新记录的同时还回去更新索引
Where条件里用不到的字段不需要索引
单值索引和复合索引选择。。。。在高并发下倾向创建复合索引
查询中排序order by的字段，排序字段若通过索引去访问将大大提高排序速度。
查询中统计或者分组的字段groupby 分组必排序，所以跟order by相关

哪些情况不要建立索引：

表记录太少，没必要
经常增删改的表，因为索引也会随之变动
数据重复且分布平均的表字段，如果某个数据列有很多重复的内容，那没什么必要建立索引。例如性别、国籍等

SIMPLE：简单的select查询，查询中不包括子查询或者union
PRIMARY:查询中若包含任何复杂的子查询，最外层的最后加载的标记为此
SUBQUERY：在select或者where列表中包含了子查询
DERIVED：在from列表中包含的子查询被标记为Derived(衍生)，mysql会递归执行这些子查询，把结果放到临时表里。
UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
UNION RESULT:从union表获取结果的select

Table:显示这行数据是关于那张表的。
***Type:访问类型排序
type可以有8种值，从最好到最坏的排序是
System>const>eq_ref>ref>range>index>all
All是全表扫描，百万级别以上必须优化，建立索引等等。
一般来说，得保证到达range和ref级别就可以了，至少range最好ref
System:表只有一行记录（等于系统表，基本不会出现）
Const:表示通过索引一次就找到了，用于比较主键索引或者唯一索引。因为只匹配一行数据，所以很快，mysql就能将该查询转换成一个常量
Eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
Ref:非唯一性索引扫描。返回匹配某个单独值得所有行，本质也是一种索引访问，他返回所有匹配某个单独值得行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
Range：只检索给定范围的行，使用一个索引来选择行。Key列显示使用了那个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引比全表扫描要好，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
Index:index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小，也就是说all和index虽然都是读全表，但是index是从索引中读取的，而all是从硬盘中读取的。
Possible_keys和key
Possible_keys：显示可能应用到这张表中的索引，查询涉及到的字段上若存在索引，则列出，但不一定会实际使用。
***Key:实际运用的索引，如果是null，则是没有使用索引，如果查询中使用了覆盖索引，则该索引仅会出现在key列表中。
Key_len:表示索引中使用的字节数，计算查询中索引的长度，在不损失精度的情况下，越小越好，此值现实的是索引字段的最大可能长度，不是实际使用长度，。
【mysql高级学习笔记】Ref:显示缩印的哪一列被使用了，如果可能的话，是一个常数，那些列或常量被用于查找索引列上得值。
***Rows:根据表统计信息以及索引选用情况，大致估算出找到所需记录所需要读取的行数
***Extra:包含不适合在其他列中显示，但是又十分重要的额外信息。
Extra中可能有的值为：

Using filesort:说明mysql会对数据使用一个外部索引排序，而不是按照表内的索引进行读取，mysql中无法利用索引完成的排序操作叫做“文件排序”。

**一般来说是因为建立一个col1_col2_col3的索引，查询时候没按照索引的顺序order by .mysql自己在内部重新生成一个排序，这样效率会慢，出现filesort尽快优化。

Using temporary:使用了临时表保存中间结果，mysql在对查询结果排序是使用了临，时表。

一般来说，原因是order by排序或者分组查询group by时候没有按照索引走。
使用了临时表，更加严重影响效率，出现必优化。

Using index : 表示相应的select操作中使用了覆盖索引，避免访问表的数据行，效率不错！如果同时出现using where,表示索引被用来执行索引键值的查找，如果没有同事出现usingwhere 表示索引用来读取数据而非执行查找动作。

覆盖索引：select的数据列只从索引中就能获得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说就是查询列要被所建立的索引覆盖。
Join语句的优化：
1.尽可能减少join语句中的NestedLoop的循环次数；永远用小的结果集驱动打的结果集。
2.优先优化NestedLoop的内层循环
3.保证join语句中被驱动表上的join条件字段已经被索引
4.当无法保证被驱动表的join条件字段被所以你且内存资源充足的前提下，不要太吝啬joinbuffer的设置。
**最佳左前缀法则：如果索引有多列，查询要从弄缩印的最左前列开始并且不能跳过索引中间的列。
**不要在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描。
** 存储引擎不能使用索引中范围条件右边的列。（范围之后索引全失效）
**尽量使用覆盖索引，脂肪纹索引的查询，索引列和查询列一致，减少select *
**mysql在使用！=或者<>不等于的时候无法使用索引导致全表扫描。
**is null和is not null也无法使用索引。
Like以通配符开头（‘%abc...’）mysql索引会失效全表扫描。百分号like加在右边不会全表扫描，或者加左边加两边使用覆盖索引。
**字符串不加单引号索引失效
**少用or用它连接的时候也会导致索引失效
分析sql慢：

观察，至少跑一天，看看生产的慢sql的情况
开启慢sql查询日志，设置阈值，超过5秒就是慢sql，抓取出来
Explain+慢sql分析
Show profile
运维经理或者DBA进行sql数据库服务器的参数调优。

总结；
我们应该做的：

慢查询日志的开启并捕获慢sql
Explain分析
Show profile查询sql在mysql服务器里面的执行细节和生命周期情况
Sql数据库服务器的参数调优

永远小表驱动大表
For(int i=1; i<5; i++){
For(int j=1; j<1000; i++){
//这种就是小表驱动大表，连接5次数据库，每次查询1000次，反之则连接1000次，然后每次查询5次。
}
}
当B表的数据集必须小于A表的数据集时候用in优于exists
Select * from A where id in (select id from B)
当A表的数据集小于B表的数据集时候用Exists优于in
Select * from A where exists （select 1 from B where B.id = A.id）****注意语法。
Order by满足两种情况会使用覆盖索引。Orderby 使用最佳佐前列，或者where+order by 满足最佳佐前列原则。否则fileSort文件排序效率低。
提高order by速度

禁止使用select *
增高大sort_buffer_size
增大max_length_for_sort_data
Group by和orderby 一样，但是能在where限定的不要使用having

慢查询日志，mysql数据库默认不开启慢查询日志，需要调优才开启。
查询是否开启？show variables like ‘%slow_query_log%’;
如何开启？set global slow_query_log = 1;
日志分析工具mysqldumpslow的使用。
存储过程：
Delimiter
Declare 声明变量
创建函数 create function
Repeat 循环
Create Procedure 创建存储过程
Call 存储过程名，---调用存储过程
Show profile：
是什么？是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql调优的测量。

查看当前mysql版本是否支持show variables like ‘profiling’;
设置开启 set profiling = ‘no’;
运行sql
查看结果show profiles;
诊断sql, show profile cpu,block io for query +上一步前面的问题sql数字号码
日常开发需要注意的结论：

① Converting Heap to myisam 查询结果太大，内存都不够了，往磁盘上搬了
② Creatingtmp table 创建临时表
③ Copingto tmp table on disk 把内存中临时表复制到磁盘！！！危险
④ Locked 锁了
表锁：偏向MYISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
加锁语句：lock table table_name read, table2_name write;
给tablename加读锁，给table2_name加写锁。
解锁：unlock tables;
读锁：是共享锁，当session_1加读锁当前表之后，session_1只能读当前表，不能改当前表，也不能读其他表，session_2能读当前表和其他表，但是写当前表会进入阻塞状态，等待session_1对当前表进行解锁，然后完成修改。
总而言之：读锁会阻塞写操作，不会阻塞读操作，而写锁会阻塞读写操作。
Show open tables；查看那些表被锁了。
如何分析表锁：
Show status like ‘table%’;
Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每次获取锁值加1；
Table_locks_waited:出现表级锁定争用而发生等待的次数，此值高则说明存在较严重的表级锁争用的情况
此外，myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
行锁：偏向innoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生所冲突的概率最低，并发度也最高。
InnoDB与myisam最大的不同，一是支持事务；二是采用了行级锁。
并发事务带来的问题：

更新丢失：两个或多个事务选择同一行，基于原始值更新该行，因为不知道其他事务的存在，会导致最后的更新覆盖其他事物的更新。
脏读：事务A读到了事务B已修改但是还未提交的数据，还在这个基础上进行了操作，这时如果B回滚，A读取的数据无效，不符合一致性要求。
不可重复读：事务A读取到了事务B已经提交的修改数据，不符合隔离性。
幻读：事务A读取到了事务B提交的新增数据，不符合隔离性。

索引失效会导致行锁变表锁，例如varchar类型sql不带引号会导致索引失效，进而导致行锁变表锁。
间隙锁：当用一个范围条件检索数据的时候，innodb会给所有符合条件的索引项加锁，对于键值存在条件范围内但是并不存在的记录，也会锁定。
怎样给某条记录加锁，行锁！！！
Begin;
Select * from table where a = 8 for update;
Update table set b=1 where a=8;
Commit;
如何分析行锁show status like ‘innodb_row_lock%’;

文章图片
image.png 尽可能让所有的数据检索都通过索引完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离。
Mysql主从复制：

文章图片
image.png 主从复制的规则：

每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个slave

一主一从常见配置：

mysql版本一致并且后台以服务运行，双方能ping通。
假如win为主机，linux为从机，则win修改my.ini文件，linux修改my.cnf文件
主从配置都在【mysqld】节点下，都是小写
主机修改my.ini文件

①．Server-id = 1 【必须】主服务器唯一ID
②．【必须】启用二进制日志文件，log-bin = 本地路径/data/mysqlbin
③．【可选】启用错误日志 log-err = 本地路径/data/mysqlerr
④．【可选】根目录；basedir= 本地路径
⑤．【可选】临时目录； tmpdir = 本地路径
⑥．【可选】数据目录；datadir = 本地路径/Data/
⑦．Read-only = 0 代表主机读、写都可以
⑧．【可选】设置不要复制的数据库 binlog-ignore-db = mysql
⑨．【可选】设置需要复制的数据库 binlog-do-db = 需要复制的数据库名字

从机修改my.cnf文件 vim /etc/my.cnf

①．【必须】从服务器唯一ID server-id=1注释掉 server-id = 2 打开注释
②．【可选】启用二进制日志数据库服务重启

主从都关闭防火墙 service iptables stop
在win主机上建立账户并授权slave

执行Grant replication slave on . to ‘zhangsan’@’从机数据库ip’identified by ‘123456’;
授权从机ip以zhangsan用户名123456密码访问主机
之后刷新
Flush privileges;
查看主机状态：
Show master status;
记录下前两个数据参数filename和position

在linux从机上配置需要复制的主机

①．执行CHANGE MASTER TO MASTER_HOST=’主机ip’,
MASTER_USER=’zhangsan’,MASTER_PASSWORD=’123456’,
MASTER_LOG_FILE=’FILE名字’,
MASTER_LOG_POS=Position的数字；
②．启动主从复制功能 start slave;
③．Show slave status\G； \G是竖向显示的意思下面两个参数都是yes说明主从复制配置成功
Slave_io_running:yes
Slave_sql_running:yes
如何停止从机复制功能：stop slave;