索引

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