MySQL--Day03

MySQL进阶Linux)数据库结构连接层 - - 客户端的连接、认证服务层 - - sql解析、结构、优化引擎层 - - 索引存储层 - -磁盘存储引擎是mysql中存储数据、建立索引、更新/查询数据等技术的实现方式存储引擎是基于表的而不是基于库的。存储引擎基于表在创建表时指定存储引擎CREATE TABLE 表名( ... )ENGINE INNODB -- 查看当前数据库支持的存储引擎 show engines;innoDB(行锁(提高并发访问性能)、支持外键、支持事务).ibd innoDB 引擎的每张表都会对应 这样一个表空间文件存储该表的表结构 表索引 表数据-- 查看系统变量 -- 查看每个表是否对应一个表空间文件 show variables like ‘innodb_file_per_table’ -- .idb文件存放在mysql数据存放目录中 -- 二进制文件在终端查看文件内容 ibd2sdi account.ibdinnoDB逻辑存储结构MySAM(表锁、不支持外键、速度快) MongoDB涉及到的磁盘文件sdi – 存储表结构MDY –存储数据MYI – 存储索引Memory 作为临时或者缓存使用(内存存放哈希索引) redissdi存储引擎选择索引索引是帮助MYSQL高效获取数据的数据结构在数据之外数据库还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引优点提高检索的效率降低数据库的IO成本 | 通过索引对数据进行排序降低排序的成本降低CPU消耗缺点索引占用空间 | 索引会降低更新表的速度常见数据结构存在的缺点二叉树顺序插入时会形成一个链表查询性能大大降低大数据情况下层级较深检索速度慢红黑树大数量情况下层级较深检索速度慢B-Tree结构(多路平衡查找数)BTree结构- -所有的元素都会出现在叶子节点数据存储在叶子节点中非叶子节点起到索引的作用Hash索引特点只能用于对等比较不支持范围查询无法利用索引完成排序操作查询效率高通常只需要一次检索为什么innoDB存储引擎选择使用BTree索引结构相对于二叉树层级更少搜索效率更高对于B-Tree无论叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少同样要保存大量的数据只能增加树的高度导致性能降低索引分类innoDB存储引擎中的分类聚集索引(必须有且只有一个) 数据存储与索引一起叶子节点保存行数据二级索引 - - 数据存储与索引分开叶子节点关联的是对应的主键索引选取规则如果存在主键索引则主键索引就是聚集索引如果不存在主键使用第一个唯一索引作为主键否则innoDB自动生成rowid索引语法回表查询二级查询 ——聚集查询创建索引create [UNIQUE /FULLTEXT] index_name on table_name(index_col_name ….关联) -- 唯一索引 CREATE INDEX name_index ON emp (name) -- 联合索引 CREATE INDEX idx_user_pro_age_sta ON emp(profession,age,status);查看索引show index from table_name\G删除索引drop index index_name on table_nameSQL性能分析工具SQL执行频率(增删改查在当前数据库中所占的比例)-- 查看增删改查的频次 show global status like ‘Com_______’;慢查询日志(定位哪些语句执行效率低)慢查询日志的开关show variables like ‘slow_query_log’;开启慢查询日志(etc/my.cnf)日志存放位置var/lib/mysql尾部实时输出内容tail -f localhost-slow.logprofile - - 每条语句的详细耗时是否支持profile操作select have_profiling; -- 查看是否开启 select profiling开启set profiling 1查看当前profile每一条语句的耗时show profiles查看指定query_id的SQL语句各个阶段的耗时情况show profile for query query_id查看指定query_id的SQL语句CPU使用情况:show profile cpu for query query_idexplain执行计划 - -获取SQL如何执行SELECT语句的信息查看SQL语句的执行计划explain SELECT * FROM emp执行计划中 id 值越大越先执行id值相同从上到下依次执行type 连接类型性能由好到差 NULL(不访问表) system(系统表) const(主键/唯一索引) eq_ref ref(非唯一索引) range index allkey 索引索引的使用最左前缀法则对于联合索引必须包含最左边的索引,如果跳过最左边的索引所有字段索引全部失效如果中间间断的话后面的字段会失效-- 联合索引 CREATE INDEX idx_user_pro_age_sta ON emp(profession,age,status); SELECT * from emp WHERE profession ... and ..范围查询范围查询右边的列会失效SELECT * from emp WHERE profession ... and age 30 and status 0 -- 范围查询右边的字段索引会失效 解决方法在业务允许的情况下尽量使用尽量不要在索引列上进行运算操作索引将失效SELECT * FROM emp WHERE SUBSTRING(phone,10,2) 15;字符串类型的索引不加引号索引会失效模糊查询模糊匹配后面模糊(%)索引不失效模糊匹配前面模糊(%)索引失效SELECT * from emp WHERE profession LIKE 软件%or连接的条件只有两侧的列都有索引的时候索引才不会失效数据分布SQL评估索引比全表扫面慢的时候不使用索引SQL提示use index() (指定使用哪个索引)select * from tb_user use index(索引名称…) where … 建议ignore index() 忽略索引select * from tb_user ignore index(索引名称…) where …force index() 强制使用select * from tb_user force index(索引名称…) where …覆盖索引(using index)select后查询的的字段覆盖索引否则容易出现回表查询在explain执行计划最后一列的Extra中前缀索引将字符串的一部分前缀作为索引-- n是前缀长度 create index idx_… on table_name(clumn(n));选择性select count(distinct email)/count(*)fromtb_user; select count(distinct substring(email,1,5))/count(*)fromtb_user;​今天学习了存储引擎和索引也是数据库中最重要的部分首先存储引擎是基于表的是MYSQL中存储数据、建立索引、查询/更新数据等操作的方式创建表时默认的存储引擎时innoDB存储引擎。其次索引时帮助MySQL高效获取数据的一中数据结构innoDB存储引擎默认的索引结构是BTree结构接着就是围绕索引学习了索引的分类其中innoDB存储引擎中的索引分为聚集索引和二级索引学习了索引的创建、查看、删除等语法学习了SQL性能分析工具、索引使用等内容。持续更新!