Mysql索引

 

参考：《MySQL高性能》 第五章

          《MySQL技术内幕：innodb存储引擎》 第五章

 

 

B+tree树 索引

 

聚簇索引(一级索引)：主键(无主键时指定一个唯一非空索引代替，无唯一非空索引时 隐式的指定主键rowid代替)，叶子节点中存储行的所有数据

辅助索引(二级索引)：普通索引，叶子节点只存储索引列对应的数据和主键列(用于去聚簇索引查找数据)

 

 

一、索引是顺序组织存储的，可避免排序，  将随机I/O变为顺序I/O

 

 

 

二、组合索引的排序

 

 

 

三：测试如下：

--辅助索引 组合索引的使用规则，

--1、建立测试数据
mysql> create table test_idx_comb_table(id int ,name varchar(10),score int ,class int);
Query OK, 0 rows affected (0.86 sec)

mysql> alter table test_idx_comb_table add index idx_test_idx_comb_table(id,name,score);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test_idx_comb_table values(1,'zhao',98,11),(2,'qian',99,11),(3,'sun',78,11),(4,'li',80,11);
Query OK, 4 rows affected (0.12 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

--2、查看测试数据
mysql> select * from test_idx_comb_table;
+------+------+-------+-------+
| id   | name | score | class |
+------+------+-------+-------+
|    1 | zhao |    98 |    11 |
|    2 | qian |    99 |    11 |
|    3 | sun  |    78 |    11 |
|    4 | li   |    80 |    11 |
+------+------+-------+-------+
4 rows in set (0.00 sec)


--3、查看执行计划
--3.1、全值匹配
mysql> explain select id,name,score,class from test_idx_comb_table  where id=3 and name='sun' and score='78';
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table               | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | ref  | idx_test_idx_comb_table | idx_test_idx_comb_table | 43      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--3.2、索引覆盖查询
--Using index   索引覆盖扫描，server层完成，无需回表
mysql> explain select id,name,score from test_idx_comb_table  where id=3 and name='sun' and score='78';      
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table               | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | ref  | idx_test_idx_comb_table | idx_test_idx_comb_table | 43      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--3.3、匹配索引最左前缀
mysql> explain select id,name,score,class from test_idx_comb_table  where id=3 ;
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table               | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | ref  | idx_test_idx_comb_table | idx_test_idx_comb_table | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--3.4、匹配索引范围值
mysql> explain select id,name,score,class from test_idx_comb_table  where id between 2 and 3 ;
+----+-------------+---------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table               | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | range | idx_test_idx_comb_table | idx_test_idx_comb_table | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

--3.5、匹配索引一列并范围匹配另一列
--Using index condition (Index Condition Pushdown，ICP) 按照索引条件过滤数据行的同时 where条件中的其他条件去过滤这些数据行   
--开启时(5.6特性) 在查询id=3的数据行的同时 会根据 name like 's%'条件过滤，然后检索的结果指向聚集索引， 最后返回给用户， 减少了访问表的次数
--未开启时(5.5及之前版本) 先查询id=3的记录 ，然后检索的结果指向聚集索引，再根据name like 's%'条件过滤， 最后返回给用户
mysql> explain select id,name,score,class from test_idx_comb_table  where id=3 and name like 's%';
+----+-------------+---------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table               | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | range | idx_test_idx_comb_table | idx_test_idx_comb_table | 38      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


--3.6、MRR特性，开启MRR优化特性(mrr_cost_based默认为on 是成本算法自行判断是否采用MRR优化，  这里设置为off 后 强制采用MRR优化)
--read_rnd_buffer
mysql> set optimizer_switch='mrr=on,mrr_cost_based=off';
Query OK, 0 rows affected (0.00 sec)

--MRR特性：通过辅助索引找到的主键 缓存起来 然后根据 主键排序后 找到记录， 这样就可以把 辅助索引找主键的随机I/O 变为根据主键排序的 顺序I/O了
--在read_rnd_buffer 中缓存起来
mysql> explain select id,name,score,class from test_idx_comb_table  where id=3 and name like 's%';
+----+-------------+---------------------+-------+-------------------------+-------------------------+---------+------+------+----------------------------------+
| id | select_type | table               | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                            |
+----+-------------+---------------------+-------+-------------------------+-------------------------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | test_idx_comb_table | range | idx_test_idx_comb_table | idx_test_idx_comb_table | 38      | NULL |    1 | Using index condition; Using MRR |
+----+-------------+---------------------+-------+-------------------------+-------------------------+---------+------+------+----------------------------------+
1 row in set (0.00 sec)



--3.7、无法使用索引查询不是最左前缀的列，如name,score字段匹配
--Using where   从数据表返回数据，再过滤掉不需要的数据
mysql> explain select id,name,score,class from test_idx_comb_table  where  name='sun' ;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,name,score,class from test_idx_comb_table  where  score='78';                       
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_idx_comb_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

 

 

--4、查看索引状态：

测试已test_stat表为例，测试如下：

--1、查看表数据
mysql> select * from test_stat;
+---+---+------+------+------+------+
| a | b | c    | d    | e    | f    |
+---+---+------+------+------+------+
| 1 | 1 |   10 |   11 |  100 |  101 |
| 1 | 2 |   10 |   11 |  200 |  102 |
| 1 | 3 |   10 |   11 |  100 |  103 |
| 1 | 4 |   10 |   12 |  200 |  104 |
| 1 | 5 |   10 |   12 |  100 |  105 |
+---+---+------+------+------+------+
5 rows in set (0.07 sec)


--2、查看表信息
mysql> show create table  test_stat\G          
*************************** 1. row ***************************
       Table: test_stat
Create Table: CREATE TABLE `test_stat` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`,`b`),
  UNIQUE KEY `i2uniq` (`e`,`f`),
  KEY `i1` (`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


--3、查看索引信息
mysql> show index from test_stat\G
*************************** 1. row ***************************
        Table: test_stat          --表名
   Non_unique: 0                  --非唯一索引是1，唯一索引是0
     Key_name: PRIMARY            --索引名称
Seq_in_index: 1                   --索引中的位置
  Column_name: a                  --索引列的名称
    Collation: A                  --存储方式，B+ 树是A
  Cardinality: 1                  --唯一值的估算，越接近1 重复值越多，如果不准 ANALYZE TABLE 可以收集表统计信息 但最好业务低估收集
     Sub_part: NULL               --是否列的部分为索引，整列为索引时  显示null， 部分为索引时显示 具体数值 比如 key idx_a (a(2)) 就是只对前两个字段进行索引操作
       Packed: NULL               --如何被压缩，没有压缩为null
         Null:                    --索引列是否允许为null，可以为空表示为YES
   Index_type: BTREE              --索引类型，B+ 树是BTREE
      Comment:                    --注释
Index_comment:              
*************************** 2. row ***************************
        Table: test_stat
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 2
  Column_name: b
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: test_stat
   Non_unique: 0
     Key_name: i2uniq
Seq_in_index: 1
  Column_name: e
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: test_stat
   Non_unique: 0
     Key_name: i2uniq
Seq_in_index: 2
  Column_name: f
    Collation: A
  Cardinality: 5
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 5. row ***************************
        Table: test_stat
   Non_unique: 1
     Key_name: i1
Seq_in_index: 1
  Column_name: c
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 6. row ***************************
        Table: test_stat
   Non_unique: 1
     Key_name: i1
Seq_in_index: 2
  Column_name: d
    Collation: A
  Cardinality: 2
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
6 rows in set (0.00 sec)

 

 

 

--五、在线ddl

--在线ddl时，会将insert、update、delete等dml操作放到缓存中，待ddl完成后 再重做应用到表上  到达数据一致性

--缓存大小为innodb_online_alter_log_max_size，默认128M， 但缓存的dml操作空间不够时会报错  建议1G

mysql>  show variables like 'innodb_online_alter_log_max_size';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+
1 row in set (0.00 sec)


mysql> select 134217728/1024/1024 M;
+--------------+
| M            |
+--------------+
| 128.00000000 |
+--------------+
1 row in set (0.00 sec)

 

 

 

--六、参数优化

 

--optimizer_search_depth        //搜索深度

--默认 62， 越小越好

--如果连接7个表 可将值设置为0 、或者让优化器 自动选择表的数量的值7

 

 

 

--七、强制和忽略索引

--1、测试数据
mysql>  CREATE TABLE test_hint_index_table (
    ->   a int(11) ,
    ->   b int(11) ,
    ->   KEY (a),
    ->   KEY (b)
    -> );

mysql> insert into test_hint_index_table values(1,1),(1,2),(2,3),(2,4),(1,2);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


--2、初始执行计划，走的b，a的索引
mysql>  explain select * from test_hint_index_table where a=1 and b=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_hint_index_table
   partitions: NULL
         type: index_merge
possible_keys: a,b
          key: b,a
      key_len: 5,5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using intersect(b,a); Using where; Using index
1 row in set, 1 warning (0.00 sec)



--3、使用use index(a)让其走a的索引，但实际没有走key为null
mysql>  explain select * from test_hint_index_table use index(a) where a=1 and b=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_hint_index_table
   partitions: NULL
         type: ALL
possible_keys: a
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.02 sec)


--4、使用force参数强制走a索引，可以走到a的索引上
mysql> explain select * from test_hint_index_table force index(a) where a=1 and b=2\G     
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_hint_index_table
   partitions: NULL
         type: ref
possible_keys: a
          key: a
      key_len: 5
          ref: const
         rows: 3
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)



--5、可以使用ignore参数，来忽略索引的使用
mysql>  explain select * from test_hint_index_table ignore index(a,b) where a=1 and b=2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test_hint_index_table
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

 

 

    

--八、不走索引的情况

--1、统计信息不准确，需要表分析

--设置持久化保存，统计信息的表， 查看last_update 最后更新的情况
mysql.innodb_table_stats 和 mysql.innodb_index_stats


--表分析
--对整张表
analyze table test_create_table;

--对分区表的分区
alter table t_exchange analyze partition p2;





--2、隐式转换

--建立测试表，b字段为char类型、b字段建立索引
mysql> create table test_conver_table (a int ,b varchar(10));   
mysql> create index idx_test_conver_table on test_conver_table(b);

--插入数据
DROP PROCEDURE IF EXISTS p_test_conver_table;

DELIMITER $
CREATE PROCEDURE p_test_conver_table()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        INSERT INTO test_conver_table(a,b) VALUES(i,i);
        SET i = i+1;
    END WHILE;
    COMMIT;
END $innodb——

CALL p_test_conver_table();


--执行语句，b带入的是整型、不是字符型，发生隐式转换，不走索引，走的全表
mysql>  explain extended  select * from test_conver_table where b=10;
+----+-------------+-------------------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys         | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------------------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_conver_table | NULL       | ALL  | idx_test_conver_table | NULL | NULL    | NULL | 9760364 |    10.00 | Using where |
+----+-------------+-------------------+------------+------+-----------------------+------+---------+------+---------+----------+-------------+
1 row in set, 4 warnings (0.04 sec)

--查看警告，无法匹配
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                           |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                 |
| Warning | 1739 | Cannot use ref access on index 'idx_test_conver_table' due to type or collation conversion on field 'b'                                                                           |
| Warning | 1739 | Cannot use range access on index 'idx_test_conver_table' due to type or collation conversion on field 'b'                                                                         |
| Note    | 1003 | /* select#1 */ select `flydb`.`test_conver_table`.`a` AS `a`,`flydb`.`test_conver_table`.`b` AS `b` from `flydb`.`test_conver_table` where (`flydb`.`test_conver_table`.`b` = 10) |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)



--带入b='10' 字符串类型， 或者修改表b字段为整型即可