索引

1. 索引的作用

   类似于一本书中的目录,起到优化查询的作用

2. 索引的分类(算法)

   B树 默认使用的索引类型

   （福利推荐：阿里云、腾讯云、华为云服务器最新限时优惠活动，云服务器1核2G仅88元/年、2核4G仅698元/3年，点击这里立即抢购>>>）

   R树

   Hash

   FullText

   GIS 索引

3. Btree索引功能上的分类

   1. 辅助索引

      • 提取索引列的所有值,进行排序
      • 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
      • 在叶子节点中的值,都会对应存储主键ID

   2. 聚集索引

      • MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的
      • MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
      • 聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根

   3. 聚集索引和辅助索引的区别

      • 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
      • 在一张表中,聚集索引只能有一个,一般是主键.
      • 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
      • 聚集索引,叶子节点存储的时有序的整行数据.
      • MySQL 的表数据存储是聚集索引组织表

4. 辅助索引细分

   1. 单列辅助索引
   2. 联合索引(覆盖索引)
   3. 唯一索引

5. 索引树高度

   索引树高度应当越低越好,一般维持在3-4最佳

   1. 数据行数较多

      分表 : parttion 用的比较少了；分片,分布式架构

   2. 字段长度

      业务允许,尽量选择字符长度短的列作为索引列

      业务不允许,采用前缀索引.

   3. 数据类型

      char 和 varchar

      enum

6. 索引的命令操作

   1. 查询索引

      desc city;

      PRI ==> 主键索引

      MUL ==> 辅助索引

      UNI ==> 唯一索引

      mysql> show index from cityG

   2. 创建索引

      • 单列的辅助索引

        mysql> alter table city add index idx_name(name);

      • 多列的联合索引

        mysql> alter table city add index idx_c_p(countrycode,population);

      • 唯一索引:

        mysql> alter table city add unique index uidx_dis(district);  mysql> select count(district) from city; mysql> select count(distinct district) from city;

      • 前缀索引

        mysql> alter table city add index idx_dis(district(5));

   3. 删除索引

      mysql> alter table city drop index idx_name; mysql> alter table city drop index idx_c_p; mysql> alter table city drop index idx_dis;

7. 压力测试

   1. 准备

      mysql> use test mysql> source /tmp/t100w.sql

   2. 未做优化之前测试

      mysqlslap --defaults-file=/etc/my.cnf  --concurrency=100 --iterations=1 --create-schema='test'  --query="select * from test.t100w where k2='MN89'" engine=innodb  --number-of-queries=2000 -uroot -p123 -verbose

   3. 索引优化后

      mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

执行计划分析

1. 作用

   将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率

2. 获取执行

   desc SQL语句
   explain SQL 语句

   mysql> desc select * from test.t100w where k2='MN89';

3. 分析执行计划

   • table

   • type

     查询类型

     • 全表扫描 ALL
     • 索引扫描 index,range,ref,eq_ref,const(system),NULL

   index: 全索引扫描

   mysql> desc select countrycode  from city;

   range: 索引范围扫描(> < >= <= , between and ,or,in,like )

   mysql> desc select * from city where id>2000; mysql> desc select  * from city where countrycode like 'CH%';

   对于辅助索引来讲,!= 和not in等语句是不走索引的

   对于主键索引列来讲,!= 和not in等语句是走rang

​
​

mysql> desc select  * from city where countrycode='CHN' or countrycode='USA'; mysql> desc select  * from city where countrycode in ('CHN','USA');

   改写    ```sql  desc   select  * from city where countrycode='CHN'   union all   select  * from city where countrycode='USA';  ```  

​
​

 **ref: 辅助索引等值查询**    ```sql  desc   select  * from city where countrycode='CHN'   union all   select  * from city where countrycode='USA';  ```  

​
​

 **eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件**    A join B     on a.x = B.y     ```sql  desc select b.name,a.name ,a.population    from city as a   join country as b   on a.countrycode=b.code sql  where a.population<100;  ```  

​
​

 **const(system) : 主键或者唯一键的等值查询**    ```sql  mysql> desc select * from  city where id=100;  ```  

​
​
​

你还在原价购买阿里云、腾讯云、华为云、天翼云产品？那就亏大啦！现在申请成为四大品牌云厂商VIP用户，可以3折优惠价购买云服务器等云产品，并且可享四大云服务商产品终身VIP优惠价，还等什么？赶紧点击下面对应链接免费申请VIP客户吧：

1、点击这里立即申请成为腾讯云VIP客户

2、点击这里立即注册成为天翼云VIP客户

3、点击这里立即申请成为华为云VIP客户

4、点击这里立享阿里云产品终身VIP优惠价

喜欢 (0)赏

[[email protected]]

分享 (0)