索引优缺点
为什么要创建索引呢（优点）？
创建索引可以大大提高系统的性能。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
建立方向索引的不利因素（缺点）
也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
什么样的字段适合创建索引
索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。
- 一般来说，应该在这些列上创建索引，例如：
- 在经常需要搜索的列上，可以加快搜索的速度；
- 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
建立索引，一般按照select的where条件来建立，比如： select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上简历索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。
不走索引的情况
- 没有查询条件没where 后面的内容 查询条件没索引.
- 查询条件没引导列. 没有有索引的列.
- 查询数量是超过表的一部分,mysql30%,oracle 20%.
- 索引失效,索引插入过多可能发生意外失效.
- 查询条件使用函数在索隐列上面.计算等.
- 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等). 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;
- 对小表查询.
- 统计数据不真实.
- CBO计算走索引花费过大的情况.
- 查询条件字符串和数字等的隐式转换.
- 使用不等于（!= 或者<>）的时候MySQL 无法使用索引.
- %% 两个百分号不走索引,开始的结尾的百分号走索引.
- not in 、not exist 、or 、 in 尽量转换为union.
- time 和date 时间格式不一致.
- B-tree索引is null不会走,is not null会走,位图索引 is null,is not null 都会走.
- 索隐列避免空列,一般选非空的列.
- Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引.
- 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引.
- MyISAM 存储引擎索引键长度总和不能超过1000 字节.
- BLOB 和TEXT 类型的列只能创建前缀索引.
高性能前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值（也称为基数，cardinality)和数据表的记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列。换句话说，前缀的”基数“应该接近于完整的列的”基数“。
为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。下面的示例是mysql官方提供的示例数据库
下载地址如下：
http://downloads.mysql.com/docs/sakila-db.zip
在示例数据库sakila中并没有合适的例子，所以从表city中生成一个示例表，这样就有足够数据进行演示：
1.解压下载的
sakila-db.zip文件
2.使用source命令以及sakila-schema.sql和sakila-data.sql文件来初始化sakila库以及相关表格
Copy
mysql> select database();
+------------+
| database() |
+------------+
| sakila |
+------------+
1 row in set (0.00 sec)
mysql> create table city_demo (city varchar(50) not null);
mysql> insert into city_demo (city) select city from city; --执行两次
Query OK, 600 rows affected (0.02 sec)
Records: 600 Duplicates: 0 Warnings: 0
mysql> update city_demo set city = ( select city from city order by rand() limit 1);
Query OK, 1198 rows affected (0.42 sec)
Rows matched: 1200 Changed: 1198 Warnings: 0
注：因为上述sql语句使用了rand函数，所以每个人的执行结果可以都不一样！首先找到最常见的城市列表：
Copy
mysql
>
select count(*) as cnt,city from city_demo group by city order by cnt desc limit 10;
+-----+---------------+
| cnt | city |
+-----+---------------+
| 8 | Dongying |
| 7 | Omdurman |
| 6 | Etawah |
| 6 | Okara |
| 6 | Tsuyama |
| 6 | Brindisi |
| 6 | Kuwana |
| 6 | Grand Prairie |
| 5 | Fuyu |
| 5 | Siegen |
+-----+---------------+
10 rows in set (0.00 sec)现在查找到频繁出现的城市前缀。先从3个前缀字母开始，然后4个，5个，6个：
Copy
mysql
>
select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
| 23 | San |
| 15 | Hal |
| 14 | Cha |
| 14 | al- |
| 12 | Bat |
| 12 | Kor |
| 11 | Don |
| 11 | Shi |
| 10 | La |
| 10 | El |
+-----+------+
10 rows in set (0.00 sec)Copy
mysql
>
select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
| 14 | San |
| 8 | Dong |
| 7 | Iwak |
| 7 | al-Q |
| 7 | Omdu |
| 6 | Kuwa |
| 6 | Tsuy |
| 6 | Brin |
| 6 | Etaw |
| 6 | Okar |
+-----+------+
10 rows in set (0.00 sec)
可以看到3字节检索到的结果与全文检索相差很大，继续增加到4个字节
mysql
>
select count(*) as cnt,left(city,5) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+-------+
| cnt | pref |
+-----+-------+
| 8 | Dongy |
| 7 | al-Qa |
| 7 | Omdur |
| 6 | Okara |
| 6 | Valle |
| 6 | Grand |
| 6 | Tsuya |
| 6 | Etawa |
| 6 | South |
| 6 | Kuwan |
+-----+-------+
10 rows in set (0.00 sec)Copy
mysql
>
select count(*) as cnt,left(city,6) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+--------+
| cnt | pref |
+-----+--------+
| 8 | Dongyi |
| 7 | Omdurm |
| 6 | Okara |
| 6 | Tsuyam |
| 6 | Valle |
| 6 | Grand |
| 6 | Etawah |
| 6 | Brindi |
| 6 | Kuwana |
| 5 | Haldia |
+-----+--------+
10 rows in set (0.01 sec)通过上面改变不同前缀长度发现，当前缀长度为6时，这个前缀的选择性就接近完整咧的选择性了。
当然还有另外更方便的方法，那就是计算完整列的选择性，并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：
Copy
mysql
>
select count(distinct city)/count(*) from city_demo;
+-------------------------------+
| count(distinct city)/count(*) |
+-------------------------------+
| 0.4333 |
+-------------------------------+
1 row in set (0.00 sec)可以在一个查询中针对不同前缀长度的选择性进行计算，这对于大表非常有用，下面给出如何在同一个查询中计算不同前缀长度的选择性：
Copy
mysql
>
select count(distinct left(city,3))/count(*) as sel3,count(distinct left(city,4))/count(*) as sel4,count(distinct left(city,5))/count(*) as sel5, count(distinct left(city,6))/count(*) as sel6 from city_demo;
+--------+--------+--------+--------+
| sel3 | sel4 | sel5 | sel6 |
+--------+--------+--------+--------+
| 0.3408 | 0.4100 | 0.4225 | 0.4300 |
+--------+--------+--------+--------+
1 row in set (0.00 sec)可以看见当索引前缀为6时的基数是0.4300，已经接近完整列选择性0.4333。
下面根据找到的索引前缀长度创建前缀索引：
Copy
mysql> alter table city_demo add key (city(6));
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0Copy
mysql
>
explain select * from city_demo where city like 'Jin%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city_demo
partitions: NULL
type: range
possible_keys: city
key: city
key_len: 8
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)可以看见正确使用刚创建的索引。
优点：前缀索引是一种能使索引更小，更快的有效办法
缺点：mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
