一 建立索引的原则
1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
2．为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
3．为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
4．限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
6．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。
前缀索引能大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性（索引选择性——不重复的索引值和数据表记录总数的比值）；
索引前缀长度的选择——计算法。（city 与 city前3个字符）
例如：SELECT COUNT(DISTINCT city)/COUNT(*) AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel2 FROM XXX
如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱，还需要做进一步判断。
缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；
有时候也可以使后缀索引——可将对应列的字符串反序存储，并创建前缀索引。
7．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
8 . 最左前缀匹配原则，非常重要的原则。
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
9 .=和in可以乱序。
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
10 . 尽量选择区分度高的列作为索引。
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录
11 .索引列不能参与计算，保持列“干净”。
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
12 .尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
14 选择合适的索引顺序
正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；
索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求；
索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）；
15.多列索引。例如：key(col1, col2, col3)；
MySQL5.0之后的版本引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位表中的行；
索引合并策略有时候是一种优化后的结果，但实际上更说明表上的索引建得很糟糕。
当出现服务器对多个索引做相交操作时（多个AND），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引;
二 索引失效
- .独立的列：如果查询中的列不是独立的，则MySQL就不会使用索引。例：SELECT actor_id FROM actor WHERE actor_id + 1 = 5
- 字符串不加单引号
- 将要使用的索引列不是复合索引列表中的第一部分，则不会使用索引
-
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null -
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0 -
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。
-
应尽量避免在 where 子句中使用 or 来连接条件 (用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到)，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20 -
可以这样查询：
select id from t where num=10
union all
select id from t where num=20 -
in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：
select id from t where num in(1,2,3) -
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3 -
尽量避免在索引过的字符数据中，使用非打头字母%搜索。这也使得引擎无法利用索引。
见如下例子：
SELECT FROM T1 WHERE NAME LIKE ‘%L%’
SELECT FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’ -
即使NAME字段建有索引，前两个查询依然无法利用索引完成加快操作，引擎不得不对全表所有数据逐条操作来完成任务。而第三个查询能够使用索引来加快操作
-
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
-
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
-
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
三 理解sql执行过程
Step1:客户端向Mysql服务器发送SQL语句。
Step2:服务器收到后先查询”查询缓存“,如果命中，从缓存中直接返回sql执行的结果集。否则，进入Step3。
Step3:服务器解析、预处理、优化sql执行计划，然后将处理好的sql放入查询的执行计划中。
Step4:执行引擎通过调用”存储引擎”(如，innodb、myisam等)提供的API去执行这个计划。
Step5：服务器返回结果给客户端
四 基本原则
最左前缀匹配原则
对于KEY last_name (last_name,first_name,dob),where 后的谓词必须包含last_name（组合索引的最左列），否则无法使用这个索引.
示例： select ... from people where .... last_name="..."....
这个语句将使用 last_name (last_name,first_name,dob)索引。
示例：
SELECT ... FROM people WHERE last_name="..." AND dob="..."
这个语句只使用了last_name (last_name,first_name,dob)的last_name列，因为缺少first_name，所以后续dob列也无法从索引中搜索。
示例：
SELECT ... FROM people WHERE last_name > "..." AND first_name= "..." AND dob= "..."这个语句只使用了last_name (last_name,first_name,dob)的last_name列，因为last_name 使用了范围查询，所以后续索引的两个列无法使用。
什么事范围查询：
使用了范围查询的语句。范围查询指使用 “>” 、”<”、“between” “like”的查询。注意“in”不算范围查询，属于多值查询条件。
转载：
443
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
