[world]>desc select * from cityG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city           #查询的表明
   partitions: NULL         
         type: ALL
possible_keys: NULL           #可能会走的索引
          key: NULL           #真正走的索引
      key_len: NULL
          ref: NULL           #索引类型
         rows: 4188
     filtered: 100.00
        Extra: NULL           #额外信息
1 row in set, 1 warning (0.00 sec)

从左到右性能依次变好.
ALL  :  
全表扫描,不走索引
例子:
1. 查询条件列,没有索引
SELECT * FROM t_100w WHERE k2='780P';  
[oldboy]>desc select * from t_100w where k2='780p';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------
| id | select_type|table|partitions|type|possible_keys|key|key_len| ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------
|  1 | SIMPLE     |t_100w|NULL     | ALL  | NULL      |NULL|NULL  | NULL | 997589 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------
1 row in set, 1 warning (0.00 sec)


2. 查询条件出现以下语句(使用辅助索引列)
USE world 
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';

注意:对于聚集索引列,使用以上语句,依然会走索引
DESC SELECT * FROM city WHERE id <> 10;
[oldboy]>desc select * from world.city where id <> '10';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id |select_type|table|partitions |type| possible_keys | key  | key_len | ref | rows |filtered | Extra   |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------
|  1 | SIMPLE    | city| NULL      |range| PRIMARY      |PRIMARY| 4      | NULL |2103 |100.00 |Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------
1 row in set, 1 warning (0.00 sec)


INDEX  :
全索引扫描
1. 查询需要获取整个索引列的值时:
DESC  SELECT countrycode  FROM city;
[world]>desc select countrycode from city;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+-----
| id |select_type|table|partitions|type|possible_keys|key      | key_len | ref  |rows| filtered | Extra    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+-----
|  1 | SIMPLE    | city| NULL     |index|NULL      | CountryCode| 3    | NULL | 4188 | 100.00| Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+-----
1 row in set, 1 warning (0.00 sec)


2. 联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c)  ---> a  ab  abc

SELECT * FROM t1 WHERE b 
SELECT * FROM t1 WHERE c    

RANGE :
索引范围扫描 
辅助索引> < >= <= LIKE IN OR 
主键 <>  NOT IN

例子:
1. 
DESC SELECT * FROM city WHERE id<5;
2. 
DESC SELECT * FROM city WHERE countrycode LIKE 'CH%';
3. 
DESC SELECT * FROM city WHERE countrycode IN ('CHN','USA');
[world]>desc select * from world.city where id <> '10';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------
| id | select_type|table|partitions|type |possible_keys| key     |key_len|ref|rows| filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------
|  1 | SIMPLE     | city| NULL     |range|PRIMARY      | PRIMARY | 4     |NULL|2103| 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+---------
1 row in set, 1 warning (0.00 sec)

注意: 
1和2例子中,可以享受到B+树的优势,但是3例子中是不能享受的.
所以,我们可以将3号列子改写:

DESC SELECT * FROM city WHERE countrycode='CHN'
UNION ALL 
SELECT * FROM city WHERE countrycode='USA';

ref: 
非唯一性索引,等值查询
DESC SELECT * FROM city WHERE countrycode='CHN';
[world]>DESC SELECT * FROM city WHERE countrycode='CHN';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+-----
| id |select_type|table|partitions|type|possible_keys| key       |key_len|ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+-----
|  1 | SIMPLE    |city | NULL     | ref|CountryCode  |CountryCode| 3     |const|  363 |  100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+-----
1 row in set, 1 warning (0.00 sec)


eq_ref: 
在多表连接时,连接条件使用了唯一索引(uk  pK)
DESC SELECT b.name,a.name FROM city AS a 
JOIN country AS b 
ON a.countrycode=b.code 
WHERE a.population <100;
DESC country

[world]>DESC SELECT b.name,a.name FROM city AS a 
    -> JOIN country AS b 
    -> ON a.countrycode=b.code 
    -> WHERE a.population <100;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------
| id |select_type|table|partitions|type  |possible_keys|key |key_len|ref       |rows|filtered| Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------
|  1 | SIMPLE    | a   | NULL     | ALL  |CountryCode  |NULL|NULL   | NULL      4188 | 33.33 | Using where |
|  1 | SIMPLE    | b   | NULL     | eq_ref | PRIMARY   |PRIMARY| 3 |world.a.CountryCode|  1 |100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------
2 rows in set, 1 warning (0.00 sec

system,const :
唯一索引的等值查询
DESC SELECT * FROM city WHERE id=10;
[world]>DESC SELECT * FROM city WHERE id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|id|select_type|table|partitions|type | possible_keys | key     | key_len | ref   | rows| filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+--------
|1 |SIMPLE     |city | NULL     |const| PRIMARY       | PRIMARY | 4       | const |    1|   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+--------
1 row in set, 1 warning (0.00 sec)

extra: 
filesort ,文件排序.
当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序；如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序，使用临时表）。

SHOW INDEX FROM city;
ALTER TABLE city ADD INDEX CountryCode(CountryCode);
ALTER TABLE city DROP INDEX idx_c_p;

DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

ALTER TABLE city ADD INDEX idx_(population);
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 
ALTER TABLE city ADD INDEX idx_c_p(countrycode,population);
ALTER TABLE city DROP INDEX idx_;
ALTER TABLE city DROP INDEX CountryCode;
DESC SELECT * FROM city WHERE countrycode='CHN'  ORDER BY population 

结论: 
1.当我们看到执行计划extra位置出现filesort,说明由文件排序出现
2.观察需要排序(ORDER BY,GROUP BY ,DISTINCT )的条件,有没有索引
3. 根据子句的执行顺序,去创建联合索引

优化前:
[root@vm01 ~]# mysqlslap --defaults-file=/etc/my.cnf 
> --concurrency=100 --iterations=1 --create-schema='oldboy' 
> --query="select * from oldboy.t_100w where k2='fgcd'" engine=innodb 
> --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 518.454 seconds
    Minimum number of seconds to run all queries: 518.454 seconds
    Maximum number of seconds to run all queries: 518.454 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20
    
创建k2的辅助索引
alter talbe t_100w add index idx_k2(k2);
    
    
    优化后:
[root@vm01 ~]# mysqlslap --defaults-file=/etc/my.cnf 
> --concurrency=100 --iterations=1 --create-schema='oldboy' 
> --query="select * from oldboy.t_100w where k2='fgcd'" engine=innodb 
> --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
    Running for engine rbose
    Average number of seconds to run all queries: 1.584 seconds
    Minimum number of seconds to run all queries: 1.584 seconds
    Maximum number of seconds to run all queries: 1.584 seconds
    Number of clients running queries: 100
    Average number of queries per client: 20

联合索引:
1. SELECT * FROM t1  WHERE a=    b=   
我们建立联合索引时:
ALTER TABLE t1 ADD INDEX idx_a_b(a,b);  
ALTER TABLE t1 ADD INDEX idx_b_a(b,a);  
以上的查询不考虑索引的顺序,优化器会自动调整where的条件顺序
注意: 索引,我们在这种情况下建索引时,需要考虑哪个列的唯一值更多,哪个放在索引左边.

2.  如果出现where 条件中出现不等值查询条件
DESC  SELECT * FROM t_100w WHERE num <1000 AND k2='DEEF';
我们建索引时:
ALTER TABLE t_100w ADD INDEX idx_2_n(k2,num);
语句书写时
DESC  SELECT * FROM t_100w WHERE  k2='DEEF'  AND  num <1000 ;
3. 如果查询中出现多子句
我们要按照子句的执行顺序进行建立索引.

题目意思:  我们公司业务慢,请你从数据库的角度分析原因
1.mysql出现性能问题,我总结有两种情况:
（1）应急性的慢：突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1.show processlist;  获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
（2）一段时间慢(持续性的):
(1)记录慢日志slowlog,分析slowlog
(2)explain 分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。那么索引设计原则又是怎样的?