MySQL 索引和连接的介绍和使用

目录

建索引的原则

索引覆盖

索引失效

左连接 内连接 右连接的区别

---

建索引的原则

字段没有大量相同取值，区分性好，比如身份证号优于性别。

字段占用空间小。

考虑使用索引覆盖。对数据很少被更新的表，如果用户经常只查询其中的几个字段，可以考虑在这几个字段上建立索引，从而将表的扫描改变为索引的扫描。

除了以上原则，在创建索引时，我们还应当注意以下的限制：

（1）限制表上的索引数目。

对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

（2）不要在有大量相同取值的字段上，建立索引。

在这样的字段（例如：性别）上建立索引，字段作为选择条件时将返回大量满足条件的记录，优化器不会使用该索引作为访问路径。

索引覆盖

假如有这样一张表:

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

两种查询语句

SELECT * FROM test WHERE age = 10;
SELECT age,NAME FROM test WHERE age = 10;

 下面的这个走的是覆盖索引，比上面的快，是因为下面这个没有回表的操作，所以速度更快。

在实际应用中，如果select 多个字段比较慢的话，可以尝试将这多个字段建立联合索引，这样只需要扫描一遍联合索引树即可得到数据。

索引失效

MySQL 索引失效的几种类型以及解决方式 - 知乎

索引列不独立是指 被索引的这列不能是表达式的一部分，不能是函数的参数，比如下面的这种情况

select id,name,age,salary from table_name where salary + 1000 = 6000;

salary 列被用户表达式的计算了，这种情况下索引就会失效，解决方式就是提前计算好条件值，不要让索引列参与表达式计算，修改后 sql 如下

select id,name,age,salary from table_name where salary = 5000;

索引字段作为函数的参数

select id,name,age,salary from table_name where substring(name,1,3)= 'luc';

解决方式是什么呢，可以提前计算好条件，不要使用索引，或者可以使用其他的 sql 替换上面的，比如，上面的sql 可以使用 like 来代替

select id,name,age,salary from table_name where name like 'luc%';

使用了左模糊

select id,name,age,salary from table_name where name like '%lucs%';

平时尽可能避免用到左模糊，可以这样写

select id,name,age,salary from table_name where name like 'lucs%';

如果实在避免不了左模糊查询的话，考虑一下搜索引擎 比如 ES

不符合最左前缀原则的查询

例如有这样一个组合索引 index(a,b,c)

select * from table_name where b='1'and c='2'
select * from table_name where c='2'

// 上面这两条 SQL 都是无法走索引执行的

最左原则，就是要最左边的优先存在。

查询的数量是大表的大部分，全表扫描比走索引更快。

左连接 内连接 右连接的区别

左连接，右连接，全外连接的区别是什么？ - 知乎

为什么要用连接（join）

因为大部分情况下，要符合数据库设计规范，数据不可能集中在同一张表里，那样的话会产生数据冗余，但是分成多张表会造成取数比较麻烦，join（连接）就是为解决上述问题的一种语法。

left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录，右表没有就填充为null；

right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录，左表没有就填充为null；

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行，两边都有才返回。

full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。