了解mysql_索引的原理及优化技巧
理解mysql_索引的原理及优化技巧
innodb的索引：
● innodb采用索引组织数据的形式，它是在存储引擎层面实现的，与后端的服务器层没有关联。不同的存储引擎有不同的实现算法。
● 索引的原理
索引采用b tree（平衡树）树的形式保存，所以是有顺序的。
索引的数据存储在表空间(tablespace)里面，
聚集索引（主键索引）上面存储了整个行的数据，而辅助索引上面仅存了指向主键的指针，因此辅助索引查询效率要低于主键索引1倍。
在插入和删除的时候性能比较低，需要维护平衡树。
● 索引的分类：
1. 聚集索引（主键索引）， 一个表只允许一个主键索引，以b+树结构存储， 索引上面已经存储了数据。查询的时间复杂度为树的高度
2. 辅助索引， 存储了主键的指向，查询时需要先找到辅助索引，然后根据辅助索引的值找到主键索引，然后再获取数据
3. 联合索引， 最左原则
因为右边的索引在建索引的时候没有顺序，所以会全表扫描。
mysql目前还做不到松散索引扫描
4. 倒排索引， 将text分词得出很多小片段的词组，对这些词组进行索引
5. 自适应hash索引。
6. 覆盖索引
select 中的字段在索引里面，不需要到再一次到聚集索引中去获取值
用explain查询，可以看到extra标注：using index
● 优化点
1. 索引是字符串，但是查询时没有带单引号 ' '
2. 一条sql只能走一个索引, 如果走多个索引建议用联合索引
3. 优化过程中优先考虑使用到聚集索引和覆盖索引，这能很大程度上提高效率
4. 建议少用in，尤其是嵌套子查询，因为那样的话外部表会做全表扫描，可以用表关联
exists 和 in 能够很好的去除，不需要distinct、group，避免了临时表的创建，效率有时会比较高
是不是一定要禁止使用子查询（如exists、in)， 这要看场景，因为用inner join 容易 出现重复记录，剔重需要用distcint，会影响到效率。
什么情况下会产生临时表？
5. min\max巧用主键索引
6. 巧用子查询更新表记录
update t inner join (select id, count(1) count from t2 group by id) t3 set t.max_value = t3.count
索引类型越小越好，对于大数据处理来说，这个可不是小事，从字符串替换为数字类型，可以极大地节省内存、磁盘存储以及网络带宽，减少IO的代价，而且很多数据结构和算法使用数字类型比字符串要更快
innodb的索引：
● innodb采用索引组织数据的形式，它是在存储引擎层面实现的，与后端的服务器层没有关联。不同的存储引擎有不同的实现算法。
● 索引的原理
索引采用b tree（平衡树）树的形式保存，所以是有顺序的。
索引的数据存储在表空间(tablespace)里面，
聚集索引（主键索引）上面存储了整个行的数据，而辅助索引上面仅存了指向主键的指针，因此辅助索引查询效率要低于主键索引1倍。
在插入和删除的时候性能比较低，需要维护平衡树。
● 索引的分类：
1. 聚集索引（主键索引）， 一个表只允许一个主键索引，以b+树结构存储， 索引上面已经存储了数据。查询的时间复杂度为树的高度
2. 辅助索引， 存储了主键的指向，查询时需要先找到辅助索引，然后根据辅助索引的值找到主键索引，然后再获取数据
3. 联合索引， 最左原则
因为右边的索引在建索引的时候没有顺序，所以会全表扫描。
mysql目前还做不到松散索引扫描
4. 倒排索引， 将text分词得出很多小片段的词组，对这些词组进行索引
5. 自适应hash索引。
6. 覆盖索引
select 中的字段在索引里面，不需要到再一次到聚集索引中去获取值
用explain查询，可以看到extra标注：using index
● 优化点
1. 索引是字符串，但是查询时没有带单引号 ' '
2. 一条sql只能走一个索引, 如果走多个索引建议用联合索引
3. 优化过程中优先考虑使用到聚集索引和覆盖索引，这能很大程度上提高效率
4. 建议少用in，尤其是嵌套子查询，因为那样的话外部表会做全表扫描，可以用表关联
exists 和 in 能够很好的去除，不需要distinct、group，避免了临时表的创建，效率有时会比较高
是不是一定要禁止使用子查询（如exists、in)， 这要看场景，因为用inner join 容易 出现重复记录，剔重需要用distcint，会影响到效率。
什么情况下会产生临时表？
5. min\max巧用主键索引
6. 巧用子查询更新表记录
update t inner join (select id, count(1) count from t2 group by id) t3 set t.max_value = t3.count
索引类型越小越好，对于大数据处理来说，这个可不是小事，从字符串替换为数字类型，可以极大地节省内存、磁盘存储以及网络带宽，减少IO的代价，而且很多数据结构和算法使用数字类型比字符串要更快