索引

1. 索引的作用

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

2. 索引的分类(算法)

   B树 默认使用的索引类型

   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 city\G

   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;
 ```

​
​
​