假设user表的id字段为聚集索引,name字段为二级索引,那么select * from user where name = 'Arm'的查询顺序如下:
会先到二级索引中查询name = Arm的数据,查询到name=Arm的id为10,然后再去聚集索引中查询id=10的数据(聚集索引中存放的是这一行的行数据)流程图如下:
思考题
以下 SQL 语句,哪个执行效率高?为什么?
select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注:id为主键,name字段创建的有索引
答:第一条语句,因为第二条须要回表查询,相当于两个步骤。
六、索引的使用(创建、查看、删除)
创建索引:
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);
表明如下:
例如,假如要在名为users的表上创建一个名为idx_username的普通索引,索引列为username,可以使用以下语句:
CREATE INDEX idx_username ON users (username);
假如要创建一个唯一索引,可以将关键字UNIQUE添加到语句中:
CREATE UNIQUE INDEX idx_email ON users (email);
假如要创建一个全文索引,可以将关键字FULLTEXT添加到语句中:
CREATE FULLTEXT INDEX idx_content ON articles (content); 查看索引:
SHOW INDEX FROM table_name; 删除索引:
DROP INDEX index_name ON table_name; 案例:
-- name字段为姓名字段,该字段的值大概会重复,为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空,且唯一,为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建团结索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为email创建合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
-- 删除索引
drop index idx_user_email on tb_user;
七、SQL性能分析
7.1 SQL执行频率(了解)
My5QL客户端毗连成功后,通过show [session|global] status命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。
使用My5QL客户端成功毗连到MySQL服务器后,可以通过使用SHOW SESSION STATUS或SHOW GLOBAL STATUS命令来获取服务器的状态信息。具体地,可以使用以下指令来查看当前数据库中INSERT(插入)、UPDATE(更新)、DELETE(删除)和SELECT(查询)的访问频次:
SHOW SESSION STATUS LIKE 'Com_insert';
SHOW SESSION STATUS LIKE 'Com_update';
SHOW SESSION STATUS LIKE 'Com_delete';
SHOW SESSION STATUS LIKE 'Com_select';
在MySQL中,"session"和"global"都是用来指代差别级别的变量或参数。
当使用团结索引举行范围查询(<, >)时,范围查询右侧的列索引将失效。这是因为范围查询须要按照肯定的顺序扫描索引,从而无法完全利用索引的有序性。
为了规避这个索引失效题目,可以考虑改用>=或者<=来取代范围查询。通过使用>=或者<=操作符,可以将范围查询转化为等值查询或者单值查询,从而使得整个团结索引仍然保持有效。
例如,假如要举行范围查询 col1 > 5 AND col2 < 10,可以改写为 col1 >= 5 AND col1 < x AND col2 < 10,其中 x 是大于 5 的一个值。这样,我们将范围查询拆分成两个等值查询,保证了团结索引的有效使用。
须要留意的是,在拆分范围查询时,我们须要根据具体情况选择合适的拆分点(比如上述例子中的 x 值),以保证查询结果的正确性和覆盖率。此外,拆分后的查询条件大概会增加一些逻辑复杂性,须要谨慎设计和测试。
8.3 SQL提示
USE INDEX:指示MySQL使用特定的索引来执行查询。
IGNORE INDEX:指示MySQL忽略特定的索引,而选择其他可用的索引来执行查询。
FORCE INDEX:强制MySQL使用特定的索引来执行查询,并忽略其他大概更适合的索引。
8.4 覆盖索引
尽量使用覆盖索引(查询使用了索引,并且须要返回的列,在该索引中已经全部能找到),淘汰 select *。
explain 中 extra 字段含义:
using index condition:查找使用了索引,但是须要回表查询数据
using where; using index;:查找使用了索引,但是须要的数据都在索引列中能找到,所以不须要回表查询
假如在聚集索引中直接能找到对应的行,则直接返回行数据,只须要一次查询,哪怕是select *;假如在辅助索引中找聚集索引,如select id, name from xxx where name='xxx';,也只须要通过辅助索引(name)查找到对应的id,返回name和name索引对应的id即可,只须要一次查询;假如是通过辅助索引查找其他字段,则须要回表查询,如select id, name, gender from xxx where name='xxx';
所以尽量不要用select *,轻易出现回表查询,降低效率,除非有团结索引包罗了所有字段 面试题:一张表,有四个字段(id, username, password, status),由于数据量大,须要对以下SQL语句举行优化,该怎样举行才是最优方案:
select id, username, password from tb_user where username='itcast';
解:给username和password字段创建团结索引,则不须要回表查询,直接覆盖索引
8.5 前缀索引
当字段范例为字符串(varchar, text等)时,有时间须要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率,此时可以只降字符串的一部分前缀,创建索引,这样可以大大节约索引空间,从而提高索引效率。
语法:create index idx_xxxx on table_name(columnn(n));
前缀长度:可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。 求选择性公式:
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
8.6 单列索引与团结索引
单列索引:即一个索引只包罗单个列
团结索引:即一个索引包罗了多个列
在业务场景中,假如存在多个查询条件,考虑针对于查询字段创建索引时,建议创建团结索引,而非单列索引。
单列索引情况:
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
这句只会用到phone索引字段
留意事项
当MySQL评估使用索引比全表扫描更慢时,会选择不使用索引。以下是一个例子:
对于一个学生表,假如包罗列info,并且大部分记录的info字段为空,并且该列设置了索引,当执行以下查询时:
SELECT * FROM student WHERE info IS NULL;
在这种情况下,MySQL的优化器大概会选择不使用该列的索引。
原因如下: