欢迎访问我的个人博客：www.cilecile.xyz
学习更多知识一起成长
前言
本文并不能直接学会索引，而是类似学习笔记，让人看到就能想起知识的样貌
索引介绍
简介：简单说，索引是一种有序的数据结构，用于高效地获取数据
优点：提高检索效率，降低IO成本；提高排序效率，降低CPU成本
缺点：索引也需要占据空间，增删改表的同时也需要维护索引，效率会降低
mysql常见索引介绍
B+Tree索引：最常见的索引，大部分引擎都支持
Hash索引：底层是hash表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
全文索引：是一种通过建立倒排索引，快速匹配文档的方式
B+Tree：在BTree的基础上，将所有的数据放在了叶子节点，非叶子节点不存放数据，而是做索引的作用，叶子节点中有多个数据，叶子节点之间是链表链接
mysql中的B+tree，在经典的B+tree的基础上进行优化，把单向链表改为了双向循环链表，以便于范围查找等
索引分类
主键索引
针对表中主键创建的索引
自动创建，只能有一个
PRIMARY
唯一索引
避免同一个表中某数据列中的值重复
可以有多个
UNIQUE
常规索引
快速定位特定数据
可以有多个
全文索引
全文索引查找的是文本中的关键字，而不是比较索引中的值
可以有多个
FULLTEXT
InnoDB中，根据索引与数据存放的位置分为
聚集索引
将数据与索引存放到一块，索引结构的叶子节点保存了行数据
有且只有一个
二级索引
将数据与索引分开存储，索引结构的叶子节点关联的是主键
可以存放多个
聚集索引选取规则
有主键，主键索引就是聚集索引
没有主键，第一个唯一索引就是聚集索引
没有主键，没有合适的唯一索引，则InnoDB自动生成一个rowid作为隐藏的聚集索引
回表查询
指二级索引找到对应值，再从聚集索引找到对应值的行数据。
原因：二级索引中没有所有我们想要的数据，只能去聚集索引中根据id查询对应数据行，从而得到所有数据
SQL性能分析工具
1. 使用 SHOW GLOBAL STATUS LIKE 'com_______' 查看数据库主要语句
2. 慢查询日志
使用 SHOW VARIABLES LIKE 'slow_query_log' 查看是否开启慢查询日志
通过设置开启慢查询日志和慢查询时间，可以在慢查询日志文件中查看有哪些语句耗时较长
3. profile
在一些简单业务中，时长没到慢查询时间，但同样也可以认为是性能较差，这时我们可以使用profile，帮助我们了解时间耗费都去哪里了
show profiles; --查看每一条SQL的基本耗时情况
show profiles for query query_id； --查看指定query_id的SQL语句各个阶段耗时情况
show profiles cpu for query query_id; --查看指定query_id的cpu使用情况
4. 重中之重——explain执行计划
语法：explain/desc + SQL语句
字段含义：
id：select查询的序列号，查询中执行的select子句或者是操作表的顺序(id值相同，从上往下；id值不同，从大到小)
select_type:查询类型
type：连接类型，性能由好到差连接类型为NULL（不查询任何表）、system（查询系统表）、const（查询主键索引或者唯一索引）、eq_ref、ref（查询非唯一性索引）、range、index(用了索引但也要遍历整个索引树)、all(全表扫描)
possible_key:显示可能应用在这张表上的索引，一个或者多个
key：实际使用的索引，如果为null，则没有使用索引
key_len：索引中使用的字节数，该值为索引最大的可能长度，并非使用长度，不损失精确性的情况下，越短越好
rows：mysql认为必须要执行查询的行数，innodb引擎的表中，是一个估计值，并不总是准确的
filtered：返回结果的行数占读取行数的百分比，值越大说明有效数据越多，越大越好
索引的使用原则
小tips:最左前缀、范围查询、函数运算、字符串引号、头部模糊、or两边、数据分布
1. 最左前缀法则：如果索引了多列（联合索引），查询从最左列开始，并且不跳过索引中的列，如果跳过某一列，后面的索引将会失效【必须包含最左边的索引，否则不走索引而是全表扫描，如果不包含中间的索引，则后面的索引都会失效】
2. 范围查询右侧索引失效：查询条件中出现(>,<)会导致之后的索引失效，尽量改为>=,<=
3. 不要在索引列上运算：在索引列上使用函数运算操作，索引将会失效
4. 字符串一定要加引号：字符串类型字段不加引号，索引将会失效
5. 不要头部模糊查询：仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效
6. 使用or时，or两边条件都要有索引：如or前的条件有索引，而后面没有索引，则所有索引都不会被用到。反之亦然。
7. 数据分布会影响索引使用：mysql评估使用索引更慢的话，则会不使用索引，改为全表扫描
SQL提示
简单来说就是，给SQL语句一些人为的提示，用于达到优化的目的。
语法：select id from tb_name xxxx index(index_name) where id = 10;
use index：建议mysql使用这个索引
ignore index：让mysql忽略某个索引
force index：强制mysql使用某个索引
索引设计原则
覆盖索引
尽量使用覆盖索引，减少 select * 。
查询索引时，如果需要输出的列不在索引中，则会进行回表查询，使用select * 很容易造成回表查询
如：有name索引，id主键索引
select id,name,info from student where name = '张三';
此时会走name索引，但name索引是二级索引，索引中包含了name和id值，并没有info，所以需要回表查询，通过聚集索引查询info的值。
前缀索引
当字段类型是字符串时，有时候需要索引很长的字符串，比如user表中用户简介，这会让索引变得很大，浪费磁盘IO，影响查询效率。
这时我们可以只取字符串的一部分前缀，建立索引，节约索引空间，提高索引效率
语法：create index idx_xxx_xxx on table_name(column(n));
如何选取：通过选取率进行挑选，
select count(distinct email,1,5)/count(*);
将字段去重并且截取前缀与字段总数做除法，得到不重复的字段数比率，称为选取率，选取率越接近1，说明越没有冲突，效率好。
我们只需要平衡前缀字符数与选取率到达较好的情况即可
单列索引&联合索引的选择
在业务场景中，存在多个查询条件，考虑对于查询字段建立索引时，建议建立联合索引。
1、针对数据量较大，查询较频繁的表建立索引
2、针对常作为查询条件、排序、分组操作的字段建立索引
3、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用的索引效率越高
4、如果是字符串类型的字段，字段长度较长，可以针对字段特点，建立前缀索引
5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省空间，避免回表，提高查询效率
6、要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价就越大，会影响增删改的效率
7、如果索引不能存储NULL值，在创建表时应该用NOT NULL约束它，当优化器知道是否包含NULL值时，会更好确定哪个索引最有效地用于查询
总结：
对于表：数据量大、经常需要查询的表添加索引
对于字段：经常作为查询条件、排序、分组操作的字段；区分度高的字段添加索引；
对于索引：大文本字符串型字段使用前缀索引；多用联合索引；索引不是越多越好；当索引不能存储NULL时，创建表时应该NOT NULL，帮助优化器选择索引
SQL语句性能优化
1、insert优化
（一）插入多条数据，应该整合成一条插入sql。
原因：每一条insert语句都要与mysql进行连接，多次插入需要多次连接、关闭连接，消耗较大。
并且一次整合的插入sql不应该数量太大，最好在500~1000条之间，多的sql应该切割开换到下一条insert语句中。
（二）此时有多条insert语句时，可以使用手动提交事务。
原因：默认自动提交事务，开启手动提交事务可以避免频繁开启事务和提交事务。
（三）插入时，应该遵从主键顺序插入
原因：根据mysql的数据组织结构，使用主键顺序插入比乱序插入速度要快(B+Tree页之间链表连接，页满了的时候，顺序插入只需要开辟新空间直接存放，乱序插入需要进行页分裂并且修改指针指向)
（四）一次性插入大批量数据，不要使用insert，而是使用mysql提供的load指令
原因：大批量数据下，insert性能不够
2、主键优化
在InnoDB中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）
----页分裂——乱序插入时，数据插入在已经满了的页之中，会将满了了的页进行页分裂，即开辟一块新空间，将一部分数据带到新页中，再重新调整指针指向
----页合并——删除一行记录时，并没有被物理删除，而是进行标记，并允许此空间能被声明
使用，而一旦删除的记录到达一定阈值（默认为百分之五十），就会寻找附近页是否有可以进行合并的数据，并将附近的数据整合到一页上。
设计原则
（一）满足业务需求情况下，尽量降低主键的长度
原因：二级索引中索引挂载的数据就是主键，主键很长，二级索引又很多，则会占用大量磁盘空间，搜索的时候也会增加IO计算量
（二）插入数据时，尽量选择顺序插入，尽量使用自增主键
原因：避免页分裂现象
（三）尽量不要使用UUID或其它自然主键，比如身份证号
原因：主键本身是无序的，会出现页分裂现象，并且UUID、身份证号本身就很长影响搜索效率
（四）业务操作时，避免对主键的修改
原因：修改主键，同时也要维护二级索引的数据结构，代价很大
3、order by优化
排序方式有两种
----Using filesort——通过索引或者全表扫描，读取到满足条件的数据行，然后把这些数据行放入缓冲区中进行排序操作，所有不是通过索引直接返回结果的排序都叫filesort排序，效率比较低
----Using index——通过有序索引直接返回有序数据，称为Using index，不需要额外排序，操作效率高
order by排序时，想要Using index，必须使order by字段名先后顺序与设置索引时一致。
如idx_user_name_age索引排序时，就必须order by name,age。
并且索引也有asc与desc之分，即索引也有升序排，降序排，如果索引都是升序，而order by的时候一升一降，则仍然会Using filesort
原因：联合索引中已经固定好页内的内容，如name=李四,age=18——>name=张三，age=20——>name=张三,age=30，而order by有先后关系，顺序不对，仍然需要放入缓冲区进行排序，只有完全匹配索引内容，才可以Using index
4、group by优化
分组操作时也可以使用索引提高效率，索引的使用同样要满足最左前缀法则，且顺序要一致
原因：与排序类似，分组同样需要相同的顺序才能与页内顺序保持一致
5、limit优化
数据量大时查询越往后查询越慢，此时需要索引优化
优化思路：一般分页查询的时候，通过创建 覆盖索引 能够更好的提升性能，并且配合覆盖索引加子查询进行优化
理解：大数据量情况下，想要靠后的数据行数据是十分缓慢的，我们可以通过覆盖索引，比如只查找对应id，提高搜索效率，再将需要的一部分id通过子查询的方式，将数据变成临时表，进行联表查询，这样子查询可以走主键索引，联表查询也可以走主键索引
例：select t1.* from tb_user t1,(select id from tb_user limit 10000000,100) t2 where t1.id = t2.id
6、count优化
优化思路：自己计数，用redis存储总数。
count()，对于返回的结果集一行一行判断，不是NULL就累加1，最后返回结果值
效率：count(字段)<count(主键 id)<count（1）≈count(*)，数据库对count(*)有优化，直接从服务层计算。
7、update优化
在事务中，update语句如果使用了索引，则会针对索引添加行锁，如果update语句条件字段中没有索引，则会由行锁升级为表锁。
优化思路：规避锁升级，update语句尽量使用索引，表锁影响并发性能
总结：用索引，遵守最左前缀原则，化零为整减开销，主键用自增且避免修改，主键顺序插入，count(*)有优化，
186
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
