网站建设改革情况汇报,app封装平台,懂福溶州做戒网站,国航网站建设1. Mysql查询语句的书写顺序Select [distinct ] 字段名称from 表1 [ join类型 join 表2 on join条件 ]where where条件group by 字段having having条件order by 排序字段limit 起始偏移量,行数2. Mysql查询语…1. Mysql查询语句的书写顺序Select [distinct ] 字段名称from 表1 [ join类型 join 表2 on join条件 ]where where条件group by 字段having having条件order by 排序字段limit 起始偏移量,行数2. Mysql查询语句的执行顺序8Select9distinct 字段名1,字段名27[fun(字段名)]1from 表13join类型join 表22on join条件4where where条件5group by 字段6having having条件10order by 排序字段11limit 起始偏移量,行数3. Mysql 如何实现多表查询MYSQL多表查询主要使用连接查询 , 连接查询的方式主要有 :内连接隐式内连接 : Select 字段 From 表A , 表B where 连接条件显式内连接 : Select 字段 From 表A inner join 表B on 连接条件外连接左外连接 : Select 字段 From 表A left join 表B on 连接条件右外连接 : Select 字段 From 表A right join 表B on 连接条件全外连接(很少用)交叉连接 : 笛卡尔效应 (很少用)4. MYSQL内连接和外连接的区别 ?内连接只有两个元素表相匹配的才能在结果集中显示。外连接左外连接: 左边为驱动表驱动表的数据全部显示匹配表的不匹配的不会显示。右外连接:右边为驱动表驱动表的数据全部显示匹配表的不匹配的不会显示。全外连接连接的表中不匹配的数据全部会显示出来。交叉连接笛卡尔效应显示的结果是链接表数的乘积。5. CHAR和VARCHAR的区别char的长度是不可变的用空格填充到指定长度大小而varchar的长度是可变的。char的存取速度比varchar要快得多char的存储方式是对英文字符ASCII占用1个字节对一个汉字占用两个字节。varchar的存储方式是对每个英文字符占用2个字节汉字也占用2个字节。6. 索引的底层数据结构了解过嘛 ?索引是是在存储引擎中实现的也就是说不同的存储引擎会使用不同的索引。 InnoDB引擎默认用的是B树 memory存储引擎把HASH当成默认索引 myisam存储引擎支持B和全文索引默认也是B树。B树 根和子节点存储的是索引值叶子节点存储的数据。 一个节点存储的索引值取决与我们索引值的大小。默认一个节点页16K 1K 1024B 一个叶子节点存储的记录取决于一条记录的大小比如一条记录是1KB一个叶能存16条从存储角度索引分为聚集索引与非聚集索引聚集索引(主键叶子节点存储的值是我们插入的原始数据非聚集索引叶子节点存储的是索引与主键值的对应关系。B树与B树最大区别B树的每个节点都存储数据。BTree是B-Tree的变种如图所示我们可以看到两部分绿色框框起来的部分是索引部分仅仅起到索引数据的作用不存储数据。红色框框起来的部分是数据存储部分在其叶子节点中要存储具体的数据。形成单向链表结构拓展-一个三阶三层高的B树可以存储多少条数据非叶子节点都是由key指针域组成的一个key占8字节一个指针占6字节而一个节点总共容量是16KB那么可以计算出一个节点可以存储的元素个数16*1024字节 / (86)1170个元素。查看mysql索引节点大小show global status like innodb_page_size; -- 节点大小16384当根节点中可以存储1170个元素那么根据每个元素的地址值又会找到下面的子节点每个子节点也会存储1170个元素那么第二层即第二次IO的时候就会找到数据大概是1170*1170135W。也就是说BTree数据结构中只需要经历两次磁盘IO就可以找到135W条数据。对于第二层每个元素有指针那么会找到第三层第三层由key数据组成假设key数据总大小是1KB而每个节点一共能存储16KB所以一个第三层一个节点大概可以存储16个元素(即16条记录)。那么结合第二层每个元素通过指针域找到第三层的节点第二层一共是135W个元素那么第三层总元素大小就是135W*16结果就是2000W的元素个数。结合上述分析BTree有如下优点千万条数据BTree可以控制在小于等于3的高度所有的数据都存储在叶子节点上并且底层已经实现了按照索引进行排序还可以支持范围查询叶子节点是一个双向链表支持从小到大或者从大到小查找关于B树数据结构感兴趣的同学可以通过一个网站进行演示https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html插入一组数据 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。然后观察一些数据插入过程中节点的变化情况。7. 索引的类型1. 按数据结构形式 BTree索引(InnoDB引擎默认、Hash索引memory引擎默认、Full-text索引myisam存储引擎支持2. 按物理存储分类 聚集索引(聚簇)、非聚集索引非聚簇二级索引)3. 按字段特性分类 主键索引、唯一索引、普通索引、全文索引普通索引MySQL中基本索引类型没有什么限制允许在定义索引的列中插入重复值和空值纯粹为了查询数据更快一点。唯一索引索引列中的值必须是唯一的但是允许为空值主键索引是一种特殊的唯一索引不允许有空值一个表只能由一个主键索引。全文索引 只有在MyISAM引擎、InnoDB5.6以后上才能使⽤用而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。4. 按字段个数分类 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 组合索引联合索引 : 在MYSQL数据库表的多个字段组合上创建的索引 , 称为组合索引也叫联合索引组合索引的使用需要遵循左前缀原则一般情况下建议使用组合索引代替单列索引主键索引除外create table index idxNameage on 表name,age,addr8. 聚集索引与非聚集索区别 ?分类含义特点聚集聚簇索引(Clustered Index)将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有,而且只有一个二级索引/非聚集非聚簇(Secondary Index)将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个聚集索引选取规则:如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。8. MYSQL支持的存储引擎有哪些 ?MYSQL存储引擎有很多, 常用的就二种 : MyISAM和InnoDB , 者两种存储引擎的区别 ;MyISAM支持256TB的数据存储 , InnerDB只支持64TB的数据存储MyISAM 不支持事务 , InnerDB支持事务MyISAM 不支持外键 , InnerDB支持外键9. 如何创建索引创建索引create [ unique ] index 索引名 on 表名 (字段名,... ) ;为tb_emp表的name字段建立一个索引create index idx_emp_name on tb_emp(name);为tb_emp表的name、username字段建立一个联合索引create index idx_emp_name_username on tb_emp(name,username);10. 在一个非主键字段上创建了索引, 想要根据该字段查询到数据, 需要查询几次 ?需要查询二次如果使用MyISAM存储引擎 , 会首先根据索引查询到数据行指针, 再根据指针获取数据如果是InnoDB存储引擎 , 会根据索引查找指定数据关联的主键ID , 再根据主键ID去主键索引中查找数据11. 知道什么是回表查询嘛 ?当我们为一张表的name字段建立了索引 , 执行如下查询语句 :select name,age from user where nameAlice那么获取到数据的过程为 :根据nameAlice查找索引树 , 定位到匹配数据的主键值为 id18根据id18到主索引获取数据记录 (回表查询)**先定位主键值再定位行记录就是所谓的回表查询它的性能较扫一遍索引树低 **12. 知道什么叫覆盖索引嘛 ?覆盖索引是指只需要在一棵索引树上就能获取SQL所需的所有列数据 , 因为无需回表查询效率更高实现覆盖索引的常见方法是将被查询的字段建立到联合索引里去。执行如下查询语句 : select name,age from user where nameAlice因为要查询 name 和 age二个字段 , 那么我们可以建立组合索引create index index_name_age on user(name,age) 那么索引存储结构如下 :这种情况下, 执行select name,age from user where nameAlice , 会先根据nameAlice, 找到记录 , 这条记录的索引上刚好又包含了 age 数据 , 直接把 Alice 77数据返回 , 就不会执行回表查询 , 这就是覆盖索引13. 知道什么是左前缀原则嘛 ?在mysql建立联合索引时会遵循左前缀匹配的原则即最左优先在检索数据时从联合索引的最左边开始匹配组合索引的第一个字段必须出现在查询组句中这个索引才会被用到 ;如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。例如 : create index index_age_name_sex on tb_user(age,name,sex);上述SQL语句对 age,name和sex建一个组合索引index_age_name_sex ,实际上这条语句相当于建立了(age) , (age,name) , (age,name,sex)三个索引 .select * from tb_user where age 49 ; -- 使用索引select * from tb_user where age 49 and name Alice ; -- 使用索引select * from tb_user where age 49 and name Alice and sex man; -- 使用索引select * from tb_user where age 49 and sex man; -- 使用索引 , 但是只有 age 匹配索引 sex没有走索引select * from tb_user where name Alice and age 49 and sex man ; -- 使用索引 , 因为MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引select * from tb_user where name Alice and sex man ; -- 不会使用索引14. 什么情况下索引会失效 ?MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度编写合理化的SQL能够提高SQL的执行效率不要在列上使用函数和进行运算不要在列上使用函数这将导致索引失效而进行全表扫描。尽量避免使用 ! 或 等否定操作符联合索引中出现范围查询(,)范围查询右侧的列索引失效。比如如下查询 agestatus select * from tb_user where profession 软件工程 and age 30 and status 0;当范围查询使用 或 时走联合索引了但是索引的长度只有age的长度就说明范围查询右边的status字段是没有走索引的。select * from tb_user where profession 软件工程 and age 30 and status 0;当范围查询使用 或 时走联合索引了索引的长度是agestatus的长度就说明所有的字段都是走索引的。所以在业务允许的情况下尽可能的使用类似于 或 这类的范围查询而避免使用 或 。尽量避免使用 or 来连接条件用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。 select * from tb_user where id 10 or age 23;select * from tb_user where phone 17799990017 or age 23; 由于age没有索引所以即使id、phone有索引索引也会失效。所以需要针对于age也要建立索引。当or连接的条件左右两侧字段都有索引时索引才会生效。多个单列索引并不是最佳选择建立组合索引代替多个单列索引, 可以避免回表查询查询中的某个列有范围查询则其右边所有列都无法使用索引优化查找索引不会包含有NULL值的列当查询条件左右两侧类型不匹配的时候会发生隐式转换隐式转换带来的影响就是可能导致索引失效而进行全表扫描。like 语句的索引失效问题like 的方式进行查询在 like “value%” 可以使用索引但是对于 like “%value%” 这样的方式执行全表查询尽量不要使用in或not in使用in查询数据,会进行全表扫描,降低效率对于连续的数值能用 between 就不要用 in 了#优化前
select id from t where num in(8,23,99)
#优化后
select id from t where num between 1 and 3必须使用in 可以使用使用union all 或 union 拆分sqlselect id from t where id 7
union all
select id from t where id 5
union all
select id from t where id 2715. 索引是越多越好嘛? 什么样的字段需要建索引, 什么样的字段不需要 ?需要创建索引情况主键自动建立主键索引频繁作为查询条件的字段应该创建索引多表关联查询中关联字段应该创建索引 (on 两边都要创建索引)查询中排序的字段应该创建索引频繁查找字段 , 应该创建索引查询中统计或者分组字段应该创建索引不要创建索引情况表记录太少经常进⾏行行增删改操作的表频繁更新的字段where条件里使用频率不高的字段索引设计原则1). 针对于数据量较大且查询比较频繁的表建立索引。2). 针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。3). 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。4). 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。5). 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。6). 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。7). 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。16.前缀索引、单列索引与联合索引当字段类型为字符串varchartextlongtext等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO 影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。create index idx_xxxx on table_name(column(n)) ;为tb_user表的email字段建立长度为5的前缀索引。create index idx_email_5 on tb_user(email(5)); 前缀长度可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高 唯一索引的选择性是1这是最好的索引选择性性能也是最好的。select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;单列索引即一个索引只包含单个列。联合索引即一个索引包含了多个列。17. mysql的性能优化从设计方面 选择合适的存储引擎 , 合适的字段类型 , 遵循范式(反范式设计)存储引擎 :不需要事务, 不需要外键读写较多的的使用MyIsam需要事务, 需要外键的使用InnoDB合适的字段类型 , 例如 :定长字符串用char , 不定长用varchar状态, 性别等有限数量值的用tinyint遵循范式 :第一范式1NF原子性第二范式2NF消除部分依赖第三范式3NF消除传递依赖• 第⼀范式1NF 原⼦性列或者字段不能再分要求属性具有原⼦性不可再分解 单一属性由基本类型构成包括整型、实数、字符型、逻辑型、日期型等。• 第⼆范式2NF 唯⼀性⼀张表只说⼀件事是对记录的惟⼀性约束要求记录有惟 ⼀标识• 第三范式3NF 直接性数据不能存在传递关系即每个属性都跟主键有直接关系 ⽽不是间接关系。2.从功能方面可以对索引优化采用缓存缓解数据库压力分库分表。3.从架构方面可以采用主从复制读写分离负载均衡18. MYSQL超大分页怎么处理 ?#效率低
select * from table where limit 1000000,10 正确的处理方法是 : 先快速定位需要获取的id再关联查询获取数据# 效率高 使用覆盖索引
select * from table where id in (select id from table limit 100000,10)
# 效率高 定位数据再使用覆盖索引
select * from table where id in (select id from table where id100000 order by id limit 10)通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据19. 如何定位慢查询 ?可以在MYSQL配置文件中开启慢查询 , 有两种方式可以开启慢查询方式一 : 修改my.ini配置文件 , 重启 MySQL 生效[mysqld]
log_outputFILE,TABLE
slow_query_logON
long_query_time0.001方式二 : 设置全局变量SET GLOBAL slow_query_log ON;
SET GLOBAL log_output FILE,TABLE;
SET GLOBAL long_query_time 0.001;20. 一个SQL语句执行很慢, 如何分析可以开启慢查询, 通过慢查询日志或者命令, 获取到执行慢的SQL语句 慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启我们可以查看一下系统变量 slow_query_log。使用EXLPAIN命令分析SQL语句的执行过程EXLPAIN命令, 比较重要的字段**(加黑加粗的是重要的)** :Explain 执行计划中各个字段的含义:字段含义idselect查询的序列号表示查询中执行select子句或者是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、 UNIONUNION 中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。possible_key显示可能应用在这张表上的索引一个或多个。key实际使用的索引如果为NULL则没有使用索引。key_len表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好 。rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的。filtered表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好。extra额外信息select_type重点解读type重点解读查询性能从上到下依次是最好到最差extra重点解读
