mysql索引

1、索引失效

1.1索引列是表示式的一部分，或是函数的一部分

SELECT book_id FROM BOOK WHERE book_id + 1 = 5;
SELECT book_id FROM BOOK WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(gmt_create) <= 1 

1.2隐式类型转换

CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `tradeid` (`tradeid`),
   KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SELECT * FROM tradelog WHERE tradeid=110717;

1.3隐式编码转换

CREATE TABLE `trade_detail` ( 
 `id` int(11) NOT NULL, 
 `tradeid` varchar(32) DEFAULT NULL, 
 `trade_step` int(11) DEFAULT NULL, /*操作步骤*/ 
 `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/ 
   PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=l.tradeid AND l.id=2;
SELECT d.* FROM tradelog l, trade_detail d WHERE (CONVERT(d.traideid USING utf8mb4)))=l.tradeid AND l.id=2;

1.4使用 order by 造成的全表扫描

SELECT * FROM user ORDER BY age DESC

上述语句在 age 上加了索引，但依然造成了全表扫描，这是因为我们使用了 SELECT *,导致回表查询，MySQL 认为回表的代价比全表扫描更大，所以不选择使用索引，如果想使用到 age 的索引，
我们可以用覆盖索引来代替:

SELECT age FROM user ORDER BY age DESC

或者加上 limit 的条件（数据比较小）

SELECT * FROM user ORDER BY age DESC limit 10

1.5不考虑索引覆盖的情况下，只要like后需要匹配的字段前加%，索引就会失效。但是后面加%，能使用索引

2、索引设计原则

第一颗星：WHERE 后面参与查询的列可以组成了单列索引或联合索引
第二颗星：避免排序，即如果 SQL 语句中出现 order by colulmn，那么取出的结果集就已经是按照 column 排序好的，不需要再生成临时表
第三颗星：SELECT 对应的列应该尽量是索引列，即尽量避免回表查询。

3、索引分类

最左前缀匹配原则：对于一个联合索引，如果有一个SQL查询语句需要执行，则只有从索引最左边的第一个字段开始到SQL语句查询条件中不包含的字段（不含）或范围条件字段（含）为止的部分才会使用索引进行加速。
在 MySQL数据库的 InnoDB存储引擎中，主键索引就是聚集索引，一张表至多有一个聚集索引。而在MyISAM存储引擎中，就没有聚集索引了，因为MyISAM存储引擎中的数据不是按索引顺序进行存储的。