索引的使用   索引太少返回结果很慢，但是索引太多，又会占用空间。每次插入新记录时，索引都会针对变化重新排序   什么时候使用索引 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.                    
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄