高唐网站开发电子购物网站

高唐网站开发,电子购物网站,标准网站建设公司,网站说明页命名索引非常重要MySQL体系结构1、存储引擎#xff08;MySQL默认InnoDB#xff09;1、InnoDB#xff1a;高可靠性和高性能的通用存储引擎存储数据#xff0c;建立索引#xff0c;更新/查询数据#xff0c;基于表设置。--创建表时指定存储引擎 create table 表名(字段 字段类型…索引非常重要MySQL体系结构1、存储引擎MySQL默认InnoDB1、InnoDB高可靠性和高性能的通用存储引擎存储数据建立索引更新/查询数据基于表设置。--创建表时指定存储引擎 create table 表名( 字段 字段类型 注释 )engine innodb; --查看当前数据库支持的存储引擎 show engines;InnoDB特点DML操作遵循ACID模型支持事务行级锁提高并发访问性能支持外键foreign key约束保持数据的完整性和正确性磁盘文件xxx.ibd 表空间文件存放表结构sdi、数据和索引逻辑存储结构表空间-段-区1M-64个页16K-行行 包括 最后操作id、指针、字段对比其他存储引擎2、MyISAM早期存储引擎不支持事务和外键支持表锁不支持行锁访问速度快磁盘文件.sdi存放表结构.MYD存放数据.MYI存放索引适合以读和Insert操作为主的很少update并发性要求不高3、Memory存储在内存有硬件/断电问题只能作为临时表或缓存使用内存存放hash索引xxx.sdi存储表结构信息访问速度快表大小有限制用于临时表和缓存无法保障数据安全性2、索引索引index帮助MySQL高效获取数据的数据结构有序指向原始数据。select * from user where age45;无索引则需要全表逐行扫描45的数据效率低有索引通过数据结构搜索到索引指向45的原始数据获取数据效率高索引结构MySQL索引主要在存储引擎层实现主要是Btree索引最常见大部分引擎都支持。对比其他数据结构二叉搜索树每个节点最多两个分支分支可能很深如果插入顺序是单调递增会“退化”成链表红黑树在此基础上解决了退化成链表的问题红黑树限制红色节点不能连续出现所有路径的黑节点数量相同因此图中2改为黑色仍存在查询性能低大数据量情况下层级深检索速度慢。B-tree多路平衡查找树例5阶5个子节点4个key5个指针动态构建超过四个数 中间key向上裂变最上层裂变Btree例3阶3个子节点2个key3个指针1所有数据都在叶子节点上层只有指针上层元素都也在叶子节点中2叶子节点形成单向链表MySQL的Btree索引在Btree基础上优化增加指向相邻叶子节点的链表指针上层是索引作用均存在页/块中。hash索引键值换算成hash值映射到对应槽位上键值hash值均存储在hash表出现冲突用链表解决。特点只支持等值匹配不能范围查询between and不能排序查询效率高通常只需要一次检索效率高于Btree索引为什么InnoDB存储引擎选择Btree索引结构相较于二叉树层级更少搜索效率高B-tree的叶子节点和非叶子节点都会保存数据导致页中存储的键值和指针减少大量数据情况下会增加树的高度导致性能下降hash索引不能范围匹配和排序索引的分类主键索引只能有一个--创建索引 create [unique|fulltext] index index_name on table_name(index_col_name,...); --unique唯一索引fulltext全文索引不加就是常规索引index_col_name字段名 --关联单字段是单列索引多字段是联合索引 --查看索引(表中所有) show index from table_name; --删除索引 drop index index_name on table_name; --例子 create index idx_user_name on tb_user(name); --默认Btree结构 create index idx_user_pro_age_sta on tb_user(profession,age,status); --联合索引InnoDB存储引擎根据索引的存储形式分为聚集索引选取规则存在主键主键索引就是聚集索引——不存在主键第一个唯一索引unique作为聚集索引——都没有InnoDB会自动生成一个rowid作为隐藏的聚集索引。例有主键作为聚集索引叶子节点保存整行全部数据二级索引name列叶子节点只存储对应的主键。回表查询select * from user where name’Arm’先二级索引找Arm和对应id再聚集索引id10找行数据回表查询性能较低SQL性能分析查看方法1、首先查询SQL执行频率查看select、insert、update、delete的访问频次show [global | session] status like ‘Com_______’;对select查询较多的数据库进行SQL优化2、慢查询日志记录所有执行时间超过指定参数10s的所有SQL语句的日志show variables like ‘slow_query_log’; —查看慢查询日志状态在配置文件/etc/mysql/mysql.conf.d/mysqld.cnf 中开启开关设置时间2s添加慢查询日志文件slow_query_log1long_query_time2slow_query_log_file /var/lib/mysql/localhost-slow.log然后重启mysqlsystemctl restart mysql3、profile详情定位时间耗费在哪select have_profiling; --查看当前mysql是否支持profile操作 select profiling; --查看profiling状态 set [global|session] profiling 1; --开启profiling开关 show profiles; --查看每一条sql的耗时基本情况 show profile [cpu] for query query_id; --指定语句各个阶段耗时情况4、explain/desc执行计划比较重要直接在select语句前加上explain/desc获取执行信息表连接和顺序explain select * from tb_user where id1;id 表示查询语句中 表的执行顺序id值大的先执行id值相同从上到下执行select_type 查询类型常见simpleprimaryunionsubquerytype 连接类型性能由好到差NULLsystemconsteq_refref、range、index、allpossible_keys可能用到的索引key 实际用到的索引key_len 索引中使用的字节数索引字段最大可能长度长度越短越好filtered 返回结果行数/需要读取的行数越大越好索引使用原则一张表默认只有主键索引执行select * from tb_sku where sn’001’;执行需要21s因此创建sn字段的索引create index idx_sku_sn on tb_sku(sn);再次执行查询只需要0.01s因此尽量根据有索引的字段筛选1、联合索引——最左前缀法则查询从索引的最左列开始且不跳过索引中的列--针对 多字段联合索引 create index idx_user_pro_age_sta on tb_user(profession,age,status); --符合最左前缀法则 explain select * from tb_user where professionmath and age31 and status0; explain select * from tb_user where professionmath and age31 ; --不符合最左前缀法则keynull不走索引走全表扫描typeall效率极低 explain select * from tb_user where age31 and status0; --不符合最左前缀法则走索引但部分失效仅profession有效 explain select * from tb_user where professionmath and status0; --联合索引中出现范围查询范围查询右侧的列索引失效 --仅profession和age索引有效 explain select * from tb_user where professionmath and age30 and status0; --均有效 explain select * from tb_user where professionmath and age30 and status0;索引失效的情况1不要在索引列上进行运算操作索引将失效explain select * from tb_user where substring(phone,10,2)’15’;返回typeall全表查询keynull没有用到索引2字符串不加单引号索引将失效3如果仅尾部模糊匹配索引不会失效如果头部模糊匹配索引失效explain select * from tb_user where profession like ‘m%’; —有索引explain select * from tb_user where profession like ‘%th’; —索引失效4or连接的条件如果前面的列有索引而后面的列没有索引那么索引均失效explain select * from tb_user where id10 or age23; —无索引5数据分布影响如果使用索引比全表扫描慢则不会用索引索引SQL提示如果某个字段既有联合索引又有单列索引MySQL 优化器会根据成本选择使用哪个索引SQL提示在SQL语句中加入提示来优化操作添加你希望用的索引--use index 建议使用索引 explain select * from tb_user use index(idx_name) where professionmath; --ignore idx 忽略索引 explain select * from tb_user ignore index(idx_name) where professionmath; --force index 强制使用索引 explain select * from tb_user force index(idx_name) where professionmath;覆盖索引减少使用select *尽量使用覆盖索引不需要回表explain的执行计划的extra列如果是using index condition查询使用了索引但是需要回表查询数据如果是using where, using index查询使用了索引需要的数据都在索引列中不需要回表查询数据效率高select id, name from th_user where name ‘Arm’; 使用name的二级索引直接能获得name和id不需要回表所以覆盖索引。例题select id, username, password from tb_user where username’itcast’; 怎么建立索引优化SQL的最优方案建立username, password联合索引不需要回表前缀索引字段类型为字符串时字符串很长时可以仅根据字符串一部分前缀建立索引降低索引体积节省磁盘IOcreate index idx_name on table(column(n)); --前n个字符 --选择性不重复的索引值/数据总数索引选择性越高效率越高唯一索引的选择性是1 select count(distinct email)/count(*) from tb_user; select count(distinct substring(email,1,5))/count(*) from tb_user; --前5个字符选择性很高且减少字符串长度 create index idx_email_5 on tb_user(email(5));联合索引的结构二级索引如果涉及多个查询条件建议建立联合索引。select id,name,phone from tb_user where nametom and phone001;覆盖索引不需要回表查询索引设计原则针对于数据量较大100万且查询比较频繁的表建立索引针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引尽量使用联合索引遵循最左前缀法则减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询