Mysql数据数据库引擎InnoDB与MyISAM

序：什么是数据引擎？它的作用是什么？

注：这里主要以mysql5.7，对于最新mysql6，8有了更多的变化，暂无研究，等研究后再次更新

mysql的数据存储在磁盘与内存(内存中数据也是来源与磁盘，放在内存中为了加快查询效率)中，这些数据被mysql使用了不同的技术形成了不同的数据文本结构放入了磁盘中，在我们使用时这些技术通过不同的索引技巧，不同的锁定水平使得我们可以放心，便捷快速的使用这些数据。这些对应的技术就是数据引擎，数据引擎在mysql是一个核心组建，在每一张表的创建时，都会指定先对应的数据引擎，最终根据用户的最终选择，构建相对应的数据结构。

mysql不同的版本对应的数据引擎的种类也不同，在5.7中主要可以分为： 1. MyIsam , 2. Mrg_Myisam, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive,  8 InnoDB这几种，而我们使用最多的最频繁的就两种：1InnoDB与Mylsam。所以这里只介绍InnoDB与Mylsam

1:InnoDB

InnoDB是我们现实中用的最多的一种引擎，它使用针对于事务的存储引擎。InnoDB引擎提供了对ACID(A：原子性;C：一致性;I：隔离型;D：一致性)事务的支持,同时实现了sql标准的4大隔离机制(1：读未提交；2：读已提交；3：可重复读；4：序列化)。该引擎提供了两种锁级别，行级锁和表级锁。MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。InnoDB创建一张表时会创建3个文件，其中db.opt存放了数据库的配置信息，比如数据库的字符集还有编码格式。student.frm是表结构文件，仅存储了表的结构、元数据(meta)，包括表结构定义信息等。不论是哪个表引擎都会有一个frm文件。student.ibd是表索引文件，包括了单独一个表的数据及索引内容。InnoDB的特点可以归纳以下几点：

1. InnoDB引擎支持事务与外健约束
2. InnoDB引擎支持外健约束
3. InnoDB引擎支持行级锁(适用于高并发型业务)和表级锁约束
4. InnoDB引擎不支持FullText类型索引(这句话其实有一定问题，mysql5.6之前不支持，5.6之后支持)
5. InnoDB引擎支持自动增加列属性auto_increment
6. InnoDB引擎不保存当前表的行数，所有对应的如果要查询总数需要扫描全表
7. InnoDB引擎在数据迁移需要通过导成

1.1：InnoDB中的索引

InonDB中存在两种类型索引：主索引和辅助索引。

InnoDB引擎创建的索引其数据底层为B+树，B+树中叶子节点中存储的为实际的数据(为表中的行数据)，这么看来InnoDB的索引本身就是数据文件，所以对该索引又称为聚簇索引(也可以称为聚集索引)(聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的)，索引对应的key一般为该表的主健(若当前表中无主键时，InnoDB会选择一个非空的唯一索引来创建聚簇索引；如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引。其实这么看来也可以理解为，InnoDB的表肯定包含主健，也为何我们一般建议表的主健为自增的，因为自增的数字在构建B+树，极大避免了为了维持B+Tree的特性而频繁的分裂调整的消耗，同时避免使用长随机字符串—例如UUID，理由在于：第一随机字符串增加了频繁的分裂调整的概率，第二：过长的字符串存储对应节点上导致该节点可存储的值变少，那么在相同数据下该健越大B+树越瘦高)。上文说到为了保证查询效率，mysql将B+树中的节点与磁盘的块(或者称为)结构结合起来，使得我们访问该节点只花费一次io操作。而因为数据对应的块只能和B+树的一个节点进行绑定，那么就表明了该索引只会存在这一个，所以我们也可以叫它主索引。如下图所示为一个主索引结构：

   

右侧为一个表其中col1为主健，那么主健15对应行数据在叶子节点中存储。这时候就会产生一个疑问？如果我对于Col3列构建一个索引那么，InnoDB该如何处理呢？这里就引出了第二个类型的索引：辅助索引(从索引和数据是否在一起的角度也可以称为非聚齐性索引。这里为何叫它辅助索引，后续介绍)

上文中说到因为每个数据块只能对应主索引的节点，那么col3创建的索引对应的B+树的结构是怎么样的呢？如下图所示：

 

这里以英文字符的ASCII码作为比较准则。该树与主索引树中最大的区别在在于叶子节点存储为数据对应的主健(这里也是为何主健不易过大的原因之一，过大的主键会导致辅助索引的树体积过大)，从这点可以看出辅助索引搜索数据可能需要检索两遍索引：第一遍通过辅助索引查到对应数据若如果所需的信息不够那么此时再通过主索引查询真正的数据。所以这里也是何称为辅助索引的原因，因为它是辅助于主索引的存在，最终都要走主索引这一条路。这里我们可以想象主索引的角色类似于公司中CTO，而辅助角色类似于架构师，多个架构师收集方法，最终都是需要CTO进行审核确定，那么架构师的作用就是辅助于CTO(该CTO也是可以收集方案)作出决断。

1.2：InnoDB中的锁

可参考大神文章很详细

InnoDB中锁可以分为两种：表锁和行锁，两者的粒度不一样，实现细节不一样，各有自己的优点和缺点，

1. 表锁：锁粒度大，加锁和解锁实现较为简单，数据库开销小，加锁块，但发生锁冲突的概率高，并发度低，因为锁的粒度大，不会出现死锁
2. 行锁：锁粒度下，加锁和解锁实现复杂，数据库开销大，加锁慢，当发生锁冲突的概率低，并发度高，因为锁的粒度小，会出现死锁的状况

注：InnoDB中行锁是和索引有关，也就是说只有用到了索引才能使用行锁，否则都只使用表锁

1.2.1：表锁

表锁一般分为两种：表读锁(Table Read Lock)和表写锁(Table Write Lock)

其中在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞即

读读不阻塞：若存在用户在读数据，其他的用户也在读数据，不会加锁

读写阻塞：若存在用户在读数据，那么其他用户不能修改当前用户读的数据，会加锁

写写阻塞：若存在用户在写数据，那么其他用户不能修改当前用户写的数据，会加锁

读锁和写锁是互斥的，读写操作是串行。

• 如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！
• 写锁和读锁优先级的问题可以通过参数调节：max_write_lock_count和low-priority-updates

共享锁用法：
LOCK TABLE table_name [ AS alias_name ] READ

排它锁用法：

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

解锁用法：

unlock tables;

1.2.2：行锁

行锁必须要用到索引的锁

InnoDB中行锁可以实现方式分为Record Lock、Gap Lock、Next-key Lock这三种

1. Record Lock：记录锁是在行锁上衍生的锁，记录锁锁的是表中的某一条记录，记录锁的出现条件必须是精准命中索引并且索引是唯一索引，如主键id。
2. Gap Lock：间隙锁，它锁定某个区间数据，间隙锁发生在查询条件为范围查询的时候,并且设定的事务类型为可重复读，并且请求共享锁或排他锁的时候，此时mysql在查询条件范围内添加锁，这里面不仅仅包含数据库中已存在的数据，还包含未来可能插入的数据，每个间隙都是两端开放的区间，这样在一定的程度上避免幻读。例如:select * from table where id >10 for update;如数据中存在id为 11，12的两条数据，mysql对此这两条数据进行加上X锁，同时也会对id大于10的间隙(这些数据不存在)加锁。那么在当前事务结束之前其他事务的新插入一条id大于10的数据是无法做到的，这在一定程度上避免了幻读的情况。默认情况下间隙锁是开启状态，可以通过show variables like 'innodb_locks_unsafe_for_binlog';命令查看为OFF则代表开启，若要关闭在my.cf中添加innodb_locks_unsafe_for_binlog = 1，重启mysql即可
3. Next-key Lock：临键锁，它锁定的是一个左开右闭的一段区间，该锁其实由Record Lock+Gap Lock共同组成,在隔离级别为可重复读情况下使用，（有以下注意情况：1.对主键或唯一索引，使用的是=查询并且命中，这种场景本身就不会出现幻读，所以只会加行记录锁,如果未如果是范围查询，.2.没有索引的列，当前读操作时，会加全表gap锁，生产环境要注意。3.非唯一索引列，如果where条件部分命中(>、<、like等)或者全未命中，则会加附近Gap间隙锁。例如，某表数据如下，非唯一索引2,6,9,9,11,15。如下语句要操作非唯一索引列9的数据，gap锁将会锁定的列是(6,11]，该区间内无法插入数据）

Next-key Lock有以下注意情况：

如下所示(其中id为主键，userId为普通索引)：

表内数据

1:对主键或唯一索引，使用的是=查询并且命中，这种情况只会加行记录锁,不会存在间隙锁，如果当前查询条件未命中那么存在间隙锁并且它锁定的区域为查询值所在的间隙区域的一个开区间。

1. select * from test.seckillOrder where id=6 for UPDATE ; 查询到数据时，为当前行加记录锁，只锁定当前行
2. select * from test.seckillOrder where id=7 for UPDATE ; 未查询到数据时，间隙锁为当前值所在间隙开区间即(6,10)之间范围
3. select * from test.seckillOrder where id>6 and id <10  for UPDATE ; 间隙锁范围为: (6,10] -在范围之内左开右闭
4. select * from test.seckillOrder where id>=6 and id <=10  for UPDATE ; 间隙锁范围为: (5,11] ，实际查询的范围可以看作id>5 and id<12
5. select * from test.seckillOrder where id>6   for UPDATE ; 间隙锁范围为: (6,+∞)
6. select * from test.seckillOrder where id<10   for UPDATE ;  间隙锁范围为: (-∞,10]

2:对于非主键索引或者非唯一索引时,也分为以下情况

1. select * from test.seckillOrder where userId=6 for UPDATE ; 查询到数据时，间隙锁为当前数据所在的上一个间隙(3,6]
2. select * from test.seckillOrder where userId=7 for UPDATE ; 未查询到数据时，间隙锁为当前值所在间隙开区间即[6,10]之间范围
3. select * from test.seckillOrder where userId>4 and userId <11  for UPDATE ;范围查询，间隙锁为当前rang范围所在的间隙范围，因为4和10也在(3,6]and(10,12]中，所以总个间隙锁范围为(3,6]and(6,10]and(10,12]
4. select * from test.seckillOrder where userId>7 and userId <10  for UPDATE ;范围查询，间隙锁为当前rang范围所在的间隙范围(6,10]
5. select * from test.seckillOrder where userId >13  for UPDATE ;范围查询，间隙锁为(12,+∞)(这里测试时遇到一个问题，记录下如果查询的数据的条数超过整个数据的15%,那么当前索引失效，会加全表gap锁)
6. select * from test.seckillOrder where userId <3  for UPDATE ;范围查询，间隙锁为(-∞,3]

3:对于非索引列查询，当前读操作时，会加全表gap锁

      

InnoDB中行锁按照使用方式可以分为两种：共享锁和排他锁

共享锁(又称S锁 读锁)：若当前A事务对某数据加了共享锁，此时该事务只能对该数据进行读不能进行修改操作。此时其他事务只能对该数据再加S锁，而不能加X锁(排它锁)，只能等A事务将锁释放时，其他事务才能对该数据进行修改操作。(在java中也有对于共享锁的具体实现，例如CountDownLatch,ReadWriteLock中的读锁都是共享锁。具体可参考AQS它是java中除Synchronized其它锁的本质)

#使用方式 
select ..... from  table lock in share mode;

排他锁(又称X锁 写锁)：若当前A事务对某事务加了排他锁，此时该事务对该数据可以进行读写操作，其他事务不能再对该数据加任何锁，直到A释放了锁。这保证了其他事务在A释放锁之前不能再读取和修改A。(在java中也有对于排他锁的具体实现，例如Synchronized,ReentrantLock都是排他锁)

##使用方式
select ... from table for update;

 

1.2.4：锁与索引的关系

从上文我们可以知道，如果要用到行锁肯定会使用到索引的

1. 当查询语句中索引类型为主索引时，它锁定的是查询主索引对应的行数据
2. 当查询语句中索引类型为辅助索引时，如果通过辅助索引查询的数据满足要求时，不会再查询主索引那么此时只会锁辅助索引。若不满足查询需求时仍然会查询主索引此时就会锁主索引
3. 当查询语句中不包含索引时，此时会用到表锁

1.2.5：InnoDB中MVCC(Multi-Version Concurrency Control)

MVCC-多版本并发控制，它可以说mysql中对乐观锁(可以看作是一个携带版本控制的CAS算法)的一个具体实现，

1.2.6：当前读与快照读

对于一个事务中的查询主要分为两种，当前读与快照读

当前读：读取的是数据库中最新版本的数据的数据，对要读取的数据加锁()，保证无其他事务对其进行修改导致数据安全，锁机制一般通过Next-key Lock(Record Lock+Gap Lock)来实现了。主要类似与以下语句为当前读：

1. select...lock in share mode (共享读锁)
2. select...for update
3. update , delete , insert

快照读：快照读主要通过MVCC机制实现，使用快照读，读取数据时不需要对数据进行加锁，且快照读不会被其他事物阻塞。所以它的性能非常高，但会存在一个问题即获取到的数据不一定是最新实时的(和事务的隔离级别)。

 

1.2.7：事务隔离级别与锁的关系

InnoDB引擎中提供了4种事务隔离级别：

1. 读未提交(READ UNCOMMITTED):隔离级别最低，存在脏读情况
2. 读提交(READ COMMITTED)：存在不可重复读，幻读的情况
3. 可重复读(REPEATABLE READ)：存在幻读的情况
4. 序列化(SERIALIZABLE)：隔离级别最高，保证事务安全，但完全串行，性能低

读提交(READ COMMITTED)：

1. 事务中若使用快照读，那么每一次查询都是快照读，所以每一次查询都是最新的数据，即事务中每次SELECT都看到其它已commit事务所作的更改。
2. 事务中若使用当前读，当前读也不加任何锁，当前insert,update,delete操作加X锁。这也是为何在该事务情况下会不可重复读的问题

不可重复读(REPEATABLE READ)：

1. 事务中若使用快照读，那么事务开启后第一个select语句才是快照读的地方，而且只有在本事务中对数据进行更改才会更新快照，因此，只有第一次SELECT之前其它已提交事务所作的更改你可以看到，但是如果已执行了SELECT，那么其它事务commit数据，你SELECT是看不到的；
2. 事务中若使用当前读，那么对当前读加S锁，对于当前insert,update,delete操作加X锁，对于当前范围查询添加(Grap Lock)间隙锁。这也是为何REPEATABLE READ可解决重复读，以及一定程度上解决了幻读问题

 

1.2.8：死锁

为何行锁中会出现死锁(死锁出现的原因在于互斥、持有、不可剥夺、环形等待)的情况，其实和索引也有着一定的关系

可以分为以下几种情况：

​第一种： 当两个事务同时执行，t1语句锁住了A数据主索引，t2语句等待了B数据的索引锁释放。另一个事务中t1语句锁定了B数据索引，t2语句A数据等待主索引的锁释放，在高并发的情况下就会导致了死锁。例如：

--A事务
select name from user where id =3 for update; 

update t1 set name ='aa' user where id =1;

--B事务
select name from user where id =1 for update; 

update t1 set name ='bb' user where id =3;

--table数据 id为主键
#####id#########name#########
#####1##########张#########
#####3##########李#########

第二种：从上述描述中我们知道InnoDB中索引分为两种聚集性或者非聚集性 ，非聚集性索引中叶节点存储的是主键，所以如果一个查询使用的非聚集性查询时不当的写法+高并发(并不是说在非并发下就不产生，而是指的概率)时就会产生死锁。如下所示：

--主索引字段为id 辅助索引为name age
--事务1
select * from user where name ='张三' for update；

--事务2
select * from user where old >25 for update;

--表中数据为
######id#######name######age#####
######1########张三#######26#####
######2########李四#######21#####
######3########王五#######23#####
######4########张三#######29#####
######5########李四#######22#####

为何出现死锁的原因在于：事务1的查询会根据辅助索引name查询到两条记录，并且存储是按照先小后大的顺序进行排序的，即加锁的顺序在于[1,张三,26]后[4,张三,29]，事务2的查询会根据辅助索引age也查询到两条记录,但是此时的查询获取的顺序为先大后小，即加锁顺序为[4,张三,29]，后[1,张三,26]，那么此时在高并发的情况下，就大概率出现死锁了，事务1于事务二都在等对方的锁释放。

那么对应我们要在写应用中注意避免出现死锁：

1. 存在操作于同一批数据的业务，需要在代码执行逻辑上保证顺序相同，避免发生交差更新的情况，例如事务1->更新A表->更新B表，事务2-> 更新B表->更新A表，需要保证其代码顺序一致
2. 在同一个事务中尽量使所有访问资源用一个锁锁定，这样可以减少死锁的发生
3. 对于很容易发生死锁的应用场景，看是否能升级锁粒度使用表级锁来进行处理(这样会牺牲一定的并发度)
4. 减少事务中业务代码的复杂读，减少其执行时间也就是加快锁释放
5. 若一个事务中包含多个锁的存在，尽量把最有可能造成锁冲突最可能影响并发度的锁尽量往后放

若出现死锁了，有两种解决方案：

1. 直接进入等待，直到超时，超时的时间根据配置的innodb_lock_wait_timeout属性设定，该值默认的时间为50s，当超时时，第一个被锁定的线程才会退出，这对于我们普通的应用根本无法接受，在应用端早就被断开连接了

2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以执行，将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

InnoDB锁中对应的特性：

1. 在不使用索引的查询中，InnoDB使用的表锁
2. 行锁中它锁定的不是单行数据，而是锁定的是当前索引，所以也会存在访问不同的行的时候因为可能索引相同(非唯一健构建的索引查询会出现此问题)，也会存在锁冲突。
3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
4. 便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引

 

2:Mylsam

Mylsam引擎在创建表的同时对应会创建3个文件，第一个用于存储表的定义—.frm文件；第二个用于存储表数据信息—.MYD；第三个用于存储索引信息—.MYI,操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。而Mylsam引擎独立与操作系统(即Linux与Winodws都可以使用)，所以这些数据可以通过文件进行平台迁移。

MyLsam有着自己的特性：

1. Mylsam引擎构建的表是不支持事务机制的，如果要使用需要使用者在业务代码中自己实现
2. Mylsam引擎支持不支持外健约束
3. Mylsam引擎支持表级锁约束
4. Mylsam引擎支持查询速度很快，应对于多度写少的场景，但若该表的insert或者update操作较多，采用表锁的效率较低，建议使用InnoDB
5. Mylsam引擎创建的表会记录当前表行数，如果SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描
6. Mylsam引擎创建的表的数据迁移较为方便，只需要讲表对应的3个文件迁移到对应的新的服务即可

2.1：Mylsam中的索引

Mylsam引擎创建的索引也可以分为两种类型：主索引与辅助索引

如下所示是一个主索引结构它的数据底层也为B+树，其中叶子节点中存放的是实际数据地址，如下图所示为Mylsam中的索引结构：

假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 

同样也是一棵B+树，data域保存也是数据记录的地址。其实在Mylsam中，主索引要求key是唯一的(Mylsam创建的表不一定要求设定主健)，而辅助索引的key可以重复，除此之外Mylsam中主索引和辅助索引（Secondary key）在结构上没有任何区别。

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取数据文件中相应数据记录。从上述描述可以看出，Mylsam引擎创建的索引数据与数据是分离的所以对比与InnoDB中索引将索引也称为非聚簇索引(也可以称为非聚集索引)(叶级页指向表中的记录，记录的物理顺序与逻辑顺序没有必然的联系。非聚簇索引则更像书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的)。

2.1：Mylsam中的锁

可参考大神文章很详细

3:注意：

3.1：组合索引(联合索引)

组合索引也可以称为联合索引，从字面上就可以理解，相对于一般索引的单字段构成，联合索引可以为多个字段创建一个索引。例如当我们为字段(a,b,c)3个字段创建一个联合索引，那么对于索引存储是按照第一个字段a进行排序，若a相同时根据B进行排序，若B相同时再根据C进行排序。这样的索引的方式我们可以类比于小时候我们通过新华字典学习拼音查询一样，相同的前缀单词肯定是排在一起的，总体上是根据首字母排序。如下所示：

a b c
1 1 6
1 2 1
1 2 3
1 5 1 
2 3 6
2 4 1
2 4 2
2 6 1 

联合索引也是由B+树实现了，不通于单索引中每个节点存储的是当字段数据，联合索引中节点会包含对应的所有字段，如下图所示：

第二例中第一个字段都是存储的Akroyd，那么此时就会按照第二个字段进行排序可以看到的确如此(Christian-Debble-Kristen).

3.2：最左前缀

从上述可以看出要想该索引有效，必须要求查询条件中必须包含第一个字段，下列查询都会使用到该联合索引，因为三个查询按照 （a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

下列查询语句就不会使用到索引

select * from table where b=2；
select * from table where c=3；
select * from table where b=2 and c=3；

下列查询语句就只会用到索引a

select * from table where a=1 and c=3；

下列查询语句也会用到索引，虽然查询位置没有遵循最左在前，原因在于mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。建议还是索引顺序来查询，这样查询优化器就不用重新编译了。

select * from table where b=2 and a=1；
select * from table where b=2 and a=1 and c=3；

3.3：前缀索引

除了联合索引之外，对mysql来说其实还有一种前缀索引。前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

一般来说以下情况可以使用前缀索引：

• 字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
• 字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
• 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

一些文章中也提到：

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

3.4：索引优化策略

1. 最左匹配原则
2. 主外健一定构建索引
3. 对where，on，groupby order by出现的字段构建索引
4. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。此类所有不建议构建索引
5. 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
6. 较长的字符串使用前缀索引，例如邮箱
7. 尽量的扩展索引而不要新建索引，比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
8. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
9. 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
10. 对于like查询，”%”不要放在前面。例如like 'aa%'就会使用到索引而like '%aa%'就不会使用到
11. 查询where条件数据类型不匹配也无法使用索引，例如字段为字符串类型，查询时候使用number类型此时该字段对应索引无效
12. 如果查询命中数超过数据总数的15%，那么对应索引失效