MySQL索引

 索引的语法

练习：

增强plug：

MySQL的索引是在存储引擎层实现的，不同的存储引擎有着不同的结构，主要包含以下几种：

索引结构	描述
B+Tree索引	常见的索引类型，大部分的引擎都支持B+数索引
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配的索引列查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text (全文索引)	是一种通过建立倒排索引快速匹配文档的方式，类似于 Lucene,Solr,ES

索引结构：

 

 

---

索引分类：

select * from user where name=‘Arm’ ，先会从二级索引查到Arm对应的主键是10，然后到聚集索引那里找到主键是10的，拿到其对应存储的row。

---

 

索引性能分析：

 

show [session] [global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、update、delete、select的访问频次：

show global status like 'Com_______';  (7个下划线)

 

慢查询日志：

mysql中默认是未开启慢查询日志的，可以用show variables like 'slow_query_log ';这个指令查看是否开启

 需要手动开启：

配置完毕之后，通过指令systemctl restart mysqld 重启MySQL服务器，查看慢日志文件记录的信息 /var/lib/mysql/localhost-slow.log

tail -f localhost-slow.log 用这句指令后只要慢查询日志有追加内容都会被显示出来。

show profiles 

 

 

 

 

指定query_id在各个阶段耗时的情况：

 

explain

 

主要关注执行计划中的type、possible_key、key、key_len字段

 

 

例子：

 id值越大越先被执行，id相同执行顺序从上到下

 possible_keys可能用到的索引是idx_user_name,   key实际用到的索引是idx_user_name。

 

索引的使用规则：

一、验证索引对查询效率的提升：

1、没构建索引时耗时20.78秒：

 

2、构建索引：

 3、构建索引后耗时0.01秒：\G是为了显示整齐。

 

二、最左前缀法则（针对联合索引）

 

 下图表中的联合索引professio、age、status，要遵守最左前缀法则，最左边的profession一定要存在，位置顺序不讲究。

 下图的语句可以查看sql执行计划中索引是否生效。

 三、

 所以业务允许的情况下尽量使用>=或者<=，避免范围查询右侧的列索引失效。

 四、

 未运算时索引正常 

 加上运算后索引失效

 

五、字符串不加引号会让索引失效

 

六、

 后面模糊走索引：

 前面模糊不走索引：

 

七、

 or前后两个条件的字段都有索引，这样索引才会生效。

 

八、

 例子：表里有phone字段从17799990001----17799990020共20条，当查找phone>=17799990005时的数据有15条，则mysql认为大部分数据都满足条件，直接走全表扫描不走索引；当查找phone>=177999900015时的数据有5条，则只占总数的小部分，那么mysql就会觉得走索引快一点，这时索引就会生效。mysql会看过滤的数据占总数据的多少来评估是否走索引。

九、

 告诉mysql查询时建议用idx_user_pro这个索引查，接不接受看mysql，用force index就会强制要求mysql用指定索引。

 

十、

select*很容易出现回表查询，除非都创建了联合索引。

 思考：

 username和password建立联合索引即可，这样不需要回表查询。

十一、

 截取长文本的字符串的一部分来构建索引的结构：

 

 

例子：user表

 查看截取到9个长度的时间

 最终决定截取5个长度的字符串作为索引

 

 十二、建议用联合索引

 联合索引的结构：

例子：

 

索引的设计原则：

总结：

1、索引的概述：

索引是高效获取数据的数据结构

2、索引的结构：

B+Tree

Hash

3、索引的分类：

主键索引、唯一索引、常规索引、全文索引

聚集索引、二级索引

4、索引语法：

create [unique] index xxx on xxx(xxx);

show index from xxx;

drop index xxx on xxx;

5、sql性能分析：

执行频次、慢查询日志、profile、explain

6、索引的使用：

联合索引

索引失效

sql提示

覆盖索引

前缀索引

单列/联合索引