3-2 mysql 索引优化

索引优化思路：

索引的创建 如何查 如何分析 保证索引不失效

（一）、SQL性能下降的原因：

1、查询语句写的不好：

2、索引失效：（单值索引，符合索引）

3、关联查询太多join(设计缺陷或不得已的需求)

4、服务器调优及各个参数设置(缓冲，线程数等)

总结：对web开发而言：搞好1和2和3就好：

（二）、人写SQL顺序：

select from join on where group by having order by limit

（二）、sql执行加载顺序：

（三）、join连接有七种方式：

1、内连接（inner join） ：只有两者的共有部分

select * from table_a as A inner join table_b  as B on A.key = B.key;

2、左连接 （left join）：左表全数据

select * from table_a as A left join table_b  as B on A.key = B.key;

3、右连接（right join）：右表全数据

select * from table_a as A right join table_b  as B on A.key = B.key;

4、左外连接 （left join）：左表去除右边

select * from table_a as A left join table_b  as B on A.key = B.key where B.key is null;

5、右外连接 （right join）：右表去除左表

select * from table_a as A right join table_b  as B on A.key = B.key where A.key is null;

6、全连接（… left join … union … right join …）：左外连接 union 右外连接

select * from table_a as A left join table_b as B on A.key = B.key 
union 	------ 去重
select * from table_a as A right join table_b as B on A.key = B.key;

7、全外连接（）：左连接 union 右连接

select * from table_a as A left join table_b as B on A.key = B.key where B.key is null 
union  ------ 去重
select * from table_a as A right join table_b as B on A.key = B.key where A.key is null

（四）、索引：是帮助MySOL高效获取数据的数据结构

1、目的：提高查询效率，可以类比字典。

2、索引的两大功能：查找（where 条件）+排序（order by 条件）

3、我们平常所说的索引：如果没有特别指明，都是指B树结构组织的索引。

4、索引往往以索引文件的形式存储在磁盘上。

5、劣势：索引也需要占用空间，insert、update、delete时降低更新表的速度，需要维护索引字段。

（五）、索引分类：一张表最多建5个索引

1、单值索引：一个索引只包含单个列，一个表可有多个单列索引。

2、唯一索引(unique)：索引列的值必须唯一，但允许空值。

3、复合索引：一个索引包含多个列

4、基本语法：

# 创建：
create [unique] index indexName on table_name (columnname(length));
alter table_name add [unique] index indexName on (columnname(length))
# 删除：
drop index indexName on table;
# 查看：
show index from table \G;

（六）、索引结构：

1、BTree索引：

2、Hash索引：

3、full-text全文索引：(FULLTEXT)

4、R-Tree索引

（七）、哪些情况需要创建索引：

1、频繁查找的

2、主键自动创建

3、外键关系建立索引

4、高并发倾向创建组合索引

5、查询中排序的字段

6、查询中统计或分组的字段

（八）、哪些情况不要创建索引：

1、表记录太少

2、经常增删改的表

3、数据重复且分布平均的表字段

（九）、索引的选择性：

索引列中不同值的数目与表中记录数的比，值接近1，选择性就越高。

（十）、性能分析：

由于底层的优化器，所以你认为的优化策略不一定是对的，需要对底层优化器具体是怎么优化的分析。

1、explain 方法优化：模拟优化器执行SQL查询语句,从而得知mysql是如何处理你的SQL语句的。

2、语法：explain + SQL语句

分析ID：	反应表的加载顺序
id 相同：执行顺序由上到下。	执行顺序由上到下。
id不同：	如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id相同又不同：	id值越大，优先级越高，越先执行，id相同可以认为是一组，从上往下执行。

分析select_type	显示查询类型
simple	简单的select查询，不包括子查询或union
primary	查询中包含任何复杂的子查询，最外层查询被标记为primary
subquery	在select或where列表中包含了子查询
deriued	在from列表中包含的子查询，临时表
union	第二个select出现在union之后
union result	从union表获取结果的select

分析type
性能

分析possible_keys和key	是否使用到了索引，多个索引竞争下正真使用到的索引
possible_keys	显示肯能用在这张表中的索引
key	实际用到的索引

分析ref	显示索引的那一列被使用了

分析rows	根据表统计信息及索引选用情况，大致估计找到所需的记录所需要读取的行数
rows	值越小越好

分析extra	包含不适合在其他列中显示但十分重要的额外信息
Using filesort	对数据使用一个外部的索引排序
Using temporary	使用了临时表保存中间结果，mySQL在对查询结果排序时使用临时表，常见于order by 和 group by
Using index	select操作使用了覆盖索引
Using where	使用了where过滤
Using join buffer	使用了连接缓存

（十一）、表优化案例：

单表优化方案：

两表优化方案：

左连接，索引加右表
有连接，索引加左表

三表优化方案：

join 语句的优化：
尽可能减少join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集。”
尽可能保证join语句中被驱动表上join条件字段已经被索引

（十二）、索引失效：

1、全值匹配，索引都用到。

2、最佳左前缀法则：索引多列，要遵守最左前缀法则。从索引的最左前列开始并且不跳过索引中的列。带头大哥不能死，中间兄弟不能断。

3、不在索引上做任何操作，会索引失效，变为全表扫描。

4、存储引擎不能使用索引中范围条件右边的列。

5、尽量使用覆盖索引，减少select *

6、使用不等于的时候无法使用索引会导致全表扫描

7、is null,is not null 也无法使用索引。

8、like以通配符开头，mysql索引会失效变成全表扫描操作。通配符要加到右边。如果非要用到双%号的话，需要用到覆盖索引哦！

9、字符串不加单引号索引失效，int 和 varchar类型底层自动转换。

10、少用or，用它来连接时会索引失效

（十三）、查询截取分析：

<一>、查询优化的三种方法：

1、explain + 慢 SQL分析：开启慢查询日志，设置阈值，超过x秒钟的就是慢SQL，抓取。

# 查看慢日志是否开启
 show variables like '%slow_query_log%';
# 开启慢日志
 set global slow_query_log = 1;
# 查看阈值时间，默认10秒
 show variables like 'long_query_time%';
# 设置阈值时间
 set global long_query_time = 3;
# 查看设置时间
 show global variables like 'long_query_time';
 # mysqldumpslow的帮助信息
 mysqldumpslow --help
 mysqldumpslow -s r -t 2 /xx/xxx/xxxx/xxxx.log

2、show profile 分析：查询SQL在mysql服务器里面的执行细节和生命周期情况。

# 查看 版本是否支持，默认关闭
 show variables like 'profiling';
 # 开启profile
 set profiling = on;
 # 运行各种SQL语句，会默认保存最近15条SQL的执行情况。
 select * from student where age > 18 order by desc group by name;
 # 显示上面这条SQL的运行情况
 show profiles;
 # 显示样式：
 Query_ID     运行时间      执行的SQL语句

# 显示第三条数据更详细的情况：
show profile cpu,block io for query 3;

3、运维经理 或 DBA，进行SQL数据库服务器的参数调优。

全局查询日志：不要在生成环境开启这个功能。

1、配置启用：在mysql的my.cnf中，设置如下：

# 开启
general_log = 1
# 记录日志文件的路径
general_log_file = /path/logfile
# 输出格式
log_output = FILE

2、编码启用：

# 命令
set global general_log = 1;
set global log_output = 'TABLE';
# 此后，你所编写的SQL语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from msyql.general_log;

<二>、排序优化：

1、永远小表驱动大表：

2、in 和 exists的不同。

<三>、OrderBy 关键字的排序优化：满足最左前缀原则。

尽量使用Index方式排序，避免使用FileSort方式排序

1、index效率高（最左前列原则），FileSort效率低。

<四>、group By 关键字优化：

1、实质：先排序后进行分组，遵照索引的最佳左前缀。其它和order by 一致。

（十三）、乐观锁悲观锁：

两锁解决库存问题

1、乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

在select时，加入一个版本字段，每次更新，同时查询和更新版本字段，如果版本字段发生变化，则，SQL语句不会执行成功。

2、悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。

在select，将select语句加入一个行锁，和更新库存的语句互斥，可以保证在查询库存时库存不被修改。

（十四）、行锁解决库存超卖问题：

1、高并发情况下，多个人同时抢购一个库存时，数据库的读写操作可以并行执行的原因，会导致修改库存时，库存不足，出现超卖。用锁将查询库存的操作和写入库存的操作互斥。

2、如何锁定一行：在SQL语句后面加上 for update

3、无索引行为 行锁升级为表锁：经常用查的字段如果没有索引，而你在上面加了行锁，这时会导致行锁升级为表锁。但是如果在这个字段上加入了索引，在加行锁，是不会出现前面这种状态的。

4、间隙锁危害：通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值不存在，也会锁定，锁定的时候无法插入锁定键值范围内的任何数据。

5、死锁：双方都等待对方先解锁。

6、行锁特点：读读互斥。

7、update：在InnoDB模式下，update默认加行锁。