一)联合索引:
1)定义:是给一张表上面的多个列增加索引，也就是说给表上面的多个列增加索引，供快速查询使用，当两个列的组合是唯一值时，联合索引是个不错的选择
联合索引和单个索引对比来讲，联合索引的所有索引项都会出现在索引上，存储引擎会先根据第一个索引项排序，如果第一个索引项相同的话才会去看第二个，所有我们在查询的时候，如果头索引不带的话，联合索引就会失效，因为在根节点他就不知道怎么往下走
一般来说在实际开发中，很少使用到单个字段来做索引，因为总的来说尽可能还是让索引的数量少，避免占用磁盘空间太多影响性能；
二)索引失效:
1)最左前缀法则:如果说使用了联合索引，那么要遵循最左前缀法则，最左前缀法则指的是从查询索引的最左列开始，并且不跳过索引中的列，如果说跳过某一列，那么直接会导致后面字段索引失效；
1.1)注意:最左前缀法则指的是最左边的列，是在进行查询的时候，联合索引的最左边字段必须存在和我们前后所写的SQL顺序没有关系
1.2)就是当你比如说创建了一张表，这张表的所有字段都有索引，那么这个时候最左匹配不遵循，索引也不会失效，因为索引树里面已经包含了有关于查询的全部字段，此时就不需要进行回表查询了
1.3)况且如果说跳过了某一列，后面的索引字段全部失效
desc select * from child where username="A" and classID=1; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | child | NULL | ref | AllIndex | AllIndex | 153 | const | 1 | 50.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+select * from child;//查询表里面所有字段 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | | 2 | B | 8989 | 2 | 89 | +----+----------+----------+---------+--------+ 2 rows in set (0.00 sec) mysql> create index AllIndex on child(username,password,classID);//针对表中的三列建立索引 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from child where username="A" and password="12345" and classID=1;//符合最左前缀法则,索引生效 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | +----+----------+----------+---------+--------+ mysql> desc select * from child where username="A" and password="12345" and classID=1; //显示信息 +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | child | NULL | ref | AllIndex | AllIndex | 311 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ mysql> select * from child where password="12345" and classID=1; //不满足最左前缀法则,索引失效 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | +----+----------+----------+---------+--------+ mysql> desc select * from child where password="12345" and classID=1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | child | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1)在联合索引中，出现了范围查询，范围查询右侧的列索引失效，所以尽量少使用>,<而是使用>=和<=
2)在索引列上面进行列运算操作，那么索引列会失效，也包括查询的列使用了函数
3)字符串类型的字段不加上引号，那么索引字段失效，或者说查询的列进行了隐式类型转换
4)如果是like尾部模糊查询，那么索引不会失效，但是如果头部模糊查询，那么索引会失效
5)or连接条件:如果说or前面的列有索引，但是后面的列没有索引，那么涉及到的索引都不会生效，只有当or两侧都有索引之后，索引才会生效
MYSQL评估如果说索引比全表扫描的更慢，那么直接会走全表扫描，索引也会失效
前缀索引:当需要针对字符串类型来进行创建索引的时候，索引的字符串就会变得很长很长，或者是大文本数据，查询时候会浪费大量的磁盘IO，影响查询效率，所以应该只将字符串的一部分前缀作为索引，这样可以大大节省索引空间
create index passwordIndex on child(password(3)); show index from child; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | child | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | child | 1 | AllIndex | 1 | username | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | AllIndex | 2 | password | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | AllIndex | 3 | classID | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | passwordIndex | 1 | password | A | 2 | 3 | NULL | YES | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------
select count(*) from child;//先进行查询一共有多行 +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) mysql> select count(distinct password) from child;//查询不重复密码有多少行 +--------------------------+ | count(distinct password) | +--------------------------+ | 2 | +--------------------------+ select count(distinct substring(password,1,3))/count(*) from child;//查询截取后的字段除以总数据长度的值是不是1,如果截取的字段出现了重复,那么值会小于1 +--------------------------------------------------+ | count(distinct substring(password,1,3))/count(*) | +--------------------------------------------------+ | 1.0000 | +--------------------------------------------------+1)create index 索引名字 on 表名(哪一个列名(整数前缀，截取充当前缀索引的范围))
2)咱们可以根据索引的选择性来进行确定，而选择性是根据不重复的索引值/数据库表中的记录总数的比值，索引选择性越高那么查询效率就越高，性能也是最好的
最后一列中的Sub_Part就是描述在原来的字符串里面截取了几个字段
上面就创建了一个前缀索引:对前五个字符构建前缀索引并没有针对这个字段建立完整的索引
1)当进行查询select * from "17799999"的话，会进行截取前5个字符，根据字符先后顺序，进行比较，也就是根据26个字母的先后顺序，查到主键id，再根据主键id在聚簇索引上面查询到完整的数据载荷了，再从数据载荷里面查询到email的值，和刚才的from后面的值进行对比，相同的话返回完整的记录
2)然后那个前缀索引的叶子结点的指针继续向后走，重复刚才的过程
单列索引和联合索引的对比:
create table User(userID int,username varchar(40),password(40)，classID int);
1)在User表中针对username和password分别创建索引，也就是说现在有两个索引
先在进行操作select username,password from User where username=XXX and password=XXX
2)但是现在这样的查询效率是比较低的，但是最终MYSQL只会选择一个索引，所以还是要进行回表查询的
3)但是当针对这两个字段创建一个联合索引在继续执行上面的SQL语句，就会效率很高的
所以说在进行多条件联合查询的时候，就例如上面的例子，针对两个列分别建立了索引，又创建了联合索引，MYSQL就会进行自动评估哪一个字段的索引效率会更高，从而会进行选择合适的，效率高的索引来进行查询
覆盖索引:是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
三)存储引擎:
连接池:提供了多个用于客户端和服务器端交互的线程
SQLInterface:接收SQL指令，返回查询结果的
Parser:解析器，语法解析SQL，语义解析SQL，生成一个语法树，便于后面进行查询的优化
Optimizer:对SQL进行优化
Cachees/Buffer:查询缓存以key(SQL语句)，value(查询出来的结果)的方式来缓存查询结果的
插件式的存储引擎:和底层的文件系统进行交互
MYSQL的存储引擎:
2.1)在创建表的时候指定存储引擎:
create table 表名() engine=存储引擎的名字
2.2)查看数据库所支持的存储引擎:show engines
四)SQL优化:
一)慢查询日志:
通过这个来进行查询那些select的操作频次比较高，要针对那些SQL执行效率比较低，MYSQL的慢查询日志记录了所有执行时间查过了指定参数的所有SQL语句的日志，MYSQL的慢查询日志没有进行开启，需要在MYSQL的配置文件里面(/ect/my.cnf)配置如下信息:
1)启动慢查询日志执行开关:show_query_log=1;
2)设置慢查询的时间是2s，如果说SQL语句执行时间超过2s，就会被视为是慢查询操作，会进行记录慢查询日志:long_query_time=2;
3)show variables like "%slow_query_log%";查询当前数据库是否支持慢查询日志
慢查询日志在linux中的路径是:/var/lib/mysql/localhost-show.log
查询里面的信息cat localhost-show.log
二)观察SQL语句的执行时间:
profile详情:
看看当前数据库是否支持profiles:select @@having_profiling
开启profile操作:set profiling =1
mysql> select * from dish; +--------+--------------+-----------+ | dishID | dishName | dishMoney | +--------+--------------+-----------+ | 2 | 红烧茄子 | 90 | | 3 | 红烧里脊 | 100 | | 4 | 红烧牛肉 | 700 | | 5 | 牛肉干 | 70 | +--------+--------------+-----------+ 4 rows in set (0.00 sec) mysql> show profiles; +----------+------------+--------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------+ | 1 | 0.00010350 | show profies | | 2 | 0.00072225 | show tables | | 3 | 0.00054600 | select * from dish | +----------+------------+--------------------+ 通过上面的命令就可以看到MYSQL语句的耗时时间show profiles:查看每一条SQL的基本查询情况
show profile for query query_id;查看指定id的SQL的查询情况，看看时间长短，看看哪一部分耗时时间
show profile cpu for query query_id;
show profile for query 3; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000073 | | checking permissions | 0.000004 | | Opening tables | 0.000102 | | init | 0.000023 | | System lock | 0.000030 | | optimizing | 0.000002 | | statistics | 0.000008 | | preparing | 0.000006 | | executing | 0.000001 | | Sending data | 0.000187 | | end | 0.000002 | | query end | 0.000013 | | closing tables | 0.000005 | | freeing items | 0.000076 | | cleaning up | 0.000018 | +----------------------+----------+
覆盖索引定义:MySQL 执行计划 explain 结果里的 key 有使用索引，如果 select 后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有 using index；覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。
1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到4)假设你定义一个联合索引
create index id_name_age on user(name,age);
5)查询名称为 liudehua 的年龄：select name, age from user where name = "生命在于运动"
6)上述语句中，查找的字段 name 和 age 都包含在联合索引 id_name_age 的索引树中，这样的查询就是覆盖索引查询
三)explain执行计划:
可以通过explain或者是desc命令来进行获取到MYSQL是如何执行select语句的信息，包括select语句执行过程中如何连接和怎么连接的顺序
expalin执行计划各个字段的含义:
4.1)id:表示MYSQL查询的序列号，表示查询中执行select子句或者是表的操作顺序，id相同，执行顺序从上到下，id不同，值越大，越被先执行
现在针对这几个字段来进行演示一下
创建一张学生表: 1)drop table if exists student; 2)create table student(id int primary key auto_increment, name varchar(40)); drop table if exists course; 3)create table course(courseid int primary key auto_increment, coursename varchar(50)); drop table if exists student_course; 4)create table student_course( studentid int, courseid int, foreign key (studentid) references student(id), foreign key (courseid) references course(courseid)); 5)insert into student values(1,"李佳伟"),(2,"张中军"),(3,"张志超"); 6)insert into course values(1,"Java"),(2,"MYSQL"),(3,"C++"); 现在我们来进行查询一下每一个人选了那些课程: mysql> select student.*,course.coursename from student_course,course,student where student.id=student_course.studentid and student_course.courseid=course.courseid; +----+-----------+------------+ | id | name | coursename | +----+-----------+------------+ | 1 | 李佳伟 | Java | | 1 | 李佳伟 | MYSQL | | 2 | 张中军 | MYSQL | | 3 | 张志超 | Java | | 3 | 张志超 | MYSQL | | 3 | 张志超 | C++ | +----+-----------+------------+刚才在进行使用外键的时候，注意:
1)本表中的字段名也要加上括号
2)注意是references
desc select student.*,course.coursename from student_course,course,student where student.id=student_course.studentid and student_course.courseid=course.courseid; +----+-------------+----------------+------------+------+--------------------+----------+---------+---------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+--------------------+----------+---------+---------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | course | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | NULL | | 1 | SIMPLE | student_course | NULL | ref | studentid,courseid | courseid | 5 | orderdish.course.courseid | 1 | 100.00 | NULL | | 1 | SIMPLE | student | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+----------------+------------+------+--------------------+----------+---------+------------------查询一下选择了MYSQL课程的学生，子查询
select courseid from course where coursename="MYSQL"; select studentid from student_course where courseid=2; select name from student where id in(1,2,3);select name from student s1 where s1.id in (select studentid from student_course s2 where s2.courseid=(select courseid from course s3 where s3.coursename="MYSQL"));----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+--------------------------------------------------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | NULL | | 1 | PRIMARY | s2 | NULL | ALL | studentid,courseid | NULL | NULL | NULL | 6 | 16.67 | Using where; FirstMatch(s1); Using join buffer (Block Nested Loop) | | 3 | SUBQUERY | s3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+----------------------------------------------4.2)select type:表示select的类型
常见的取值有simple:简单表，表示不进行使用表连接或者子查询
primary:表示主查询，即外层的查询
union:表示union后面第二个或者后面的查询语句
subquery:表示select where后面包括了子查询等等
4.3)type:表示连接类型，性能由好到差的连接类型有
null(什么时候都不操作表)
system(系统表)，
const:把一个主键放在where后面进行作为条件查询
eq_ref(在join中使用主键或者唯一索引)，
ref(使用非唯一性索引进行查询)，
range(索引的范围查询)，
index(代表索引覆盖，遍历所有的索引查找)
all(全表扫描，(通常没有建索引的列))；
4.4)possible_key:显示可能应用在这张表上面的索引，一个或者多个
4.5)key_len:表示索引中所使用的字节数，该值可能为索引字段最大可能长度，而不是实际使用长度，在不损失精度的情况下，长度越短越好
4.6)rows:MYSQL认为必须要进行执行查询的行数，在innodb引擎中是一个估计值，但是结果并不总是准确的
4.7)filedter:返回结果的行数占需要进行读取行数的百分比，这个值越大性能越高
4.8)Extra:表示额外数据查询，执行情况的描述和说明
4.9)key:表示实际使用到的索引，如果为空，那么表示从来没有使用过索引，反之，那么使用到了索引
5.0)table:表示输出结果的表
