Mysql优化-mysql的索引-索引列选择
说明
整理一下如何正确的使用索引
正确使用索引
并不是说我们创建了索引就一定会加快查询速度，若想利用索引达到预想的提高查询速度的效果，我们在添加索引时，必须遵循以下问题。
范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!=、between...and...、like、大于号、小于号
等于或者大于小于
mysql> select count(*) from s1 where id =1000;
+----------+
| count(*) | #明确指定找某一个，此时查询会很快
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from s1 where id>1000;
+----------+
| count(*) | # >条件时虽然也会用到索引树，但是没有明确哪个id,所以相当于
+----------+ # 指定了一个大的范围，将会从1001、1002依次往后查，跟查全表差不多
| 5073571 |
+----------+
1 row in set (2.73 sec)
mysql> select count(*) from s1 where id>1000 and id <1500;
+----------+
| count(*) |
+----------+ # 如果范围很小的话，查询速度也会很快
| 7984 |
+----------+
1 row in set (0.01 sec)
不等于！=
mysql> select count(*) from s1 where id=1000;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from s1 where id!=1000;
+----------+
| count(*) |
+----------+#不等于是一个很大的范围，所以也会很慢
| 5400678 |
+----------+
1 row in set (3.81 sec)
between ...and...
mysql> select count(*) from s1 where id between 1000 and 3000;
+----------+
| count(*) |
+----------+ #范围小时会很快
| 32016 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from s1 where id between 1000 and 300000;
+----------+
| count(*) |
+----------+ #范围大是会很慢
| 3158716 |
+----------+
1 row in set (2.07 sec)
like
mysql> select count(*) from s1 where email like 'zxliucn@163.com';
+----------+
| count(*) |
+----------+ #当没有索引时，直接查询会很慢
| 0 |
+----------+
1 row in set (6.46 sec)
mysql> create index d on s1(email);
Query OK, 0 rows affected (43.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(*) from s1 where email like 'zxliucn@163.com';
+----------+
| count(*) |
+----------+ #创建索引后，速度明显提升
| 0 |
+----------+
1 row in set (0.36 sec)
mysql> select count(*) from s1 where email like 'zxliucn@163%';
+----------+
| count(*) | ps:like中的'%'表示任意多个字符
+----------+ #当%在后面时查询依旧很快
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from s1 where email like '%zxliucn@163';
+----------+
| count(*) |
+----------+ #当%在开头时,会全表扫描所以很慢。
| 0 |
+----------+
1 row in set (4.08 sec)
1、like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引，不走全表扫描。
2、like keyword% 索引有效。
3、like %keyword% 索引失效，也无法使用反向索引
=和in
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
区分度高的列做索引
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
我们编写存储过程为表s1批量添加记录，name字段的值均为duoduo，也就是说name这个字段的区分度很低（gender字段也是一样的，我们稍后再搭理它）
回忆b+树的结构，查询的速度与树的高度成反比，要想将树的高低控制的很低，需要保证：在某一层内数据项均是按照从左到右，从小到大的顺序依次排开，即左1<左2<左3<...
而对于区分度低的字段，无法找到大小关系，因为值都是相等的，毫无疑问，还想要用b+树存放这些等值的数据，只能增加树的高度，字段的区分度越低，则树的高度越高。极端的情况，索引字段的值都一样，那么b+树几乎成了一根棍。本例中就是这种极端的情况，name字段所有的值均为'duoduo'
#现在我们得出一个结论：为区分度低的字段建立索引，索引树的高度会很高，然而这具体会带来什么影响呢？？？
#1：如果条件是name='xxxx',那么肯定是可以第一时间判断出'xxxx'是不在索引树中的（因为树中所有的值均为'duoduo'），所以查询速度很快
#2：如果条件正好是name='duoduo',查询时，我们永远无法从树的某个位置得到一个明确的范围，只能往下找，往下找，往下找。。。这与全表扫描的IO次数没有多大区别，所以速度很慢
索引列不能参与计算
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29')
其他注意事项
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
总结
以上就是对Mysql日常使用索引的相关介绍，由于博主发现这一章还有很多东西要讲，所以把多列索引、联合索引、覆盖索引放到另外一篇文章，大家有什么问题可以给博主留言。
结尾
腹有诗书气自华，最是书香能致远。