《一起学mysql》4
索引的使用
索引太少返回结果很慢，但是索引太多，又会占用空间。每次插入新记录时，索引都会针对变化重新排序
什么时候使用索引
1.where 从句中用到的字段
select * from tb where f1 = 'xx' ,如果f1 进行了索引，那么这条sql 语句的销毁就会提高
2 max(),min()
select max(id) from tb
对id 索引非常有用，因为索引是按照顺序排序的，所以直接返回最后一个值即可
3.当返回的内容是索引的一部分时
这种情况不必扫描全表，只需查看索引即可
这种情况是不行的：f1 char(20),index f1(10),select f1 from tb ,因为索引只有10个字符，返回的却是20个
字符，必须扫描全表，扫描索引是不行的
4.order by
select * from tb order by f1,若 f1 是被索引的，则按照索引排序的顺序返回即可
5.在连接条件处使用
select t1.c1,t1.c2,t2.c3
from t1 join t2
on t1.c4=t2.c5
where t1.c6=xx;
如果 c4 和 c5 加了索引，也可以被使用到
6.不以% 开头的like 语句vs
select * from tb where name like 'jas%'
name 列加了索引也会被使用到
但是这种情况是不行的：select * from tb where name like '%jas%'
因为索引是按照第一个字符的字母顺序排序的
创建索引的一些建议：
1.where 条件中的字段创建索引
2.被索引的字段内容重复率越低越好，主键是最好的索引，每个id 只返回一条记录，枚举就比较差劲，比方
说 性别，查询 性别‘男’，可能会返回近一半的记录
3.使用前缀索引
4.不要创建太多的索引，太多的索引会拖慢插入和更新速度
最左原则
这个问题单纯的口头描述很难说清楚，直接看伪代码
create table tb (f1 char(20), f2 char(20), f3 char(20), f4 char(20), index(f1,f2,f3))
对于上面的表我们来进行下面几个sql 查询
1. select * from tb where f1='xx1' and f2 ='xx2' and f3='xx3'
2. select * from tb where f1='xx1' and f2 ='xx2'
3. select * from tb where f1='xx1'
4. select * from tb where f2 ='xx2' and f3='xx3'
5. select * from tb where f1='xx1' and f3='xx3'
上面的sql 中 1，2，3 的索引全部其作用
4中索引不会起作用
5中只有索引f1 起作用
也就是说复合索引使用的时候，必须是从最左侧开始，且是连续的，这样所有的索引才会被使用到
explain
下面来说一个很有用的东西，explain
先来建两张表，存储学生和老师信息，
create table student
(
stu_id int(3) zerofill,
name char(10),
majar char(10),
teacher_id int
);
create table teacher
(
teacher_id int,
name char(10)
);
insert into student values
(1,'丽丽','chemistry',1),
(2,'丽娟','english',2),
(3,'芳丽','儒学',3),
(4,'光头强','佛学',1),
(5,'熊大','math',2),
(6,'熊二','地理',3),
(7,'陶峰','高分子',4),
(8,'波波','机电',1),
(9,'米老鼠','土木',2);
insert into teacher values
(1,'刘老师'),
(2,'王老师'),
(3,'李老师'),
(4,'柴老师');
赶紧来试试explain
MariaDB [jason]> explain select * from student where stu_id=1;
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
给stu_id 加上索引再来试一下
MariaDB [jason]> alter table student add index(stu_id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [jason]> explain select * from student where stu_id=1;
+------+-------------+---------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+--------+---------+-------+------+-------+
| 1 | SIMPLE | student | ref | stu_id | stu_id | 5 | const | 1 | |
+------+-------------+---------+------+---------------+--------+---------+-------+------+-------+
有木有发现不一样？下面我们来解释一下explain 各个字段含义
说实话，我对参考资料中的的解释有些困惑，我想先自己造几个例子看看
MariaDB [jason]> show indexes from student ;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 1 | stu_id | 1 | stu_id | A | 9 | NULL | NULL | YES | BTREE | | |
| student | 1 | teacher_id | 1 | teacher_id | A | 9 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
MariaDB [jason]> drop index stu_id on student;
MariaDB [jason]> alter table student add primary key(stu_id);
MariaDB [jason]> show indexes from teacher;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher | 1 | teacher_id | 1 | teacher_id | A | 1 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
MariaDB [jason]> drop index teacher_id on teacher;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [jason]> show indexes from teacher;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher | 1 | teacher_id | 1 | teacher_id | A | 1 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
MariaDB [jason]> drop index teacher_id on teacher;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [jason]> alter talbe teacher add primary key(teacher_id);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'talbe teacher add primary key(teacher_id)' at line 1
MariaDB [jason]> alter table teacher add primary key(teacher_id);
我们先把student表中的stu_id 和tercher表中的teacher_id 变成了主键
example1：
MariaDB [jason]> explain select * from student where stu_id = 1;
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | student | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
example2：
MariaDB [jason]> explain select * from student where stu_id > 2;
+------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | student | range | PRIMARY | PRIMARY | 4 | NULL | 7 | Using where |
+------+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
example3：
MariaDB [jason]> explain select * from student where teacher_id = 1;
+------+-------------+---------+------+---------------+------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------------+---------+-------+------+-------+
| 1 | SIMPLE | student | ref | teacher_id | teacher_id | 5 | const | 3 | |
+------+-------------+---------+------+---------------+------------+---------+-------+------+-------+
example4：
MariaDB [jason]> explain select * from student where teacher_id>2;
+------+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------+
| 1 | SIMPLE | student | range | teacher_id | teacher_id | 5 | NULL | 3 | Using index condition |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------+
example5：
ariaDB [jason]> explain select s.stu_id,s.name,s.majar,s.teacher_id,t.name
-> from student s join teacher t
-> on s.teacher_id = t.teacher_id;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | s | ALL | teacher_id | NULL | NULL | NULL | 9 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
example6：
MariaDB [jason]> explain select s.stu_id,s.name,s.majar,s.teacher_id,t.name
-> from student s left join teacher t
-> on s.teacher_id = t.teacher_id;
+------+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 9 | |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | jason.s.teacher_id | 1 | |
+------+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------+
example7：
MariaDB [jason]> explain select s.stu_id,s.name,s.majar,s.teacher_id,t.name
-> from student s left join teacher t
-> on s.teacher_id = t.teacher_id
-> where t.name is not null;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
| 1 | SIMPLE | s | ALL | teacher_id | NULL | NULL | NULL | 9 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
example8：
MariaDB [jason]> explain select s.stu_id,s.name,s.majar,s.teacher_id,t.name
-> from student s right join teacher t
-> on s.teacher_id = t.teacher_id;
+------+-------------+-------+------+---------------+------------+---------+--------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------------+---------+--------------------+------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | s | ref | teacher_id | teacher_id | 4 | jason.t.teacher_id | 1 | |
+------+-------------+-------+------+---------------+------------+---------+--------------------+------+-------+
我尝试过使用full outer join ，但是mysql 不支持外连接
example9：
MariaDB [jason]> explain select stu_id,teacher_id from student;
+------+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | student | index | NULL | teacher_id | 4 | NULL | 9 | Using index |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-------------+
example10：
MariaDB [jason]> explain select max(teacher_id) from student;
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
example11：
MariaDB [jason]> explain select * from student order by teacher_id desc;
+------+-------------+---------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 9 | Using filesort |
+------+-------------+---------+------+---------------+------+---------+------+------+----------------+
example12：
MariaDB [jason]> explain select * from student force index(teacher_id) order by teacher_id desc;
+------+-------------+---------+-------+---------------+------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-------+
| 1 | SIMPLE | student | index | NULL | teacher_id | 4 | NULL | 9 | |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-------+
example13：
MariaDB [jason]> alter table student add index(name);
MariaDB [jason]> explain select * from student where name like '丽%';
+------+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | student | range | name | name | 41 | NULL | 2 | Using index condition |
+------+-------------+---------+-------+---------------+------+---------+------+------+-----------------------+
example14：
MariaDB [jason]> explain select * from student where teacher_id +5 < 7;
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | student | ALL | NULL | NULL | NULL | NULL | 9 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
MariaDB [jason]> explain select * from student where teacher_id < 2;
+------+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------+
| 1 | SIMPLE | student | range | teacher_id | teacher_id | 4 | NULL | 3 | Using index condition |
+------+-------------+---------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
example 15:
MariaDB [jason]> explain select s.stu_id,s.name,s.majar,s.teacher_id,t.name
-> from student s left join teacher t
-> on s.teacher_id = t.teacher_id
-> where t.teacher_id is null;
+------+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------------------+
| 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 9 | |
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | jason.s.teacher_id | 1 | Using where; Not exists |
+------+-------------+-------+--------+---------------+---------+---------+--------------------+------+-------------------------+
2 rows in set (0.00 sec)
example 16:
MariaDB [jason]> explain select *
-> from t2 left join t1 on t2.f2=t1.f1
-> left join t3 on t3.f3=t2.f2
-> left join t4 on t4.f4=t3.f3
-> where t1.f1=1;
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | t3 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | t4 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
这里没有列出最左原则的例子，大家可以自己个儿试一下，
关于expalin 各列的解释可以看这里
analyze table
mysql 在执行时select 或join 时会使用索引的分布信息来决定表的关联顺序、或使用那个索引，
analyze table tablename 就可以分析并存储索引的分布信息，
MariaDB [jason]> analyze table student;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| jason.student | analyze | status | OK |
+---------------+---------+----------+----------+
可以通过 show index from tb_name 来查看索引的情况
MariaDB [jason]> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student | 0 | PRIMARY | 1 | stu_id | A | 10 | NULL | NULL | | BTREE | | |
| student | 1 | teacher_id | 1 | teacher_id | A | 10 | NULL | NULL | | BTREE | | |
| student | 1 | name | 1 | name | A | 10 | NULL | NULL | YES | BTREE | | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
各字段的解释看这里
重点解释一下Cardinality
1. 列值代表的是此列中存储的唯一值的个数（如果此列为primary key 则值为记录的行数）
2. 列值只是个估计值，并不准确。
3. 列值不会自动更新，需要通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库。
4. 列值的大小影响Join时是否选用这个Index的判断。
5. 创建Index时，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数。
6. MyISAM与InnoDB对于Cardinality的计算方式不同。
optimize
如果对表进行了大量的删除和更新操作，那么表中就会留下缝隙，optimize table tb_name 可以将缝隙删除
将分割的记录连接在一起
MariaDB [jason]> optimize table student;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| jason.student | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| jason.student | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
什么鬼？原来是innodb 不支持optimize, 百度找到了方法
ALTER TABLE table.name ENGINE='InnoDB';
This will create a copy of the original table, and drop the original table, and replace to the original place.
Although this is safe, but I suggest you do backup and test first before doing this.
欢迎转载，不必注明出处
