1、建立索引的原则

  1. 为了使索引的使用效率更高, 在创建索引时, 必须考虑在哪些字段上创建索引和创建什么类型的索引

2、DBA运维范畴

2.1、建表时一定要有主键

唯一性索引的值是唯一的, 可以更快速的通过该索引来确定某条记录
例如: 学生表中学号是具有唯一性的字段, 为该字段建立唯一性索引可以很快的确定某个学生的信息
如果使用姓名的话, 可能存在同名现象, 从而降低查询速度

优化方案:
1. 如果非得使用重复值较多的列作为查询条件 (例如: 男女), 可以将表逻辑拆分
2. 可以将此列和其他的查询类, 做联和索引

#查询重复率
select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

2.2、为经常需要where 、order by、goup by、join on 等操作的字段

为这些字段创建索引 (联合索引), 因为排序操作会浪费很多时间

2.3、尽量使用前缀来索引

如果索引字段的值很长, 最好使用值的前缀来索引

2.4、限制索引的数目

索引的数目不是越多越好
可能会产生的问题:
1. 每个索引都需要占用磁盘空间, 索引越多, 需要的磁盘空间就越大
2. 修改表时, 对索引的重构和更新很麻烦, 越多的索引, 会使更新表变得很浪费时间
3. 优化器的负担会很重, 有可能会影响到优化器的选择

percona-toolkit中有个工具, 专门分析索引是否有用, sqlyong、navicat等也有此功能

2.5、删除不再使用或者很少使用的索引

表中的数据被大量更新, 或者数据的使用方式被改变后, 原有的一些索引可能不再需要 
数据库管理员应当定期找出这些索引, 将它们删除, 从而减少索引对更新操作的影响

2.6、大表加索引、要在业务不繁忙期间操作

推荐使用 pt工具

2.7、尽量少在经常更新值的列上建索引

总结: 建索引原则

1. 必须要有主键,如果没有可以做为主键条件的列,创建无关列
2. 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
3. 最好使用唯一值多的列作为索引, 如果索引列重复值较多, 可以考虑使用联合索引
4. 列值长度较长的索引列, 我们建议使用前缀索引
5. 降低索引条目, 一方面不要创建没用索引, 不常使用的索引清理, percona toolkit(xxxxx)
6. 索引维护要避开业务繁忙期, 建议用pt-osc

3、开发范畴

不走索引的情况

3.1、没有查询条件、或者查询条件没有建立索引

select * from city; 
select * from city where 1=1;

3.2、查询结果集是原表中的大部分数据、应该是15-25%以上

t100w表 num字段有索引   
desc select * from test.t100w where id > 1;
type: ALL, 全表扫描

产生原因:
查询的结果集, 超过了总数行数25%, 优化器觉得就没有必要走索引了
MySQL的预读功能有关

解决方案:
可以通过精确查找范围, 达到优化的效果
1000000
>500000 and < 600000

3.3、索引本身失效、统计信息不真实 (过旧)

索引有自我维护的能力
对于表内容变化比较频繁的情况下, 有可能会出现索引失效
一般是删除重建

现象:
有一条select语句平常查询时很快, 突然有一天很慢, 会是什么原因
select查询操作, 索引失效, 统计数据不真实

原因: 这两张存储统计信息的表更新不及时, 因为是异步更新的
统计信息存储在.idb文件中, 异步更新, 一般会攒到一个更新范围, 一起更新
innodb_index_stats
innodb_table_stats 

解决方案:
1. 暴力手段
删除手段, 重建索引

2. 手工刷新统计信息 (大批量更新操作时, 建议手工执行一下)
mysql> analyze table world.city;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| world.city | analyze | status   | OK       |
+------------+---------+----------+----------+

#帮助信息
help
help contents;
help Table Maintenance;
help ANALYZE TABLE;

3.4、查询条件使用函数在索引列上, 或者对索引列进行运算, 运算包括 (+ , - , * , / , != 等)

例子:
错误的例子: select * from test where id-1=9;
正确的例子: select * from test where id=10;
算术运算
函数运算
子查询

3.5、隐式转换导致索引失效、这一点应当引起重视、也是开发中经常会犯的错误

这样会导致索引失效, 错误的例子:
#定义的字符串类型, 查询时用数字类型, 数据库会隐式的完成数据类型转换, 使用全表扫描: ALL
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| telnum | varchar(20) | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                   |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref   | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab   | ALL  | inx_tel       | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab   | ref  | inx_tel       | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

3.6、<>、not in、不走索引 (辅助索引)

EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id   | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走, 也有可能不走, 和结果集有关, 尽量结合业务添加limit
or 或 in尽量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成:
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

3.7、like “%_” 百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'; 不走索引
%linux%类的搜索需求, 可以使用elasticsearch 或者 mongodb 专门做搜索服务的数据库产品