Mysql 索引 及其高效使用索引

Mysql 数据库索引原理及其高效使用索引


首先要理解什么是索引：索引是对数据库表中一个或多个列（例如，employee 表的姓名 (name) 列）的值进行排序的结构，同时索引对查询的速度有着至关重要的影响，理解索引同时也是针对数据库性能调优的起点




1.  选择索引的数据类型 
Mysql 支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常遵循以下一些指导原则：
(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。




1.1 选择标识符
选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。
(1)    整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。


(2)    字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）






2、索引入门


对于任何DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。




备注  ：DBMS   数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS 
常见DBMS :  Oracle、Sybase、Informix、Microsoft SQL Server、Microsoft Access、Visual FoxPro等



2.1、索引的类型
索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。






以上基础知识 还有很多 就不一一罗列了。


高级知识 及其特殊 小用法 实现高效SQL


用过Oracle 童靴们 都知道可以强制使用索引来优化种种没有走索引SQL ，同时Mysql 也支持，分为两种正反面 强制使用索引 与不使用索引。


在Mysql 表中的如果是单主键的话，系统就会默认索引为主键 索引。 

如果存在复合主键的情况下遵循的是 左对齐的原则如同 我上面讲诉 查询 左表会优先执行 索引 右表单独使用会失效，前提条件建立在右表列中等于左表的等值  






当前你在运行一顿SQL当中发现整体的逻辑处理无问题的情况下 ，SQL的执行结果或者EXPLAIN 还是差强人意 此时不妨考虑下 是否问题出在索引上面：




两种概念名词 ：
1.强制索引
     强制索引顾名思义 就是在执行过程 强制当前SQL状态使用该指定索引
 
2.禁止使用索引
与上者相反 原理 ，禁止使用该指定索引
                   
                   
举例 ：
（1）mysql强制使用索引:force index(索引名或者主键PRI)
select * from table force index(PRI) limit 2;(强制使用主键)


select * from table force index(ziduan1_index) limit 2;(强制使用索引"ziduan1_index")


select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引"PRI和ziduan1_index")


（2）mysql禁止某个索引：ignore index(索引名或者主键PRI)
select * from table ignore index(PRI) limit 2;(禁止使用主键)


select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引"ziduan1_index")


select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")

附图一张项目中简单应用到 use 关键字 指定索引：



备注：
(1)宽窄索引

用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引); 复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引; 
同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为   1-2  列的索引,宽索引也就是索引列  超过2列  的索引; 
设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效



(2)使用复合索引（组合索引）注意事项


　 2.1 对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;
　　select * from table1 where col1=A AND col2=B AND col3=D
　　如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引


　　2.2 何时是用复合索引
　　根据where条件建索引是极其重要的一个原则;
　　注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中




2.3 复合索引会替代单一索引么
　　如果索引满足窄索引的情况下可以建立复合索引,这样可以节约空间和时间