MYSQL 8 基本操作之04 (索引)

1. 创建索引

root@127.0.0.1 : testdb【11:03:23】134 SQL->alter table mp_user drop index ind_mp_user_01;
root@127.0.0.1 : testdb【11:03:23】134 SQL->create index ind_mp_user_01 on mp_user(mp_user_name) using btree;
or
root@127.0.0.1 : testdb【10:24:07】10 SQL->alter table mp_user add index ind_mp_user_01 (mp_user_name) using btree;

root@127.0.0.1 : testdb【11:03:26】135 SQL->explain select * from mp_user where mp_user_name='xag5' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mp_user
   partitions: NULL
         type: ref
possible_keys: ind_mp_user_01
          key: ind_mp_user_01
      key_len: 83
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index

root@127.0.0.1 : testdb【11:20:13】197 SQL->SHOW INDEX FROM mp_user;
+---------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| mp_user |          0 | PRIMARY        |            1 | mp_user_seq  | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| mp_user |          1 | ind_mp_user_01 |            1 | mp_user_name | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

2.B-TREE索引与HASH索引，两者的区别

B-TREE索引
B-TREE索引的特点

B-TREEB-TREE以B+树结构存储数据，大大加快了数据的查询速度
B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）
 
B-TREE索引使用场景

全值匹配的查询SQL，如 where act_id= '1111_act'
联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
匹配模糊查询的前匹配，如where act_name like '11_act%'
匹配范围值的SQL查询，如where act_date > '9865123547215'（not in和<>无法使用索引）
覆盖索引的SQL查询，就是说select出来的字段都建立了索引
 

HASH索引
HASH的特点

Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引,
例如=和<=>【安全等於 NULL是等於NULL的】操作符（但是快很多）
存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询
Hash索引无法用于排序
Hash不适用于区分度小的列上，如性别字段

3.查看索引使用情况

#如果索引正在工作，则Handler_read_key 值将很高（代表一个索引值被读的次数，很低则表示索引很少被使用）
#Handler_read_rnd_next值高意味着查询低效，需要建立索引补救（值表在数据文件中读下一行的请求数，如果进行大量的表扫描则Handler_read_rnd_next值较高，通常未使用到索引）
root@127.0.0.1 : testdb【10:42:17】14 SQL->show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 120   |
| Handler_read_last     | 0     |
| Handler_read_next     | 164   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 14    |
| Handler_read_rnd_next | 17    |
+-----------------------+-------+