MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

通过索引优化来实现MySQL的ORDER BY语句优化：

1、ORDER BY的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多个字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By不能使用索引来优化排序的情况

• 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引)
  SELECT * FROM t1 ORDER BY key1, key2;
• 在非连续的索引键部分上做 ORDER BY：(key_part1,key_part2建立联合索引;key2建立索引)
  SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
• 同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)
  SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
• 用于搜索记录的索引键和做 ORDER BY 的不是同一个：(key1,key2分别建立索引)
  SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
• 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现order by的优化
  SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特别提示:
1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
2>在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。

数据库索引是什么?

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.

数据库索引的本质是数据结构,这种数据结构能够帮助我们快速的获取数据库中的数据。

数据库为什么要使用索引?

从定义中我们就知道,使用索引就是为了提高效率。下面的例子能让我们直观的看到索引的作用。

我们有下面一张employees表,大概三十万行。

我们想找到first_name为Chirstian的结果,下面比较一下加上索引前后查询所耗费的时间,从结果我们可以看出使用索引可以使查询效率提高20倍。

mysql> select * from employees where first_name = 'Chirstian';
mysql> alter table employees add index first_name (first_name);
mysql> select * from employees where first_name = 'Chirstian';

既然索引的作用这么大,那我们给每列都加上索引不就可以使效率最大化了吗?

答案是否定的,具体原因要我们了解索引的实现原理才能明白。

Mysql索引实现

写在前面

我们了解Mysql索引是什么以及如何实现的目的是为了更高效的使用Mysql,而不是为了去真的实现一个数据库。所以我们这里只介绍Mysql的两个常用引擎Myisam以及Innodb的实现原理。

Myisam和Innodb引擎都是使用B+树作为索引结构,实现上略有差别,所以我们先看看什么是B+树。选择B+树而不是其他数据结构的原因主要是因为数据是保存在硬盘上而不是内存中,所以减少磁盘IO次数才是提升效率的关键。如果对具体原理感兴趣,可以自行google,这对我们如何优化作用有限所以就不展开说了。

B+树索引

一图胜万言,下面就是一个B+树。

节点 与二叉树不同的是,B+中的节点可以有多个元素及多个子节点。在B+索引树中,非叶子节点由索引元素和指向子节点的指针组成,他们的作用就是找到叶子节点,因为只有叶子节点中有最终要找的数据信息。从图中可以看出每个节点中指针的数量比索引元素数量多一个,在叶子节点中,因为没有子节点,多出的那个指针指向下一个叶子节点,这样把所有叶子节点串联起来,这对于范围搜索很有用。在实际应用中一个节点的大小是固定的通常等于磁盘一个页的大小,这样存取一个节点只需要一次磁盘IO,一般节点可存上百个元素,所以索引几百万数据B+树高不会超过3。

搜索 搜索类似于二叉查找树,从根节点开始自顶向下遍历,直到叶子节点,在节点内部典型的是使用二分查找来确定位置。如果是范围查找,对于B+树而言是非常方便的,因为叶子节点是有序且连续的。

插入 首先对树进行搜索找到应该存入的叶子节点。之前我们提到节点的大小是固定的,如果节点内还没放满,则直接插入。如果节点满了,则创建新节点把原节点插入新元素后的一半放入新节点,然后把新节点最小的元素插入父节点。如果父节点满了,进行同样的操作,根节点也不例外。

删除 首先对树进行搜索找到叶子节点并删除元素。当删除后的叶子节点不满一半时:如果兄弟节点过半数则借一个过来,并更新父节点中子节点的分界值;如果等于半数则合并,因为父节点中有两个指针指向这两个兄弟节点,所以需要删除多余的一个来更新父节点,如果删除后父节点不满一半,继续递归以上步骤,直到根节点。

从B+树的特点可以看出,虽然B+树索引能够让我们在有限次磁盘IO次数内快速的查询到数据,但是在插入和删除时也要付出维护B+树的代价,这也是为什么在开始说的不能把每列都加上索引的原因之一。

为了帮助大家更好的理解,这个网站通过动画展示了B+树的查询、插入、删除操作的实现过程。这个视频分3集讲解了B+树和在索引中的实现,不过因为在youtube上,翻墙才能看。

Myisam与Innodb索引的区别

虽然它们都是用B+树实现的索引,但是实现上略有差别,主要有两点。

叶子节点存储数据内容的差别 Myisam存储的是存放真实数据的地址,而Innodb存储的是真实的数据。其实Innodb存储的数据本身就是主键的B+树索引,因为索引内存储着真实的数据。

辅助索引叶子节点存储数据内容的差别 Mysiam存储的还是真实数据的地址,与主索引一样。Innodb存储的是主键的值。

题外话:在一般后端工程师的面试中都会有关于Mysql的问题,其中比较Myisam和Innodb引擎就是常问的一个。网上的答案也都比较陈旧而且只有结论没有验证。比如比较中有一项是否支持Full-text索引,其实Innodb在Mysql5.6中就已经支持了,不过大多数答案都没有更新。还有完全不考虑实际情况就说Myisam在查、Innodb在增删改上更快,我在网上很久也没有找到支撑这些结论的理论或者实验基础。 就我个人而言我更推荐使用Innodb引擎,所以前面我没有深入的比较它们索引的差别。推荐Innodb不单单是因为它在5.5版本之后成为Mysql的默认引擎,还有它拥有支持事务、外键、MVCC、行锁这些更先进的引擎技术。为了类似于不带where的count时Myisam速度更快这样模棱两可的性能差别,而抛弃这些先进的技术,很不值得。而且即使真的像上面所说的Innodb在查时性能稍差于Myisam,我也不认为会因此成为整个架构性能的瓶颈所在,如果真的需要更换引擎来提升性能了,那在架构其他地方的问题可能更大。

Mysql索引类型及优化策略

索引类型

唯一索引(unique index) 看见名字我们就知道,唯一索引列中的值必须是唯一的。不过有一个例外,可以有且可以有多个Null。

普通索引(index) 普通索引可以包括不止一列,一般把多个列组成的普通索引叫组合索引,也有把普通索引看成是只有一列的组合索引的。此外,在索引字符串时,可以只把前几位作为索引来提升效率。

主键(primary key) 主键必须唯一,不同的是不能有Null。而且一个表只能有一个主键。有很多人认为主键是唯一索引的一种,其实是不准确的。主键也可以是组合索引,只要组合的每条结果是唯一的。这在某些场景非常实用,比如一个多对多关系中的枢纽表就非常适合使用复合主键。下图就是一个典型的用户权限功能的实现,用户和角色、角色和权限都是多对多的关系,需要枢纽表来记录他们之间的对应关系,而这些关系都是唯一的,所以这种枢纽表用复合主键非常合适。

优化策略

最左前缀匹配原则

还拿前面的employees表举例,比如我们建立一个(birth_date, first_name, last_name ) 的组合索引。

mysql> alter table employees add index bd_fn_ln (birth_date, first_name, last_name);

下面的查询是用到索引的: mysql> select from employees where birth_date = '1954-05-01' and first_name = 'Chirstian' and last_name = 'Koblick'; mysql> select from employees where birth_date = '1954-05-01' and first_name = 'Chirstian'; mysql> select * from employees where birth_date = '1954-05-01' and last_name = 'Koblick'; 下面是这三个查询explain结果。

1 row in set (0.00 sec)

1 row in set (0.01 sec)

1 row in set (0.00 sec)

虽然结果都是一条,不过前两个查询都用到了联合索引。最后一个只用到了birth_date这一个索引,所以会在birth_date = 1954-05-01 的60结果中遍历last_name来找到等于Koblick的结果。还有, 如果where中都是精确匹配(使用’=’号),那它们的顺序不会影响索引的使用。

而下面这个查询因为没用到组合索引的最左列,所以不会用到索引而是遍历了所有的数据,这就是最左前缀匹配:

1 row in set (0.18 sec)

1 row in set (0.00 sec)

选择区分度高的列作为索引

区分度:count(distinct col)/count(*)。 区分度是一个介于0和1之间的小数,越接近1区分度越高,越适合做索引。 原因很容易理解,比如一个辞典中全是以a和b开头的单词,那么按照首字母简历一个目录(索引),那么目录上一共就两条,每条的范围对应差不多半本辞典,那这个目录(索引)毫无用处。相反,一个班级的学生信息以学号做索引,那么区分度为1,只要找到学号就能直接找到相对应的学生信息,这个索引就非常有效。

不要在比较运算符左侧使用函数或进行计算

在sql语句的比较运算符左侧使用函数或进行计算会使索引失效。

1 row in set (0.11 sec)

1 row in set (0.00 sec)

事例数据库

以上用做事例的employees表来自Mysql官方事例数据库-employees,里面有安装说明,数据库大小合适,非常适合练习。

1、什么是事务

事务是一条或多条数据库操作语句的组合，具备ACID，4个特点。

原子性：要不全部成功，要不全部撤销

隔离性：事务之间相互独立，互不干扰

一致性：数据库正确地改变状态后，数据库的一致性约束没有被破坏

持久性：事务的提交结果，将持久保存在数据库中

2、事务并发会产生什么问题

1）第一类丢失更新：在没有事务隔离的情况下，两个事务都同时更新一行数据，但是第二个事务却中途失败退出， 导致对数据的两个修改都失效了。

例如：

张三的工资为5000，事务A中获取工资为5000，事务B获取工资为5000，汇入100，并提交数据库，工资变为5100，

随后

事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。

2）脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
例如：
　　张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
　　与此同时，
　　事务B正在读取张三的工资，读取到张三的工资为8000。
　　随后，
　　事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
　　最后，
　　事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

3）不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
　　在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B把张三的工资改为8000，并提交了事务。
　　随后，
　　在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

4）第二类丢失更新：不可重复读的特例。
有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。

例如：

在事务A中，读取到张三的存款为5000，操作没有完成，事务还没提交。
　　与此同时，
　　事务B，存储1000，把张三的存款改为6000，并提交了事务。
　　随后，
　　在事务A中，存储500，把张三的存款改为5500，并提交了事务，这样事务A的更新覆盖了事务B的更新。

5）幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
　　目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
　　此时，
　　事务B插入一条工资也为5000的记录。
　　这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

提醒：
不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样

3、事务隔离级别，解决什么并发问题，以及存在什么并发问题

（1）READ_UNCOMMITTED
　　这是事务最低的隔离级别，它充许另外一个事务可以看到这个事务未提交的数据。
　　解决第一类丢失更新的问题，但是会出现脏读、不可重复读、第二类丢失更新的问题，幻读 。
（2）READ_COMMITTED
　　保证一个事务修改的数据提交后才能被另外一个事务读取，即另外一个事务不能读取该事务未提交的数据。
　　解决第一类丢失更新和脏读的问题，但会出现不可重复读、第二类丢失更新的问题，幻读问题
（3）REPEATABLE_READ
　　保证一个事务相同条件下前后两次获取的数据是一致的

解决第一类丢失更新，脏读、不可重复读、第二类丢失更新的问题，但会出幻读。
（4）SERIALIZABLE
　　事务被处理为顺序执行。
　　解决所有问题

提醒：

Mysql默认的事务隔离级别为repeatable_read

4、InnoDB引擎的锁机制

（之所以以InnoDB为主介绍锁，是因为InnoDB支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁）

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

说明：

1）共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预。

2）对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

3）InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！。

http://www.cnblogs.com/fidelQuan/p/4549068.html