一、索引
1.什么是索引
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。
2.索引的种类
1)BTREE:B+树索引（Btree，B+tress，B*tree）
2)HASH：HASH索引 (memery搜索引擎支持)
3)FULLTEXT：全文索引 （只可以用在myisam搜索引擎，一般纯文本数据格式才会使用）
4)RTREE：R树索引 （仅支持geometry数据类型，经纬度）
二分法：
5000
1-2500 2501-5000
2501-3751 3752-5000
1）Btree索引介绍
2）B+tree索引介绍
3）B*tree索引介绍
3.索引根据算法分类
索引是建立在表的字段上面的
当where后面接的条件里面有索引会加快查询速度
1）主键索引（聚集索引）
1.建表时直接添加主键索引
mysql> create table student(id int not null primary key comment '学号');
mysql> create table student(id int not null comment '学号', primary key(id));
2.已经建好的表，添加主键索引
mysql> alter table student2 add primary key pri_id(id);
mysql> alter table student2 add primary key suibianxieshenme(id);
mysql> show index from student2;
2）唯一键索引
1.建表时直接添加唯一键索引
mysql> create table student(id int not null unique key comment '学号');
2.添加唯一键索引
mysql> alter table student add unique key uni_id(id);
mysql> create unique key uni_id on student(id);
扩展
mysql> select count(name) from city;
mysql> select distinct(name) from city;
mysql> alter table country add unique key uni_name(name);
3）普通索引（辅助索引）
mysql> alter table city add index idx_name(name);
mysql> create index idx_dis on city(District);
4）全文索引
mysql> create table pull(id int, bookname varchar(10), content text, fulltext(content));
mysql> select * from pull where match(content) against('查找的字符串');
5）查看索引
1.方式一：
mysql> show index from city;
2.方式二：
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | MUL | | |
| CountryCode | char(3) | NO | UNI | | |
| District | char(20) | NO | MUL | | |
| Population | int(11) | NO | MUL | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
PRI：主键索引
UNI：唯一键索引
MUL：普通索引
6）删除索引
mysql> alter table city drop index idx_dis;
4.索引根据创建方式分类
1.在创建索引的时候，会把创建索引那一列的数据按照Btree的方式进行排序；
2.创建索引，会占用磁盘空间，所以不要每一列都创建索引；
3.在同一列上，避免创建多个索引；
4.避免在数据很长的列上创建索引，如果想创建索引，就创建前缀索引；
1）前缀索引
mysql> alter table student add index idx_name(sname(4));
2）联合索引
mysql> create database xiangqin;
mysql> use xiangqin;
mysql> create table people(id int, name varchar(20), age tinyint, money int, gender enum('m','f'), hight int, weight int, figure varchar(10), looks varchar(10));
mysql> alter table people add index idx_all(gender,looks,age,money);
mysql> show index from people;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people | 1 | idx_all | 1 | gender | A | 10 | NULL | NULL | YES | BTREE | | |
| people | 1 | idx_all | 2 | looks | A | 10 | NULL | NULL | YES | BTREE | | |
| people | 1 | idx_all | 3 | age | A | 10 | NULL | NULL | YES | BTREE | | |
| people | 1 | idx_all | 4 | money | A | 10 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
二、explain 使用
1.explain用法
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
mysql> explain select * from city where countrycode='CHN' or countrycode='USA';
mysql> explain select * from city where countrycode in ('CHN','USA');
2.explain 注解
id 执行顺序
table 查询的表
type 查询使用的类型
possible_keys 可能使用到的索引
key 真的使用到的索引
key_len 索引长度，可以使用前缀索引控制
ref 查询速度的注释
rows 查询数据的数量，有的时候会比真实数据偏高
Extra
Using temporary 使用grouop by的时候出现
Using filesort 使用order by 的时候出现
Using join buffer 使用join on的时候缓存
3.查询数据的种类
1）全表扫描
在explain语句结果中，type为ALL
1）查询所有数据的时候
mysql> explain select * from city;
2）不走索引的时候
2.1 当查询条件没有索引的时候
mysql> explain select * from city where District='shanghai';
2.2 索引创建有问题
2.3 语句有问题
2）索引扫描
1）index 全索引扫描
mysql> explain select population from city;
2）range 范围查询 （sql与只要达到range级别就ok了）
mysql> explain select * from city where countrycode='CHN' or countrycode='USA';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where population > 30000000;
3）ref 精确查找
mysql> explain select * from city where population = 30000000;
4）eq_ref 类似于ref，使用join on的时候
mysql> explain select * from city join country on city.countrycode=country.code where city.population < 100;
5）const 查询的条件的列是唯一索引或主键索引
mysql> explain select * from country where name='Aruba'; 唯一键
mysql> explain select * from country where code='CHN'; 主键
6）system 跟const同级别
7）null 执行过程中不访问表或者索引
mysql> explain select min(population) from city;
三、建立索引
1.建立索引的原则
mysql> select count(distinct(id)) from xuesheng;
+---------------------+
| count(distinct(id)) |
+---------------------+
| 14 |
+---------------------+
1 row in set (0.00 sec)
mysql> select count(id) from xuesheng;
+-----------+
| count(id) |
+-----------+
| 14 |
+-----------+
1 row in set (0.00 sec)
2.什么时候不走索引？
1）没有查询条件，或者查询条件没有建索引
mysql> explain select * from city;
mysql> explain select * from city where 1=1;
mysql> explain select * from city where District='shanghai';
2）查询的结果集是原表中的大部分数据，25%以上
mysql> explain select * from city where population < '3000000';
mysql> select * from city where population < '100000';
mysql> explain select * from city where population < '1000000000' limit 60;
mysql> explain select * from city where population < '1000000000' limit 60,60;
3）索引本身失效
反复的修改，添加索引，索引被玩坏了，每一次添加索引，都会对数据进行排序
4）查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
mysql> explain select * from city where id-1=1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | city | ALL | NULL | NULL | NULL | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from city where id=1+1;
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | city | const | PRIMARY,index_id | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
5）隐式转换，会导致索引失效
mysql> create table test(id int,name varchar(10),phonenum varchar(20));
mysql> alter table test unique key idx_num(phonenum);
mysql> insert into test values(1,'jc','110'),(2,'hj','119'),(3,'jm','120');
mysql> insert into test values(4,'cdh',114),(5,'USAjc',999),(6,'hc',12306);
mysql> explain select * from test where phonenum=110;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | idx_num | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where phonenum='110';
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | test | const | idx_num | idx_num | 23 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
6）<> 和 not in 和 or 也不走索引
mysql> explain select * from test where phonenum <> '110';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | idx_num | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where phonenum not in ('110','120','114','999','12306');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | idx_num | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where phonenum='110' or phonenum='120';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | idx_num | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test where phonenum='110' union all select * from test where phonenum='120';
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| 1 | PRIMARY | test | const | idx_num | idx_num | 23 | const | 1 | NULL |
| 2 | UNION | test | const | idx_num | idx_num | 23 | const | 1 | NULL |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows in set (0.00 sec)
7）like模糊查询 % 位置决定走不走索引
mysql> explain select * from city where countrycode like '%HN';
8）联合索引查询的条件不是按照顺序排列的，或者条件没有联合索引的第一个列
CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
函数总结：
password()
database()
now()
count()
distinct()
max()
min()
sum()
avg()