// 假設(shè)一個(gè)數(shù)據(jù)頁(yè)16K、一行數(shù)據(jù)1K、索引間指針6字節(jié)、索引字段bigint類型(8字節(jié))

// 索引個(gè)數(shù)
K = 16*1024/(8+6) =1170

// 單個(gè)葉子節(jié)點(diǎn)記錄數(shù)
N = 16/1 = 16

// 三層B+記錄數(shù)
V = K*K*N = 21902400

-- 查看當(dāng)前索引基數(shù)
mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test |   0 | PRIMARY |   1 | id   | A   |  100256 | NULL  | NULL |  | BTREE  |   |    |
| test |   1 | index_a |   1 | a   | A   |  98199 | NULL  | NULL | YES | BTREE  |   |    |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------------+
| Variable_name      | Value  |
+--------------------------------------+-------------+
-- 是否自動(dòng)觸發(fā)更新統(tǒng)計(jì)信息，當(dāng)被修改的數(shù)據(jù)超過(guò)10%時(shí)就會(huì)觸發(fā)統(tǒng)計(jì)信息重新統(tǒng)計(jì)計(jì)算
| innodb_stats_auto_recalc    | ON   |
-- 控制在重新計(jì)算統(tǒng)計(jì)信息時(shí)是否會(huì)考慮刪除標(biāo)記的記錄
| innodb_stats_include_delete_marked | OFF   |
-- 對(duì)null值的統(tǒng)計(jì)方法，當(dāng)變量設(shè)置為nulls_equal時(shí)，所有NULL值都被視為相同
| innodb_stats_method     | nulls_equal | 
-- 操作元數(shù)據(jù)時(shí)是否觸發(fā)更新統(tǒng)計(jì)信息
| innodb_stats_on_metadata    | OFF   |
-- 統(tǒng)計(jì)信息是否持久化存儲(chǔ)
| innodb_stats_persistent    | ON   |
-- innodb_stats_persistent=on，持久化統(tǒng)計(jì)信息采樣的抽樣頁(yè)數(shù)
| innodb_stats_persistent_sample_pages | 20   |
-- 不推薦使用，已經(jīng)被innodb_stats_transient_sample_pages替換
| innodb_stats_sample_pages   | 8   |
-- 瞬時(shí)抽樣page數(shù)
| innodb_stats_transient_sample_pages | 8   |
+--------------------------------------+-------------+

-- 重新統(tǒng)計(jì)索引信息
mysql> analyze table t;

-- 創(chuàng)建表
mysql> CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;

-- 定義測(cè)試數(shù)據(jù)存儲(chǔ)過(guò)程
mysql> delimiter ;
CREATE PROCEDURE idata ()
BEGIN

DECLARE i INT ;
SET i = 1 ;
WHILE (i <= 100000) DO
 INSERT INTO t
VALUES
 (i, i, i) ;
SET i = i + 1 ;
END
WHILE ;
END;
delimiter ;

-- 執(zhí)行存儲(chǔ)過(guò)程，插入測(cè)試數(shù)據(jù)
mysql> CALL idata ();

-- 查看執(zhí)行計(jì)劃，使用了字段a上的索引
mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra         |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+
| 1 | SIMPLE   | t   | range | a       | a  | 5    | NULL | 10000 | Using index condition |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+-----------------------+

-- 由于需要進(jìn)行字段b排序，雖然索引b需要掃描更多的行數(shù)，但本身是有序的，綜合掃描行數(shù)和排序，優(yōu)化器選擇了索引b，認(rèn)為代價(jià)更小
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra               |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
| 1 | SIMPLE   | t   | range | a,b      | b  | 5    | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+

-- 方案1：通過(guò)force index強(qiáng)制走索引a，糾正優(yōu)化器錯(cuò)誤的選擇，不建議使用（不通用，且索引名稱更變語(yǔ)句也需要變）
mysql> explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                       |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE   | t   | range | a       | a  | 5    | NULL | 999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+

-- 方案2：引導(dǎo) MySQL 使用我們期望的索引，按b,a排序，優(yōu)化器需要考慮a排序的代價(jià)
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                       |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE   | t   | range | a,b      | a  | 5    | NULL | 999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+

-- 方案3：有些場(chǎng)景下，我們可以新建一個(gè)更合適的索引，來(lái)提供給優(yōu)化器做選擇，或刪掉誤用的索引
ALTER TABLE `t`
DROP INDEX `a`,
DROP INDEX `b`,
ADD INDEX `ab` (`a`,`b`) ;

-- 表t中字段xxx的索引選擇性
select count(distinct xxx)/count(id) from t;

-- 只需要查 ID 的值，而 ID 的值已經(jīng)在 k 索引樹(shù)上了，因此可以直接提供查詢結(jié)果，不需要回表
select ID from T where k between 3 and 5

-- 增加字段V，每次查詢需要返回V，可考慮把k、v做成聯(lián)合索引
select ID,V from T where k between 3 and 5

-- id、name、age三列，name、age上創(chuàng)建聯(lián)合索引

-- 滿足最左前綴原則，name、age均走索引
select * from T where name='xxx' and age=12

-- Mysql自動(dòng)優(yōu)化，調(diào)整name、age順序，，name、age均走索引
select * from T where age=12 and name='xxx'

-- name滿足最左前綴原則走索引，MySQL5.6引入索引下推優(yōu)化（index condition pushdown)，即索引中先過(guò)濾掉不滿足age=12的記錄再回表
select * from T where name like 'xxx%' and age=12

-- 不滿足最左前綴原則，均不走索引
select * from T where name like '%xxx%' and age=12

-- 滿足最左前綴原則，name走索引
select * from T where name='xxx'

-- 不滿足最左前綴原則，不走索引
select * from T where age=12

mysql> create table SUser(
ID bigint unsigned primary key,
name varchar(64),  
email varchar(64),
...
)engine=innodb;

-- 以下查詢場(chǎng)景
mysql> select name from SUser where email='xxx';

-- 方案1：全文本索引，回表次數(shù)由符合條件的數(shù)據(jù)量決定
mysql> alter table SUser add index index1(email);

-- 方案2：前綴索引，回表次數(shù)由前綴匹配結(jié)果決定
mysql> alter table SUser add index index2(email(6));

-- 預(yù)設(shè)一個(gè)可以接受的區(qū)分度損失比，選擇滿足條件中最小的前綴長(zhǎng)度
select count(distinct left(email,n))/count(distinct email) from SUser;

-- 查詢時(shí)字符串反轉(zhuǎn)查詢
mysql> select field_list from t where id_card = reverse('input_id_card_string');

-- 創(chuàng)建一個(gè)整數(shù)字段，來(lái)保存身份證的校驗(yàn)碼，同時(shí)在這個(gè)字段上創(chuàng)建索引
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

-- 查詢時(shí)使用hash字段走索引查詢，再使用原字段精度過(guò)濾
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

-- 使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化
select id,email from SUser where email='xxx';