Mysql 索引

Mysql 总结

1.索引的定义及 作用

索引（INDEX）：是帮助MySQL高效获取数据的数据结果。从而可以得到索引的本质：索引是排好序的快速查找数据结构 （底层结构B+树和B树）

优点

（1）减少查询IO; （2）优化等值查询或者范围查询；也即是范围查询和等值查询都是会走索引的； （3）利用有序特性（例如：order by/group by/distinct/max/min等函数）；这些操作都是利用排序技术来实现的，而索引天然就是有序的，因此使用到order by等有序操作时，对相关字段建立索引会提高效率。

缺点：

1.索引自身会占用空间，所以不宜过多建立

2.每次的插入和更新都会引起索引的变动，降低表的更新速度。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

2.索引类型：

单值索引，唯一索引，复合索引，聚集索引，前缀索引

3.Explain （Sql执行计划）

Mysql 总结

1.索引的定义及 作用

索引（INDEX）：是帮助MySQL高效获取数据的数据结果。从而可以得到索引的本质：索引是排好序的快速查找数据结构 （底层结构B+树和B树）

优点

（1）减少查询IO; （2）优化等值查询或者范围查询；也即是范围查询和等值查询都是会走索引的； （3）利用有序特性（例如：order by/group by/distinct/max/min等函数）；这些操作都是利用排序技术来实现的，而索引天然就是有序的，因此使用到order by等有序操作时，对相关字段建立索引会提高效率。

缺点：

1.索引自身会占用空间，所以不宜过多建立

2.每次的插入和更新都会引起索引的变动，降低表的更新速度。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

2.索引类型：

单值索引，唯一索引，复合索引，聚集索引，前缀索引

3.Explain （Sql执行计划）

EXPLAIN能干嘛？

可以查看以下信息：

• id：表的读取顺序。

• select_type：数据读取操作的操作类型。

• possible_keys：哪些索引可以使用。

• key：哪些索引被实际使用。

• ref：表之间的引用。

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

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

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。
​
Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
​
Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
​
Using where：表明使用了WHERE过滤。
​
Using join buffer：使用了连接缓存。
​
impossible where：WHERE子句的值总是false，不能用来获取任何元组。
​

索引失效分析依据：

全值匹配我最爱。
​
最佳左前缀法则。
​
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
​
索引中范围条件右边的字段会全部失效。
​
尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。
​
MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。====sometimes
​
is null、is not null也无法使用索引。
​
like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）。
​
字符串不加单引号索引失效。
​
少用or，用它来连接时会索引失效。

3.小表驱动大表：

原理：类似于双重循环，小表在外层，大表在里层

for(int i = 1; i <= 5; i ++){
    for(int j = 1; j <= 1000; j++){
         ......
    }
}

原理：在检索大表的时候可以使用索引，B+树查找时间复杂度是logn，所以小驱大大概的时间200log2000000，当然比2000000log200快很多,究其原因要在 被驱动表中建立与筛选条件一致或顺序一致的的索引

join：

b小表 a大表

SELECT b.boy,t.toy
FROM boys b
LEFT JOIN toys t
ON b.toy_id=t.toy_id;

t是大表，且on条件中的toy_id是索引，这样在扫描完小表再去匹配大表时，按索引去匹配会快很多。

exist与in问题

/* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */
 
/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)
 
/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);

EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。可以理解为：将主查询的数据放入子查询中做条件验证，根据 检验结果决定主查询数据是否保留。

EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。

EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

4.MyISAM与InnoDB 的引擎区别

1.InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3.InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。

4.InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；=======事务原因

5.InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁。InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。即无索引操作导致行锁升级表锁

5.锁

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）。

表独占写锁（Table Write Lock）。

対MyISAM表进行操作，会有以下情况：

対MyISAM表的读操作（加读锁），不会阻塞其他线程对同一表的读操作，但是会阻塞其他线程和自己对同一表的写操作。同时也会阻塞持有锁的线程对其他表的操作。只有当读锁释放之后，才会执行其他线程的写操作。

対MyISAM表的写操作（加写锁），会阻塞其他线程对同一表的读和写操作，同时也会阻塞持有锁的线程对其他表的操作。只有当写锁释放之后，才会执行其他线程的读写操作。

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

5.行锁

特点： 偏向Innodb存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。

事务知识 回顾：A C I D 原子性 atomicity 一致性consistent 隔离性 isolation 持久型 duration

并发事务带来的问题：

更新丢失：同时更改，会有一方的更改丢失

脏读：读未提交

不可重复读 ：由于后者插足修改，导致两次读到的结果不一致

幻读：由于后者插足添加，导致两次读到的记录条数不一致

默认隔离级别：可重复读 Repeatable read

如何锁定一行：

begin：
​
select * from 表 where 某一行的条件 for update;

优化建议：

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

• 合理设计索引，尽量缩小锁的范围。

• 尽可能减少索引条件，避免间隙锁。

• 尽量控制事务大小，减少锁定资源量和时间长度。

• 尽可能低级别事务隔离。

6.索引优化 总结：

1.依据where查询条件建立索引，且过滤顺序尽量和索引建立顺序一致，可使用部分，单要遵循最左前缀原，而且则中间索引字段不要中断

例如有idx_smp(a,b,c)这样的索引，where a=?或者where a=? and b=?都可以使用该索引，但是where a=? and c=?就无法使用该c索引，只能使用到a索引

2.使用联合索引 而不是单列索引，精确度高，查询速度快

3.联合索引中索引的顺序根据区分度排，区分度大的放在前面。区分度是指字段值的种类，字段值种类越多的字段要放在前面，例如：idx_smp(name,gender)的效率要比idx_smp(gender,name)的效率高

4.避免建立冗余索引，例如建立了idx_smp（a,b,c）就不需要建立idx_smp(a)、idx_smp(a,b)索引了。

5.order by group by distinct等需要排序的操作，在没有索引的大数据量情况下需要排序，对IO和CPU性能消耗很大。如果有类似排序需求，则需要对相关字段建立索引，这样利用索引的有序特性不需要排序，直接按着索引顺序扫描即可。尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则

​
/* 创建表 */
CREATE TABLE `test03`(
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`c1` CHAR(10),
`c2` CHAR(10),
`c3` CHAR(10),
`c4` CHAR(10),
`c5` CHAR(10)
);

例如某表按顺序 建立 c1，c2，c3，c4 索引

​
/* 用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
​
/* 用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;
​
/*用到c1 c2 两个字段，c1,c2用于查找，c3因c2是范围查找而失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` > '66' GROUP BY `c3`;
​
/* 
 用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
 因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;
​
​

/* 创建a b c三个字段的索引 */
idx_table_a_b_c(a, b, c)
 
/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;
 
/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;
 
/* 3.不能使用索引进行排序 */
ORDER BY a ASC, b DESC, c DESC;  /* 排序不一致 */
WHERE g = const ORDER BY b, c;   /* 丢失a字段索引 */
WHERE a = const ORDER BY c;      /* 丢失b字段索引 */
WHERE a = const ORDER BY a, d;   /* d字段不是索引的一部分 */
WHERE a IN (...) ORDER BY b, c;  /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 */

1. select ＊ 不建议使用，因为会读取大量数据，也不利于使用索引覆盖技术。索引字段能够完全在索引中获取， 就不要使用select ＊（因为会导致回表），无法完整在索引中获取，也是建议select具体字段。

7.查看是否使用了索引：explain命令查看

explain的一些使用建议：

1.对不确定执行计划的关键语句上线前务必explain； 
2.type为all的要格外注意，避免全表扫描； 
k3.ey_len只能用很少一部分前缀的，要注意索引字段顺序等； 
4.extra里看到using filesort 和using tmporary都要尽量优化，这两种fetch方式不应该出现在任何执行频繁的关键语句中。

8.尽量避免上文提到的索引失效情况

9.永远用小表 驱动大表

10.针对较长的字要设置前缀索引。有时候，可能要创建的索引是很长的字符串，如果我们使用完整的列，可能会造成索引变大且变慢。此时使用前缀索引，只索引字段的部分值，可以帮助我们大大节省索引空间，提高索引效率。